使用Java编程删除Excel行 [英] Delete Excel rows programatically using Java

问题描述

查找最后一个列单元格值。如果单元格值为无效或不适用，请删除整行。

Finding last column cell values. if cell value is "Not Valid" or "Not Applicable", delete the entire row.

到目前为止我写的代码如下：

The code I have written so far is as follows:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;

 public class Column
 {
 public static void main(String[] args) throws InvalidFormatException, FileNotFoundException, IOException
  {
    try
    {
    Workbook wb = WorkbookFactory.create(new FileInputStream("C:/Users/Excel1.xlsx"));
    Sheet sheet = wb.getSheet("Retail-All");

    Workbook wb2 = new HSSFWorkbook();
    wb2 = wb;

    Row row;
    row = sheet.getRow(0);
    int getLastCell=row.getLastCellNum()-1;
    int lastIndex = sheet.getLastRowNum();

    for (int i=0; i<=lastIndex; i++)
    {
      row=sheet.getRow(i);
      if(row.getCell(getLastCell)!=null && (row.getCell(getLastCell).toString().equalsIgnoreCase("Not valid) || row.getCell(getLastCell).toString().equalsIgnoreCase("Not Applicable")))
          {
           sheet.removeRow(row);
           //sheet.shiftRows(i, lastIndex, -1);
        }
    }
    FileOutputStream fileOut = new FileOutputStream("C:/Users/shiftRows.xlsx");
    wb2.write(fileOut);
    fileOut.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

}

使用上面的代码，使用row.setZeroHeight（true）;方法我可以隐藏

With the above code, using row.setZeroHeight(true); method I can able to hide the rows.

使用上面的代码，使用sheet.removeRow（row）;方法我可以清空该行中的所有单元格。

With the above code, using sheet.removeRow(row); method I can able to empty all the cells in that particular row.

我在网上找到了一些代码，但是没有一个是永久删除行。我的要求是永久删除行如何o代码以满足我的要求？

I found some code in net, but none of them are deleting the rows permanently.My requirement is to delete the rows permanently. How to code to meet my requirement?

推荐答案

而不是 sheet.removeRow（row）; 你可以使用 shiftRows（int startRow，int endRow，int n）指令，这似乎已经尝试了，它们在startRow和endRow之间移动n个行。

Instead of sheet.removeRow(row); you can use the shiftRows(int startRow, int endRow, int n) instruction, that seems you have tried, that shifts rows between startRow and endRow n number of rows.

在您的情况下，将

// ... code before    ...
for (int i=0; i<=lastIndex; i++)
        {
          row=sheet.getRow(i);
          if(row.getCell(getLastCell)!=null && (row.getCell(getLastCell).toString().equalsIgnoreCase("Not valid") || row.getCell(getLastCell).toString().equalsIgnoreCase("Not Applicable")))
              {
               // This should shift up by 1 row all the rows below
               sheet.shiftRows(i+1, lastIndex, -1);
               i--; // Since you move row at i+1 to i you have to recompute the i-th row
            }
        }
// ... code after ...

检查 org.apache.poi.hssf.usermodel.HSSFSheet文档了解更多信息

这篇关于使用Java编程删除Excel行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！