Java Apache Poi SXSSF with Excel Macros

Question

I have a excel template that has macros (.xlsm), I want to read it in, add a million rows to it and write it out.

I know that reading and re-writing files with POI that contain macros will preserve the macros. I need to write out the excel using SXSSF (ram limitations), but SXSSF doesn't read files.

Question: How can I read in an excel with macros using XSSF, and then write out the excel with macros using SXSSF?

Answer 1

Apache POI supports writing a spreadsheet with a large number of rows via SXSSFWorkbook based on a "template workbook". See the relevant constructor for details.

So you would open the .xlsm via XSSFWorkbook and then create the SXSSFWorkbook with that as template.

This should also keep the macros in place as far as I see.