如何在Java中更新Excel文件?

问题描述

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

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

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

解决方案

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

您可以使用 WorkbookFactory 如下:

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

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

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

完成 Workbook 的编辑后，您可以将其保存为覆盖您的初始excel文件(以某种方式，您现在将使用所有更改来更新excel文件)如下:

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

您就完成了！简单有效！

这是一些适合您需求的代码:

  public static void main(String [] args)引发EncryptedDocumentException，IOException {//步骤1:将您的excel文件加载为工作簿字符串excelFilePath ="D:\\ Desktop \\ testExcel.xlsx";工作簿工作簿= WorkbookFactory.create(new FileInputStream(excelFilePath));//步骤2:根据需要修改工作簿迭代器< Sheet>sheetIterator = workbook.sheetIterator();//获取所有工作表的迭代器while(sheetIterator.hasNext()){迭代器< Row>rowIterator = sheetIterator.next().rowIterator();//为(当前工作表的)所有行获取一个迭代器而(rowIterator.hasNext()){行row = rowIterator.next();//将您的内部逻辑放在这里，以了解该行是否需要更改！int cellsn = row.getLastCellNum()== -1吗?0:row.getLastCellNum();for(int j = cellsn; j< cellsn + 32; ++ j){row.createCell(j，CellType.STRING).setCellValue(在最后一个存在的n°之后的新列" +(j-celln + 1))；}}}//步骤3:使用所有惊人的更改来更新原始excel文件！FileOutputStream outputStream =新的FileOutputStream(excelFilePath);workbook.write(outputStream);workbook.close();outputStream.close();} 

一些最后的注意事项:

请勿调用方法创建(java.io.File文件)，而不是创建(java.io.InputStream inp)，同时使用 WorkbookFactory 并创建您的工作簿，保存文件时会出现问题，如.

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

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

最后，这是运行我的代码所需的所有自由的明确清单(它们很多，不要惊慌): Apache Poi 4.1.2 ， Apache Poi Ooxml 4.1.2 ， Apache Poi Ooxml模式4.1.2 ， XmlBeans ，常用编解码器，公用收藏集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文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！