Excel Sheet Appender - Bugs??

KNIME Analytics Platform
1

I am working on an report distribution where I append Excel sheets to an existing report and ultimately attach that file to an email and send.  

One issue I am having is that the Excel Sheet Appender is failing evaluation on cells that contain an AVERAGEIFS function that is dependent on a TODAY function.  

When I change it to a SUMIFS/COUNTIFS with the same criteria, the KNIME error no longer generates on execution, but the cell values still will not update automatically (if I open the workbook, it is fine, but if I preview the workbook in Outlook, the cells do not update correctly).  

I have an additional COUNTIFS/COUNTIFS that will not evaluate until I refresh calculations, but I have plenty of other cells that contain COUNTIFS or SUMIFS that do evaluate correctly.  

Is this a bug in the Excel Sheet Appender, or a limitation within Excel itself?

2

Hi Evan,

Are you creating the formulas within KNIME, or were they already present when you read the data into KNIME? The difference between those two is that formulas will only evaluate if they were already in another sheet in the Excel file. This allows you to update the values based on the new data coming in. If you create the formulas in KNIME, they are only passed as strings to Excel and not evaluated.

Cheers,

Roland

3

They are present in the Excel sheet before the data is appended.

If I opened the Excel and refreshed all formulas, they would update, but that added an additional step.

4

I think this discussion is relevant to your question. Both approaches use Python either to:

  • open and save Excel after having entered the data. The solution discussed would only work on Windows (I have not done any research if there might be packages out there for Mac or Linux/Unix)
  • store the formulas with the values you want to have. Then other applications like email preview would respect that. The downside is you would have to do the calculations und the formulas all in Python (via KNIME) and then store them

Hmm one very brutal way I maybe would have used in the old days would go via VBA. Open and save all the Excel files. Maybe via a Batch script that first executes the KNIME workflow to fill the Excel files, then an Excel file that does the opening and storing and finally one last KNIME workflow via batch that would send out the Emails … yes dirty and maybe an overkill.

2 Likes