使用Apache POI HSSF从Excel表中删除一行

I'm using the Apache POi HSSF library to import info into my application. The problem is that the files have some extra/empty rows that need to be removed first before parsing.

我正在使用Apache POi HSSF库将信息导入我的应用程序。问题是，这些文件有一些额外的/空行，需要在解析之前先删除它们。

There's not a HSSFSheet.removeRow( int rowNum ) method. Only removeRow( HSSFRow row ). The problem with this it that empty rows can't be removed. For example:

没有HSSFSheet。removeRow(int rowNum)方法。只有远程(HSSFRow行)。这样做的问题是不能删除空行。例如:

sheet.removeRow( sheet.getRow(rowNum) );

gives a NullPointerException on empty rows because getRow() returns null. Also, as I read on forums, removeRow() only erases the cell contents but the row is still there as an empty row.

给空行一个NullPointerException，因为getRow()返回null。另外，正如我在论坛上看到的，removeRow()只删除单元格内容，但行仍然作为空行存在。

Is there a way of removing rows (empty or not) without creating a whole new sheet without the rows that I want to remove?

是否有一种方法可以删除行(空的或不空的)，而不需要创建一个没有我想要删除的行的新表?

7 个解决方案

#1

 /**
 * Remove a row by its index
 * @param sheet a Excel sheet
 * @param rowIndex a 0 based index of removing row
 */
public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum=sheet.getLastRowNum();
    if(rowIndex>=0&&rowIndex<lastRowNum){
        sheet.shiftRows(rowIndex+1,lastRowNum, -1);
    }
    if(rowIndex==lastRowNum){
        HSSFRow removingRow=sheet.getRow(rowIndex);
        if(removingRow!=null){
            sheet.removeRow(removingRow);
        }
    }
}

#2

I know, this is a 3 year old question, but I had to solve the same problem recently, and I had to do it in C#. And here is the function I'm using with NPOI, .Net 4.0

我知道，这是一个3岁的问题，但我最近必须解决同样的问题，我必须用c#来解决。这是我在NPOI。net 4.0中使用的函数

    public static void DeleteRow(this ISheet sheet, IRow row)
    {
        sheet.RemoveRow(row);   // this only deletes all the cell values

        int rowIndex = row.RowNum;

        int lastRowNum = sheet.LastRowNum;

        if (rowIndex >= 0 && rowIndex < lastRowNum)
        {
            sheet.ShiftRows(rowIndex + 1, lastRowNum, -1);
        }
    }

#3

The HSSFRow has a method called setRowNum(int rowIndex).

HSSFRow有一个名为setRowNum(int rowIndex)的方法。

When you have to "delete" a row, you put that index in a List. Then, when you get to the next row non-empty, you take an index from that list and set it calling setRowNum(), and remove the index from that list. (Or you can use a queue)

当您必须“删除”一行时，您将该索引放入一个列表中。然后，当到达下一行非空时，从该列表中取出一个索引，并将其设置为setRowNum()，并从该列表中删除该索引。(也可以使用队列)

#4

Something along the lines of

类似的东西

int newrownum=0;
for (int i=0; i<=sheet.getLastRowNum(); i++) {
  HSSFRow row=sheet.getRow(i);
  if (row) row.setRowNum(newrownum++);
}

should do the trick.

应该足够了。

#5

My special case (it worked for me):

我的特殊情况(它对我有效):

    //Various times to delete all the rows without units
    for (int j=0;j<7;j++) {
      //Follow all the rows to delete lines without units (and look for the TOTAL row)
      for (int i=1;i<sheet.getLastRowNum();i++) {
        //Starting on the 2nd row, ignoring first one
        row = sheet.getRow(i);
        cell = row.getCell(garMACode);
        if (cell != null) 
        {
          //Ignore empty rows (they have a "." on first column)
          if (cell.getStringCellValue().compareTo(".") != 0) {  
            if (cell.getStringCellValue().compareTo("TOTAL") == 0) {
              cell = row.getCell(garMAUnits+1);
              cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
              cell.setCellFormula("SUM(BB1" + ":BB" + (i - 1) + ")");
            } else {
              cell = row.getCell(garMAUnits);
              if (cell != null) {
                int valor = (int)(cell.getNumericCellValue());
                if (valor == 0 ) {
                  //sheet.removeRow(row);
                  removeRow(sheet,i);
                }
              }
            }
          }
        }
      }
    }

#6

This answer is an extension over AndreAY's answer, Giving you complete function on deleting a row.

这个答案是对AndreAY的答案的扩展，提供了删除一行的完整函数。

public boolean deleteRow(String sheetName, String excelPath, int rowNo) throws IOException {

    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;

    try {
        FileInputStream file = new FileInputStream(new File(excelPath));
        workbook = new XSSFWorkbook(file);
        sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            return false;
        }
        int lastRowNum = sheet.getLastRowNum();
        if (rowNo >= 0 && rowNo < lastRowNum) {
            sheet.shiftRows(rowNo + 1, lastRowNum, -1);
        }
        if (rowNo == lastRowNum) {
            XSSFRow removingRow=sheet.getRow(rowNo);
            if(removingRow != null) {
                sheet.removeRow(removingRow);
            }
        }
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File(excelPath));
        workbook.write(outFile);
        outFile.close();


    } catch(Exception e) {
        throw e;
    } finally {
        if(workbook != null)
            workbook.close();
    }
    return false;
}

#7

I'm trying to reach back into the depths of my brain for my POI-related experience from a year or two ago, but my first question would be: why do the rows need to be removed before parsing? Why don't you just catch the null result from the sheet.getRow(rowNum) call and move on?

我正试图回到我的大脑深处，寻找一两年前与poi相关的经验，但我的第一个问题是:为什么在解析之前需要删除这些行?为什么不直接从sheet.getRow(rowNum)调用获取null结果，然后继续呢?

#1

 /**
 * Remove a row by its index
 * @param sheet a Excel sheet
 * @param rowIndex a 0 based index of removing row
 */
public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum=sheet.getLastRowNum();
    if(rowIndex>=0&&rowIndex<lastRowNum){
        sheet.shiftRows(rowIndex+1,lastRowNum, -1);
    }
    if(rowIndex==lastRowNum){
        HSSFRow removingRow=sheet.getRow(rowIndex);
        if(removingRow!=null){
            sheet.removeRow(removingRow);
        }
    }
}

#2