【经验分享】EasyExcel实现自定义动态化导出excel

需求：

        需求涉及保存设备的灵活属性，设备的属性并不固定。允许设备的属性动态扩展。最终数据库采用EAV 模型的设计模式，使用三张表实现：设备表 、属性定义表、属性值表。

        现在堆上述数据进行导入和导出。

解决：

        这里使用EasyExcel，处理数据的导入导出。工具类核心方法如下：

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import org.springframework.util.CollectionUtils;import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.*;
import java.util.stream.Collectors;/*** 自定义导出* @param os* @param titleList* @param mergeTitles* @param dataList*/public static void customDynamicExport(OutputStream os, List<String> titleList,List<String> mergeTitles, List<List<Object>> dataList) {
//        fileName = fileName + ".xlsx";// 导出的数据转换为Map数据结构， k -> 标题，v -> 此标题下的数据按顺序Map<String, List<Object>> dataMap = new HashMap<>(titleList.size());for (int i = 0 ; i < titleList.size(); i++) {// 当前列数据List<Object> currectTitleDatas = new ArrayList<>();for (int j = 0; j < dataList.size(); j++) {currectTitleDatas.add(dataList.get(j).get(i));}dataMap.put(titleList.get(i), currectTitleDatas);}// **标题信息List<List<String>> headTitleInfo = excelTitle(titleList);// **数据信息List<List<Object>> excelDataInfo = excelData(dataList);// **合并单元格信息List<LoopMergeStrategy> mergeStrategies = mergeCells(titleList, mergeTitles, dataMap);ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(os);if (!CollectionUtils.isEmpty(mergeStrategies)) {
//            log.info("开始进行合并单元格操作 mergeStrategies ===> {}", mergeStrategies);mergeStrategies.forEach(m -> {excelWriterBuilder.registerWriteHandler(m);});}excelWriterBuilder.head(headTitleInfo).sheet("模板")// 当然这里数据也可以用 List<List<String>> 去传入.doWrite(excelDataInfo);}/*** **excel标题** @param titleList* @return*/private static List<List<String>> excelTitle(List<String> titleList) {List<List<String>> excelTitleList = new ArrayList<>(titleList.size());titleList.forEach(k -> {List<String> titles = new ArrayList<>(1);titles.add(k);excelTitleList.add(titles);});return excelTitleList;}private static List<List<Object>> excelData(List<List<Object>> dataList) {return dataList;}private static List<LoopMergeStrategy> mergeCells(List<String> titleList, List<String> mergeTitles, Map<String, List<Object>> dataMap) {// 将需要合并的项转换为Map，以便**合并位置Map<String, String> mergeTitleMap = mergeTitles.stream().collect(Collectors.toMap(k -> k, k -> k));// **合并位置Map<String, Integer> mergeLocationMap = new HashMap<>(mergeTitles.size());for (int i = 0; i < titleList.size(); i++) {if (Objects.nonNull(mergeTitleMap.get(titleList.get(i)))) {mergeLocationMap.put(titleList.get(i), i);}}List<LoopMergeStrategy> mergeStrategyList = new ArrayList<>();mergeTitles.forEach(k -> {// 合并数据所在的列数Integer columnIndex = mergeLocationMap.get(k);List<Object> mergeDatas = dataMap.get(k);// 需要合并的数量Integer currectMergeNum = 1;for (int m = 0; m < mergeDatas.size(); m++) {// 是否存在合并环节Boolean isMerge = false;if (m != 0 && Objects.equals(mergeDatas.get(m), mergeDatas.get(m - 1))) {isMerge = true;currectMergeNum = currectMergeNum + 1;if (m == mergeDatas.size() - 1) {isMerge = false;}}if (!isMerge && currectMergeNum > 1) {// 合并的行数Integer eachRow = currectMergeNum;mergeStrategyList.add(new LoopMergeStrategy(eachRow, columnIndex));currectMergeNum = 1;}}});return mergeStrategyList;}