easyPoi 曲线实现导出excel含公式
项目中需要实现导出excel并包含公式函数。图简便使用了easypoi的模板导出数据,然后再执行函数。废话不多说直接上代码
1、定义excel模板
因easyPoi TemplateExportParams模板不支持excel单元格中含有公式,要么是占位符要么赋值。
2、加载模板并赋值
简单样例模板
- 使用TemplateExportParams读取excel
TemplateExportParams params = new TemplateExportParams("static/file/example.xlsx");
// 标题开始行
params.setHeadingStartRow(0);
// 标题行数
params.setHeadingRows(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;
- 组装公式
/**
* 组装公式
*
* @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);
}
}```