Apache Poi excel 删除空白行 [英] Apache Poi excel remove blank rows

问题描述

我有一个 3000 行的 excel 文件.我删除了 2000(使用 ms excel 应用程序)，但是当我从代码中调用 sheet.getLastRowNum() 时，它给了我 3000(而不是 1000).如何删除空白行?

我尝试了 此处 但它不起作用....

解决方案

有两种方法:

1.) 无代码:将你的excel内容复制粘贴到一个新的excel中，稍后根据需要重命名.

2.) 使用代码(我没有找到它的任何函数，所以我创建了自己的函数):

您需要检查每个单元格是否存在任何类型的空白/空字符串/null 类型的东西.在处理行之前(我期待你正在处理行明智的，我也在使用org.apache.poi.xssf.usermodel.XSSFRow)，放一个if 检查，并在 if(condition) 中检查此方法的返回类型，如果为真，则表示行 (XSSFRow) 具有某些值，否则将迭代器移至下一行

public boolean containsValue(XSSFRow row, int fcell, int lcell){布尔标志 = 假；for (int i = fcell; i 

所以最后你的处理方法看起来像

<预><代码>...int fcell = row.getFirstCellNum();//excel的第一个单元格编号int lcell = row.getLastCellNum();//excel的最后一个单元格编号而 (rows.hasNext()) {row = (XSSFRow) rows.next();//增加行迭代器if(containsValue(row, fcell, lcell) == true){....//加工..}}

希望这会有所帮助.:)

I have an excel file with 3000 rows. I remove the 2000 (with ms excel app), but when i call the sheet.getLastRowNum() from code , it gives me 3000 (instead of 1000).. How can i remove the blank rows?

I tried the code from here but it doesn't works....

解决方案

There are two ways for it:

1.) Without code: Copy the content of your excel and paste it in a new excel, and later rename is as required.

2.) With code(I did not find any functions for it so I created my own function):

You need to check each of the cells for any type of blank/empty string/null kind of things. Before processing the row(I am expecting you are processing row wise also I am using org.apache.poi.xssf.usermodel.XSSFRow), put a if check, and check for this method's return type in the if(condition), if it is true that means the row(XSSFRow) has some value other wise move the iterator to next row

public boolean containsValue(XSSFRow row, int fcell, int lcell) 
{
    boolean flag = false;
    for (int i = fcell; i < lcell; i++) {
    if (StringUtils.isEmpty(String.valueOf(row.getCell(i))) == true || 
        StringUtils.isWhitespace(String.valueOf(row.getCell(i))) == true || 
        StringUtils.isBlank(String.valueOf(row.getCell(i))) == true || 
        String.valueOf(row.getCell(i)).length() == 0 || 
        row.getCell(i) == null) {} 
    else {
                flag = true;
        }
    }
        return flag;
}

So finally your processing method will look like

.
.
.
int fcell = row.getFirstCellNum();// first cell number of excel
int lcell = row.getLastCellNum(); //last cell number of excel
while (rows.hasNext()) {
row = (XSSFRow) rows.next();//increment the row iterator
if(containsValue(row, fcell, lcell) == true){

.
.
..//processing
.
.
}

}

Hope this will help. :)

这篇关于Apache Poi excel 删除空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！