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. :)
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.
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.
