1.代码一
package test;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class test2{
public static void main(String[] args) {
Workbook wb = null;
try {
wb = new HSSFWorkbook(new FileInputStream("E:\\Workspaces\\testdata\\仓库数据.xls"));
} catch (Exception e) {
//
}
Sheet sheet = wb.getSheetAt(0);
CellReference cellReference = new CellReference("A4");
boolean flag = false;
System.out.println("总行数:"+(sheet.getLastRowNum()+1));
for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
Row r = sheet.getRow(i);
if(r == null){
// 如果是空行(即没有任何数据、格式),直接把它以下的数据往上移动
sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
continue;
}
flag = false;
for(Cell c:r){
if(c.getCellType() != Cell.CELL_TYPE_BLANK){
flag = true;
break;
}
}
if(flag){
i++;
continue;
}
else{//如果是空白行(即可能没有数据,但是有一定格式)
if(i == sheet.getLastRowNum())//如果到了最后一行,直接将那一行remove掉
sheet.removeRow(r);
else//如果还没到最后一行,则数据往上移一行
sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
}
}
System.out.println("总行数:"+(sheet.getLastRowNum()+1));
}
}
2. 代码二
public static int getVaildRows(String path) throws IOException, InvalidFormatException {
FileInputStream excelFileInputStream = new FileInputStream(path);
Workbook wb = WorkbookFactory.create(excelFileInputStream);
Sheet sheet = wb.getSheetAt(0);
CellReference cellReference = new CellReference("A4");
boolean flag = false;
for (int i = cellReference.getRow(); i <= sheet.getLastRowNum(); ) {
Row r = sheet.getRow(i);
if (r == null) {
// 如果是空行(即没有任何数据、格式),直接把它以下的数据往上移动
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
continue;
}
flag = false;
for (Cell c : r) {
if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
flag = true;
break;
}
}
if (flag) {
i++;
continue;
} else {//如果是空白行(即可能没有数据,但是有一定格式)
if (i == sheet.getLastRowNum())//如果到了最后一行,直接将那一行remove掉
sheet.removeRow(r);
else//如果还没到最后一行,则数据往上移一行
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
}
return sheet.getLastRowNum() + 1;
}
测试:
public static void main(String[] args) throws IOException, InvalidFormatException {
String path ="E:\\file\\2.0\\sc\\0101010330202\\0101010330202.xlsx";
int rows = getVaildRows(path);
System.out.println("总行数:"+rows);
}