Excel公式计算

背景

数据导入excel利用公式计算再读取出来

实体类

public class TestEntity {
    private BigDecimal num1;
    private BigDecimal num2;
    private BigDecimal num3;
    private BigDecimal num4;
}

模板excel

-- 第一行test4 =A2+B2+C2

-- 第二行test4 =A3*B3+C3

代码

    @Override
    public List<TestEntity> test() throws Exception {
        List<TestEntity> list = new LinkedList<>();
        TestEntity dto1 = new TestEntity();
        dto1.setNum1(new BigDecimal("1"));
        dto1.setNum2(new BigDecimal("2"));
        dto1.setNum3(new BigDecimal("3"));
        list.add(dto1);
        TestEntity dto2 = new TestEntity();
        dto2.setNum1(new BigDecimal("2"));
        dto2.setNum2(new BigDecimal("3"));
        dto2.setNum3(new BigDecimal("4"));
        list.add(dto2);
        List<String> properList = Arrays.asList("num1", "num2", "num3", "num4");
        InputStream is = EduChapterServiceImpl.class.getClassLoader().getResourceAsStream("templates/" + "测试.xlsx");
        XSSFWorkbook workbook;
        try {
            workbook = new XSSFWorkbook(is);
        } catch (IOException e) {
            throw null;
        }
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowNum = 1;
        int columnNum = 0;
        for (int i = rowNum; i < list.size() + rowNum; i++) {
            Row row = sheet.getRow(i);
            for (int j = columnNum; j < properList.size() + columnNum; j++) {
                String column = properList.get(j - columnNum);
                Cell cell = row.getCell(j);
                Field field = null;
                BigDecimal num = null;
                try {
                    field = list.get(i - rowNum).getClass().getDeclaredField(column);
                    ReflectionUtils.makeAccessible(field);
                    num = (BigDecimal) field.get(list.get(i - rowNum));
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (!ObjectUtils.isEmpty(num)) {
                    cell.setCellValue(num.toString());
                }
            }
        }
        //数据清洗
        workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
        readExcel(list, sheet, rowNum, columnNum, properList, list.get(0).getClass());
        return list;
    }
 
    /**
     * 读取excel
     *
     * @param list
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @param properList
     * @param clazz
     * @throws Exception
     */
    public void readExcel(List<?> list, Sheet sheet, int rowNum, int columnNum, List<String> properList, Class<?> clazz) throws Exception {
        for (int i = rowNum; i < list.size() + rowNum; i++) {
            Row row = sheet.getRow(i);
            for (int j = columnNum; j < properList.size() + columnNum; j++) {
                Cell cell = row.getCell(j);
                String column = properList.get(j - columnNum);
                Field field = clazz.getDeclaredField(column);
                ReflectionUtils.makeAccessible(field);
                field.set(list.get(i - rowNum), new BigDecimal(getNumberValue(cell)));
            }
        }
    }
 
    /**
     * 解析单元格
     *
     * @param cell
     * @return
     */
    public String getNumberValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    cellValue = cell.getStringCellValue();
                }
                break;
            case BLANK:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

运行结果