一、简单的数据导出(不涉及数据的合并)
导出效果
必要的前期准备
1.基本的数据表
CREATE TABLE `excel_dynamic` ( `id` bigint(22) NOT NULL AUTO_INCREMENT, `function_number` int(5) NOT NULL COMMENT '功能编号', `function_name` varchar(50) DEFAULT NULL COMMENT '功能名字', `header` varchar(255) DEFAULT NULL COMMENT '复杂表头使用', `field_name` varchar(50) DEFAULT NULL COMMENT '字段名字', `property_name` varchar(50) DEFAULT NULL COMMENT '标记属性名', `type` int(2) DEFAULT NULL COMMENT '类型(1:字符串 2:时间 3:金额 4:枚举)', `additional_data` varchar(20) DEFAULT NULL COMMENT '额外操作如:yyyy-mm-dd 或 枚举相关', `sort` int(5) unsigned DEFAULT NULL COMMENT '排序', PRIMARY KEY (`id`), KEY `index_function_number` (`function_number`) ) ENGINE=InnoDB AUTO_INCREMENT=1739178131555749905 DEFAULT CHARSET=utf8 COMMENT='动态Excel';
2.必要的实体类
@Data public class ExcelDynamic { @TableId private Long id; /** * 功能编号 */ private Integer functionNumber; /** * 功能名字 */ private String functionName; /** * 表头 (预留) */ private String header; /** * 字段名 中文 */ private String fieldName; /** * 字段名 */ private String propertyName; /** * 字段类型 */ private Integer type; /** * 额外标识 */ private String additionalData; /** * 排序 */ private Integer sort; }
@Data public class ExcelData { private List<List<String>> headList; private List<List<String>> dataList; }
3.枚举
@Getter @AllArgsConstructor public enum ExcelDynamicsTypeEnum { STRING(1, "普通文本"), DATE(2, "时间"), MONEY(3, "金额"), // 枚举 ENUM(4, "枚举"), ; /** * 序号 */ private final Integer code; /** * 内容 */ private final String content; }
4.和数据库连接使用的Mapper
@Mapper public interface ExcelDynamicMapper extends BaseMapper<ExcelDynamic> { }
5.核心service
点击查看完整内容
import cn.hutool.core.date.DateUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.builder.ExcelWriterBuilder; import com.alibaba.excel.write.handler.AbstractCellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.example.test05.demo.enums.ExcelDynamicsTypeEnum; import com.example.test05.demo.mapper.ExcelDynamicMapper; import com.example.test05.demo.model.ExcelData; import com.example.test05.demo.model.ExcelDynamic; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.io.File; import java.lang.reflect.Field; import java.math.BigDecimal; import java.util.*; import java.util.concurrent.atomic.AtomicInteger; import java.util.stream.Collectors; import java.util.stream.Stream; import static java.util.stream.Collectors.toList; /** * @author DBC * @description Excel动态导出工具类 * @date 2023/11/16 10:02 * @network dbc655.top */ @Service @Slf4j public class ExcelDynamicsService { @Resource ExcelDynamicMapper excelDynamicMapper; /** * 导出excel * * @param fileName 文件名 * @param file 文件 * @param listExport 导出的数据 * @param functionNumber 功能号 */ public void getExcel(String fileName, File file, List<?> listExport, int functionNumber) { excelWrite(EasyExcel.write(file), generateExcelData(listExport, functionNumber), fileName, new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())); } /** * 导出excel * * @param fileName 文件名 * @param fileUrl 导出文件地址 * @param listExport 导出的数据 * @param functionNumber 功能号 */ public void getExcel(String fileName, String fileUrl, List<?> listExport, int functionNumber) { excelWrite(EasyExcel.write(fileUrl), generateExcelData(listExport, functionNumber), fileName, new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())); } private void excelWrite(ExcelWriterBuilder file, ExcelData excelData, String fileName, HorizontalCellStyleStrategy horizontalCellStyleStrategy) { file .head(excelData.getHeadList()) .sheet(fileName) .registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 第一列为序号列,宽度固定为 20 .registerWriteHandler(new CustomCellWriteHandler(0, 15)) .doWrite(excelData.getDataList()); } private WriteCellStyle getWriteStyle() { WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 设置内容水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 设置内容垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return contentWriteCellStyle; } /** * 获取头的样式 * * @return */ private WriteCellStyle getHeadStyle() { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 12); headWriteCellStyle.setWriteFont(headWriteFont); return headWriteCellStyle; } class CustomCellWriteHandler extends AbstractCellWriteHandler { private final int columnIndex; private final int width; public CustomCellWriteHandler(int columnIndex, int width) { this.columnIndex = columnIndex; this.width = width; } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if (isHead && columnIndex == cell.getColumnIndex()) { cell.getSheet().setColumnWidth(cell.getColumnIndex(), width * 256); } } } /** * 生成excel数据 * * @param listExport * @param functionNumber * @return */ public ExcelData generateExcelData(List<?> listExport, int functionNumber) { List<ExcelDynamic> excelDynamics = excelDynamicMapper.selectList( new QueryWrapper<ExcelDynamic>().lambda() .eq(ExcelDynamic::getFunctionNumber, functionNumber) .orderByAsc(ExcelDynamic::getSort) ); List<List<String>> headList = new ArrayList<>(); if (isHeaderName(excelDynamics) != null) { headList.add(getIndex(excelDynamics)); headList.addAll(excelDynamics.stream() .map(d -> Arrays.asList(d.getHeader().split(","))) .collect(Collectors.toList())); } else { headList = Stream.concat(Stream.of(Collections.singletonList("序号")), excelDynamics.stream().map(d -> Collections.singletonList(d.getFieldName()))) .collect(Collectors.toList()); } Map<String, String> valueCache = new HashMap<>(); AtomicInteger index = new AtomicInteger(1); List<List<String>> dataList = listExport.stream() .map(obj -> mapObjectToDataListWithIndex(obj, excelDynamics, valueCache, index)) .collect(toList()); ExcelData excelData = new ExcelData(); excelData.setDataList(dataList); excelData.setHeadList(headList); return excelData; } public String isHeaderName(List<ExcelDynamic> excelDynamics) { if (CollectionUtils.isEmpty(excelDynamics)) { return null; } String functionName = excelDynamics.get(0).getHeader(); if (functionName == null) { return null; } return functionName; } /** * 获取序号 * * @param excelDynamics * @return */ public List<String> getIndex(List<ExcelDynamic> excelDynamics) { String headerName = isHeaderName(excelDynamics); if (headerName == null) { return Collections.singletonList("序号"); } // 该字段是,分割的,获取数组长度 List<String> headerList = Arrays.asList(headerName.split(",")); List<String> index = new ArrayList<>(); for (int i = 0; i < headerList.size() -1 ; i++) { index.add("序号"); } return index; } private List<String> mapObjectToDataListWithIndex(Object obj, List<ExcelDynamic> excelDynamics, Map<String, String> valueCache, AtomicInteger index) { // 在返回列表中添加自增的序号,并将 ExcelDynamic 映射为对应的字段值,最后收集为列表 return excelDynamics.stream() .map(ed -> getFieldStringValue(obj, ed, valueCache)) .collect(Collectors.collectingAndThen(toList(), list -> { list.add(0, Integer.toString(index.getAndIncrement())); // 在列表头部添加自增的序号 return list; })); } /** * 获取字典值 * * @param obj * @param excelDynamic * @return */ private String getFieldStringValue(Object obj, ExcelDynamic excelDynamic, Map<String, String> valueCache) { try { Field field = obj.getClass().getDeclaredField(excelDynamic.getPropertyName()); field.setAccessible(true); Object value = field.get(obj); if (value == null) { return ""; } if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.DATE.getCode())) { // 时间模式 使用 hutool return DateUtil.format((Date) value, excelDynamic.getAdditionalData()); } else if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.MONEY.getCode())) { // 金额模式 return roundIfTrailingZeros((BigDecimal) value).toString(); } else { return value.toString(); } } catch (NoSuchFieldException | IllegalAccessException | ClassCastException e) { throw new RuntimeException("动态转换出错了!" + excelDynamic.getPropertyName(), e); } } /** * 去除小数点后面的0 * * @param num * @return */ public static BigDecimal roundIfTrailingZeros(BigDecimal num) { return new BigDecimal(num.stripTrailingZeros().toPlainString()); } }
示例代码及数据库配置
@GetMapping("/detail") public void test() { List<UserDO> list = userService.list(); List<UserVO> voList = new ArrayList<>(); for (UserDO userDO : list) { UserVO userVO = new UserVO(); userVO.setId(userDO.getId()); userVO.setName1(userDO.getName()); userVO.setName2(userDO.getName()); userVO.setName3(userDO.getName()); userVO.setName4(userDO.getName()); userVO.setName5(userDO.getName()); voList.add(userVO); } excelDynamicsService.getExcel("测试动态导出", "D:\\jar\\"+ IdUtil.getSnowflakeNextIdStr()+".xlsx", voList,1); }
导出效果
二、重头戏——复杂的数据自动合并导出
1.核心代码类
点击查看完整内容
import cn.hutool.core.date.DateUtil; import cn.hutool.core.util.ReflectUtil; import cn.hutool.core.util.StrUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.builder.ExcelWriterBuilder; import com.alibaba.excel.write.handler.AbstractCellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.example.test05.demo.enums.ExcelDynamicsTypeEnum; import com.example.test05.demo.mapper.ExcelDynamicMapper; import com.example.test05.demo.model.ExcelData; import com.example.test05.demo.model.ExcelDynamic; import com.example.test05.demo.tool.ExcelMergeUtil; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.usermodel.*; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.io.File; import java.math.BigDecimal; import java.util.*; import java.util.concurrent.atomic.AtomicInteger; import java.util.stream.Collectors; import java.util.stream.IntStream; import java.util.stream.Stream; import static java.util.stream.Collectors.toList; /** * @author DBC * @description Excel动态导出工具类 * @date 2023/11/16 10:02 * @network dbc655.top */ @Service @Slf4j public class ExcelDynamicsService { @Resource ExcelDynamicMapper excelDynamicMapper; /** * 导出excel * * @param fileName 文件名 * @param file 文件 * @param listExport 导出的数据 * @param functionNumber 功能号 */ public void getExcel(String fileName, File file, List<?> listExport, int functionNumber) { excelWrite(EasyExcel.write(file), generateExcelData(listExport, functionNumber), fileName, new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())); } /** * 导出excel * * @param fileName 文件名 * @param fileUrl 导出文件地址 * @param listExport 导出的数据 * @param functionNumber 功能号 */ public void getExcel(String fileName, String fileUrl, List<?> listExport, int functionNumber) { excelWrite(EasyExcel.write(fileUrl), generateExcelData(listExport, functionNumber), fileName, new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())); } /** * 生成excel数据 —— 复杂版本 * * @param listExport * @param functionNumber * @return */ public ExcelData generateExcelData(List<?> listExport, int functionNumber) { List<ExcelDynamic> excelDynamics = excelDynamicMapper.selectList( new QueryWrapper<ExcelDynamic>().lambda() .eq(ExcelDynamic::getFunctionNumber, functionNumber) .orderByAsc(ExcelDynamic::getSort) ); List<List<String>> headList = new ArrayList<>(); headList = getHeadList(excelDynamics, headList); List<List<String>> dataList = new ArrayList<>(); Map<String, String> valueCache = new HashMap<>(); AtomicInteger index = new AtomicInteger(1); for (Object obj : listExport) { mapObjectToDataListWithIndexComplex(obj, excelDynamics, valueCache, index, dataList); } ExcelData excelData = new ExcelData(); excelData.setDataList(dataList); excelData.setHeadList(headList); excelData.setMergeRowIndex(headList.get(0).size()); excelData.setMergeColumnIndex(getMergeColumnIndex(excelDynamics)); return excelData; } /** * 需要合并的列 * @param excelDynamics * @return */ private int[] getMergeColumnIndex(List<ExcelDynamic> excelDynamics) { List<Integer> mergeColumnIndex = new ArrayList<>(); // 给一个默认的0值 mergeColumnIndex.add(0); for (int i = 0; i < excelDynamics.size(); i++) { if (excelDynamics.get(i).getIsMerge() == 1) { mergeColumnIndex.add(i + 1); } } return mergeColumnIndex.stream().mapToInt(Integer::intValue).toArray(); } /** * 数据的组合 * @param obj 一个item数据 * @param excelDynamics 动态数据表 * @param valueCache 缓存字典 * @param index 序号 * @param dataListAll 最终的数据列表 */ private void mapObjectToDataListWithIndexComplex(Object obj, List<ExcelDynamic> excelDynamics, Map<String, String> valueCache, AtomicInteger index, List<List<String>> dataListAll) { // 提取 propertyName 中带有.的字段 Set<String> complexList = excelDynamics.stream() .map(ExcelDynamic::getPropertyName) .filter(propertyName -> propertyName.contains(".")) .map(propertyName -> propertyName.substring(0, propertyName.lastIndexOf("."))) .filter(StrUtil::isNotBlank) .collect(Collectors.toSet()); // 获取每个数组中长度最长的一个 int complexListLength = complexList.stream() .mapToInt(complex -> { List<?> itemListTemp = (List<?>) ReflectUtil.getFieldValue(obj, complex); return (itemListTemp != null) ? itemListTemp.size() : 0; }) .max() .orElse(0); // 拿到全部的列表数据 Map<String, List<?>> allListMap = complexList.stream() .collect(Collectors.toMap( complex -> complex, complex -> { List<?> itemListTemp = (List<?>) ReflectUtil.getFieldValue(obj, complex); return CollectionUtils.isNotEmpty(itemListTemp) ? itemListTemp : new ArrayList<>(); })); // 关键的拼接组装数据 IntStream.range(0, complexListLength > 0 ? complexListLength : 1) .forEach(j -> { List<String> dataList = new ArrayList<>(); // 添加序号 dataList.add(String.valueOf(index.get())); excelDynamics.forEach(excelDynamic -> { String propertyName = excelDynamic.getPropertyName(); // 通过是否有.来判断是否是嵌套的内容,如果是那么就执行不一样的操作 if (propertyName.contains(".")) { // 获取到嵌套的列表 String listKeyName = propertyName.substring(0, propertyName.lastIndexOf(".")); List<?> objects = allListMap.get(listKeyName); // 这里需要判断这个列表的长度是否是正常的,我们直接通过判断是否循环超过最大数组的长度即可,如果超过了,那么就只需要填充空字符串即可 String fieldStringValue = (j < objects.size()) ? getFieldStringValue(ReflectUtil.getFieldValue(objects.get(j), propertyName.substring(propertyName.lastIndexOf(".") + 1)), excelDynamic, valueCache) : ""; dataList.add(fieldStringValue); } else { String fieldStringValue = getFieldStringValue(ReflectUtil.getFieldValue(obj, propertyName), excelDynamic, valueCache); dataList.add(fieldStringValue); } }); dataListAll.add(dataList); }); // 序号自增 index.getAndIncrement(); } /** * 获取头部列表 * * @param excelDynamics * @param headList * @return */ private List<List<String>> getHeadList(List<ExcelDynamic> excelDynamics, List<List<String>> headList) { if (isHeaderName(excelDynamics) != null) { headList.add(getIndex(excelDynamics)); headList.addAll(excelDynamics.stream() .map(d -> Arrays.asList(d.getHeader().split(","))) .collect(Collectors.toList())); } else { headList = Stream.concat(Stream.of(Collections.singletonList("序号")), excelDynamics.stream().map(d -> Collections.singletonList(d.getFieldName()))) .collect(Collectors.toList()); } return headList; } /** * 写出数据 * @param file * @param excelData * @param fileName * @param horizontalCellStyleStrategy */ private void excelWrite(ExcelWriterBuilder file, ExcelData excelData, String fileName, HorizontalCellStyleStrategy horizontalCellStyleStrategy) { file .head(excelData.getHeadList()) .sheet(fileName) .registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new ExcelMergeUtil(excelData.getMergeRowIndex(), excelData.getMergeColumnIndex())) // 第一列为序号列,宽度固定为 20 .registerWriteHandler(new CustomCellWriteHandler(0, 15)) .doWrite(excelData.getDataList()); } /** * 设置内容的样式 * @return */ private WriteCellStyle getWriteStyle() { WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 设置内容水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //自动换行 contentWriteCellStyle.setWrapped(true); //垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置左边框 contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置右边框 contentWriteCellStyle.setBorderRight(BorderStyle.THIN); //设置上边框 contentWriteCellStyle.setBorderTop(BorderStyle.THIN); //设置下边框 contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); return contentWriteCellStyle; } /** * 获取头的样式 * * @return */ private WriteCellStyle getHeadStyle() { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 12); headWriteCellStyle.setWriteFont(headWriteFont); return headWriteCellStyle; } /** * 单独设置序号的样式 */ class CustomCellWriteHandler extends AbstractCellWriteHandler { private final int columnIndex; private final int width; public CustomCellWriteHandler(int columnIndex, int width) { this.columnIndex = columnIndex; this.width = width; } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if (isHead && columnIndex == cell.getColumnIndex()) { cell.getSheet().setColumnWidth(cell.getColumnIndex(), width * 256); } } } public String isHeaderName(List<ExcelDynamic> excelDynamics) { if (CollectionUtils.isEmpty(excelDynamics)) { return null; } String functionName = excelDynamics.get(0).getHeader(); if (StrUtil.isBlank(functionName)) { return null; } return functionName; } /** * 获取序号 * * @param excelDynamics * @return */ public List<String> getIndex(List<ExcelDynamic> excelDynamics) { String headerName = isHeaderName(excelDynamics); if (headerName == null) { return Collections.singletonList("序号"); } // 该字段是,分割的,获取数组长度 List<String> headerList = Arrays.asList(headerName.split(",")); List<String> index = new ArrayList<>(); for (int i = 0; i < headerList.size(); i++) { index.add("序号"); } return index; } /** * 获取字典值 * * @param value * @param excelDynamic * @return */ private String getFieldStringValue(Object value, ExcelDynamic excelDynamic, Map<String, String> valueCache) { try { if (value == null) { return ""; } if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.DATE.getCode())) { // 时间模式 使用 hutool return DateUtil.format((Date) value, excelDynamic.getAdditionalData()); } else if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.MONEY.getCode())) { // 金额模式 return roundIfTrailingZeros((BigDecimal) value).toString(); } else { return value.toString(); } } catch (ClassCastException e) { throw new RuntimeException("动态转换出错了!" + excelDynamic.getPropertyName(), e); } } /** * 去除小数点后面的0 * * @param num * @return */ public static BigDecimal roundIfTrailingZeros(BigDecimal num) { return new BigDecimal(num.stripTrailingZeros().toPlainString()); } }
2.合并相同字段的工具类
点击查看完整内容
import com.alibaba.excel.EasyExcel; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.List; /** * @author DBC * @date 2023/11/17 15:25 * @network dbc655.top * 自由合并单元格 */ public class ExcelMergeUtil implements CellWriteHandler { private int[] mergeColumnIndex; private Integer mergeRowIndex; public ExcelMergeUtil() { } public ExcelMergeUtil(Integer mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); // 将当前单元格数据与上一个单元格数据比较 Boolean dataBool = preData.equals(curData); // 此处需要注意:我们默认有序号,通过序号进行合并,我们只需要维护好序号即可! Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue()); if (dataBool && bool) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并,则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } }
3.相关类更新,以及数据库表的更新
实体类:ExcelDynamic
@Data public class ExcelDynamic { @TableId private Long id; /** * 功能编号 */ private Integer functionNumber; /** * 功能名字 */ private String functionName; /** * 表头 (预留) */ private String header; /** * 字段名 中文 */ private String fieldName; /** * 字段名 */ private String propertyName; /** * 字段类型 */ private Integer type; /** * 额外标识 */ private String additionalData; /** * 排序 */ private Integer sort; /** * 是否需要合并 */ private Integer isMerge; }
构造导出类:ExcelData
@Data public class ExcelData { private List<List<String>> headList; private List<List<String>> dataList; private Integer mergeRowIndex; private int[] mergeColumnIndex; }
动态导出表
CREATE TABLE `excel_dynamic` ( `id` bigint(22) NOT NULL AUTO_INCREMENT, `function_number` int(5) NOT NULL COMMENT '功能编号', `function_name` varchar(50) DEFAULT NULL COMMENT '功能名字', `header` varchar(255) DEFAULT NULL COMMENT '复杂表头使用', `field_name` varchar(50) DEFAULT NULL COMMENT '字段名字', `property_name` varchar(50) DEFAULT NULL COMMENT '标记属性名', `type` int(2) DEFAULT NULL COMMENT '类型(1:字符串 2:时间 3:金额 4:枚举)', `additional_data` varchar(20) DEFAULT NULL COMMENT '额外操作如:yyyy-mm-dd 或 枚举相关', `sort` int(5) unsigned DEFAULT NULL COMMENT '排序', `is_merge` int(1) DEFAULT '0' COMMENT '是否需要合并', PRIMARY KEY (`id`), KEY `index_function_number` (`function_number`) ) ENGINE=InnoDB AUTO_INCREMENT=1739178131555749921 DEFAULT CHARSET=utf8 COMMENT='动态Excel';
示例代码
List<UserDO> list = userService.list(); List<UserVO> voList = new ArrayList<>(); for (UserDO userDO : list) { UserVO userVO = new UserVO(); userVO.setId(userDO.getId()); userVO.setName1(userDO.getName() +1); userVO.setName2(userDO.getName() +2); userVO.setName3(userDO.getName()+3); userVO.setName4(userDO.getName()+4); userVO.setName5(userDO.getName()+5); List<UserVO2> userVOList = new ArrayList<>(); List<UserVO2> userVOList2 = new ArrayList<>(); for (int i = 0; i < 3; i++) { UserVO2 userVO2 = new UserVO2(); userVO2.setId(userDO.getId()); userVO2.setName1(userDO.getName() +"我是需要合并的1号"); userVO2.setName4(userDO.getName()+"我是需要合并的4号"); userVO2.setName5(userDO.getName()+"我是需要合并的5号"); userVOList.add(userVO2); } for (int i = 0; i < 10; i++) { UserVO2 userVO2 = new UserVO2(); userVO2.setId(userDO.getId()); userVO2.setName1(userDO.getName() +"我是第二块需要合并的1号"); userVO2.setName2(userDO.getName() +"我是第二块需要合并的2号"); userVO2.setName3(userDO.getName()+"我是第二块需要合并的3号"); userVO2.setName4(userDO.getName()+"我是第二块需要合并的4号"); userVO2.setName5(userDO.getName()+"我是第二块需要合并的5号"); userVOList2.add(userVO2); } userVO.setUserVOList(userVOList); userVO.setUserVOList2(userVOList2); voList.add(userVO); } excelDynamicsService.getExcel("测试动态导出", "C:\\Users\\dbc\\Desktop\\project\\test\\"+ IdUtil.getSnowflakeNextIdStr()+".xlsx", voList,1);
数据库配置
最终导出效果
三、兼容百万数据导出
1.关键核心导出方法
点击查看完整内容
@Service @Slf4j public class ExcelDynamicsService extends ServiceImpl<ExcelDynamicMapper, ExcelDynamic> { @Resource ExcelDynamicMapper excelDynamicMapper; /** * 导出excel * * @param fileName 文件名 * @param file 文件 * @param listExport 导出的数据 * @param functionNumber 功能号 */ public void getExcel(String fileName, File file, List<?> listExport, int functionNumber) { excelWrite(EasyExcel.write(file), generateExcelData(listExport, functionNumber), fileName, new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())); } /** * 导出excel * * @param fileName 文件名 * @param fileUrl 导出文件地址 * @param listExport 导出的数据 * @param functionNumber 功能号 */ public void getExcel(String fileName, String fileUrl, List<?> listExport, int functionNumber) { excelWrite(EasyExcel.write(fileUrl), generateExcelData(listExport, functionNumber), fileName, new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())); } /** * 导出excel * * @param fileUrl 导出的数据 */ public ExcelData getBigExcel(String fileUrl, ExcelData excelData) { // 定义基础信息 basicInformation(excelData); ExcelWriter excelWriter = EasyExcel.write(fileUrl) .head(excelData.getHeadList()) .registerWriteHandler(new HorizontalCellStyleStrategy(getHeadStyle(), getWriteStyle())) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new ExcelMergeUtil(excelData.getMergeRowIndex(), excelData.getMergeColumnIndex())) .registerWriteHandler(new CustomCellWriteHandler(0, 15)) .build(); excelData.setExcelWriter(excelWriter); return excelData; } /** * 定义基础信息 * @param excelData */ private void basicInformation(ExcelData excelData) { excelData.setHeadList(getHeadList(excelData.getExcelDynamics())); excelData.setMergeRowIndex(excelData.getHeadList().get(0).size()); excelData.setMergeColumnIndex(getMergeColumnIndex(excelData.getExcelDynamics())); } /** * 导出excel * * @param fileName 文件名 * @param listExport 导出的数据 */ public ExcelData getBigExcel(String fileName, List<?> listExport,ExcelData excelData) { List<List<String>> dataList = new ArrayList<>(); Map<String, String> valueCache = new HashMap<>(); for (Object obj : listExport) { mapObjectToDataListWithIndexComplex(obj, excelData.getExcelDynamics(), valueCache, excelData.getIndex(), dataList); } excelData.getExcelWriter().write(dataList,EasyExcel.writerSheet(fileName).build()); return excelData; } /** * 生成excel数据 —— 复杂版本 * * @param listExport * @param functionNumber * @return */ public ExcelData generateExcelData(List<?> listExport, int functionNumber) { List<ExcelDynamic> excelDynamics = excelDynamicMapper.selectList( new QueryWrapper<ExcelDynamic>().lambda() .eq(ExcelDynamic::getFunctionNumber, functionNumber) .orderByAsc(ExcelDynamic::getSort) ); List<List<String>> headList = getHeadList(excelDynamics); List<List<String>> dataList = new ArrayList<>(); Map<String, String> valueCache = new HashMap<>(); AtomicInteger index = new AtomicInteger(1); for (Object obj : listExport) { mapObjectToDataListWithIndexComplex(obj, excelDynamics, valueCache, index, dataList); } ExcelData excelData = new ExcelData(); excelData.setDataList(dataList); excelData.setHeadList(headList); excelData.setMergeRowIndex(headList.get(0).size()); excelData.setMergeColumnIndex(getMergeColumnIndex(excelDynamics)); return excelData; } /** * 需要合并的列 * @param excelDynamics * @return */ private int[] getMergeColumnIndex(List<ExcelDynamic> excelDynamics) { List<Integer> mergeColumnIndex = new ArrayList<>(); // 给一个默认的0值 mergeColumnIndex.add(0); for (int i = 0; i < excelDynamics.size(); i++) { if (excelDynamics.get(i).getIsMerge() == 1) { mergeColumnIndex.add(i + 1); } } return mergeColumnIndex.stream().mapToInt(Integer::intValue).toArray(); } /** * 数据的组合 * @param obj 一个item数据 * @param excelDynamics 动态数据表 * @param valueCache 缓存字典 * @param index 序号 * @param dataListAll 最终的数据列表 */ private void mapObjectToDataListWithIndexComplex(Object obj, List<ExcelDynamic> excelDynamics, Map<String, String> valueCache, AtomicInteger index, List<List<String>> dataListAll) { // 提取 propertyName 中带有.的字段 Set<String> complexList = excelDynamics.stream() .map(ExcelDynamic::getPropertyName) .filter(propertyName -> propertyName.contains(".")) .map(propertyName -> propertyName.substring(0, propertyName.lastIndexOf("."))) .filter(StrUtil::isNotBlank) .collect(Collectors.toSet()); // 获取每个数组中长度最长的一个 int complexListLength = complexList.stream() .mapToInt(complex -> { List<?> itemListTemp = (List<?>) ReflectUtil.getFieldValue(obj, complex); return (itemListTemp != null) ? itemListTemp.size() : 0; }) .max() .orElse(0); // 拿到全部的列表数据 Map<String, List<?>> allListMap = complexList.stream() .collect(Collectors.toMap( complex -> complex, complex -> { List<?> itemListTemp = (List<?>) ReflectUtil.getFieldValue(obj, complex); return CollectionUtils.isNotEmpty(itemListTemp) ? itemListTemp : new ArrayList<>(); })); // 关键的拼接组装数据 IntStream.range(0, complexListLength > 0 ? complexListLength : 1) .forEach(j -> { List<String> dataList = new ArrayList<>(); // 添加序号 dataList.add(String.valueOf(index.get())); excelDynamics.forEach(excelDynamic -> { String propertyName = excelDynamic.getPropertyName(); // 通过是否有.来判断是否是嵌套的内容,如果是那么就执行不一样的操作 if (propertyName.contains(".")) { // 获取到嵌套的列表 String listKeyName = propertyName.substring(0, propertyName.lastIndexOf(".")); List<?> objects = allListMap.get(listKeyName); // 这里需要判断这个列表的长度是否是正常的,我们直接通过判断是否循环超过最大数组的长度即可,如果超过了,那么就只需要填充空字符串即可 String fieldStringValue = (j < objects.size()) ? getFieldStringValue(ReflectUtil.getFieldValue(objects.get(j), propertyName.substring(propertyName.lastIndexOf(".") + 1)), excelDynamic, valueCache) : ""; dataList.add(fieldStringValue); } else { String fieldStringValue = getFieldStringValue(ReflectUtil.getFieldValue(obj, propertyName), excelDynamic, valueCache); dataList.add(fieldStringValue); } }); dataListAll.add(dataList); }); // 序号自增 index.getAndIncrement(); } /** * 获取头部列表 * * @param excelDynamics * @return */ private List<List<String>> getHeadList(List<ExcelDynamic> excelDynamics) { List<List<String>> headList = new ArrayList<>(); if (isHeaderName(excelDynamics) != null) { headList.add(getIndex(excelDynamics)); headList.addAll(excelDynamics.stream() .map(d -> Arrays.asList(d.getHeader().split(","))) .collect(Collectors.toList())); } else { headList = Stream.concat(Stream.of(Collections.singletonList("序号")), excelDynamics.stream().map(d -> Collections.singletonList(d.getFieldName()))) .collect(Collectors.toList()); } return headList; } public List<ExcelDynamic> getExcelDynamics(Integer functionNumber) { List<ExcelDynamic> excelDynamics; excelDynamics = excelDynamicMapper.selectList( new QueryWrapper<ExcelDynamic>().lambda() .eq(ExcelDynamic::getFunctionNumber, functionNumber) .orderByAsc(ExcelDynamic::getSort) ); return excelDynamics; } /** * 写出数据 * @param file * @param excelData * @param fileName * @param horizontalCellStyleStrategy */ private void excelWrite(ExcelWriterBuilder file, ExcelData excelData, String fileName, HorizontalCellStyleStrategy horizontalCellStyleStrategy) { file .head(excelData.getHeadList()) .sheet(fileName) .registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new ExcelMergeUtil(excelData.getMergeRowIndex(), excelData.getMergeColumnIndex())) // 第一列为序号列,宽度固定为 20 .registerWriteHandler(new CustomCellWriteHandler(0, 15)) .doWrite(excelData.getDataList()); } /** * 设置内容的样式 * @return */ private WriteCellStyle getWriteStyle() { WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 设置内容水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //自动换行 contentWriteCellStyle.setWrapped(true); //垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置左边框 contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置右边框 contentWriteCellStyle.setBorderRight(BorderStyle.THIN); //设置上边框 contentWriteCellStyle.setBorderTop(BorderStyle.THIN); //设置下边框 contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); return contentWriteCellStyle; } /** * 获取头的样式 * * @return */ private WriteCellStyle getHeadStyle() { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 12); headWriteCellStyle.setWriteFont(headWriteFont); return headWriteCellStyle; } /** * 单独设置序号的样式 */ class CustomCellWriteHandler extends AbstractCellWriteHandler { private final int columnIndex; private final int width; public CustomCellWriteHandler(int columnIndex, int width) { this.columnIndex = columnIndex; this.width = width; } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if (isHead && columnIndex == cell.getColumnIndex()) { cell.getSheet().setColumnWidth(cell.getColumnIndex(), width * 256); } } } public String isHeaderName(List<ExcelDynamic> excelDynamics) { if (CollectionUtils.isEmpty(excelDynamics)) { return null; } String functionName = excelDynamics.get(0).getHeader(); if (StrUtil.isBlank(functionName)) { return null; } return functionName; } /** * 获取序号 * * @param excelDynamics * @return */ public List<String> getIndex(List<ExcelDynamic> excelDynamics) { String headerName = isHeaderName(excelDynamics); if (headerName == null) { return Collections.singletonList("序号"); } // 该字段是,分割的,获取数组长度 List<String> headerList = Arrays.asList(headerName.split(",")); List<String> index = new ArrayList<>(); for (int i = 0; i < headerList.size(); i++) { index.add("序号"); } return index; } /** * 获取字典值 * * @param value * @param excelDynamic * @return */ private String getFieldStringValue(Object value, ExcelDynamic excelDynamic, Map<String, String> valueCache) { try { if (value == null) { return ""; } if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.DATE.getCode())) { // 时间模式 使用 hutool return DateUtil.format((Date) value, excelDynamic.getAdditionalData()); } else if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.MONEY.getCode())) { // 金额模式 return roundIfTrailingZeros((BigDecimal) value).toString(); } else if (Objects.equals(excelDynamic.getType(), ExcelDynamicsTypeEnum.ENUM.getCode())) { // // 枚举模式 // return getValue(excelDynamic.getAdditionalData(), value.toString(),valueCache); } else { return value.toString(); } } catch (ClassCastException e) { throw new RuntimeException("动态转换出错了!" + excelDynamic.getPropertyName(), e); } return null; } /** * 去除小数点后面的0 * * @param num * @return */ public static BigDecimal roundIfTrailingZeros(BigDecimal num) { return new BigDecimal(num.stripTrailingZeros().toPlainString()); }
略微优化过后的合并工具类
点击查看完整内容
/** * @author DBC * @date 2023/11/17 15:25 * @network dbc655.top * 自由合并单元格 */ public class ExcelMergeUtil implements CellWriteHandler { private int[] mergeColumnIndex; private Integer mergeRowIndex; public ExcelMergeUtil() { } public ExcelMergeUtil(Integer mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { System.out.println("当前行:" + curRowIndex + "当前列:" + curColIndex); Sheet sheet = writeSheetHolder.getSheet(); Row curRow = cell.getRow(); // 获取当前单元格数据 Object curData = getCellValue(cell); // 获取前一行相同列的单元格 Row preRow = sheet.getRow(curRowIndex - 1); Cell preCell = preRow.getCell(curColIndex); Object preData = getCellValue(preCell); // 检查序号是否相同 boolean bool = curRow.getCell(0).getStringCellValue().equals(preRow.getCell(0).getStringCellValue()); // 将当前单元格数据与上一个单元格数据比较 boolean dataBool = preData.equals(curData); if (dataBool && bool) { List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); Map<String, Boolean> mergedMap = new HashMap<>(); for (CellRangeAddress cellRangeAddr : mergeRegions) { // 使用字符串表示形式作为键 String key = cellRangeAddr.formatAsString(); mergedMap.put(key, true); if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(mergeRegions.indexOf(cellRangeAddr)); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); // 如果合并,则提前退出 return; } } // 如果尚未合并,添加新的合并区域 if (!mergedMap.containsKey(new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex).formatAsString())) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } private Object getCellValue(Cell cell) { return cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); } }
3.略微有变动的导出实体
@Data public class ExcelData { private List<List<String>> headList; private List<List<String>> dataList; private Integer mergeRowIndex; private int[] mergeColumnIndex; private AtomicInteger index = new AtomicInteger(1); private ExcelWriter excelWriter; private List<ExcelDynamic> excelDynamics; }
4.基本示例代码以及导出部分截图
@GetMapping("/detail") public void test() throws NoSuchFieldException, IllegalAccessException { List<UserDO> list = userService.list(); List<UserVO> voList = new ArrayList<>(); for (UserDO userDO : list) { UserVO userVO = new UserVO(); userVO.setId(userDO.getId()); userVO.setName1(userDO.getName() +1); userVO.setName2(userDO.getName() +2); userVO.setName3(userDO.getName()+3); userVO.setName4(userDO.getName()+4); userVO.setName5(userDO.getName()+5); List<UserVO2> userVOList = new ArrayList<>(); List<UserVO2> userVOList2 = new ArrayList<>(); for (int i = 0; i < 3; i++) { UserVO2 userVO2 = new UserVO2(); userVO2.setId(userDO.getId()); userVO2.setName1(userDO.getName() +"我是需要合并的1号" + i); userVO2.setName2(userDO.getName() +"我是第二块需要合并的2号"+ i); userVO2.setName3(userDO.getName()+"我是第二块需要合并的3号"+ i); userVO2.setName4(userDO.getName()+"我是需要合并的4号"+ i); userVO2.setName5(userDO.getName()+"我是需要合并的5号"+ i); userVOList.add(userVO2); } for (int i = 0; i < 10; i++) { UserVO2 userVO2 = new UserVO2(); userVO2.setId(userDO.getId()); userVO2.setName1(userDO.getName() +"我是第二块需要合并的1号"+ i); userVO2.setName2(userDO.getName() +"我是第二块需要合并的2号"+ i); userVO2.setName3(userDO.getName()+"我是第二块需要合并的3号"+ i); userVO2.setName4(userDO.getName()+"我是第二块需要合并的4号"+ i); userVO2.setName5(userDO.getName()+"我是第二块需要合并的5号"+ i); userVOList2.add(userVO2); } userVO.setUserVOList(userVOList); userVO.setUserVOList2(userVOList2); for (int i = 0; i < 100; i++) { voList.add(userVO); } } ExcelData excelData = new ExcelData(); excelData.setExcelDynamics(excelDynamicsService.getExcelDynamics(1)); try { excelDynamicsService.getBigExcel("D:\\jar\\" + IdUtil.getSnowflakeNextIdStr() + ".xlsx", excelData); for (int i = 0; i < 1; i++) { excelDynamicsService.getBigExcel("测试动态导出",voList,excelData); System.out.println("这是第"+i+"次"); } }finally { if (excelData.getExcelWriter() != null){ excelData.getExcelWriter().finish(); } } }
四、接近最终版本——99%完成度(仅差设计模式重构优化逻辑)
1.完整关键合并类
点击查看完整内容
import cn.hutool.core.collection.CollUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.handler.WorkbookWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.example.test05.demo.model.ExcelSheet; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.*; import java.util.stream.Collectors; /** * @author DBC * @date 2024/01/25 15:25 * @network dbc655.top * 自由合并单元格 */ public class ExcelMergeUtil implements CellWriteHandler, WorkbookWriteHandler { private int[] mergeColumnIndex; private Integer mergeRowIndex; // 合并的单元格map private ThreadLocal
示例代码和导出截图
示例代码
List<UserDO> list = userService.list(); List<UserVO> voList = new ArrayList<>(); for (UserDO userDO : list) { UserVO userVO = new UserVO(); userVO.setId(userDO.getId()); userVO.setName1(userDO.getName() + 1); userVO.setName2(userDO.getName() + 2); userVO.setName3(userDO.getName() + 3); userVO.setName4(userDO.getName() + 4); userVO.setName5(userDO.getName() + 5); List<UserVO2> userVOList = new ArrayList<>(); List<UserVO2> userVOList2 = new ArrayList<>(); for (int i = 0; i < 10; i++) { UserVO2 userVO2 = new UserVO2(); userVO2.setId(userDO.getId()); userVO2.setName1(userDO.getName() + "我是需要合并的1号" + i); userVO2.setName2(userDO.getName() + "我是第二块需要合并的2号" + i); userVO2.setName3(userDO.getName() + "我是第二块需要合并的3号" + i); userVO2.setName4(userDO.getName() + "我是需要合并的4号" + i); userVO2.setName5(userDO.getName() + "我是需要合并的5号" + i); userVOList.add(userVO2); } for (int i = 0; i < 20; i++) { UserVO2 userVO2 = new UserVO2(); userVO2.setId(userDO.getId()); userVO2.setName1(userDO.getName() + "我是第二块需要合并的1号" + i); userVO2.setName2(userDO.getName() + "我是第二块需要合并的2号" + i); userVO2.setName3(userDO.getName() + "我是第二块需要合并的3号" + i); userVO2.setName4(userDO.getName() + "我是第二块需要合并的4号" + i); userVO2.setName5(userDO.getName() + "我是第二块需要合并的5号" + i); userVOList2.add(userVO2); } userVO.setUserVOList(userVOList); userVO.setUserVOList2(userVOList2); for (int i = 0; i < 20; i++) { voList.add(userVO); } } ExcelData excelData = new ExcelData(); excelData.setExcelDynamics(excelDynamicsService.getExcelDynamics(1)); // 开始时间 long begin = System.currentTimeMillis(); try { excelDynamicsService.getBigExcel("D:\\jar\\" + IdUtil.getSnowflakeNextIdStr() + ".xlsx", excelData); for (int i = 0; i < 10; i++) { excelDynamicsService.getBigExcel("测试动态导出", voList, excelData); System.out.println("这是第" + (i+1) + "次"); } } finally { if (excelData.getExcelWriter() != null) { excelData.getExcelWriter().finish(); // 结束时间 long end = System.currentTimeMillis(); // 耗时 System.out.println("耗时:" + (end - begin) + "ms"); ExcelSheet excelSheet = excelSheetThreadLocal.get(); excelSheetThreadLocal.remove(); } }
导出截图
五、通过数据库的配置类转换为前端所需要的表头
1.前端获取表头,后端代码
前端所需的model类
DynamicsHead
@Data public class DynamicsHead { private String id; private String prop; private String label; private List<DynamicsHead> children; }
解析配置相关代码
/** * 获取头部列表 * * @return */ public List<DynamicsHead> getDynamicHeadList(Integer id) { List<ExcelDynamic> excelDynamics = getBaseMapper().selectList( new QueryWrapper<ExcelDynamic>().lambda() .eq(ExcelDynamic::getFunctionNumber, id) .orderByAsc(ExcelDynamic::getSort) ); return buildDynamicsHeads(excelDynamics); } public static List<DynamicsHead> buildDynamicsHeads(List<ExcelDynamic> excelDynamics) { Map<String, DynamicsHead> headMap = new HashMap<>(); List<DynamicsHead> dynamicsHeads = new ArrayList<>(); for (ExcelDynamic excelDynamic : excelDynamics) { String[] headers = excelDynamic.getHeader().split(","); addHeaders(dynamicsHeads, headers, 0, headMap, "", excelDynamic); } removeRedundantChildren(dynamicsHeads); return dynamicsHeads; } private static void removeRedundantChildren(List<DynamicsHead> dynamicsHeads) { Iterator<DynamicsHead> iterator = dynamicsHeads.iterator(); while (iterator.hasNext()) { DynamicsHead dynamicsHead = iterator.next(); // 递归调用检查子节点 removeRedundantChildren(dynamicsHead.getChildren()); // 检查子节点是否只有一个,并且子节点的label与该节点的label相同 if (dynamicsHead.getChildren().size() == 1) { DynamicsHead onlyChild = dynamicsHead.getChildren().get(0); if (onlyChild.getLabel().equals(dynamicsHead.getLabel())) { dynamicsHead.setChildren(onlyChild.getChildren()); dynamicsHead.setProp(onlyChild.getProp()); } } } }
2.前端代码
点击查看完整内容
<template> <el-table-column :label="columnHeader.label" :prop="columnHeader.label" align="center" > <template v-for="item in columnHeader.children"> <tableColumn v-if="item.children && item.children.length" :key="item.id" :column-header="item"></tableColumn> <el-table-column v-else :key="item.name" :label="item.label" :prop="item.prop" align="center"></el-table-column> </template> </el-table-column> </template> <script> export default { name: "tableColumn", props: { columnHeader: { type: Object, required: true } } }; </script> <style scoped lang='less'> </style>
<template> <el-table :data="tableData" :height="height" v-loading="tableLoading"> <template v-for="item in tableHeader"> <table-column v-if="item.children && item.children.length" :key="item.id" :column-header="item"></table-column> <el-table-column v-else :key="item.id" :label="item.label" :prop="item.prop" align="center"></el-table-column> </template> </el-table> </template> <script> import tableColumn from "./table-column"; export default { props: { tableLoading: false, // 表格的数据 tableData: { type: Array, required: true }, // 多级表头的数据 tableHeader: { type: Array, required: true }, // 表格的高度 height: { type: String }, }, components: { tableColumn } }; </script> <style scoped lang='less'> </style>
<div class="top"> <multi-level-table v-loading="tableLoading" :table-data="tableData" :table-header="columnList"></multi-level-table> </div>
六、通过Excel文件,自动获取所需的配置数据
完整前端代码
<template> <div style="padding: 20px"> <el-alert title="操作说明" type="info" closable show-icon center effect="light" > 目前已有动态操作:字符串、时间、金钱、枚举字典 <br> 一、type 1 字符串 <br> 二、type 2 时间 选择对应的时间格式 1:yyyy-MM-dd 2:yyyy-MM-dd HH:mm:ss <br> 三、type 3 金钱 <br> 四、type 4 枚举字典 选择对应的字典名称 </el-alert> <div style="margin-bottom: 16px; margin-top: 16px;"> <el-row :gutter="20"> <el-col :span="8"> <el-input v-model="defaultFunctionCode" placeholder="请输入功能编号" clearable> <template slot="prepend">功能编号</template> </el-input> </el-col> <el-col :span="8"> <el-input v-model="defaultFunctionName" placeholder="请输入功能名字" clearable> <template slot="prepend">功能名字</template> </el-input> </el-col> </el-row> </div> <div style="margin-bottom: 16px; display: flex; align-items: center;"> <el-input-number v-model="headerRows" :min="1" label="表头行数" style="margin-right: 16px; width: 120px;" /> <el-upload class="upload-excel" action="#" :auto-upload="false" :on-change="handleFileChange" :show-file-list="false" accept=".xlsx,.xls"> <el-button type="primary">选择Excel文件并自动填充表格</el-button> </el-upload> </div> <el-table :data="tableData" style="width: 100%"> <el-table-column label="功能编号" prop="functionCode"> <template slot-scope="scope"> <el-input v-model="scope.row.functionCode"></el-input> </template> </el-table-column> <el-table-column label="功能名字" prop="functionName"> <template slot-scope="scope"> <el-input v-model="scope.row.functionName"></el-input> </template> </el-table-column> <el-table-column label="表头" prop="header"> <template slot-scope="scope"> <el-input v-model="scope.row.header"></el-input> </template> </el-table-column> <el-table-column label="字段中文名" prop="fieldChineseName"> <template slot-scope="scope"> <el-input v-model="scope.row.fieldChineseName"></el-input> </template> </el-table-column> <el-table-column label="字段名" prop="fieldName"> <template slot-scope="scope"> <el-input v-model="scope.row.fieldName"></el-input> </template> </el-table-column> <el-table-column type='number' label="字段类型" prop="fieldType"> <template slot-scope="scope"> <el-input v-model="scope.row.fieldType"></el-input> </template> </el-table-column> <el-table-column label="额外操作" prop="extraOperation"> <template slot-scope="scope"> <el-input v-model="scope.row.extraOperation"></el-input> </template> </el-table-column> <el-table-column label="排序" prop="sorting"> <template slot-scope="scope"> <el-input type='number' v-model="scope.row.sorting"></el-input> </template> </el-table-column> </el-table> <div style="margin-top: 20px"></div> <el-button type="primary" @click="addRow">添加行</el-button> <el-button type="success" @click="submitForm">提交</el-button> <div style="margin-bottom: 20px"></div> <el-card class="box-card"> <div slot="header" class="clearfix"> <span>返回结果</span> </div> <el-input :rows="20" type="textarea" placeholder="请输入内容" v-model="textarea"> </el-input> </el-card> </div> </template> <script> import {getSql} from "@/api/gxyf/gxyf"; import * as XLSX from 'xlsx' function getHeaderMatrix(worksheet, headerRows) { const range = XLSX.utils.decode_range(worksheet['!ref']); const matrix = []; // 读取表头行 for (let r = 0; r < headerRows; r++) { const row = []; for (let c = 0; c <= range.e.c; c++) { const cellAddress = XLSX.utils.encode_cell({ r, c }); const cell = worksheet[cellAddress]; if (cell && cell.v) { row.push(cell.v); } else { // 横向补全:向左找最近的非空 let leftVal = ''; for (let left = c - 1; left >= 0; left--) { if (row[left]) { leftVal = row[left]; break; } } row.push(leftVal); } } matrix.push(row); } // 读取字段名行(表头行数+1行) const fieldNames = []; const fieldNameRow = headerRows; // 因为是0基的索引,所以不需要+1 for (let c = 0; c <= range.e.c; c++) { const cellAddress = XLSX.utils.encode_cell({ r: fieldNameRow, c }); const cell = worksheet[cellAddress]; fieldNames.push(cell && cell.v ? cell.v : ''); } return { matrix, fieldNames }; } function getHeaderPaths(matrix) { // 计算每一列的完整表头路径 const colCount = matrix[0].length; const paths = []; for (let c = 0; c < colCount; c++) { const path = []; for (let r = 0; r < matrix.length; r++) { // 跳过被合并为空的单元格,向上查找 let val = matrix[r][c]; if (!val) { // 向上找最近的非空 for (let up = r - 1; up >= 0; up--) { if (matrix[up][c]) { val = matrix[up][c]; break; } } } path.push(val); } // 只保留最后一级非空(叶子) const leaf = path[path.length - 1]; // 组合成字符串 paths.push({ full: path.join(','), leaf }); } return paths; } export default { name: "Index", data() { return { tableData: [ // 初始表格数据 { functionCode: '', header:'', functionName: '', fieldChineseName: '', fieldName: '', fieldType: '1', extraOperation: '', sorting: 100 } ], textarea: '', headerRows: 2, defaultFunctionCode: '', defaultFunctionName: '' }; }, methods: { addRow() { // 添加新行 this.tableData.push({ functionCode: this.tableData[0].functionCode === '' ? '' :this.tableData[0].functionCode, functionName: this.tableData[0].functionName === '' ? '' :this.tableData[0].functionName, header:'', fieldChineseName: '', fieldName: '', fieldType: '1', extraOperation: '', sorting: (this.tableData.length + 1) * 100 }); }, submitForm() { // 生成SQL语句 let sql = ''; for (const item of this.tableData) { sql += `INSERT INTO \`excel_dynamic\`(\`function_number\`, \`function_name\`, \`header\`, \`field_name\`, ` + `\`property_name\`, \`type\`, \`additional_data\`, \`sort\`) VALUES` + ` ('${item.functionCode}', '${item.functionName}', '${item.header}', '${item.fieldChineseName}', ` + `'${item.fieldName}', ${item.fieldType}, '${item.extraOperation}', ${item.sorting});\n`; } this.textarea = sql; }, handleFileChange(file) { this.readExcel(file.raw); }, readExcel(file) { const reader = new FileReader(); reader.onload = (e) => { try { const data = e.target.result; const workbook = XLSX.read(data, { type: 'array' }); const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; const { matrix, fieldNames } = getHeaderMatrix(worksheet, this.headerRows); const paths = getHeaderPaths(matrix); // 自动填充表格数据 this.tableData = paths.map((item, idx) => ({ functionCode: this.defaultFunctionCode, functionName: this.defaultFunctionName, header: item.full, fieldChineseName: item.leaf, fieldName: fieldNames[idx] || '', fieldType: '1', extraOperation: '', sorting: (idx + 1) * 100 })); } catch (error) { console.error('读取Excel文件失败:', error); this.$message.error('读取Excel文件失败,请检查文件格式是否正确'); } }; reader.readAsArrayBuffer(file); } }, }; </script> <style scoped lang="scss"> </style>
本文作者为DBC,转载请注明。