如何在已有的excel中使用HSSF (Apache POI)在两行之间插入一行

Somehow I manage to create new rows between two rows in an existing excel file. The problem is, some of the formatting were not include along the shifting of the rows.

在现有的excel文件中，我设法在两行之间创建新的行。问题是，有些格式并没有随行移动而包含。

One of this, is the row that are hide are not relatively go along during the shift. What I mean is(ex.), rows from 20 to 30 is hidden, but when a create new rows the formating still there. The hidden rows must also move during the insertion/creation of new rows, it should be 21 to 31.

其中之一是隐藏的行在转换过程中不会相对地运行。我的意思是(例如)，从20到30的行是隐藏的，但是当创建新的行时，格式化仍然存在。在插入/创建新行期间，隐藏行也必须移动，应该是21到31。

Another thing is, the other object in the sheet that are not in the cell. Like the text box are not move along after the new row is created. Its like the position of these object are fixed. But I want it to move, the same thing as I insert a new row or paste row in excel. If there is a function of inserting a new row, please let me know.

另一件事是，表中不在单元格中的另一个对象。就像创建新行之后，文本框不会移动。它的位置是固定的。但是我想让它移动，就像我在excel中插入新行或粘贴行一样。如果有插入新行的功能，请告诉我。

This what I have right now, just a snippet from my code.

这就是我现在所拥有的，我的代码片段。

HSSFWorkbook wb = new HSSFWorkbook(template); //template is the source of file
HSSFSheet sheet = wb.getSheet("SAMPLE");
HSSFRow newRow;
HSSFCell cellData;

int createNewRowAt = 9; //Add the new row between row 9 and 10

sheet.shiftRows(createNewRowAt, sheet.getLastRowNum(), 1, true, false);
newRow = sheet.createRow(createNewRowAt);
newRow = sheet.getRow(createNewRowAt);

If copy and paste of rows is possible that would be big help. But I already ask it here and can't find a solution. So I decided to create a row as an interim solution. I'm done with it but having a problem like this.

如果复制和粘贴行是可能的，那将是很大的帮助。但我已经问过了，找不到解决办法。所以我决定创建一个row作为临时解决方案。我受够了，但有个问题。

Any help will be much appreciated. Thanks!

非常感谢您的帮助。谢谢!

8 个解决方案

#1

Helper function to copy rows shamelessly adapted from here

帮助函数复制在这里不厚道地修改的行。

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class RowCopy {

    public static void main(String[] args) throws Exception{
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("c:/input.xls"));
        HSSFSheet sheet = workbook.getSheet("Sheet1");
        copyRow(workbook, sheet, 0, 1);
        FileOutputStream out = new FileOutputStream("c:/output.xls");
        workbook.write(out);
        out.close();
    }

    private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Get the source / new row
        HSSFRow newRow = worksheet.getRow(destinationRowNum);
        HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

        // If the row exist in destination, push down all rows by 1 else create a new row
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Loop through source columns to add to new row
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Grab a copy of the old/new cell
            HSSFCell oldCell = sourceRow.getCell(i);
            HSSFCell newCell = newRow.createCell(i);

            // If the old cell is null jump to next cell
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Copy style from old cell and apply to new cell
            HSSFCellStyle newCellStyle = workbook.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            ;
            newCell.setCellStyle(newCellStyle);

            // If there is a cell comment, copy
            if (oldCell.getCellComment() != null) {
                newCell.setCellComment(oldCell.getCellComment());
            }

            // If there is a cell hyperlink, copy
            if (oldCell.getHyperlink() != null) {
                newCell.setHyperlink(oldCell.getHyperlink());
            }

            // Set the cell data type
            newCell.setCellType(oldCell.getCellType());

            // Set the cell data value
            switch (oldCell.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    newCell.setCellValue(oldCell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    newCell.setCellValue(oldCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_ERROR:
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    newCell.setCellFormula(oldCell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    newCell.setCellValue(oldCell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    newCell.setCellValue(oldCell.getRichStringCellValue());
                    break;
            }
        }

        // If there are are any merged regions in the source row, copy to new row
        for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                        (newRow.getRowNum() +
                                (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                        )),
                        cellRangeAddress.getFirstColumn(),
                        cellRangeAddress.getLastColumn());
                worksheet.addMergedRegion(newCellRangeAddress);
            }
        }
    }
}

#2

Referencing Qwerty's answer, you can avoid to inflate XL size by re-using cellStyle. And when the type is CELL_TYPE_BLANK, getStringCellValue returns "" instead of null.

参照Qwerty的回答，您可以通过重复使用cellStyle避免膨胀XL码。当类型为CELL_TYPE_BLANK时，getStringCellValue返回“”而不是null。

private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
  // Get the source / new row
  Row newRow = worksheet.getRow(destinationRowNum);
  Row sourceRow = worksheet.getRow(sourceRowNum);

  // If the row exist in destination, push down all rows by 1 else create a new row
  if (newRow != null) {
    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
  } else {
    newRow = worksheet.createRow(destinationRowNum);
  }

  // Loop through source columns to add to new row
  for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
    // Grab a copy of the old/new cell
    Cell oldCell = sourceRow.getCell(i);
    Cell newCell = newRow.createCell(i);

    // If the old cell is null jump to next cell
    if (oldCell == null) {
      newCell = null;
      continue;
    }

    // Use old cell style
    newCell.setCellStyle(oldCell.getCellStyle());

    // If there is a cell comment, copy
    if (newCell.getCellComment() != null) {
      newCell.setCellComment(oldCell.getCellComment());
    }

    // If there is a cell hyperlink, copy
    if (oldCell.getHyperlink() != null) {
      newCell.setHyperlink(oldCell.getHyperlink());
    }

    // Set the cell data type
    newCell.setCellType(oldCell.getCellType());

    // Set the cell data value
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
      break;
    case Cell.CELL_TYPE_BOOLEAN:
      newCell.setCellValue(oldCell.getBooleanCellValue());
      break;
    case Cell.CELL_TYPE_ERROR:
      newCell.setCellErrorValue(oldCell.getErrorCellValue());
      break;
    case Cell.CELL_TYPE_FORMULA:
      newCell.setCellFormula(oldCell.getCellFormula());
      break;
    case Cell.CELL_TYPE_NUMERIC:
      newCell.setCellValue(oldCell.getNumericCellValue());
      break;
    case Cell.CELL_TYPE_STRING:
      newCell.setCellValue(oldCell.getRichStringCellValue());
      break;
    }
  }
}

#3

I merged some of the other answers and comments in the following implementation, tested with Apache POI v3.9.

我在下面的实现中合并了一些其他的答案和注释，并与Apache POI v3.9进行了测试。

I have only one rownum parameter because I shift down the target row and copy it in the new empty row. Formulas are handled as expected, they are not copied verbatim, with one exception: references to cells that are above the copied line are not updated; the workaround is to replace these explicit references (if any) with references calculated using INDIRECT() as suggested by this post.

我只有一个rownum参数，因为我向下移动目标行并将其复制到新的空行中。公式按预期的方式处理，它们不会逐字复制，但有一个例外:对复制行之上的单元格的引用不会更新;解决方法是用间接()计算的引用替换这些显式引用(如果有的话)。

protected void copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) 
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow;  //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1);  //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(formulasArray[i]);
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
            default:   
                break; 
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() +
                            (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                    )),
                    cellRangeAddress.getFirstColumn(),
                    cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
}

I'm using this implementation in production code.

我在产品代码中使用这个实现。

#4

For people who are looking to insert a row between two rows in an existing excel with XSSF (Apache POI), there is already a method "copyRows" implemented in the XSSFSheet.

对于那些希望在现有的XSSF (Apache POI)中插入一行的人来说，已经有了在XSSFSheet中实现的方法“copyRows”。

import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;

public class App2 throws Exception{
    public static void main(String[] args){
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("input.xlsx"));
        XSSFSheet sheet = workbook.getSheet("Sheet1");
        sheet.copyRows(0, 2, 3, new CellCopyPolicy());
        FileOutputStream out = new FileOutputStream("output.xlsx");
        workbook.write(out);
        out.close();
    }
}

#5

As to formulas being "updated" in the new row, since all the copying occurs after the shift, the old row (now one index up from the new row) has already had its formula shifted, so copying it to the new row will make the new row reference the old rows cells. A solution would be to parse out the formulas BEFORE the shift, then apply those (a simple String array would do the job. I'm sure you can code that in a few lines).

公式被“更新”的新行,因为所有的复制发生转变后,旧的行(现在一个索引的新行)已经有了自己的公式了,所以将它复制到新行将新行参考旧行细胞。一种解决方案是在移位之前解析这些公式，然后应用它们(一个简单的字符串数组就可以做到这一点)。我相信你可以用几行代码写出来)。

At start of function:

在开始功能:

ArrayList<String> fArray = new ArrayList<String>();
Row origRow = sheet.getRow(sourceRow);
for (int i = 0; i < origRow.getLastCellNum(); i++) {
    if (origRow.getCell(i) != null && origRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) 
        fArray.add(origRow.getCell(i).getCellFormula());
    else fArray.add(null);
}

Then when applying the formula to a cell:

然后当将公式应用到单元格时:

newCell.setCellFormula(fArray.get(i));

#6

I came across the same issue recently. I had to insert new rows in a document with hidden rows and faced the same issues with you. After some search and some emails in apache poi list, it seems like a bug in shiftrows() when a document has hidden rows.

我最近遇到了同样的问题。我必须在一个包含隐藏行的文档中插入新的行，并与您面临相同的问题。在apache poi列表中进行了一些搜索和电子邮件之后，当一个文档有隐藏的行时，看起来就像是shiftrows()中的一个bug。

#7

Referencing Qwerty's answer, if the destRow isnot null, sheet.shiftRows() will change the destRow's reference to the next row; so we should always create a new row:

引用Qwerty的答案，如果destRow不是null，那么sheet.shiftRows()将更改destRow对下一行的引用;因此，我们应该始终创建一个新的行:

if (destRow != null) {
  sheet.shiftRows(destination, sheet.getLastRowNum(), 1);
}
destRow = sheet.createRow(destination);

#8

I've implemented this in Kotlin like this:

我在Kotlin实现了这个

fun Sheet.buildRow ( rowNum:Int ) : Row {
    val templateRow = this.getRow( rowNum )
    this.shiftRows( rowNum+1, sheet.lastRowNum, 1 )
    val newRow = this.createRow( rowNum+1 )
    templateRow.cellIterator().forEach {
        newRow.createCell( it.columnIndex ).cellStyle = it.cellStyle
    }
    return templateRow
}

It doesn't copy the cell values, just the format. Should be applicable to Java as well.

它不复制单元格值，只是格式。也应该适用于Java。