logo标签列表

如何使用HSSF（Apache POI）在现有的Excel中在两行之间插入一行

javaexcelapache-poipoi-hssf
58
一些如何在现有的Excel文件中两行之间创建新行。问题是，在调整行时，一些格式未被包含在内，其中一个例子是隐藏的行没有相对移动。我的意思是（例如）从20到30的行被隐藏了，但是当我创建新行时，格式仍然存在。随着插入/创建新行，隐藏的行也必须移动，应该是21到31。另一个问题是表格中的其他对象并非在单元格中，例如文本框在创建新行后不会移动，就好像这些对象的位置是固定的一样。但是我希望它们能够移动，就像我在Excel中插入新行或复制行一样。如果有插入新行的函数，请让我知道。这是我现在的代码片段。
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);

如果可以复制和粘贴行，那将是很大的帮助。但我已经在这里提问过了，没有找到解决方案。因此，我决定创建一个行作为临时解决方案。我完成了它，但遇到了这样的问题。

任何帮助都将不胜感激。谢谢！

4
这个问题已经帮助了我两次！ :-) 感谢你的提问。 - Peter Perháč
尝试使用Java Apache POI在Excel中插入一行，可以参考此链接：https://dev59.com/AWUq5IYBdhLWcg3waPzr - deldev
8个回答
75

从这里借鉴并修改的行复制辅助函数。

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
你知道我们如何支持在复制时自适应公式吗？比如我将第一行复制到第二行，并想包含公式。所以原始公式可能是sum(A1:K1)用于第一行，现在我希望我的第二行（副本）有sum(A2:K2)。目前，它直接复制公式，所以我得到两个都使用例如sum(A1:K1)的结果。 - Toby
4
我已经获取了这段代码并且它对我起作用。我做了两个小改动。1) 使用Row、Sheet和Workbook的接口代替HSSF*具体类；2) 使得从源行复制数据变成可选的。 - Bill Comer
1
有什么特别的原因导致这段代码会遇到一个运行时错误 "java.lang.RuntimeException" 尚未实现？:| 相当困惑。 - Stupid.Fat.Cat
@Stupid.Fat.Cat，这个错误可能通过从Workbook类中删除HSSF前缀来修复，Workbook是HSSFWorkbook的父类，因此在更广泛的使用中使用Workbook更为合理。 - ivan.mylyanyk
还有一点不要忘记，每个工作簿的单元格样式是有限制的。我的代码在40000时出现了问题，尽管微软声称有64000的限制。无论如何，如果您不需要更改样式，最好复制样式而不是创建新样式。 - mykola
显示剩余6条评论
17

对于想要在现有的XSSF（Apache POI） Excel文件中，在两行之间插入一行的人来说，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();
    }
}
这不会插入新行，但是结合shiftRows和copyRows可以正常工作！ - golimar
1
shiftRows和copyRows的功能都很好，但遗憾的是它们非常缓慢。 - dgregory
12

参考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;
    }
  }
}
4
记录一下，复制评论并不能解决问题。首先，代码检查 newCell.getCellComment() 的值，当然是 null。然而，修复这个 if 语句也不起作用，因为评论会被移动而不是被复制。需要实例化一个新的 Comment，这更加复杂。请参见 https://dev59.com/NkzSa4cB1Zd3GeqPjAkl - Didier L
如果您的 Excel 文件中的样式部分太大（即 .xlsx 文件中的 styles.xml 文件过大、JVM 的堆使用过多、执行速度太慢等），请确保按照这个示例操作。 - golimar
5

参考Qwerty的答案，如果destRow不为null，sheet.shiftRows()会将destRow的引用更改为下一行；因此我们应该总是创建一个新行：

if (destRow != null) {
  sheet.shiftRows(destination, sheet.getLastRowNum(), 1);
}
destRow = sheet.createRow(destination);
这是精确的解决方案，加上了qwerty答案。谢谢兄弟，它很有帮助。 - Sushant Gosavi
1
我用 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
}

它不复制单元格的值，只复制格式。同样适用于Java。
只需要检查最后一行：if (sheet.getLastRowNum() > rowNum) { sheet.shiftRows - undefined
1

我将其他答案和评论合并到了以下实现中，使用Apache POI v3.9进行测试。

我只有一个rownum参数，因为我向下移动目标行并将其复制到新的空行中。公式按预期处理，它们不是逐字复制的，有一个例外：对于引用在复制行上方的单元格，它们没有更新；解决方法是用INDIRECT()计算出这些显式引用（如果有）所引用的单元格，正如this post所建议的那样。

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

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

0
关于公式在新行中的“更新”，由于所有复制都发生在移位之后，旧行（现在从新行上升一个索引）已经移动了其公式，因此将其复制到新行将使新行引用旧行单元格。解决方案是在移位之前解析出公式，然后应用这些公式（一个简单的字符串数组就可以完成这项工作。我相信你可以用几行代码来实现这个功能）。
在函数开始时：
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);
}

然后将公式应用到单元格中：

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

最近我遇到了同样的问题。我需要在一个有隐藏行的文档中插入新行，和你一样也遇到了相同的问题。经过一番搜索和在Apache POI列表中发送了一些电子邮件后，似乎shiftrows()函数在文档中存在隐藏行时会出现bug。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接