write data to excel sheet on row 10

KNIME Analytics Platform
1

Does anyone know if it is possible to write data to an Excel file (xlsx) on sheet 2 starting on a specific cell address.
F.i. writing to sheet 2 cell B10

I have found node Excel Writer (XLS) and node Excel Sheet Appender (XLS)

Thanks

2

You will need to add top 9 rows to your table using Table Creator and Concatenate nodes. Plus add a column by Rule Engine with TRUE => “”. And with Column Sorter place new column at the firs position.

3

Hi Izaychik63,

thanks for your solution, but I need to write the data in a sheet with already data in it. There is already data in the first 7 rows.

When I do it like you propose then the first 9 rows are empty and my original data from the first rows are gone.

Do you have an other idea

Thanks

4

You need to read your Excel data to KNIME and add new data then write changes back. Currently KNIME is not writing a portion of the page.

5

oke then I will first write all data to Knime and combine it in here and then write all at once to Excel

Thanks

6

well … there is a way. You could use the Python module openpyxl and open the stored excel file in Python and do some manipulations and then save it again. Openpyxl offers a wide range of possibilities.

Please be aware that this might not be the most stable way, and it could get tricky if you have large or complicated Excel files.

image
image730×593 117 KB

image
image1010×680 248 KB

kn_example_python_excel_manipulate.knar (54.5 KB)

2 Likes
7

Hi Mlauber,

thanks for your contribution. I will have a look at your solution
What do you mean by " not the most stable way" and get tricky"

thanks

8

I had a few error messages first with Openpyxl. Then I changed something in the paths and then reinstalled the library via anaconda. Then it seemed to work just fine. I only meant you should test it if it suits your needs. You can do a lot of manipulations. I would assume manipulating an existing excel file makes sense if you have further graphs or formats and stuff like that. If you just want to add the rows to have the data there it might be better just to load the file, do the manipulations in KNIME and save it again overwriting the original file.

I just wanted to show that there is a way :slight_smile:

9

OK thats clear. Good to know