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();

... 写东西

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

 workbook.write(fileOut);

 fileOut.close();

 workbook.close();

}

Answer 2

这个错误主要发生在我们试图写入已经关闭的工作簿的内容时。

public void writeToExcel(File file) throws IOException {

    LOGGER.debug("Writing chunks data to excel {}", file);

    try (FileOutputStream outputStream = new FileOutputStream(file)) {

        workbook.write(outputStream);

    } catch (IOException e) {

        LOGGER.error("Exception raised while writing chunks of items {}", e.getLocalizedMessage());

    } finally {

        // This line will take of closing XSSFWorkbook along with SXSSFWorkbook

        workbook.close();

    }

}

这段代码是抛出以下异常的代码

Exception thrown is:

Exception raised while writing chunks of items 

"Cannot write data, document seems to have been closed already" 

为什么我们得到这个例外？看看这段代码

 private void instructionSheet(Resource resource) {

    try {

        InputStream in = resource.getInputStream();

        // This is try-with-resources block which is closing the XSSFWorkbook

        try (XSSFWorkbook xssfwb = new XSSFWorkbook(OPCPackage.open(in))) {

            workbook = new SXSSFWorkbook(xssfwb);

        }

    } catch (IOException | InvalidFormatException e) {

        LOGGER.error("The instruction sheet failed to create {}", e.getLocalizedMessage());

    }

}

您可以注意到第二个 try 块是 try-with-resources 块并且正在关闭工作簿，因此我们得到了异常。

我们只是通过删除第二个 try 块来解决它，即

private void instructionSheet(Resource resource) {

    try {

        workbook = new SXSSFWorkbook(new XSSFWorkbook(OPCPackage.open(resource.getInputStream())));

    } catch (IOException | InvalidFormatException e) {

        LOGGER.error("The instruction sheet failed to create {}", e.getLocalizedMessage());

    }

}

您可以在这个答案的第一个代码块中注意到，我们在将内容写入文件后关闭工作簿。

在 finally 块中调用的close方法将负责关闭XSSFWorkbook实例以及SXSSFWorkbook。