Apache POI：用 Java 写入 excel：打开工作簿

Question

我有一个存储 Excel 工作簿的类：

private Map<String, Workbook> workbooks = new HashMap();

public Workbook createWorkbook(String name)  {

    Workbook workbook = new XSSFWorkbook();

    workbooks.put(name, workbook);

    return workbook;

}

以及写入具有指定名称的工作簿的方法：

public void write(List<ExcelData> data, String workbookName) {

   Workbook workbook = workbooks.get(workbookName);

   CreationHelper createHelper = workbook.getCreationHelper();

   ... write stuff

   FileOutputStream fileOut = new FileOutputStream(workbookName + ".xlsx");

   workbook.write(fileOut);

   fileOut.close();

   workbook.close();

}

但是，当我尝试为同一个工作簿调用方法write两次时：

testExcel.write(data, "Default");

testExcel.write(data1, "Default");

我得到

Exception in thread "main" java.io.IOException: Cannot write data, document seems to have been closed already

错误。我知道我可以像这样打开现有的 Excel 工作簿：

FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

Workbook workbook = WorkbookFactory.create(inputStream);

但我想知道是否有办法通过存储Workbook变量来绕过它。有什么内在机制在起作用？workbook.write()调用后变量是否失效？

Answer 1

您可以将工作簿的名称存储在列表中，而不是存储工作簿本身private List<String> workbooks = new ArrayList();重写 createWorkbook 以仅存储 excel 工作表的名称 重写 write 方法，使其创建一个新工作簿，如下所示public void write(List<ExcelData> data, String workbookName) {Workbook workbook = new XSSFWorkbook();CreationHelper createHelper = workbook.getCreationHelper();... 写东西&nbsp;FileOutputStream fileOut = new FileOutputStream(workbookName + ".xlsx");&nbsp;workbook.write(fileOut);&nbsp;fileOut.close();&nbsp;workbook.close();}

Answer 2

这个错误主要发生在我们试图写入已经关闭的工作簿的内容时。public void writeToExcel(File file) throws IOException {&nbsp; &nbsp; LOGGER.debug("Writing chunks data to excel {}", file);&nbsp; &nbsp; try (FileOutputStream outputStream = new FileOutputStream(file)) {&nbsp; &nbsp; &nbsp; &nbsp; workbook.write(outputStream);&nbsp; &nbsp; } catch (IOException e) {&nbsp; &nbsp; &nbsp; &nbsp; LOGGER.error("Exception raised while writing chunks of items {}", e.getLocalizedMessage());&nbsp; &nbsp; } finally {&nbsp; &nbsp; &nbsp; &nbsp; // This line will take of closing XSSFWorkbook along with SXSSFWorkbook&nbsp; &nbsp; &nbsp; &nbsp; workbook.close();&nbsp; &nbsp; }}这段代码是抛出以下异常的代码Exception thrown is:Exception raised while writing chunks of items&nbsp;"Cannot write data, document seems to have been closed already"&nbsp;为什么我们得到这个例外？看看这段代码&nbsp;private void instructionSheet(Resource resource) {&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; InputStream in = resource.getInputStream();&nbsp; &nbsp; &nbsp; &nbsp; // This is try-with-resources block which is closing the XSSFWorkbook&nbsp; &nbsp; &nbsp; &nbsp; try (XSSFWorkbook xssfwb = new XSSFWorkbook(OPCPackage.open(in))) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; workbook = new SXSSFWorkbook(xssfwb);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; } catch (IOException | InvalidFormatException e) {&nbsp; &nbsp; &nbsp; &nbsp; LOGGER.error("The instruction sheet failed to create {}", e.getLocalizedMessage());&nbsp; &nbsp; }}您可以注意到第二个 try 块是 try-with-resources 块并且正在关闭工作簿，因此我们得到了异常。我们只是通过删除第二个 try 块来解决它，即private void instructionSheet(Resource resource) {&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; workbook = new SXSSFWorkbook(new XSSFWorkbook(OPCPackage.open(resource.getInputStream())));&nbsp; &nbsp; } catch (IOException | InvalidFormatException e) {&nbsp; &nbsp; &nbsp; &nbsp; LOGGER.error("The instruction sheet failed to create {}", e.getLocalizedMessage());&nbsp; &nbsp; }}您可以在这个答案的第一个代码块中注意到，我们在将内容写入文件后关闭工作簿。在 finally 块中调用的close方法将负责关闭XSSFWorkbook实例以及SXSSFWorkbook。