问题描述
1、项目中需要多shee导出,需要动态生成列。
2、我的方法是在执行ExcelExportUtil.exportExcel之后,插入自定义的列。
3、发现在执行ExcelExportUtil.exportExcel时传入的list,在下边用的时候变成了空数组
4、最后查看源码发现在执行exportExcel时会删除源数据
1、EasyPoi 导出excel 会删除的数据源
导出excel的时候发现传入的数据源,在执行过ExcelExportUtil.exportExcel之后会变成空list
最后查看源码发现EasyPoi中ExcelExportService里边的remove
解决办法是通过源数据重新生成新数据传入
方法中Entity是你自己的映射类
// 使用ObjectMapper(Entity是你自己的映射类)
ObjectMapper mapper = new ObjectMapper();
String str = mapper.writeValueAsString(data);
JavaType javaType =
mapper.getTypeFactory().constructParametricType(List.class, Entity.class);
List<Entity> excelData = mapper.readValue(str, javaType);
// 使用JSON序列化和反序列化
List<Entity> excelData =
JSON.parseArray(JSON.toJSONString(data), Entity.class);
2、下边是动态写入cell的主要代码
生成cell,在默认列后插入生成的cell
// 获取默认数据的列数
private int getCellNum(Sheet sheet) {
Iterator<Cell> cellIterator = sheet.getRow(0).cellIterator();
int cellAllNum = 1;
while (cellIterator.hasNext()) {
Cell next = cellIterator.next();
String cellValue = next.getStringCellValue();
if (StringUtils.isNotBlank(cellValue)) {
cellAllNum += 1;
}
}
return cellAllNum;
}
// 在默认列后插入自定义的cell
public void insertCell(Workbook workbook, Exam exam, List<Entity> data) {
// 获取第二个sheet
Sheet sheet = workbook.getSheetAt(1);
CellStyle cellStyle = workbook.createCellStyle();
// 设置水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 获取sheet的列数
int cellNum = this.getCellNum(sheet);
// getExcelHeaderList自己生成动态表头的逻辑
List<String> resultHeaderList = this.getExcelHeaderList();
for (int i = 0; i < resultHeaderList.size(); i++) {
// 生成表头
Cell cell = sheet.getRow(0).createCell(cellNum + i);
cell.setCellStyle(cellStyle);
cell.setCellValue(String.format("%s、%s", i + 1, resultHeaderList.get(i)));
}
for (int i = 0; i < data.size(); i++) {
Row row = sheet.getRow(i + 1);
Map<String, Float> examItemMap = data.get(i).getExamItemMap();
for (int j = 0; j < resultHeaderList.size(); j++) {
sheet.setColumnWidth(cellNum + j, 256 * 20);
Cell cell = row.createCell(cellNum + j);
cell.setCellStyle(cellStyle);
cell.setCellValue(你自己的数据);
}
}
}
生成sheet
public Map<String, Object> getExcelListMap(String sheetName, Class<?> clazz, List<?> list) {
Map<String, Object> map = new HashMap<>();
ExportParams sheet = new ExportParams();
sheet.setSheetName(sheetName);
map.put("title", sheet);
map.put("entity", clazz);
map.put("data", list);
return map;
}
执行导出
public void export(HttpServletResponse response, @PathVariable Long id) throws IOException {
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("excel名称.xls", "UTF-8"));
// 添加sheet
List<Map<String, Object>> excelList = new ArrayList<>();
excelList.add(this.getExcelListMap("成绩分析", Entity1.class, data1));
excelList.add(this.getExcelListMap("考试明细", Entity1.class, data2));
// 导出
Workbook workbook = ExcelExportUtil.exportExcel(excelList, ExcelType.HSSF);
// 动态修改cell数据
insertCell(workbook, exam, data);
workbook.write(response.getOutputStream());
workbook.close();
}