Creating excel sheet from template in Java, new versions of Excel

Question

I found the following code to create a excel sheet from an existing template with formats and add data to it and save it to a new file

POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream("template.xls"));
HSSFWorkbook wb = new  HSSFWorkbook(fs, true);
Will load an xls, preserving its structure (macros included). You can then modify it,

HSSFSheet sheet1 = wb.getSheet("Data"); ...

and then save it.

FileOutputStream fileOut = new FileOutputStream("new.xls"); 
wb.write(fileOut);
fileOut.close();

This works absolutely fine. But my issue is that I am dealing with new versions of excel now. So I need to develop a similar code to handle new version of template. Can someone suggest how can I do this? I tried changing HSSWorkbook to XSSFWorkbook. however XSSFWorkbook doesn't have a constructor that lets me pass a boolean. Also. when i tried it, it copies the data but the rows with data do not retain the formatting of the columns that the template has.

Answer 1

This should work fine (though it's always best to use the latest version of POI for all the bug fixes):

Workbook wb = new XSSFWorkbook( OPCPackage.open("template.xlsx") );
Sheet sheet = wb.getSheetAt(0);

// Make changes to the sheet
sheet.getRow(2).getCell(0).setCellValue("Changed value"); // For example

// All done
FileOutputStream fileOut = new FileOutputStream("new.xls"); 
wb.write(fileOut);
fileOut.close();

If you code against the interfaces, then you can just swap between HSSF and XSSF in your constructor, and have your code work for both formats