Copy Rows and paste into an existing excel file with existing layout

Hello KNIME-community,

i am new at the KNIME-universe and i have a short question.

After I put together two tables and filtered, I get the following lines (picture A)

. I would like to copy and paste these lines into the already existing file with existing layout.

Can someone help me with which node I achieve this?

I have already tried some nodes (e.g. concatenate, joiner etc.), but always the existing layout (picture B)

is “destroyed”.

Can someone help me?

Many, many thanks in advance!

Chrimbo

You can use Excel Sheet Uppender and then reference data from uppended sheet to the form. Direct form filling is not possible without coding.

4 Likes

I think basically you have three options.

  • Export the data to a separate Excel sheet and use Excel references like lookup to bring the data to your table
  • Bring the data together in KNMIE the way you want them and Export them. Downside no Formulas
  • Use Python to manipulate an existing Excel File or write formulas to cells

Or a combination

Python Excel Drop Down

Python Excel manipulation

4 Likes

Hi izaychik63 and mlauber71,

thank you very much for your fast answer.

I thought that this is not so easy. I am really new, so I have to work through your answer mlauber71. but i will give you feedback! Thank you for your answers !

2 Likes

Is there no possibility in Knime, to mark certain cells within an Excel sheet and then paste only the values at another place / sheet?

Would be so easy in Excel or with VBA and I would assume it should not that difficult to create such a node?

Unfortunately I’m beginner and have no glue how to create nodes.
Anybody an idea how to realise such a task with a certain Knime node?

Thx & BR
Stefan

Hi there @MrS,

welcome to KNIME Community!

No to my knowledge. And don’t think it is easy as it sounds. You have to import data from Excel. Choose rows and columns you need and then write it into desired location.

Br,
Ivan

There are two ways you can do this. One, uppend the Excel data and then put data to the form.
The other way is to copy the required rows and columns and then pasting them on to the form.

1 Like

Hello Ivan, thx for your reply. The problem I have is not to get the data, but to paste it into an formatted Excel sheet without changing format (column and row width / height, grid lines, other formatting).
In Excel I would copy the data Array and special paste only the values, not the Format. At the moment, I need to do this manually after
Knime Processing, and I would have preferred not to touch the final Excel sheet. Perhaps there is a way, do this with a node where to include a VBA Macro?

Hi @MrS,

yep, I understand. What you can try is to have formulas in your formatted sheet to existing sheet where you are going to write data from KNIME. This way once you write data sheet will be filled with new data and you should not lose your formatting.

Regarding VBA Macro check this topic:

Br,
Ivan

hi, Ivan, can you please give more details how to write data from KNIME into certain excel cells?
I have quite similar task as in this topic.

I have this form:

Order TODAY()
date TODAY()
Batch number: 7480
№ п/п Dr Cr APA № Cума/ Amount Currency
* 1 4 563458 965 107777 Calculation1+Calculation2 USD
* 2 4 563458 965 107777 Calculation1 USD
* 3 4 563458 965 107777 Calculation1 USD
Total CR
Total DR
Maker: Finance analyst Surname
Checker: CFO Surname

I got Calculation1 and Calculation2 as a result of my workflow and now I need to populate them into this form.

I also need to update cells TODAY() with current date and update surnames and positions of a Maker and a Checker

@Skirpichnikova_Ksenia if you absolutely must change values on an existing Excel sheet I only know the above mentioned way to use Python and openpyxl.

Maybe you check out the examples linked under the 400er numbers in this meta collection:

1 Like