在Java，新版本的Excel中从模板创建Excel工作表

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

我发现以下代码从现有模板创建excel表格，并使用格式并向其添加数据并将其保存到新文件中

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.

这绝对没问题。但我的问题是我现在正在处理新版本的excel。所以我需要开发一个类似的代码来处理新版本的模板。有人可以建议我怎么做？我尝试将HSSWorkbook更改为XSSFWorkbook。但是XSSFWorkbook没有允许我传递布尔值的构造函数。也。当我尝试它时，它会复制数据，但带有数据的行不会保留模板所具有的列的格式。

2 个解决方案

#1

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

这应该可以正常工作（尽管最好使用最新版本的POI来修复所有错误）：

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

如果您对接口进行编码，那么您只需在构造函数中交换HSSF和XSSF，并使代码适用于两种格式

#2

I used XSSF and it is working fine.

我使用XSSF，它工作正常。

        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

只需要在maven的pom.xml中添加这个依赖项

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

#1