1、依赖

       <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.3</version></dependency>

2、模板

3、实体类

package com.skybird.iot.addons.productionManagement.qualityTesting.backend.jdo;import com.alibaba.excel.annotation.ExcelProperty;public class qualityTestingExcelDao {@ExcelProperty("序号")private String index;@ExcelProperty("质检类别")private String workingProcedure;@ExcelProperty("检查部位")private String inspectionPart;@ExcelProperty("检查内容")private String inspectionContent;@ExcelProperty("检查方法")private String inspectionMethod;@ExcelProperty("检查结果")private String inspectionResult;@ExcelProperty("备注")private String notes;public String getIndex() {return index;}public void setIndex(String index) {this.index = index;}public String getWorkingProcedure() {return workingProcedure;}public void setWorkingProcedure(String workingProcedure) {this.workingProcedure = workingProcedure;}public String getInspectionPart() {return inspectionPart;}public void setInspectionPart(String inspectionPart) {this.inspectionPart = inspectionPart;}public String getInspectionContent() {return inspectionContent;}public void setInspectionContent(String inspectionContent) {this.inspectionContent = inspectionContent;}public String getInspectionMethod() {return inspectionMethod;}public void setInspectionMethod(String inspectionMethod) {this.inspectionMethod = inspectionMethod;}public String getInspectionResult() {return inspectionResult;}public void setInspectionResult(String inspectionResult) {this.inspectionResult = inspectionResult;}public String getNotes() {return notes;}public void setNotes(String notes) {this.notes = notes;}@Overridepublic String toString() {return "qualityTestingExcelDao{"+ "index='"+ index+ '\''+ ", workingProcedure='"+ workingProcedure+ '\''+ ", inspectionPart='"+ inspectionPart+ '\''+ ", inspectionContent='"+ inspectionContent+ '\''+ ", inspectionMethod='"+ inspectionMethod+ '\''+ ", inspectionResult='"+ inspectionResult+ '\''+ ", notes='"+ notes+ '\''+ '}';}
}

4、接口

private static List<qualityTestingExcelDao> getList(Document dto) {List<Document> list = DocuLib.getList(dto, "qualityInspectionList");List<qualityTestingExcelDao> excelList = new ArrayList<>();// 用于记录当前质检序号int index = 0;// 用于记录当前质检类别String inspectionPart = "";for (int i = 0; i < list.size(); i++) {Document item = list.get(i);String workingProcedure = DocuLib.getStr(item, "workingProcedure");List<Document> detectionList = DocuLib.getList(item, "detectionList");if (ObjectUtils.isNotEmpty(detectionList)) {for (Document row : detectionList) {qualityTestingExcelDao dao = new qualityTestingExcelDao();String inspectionPartDb = DocuLib.getStr(row, "project");if (!inspectionPart.equals(inspectionPartDb)) {inspectionPart = inspectionPartDb;index++;}dao.setIndex(String.valueOf(index));dao.setWorkingProcedure(workingProcedure);dao.setInspectionPart(inspectionPartDb);dao.setInspectionContent(DocuLib.getStr(row, "content"));dao.setInspectionMethod(DocuLib.getStr(row, "inspectionMethods.name"));dao.setInspectionResult(DocuLib.getStr(row, "result.name"));dao.setNotes(DocuLib.getStr(row, "illustrate"));excelList.add(dao);}} else {qualityTestingExcelDao dao = new qualityTestingExcelDao();dao.setIndex(String.valueOf(index));dao.setWorkingProcedure(workingProcedure);excelList.add(dao);}}return excelList;}/*** 根据模板下载** @param response* @throws IOException*/@GetMapping("/excel")public void excel(HttpServletResponse response, @RequestParam("id") String id)throws IOException {try {Document dto = DBUtils.find(qualityTesting.collectionName, new Document("id", id));List<qualityTestingExcelDao> excelList = getList(dto);InputStream templateStream =qualityTestingWeb.class.getResourceAsStream("/templates/qualityTesting.xlsx");if (templateStream == null) {throw new FileNotFoundException("未找到模板文件");}// 生成目标文件ExcelWriter excelWriter =EasyExcel.write(response.getOutputStream()).withTemplate(templateStream).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();// 每次都会重新生成新的一行，而不是使用下面的空行FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();// 替换第一种占位符Map<String, Object> map = new HashMap<>();map.put("name", DocuLib.getStr(dto, "productName"));map.put("userName", DocuLib.getStr(dto, "completeBy.name"));map.put("time", DocuLib.getStr(dto, "completeDate"));excelWriter.fill(map, writeSheet);// 第二种占位符替换，这里定义了 hisDataexcelWriter.fill(new FillWrapper("dto", excelList), fillConfig, writeSheet);excelWriter.finish();// 设置响应头response.setContentType("application/vnd.ms-excel"); // 设置文本内省response.setCharacterEncoding("utf-8"); // 设置字符编码response.setHeader("Content-disposition", "attachment;fileName=name.xlsx");// 关闭模板流templateStream.close();} catch (FileNotFoundException e) {// 处理文件未找到异常response.setStatus(HttpServletResponse.SC_NOT_FOUND);// 返回适当的错误消息response.getWriter().write("未找到模板文件");} catch (Exception e) {// 处理其他异常response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);// 返回适当的错误消息response.getWriter().write("内部服务器错误");}}

5、效果