接上一篇EasyExcel导出导入excel的文章，附上一份完整的工具类代码。对于字体颜色名称，请参考这篇文章。
POI字体颜色
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.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
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.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.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(Map<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)) // 缩放比例.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 sheetMap Sheet 配置信息，key 为 Sheet 名称，value 为对应实体类的 Class 对象* @return 解析后的数据 Map，key 为 Sheet 名称，value 为对应实体对象列表* @throws IOException 文件读取异常*/public static Map<String, List<?>> importDynamicMultiSheet(MultipartFile file, Map<String, Class<?>> sheetMap) throws IOException {// 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 : sheetMap.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;}}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) 9);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%}}
}
_百度推广优化方案_郑州官网网络营销外包)
