Description of problem:
I just sent a task and said that I want to export, and the export is required to be multi-sheet and complex headers. The most commonly used method of easyPoi is MVC export. There is also a key-value pair method in the official document (http://easypoi.mydoc.io /#category_50222) to find key clues
Analysis
`According to the previous writing method as follows:
1. First design the table header as follows
//Design header
List<ExcelExportEntity> colList = new ArrayList<>();
ExcelExportEntity colEntity = new ExcelExportEntity("Name", "studentName");
colEntity.setNeedMerge(true);
colEntity.setWidth(20);
colList. add(colEntity);
colEntity = new ExcelExportEntity("Grade", "graderName");
colEntity.setNeedMerge(true);
colEntity.setWidth(15);
colList. add(colEntity);
colEntity = new ExcelExportEntity("class", "className");
colEntity.setNeedMerge(true);
colEntity.setWidth(15);
colList. add(colEntity);
colEntity = new ExcelExportEntity("course", "course");
// multi-level header
List<ExcelExportEntity> entityList = new ArrayList<>();
ExcelExportEntity entity = new ExcelExportEntity("Chinese", "course1");
entity.setNeedMerge(true);
entity.setWidth(15);
entityList. add(entity);
entity = new ExcelExportEntity("Mathematics", "course2");
entity.setNeedMerge(true);
entity.setWidth(15);
entityList. add(entity);
entity = new ExcelExportEntity("English", "course3");
entity.setNeedMerge(true);
entity.setWidth(15);
entityList. add(entity);
colEntity.setList(entityList);
colList. add(colEntity);
colEntity = new ExcelExportEntity("school", "schoolName");
colEntity.setNeedMerge(true);
colEntity.setWidth(15);
colList. add(colEntity);
2. Encapsulate the data into the map. It should be noted that the written data needs to add an additional layer of List<Map> to package the main data sets in the sheet: title, entityList, date (Note: Is the name fixed? , but it should correspond to the key value when fetching in the exportExcel() method)
List<Map<String, Object>> sheetsList = new ArrayList<>();
for(String sheetName : sheetList){<!-- -->
List<Map<String, Object>> dataList = new ArrayList<>();
for(StuInfo stu : studentInfoList){<!-- -->
// row data
Map<String, Object> valMap = new HashMap<>(5);
valMap.put("studentName", stu.getStudentName());
valMap.put("graderName", stu.getGradeName());
valMap.put("className", stu.getClassName());
valMap.put("schoolName", stu.getStudentName());
//Multi-level table header corresponds to row data
List<Map<String, Object>> maps = new ArrayList<>();
Map<String, Object> map = new HashMap<>();
map.put("course1",scoreMap.get("score1"));
map.put("course2",scoreMap.get("score2"));
map.put("course3",scoreMap.get("score3"));
maps. add(map);
valMap.put("course", maps);
dataList.add(valMap);
}
Map<String, Object> sheetExportMap = new HashMap<>();
//sheet name, content, content title
ExportParams sheetExportParams = new ExportParams("School student information title", sheetName, ExcelType.HSSF);
sheetExportParams.setTitleHeight((short) 20);
sheetExportParams.setStyle(ExcelStyleUtil.class);
//title sets the sheet name and the title of the first line
sheetExportMap.put("title", sheetExportParams);
//Export the header of the table design
sheetExportMap.put("entityList", colList);
//Export data list<map> format
sheetExportMap.put("data", dataList);
sheetsList. add(sheetExportMap);
}
3. Key important points Export the createSheetWithList method to store the header data in
Workbook workbook = MyExcelExportService. exportExport(sheetsList);
According to the official documentation, rewrite the call to exportExcel() and createSheet()
public class MyExcelExportService {<!-- -->
private static final Logger LOGGER = LoggerFactory. getLogger(ExportBase. class);
public static Workbook exportExcel(List<Map<String, Object>> list) {<!-- -->
Workbook workbook = new HSSFWorkbook();
for (Map<String, Object> map : list) {<!-- -->
MyExcelExportService service = new MyExcelExportService();
service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class, (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get ("data"));
}
return workbook;
}
public void createSheetWithList(Workbook workbook, ExportParams entity, Class<?> pojoClass, List<ExcelExportEntity> entityList, Collection<?> dataSet) {<!-- -->
if (LOGGER.isDebugEnabled()) {<!-- -->
LOGGER.debug("Excel export start ,class is {}", pojoClass);
LOGGER.debug("Excel version is {}",
entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
}
if (workbook == null || entity == null || pojoClass == null || dataSet == null) {<!-- -->
throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
}
try {<!-- -->
//emphasis here
//The source code is List<ExcelExportEntity> excelParams = new ArrayList();
//We want dynamic headers and columns, so we need to insert the designed headers
List<ExcelExportEntity> excelParams = entityList;
Field[] fileds = PoiPublicUtil. getClassFields(pojoClass);
ExcelTarget etarget = pojoClass. getAnnotation(ExcelTarget. class);
String targetId = etarget == null ? null : etarget. value();
//Once all the parameters are obtained, the following logical judgments are consistent
ExcelExportServer excelExportServer = new ExcelExportServer();
excelExportServer.getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null);
excelExportServer.createSheetForMap(workbook, entity, excelParams, dataSet);
} catch (Exception e) {<!-- -->
LOGGER. error(e. getMessage(), e);
throw new ExcelExportException(ExcelExportEnum. EXPORT_ERROR, e. getCause());
}
}
}
Result display:
The complete code is as follows:
public void getTestExport(HttpServletResponse response) {<!-- -->
//test sheet
List<String> sheetList = new ArrayList<>();
sheetList.add("test sheet1");
sheetList.add("test sheet2");
sheetList.add("test sheet3");
// test data content
Map<String,String> scoreMap = new HashMap<>();
scoreMap.put("score1", "100");
scoreMap.put("score2", "100");
scoreMap.put("score3", "100");
List<StuInfo> studentInfoList = new ArrayList<>();
StuInfo info = new StuInfo();
info.setStudentName("Zhang San");
info.setGradeName("Grade 1");
info.setClassName("Class One");
info.setSchoolName("xxx school");
studentInfoList. add(info);
info = new StuInfo();
info.setStudentName("Li Si");
info.setGradeName("Second Grade");
info.setClassName("Class One");
info.setSchoolName("xxx school");
studentInfoList. add(info);
info = new StuInfo();
info.setStudentName("Wang Wu");
info.setGradeName("Grade 3");
info.setClassName("Class One");
info.setSchoolName("xxx school");
studentInfoList. add(info);
// design header
List<ExcelExportEntity> colList = new ArrayList<>();
ExcelExportEntity colEntity = new ExcelExportEntity("Name", "studentName");
colEntity.setNeedMerge(true);
colEntity.setWidth(20);
colList. add(colEntity);
colEntity = new ExcelExportEntity("Grade", "graderName");
colEntity.setNeedMerge(true);
colEntity.setWidth(15);
colList. add(colEntity);
colEntity = new ExcelExportEntity("class", "className");
colEntity.setNeedMerge(true);
colEntity.setWidth(15);
colList. add(colEntity);
colEntity = new ExcelExportEntity("course", "course");
// multi-level header
List<ExcelExportEntity> entityList = new ArrayList<>();
ExcelExportEntity entity = new ExcelExportEntity("Chinese", "course1");
entity.setNeedMerge(true);
entity.setWidth(15);
entityList. add(entity);
entity = new ExcelExportEntity("Mathematics", "course2");
entity.setNeedMerge(true);
entity.setWidth(15);
entityList. add(entity);
entity = new ExcelExportEntity("English", "course3");
entity.setNeedMerge(true);
entity.setWidth(15);
entityList. add(entity);
colEntity.setList(entityList);
colList. add(colEntity);
colEntity = new ExcelExportEntity("school", "schoolName");
colEntity.setNeedMerge(true);
colEntity.setWidth(15);
colList. add(colEntity);
\t\t
List<Map<String, Object>> sheetsList = new ArrayList<>();
for(String sheetName : sheetList){<!-- -->
List<Map<String, Object>> dataList = new ArrayList<>();
for(StuInfo stu : studentInfoList){<!-- -->
// row data
Map<String, Object> valMap = new HashMap<>(5);
valMap.put("studentName", stu.getStudentName());
valMap.put("graderName", stu.getGradeName());
valMap.put("className", stu.getClassName());
valMap.put("schoolName", stu.getSchoolName());
//Multi-level table header corresponds to row data
List<Map<String, Object>> maps = new ArrayList<>();
Map<String, Object> map = new HashMap<>();
map.put("course1",scoreMap.get("score1"));
map.put("course2",scoreMap.get("score2"));
map.put("course3",scoreMap.get("score3"));
maps. add(map);
valMap.put("course", maps);
dataList.add(valMap);
}
Map<String, Object> sheetExportMap = new HashMap<>();
//sheet name, content, content title
ExportParams sheetExportParams = new ExportParams("School student information title", sheetName, ExcelType.HSSF);
sheetExportParams.setTitleHeight((short) 20);
sheetExportParams.setStyle(ExcelStyleUtil.class);
sheetExportMap.put("title", sheetExportParams);
sheetExportMap.put("entityList", colList);
sheetExportMap.put("data", dataList);
sheetsList. add(sheetExportMap);
}
String filename = "Student Information" + ".xls";
//export table
Workbook workbook = MyExcelExportService. exportExcel(sheetsList);
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);// write the data out
} catch (Exception e) {<!-- -->
e.printStackTrace();
} finally {<!-- -->
out. close();
}
}catch (Exception e){<!-- -->
e.printStackTrace();
}
}