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

问题描述

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

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

因此，我需要能够从同一段代码对excel文件进​​行读写，然后保存更改以更新原始excel文件(使用相同的文件名).

解决方案

我真的认为 Apache Poi 是在Java中，管理方面的最佳解决方案胜于Java.您可能想看看 (网址可能会随着时间的推移而略有变化).本文也可能会提示您所需的解决方案.

您可以使用

一些最后的注意事项:

请勿调用方法创建(java.io.File文件)，而不是这篇文章.

我真的不知道您最初在标签中添加标签"selenium"的原因.问题，但是如果您正在考虑使用一些蜘蛛代码来手动"打开并编辑您的excel文件，请不要这样做.此解决方案与HSSF一样可怕(您是否收到笑话?).

如果您使用的是.xlsx excel文件，则可能要使用Apache Poi XSSF对象(如 XSSFCell ).有关 XSSF与HSSF的更多信息./p>

最后，这是运行我的代码所需的所有自由的明确清单(它们很多，不要惊慌):常用编解码器，公用收藏集4 ，常用数学3 .如果您使用的是Maven，只需将以下行添加到依赖项文件中:

 < dependency>< groupId> org.apache.poi</groupId>< artifactId> poi</artifactId>< version> 4.1.2</version></dependency><依赖关系>< groupId> org.apache.poi</groupId>< artifactId> poi-ooxml</artifactId>< version> 4.1.2</version></dependency> 

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

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

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.

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).

解决方案

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.

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

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

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

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.

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

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.

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?).

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.

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屋！