Advanced Excel multi-sheet export cannot be imported until file is opened and saved in Excel

0
Mendix Version: 10.24.13Modules:SONACA Advanced Excel (latest available version from Marketplace)Data Importer moduleIssue:I am using the SONACA Advanced Excel module to generate Excel files.When I export a workbook with a single sheet, I can immediately import the same file successfully using Data Importer.However, when I export a workbook containing multiple sheets, importing the same file fails.The interesting part is that if I open the exported file in Microsoft Excel and simply click Save (without making any changes), the import works successfully afterwards.Observations:Single-sheet export → Import works.Multi-sheet export → Import fails.Multi-sheet export → Open in Excel → Save → Import works.Same import mapping and microflow are being used.Data itself appears valid.This makes me suspect that the workbook generated by Advanced Excel is missing or generating some workbook metadata that Excel automatically repairs when the file is saved.Questions:Has anyone experienced this issue with SONACA Advanced Excel and Data Importer?Is there a known limitation or compatibility issue with multi-sheet workbooks?Does SONACA Advanced Excel fully support exporting multi-sheet workbooks that can be directly re-imported?Are there any recommended workarounds or fixes?Any help would be greatly appreciated.
asked
1 answers
0

Hi Praveen Singh


Yes this is a known class of issue, and your diagnosis is essentially correct. Both SONACA Advanced Excel and the Data Importer run on Apache POI under the hood. The writer (Advanced Excel) and the reader (Data Importer) have different tolerance for OOXML package structure. Excel's importer is strict about the package; the file you generate is valid-enough-to-open but not fully standards-compliant.When you add multiple sheets, the most likely culprits are [content_type].xml / workbook.xml.rels not correctly declaring every sheet part and its relationship ID (single sheet = one trivial relationship; multi-sheet exposes the gap).sharedStrings.xml count/uniqueCount mismatch, or inconsistent inline-vs-shared string handling across sheets.File written with a streaming writer (SXSSF) that doesn't fully finalize workbook metadata.


When you open in Excel and Save, Excel rewrites a clean, normalized OOXML package — which is exactly why the import then succeeds. This is the same root cause behind the Data Importer/Excel Importer's own warning message: if the file was not created with Microsoft Excel for desktop, try opening the file with Excel and saving it with the same name before importing.


Answering your question

  1. Yes, this is a recurring pattern with POI-generated files imported by Mendix's Excel/Data Importer not unique to SONACA but commonly triggered by it.
  2. Multi-sheet is the trigger, not the limitation per it's package-metadata completeness, which multi-sheet exposes.
  3. SONACA can export multi-sheet, but doesn't always produce a package strict enough for direct re-import without the Excel "repair" pass.


Hack which you can worth trying is Add a post-export Java action that opens the generated file with POI's WorkbookFactory and writes it straight back out. This rebuilds the package exactly like Excel's Save


try (Workbook wb = WorkbookFactory.create(inputStream);
     OutputStream out = new FileOutputStream(normalizedFile)) {
    wb.write(out);  // POI rewrites a clean, compliant package
}


I hope this helps

answered