How to implement an existant Excel file without losing format ?

[barp]

Hello,

I’m French and new user of Knime so excuse me for my English and perhaps simpke questions
I have a Template file that i’d like to implement with Knime without losing the format I defined in the file.

I’d just like Knime fills the sheets i want without changing cells format ? Is it possible and how ?

Thanks for your help

Benoit

[mlauber71]

Hello @barp welcome to the KNIME form. You could check out these discussions. There is no genuine way to just export the data from KNIME to excel and keep the formats but you have some options:

Append data from 3 DB query reader to 3 different tabs in XLSX

To actuality append data to an existing Excel sheet I see this options: have the sheet as a data source and the pivot, charts and formats on another sheet. You would have to make sure that there is a new calculation when the Excel file is opened use the Continental KNIME nodes to format the data afterwards. I think they are there for this purpose export to a CSV or sqlite file and have a database import in Excel but then the file would have external dependencies If you want just to add a few …

Manipulating excel Sheets

Well yes that is possible. You could use the mighty Python library OpenPyXL. Yes it is some work but it is doable (you have a few links to examples attached). Then there are the Continental Nodes for KNIME that deal mainly with formatting. They might be useful and possibly easier to handle than Python. Python Excel Drop Down Python Excel manipulation Python Excel table object

[barp]

Hello

Thanks for your answer. I’ve thought about the first solution but I prefer simple solutions and “Killing” Excel manual process after.

The 2nd makes me a litle afraid with Python… Is there another simple solution with a node or Knime function ?

[mlauber71]

@barp

The 2nd makes me a litle afraid with Python… Is there another simple solution with a node or Knime function ?

You could restore/set formats with the Continental nodes. I am not sure if you could extract your current style and then bring it back after you have exported (and overwritten) your current excel sheet (containing your styles and formats).

[barp]

Yes, a little difficult. I know antoher application wtih which you can do it easily. But I don’t have it at work for the moment (i’ve asked for but it’s not free and companies are sometimes afraid to invest)

[barp]

You just tellt he application you insert your results from a line and it keeps all formats defined

[ipazin]

Hi there @barp,

welcome to KNIME Community!

If your Excel file(s) have same/similar and stable structure you can utilize Continental nodes. Create one or more flows defining desired format and after you read data into KNIME and process it write it down with format you had. Should be feasible.

Still I agree that process other application uses is simpler. You mind sharing the application’s name?

Br,
Ivan

[barp]

It’s a French application called Amadea (editor : Isoft) but it’s not free as Knime

@mlauber71 I’ve tried the link with Python (solution I prefer to continental nodes and without simple solution) but I have errors. Would you have an example that works with every thing required (python code and library, excel fils input and output and knime project). I’d like to look.

Else, I will proceed by an export simple and formulas such as vlookup in Excel to present figures the way they were before.

Thanks

[barp]

@everybody if you have full examples which work using Python ?

[mlauber71]

I think the most practical approach would be to separate the data and the formatting in Excel. SO you could just export the data from KNIME into da ‘data sheet’ and the sheets in Excel stay the same. That obviously provides some challanges with consistency etc.

Unfortunately I do not have a full Python example for your case, and I am not entirely familiar with the Continental nodes. But you could check out my repository on the KNIME hub for example containing python and excel. They might give you and idea - but you would have to invest some additional work.

Repository of mlauber71 – Public