如何在java中更新excel文件? [英] How to update an excel file in java?

问题描述

我需要检查一个 excel 文件中的 30 张纸.每张纸最多有 7 行和很多列.

I need to check 30 sheets in an excel file. Every sheet has a maximum of 7 rows and plenty of columns.

我需要通过在最后一个现有列之后添加 32 个新列来更新每一行(如果某个条件为真)，然后保存文件.

I need to update every row (if a certain condition is true) by adding 32 new columns after the last existing column, then save the file.

所以我需要能够从同一段代码读取和写入我的 excel 文件，然后保存我的更改并更新原始 excel 文件(使用相同的文件名).

So I need to be able to do both read and write on my excel file from the same piece of code, then to save my changes updating the original excel file (using the same file name).

推荐答案

我真的认为 Apache Poi 是在 Java 中管理 excel 的最佳解决方案.你可能想看看 at 这些示例 由 Apache Poi 本身提供关于这个主题(网址可能会随着时间的推移略有变化).本文也可以为您要求的解决方案提供提示.

I really think Apache Poi is the best solution when it comes to managing excels in Java. You might want to have a look at these examples provided by Apache Poi itself about this topic (the url may change slightly over time). This article also may provide an hint for the solution you're asking for.

您可以使用 WorkbookFactory 如下:

You can create a new Workbook (the Java Object representing your excel) using WorkbookFactory as follows:

Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFilePath));

其中 excelFilePath 是(显然)要更新的 excel 文件的路径.

where excelFilePath is (obviously) the Path of the excel file you want to update.

你现在可以用你的工作簿做任何你想做的事，欢呼吧！您可以阅读和更改其所有内容，并(最终)编写新内容！请注意，对 Workbook 所做的所有更改实际上都不会影响您的 excel 文件.您正在编辑 Workbook Java 对象，而不是实际的excel文件.从某种意义上说，您的 Workbook 最初是根据您的 excel 文件内容创建的，然后完全独立于它.

You can now do whatever you want with your Workbook, hurray! You can both read and change all its content and (eventually) write new one! Note that all the changes to your Workbook are NOT actually going to affect your excel file. You're editing the Workbook Java Object, not the actual excel file. In a sense, your Workbook is initially created based on your excel file content, then it is totally independent from it.

完成工作簿的编辑后，您可以保存它以覆盖您的初始Excel文件(在某种程度上，您现在正在使用所有更改更新您的Excel文件em>) 如下:

Once you're done editing your Workbook, you can save it overriding your initial excel file (in a way, you're now updating your excel file with all your changes) as follows:

FileOutputStream outputStream = new FileOutputStream(excelFilePath);
workbook.write(outputStream);
workbook.close();
outputStream.close();

你就完成了！简单有效！

and you're done! Simple and effective!

以下是一些可能适合您的需求的代码:

Here it is some code that may suit your needs:

public static void main(String[] args) throws EncryptedDocumentException, IOException {
    
    // Step 1: load your excel file as a Workbook
    
    String excelFilePath = "D:\\Desktop\\testExcel.xlsx";
    Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFilePath));
    
    // Step 2: modify your Workbook as you prefer
    
    Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // Getting an iterator for all the sheets
    while (sheetIterator.hasNext()) {
        Iterator<Row> rowIterator = sheetIterator.next().rowIterator(); // Getting an iterator for all the rows (of current sheet)
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            // Put here your internal logic to understand if the row needs some changes!
            int cellsn = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
            for (int j = cellsn ; j < cellsn + 32 ; ++j) {
                row.createCell(j, CellType.STRING).setCellValue("New column after the last existing one n°" + (j - cellsn + 1));
            }
        }
    }
    
    // Step 3: update the original excel file with all your amazing changes!
    
    FileOutputStream outputStream = new FileOutputStream(excelFilePath);
    workbook.write(outputStream);
    workbook.close();
    outputStream.close();
}

一些最后的说明:

不要调用方法 create(java.io.File file) 而不是 create(java.io.InputStream inp) 同时使用 WorkbookFactory 并创建您的 工作簿，它会在保存文件时导致问题，因为它在 这篇文章.

Some final notes:

Do not call the method create(java.io.File file) instead of create(java.io.InputStream inp) while using WorkbookFactory and creating your Workbook, it will lead to problems when saving your file as it's discussed on this post.

我真的不知道您最初添加标签硒"的原因是什么?对你的问题但是如果你想使用一些蜘蛛代码来手动"打开和编辑你的excel文件，请不要.这个解决方案和 HSSF 一样可怕(你有没有看到 笑话?).

I really don't know the reason why you initially add the tag "selenium" to your question but if you're thinking to use some spider-code to "manually" open and edit your excel file, please don't. This solution is as horrible as HSSF is (did you get the joke?).

如果您使用 .xlsx excel 文件，您可能需要使用 Apache Poi XSSF 对象(如 XSSFWorkbook, XSSFRow 和 XSSFCell).更多关于XSSF 与 HSSF 在这篇博文中的对比.

If you're using .xlsx excel files, you may want to use Apache Poi XSSF objects (as XSSFWorkbook, XSSFRow and XSSFCell). More about XSSF vs HSSF on this post.

最后，这是我的代码运行所需的所有库的明确列表(它们很多，不要惊慌):Apache Poi 4.1.2, Apache Poi Ooxml 4.1.2, Apache Poi Ooxml Schemas 4.1.2，XmlBeans, 公共编解码器, Commons Collections4, Commons Compress 和 Commons Math3.如果您使用 Maven，只需将以下几行添加到您的依赖项文件中:

Finally, here it is the explicit list of all libreries needed in order for my code to run (they're a lot, do not panic): Apache Poi 4.1.2, Apache Poi Ooxml 4.1.2, Apache Poi Ooxml Schemas 4.1.2, XmlBeans, Commons Codec, Commons Collections4, Commons Compress and Commons Math3. If you're using Maven, just add to your dependencies file the following lines:

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>4.1.2</version>
</dependency>

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

仅此而已，希望对您有所帮助！干得好！

And that's all, hope it helps! Good work!

这篇关于如何在java中更新excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！