此案例能够正常解析引用公式的值,以及计算公式的值
@SpringBootTest
class DemoApplicationTests {
@Test
void contextLoads() throws IOException {
// 流读取文件
FileInputStream is = new FileInputStream(new File("C:\\Users\\T011921\\Desktop\\测试引用1.xlsx"));
// 根据流创建文件
Workbook wb = new XSSFWorkbook(is);
// 获取sheet
Sheet sheet = wb.getSheetAt(0);
int totalCells =sheet.getRow(0).getPhysicalNumberOfCells();
List list = new ArrayList();
// 遍历row
for(Row row:sheet){
// 第一行是标题,不需要读取
if(row.getRowNum() < 2){
continue;
}
// 当遇到空行,跳过
Map map = new HashMap();
for (int c =0; c < totalCells; c++) {
Cell cell = row.getCell(c);
String value = getCellValue(cell);
map.put(c,value);
}
list.add(map);
}
System.out.println(list);
}
/**
* 读取单元格内容 包括计算公式的结果,引用公式的结果(引用公式值当前的sheet单元格,引用了另一个Excel文件的内容例:='C:\Users\T011921\Desktop\[测试引用.xlsx]Sheet1'!A3)
* @param cell
* @return
*/
public static String getCellValue(Cell cell){
System.out.println(cell);
String value = null;
if(cell != null){
System.out.println(cell.getCellType());
switch (cell.getCellType()){
case BLANK:
value = "";
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
switch (cell.getCachedFormulaResultType()){
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
value = sdf.format(date);
}else{
BigDecimal n = new BigDecimal(cell.getNumericCellValue());
DecimalFormat decimalFormat = new DecimalFormat("0");
decimalFormat.setMaximumFractionDigits(18);
value = decimalFormat.format(n.doubleValue());
}
break;
case STRING:
value = String.valueOf(cell.getStringCellValue());
if(value != null){
value = value.trim();
}
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR: value = "";
break;
default:
value = cell.getRichStringCellValue().getString();
break;
}
break;
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
value = sdf.format(date);
}else{
BigDecimal n = new BigDecimal(cell.getNumericCellValue());
DecimalFormat decimalFormat = new DecimalFormat("0");
decimalFormat.setMaximumFractionDigits(18);
value = decimalFormat.format(n.doubleValue());
}
break;
case STRING:
value = String.valueOf(cell.getStringCellValue());
if(value != null){
value = value.trim();
}
break;
default:
value = cell.getRichStringCellValue().getString();
break;
}
}
return value;
}
}
所需POM
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>