想要效果
1、自定义表头
2、学校根据老师合并单元格,老师根据学生合并单元格。三层嵌套导出
3、生成多个sheet
代码
public static void exportTest(){
List<ExcelExportEntity> entityList = new ArrayList<>();
//自定所有表头
ExcelExportEntity schoolName = new ExcelExportEntity("学校名称", "schoolName");
ExcelExportEntity teacherName = new ExcelExportEntity("老师名称", "teacherName");
ExcelExportEntity subjectName = new ExcelExportEntity("课程", "subjectName");
ExcelExportEntity studentName = new ExcelExportEntity("学生名称", "studentName");
ExcelExportEntity age = new ExcelExportEntity("年龄", "age");
ExcelExportEntity time = new ExcelExportEntity("入学时间", "time");
time.setFormat("yyyy-MM-dd");
time.setWidth(30);
//一对多的数据所以要合并单元格
schoolName.setNeedMerge(true);
teacherName.setNeedMerge(true);
subjectName.setNeedMerge(true);
entityList.add(schoolName);
//老师合并
ExcelExportEntity teacher = new ExcelExportEntity(null, "teacher");
List<ExcelExportEntity> temp = new ArrayList<>();
temp.add(teacherName);
temp.add(subjectName);
teacher.setList(temp);
//学生合并-三层嵌套的sortNum无效,可以先排序再add到temp2,达到sortNum效果
ExcelExportEntity students = new ExcelExportEntity(null, "students");
List<ExcelExportEntity> temp2 = new ArrayList<>();
temp2.add(studentName);
temp2.add(age);
temp2.add(time);
students.setList(temp2);
temp.add(students);
entityList.add(teacher);
//构建数据
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < 2; i++){
Map<String, Object> userEntityMap = new HashMap<>();
userEntityMap.put("schoolName", "学校" + i);
List<Map<String, Object>> dataList2 = new ArrayList<>();
for (int j = 0; j < 2; j++){
Map<String, Object> userEntityMap2 = new HashMap<>();
userEntityMap2.put("teacherName", "老师" + j);
userEntityMap2.put("subjectName", j);
List<Map<String, Object>> dataList3 = new ArrayList<>();
for (int k = 0; k < 3; k++){
Map<String, Object> userEntityMap3 = new HashMap<>();
userEntityMap3.put("studentName", "学生" + k);
userEntityMap3.put("age", k);
userEntityMap3.put("time", new Date(System.currentTimeMillis() + k));
dataList3.add(userEntityMap3);
}
userEntityMap2.put("students", dataList3);
dataList2.add(userEntityMap2);
}
userEntityMap.put("teacher", dataList2);
dataList.add(userEntityMap);
}
//模拟sheet2数据
List<Map<String, Object>> dataList2 = new ArrayList<>(dataList);
//模拟sheet3数据
List<Map<String, Object>> dataList3 = new ArrayList<>(dataList);
//sheet1
ExportParams params1 = new ExportParams();
params1.setType(ExcelType.HSSF);
params1.setSheetName("用户1");
params1.setTitle("信息表1");
Workbook workbook = ExcelExportUtil.exportExcel(params1, entityList, dataList);
//sheet2
ExportParams params2 = new ExportParams();
params2.setType(ExcelType.HSSF);
params2.setSheetName("用户2");
params2.setTitle("信息表2");
new ExcelExportService().createSheetForMap(workbook, params2, entityList, dataList2);
//sheet3
ExportParams params3 = new ExportParams();
params3.setType(ExcelType.HSSF);
params3.setSheetName("用户3");
params3.setTitle("信息表3");
new ExcelExportService().createSheetForMap(workbook, params3, entityList, dataList3);
try {
FileOutputStream fos = new FileOutputStream("C:\\Users\\u\\Desktop\\合并导出测试.xls");
workbook.write(fos);
fos.close();
}catch (Exception e){
e.printStackTrace();
}
}
导出效果
总结
1、三层嵌套的width、sortNum设置没有效果,需要自己调整样式
width调整:
参考:java用POI设置Excel的列宽_sheet.setcolumnwidth-CSDN博客
//遍历每个sheet
Sheet sheetAt = workbook.getSheetAt(0);
//设置第一列长度是30
sheetAt.setColumnWidth(0, 30 * 256 + 184);