EasyExcel导出导入excel工具类

接上一篇EasyExcel导出导入excel的文章，附上一份完整的工具类代码。对于字体颜色名称，请参考这篇文章。
POI字体颜色

小技巧

• 类转换用属性拷贝
• 不同类如果有相同属性，则使用反射验证，减少代码量

    private List<Person> validateAndSetTypeThenReturn(List<Long> list, List<?> objects, String key) {Consumer<Object> validator = item -> {try {Method setType = item.getClass().getMethod("setType", String.class);Method getId = item.getClass().getMethod("getId");// 设置类型setType.invoke(item, key);// 校验ID必填if (!list.contains((Long) getId.invoke(item))) {throw new ServiceException("无效的ID" + getId.invoke(item));}} catch (ServiceException ex) {throw new ServiceException(ex.getMessage());} catch (Exception e) {throw new ServiceException("数据验证失败");}};objects.forEach(validator);return BeanUtil.copyToList(objects, Person.class);}

• 遍历List不如遍历枚举

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
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.yfld.common.core.convert.ExcelBigNumberConvert;
import com.yfld.common.core.exception.ServiceException;
import lombok.Data;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;/*** @ClassName : ExcelUtilAdvance* @Description : excel高级工具*/
@Slf4j
public class ExcelUtilAdvance {/*** 导出多 Sheet 版本的 Excel 文件（动态生成，无需模板）** @param sheetDataMap Sheet 数据映射，key 为 Sheet 名称，value 为数据列表（列表元素需为同一 DTO 类型）* @param filename     导出的 Excel 文件名* @param response     HttpServletResponse* @throws IOException 文件操作异常*/public static void exportDynamicMultiSheet(LinkedHashMap<String, List<?>> sheetDataMap, String filename, HttpServletResponse response) {try {// 1. 设置 HTTP 响应头（文件类型和编码）setResponse(filename, response);// 2. 创建写入处理器-指定样式HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(createHeaderStyle(), createContentStyle());// 3. 创建 ExcelWriter 对象（自动关闭流）try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).autoCloseStream(true).registerConverter(new ExcelBigNumberConvert()).registerWriteHandler(styleStrategy)  // 样式处理器.registerWriteHandler(new AutoColumnWidthStyleStrategy())  // 自动列宽适配.registerWriteHandler(new ZoomScaleHandler(150))  // 缩放比例
//                    .registerWriteHandler(new FirstRedStarHeaderHandler())  // 表头着色器，将*号标记为红色.build()) {// 4. 遍历所有 Sheet 数据for (Map.Entry<String, List<?>> entry : sheetDataMap.entrySet()) {String sheetName = entry.getKey();List<?> dataList = entry.getValue();// 4.1 动态推断 DTO 类型（通过列表第一个元素）if (CollUtil.isEmpty(dataList)) {throw new IllegalArgumentException("Sheet [" + sheetName + "] 数据列表不能为空");}Class<?> dtoClass = dataList.get(0).getClass();// 4.2 创建 WriteSheet（动态绑定表头和样式）WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(dtoClass)          // 根据 DTO 类自动生成表头.needHead(Boolean.TRUE)  // 强制生成表头.build();// 4.3 写入数据（自动跳过空列表）excelWriter.write(dataList, writeSheet);}}} catch (IOException e) {throw new ServiceException("导出 Excel 失败：" + e.getMessage(), e);}}/*** 导入多 Sheet 版本的 Excel 文件，自动将每个 Sheet 映射到指定实体类** @param file         上传的 Excel 文件* @param sheetNameMap Sheet 配置信息，key 为 Sheet 名称，value 为对应实体类的 Class 对象* @return 解析后的数据 Map，key 为 Sheet 名称，value 为对应实体对象列表*/public static Map<String, List<?>> importDynamicMultiSheet(MultipartFile file, Map<String, Class<?>> sheetNameMap) {try {// 1. 创建 ExcelReader 对象（自动关闭流）try (ExcelReader excelReader = EasyExcel.read(file.getInputStream()).registerConverter(new ExcelBigNumberConvert()).autoCloseStream(true) // 确保流自动关闭.build()) {// 2. 初始化数据容器，存储每个 Sheet 的解析结果Map<String, List<?>> dataMap = new LinkedHashMap<>(); // 保持插入顺序// 3. 遍历 Sheet 配置信息for (Map.Entry<String, Class<?>> entry : sheetNameMap.entrySet()) {String sheetName = entry.getKey();Class<?> clazz = entry.getValue();// 4. 创建数据监听器（泛型安全）GenericSheetListener<Object> listener = new GenericSheetListener<>();// 5. 构建 ReadSheet（指定 Sheet 名称和表头类）ReadSheet readSheet = EasyExcel.readSheet(sheetName).head(clazz).registerReadListener(listener).headRowNumber(1) // 表头行数.build();try {// 6. 读取当前 Sheet 数据excelReader.read(readSheet);} catch (Exception e) {throw new RuntimeException("解析 Sheet [" + sheetName + "] 失败: " + e.getMessage(), e);}// 7. 将解析结果存入 Map（类型安全转换）dataMap.put(sheetName, convertToTypedList(listener.getDataList(), clazz));}return dataMap;}} catch (Exception e) {throw new RuntimeException("导入 Excel 失败：" + e.getMessage(), e);}}private static <T> List<T> convertToTypedList(List<Object> dataList, Class<T> clazz) {return dataList.stream().map(clazz::cast).collect(Collectors.toList());}/*** 创建统一的表头样式（灰色背景、居中、加粗）** @return WriteCellStyle*/private static WriteCellStyle createHeaderStyle() {WriteCellStyle style = new WriteCellStyle();// 表头背景色-青绿色style.setFillForegroundColor(IndexedColors.AQUA.getIndex());style.setWrapped(false);// 水平剧中style.setHorizontalAlignment(HorizontalAlignment.CENTER);// 自动调整列宽// 字体WriteFont font = new WriteFont();font.setBold(true);font.setFontName("宋体");font.setFontHeightInPoints((short) 11);
//        font.setColor(IndexedColors.BLACK.getIndex());style.setWriteFont(font);return style;}/*** 创建内容样式（中对齐、黑色字体）*/private static WriteCellStyle createContentStyle() {WriteCellStyle style = new WriteCellStyle();style.setHorizontalAlignment(HorizontalAlignment.CENTER);WriteFont font = new WriteFont();font.setFontName("宋体");font.setFontHeightInPoints((short) 10);style.setWriteFont(font);return style;}private static void setResponse(String filename, HttpServletResponse response) throws UnsupportedEncodingException {// 设置响应头-excel文件类型和文件名response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));}/*** 通用 Sheet 数据监听器（泛型类）** @param <T> 实体类型*/@Getterprivate static class GenericSheetListener<T> implements ReadListener<T> {private final List<T> dataList = new ArrayList<>();@Overridepublic void invoke(T data, AnalysisContext context) {if (StrUtil.isEmpty(data.toString())) {throw new ExcelAnalysisException("第 " + context.readRowHolder().getRowIndex() + " 行数据错误：不能为空");}dataList.add(data); // 收集每行数据}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 可在此添加后处理逻辑（如校验、日志）log.info("解析完成，共处理 {} 行数据", dataList.size());}}/*** 自动调整列宽，用于在写入完成后动态设置列宽*/public static class AutoColumnWidthStyleStrategy implements WorkbookWriteHandler {@Overridepublic void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {SXSSFWorkbook workbook = (SXSSFWorkbook) writeWorkbookHolder.getWorkbook();// 遍历所有Sheetfor (int i = 0; i < workbook.getNumberOfSheets(); i++) {SXSSFSheet sheet = workbook.getSheetAt(i);adjustColumnWidth(sheet);}}private void adjustColumnWidth(SXSSFSheet sheet) {sheet.trackAllColumnsForAutoSizing();// 获取第一行（表头）Row headerRow = sheet.getRow(0);if (headerRow == null) {log.error("表头行不存在，无法调整列宽");return;}int columnCount = headerRow.getLastCellNum();for (int i = 0; i < columnCount; i++) {// 自动调整列宽sheet.autoSizeColumn(i);// 解决自动设置列宽中文失效的问题
//                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);// 设置列宽范围（示例：10到50字符）int currentWidth = sheet.getColumnWidth(i);int minWidth = 10 * 256;  // 10字符int maxWidth = 50 * 256;  // 50字符if (currentWidth < minWidth) {sheet.setColumnWidth(i, minWidth);} else if (currentWidth > maxWidth) {sheet.setColumnWidth(i, maxWidth);}}}}/*** 自定义缩放比例处理器，用于在写入完成后设置 Sheet 的显示比例*/@Datapublic static class ZoomScaleHandler implements SheetWriteHandler {private final int zoomScale; // 缩放比例（如 150 表示 150%）@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();// 设置缩放比例（POI 的 setZoom 方法直接接受百分比值）sheet.setZoom(zoomScale); // 例如 150 表示 150%}}/*** 表头着色器*/public static class FirstRedStarHeaderHandler implements CellWriteHandler {@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList,Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {if (isHead && cell.getRow().getRowNum() == 0) { // 仅处理第一行表头// 1. 检查单元格类型和内容if (cell.getCellType() != CellType.STRING || cell.getStringCellValue() == null) {return;}String cellValue = cell.getStringCellValue();if (!cellValue.startsWith("*")) {return;}// 2. 获取工作簿和原始样式Workbook workbook = cell.getSheet().getWorkbook();CellStyle originalStyle = cell.getCellStyle();// 3. 创建红色字体（继承默认属性）Font defaultFont = workbook.getFontAt(originalStyle.getFontIndexAsInt());Font redFont = workbook.createFont();redFont.setFontName(defaultFont.getFontName());redFont.setFontHeightInPoints(defaultFont.getFontHeightInPoints());redFont.setBold(defaultFont.getBold());redFont.setColor(IndexedColors.RED.getIndex()); // 红色// 4. 构建富文本XSSFRichTextString richText = new XSSFRichTextString(cellValue);richText.applyFont(0, 1, redFont); // 第一个字符（*）为红色if (cellValue.length() > 1) {// 剩余字符保留默认样式richText.applyFont(1, cellValue.length(), defaultFont);}// 5. 应用富文本到单元格cell.setCellValue(richText);}}}
}