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
– oers

2012-01-26 08:24:08 +00:00
Commented Jan 26, 2012 at 8:24 — oers
– oers, Commented Jan 26, 2012 at 8:24

Gagravarr · Accepted Answer · 2012-01-26 09:45:51Z

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, 2012 at 9:45

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cma3982 Over a year ago

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

Gagravarr Over a year ago

Make sure you have the latest version of POI, and you've included all the Jars and their dependencies

cma3982 Over a year ago

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 Over a year ago

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

Bharthan · Accepted Answer · 2017-01-09 13:49:11Z

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>

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related