easyPoi 曲线实现导出excel含公式

项目中需要实现导出excel并包含公式函数。图简便使用了easypoi的模板导出数据，然后再执行函数。废话不多说直接上代码

1、定义excel模板

因easyPoi TemplateExportParams模板不支持excel单元格中含有公式，要么是占位符要么赋值。

2、加载模板并赋值

简单样例模板

1. 使用TemplateExportParams读取excel

 TemplateExportParams params = new TemplateExportParams("static/file/example.xlsx");
        // 标题开始行
        params.setHeadingStartRow(0);
        // 标题行数
        params.setHeadingRows(2);

2. 封装数据

 private List<Map<String, Object>> buildData(List<Object> datas) {
        List<Map<String, Object>> maps = new ArrayList<>();
        int i = 3;
        for (AccountExtendInfo item : datas) {
   
            Map map = BeanToMap.convert(item, new String[]{"expirationDate", "balance", "accountDays",
                    "endCreditAmount", "overdue", "overdueDays", "badDebtHalfAmount"});
            map.putAll(buildEvaluatorMap(i));
            maps.add(map);
        }
        return maps;

3. 组装公式

 /**
     * 组装公式
     *
     * @param i
     * @return
     */
    private Map<String, String> buildEvaluatorMap(int i) {
        Map<String, String> map = new HashMap();
        map.put("expirationDate", "=DATE(YEAR(K" + i + "),MONTH(K" + i + ")+(DAY(K" + i + ")>=2),10)");
        map.put("balance", "=N" + i + "-P" + i + "-R" + i + "");
        map.put("accountDays", "=IF(T" + i + "=0,\"\",(TODAY()-K" + i + "))");
        map.put("endCreditAmount", "=I" + i + "-T" + i);
        map.put("overdue", "=IF(T" + i + "=0,\"N\",IF((TODAY()-S" + i + ")>0,\"Y\",\"N\"))");
        map.put("overdueDays", "=IF(T" + i + "=0,\"\",IF((TODAY()-S" + i + ")>0,TODAY()-S" + i + ",\"\"))");
        map.put("badDebtHalfAmount", "=IF(X" + i + "<=30,T" + i + "/2,\"\")");
        map.put("badDebtAmount", "=IF(X" + i + "=\"\",\"\",IF(X" + i + ">30,T" + i + ",\"\"))");
        
        return map;
    }```

 4. 执行单元格中的公式，并导出；
 

```java
 * 执行单元格公式
     */
    private void exportExcel(TemplateExportParams params, List<Map<String, Object>> reportBodyList, HttpServletResponse response, String fileName) {

        Map<String, Object> data = new HashMap<>();
        data.put("list", reportBodyList);

        Workbook workbook = ExcelExportUtil.exportExcel(params, data);
        workbook.setForceFormulaRecalculation(true);
       
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                for (Row r : sheet) {
                    for (Cell c : r) {
                        if (c != null) {
                            String cell = c.getStringCellValue();
                            if (cell.indexOf("=") == 0) {
                                c.setCellFormula(cell.substring(1));
                                evaluator.evaluate(c);
                            }
                        }
                    }
                }
            
        }

        ExcelUtil.setBrowser(response, workbook, fileName);
    }```

 6. 浏览器写出excel；
 

```java
 /**
     * 写出excel
     *
     * @param response
     * @param workbook
     * @param fileName
     */
    public static void setBrowser(HttpServletResponse response, Workbook workbook, String fileName) {
        try {
            OutputStream out = response.getOutputStream();
            response.reset();
            response.setContentType("application/x-msdownload");
            if (fileName != null) {
                fileName = fileName.endsWith(EXCEL_2007) ? fileName : fileName.endsWith(EXCEL_2003) ? fileName : (fileName + EXCEL_2007);
                fileName = new String(fileName.getBytes("utf-8"), "utf-8");
                response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
            } else {
                response.setHeader("Content-disposition", "attachment; filename=" + "exportExcel" + ".xls");
            }
            workbook.write(out);
            out.close();
            log.info("下载成功！");
        } catch (Exception e) {
            log.error("下载失败！{}",e);
            
        }

    }```