Question

Java poi 读取Excel文件，为什么只有三行的数据，却读出3800多行？

private List<List<String>> read2(HSSFWorkbook wb) {
List<List<String>> dataLst = new ArrayList<List<String>>();
/** 得到第一个shell */
HSSFSheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
this.totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
if (this.totalRows >= 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 */
for (int r = 0; r < this.totalRows; r++) {
HSSFRow row = sheet.getRow(r);
if (row == null) {
continue;
}
List<String> rowLst = new ArrayList<String>();
/** 循环Excel的列 */
for (int c = 0; c < this.getTotalCells(); c++) {
HSSFCell cell = row.getCell((short)c);
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
cellValue = cell.getNumericCellValue() + "";
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
rowLst.add(cellValue);
}
/** 保存第r行的第c列 */
dataLst.add(rowLst);
}
return dataLst;
}

Answer 1

参考代码
public void getValueOfFormulaCell() throws IOException
{
FileInputStream xlsfile = new FileInputStream(new File("D:\\Desktop\\Temp\\marks.xls"));
HSSFWorkbook objWorkbook = new HSSFWorkbook(xlsfile);
Sheet sheet = objWorkbook.getSheetAt(0);
FormulaEvaluator evaluator = objWorkbook.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B4
CellReference cellReference = new CellReference("B4");
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());

CellValue cellValue = evaluator.evaluate(cell);

switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cellValue.getNumberValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cellValue.getStringValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;

// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA:
break;
}
}