Modify an excel file storaged in onedrive from Knime

[Jalvear]

Hello

I want to know if it’s possible modify an excel that is in one drive from Knime.
If the changes made from Knime could be seen on the excel file?

Thank you
JAlvear

[bruno29a]

Hi @Jalvear , the way it works in Knime is, you don’t really open an Excel file for modification as you would via Excel. In Knime, you read an Excel file where the data is in the form of a Knime table. You then do any manipulation/processing that you need, which will result in a Knime table. You can then save that manipulated data as the same file that you read, which will overwrite the file with the new data.

So the excel file will contain the changes made from Knime after that. This means you will need permission to overwrite the file.

[mlauber71]

@Jalvear if you want to have a short overview about waht you can and cannot do with KNIME and Excel here is a summary:

Excel Reader - Transformation -Removing Columns Multiple Sheets

@mb7846 I think we would first have to establish what you and and cannot do with generic Excel functions in KNIME right now you can load almost all Excel sheets in various settings (all sheets from one file at once if they have the same structure), loop thru a lot of Excel files, read all sheet names to make decisions about them and so on. So you can import all kinds of dats from Excel then you can EXPORT data either by writing a new file or replacing a single sheet (which will be a plain data…

To explore further you could have a look at this collection

Import EXCEL - a meta workflow trying to answer questions about importing...

Then concerning OneDrive you might want to consider your specific settings. Would you write the file to your ‘local’ ONeDrive folder and then it would get synced to the cloud or do you want to write directly to the cloud folder?

https://forum.knime.com/search?q=OneDrive

[Jalvear]

Hello

When I do this I lost the excel format like the colors in each sheet, conditionals an formulas.

That’s why I’d like to modify from Knime directly in order to not disturb this process

[bruno29a]

Hi @Jalvear , as I explained, Knime does not open an Excel file for modification. However, you could probably accomplish what you are trying to do via Java (so Java Snippet), there must be some libraries in Java that allows you to open an Excel file and modify the file.

EDIT: It looks like the org.apache.poi library would do.

[mlauber71]

@Jalvear You could try this community node and see if this can help you

Write To Excel Template

You can update cells with this node:

Excel Cell Updater

The formatting of existing cells, rows and columns in the Excel sheet will be preserved.

Maybe you could try some of these. Maybe you could provide us with an example.

[Jalvear]

Hello

Thanks a lot for the explanation given. I’m going to try it. I’ll tell you.