java Apache Poi excel 删除空白行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17944564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apache Poi excel remove blank rows
提问by user1005633
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?
我有一个 3000 行的 excel 文件。我删除了 2000（使用 ms excel 应用程序），但是当我从代码中调用 sheet.getLastRowNum() 时，它给了我 3000（而不是 1000）。如何删除空白行？
I tried the code from herebut it doesn't works....
我从这里尝试了代码，但它不起作用....
回答by Mukesh S
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.
1.)无代码：复制你的excel内容并粘贴到一个新的excel中，稍后根据需要重命名。
2.) With code(I did not find any functions for it so I created my own function):
2.)使用代码（我没有找到它的任何函数，所以我创建了自己的函数）：
You need to check each of the cells for any type of blank/empty string/nullkind 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 ifcheck, 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
您需要检查每个单元格是否有任何类型的空白/空字符串/空类型的东西。在处理行之前（我希望你正在处理行，我也在使用org.apache.poi.xssf.usermodel.XSSFRow），放置一个if检查，并在if(condition) 中检查这个方法的返回类型，如果确实，这意味着行（XSSFRow）有一些值，否则将迭代器移动到下一行
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. :)
希望这会有所帮助。:)
回答by Lenymm
I haven't found any solution on how to easily get the "real" number of rows but I've found a solution to remove such rows which might be useful to someone who's tackling similar issue. See bellow.
我还没有找到任何关于如何轻松获取“真实”行数的解决方案，但我找到了一种删除此类行的解决方案，这可能对解决类似问题的人有用。见下文。
I've searched a bit and found this solution
我搜索了一下，找到了这个解决方案
All it does is it deletes those empty rows from the bottom which might be exactly what you want.
它所做的只是从底部删除那些可能正是您想要的空行。
回答by Sankumarsingh
As per my understanding for deleting rows you Must have selected all the cells and pressed Delete button. If I am right then you have deleted the rows by wrong way. By this way the cells become blank not deleted so the rows actually contain cells with blank values and that is why get included in the row count.
根据我对删除行的理解，您必须选择所有单元格并按下删除按钮。如果我是对的，那么您以错误的方式删除了行。通过这种方式，单元格变为空白而不是删除，因此行实际上包含具有空白值的单元格，这就是为什么要包含在行数中的原因。
The correct way to do this is select the row from the left of its first cell where row numbers are appearing. Clicking there on row numbers will select the complete row. Select all required rows with the help of shift key. Now right click and then select delete.
正确的方法是从出现行号的第一个单元格的左侧选择行。单击行号将选择完整的行。在 shift 键的帮助下选择所有需要的行。现在右键单击，然后选择删除。
回答by kylin
- remove rows/columns by poi api
- transfer xls to csv
- transfer csv to xls hope this will help you
- 通过 poi api 删除行/列
- 将 xls 传输到 csv
- 将 csv 传输到 xls 希望这对您有所帮助
