Excel Reader dumps formulas

[Cardinal_Fire]

Greetings all -

Question
A quick question about a possibly obvious matter:

• The Excel Reader doesn’t import formulas, only the “results” - is this right?

Background:
I’ve some “COUNTIF” functions that need to run, after Knime, for column summaries that I can’t achieve in Knime. As such, I’m trying to import the pre-prepared columns of Excel formulas so that when the output xlsx file is viewed, they match the relevant columns that Knime has so perfectly created for me.

• Example of pre-prep’d (summarising) formula: =IF(COUNTIF(CP2:CT2,“Non-Compliant”), “Non-Compliant”, “Compliant”)

When however, I import the “source” spreadsheet bearing the 4 columns of source formulas, I only see the default value (“Compliant”), not the formula.

In essence…
I want the formulas to pass through Knime untouched, so they can operate once the resulting xlsx file is viewed in Excel. Is what I’m seeing “just the way it is” or am I doing something obviously foolish? -or is there a solution?

Respectful thanks,
Cardinal

[mlauber71]

The KNIME import and Export function cannot deal with Excel functions themselves. You can only get the results.

If you want to preserve or restore Excel functions you have to either:

• use them on a sheet you would not touch with KNIME, Formulas that might refer to a ‘simple’ data sheet you would im- and export to and from KNIME
• use a Python package like openpyxl or R readxl

Python Excel manipulation

Manipulate tabs inside an excel

You can do several manipulations for Excel with the Python library openpyxl. Please keep in mind that the syntax is not super easy and I encountered some quirks (they may or may not have to do with doing it on a Mac) [image] kn_example_python_excel_manipulate.knar (57.1 KB) Also the concept of Continental to spread the use of KNIME within the company is bearing some fruit, although I have not (yet) seen Excel Data manipulation nodes, but you already can do a lot of things with their nodes. …

write data to excel sheet on row 10

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] [image] kn_example_python_excel_manipulate.knar (54.5 KB)

Python Excel table object

Write KNIME table to Excel as table (proposal)

OK I think now I get what you want. Yes it is possible to first save a table as a ‘pure’ excel file and then define a range as a table object “All” which you can later use to refer to it. In my example the no of lines that the object would have is assigned dynamically while the columns are fixed but one might also be able to change that to a dynamic setting. [image] After the ‘raw’ KNIME export: [image] After openpyxl got to work: [image] (cf. https://openpyxl.readthedocs.io/en/stable/…

[Cardinal_Fire]

mlauber71 Thank you - I figured this was the case but wanted to make sure this fairly straightforward matter didn’t have some cunning resolution.

Appreciate your speed and for kindly taking the time to pull those examples together - I hadn’t thought of that line of investigation so hadn’t yet seen them on the forum. I will look into them and consider the “load versus effort” in relation to their use.

Greatly appreciated. Thank you.

[mlauber71]

If you look for some formatting to apply to your Excel files from within KNIME you might take a look at the brand new

Continental Nodes for KNIME — XLS Formatter Nodes

https://www.knime.com/community/continental-nodes-for-knime-xls-formatter

Maybe one day they will also come up with something to manipulate the structure of Excel files …

[Cardinal_Fire]

(Ha! - As it happens, once Knime spits out a perfect workbook, I then have to spend time “conditional formatting” and otherwise colourising the data for easier human consumption - this Formatter Node is actually something I was looking for months ago! - Great to see this)

Really REALLY appreciate this news and you going the “extra mile” here in sharing it.

Sincere best regards
Gareth

[Cardinal_Fire]

I FOUND THE SOLUTION

On the Source XLSX file, that contains the formula laden columns you want inserted…

…take the “=” sign off the start of the formula ;0)

Therefore the formula “=IF(…” just becomes “IF(…” text.

I can then, after Knime has completed, perform a find and replace on IF swapping all for =IF.

POW!

[genchak]

Hi @mlauber71 I have Anaconda installed and my Excel formatting Python script is running perfectly. I have installed Python in KNIME Preferences correctly. Everything looks right, but KNIME does not recognize openpyxl as a module. KNIME works with pandas module fine. I appreciate a lot if you can help me how I can import openpyxl in KNIME? THis is the error message I got when I ran “kn_python_excel_manipulate” flow above. Thank you very much.

Traceback (most recent call last):
File “C:\Program Files\KNIME\plugins\org.knime.python2_3.7.1.v201901281201\py\PythonKernelBase.py”, line 278, in execute
exec(source_code, self._exec_env, self._exec_env)
File “”, line 6, in
ModuleNotFoundError: No module named ‘openpyxl’