Excel Writer created XLSX file only readable by interface after open save close routine

[denisnguyen]

Dear KNIME community,

I am experiencing problems reading an XLSX file created via Excel Writer Node via an interface (that likely tries to read the file in XML-schema).
I discovered that the problem gets resolved if I manually open and save the file in Excel prior to starting the import via interface.
At the same time I see that the file size increases significantly between the initially create file (Excel Writer Node) and the filesize after opening and saving via Excel.

I do not get an error message when executing the workflow (I am runnint version 4.7.). I also do not get any error or prompt when opening the created file in Excel.

I fixed the problem on my local machine by running a .bat file that would open, save and close the created file - however the .bat file cannot be executed on the target system.
Hence I need to get KNIME to writing the XLSX file in the “right” (i.E. machine-readable) format.
Any ideas how to fix this - a solution would be highly appreciated!

Thank you very much in advance and kind regards,

Denis

[mlauber71]

@denisnguyen what you could try is use Openpyxl included in the Python extension to export data to an Excel file and try to use that:

Export Excel File with OpenPyxl (simple) from KNIME - also add some formats –...

[hotzm]

I compared the XLSX files generated by the Excel Writer and by Excel Writer → SaveAs in Excel (using Data Generator->Number to String as sample data). I used unzip to extract the XLSX to a folder and a diff viewer on the contents.
The only substantial difference I can see (apart from some XML attributes being reshuffled, different, etc, and the Excel default theme) is that the Excel Writer does not write a “shared strings table” (in order to optimize for low memory consumption). Maybe the missing shared strings table (more specifically it is empty, if you look at the XML file on disk) is the problem for your interface?

I would be glad if you could test that theory for me. You would hopefully get a suitable workaround and I can see what we would need to fix in an upcoming release.

Fortunately, it turns out that we can coerce the Excel Writer into writing this shared strings table for us .

All we need to do is to get the Excel Writer to “append” to the file and “Evaluate formulas”: then POI’s XSSFWorkbook is not used, which does not use shared-strings-table by default to conserve memory, and instead XSSFWorkbook is used, which uses shared-strings-table by default.

I got it working (and confirmed the presence of the populated shared strings table) with:

1. Excel Writer writes an empty table to the XLSX with the default settings. You can probably also create an empty XLSX in Excel and use that in step 2, but I wanted to test with a KNIME-only solution to make sure we don’t have Excel fixing up anything.
   

   

2. The Data Generator->Number to String data is written by another Excel Writer to the same file (passed via flow variable) with settings:

   • If file exists: append
   • If sheet exists: append
   • Unchecked “Don’t write column headers if sheet exists”
   • Checked “Evaluate formulas”
     
     

The workflow looks very simple and is attached:

WriteXLSXWithSharedStringsTable.knwf (1.2 MB)

The only drawback of this workaround (and any solution producing the shared strings table) is the higher memory consumption.

If this is not the cause for the problem, then I would need more info about any error message from your interface.