Java 在 Apache POI 中使用 Excel 模板
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/714172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Excel templates with Apache POI
提问by Jake
Basic question: How do I load an Excel template for use with POI and then save it to an XLS file?
基本问题：如何加载用于 POI 的 Excel 模板，然后将其保存到 XLS 文件？
Edit:
编辑：
The answer is:
答案是：
FileInputStream inputStream = new FileInputStream(new File(templateFile));
Workbook workbook = new HSSFWorkbook(inputStream);
(Just load the template as a workbook and then write the workbook as an XLS file elsewhere.)
（只需将模板作为工作簿加载，然后将工作簿作为 XLS 文件写入其他地方。）
采纳答案by Brian Agnew
Have you tried loading it up as a standard .xls using POI, amending it and then saving it ?
您是否尝试过使用 POI 将其作为标准 .xls 加载、修改然后保存？
This is the approach I've used for inserting macros in a POI-generated .xls. I create the file with the macro (admittedly as an .xls) and then load it into my app, populate with data and save as a newly-created .xls. That all worked fine.
这是我用于在 POI 生成的 .xls 中插入宏的方法。我使用宏创建文件（当然是 .xls），然后将其加载到我的应用程序中，填充数据并另存为新创建的 .xls。这一切都很好。
回答by PATRY Guillaume
You can directly load an .xls that will act as the template, and modify it.
您可以直接加载将用作模板的 .xls 并对其进行修改。
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,
将加载一个 xls，保留其结构（包括宏）。然后你可以修改它，
HSSFSheet sheet1 = wb.getSheet("Data");
...
and then save it.
然后保存它。
FileOutputStream fileOut = new FileOutputStream("new.xls");
wb.write(fileOut);
fileOut.close();
Hope this helps.
希望这可以帮助。
回答by Doan Quang Viet
You can also use internal template as a resource.
您还可以使用内部模板作为资源。
InputStream fis = ChartSample.class.getResourceAsStream("/templates.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
HSSFSheet sh = wb.getSheetAt(0);
//Here you go
And save that:
并保存：
out = new FileOutputStream("./new.xls");
wb.write(out);
out.close();
回答by Dherik
You can create a XLS file from a XLS template.
您可以从 XLS 模板创建 XLS 文件。
But, to do this, you need to create a copy of the template every time you need to use the template. If not, you will edit the original template (what you not want).
但是，要做到这一点，您需要在每次需要使用模板时创建模板的副本。如果没有，您将编辑原始模板（您不想要的）。
So, you need first get your template file:
因此，您首先需要获取模板文件：
URL url = Thread.currentThread().getContextClassLoader().getResource("templates/template.xls");
File file = new File(url.getPath());
Copy the template file:
复制模板文件：
try (FileOutputStream fileOutputStream = new FileOutputStream("/home/jake/fileCopiedFromTemplate.xls")) {
Files.copy(file.toPath(), fileOutputStream);
Workbook workbook = new HSSFWorkbook();
workbook.write(fileOutputStream);
}
Access the new copied file:
访问新复制的文件：
FileInputStream inp = new FileInputStream("/home/jake/fileCopiedFromTemplate.xls");
Create a Workbook, so you can write in your new file:
创建一个Workbook, 以便您可以在新文件中写入：
Workbook workbook = WorkbookFactory.create(inp);
After write in your workbook:
在您的工作簿中写入后：
try (FileOutputStream fileOut = new FileOutputStream("/home/jake/fileCopiedFromTemplate.xls")) {
workbook.write(fileOut);
}
A tip to create a XLS template file is mark the template with some variable for you localize the position that you would like to fill. Like:
创建 XLS 模板文件的一个技巧是用一些变量标记模板，以便您本地化要填充的位置。喜欢：
------------------------------------
| | Columna A | Column B |
------------------------------------
| 1 | Some description |
------------------------------------
| 2 | {person.name} | {person.age} |
------------------------------------
回答by Sveta Rybalko
For excel-files with .xlsxuse the following:
对于.xlsx使用以下内容的excel 文件：
FileInputStream inputStream = new FileInputStream(new File("template.xlsx"));
@SuppressWarnings("resource")
Workbook wb = new XSSFWorkbook(inputStream);
Sheet sheet = wb.getSheet("sheet1");
回答by Bharthan
You can use XSSF by adding poi-ooxml dependency in your maven pom.xml if you want to save it in 2007+ format.
如果您想将其保存为 2007+ 格式，您可以通过在您的 maven pom.xml 中添加 poi-ooxml 依赖项来使用 XSSF。
If you have template.xlsx file with a summary sheet in this xmls and you want to change a particular cell you can do it as:
如果您在此 xmls 中有带有摘要表的 template.xlsx 文件，并且您想更改特定单元格，则可以执行以下操作：
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("template.xlsx"));
FileOutputStream fileOut = new FileOutputStream("new.xlsx");
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();
