easy poi 动态表头导出 excel 多sheet页

<div class="cnblogs_Highlighter"><pre class="brush:csharp;gutter:true;">

public void exportExcel(HttpServletResponse response, DataParams dataParams) throws IOException {
List<TriduumVo> triduum = null;
List<WhiteVo> whiteVos = null;
if (dataParams.getType().equals("DAY")) {
triduum = this.getOperationByTriduum(dataParams);
whiteVos = this.getWhiteByTriduum(dataParams);
} else {
triduum = this.getOperationByMarch(dataParams);
whiteVos = this.getWhiteByMarch(dataParams);
}
try {
List<Map<String, Object>> sheetsList = new ArrayList<>();
Map<String, Object> singleMap = getSingleMap(triduum, dataParams);
Map<String, Object> multipleMap = getMultipleMap(whiteVos, dataParams);
sheetsList.add(singleMap);
sheetsList.add(multipleMap);

//Workbook workbook = ExcelExportUtil.exportExcel(params, exportList, list);
Workbook workbook = ExcelService.exportExcel(sheetsList);
// 设置响应头
response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("dome.xlsx", "utf-8"));
// 写入到流中
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
workbook.close();
outputStream.close();
} catch (IOException e) {
response.setHeader("Content-type", "text/html;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
PrintWriter pw = response.getWriter();
pw.write("导出文件异常！");

}
}


public Map<String, Object> getSingleMap(List<TriduumVo> paramList, DataParams dataParams) {
// exportList为表头总的集合，导出的EXCEL表格的表头完全是按照这个来生成的2023-07-07
List<String> lastDayList = new ArrayList<>();
List<String> lastMonthList = new ArrayList<>();
if (dataParams.getType().equals("DAY")) {
List<String> lastDays = CountDate.getLastDays(dataParams.getDateTimes(), 3);
for (String newDay : lastDays) {
String substring = newDay.substring(5) + "日";
lastDayList.add(substring.replace("-", "月"));
}

} else {
List<String> lastMonths = CountDate.getLastMonths(dataParams.getDateTimes(), 3);
for (String newMonth : lastMonths) {
String substring = newMonth.substring(newMonth.length() - 2) + "月";
lastMonthList.add(substring);
}
}

List<ExcelExportEntity> exportList = new ArrayList<>();
// 创建最底部的一级表头10个
ExcelExportEntity S1 = new ExcelExportEntity("名称", "s1");
ExcelExportEntity A1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "a1");
ExcelExportEntity A2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "a2");
ExcelExportEntity A3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "a3");
ExcelExportEntity B1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "b1");
ExcelExportEntity B2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "b2");
ExcelExportEntity B3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "b3");
ExcelExportEntity C1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "c1");
ExcelExportEntity C2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "c2");
ExcelExportEntity C3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "c3");
ExcelExportEntity D1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "d1");
ExcelExportEntity D2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "d2");
ExcelExportEntity D3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "d3");
ExcelExportEntity E1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "e1");
ExcelExportEntity E2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "e2");
ExcelExportEntity E3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "e3");
// 创建二级表头，并将二级表头对应的下级一级表头放入其中，以此类推...
ExcelExportEntity S = new ExcelExportEntity("", "s");
S.setList(Arrays.asList(S1));
S.setWidth(25);
ExcelExportEntity A = new ExcelExportEntity("名称", "a");
A.setList(Arrays.asList(A1, A2, A3));
A.setWidth(15);
ExcelExportEntity B = new ExcelExportEntity("数据1", "b");
B.setList(Arrays.asList(B1, B2, B3));
B.setWidth(15);
ExcelExportEntity C = new ExcelExportEntity("数据2", "c");
C.setList(Arrays.asList(C1, C2, C3));
C.setWidth(15);
ExcelExportEntity D = new ExcelExportEntity("数据3)", "d");
D.setList(Arrays.asList(D1, D2, D3));
D.setWidth(15);
ExcelExportEntity E = new ExcelExportEntity("数据4", "e");
E.setList(Arrays.asList(E1, E2, E3));
E.setWidth(15);
//封装
exportList.add(S);
exportList.add(A);
exportList.add(B);
exportList.add(C);
exportList.add(D);
exportList.add(E);


// 数据绑定
List<Map<String, Object>> list = new ArrayList<>();
for (TriduumVo dataParam : paramList) {
Map<String, Object> hand = new HashMap<>();
Map<String, Object> s = new HashMap<>();
Map<String, Object> a = new HashMap<>();
Map<String, Object> b = new HashMap<>();
Map<String, Object> c = new HashMap<>();
Map<String, Object> d = new HashMap<>();
Map<String, Object> e = new HashMap<>();
s.put("s1", dataParam.getEquipName());
a.put("a1", dataParam.getRunningDura3());
a.put("a2", dataParam.getRunningDura2());
a.put("a3", dataParam.getRunningDura1());

b.put("b1", dataParam.getTaskDura3());
b.put("b2", dataParam.getTaskDura2());
b.put("b3", dataParam.getTaskDura1());

c.put("c1", dataParam.getWorkDura3());
c.put("c2", dataParam.getWorkDura2());
c.put("c3", dataParam.getWorkDura1());

d.put("d1", dataParam.getOeeRatio3());
d.put("d2", dataParam.getOeeRatio2());
d.put("d3", dataParam.getOeeRatio1());

e.put("e1", dataParam.getEffecUseRatio3());
e.put("e2", dataParam.getEffecUseRatio2());
e.put("e3", dataParam.getEffecUseRatio1());

hand.put("s", Arrays.asList(s));
hand.put("a", Arrays.asList(a));
hand.put("b", Arrays.asList(b));
hand.put("c", Arrays.asList(c));
hand.put("d", Arrays.asList(d));
hand.put("e", Arrays.asList(e));
//封装
list.add(hand);

}

Map<String, Object> sheetExportMap = new HashMap<>();
//sheet名称、内容、内容标题
ExportParams sheetExportParams = new ExportParams("统计", "统计", ExcelType.HSSF);

//title 设置的是sheet名称和第一行的标题
sheetExportMap.put("title", sheetExportParams);
//导出表设计的表头
sheetExportMap.put("entityList", exportList);
//导出数据list<map>格式
sheetExportMap.put("data", list);

return sheetExportMap;

}


public Map<String, Object> getMultipleMap(List<WhiteVo> paramList, DataParams dataParams) {
// exportList为表头总的集合，导出的EXCEL表格的表头完全是按照这个来生成的2023-07-07
List<String> lastDayList = new ArrayList<>();
List<String> lastMonthList = new ArrayList<>();
if (dataParams.getType().equals("DAY")) {
List<String> lastDays = CountDate.getLastDays(dataParams.getDateTimes(), 3);
for (String newDay : lastDays) {
String substring = newDay.substring(5) + "日";
lastDayList.add(substring.replace("-", "月"));
}

} else {
List<String> lastMonths = CountDate.getLastMonths(dataParams.getDateTimes(), 3);
for (String newMonth : lastMonths) {
String substring = newMonth.substring(newMonth.length() - 2) + "月";
lastMonthList.add(substring);
}
}

List<ExcelExportEntity> exportList = new ArrayList<>();
// 创建最底部的一级表头10个
ExcelExportEntity S1 = new ExcelExportEntity("设备/工位", "s1");
ExcelExportEntity A1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "a1");
ExcelExportEntity A2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "a2");
ExcelExportEntity A3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "a3");
ExcelExportEntity B1 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(0) : lastMonthList.get(0), "b1");
ExcelExportEntity B2 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(1) : lastMonthList.get(1), "b2");
ExcelExportEntity B3 = new ExcelExportEntity(dataParams.getType().equals("DAY") ? lastDayList.get(2) : lastMonthList.get(2), "b3");
// 创建二级表头，并将二级表头对应的下级一级表头放入其中，以此类推...
ExcelExportEntity S = new ExcelExportEntity("", "s");
S.setList(Arrays.asList(S1));
S.setWidth(50);
ExcelExportEntity A = new ExcelExportEntity("合计1", "a");
A.setList(Arrays.asList(A1, A2, A3));
A.setWidth(50);
ExcelExportEntity B = new ExcelExportEntity("合计2", "b");
B.setList(Arrays.asList(B1, B2, B3));
B.setWidth(50);
//封装
exportList.add(S);
exportList.add(A);
exportList.add(B);

// 数据绑定
List<Map<String, Object>> list = new ArrayList<>();
for (WhiteVo dataParam : paramList) {
Map<String, Object> hand = new HashMap<>();
Map<String, Object> s = new HashMap<>();
Map<String, Object> a = new HashMap<>();
Map<String, Object> b = new HashMap<>();
s.put("s1", dataParam.getAcrossName());
a.put("a1", dataParam.getDayWorkDura3());
a.put("a2", dataParam.getDayWorkDura2());
a.put("a3", dataParam.getDayWorkDura1());

b.put("b1", dataParam.getNightWorkDura3());
b.put("b2", dataParam.getNightWorkDura2());
b.put("b3", dataParam.getNightWorkDura1());

hand.put("s", Arrays.asList(s));
hand.put("a", Arrays.asList(a));
hand.put("b", Arrays.asList(b));
//封装
list.add(hand);

}

Map<String, Object> sheetExportMap = new HashMap<>();
//sheet名称、内容、内容标题
ExportParams sheetExportParams = new ExportParams("数据统计", "数据统计", ExcelType.HSSF);

//title 设置的是sheet名称和第一行的标题
sheetExportMap.put("title", sheetExportParams);
//导出表设计的表头
sheetExportMap.put("entityList", exportList);
//导出数据list<map>格式
sheetExportMap.put("data", list);

return sheetExportMap;
}
 

</pre></div>