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.

which version of POI are you using? – oers Jan 26 '12 at 08:24 — oers, Jan 26 '12 at 08:24

score 9 · Answer 1 · answered Jan 26 '12 at 09:45

9

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

answered Jan 26 '12 at 09:45

Gagravarr

47,320
10
111
156

I tried the above code.I get the following error Exception in thread "main" java.lang.NoClassDefFoundError: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet at java.lang.J9VMInternals.verifyImpl(Native Method) at java.lang.J9VMInternals.verify(J9VMInternals.java:68) at java.lang.J9VMInternals.initialize(J9VMInternals.java:129) at com.caremark.eztest.common.utilities.CSVToExcelFileConverter.convertCSVToXLSComprehensive(CSVToExcelFileConverter.java:80) at – cma3982 Jan 26 '12 at 23:03
Make sure you have the latest version of POI, and you've included all the [Jars and their dependencies](http://poi.apache.org/overview.html) – Gagravarr Jan 27 '12 at 10:43
Thanks @Gagravarr I noticed i didnt have the jar in runtime environment. That exception is gone now but getCell() on the row is returning null and so i need to create a new cell in the row – cma3982 Jan 27 '12 at 14:47
Thanks @Gagravarr I noticed i didnt have the jar in runtime environment. That exception is gone now but getCell() on the row is returning null and so i need to create a new cell in the row and it doesnt copy the formatting of the template – cma3982 Jan 27 '12 at 14:57

score 3 · Answer 2 · answered Jan 09 '17 at 13:49

I used XSSF and it is working fine.

        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("template.xlsx"));           
        FileOutputStream fileOut = new FileOutputStream("new.xlsx");
        //Sheet mySheet = wb.getSheetAt(0);
        XSSFSheet sheet1 = wb.getSheet("Summary");
        XSSFRow row = sheet1.getRow(15);
        XSSFCell cell = row.getCell(3);
        cell.setCellValue("Bharthan");

        wb.write(fileOut);
        log.info("Written xls file");
        fileOut.close();

Just need to add this dependency in pom.xml of maven

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.8-beta4</version>
    </dependency>

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

2 Answers2