EasyExcel_通过模板导出(多sheet、列表、图片)

前言

产品今天提了个需求，大概是这样的，来，请看大屏幕。。。额。。。搞错了，重来！来，请看需求
设计到多sheet、列表、图片的模板导出，先看成品

一、EasyExcel是什么？

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。
详细介绍见官网：EasyExcel官网

二、模板样式调整

三、使用步骤

1.引入jar包

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

2.方法示例

2.1 Controller：

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.net.URLEncoder;
/**1. <p>2. 可靠性测试委托单 前端控制器3. </p>4.  5. @author zs6. @since 2024-06-21*/
@RestController
@Api(tags = "可靠性测试委托单")
@Validated
@Slf4j
@RequestMapping("/reliabitestEntrust")
public class ReliabitestEntrustController {@Resourceprivate IReliabitestEntrustService entrustService;@ApiOperation(value = "可靠性测试委托单-下载报告")@GetMapping("/downEntrustReport")public void downEntrustReport(HttpServletResponse response, @RequestParam("id") @NotNull(message = "id不能为空") Long id) {try {ByteArrayOutputStream oos = new ByteArrayOutputStream();String fileName = entrustService.downEntrustReport(oos, id);response.setHeader("Content-Disposition", "attachment;filename*=" + URLEncoder.encode(fileName, "UTF-8"));oos.writeTo(response.getOutputStream());oos.flush();oos.close();} catch (Exception e) {log.error("可靠性测试委托单-下载报告出错了：{}", e);response.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");try {ResponseBean resp = ResponseBean.buildFail(CodeEnums.FAIL.getCode(), e.getMessage());response.getWriter().println(JSONObject.toJSONString(resp));} catch (Exception e1) {log.error("可靠性测试委托单-下载报告,出错了：{}", e1);}}}
}

2.2 Service：

/*** <p>* 可靠性测试委托单 服务类* </p>** @author zs* @since 2024-06-21*/
public interface IReliabitestEntrustService extends IService<ReliabitestEntrust> { /*** 功能: 下载委托单报告* 调用时机: 点击下载报告按钮* 注意事项: 参数必传** @return* @author zs* @date 2024/6/27*/String downEntrustReport(OutputStream outputStream, Long id) throws Exception;}

2.3 ServiceImpl:

 /*** 功能: 下载委托单报告* 调用时机: 点击下载报告按钮* 注意事项: 参数必传** @return* @author zs* @date 2024/6/27*/@Overridepublic String downEntrustReport(OutputStream outputStream, Long id) throws Exception {// Step 1: 获取委托单主表、详情、问题点与改善、委托单配置CompletableFuture<ReliabitestEntrust> entrustFuture = CompletableFuture.supplyAsync(() -> this.getById(id));CompletableFuture<List<ReliabitestEntrustDetail>> detailFuture = CompletableFuture.supplyAsync(() -> entrustDetailService.queryByEntrustId(id));CompletableFuture<List<ReliabitestEntrustQuestion>> questionFuture = CompletableFuture.supplyAsync(() -> entrustQuestionService.queryByEntrustId(id));// 获取config配置ResponseBean<List<GetConfigListByTypeResp>> response = basicsClient.getConfigsByType(47);CheckException.checkThrowException(() -> !response.isSuccess() || CollectionUtil.isEmpty(response.getData()), "委托单配置为空,请检查配置!!!");List<GetConfigListByTypeResp> configList = response.getData();// 试验项目 code+名称Map<String, String> codeAndNameMap = configList.stream().collect(Collectors.toMap(e -> e.getCode(), a -> a.getName(), (k1, k2) -> k1));// 子集List<GetConfigListByTypeResp> childrenList = configList.stream().map(GetConfigListByTypeResp::getChildrenList).flatMap(List::stream).collect(Collectors.toList());Map<String, String> childMap = childrenList.stream().collect(Collectors.toMap(e -> e.getCode(), a -> a.getName(), (k1, k2) -> k1));// Step 2: 组装模板数据ReliabitestEntrust entrust = entrustFuture.get();// 主信息DownEntrustReportDTO dto = new DownEntrustReportDTO();if (ObjectUtil.isNotNull(entrust)) {assembleData(detailFuture, questionFuture, codeAndNameMap, childMap, entrust, dto);}// Step 3: 获取下载报告的模板String tempFileName = tempPath + "可靠性测试委托单_报告模板.xlsx";// Step 4: 模板数据写入FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();ExcelWriter build = EasyExcel.write(outputStream).withTemplate(tempFileName).build();WriteSheet writeSheet0 = EasyExcel.writerSheet(0).build();build.fill(dto, writeSheet0);WriteSheet writeSheet1 = EasyExcel.writerSheet(1).registerWriteHandler(new CustomMergeStrategy()).build();build.fill(dto, fillConfig, writeSheet1);build.fill(new FillWrapper("projects", dto.getProjects()), fillConfig, writeSheet1);WriteSheet writeSheet2 = EasyExcel.writerSheet(2).registerWriteHandler(new CustomMergeStrategy()).build();build.fill(dto, fillConfig, writeSheet2);build.fill(new FillWrapper("questions", dto.getQuestions()), fillConfig, writeSheet2);build.fill(new FillWrapper("details", dto.getDetails()), fillConfig, writeSheet2);build.fill(new FillWrapper("infos", dto.getInfos()), fillConfig, writeSheet2);build.finish();String fileName = String.format("%s_%s.xlsx", dto.getReportCode(), System.currentTimeMillis()).replaceAll(" ", "-");log.info("报告委托单名称组成格式：样品ID 样品名称/规则-样品类别-测试报告(固定值)-创建日期(日期格式：yyyyMMdd),{}", dto.getEntrustName());return fileName;}/*** 功能: 组装模板数据* 调用时机:** @author zs* @date 2024/6/30*/private void assembleData(CompletableFuture<List<ReliabitestEntrustDetail>> detailFuture,CompletableFuture<List<ReliabitestEntrustQuestion>> questionFuture,Map<String, String> codeAndNameMap, Map<String, String> childMap,ReliabitestEntrust entrust, DownEntrustReportDTO dto) throws Exception {String sampleIdAndName = String.format("%s %s", entrust.getSampleId(), entrust.getSampleName());// 报告委托单名称组成格式：样品ID 样品名称/规则-样品类别-测试报告(固定值)-创建日期(日期格式：yyyyMMdd)String sampleTypeStr = "";for (EntrustSampleTypeEnums value : EntrustSampleTypeEnums.values()) {if (value.getCode().equals(entrust.getSampleType())) {sampleTypeStr = value.getMsg();break;}}String formatTime = DateTimeFormatter.ofPattern("yyyyMMdd").format(entrust.getCreateTime().toLocalDate());String entrustName = String.format("%s %s-%s-测试报告-%s", entrust.getSampleId(), entrust.getSampleName(), sampleTypeStr, formatTime);dto.setSampleIdAndName(sampleIdAndName);dto.setEntrustName(entrust.getEntrustCode());dto.setSampleGiveDate(entrust.getSampleGiveDate().toLocalDate().toString());dto.setTotalSampleNum(entrust.getTotalSampleNum());dto.setTestSamplingStr(entrust.getTestSampling() == 1 ? "抽样" : "送样");dto.setTestBy(entrust.getTestBy());dto.setTestHumidity(entrust.getTestHumidity());dto.setTestTemperature(entrust.getTestTemperature());dto.setAuditBy(entrust.getAuditBy());dto.setTestBasis(entrust.getTestBasis());dto.setApplyBy(entrust.getApplyBy());dto.setReportCode(entrustName);dto.setActualStartDate(entrust.getActualStartDate().toLocalDate().toString());dto.setActualFinishDate(entrust.getActualFinishDate().toLocalDate().toString());dto.setTestSampleInsp(entrust.getTestSampleInsp());dto.setTestTotalResultStr(EntrustTestResultEnums.findByCode(entrust.getTestTotalResult()));dto.setTestResultRemark(entrust.getTestResultRemark());// 试验项目及判定、试验明细、试验信息展示List<ReliabitestEntrustDetail> details = detailFuture.get();if (CollectionUtil.isNotEmpty(details)) {List<DownEntrustReportDTO.TestProject> testProjects = new ArrayList<>();List<DownEntrustReportDTO.TestDetail> testDetails = new ArrayList<>();List<DownEntrustReportDTO.TestInfo> testInfos = new ArrayList<>();for (int i = 0; i < details.size(); i++) {int index = i + 1;ReliabitestEntrustDetail entrustDetail = details.get(i);// 试验结果转换String testResult = EntrustTestResultEnums.findByCode(entrustDetail.getTestResult());// 试验项目转换String testProject = ObjectUtil.isNotNull(codeAndNameMap.get(entrustDetail.getTestProject())) ?codeAndNameMap.get(entrustDetail.getTestProject()) : "";// 试验条件参数String testCondition = ObjectUtil.isNotNull(childMap.get(entrustDetail.getTestCondition())) ?childMap.get(entrustDetail.getTestCondition()) : "";// 试验项目及判定DownEntrustReportDTO.TestProject project = new DownEntrustReportDTO.TestProject();project.setIndex(Long.valueOf(index));project.setTestProject(testProject);      // 试验项目StringBuffer sb = new StringBuffer();for (int i1 = 1; i1 <= entrustDetail.getSampleNum().intValue(); i1++) {sb.append(entrustDetail.getSampleGroup()).append("-").append(i1);if (i1 < entrustDetail.getSampleNum().intValue()) {sb.append("、");}}project.setGroupTestSequence(sb.toString());project.setTestResultStr(testResult);project.setTestResultDesc(entrustDetail.getTestResultDesc());testProjects.add(project);// 试验明细DownEntrustReportDTO.TestDetail detail = new DownEntrustReportDTO.TestDetail();detail.setIndex(Long.valueOf(index));detail.setTestProject(testProject);      // 试验项目detail.setTestCondition(testCondition);  // 试验参数(条件)detail.setJudgeStandardStr(entrustDetail.getJudgeStandard());detail.setTestMethod(entrustDetail.getTestMethod());detail.setTestResultStr(testResult);detail.setTestResultDesc(entrustDetail.getTestResultDesc());testDetails.add(detail);// 试验信息展示DownEntrustReportDTO.TestInfo info = new DownEntrustReportDTO.TestInfo();info.setIndex(Long.valueOf(index));info.setTestProject(testProject);      // 试验项目// 测试前图片if (StringUtils.isNotBlank(entrustDetail.getBeforeTest())) {byte[] beforePicByte = getImage(entrustDetail.getBeforeTest());WriteCellData<Void> voidWriteCellData = imageCells(beforePicByte, 0, 2);info.setBeforePic(voidWriteCellData);}// 测试中图片地址if (StringUtils.isNotBlank(entrustDetail.getDuringTest())) {byte[] duringPicByte = getImage(entrustDetail.getDuringTest());WriteCellData<Void> voidWriteCellData = imageCells(duringPicByte, 0, 2);info.setDuringPic(voidWriteCellData);}// 测试后图片地址if (StringUtils.isNotBlank(entrustDetail.getAfterTest())) {byte[] afterPicByte = getImage(entrustDetail.getAfterTest());WriteCellData<Void> voidWriteCellData = imageCells(afterPicByte, 0, 2);info.setAfterPic(voidWriteCellData);}testInfos.add(info);}dto.setProjects(testProjects);dto.setDetails(testDetails);dto.setInfos(testInfos);}// 问题点与改善List<ReliabitestEntrustQuestion> questions = questionFuture.get();if (CollectionUtil.isNotEmpty(questions)) {List<DownEntrustReportDTO.TestQuestion> testQuestions = new ArrayList<>();for (int i = 0; i < questions.size(); i++) {ReliabitestEntrustQuestion entrustQuestion = questions.get(i);DownEntrustReportDTO.TestQuestion question = new DownEntrustReportDTO.TestQuestion();question.setProposeTime(entrustQuestion.getProposeTime().toLocalDate().toString());question.setQuestionDesc(entrustQuestion.getQuestionDesc());question.setQuestionTypeStr(EntrustQuestionTypeEnums.findByCode(entrustQuestion.getQuestionType()));question.setQuestionRemark(entrustQuestion.getQuestionRemark());testQuestions.add(question);}dto.setQuestions(testQuestions);}}/*** 功能: 设置单元格格式* 调用时机:* 使用方法:* 注意事项: ** @Param bytes 图片字节* @Param lastRowIndex 行偏移量* @Param lastColumnIndex 列偏移* @author zs* @date 2024/6/30*/public static WriteCellData<Void> imageCells(byte[] bytes, Integer lastRowIndex, Integer lastColumnIndex) throws IOException {WriteCellData<Void> writeCellData = new WriteCellData<>();// 可以放入多个图片，目前只放一张List<ImageData> imageDataList = new ArrayList<>();writeCellData.setImageDataList(imageDataList);ImageData imageData = new ImageData();imageDataList.add(imageData);// 设置图片imageData.setImage(bytes);// 上右下左需要留空，通过这种方式调整图片大小，单位为像素imageData.setTop(5);imageData.setRight(5);imageData.setBottom(5);imageData.setLeft(5);//以下四个属性分别为设置单元格偏移量，因为图片可能占据多个单元格（合并单元格）// 这里以左上角单元格为起始，所以FirstRowIndex和FirstColumnIndex默认为0// 向右增加一格则设置LastColumnIndex为1，// 向下增加一格设置LastRowIndex属性为1，imageData.setRelativeFirstRowIndex(0);imageData.setRelativeFirstColumnIndex(0);imageData.setRelativeLastRowIndex(lastRowIndex);imageData.setRelativeLastColumnIndex(lastColumnIndex);return writeCellData;}/*** 功能: url链接转byte* 调用时机: 图片URL需要转换时* 注意事项: 参数必传** @author zs* @date 2024/6/28*/private byte[] getImage(String url) {try {URL imageUrl = new URL(url);HttpURLConnection conn = (HttpURLConnection) imageUrl.openConnection();// 设置超时以提高响应性和避免无限等待conn.setConnectTimeout(5000); // 连接超时5秒conn.setReadTimeout(5000);    // 读取超时5秒// 设置为只输入模式conn.setRequestMethod("GET");conn.setDoInput(true);conn.connect();// try (Resource r = acquireResource())语法会在try块结束时(无论是否正常结束还是因为异常结束)自动调用资源的close()方法try (InputStream is = conn.getInputStream()) {return IoUtils.toByteArray(is);}} catch (IOException e) {throw new RuntimeException(String.format("URL转byte报错：%s", e));}}

2.4 合并策略：

package com.smallrig.sku.handler.excel;import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;import java.util.List;/*** CustomMergeStrategy class.** @author zs* @program: nakadai* @description: 合并单元格(获取前一行样式应用于当前行)* @date 2024/6/30*/
public class CustomMergeStrategy extends AbstractMergeStrategy {@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (relativeRowIndex == null || relativeRowIndex == 0) {return;}int rowIndex = cell.getRowIndex();int colIndex = cell.getColumnIndex();sheet = cell.getSheet();Row preRow = sheet.getRow(rowIndex - 1);//获取上一行的该格Cell preCell = preRow.getCell(colIndex);List<CellRangeAddress> list = sheet.getMergedRegions();CellStyle cs = cell.getCellStyle();cell.setCellStyle(cs);for (int i = 0, len = list.size(); i < len; i++) {CellRangeAddress cellRangeAddress = list.get(i);if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {int lastColIndex = cellRangeAddress.getLastColumn();int firstColIndex = cellRangeAddress.getFirstColumn();CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);sheet.addMergedRegion(cra);RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);return;}}}}

2.5 对应DTO

package com.smallrig.sku.dto;import com.alibaba.excel.metadata.data.WriteCellData;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;import java.math.BigDecimal;
import java.util.List;/*** DownloadEntrustReportDTO class.** @author zs* @program: nakadai* @description: 下载委托单报告* @date 2024/6/27*/
@Data
@ApiModel(value = "下载委托单报告")
public class DownEntrustReportDTO {@ApiModelProperty(value = "样品ID和样品名称/规格组合")private String sampleIdAndName;@ApiModelProperty("委托单名称")private String entrustName;@ApiModelProperty("送样日期")private String sampleGiveDate;@ApiModelProperty("总样品数量")private Long totalSampleNum;@ApiModelProperty("取样: 1 抽样 2 送样")private String testSamplingStr;@ApiModelProperty("试验单位")private String testCompany = "质量服务部";@ApiModelProperty("测试工程师")private String testBy;@ApiModelProperty("测试环境湿度%RH")private BigDecimal testHumidity;@ApiModelProperty("测试环境温度℃")private BigDecimal testTemperature;@ApiModelProperty("审核人")private String auditBy;@ApiModelProperty("试验依据")private String testBasis;@ApiModelProperty("申请人")private String applyBy;// 报告编号格式：委托单名称+"报告(固定值)"+当前日期(日期格式：yyyyMMdd)@ApiModelProperty(value = "报告编号")private String reportCode;@ApiModelProperty("开始试验日期")private String actualStartDate;@ApiModelProperty("结束试验日期")private String actualFinishDate;@ApiModelProperty("试验前样品检验")private String testSampleInsp;@ApiModelProperty("总测试结果(EntrustTestResultEnums.class)")private String testTotalResultStr;@ApiModelProperty("测试结果备注")private String testResultRemark;@ApiModelProperty(value = "试验项目及判定列表")private List<TestProject> projects;@ApiModelProperty(value = "问题点与改善")private List<TestQuestion> questions;@ApiModelProperty(value = "试验明细")private List<TestDetail> details;@ApiModelProperty(value = "试验信息展示")private List<TestInfo> infos;@Data@ApiModel(value = "试验项目及判定")public static class TestProject {@ApiModelProperty(value = "序号")private Long index;@ApiModelProperty("试验项目")private String testProject;// 试验顺序格式：分组+"-"+组内试验顺序@ApiModelProperty("试验顺序")private String groupTestSequence;@ApiModelProperty("试验结果")private String testResultStr;@ApiModelProperty("试验结果描述")private String testResultDesc;}@Data@ApiModel(value = "问题点与改善")public static class TestQuestion {@ApiModelProperty("提出时间")private String proposeTime;@ApiModelProperty("问题描述")private String questionDesc;@ApiModelProperty("问题类型")private String questionTypeStr;@ApiModelProperty("问题备注")private String questionRemark;}@Data@ApiModel(value = "试验明细")public static class TestDetail {@ApiModelProperty(value = "序号")private Long index;@ApiModelProperty("试验项目")private String testProject;@ApiModelProperty("试验参数(条件)")private String testCondition;@ApiModelProperty("判定标准")private String judgeStandardStr;@ApiModelProperty("试验方法")private String testMethod;@ApiModelProperty("试验结果")private String testResultStr;@ApiModelProperty("试验结果描述")private String testResultDesc;}@Data@ApiModel(value = "试验信息展示")public static class TestInfo {@ApiModelProperty(value = "序号")private Long index;@ApiModelProperty("试验项目")private String testProject;@ApiModelProperty("测试前图片地址")private WriteCellData beforePic;@ApiModelProperty("测试中图片地址")private WriteCellData duringPic;@ApiModelProperty("测试后图片地址")private WriteCellData afterPic;}}

总结

整个方法中有几个重要点：

1. 合并逻辑，因为模板设计中，图片存在跨行和跨列。
2. 通过图片链接去获取图片数据并转换和写入
3. DTO中图片类使用WriteCellData
4. 多sheet页写入需要对应不同的WriteSheet

原创不易，望一键三连 (^ _ ^)