如何使用java和apache poi删除excel中的空单元格

Using Apache POI, I am extracting values from an Excel file and writing to another Excel file.

使用Apache POI，我从Excel文件中提取值，并将其写入另一个Excel文件。

However I am getting empty cells in the output Excel file. How do I:

不过，我在输出Excel文件中获得了空单元格。我如何:

detect at the end which rows are empty
在末尾检测哪些行是空的
and then remove those rows?
然后删除这些行?

I have added this part of code to remove rows. I am able to detect the rows which are empty but not able to remove it.

我添加了这部分代码以删除行。我可以检测空行但不能删除它。

Please tell how to implement in the above code I added.

I did by adding this

我加了这个

public class Nlp_health {
private static boolean isRowEmpty;

public static void main(String[] args) throws IOException, ParseException, RowsExceededException, WriteException
{
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Sheet1");
    XSSFSheet sheet2 = wb.createSheet("Sheet2");
    XSSFSheet sheet3 = wb.createSheet("Sheet3");
    XSSFRow row_1 = sheet1.createRow((char)0);
    XSSFRow row_2 = sheet2.createRow((char)0);
    XSSFRow row_3 = sheet3.createRow((char)0);
    row_1.createCell(0).setCellValue("Column1");
    row_1.createCell(1).setCellValue("Column2");

    row_2.createCell(0).setCellValue("Column1");
    row_2.createCell(1).setCellValue("Column2");

    row_3.createCell(0).setCellValue("Column1");
    row_3.createCell(1).setCellValue("Column2");

    FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Master\\Desktop\\Nlp_health3.xlsx");
    wb.write(fileOut);
    fileOut.close();

Xls_Reader datatable = new Xls_Reader("C:\\Users\\Master\\Desktop\\SMB NLP Projects to Update 20140722.xlsx");
Xls_Reader datatable1 = new Xls_Reader("C:\\Users\\Master\\Desktop\\Nlp_health.xlsx");
int rows_count = datatable.getRowCount("Projects View");
System.out.println("Total number of rows = " + rows_count);
System.out.println();
for(int i=4; i<rows_count;i++)
{
    String milestone = datatable.getCellData("Projects View", "SMB Project Health", i);
    String project = datatable.getCellData("Projects View", "Name", i);
{
    //System.out.println(value);
    if (milestone.equals("Yellow")){
        System.out.println("1st step = " + project);

          datatable1.setCellData("Sheet1", "Column1", i, project);
          datatable1.setCellData("Sheet1", "Column2", i, "Yellow");
          //System.out.println(project);
    }
    if(milestone.equals("Red")){
        System.out.println("2nd step = " + project);
        datatable1.setCellData("Sheet1", "Column2", i, "Red");
        datatable1.setCellData("Sheet1", "Column1", i, project);

    }

    }
}

int rows_count_new = datatable1.getRowCount("Sheet1");
System.out.println("Number of rows are" + rows_count_new );
for (int j = 0; j <= rows_count_new; j++){
    String empty_cells = datatable1.getCellData("Sheet1", "Column1", j);
    System.out.println("Empty step1 = " + empty_cells);
     if(sheet1.getRow(j)==null){
            sheet1.shiftRows(j + 1, sheet1.getLastRowNum(), -1);
            j--;
        continue;
        }
        for(int k =0; k<sheet1.getRow(j).getLastCellNum();k++){
            if(sheet1.getRow(j).getCell(k).toString().trim().equals("")){
                isRowEmpty=true;
            }else {
                isRowEmpty=false;
                break;
            }
        }
        if(isRowEmpty==true){
            sheet1.shiftRows(j + 1, sheet1.getLastRowNum(), -1);
            j--;
        }


}





}

}

2 个解决方案

#1

The POI Javadoc clearly documents XSSFSheet#removeRow(), which will remove the entire row from the sheet.

POI Javadoc清楚地记录了XSSFSheet#removeRow()，它将从表中删除整个行。

There's also XSSFRow#removeCell(cell) to remove single cells.

还有XSSFRow#removeCell(cell)来删除单个单元。

#2

Assumption: As per your question it seems that you are worried about blank rows, not about blank cells coming within a row having not all cells blank.

假设:就您的问题而言，您似乎担心的是空行，而不是一个行内的空单元格不是所有的单元格都是空的。

A Blank row can be found in two ways.

可以通过两种方式找到一个空行。

First, the Row is in between the other rows, but never be initialized or created. In this case Sheet.getRow(i) will be null.
首先，行位于其他行之间，但从未被初始化或创建。在这种情况下，Sheet.getRow(i)将为null。
And Second, the Row was created, its cell may or may not get used but now all of its cells are blank. In this case Sheet.getRow(i) will not be null. (you can check it by using Sheet.getRow(i).getLastCellNum() it will always show you the count same as other rows.)
第二，这个行被创建，它的细胞可能被使用，也可能不被使用但是现在所有的细胞都是空白的。在这种情况下，Sheet.getRow(i)将不是null。(您可以使用Sheet.getRow(i). getlastcellnum()检查它，它总是显示与其他行相同的计数。)

In general case the second condition occurs. Perhaps in your case, it should be the reason. For this you need to add additional condition to check whether all the cells are blank or not.

一般情况下会出现第二个条件。也许对你来说，这应该是原因。为此，您需要添加附加条件来检查所有单元格是否为空。

    for(int i = 0; i < sheet.getLastRowNum(); i++){
        if(sheet.getRow(i)==null){
           // condition of blank row so do whatever you want to do in this case
           // for example I have done nothing just removed that row.
            sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            i--;
        continue;
        }
        for(int j =0; j<sheet.getRow(i).getLastCellNum();j++){
            if(sheet.getRow(i).getCell(j).toString().trim().equals("")){
                isRowEmpty=true;
            }else {
                isRowEmpty=false;
                break;
            }
        }
        if(isRowEmpty==true){
         // condition of blank row so do whatever you want to do in this case
         // for example I have done nothing just removed that row.
            sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            i--;
        }
    }

#1