1. Overview

1.概述

Sometimes, we might need to manipulate Excel files in a Java application.

有时，我们可能需要在一个Java应用程序中操作Excel文件。

In this tutorial, we’ll look specifically at inserting a new row between two rows in an Excel file using the Apache POI library.

在本教程中，我们将具体研究如何使用Apache POI库在Excel文件的两行之间插入新行。

2. Maven Dependency

2.Maven的依赖性

First, we have to add the poi-ooxml Maven dependency to our pom.xml file:

首先，我们必须将poi-ooxmlMaven依赖项添加到我们的pom.xml文件中。

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

3. Inserting Rows Between Two Rows

3.在两行之间插入行

3.1. Apache POI Related Classes

3.1.Apache POI相关类

Apache POI is a collection of libraries — each one dedicated to manipulating a particular type of file. The XSSF library contains the classes for handling the xlsx Excel format. The figure below shows the Apache POI related interfaces and classes for manipulating xlsx Excel files:

Apache POI是一个库的集合–每一个库都专门用于处理一个特定类型的文件。XSSF库包含处理xlsxExcel格式的类。下图显示了Apache POI的相关接口和用于处理xlsx Excel文件的类。

3.2. Implementing the Row Insert

3.2.实现行插入

For inserting m rows in the middle of an existing Excel sheet, all the rows from the insertion point to the last row should be moved down by m rows.

对于在现有Excel工作表的中间插入m行，从插入点到最后一行的所有行应该向下移动m行。

First of all, we need to read the Excel file. For this step, we use the XSSFWorkbook class:

首先，我们需要读取Excel文件。对于这一步，我们使用XSSFWorkbook类。

Workbook workbook = new XSSFWorkbook(fileLocation);

The second step is accessing the sheet in the workbook by using the getSheet() method:

第二步是通过使用getSheet()方法访问工作簿中的工作表。

Sheet sheet = workbook.getSheetAt(0);

The third step is shifting the rows, from the row currently positioned where we want to begin the insertion of new rows, through the last row of the sheet:

第三步是移动行，从目前我们要开始插入新行的位置开始，一直到工作表的最后一行。

int lastRow = sheet.getLastRowNum(); 
sheet.shiftRows(startRow, lastRow, rowNumber, true, true);

In this step, we get the last row number by using the getLastRowNum() method and shift the rows using the shiftRows() method. This method shifts rows between startRow and lastRow by the size of rowNumber.

在这一步，我们通过使用getLastRowNum()方法得到最后一行的编号，并使用shiftRows()方法转移行。这个方法在startRow和lastRow之间按rowNumber的大小移动行。

Finally, we insert the new rows by using the createRow() method:

最后，我们通过使用createRow()方法插入新行。

sheet.createRow(startRow);

It’s worth noting that the above implementation will keep the formatting of the rows being moved. Also, if there are hidden rows in the range we’re moving, they move during the insertion of new rows.

值得注意的是，上面的实现将保持被移动的行的格式。另外，如果在我们要移动的范围内有隐藏的行，它们会在插入新行的过程中移动。

3.3. Unit Test

3.3.单位测试

Let’s write a test case that reads a workbook in the resource directory, then inserts a row at position 2 and writes the content to a new Excel file. Finally, we assert the row number of the result file with the main file.

让我们写一个测试案例，读取资源目录中的工作簿，然后在位置2插入一行，并将内容写入一个新的Excel文件。最后，我们用主文件断言结果文件的行号。

Let’s define a test case:

我们来定义一个测试用例。

public void givenWorkbook_whenInsertRowBetween_thenRowCreated() {
    int startRow = 2;
    int rowNumber = 1;
    Workbook workbook = new XSSFWorkbook(fileLocation);
    Sheet sheet = workbook.getSheetAt(0);

    int lastRow = sheet.getLastRowNum();
    if (lastRow < startRow) {
        sheet.createRow(startRow);
    }

    sheet.shiftRows(startRow, lastRow, rowNumber, true, true);
    sheet.createRow(startRow);

    FileOutputStream outputStream = new FileOutputStream(NEW_FILE_NAME);
    workbook.write(outputStream);

    File file = new File(NEW_FILE_NAME);

    final int expectedRowResult = 5;
    Assertions.assertEquals(expectedRowResult, workbook.getSheetAt(0).getLastRowNum());

    outputStream.close();
    file.delete();
    workbook.close();
}

4. Conclusion

4.总结

In summary, we’ve learned how to insert a row between two rows in an Excel file using the Apache POI library. As always, the full source code of the article is available over on GitHub.

综上所述，我们已经学会了如何使用Apache POI库在Excel文件的两行之间插入一行。与往常一样，本文的完整源代码可在GitHub上获得。