Apache POI如何解析excel中存在公式的单元格,并确认单元格位置
package excelBig;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelTest {
public static void main(String[] args) {
try {
// 指定要读取的Excel文件路径
String excelFilePath = "你的文件路径\测试.xls";
// 创建文件输入流
FileInputStream inputStream = new FileInputStream(excelFilePath);
// 创建工作簿对象(针对.xls格式)
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
// 获取第一个工作表(通常在Excel中,第一个工作表的索引为0)
HSSFSheet sheet = workbook.getSheetAt(0);
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(workbook);
// 迭代遍历每一行
for (Row row : sheet) {
// 迭代遍历每一列
for (Cell cell : row) {
// 获取单元格的数据
CellType cellType = cell.getCellType();
if (cellType == CellType.FORMULA) {
formulaEvaluator.evaluateFormulaCell(cell);
System.err.println("第" + (cell.getRowIndex()+1) + "行");
System.err.println("第" + (cell.getColumnIndex()+1) + "列");
System.err.println("坐标:(" + ( cell.getRowIndex()+1) + "," + (cell.getColumnIndex()+1) + ")");
String formula = cell.getCellFormula();
System.err.println("计算公式为:" + formula);
// 进行计算并拿到值
CellValue value = formulaEvaluator.evaluate(cell);
// 将值转化成字符串
String format = value.formatAsString();
System.err.println("值为:" + format);
System.err.println("-------");
}else if (cellType == CellType.STRING) {
//System.out.print(cell.getStringCellValue() + "\t");
} else if (cellType == CellType.NUMERIC) {
//System.out.print(cell.getNumericCellValue() + "\t");
} else if (cellType == CellType.BOOLEAN) {
//System.out.print(cell.getBooleanCellValue() + "\t");
} else if (cellType == CellType.BLANK) {
// System.out.print("[BLANK]\t");
}
}
//System.out.println(); // 换行
}
// 关闭文件输入流
inputStream.close();
// 关闭工作簿
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}