esayExcel根据模板导出包含图片

1、效果

2、模板

3、工具类代码

java">  /*** 根据模板填充* @param response* @param templateStream 模板文件流* @param map 模板文件所需要的参数* @param list list循环模板参数* @throws IOException*/public static void templateFilling(HttpServletRequest servletRequest,HttpServletResponse response, InputStream templateStream, Map<String, Object> map, List<FillWrapper> list,String name) throws IOException {try {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();excelWriter.fill(map, writeSheet);// 第二种占位符替换for (FillWrapper fillWrapper : list) {excelWriter.fill(fillWrapper, fillConfig, writeSheet);}excelWriter.finish();/*兼容IE和其他浏览器导出文件名乱码的问题*/name = downloadCommFileName(name, servletRequest);// 设置响应头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("内部服务器错误");}}

4、实体类

java">package com.skybird.iot.addons.tpm.care.backend.jdo;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import lombok.EqualsAndHashCode;import java.net.URL;@EqualsAndHashCode
@ContentRowHeight(15)
@ColumnWidth(15)
public class DeviceCareOrderTemplateExcelDao {@ExcelProperty("保养部位")private String part;@ExcelProperty("保养项目")private String project;@ExcelProperty("作业方法")private String method;@ExcelProperty("保养标准")private String standard;@ExcelProperty("图片说明")private URL viewImage;@ExcelProperty("保养结果")private String result;@ExcelProperty("备注")private String remark;public String getPart() {return part;}public void setPart(String part) {this.part = part;}public String getProject() {return project;}public void setProject(String project) {this.project = project;}public String getMethod() {return method;}public void setMethod(String method) {this.method = method;}public String getStandard() {return standard;}public void setStandard(String standard) {this.standard = standard;}public URL getViewImage() {return viewImage;}public void setViewImage(URL viewImage) {this.viewImage = viewImage;}public String getResult() {return result;}public void setResult(String result) {this.result = result;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}
}

5、URL数据说明

viewImage字段数据需要组装成图片地址

6、接口

java">@RequestMapping("/exportTemplate")public void exportTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {String data = request.getParameter("data");Document filter = DocuLib.parseDecode(data);String id = DocuLib.getID(filter);Document entity = DBUtils.findByID(QDeviceCareOrder.collectionName, id);// 获取协议（例如 http: 或 https:）String protocol = request.getScheme();// 获取主机名（例如 localhost）String hostname = request.getServerName();// 获取端口号（如果有）int port = request.getServerPort();// 构建前缀部分StringBuilder baseUrl = new StringBuilder();baseUrl.append(protocol).append("://").append(hostname);// 如果有端口号，添加端口号if ((protocol.equals("http") && port != 80) || (protocol.equals("https") && port != 443)) {baseUrl.append(":").append(port);}// 获取上下文路径（例如 /yoren）String contextPath = request.getContextPath();if (contextPath != null && !contextPath.isEmpty()) {baseUrl.append(contextPath);}String head = baseUrl+"/";InputStream templateStream =DeviceCareOrderWeb.class.getResourceAsStream("/templates/deviceCare.xlsx");List<Document> itemList = DocuLib.getList(entity, "itemList");List<DeviceCareOrderTemplateExcelDao> excelList = new ArrayList<>();for (Document item : itemList) {DeviceCareOrderTemplateExcelDao dao = new DeviceCareOrderTemplateExcelDao();dao.setPart(DocuLib.getStr(item, "part"));dao.setProject(DocuLib.getStr(item, "project"));dao.setMethod(DocuLib.getStr(item, "method"));dao.setStandard(DocuLib.getStr(item, "standard"));String viewImage = DocuLib.getStr(item, "view_image");dao.setViewImage(StrUtil.isNotEmpty(viewImage)?new URL(head + "gridfs/" + viewImage):null);dao.setResult(StrUtil.isNotEmpty(DocuLib.getStr(item, "result")) ? DocuLib.getStr(item, "result").equals("finish") ? "完成" : "异常" : "");dao.setRemark(DocuLib.getStr(item, "remark"));excelList.add(dao);}List<Document> fileImageList = DocuLib.getList(entity, "fileImageList");List<DeviceCareOrderTemplateExcelDao> ImageList = new ArrayList<>();for (Document item : fileImageList) {DeviceCareOrderTemplateExcelDao dao = new DeviceCareOrderTemplateExcelDao();dao.setViewImage(new URL(head + DocuLib.getStr(item, "url")));ImageList.add(dao);}Map<String, Object> map = new HashMap<>();map.put("code", DocuLib.getStr(entity,"code"));map.put("name", DocuLib.getStr(entity,"name"));map.put("deviceName", DocuLib.getStr(entity,"device.name"));String start = StrUtil.isNotEmpty(DocuLib.getStr(entity,"planDateBetween.start"))? DocuLib.getStr(entity,"planDateBetween.start"): "";String end = StrUtil.isNotEmpty(DocuLib.getStr(entity,"planDateBetween.end"))? DocuLib.getStr(entity,"planDateBetween.end"): "";map.put("time", start + "~" + end);map.put("workTimeBegin", DocuLib.getStr(entity,"workTimeBegin"));map.put("workTimeEnd", DocuLib.getStr(entity,"workTimeEnd"));map.put("workTimeHour", DocuLib.getStr(entity,"workTimeHour")+" 小时");map.put("remark", DocuLib.getStr(entity,"remark"));map.put("createUserName", DocuLib.isNotEmpty(DocuLib.getDocu(entity,"createUser"))?DocuLib.getStr(entity,"createUser.userName")+"-"+DocuLib.getStr(entity,"createUser.name"):"");map.put("createDate", DateUtil.format(DocuLib.getDate(entity, "create_date"), "yyyy-MM-dd HH:mm:ss.SSS"));map.put("careUserName", DocuLib.isNotEmpty(DocuLib.getDocu(entity,"careUser"))?DocuLib.getStr(entity,"careUser.userName")+"-"+DocuLib.getStr(entity,"careUser.name"):"");map.put("startTime", DateUtil.format(DocuLib.getDate(entity, "start_time"), "yyyy-MM-dd HH:mm:ss.SSS"));map.put("auditUserName", DocuLib.isNotEmpty(DocuLib.getDocu(entity,"auditUser"))?DocuLib.getStr(entity,"auditUser.userName")+"-"+DocuLib.getStr(entity,"auditUser.name"):"");map.put("auditTime", DateUtil.format(DocuLib.getDate(entity, "audit_time"), "yyyy-MM-dd HH:mm:ss.SSS"));List<FillWrapper> list = new ArrayList<>();if (CollUtil.isNotEmpty(excelList)) {list.add(new FillWrapper("dto", excelList));}if (CollUtil.isNotEmpty(ImageList)) {list.add(new FillWrapper("row", ImageList));}templateFilling(request,response,templateStream,map,list,StrUtil.join("-", "设备保养详情", System.currentTimeMillis()));}