问题描述
在使用easypoi时导出异常报错:cn.afterturn.easypoi.exception.excel.ExcelExportException: Excel导出错误
原因分析:
在导出时使用的是动态层级列,当动态层级列为空时,就会出现此情况。
解决方案:
在使用动态层级列时最好判断一下需要遍历的集合是否为空。
错误完整代码如下:
public void testExport(HttpServletResponse response){
Map<String,String> courseMap1 = new HashMap<>();
courseMap1.put("001","语文");
courseMap1.put("002","数学");
courseMap1.put("003","英语");
courseMap1.put("004","体育");
Map<String,String> courseMap2 = new HashMap<>();
// courseMap2.put("001","语文");
// courseMap2.put("002","数学");
// courseMap2.put("003","英语");
// courseMap2.put("004","体育");
List<ExcelExportEntity> colList = new ArrayList<>();
ExcelExportEntity entity = new ExcelExportEntity("姓名","name");
entity.setNeedMerge(true);
entity.setWidth(15);
colList.add(entity);
entity = new ExcelExportEntity("性别","sex");
entity.setNeedMerge(true);
entity.setWidth(15);
colList.add(entity);
entity = new ExcelExportEntity("年龄","age");
entity.setNeedMerge(true);
entity.setWidth(15);
colList.add(entity);
if(!courseMap1.isEmpty()){
entity = new ExcelExportEntity("科目1","course1");
List<ExcelExportEntity> colCourseList = new ArrayList<>();
courseMap1.entrySet().forEach(course -> {
ExcelExportEntity entityCourse = new ExcelExportEntity(course.getValue(),course.getKey());
entityCourse.setNeedMerge(true);
entityCourse.setWidth(15);
colCourseList.add(entityCourse);
});
entity.setList(colCourseList);
colList.add(entity);
}
if(!courseMap2.isEmpty()){
entity = new ExcelExportEntity("科目2","course2");
List<ExcelExportEntity> colCourseList2 = new ArrayList<>();
courseMap2.entrySet().forEach(course -> {
ExcelExportEntity entityCourse = new ExcelExportEntity(course.getValue(),course.getKey());
entityCourse.setNeedMerge(true);
entityCourse.setWidth(15);
colCourseList2.add(entityCourse);
});
entity.setList(colCourseList2);
colList.add(entity);
}
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
for(int i = 1; i < 5; i++){
Map<String, Object> data = new HashMap<>();
data.put("name","张三"+i);
data.put("sex","男");
data.put("age",i+7);
if(!courseMap1.isEmpty()){
List<Map<String, Object>> courseDataList1 = new ArrayList<Map<String, Object>>();
Map<String, Object> courseDataMap1 = new HashMap<>();
courseMap1.entrySet().forEach(course -> {
courseDataMap1.put(course.getKey(),100);
});
courseDataList1.add(courseDataMap1);
data.put("course1",courseDataList1);
}
if(!courseMap2.isEmpty()){
List<Map<String, Object>> courseDataList2 = new ArrayList<Map<String, Object>>();
Map<String, Object> courseDataMap2 = new HashMap<>();
courseMap2.entrySet().forEach(course -> {
courseDataMap2.put(course.getKey(),100);
});
courseDataList2.add(courseDataMap2);
data.put("course2",courseDataList2);
}
dataList.add(data);
}
//设置文件名、sheet名、表头名
String filename = "学生成绩" + ".xls";
ExportParams exportParams = new ExportParams("成绩", "成绩",
ExcelType.HSSF);
exportParams.setTitleHeight((short) 20);
// 设置导出样式
//导出
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, dataList);
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=" + new String((filename).getBytes("utf-8"), "ISO-8859-1"));
response.addHeader("Cache-Control", "no-cache");
OutputStream out = response.getOutputStream();
try {
workbook.write(out);// 将数据写出去
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}catch (Exception e){
e.printStackTrace();
}
}