Spring Boot + Easy Excel 自定义复杂样式导入导出

tips：能用模板就用模板，当模板不适用的情况下，再选择自定义生成 Excel。

官网：https://easyexcel.opensource.alibaba.com

安装

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

一、处理自定义导出复杂场景

1、列不固定，动态列
2、动态下拉
3、自定义锁定行/列，添加密码
4、合并单元格
5、导入自定义统一注解统一校验
6、样式处理（字体，颜色，底色，富文本，列宽，行宽等）
7、冻结窗格
8、多Sheet处理

1、列不固定，动态列

首先定义一个公共实体，处理公共字段和动态列字段，具体实体则继承该类即可。

package com.example.springbootexcel.excel.base.model;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;import java.util.List;
import java.util.Map;@Data
public class BaseExcel {@ExcelProperty( value = "序号")private String num;/*** 动态字段处理*/private List<Map<String, Object>> dynamicList;}

2、动态下拉

封装一个公共类，构造入参Map，key为表头，value为下拉字符串数组。
.registerWriteHandler(new DropDownHandler(dropDownMap));

package com.example.springbootexcel.excel.base.style;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;import java.util.Map;/*** 添加下拉选单** @author jason*/
public class DropDownHandler implements SheetWriteHandler {private final Map<Integer, String[]> dropDownMap;  // key:列号(从0开始), value:下拉数据public DropDownHandler(Map<Integer, String[]> dropDownMap) {this.dropDownMap = dropDownMap;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 不需要实现}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (dropDownMap == null || dropDownMap.isEmpty()) {return;}Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();dropDownMap.forEach((columnIndex, dropDownData) -> {// 设置下拉框数据范围 (这里设置从第2行到第10000行)CellRangeAddressList addressList = new CellRangeAddressList(1, 9999, columnIndex, columnIndex);// 创建数据验证约束DataValidationConstraint constraint = helper.createExplicitListConstraint(dropDownData);// 创建数据验证DataValidation validation = helper.createValidation(constraint, addressList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "请从下拉选项中选择");// 添加验证到sheetsheet.addValidationData(validation);});}
}

3、自定义锁定行/列，添加密码

@Overridepublic void afterCellCreate(CellWriteHandlerContext context) {WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();Sheet sheet = writeSheetHolder.getSheet();Workbook workbook = sheet.getWorkbook();Cell cell = context.getCell();int columnIndex = cell.getColumnIndex();Row row = cell.getRow();// 设置工作表保护if (!sheet.getProtect()) {XSSFSheet xssfSheet = (XSSFSheet) sheet;// 启用保护xssfSheet.protectSheet("1234");// 设置保护选项：允许删除未锁定行xssfSheet.lockDeleteRows(false);// 设置保护选项：允许插入未锁定行xssfSheet.lockInsertRows(false);}// 设置工作表的默认单元格样式为不锁定CellStyle defaultStyle = workbook.createCellStyle();defaultStyle.setLocked(false);sheet.setDefaultColumnStyle(columnIndex, defaultStyle);row.setRowStyle(defaultStyle);}

4、合并单元格

sheet.addMergedRegion(new CellRangeAddress(relativeRowIndex, relativeRowIndex, 0, 10));

5、导入自定义统一注解统一校验

package com.example.springbootexcel.excel.base.component;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** tips：非必填校验，填了就校验，不填不校验*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelValidation {/*** 日期校验** @return true表示必须为日期，false表示不限制*/boolean date() default false;/*** 是否必须为数字** @return true表示必须为数字，false表示不限制*/boolean numeric() default false;/*** 是否允许小数，且最多两位小数** @return true表示允许最多两位小数，false表示不允许小数*/boolean decimal() default false;/*** 是否允许斜杠** @return true表示允许斜杠，false表示不允许*/boolean allowSlash() default false;/*** 校验失败时的错误提示信息** @return 错误提示信息*/String message() default "字段校验失败";}

6、样式处理（字体，颜色，底色，富文本，列宽，行宽等）

// 基本样式设置cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);// 设置水平对齐为左对齐cellStyle.setAlignment(HorizontalAlignment.LEFT);// 设置垂直对齐为垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置自动换行cellStyle.setWrapText(true);// 创建默认字体Font defaultFont = workbook.createFont();defaultFont.setFontName(DEFAULT_FONT_NAME);defaultFont.setFontHeightInPoints(DEFAULT_FONT_POINTS);defaultFont.setColor(IndexedColors.BLACK.getIndex());defaultFont.setBold(false);// 创建红色字体Font redFont = workbook.createFont();redFont.setFontName(DEFAULT_FONT_NAME);redFont.setFontHeightInPoints(DEFAULT_FONT_POINTS);redFont.setColor(IndexedColors.RED.getIndex());redFont.setBold(true);// 自定义列宽String cellValue = cell.getStringCellValue();Integer columnWidth = COLUMN_WIDTHS.get(cellValue);if (ObjectUtil.isNotNull(columnWidth) && !CollectionUtil.contains(COLUMN_WIDTHS_EXIST, context.getColumnIndex())) {sheet.setColumnWidth(context.getColumnIndex(), columnWidth);COLUMN_WIDTHS_EXIST.add(context.getColumnIndex());}// 设置默认宽度if (!CollectionUtil.contains(COLUMN_WIDTHS_EXIST, context.getColumnIndex())) {sheet.setColumnWidth(context.getColumnIndex(), DEFAULT_COLUMN_WIDTH);}// 提示词if (CollectionUtil.contains(TIPS_LIST, relativeRowIndex)) {defaultFont = redFont;// 合并单元格sheet.addMergedRegion(new CellRangeAddress(relativeRowIndex, relativeRowIndex, 0, 10));}// 表头if (CollectionUtil.contains(HEAD_LIST, relativeRowIndex)) {defaultFont.setBold(true);// 背景色cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 动态字段标红if (CollectionUtil.contains(HEAD_READ_COLOR, cell.getColumnIndex())) {defaultFont = redFont;} else {// 星号标红RichTextString richText = cell.getRichStringCellValue();if (StrUtil.startWith(cellValue, "*")) {richText.applyFont(0, 1, redFont);if (cellValue.length() > 1) {richText.applyFont(1, cellValue.length(), defaultFont);}cell.setCellValue(richText);}}}cellStyle.setFont(defaultFont);

7、冻结窗格

.registerWriteHandler(new FreezePaneHandler(2))

package com.example.springbootexcel.excel.base.style;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;/*** 冻结窗格** @author jason*/
public class FreezePaneHandler implements SheetWriteHandler {private final int row;  // 需要冻结的行public FreezePaneHandler(int row) {this.row = row;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();// 冻结首行// sheet.createFreezePane(0, 1, 0, 1);sheet.createFreezePane(0, row, 0, row);}}

8、多Sheet处理

        // 创建 ExcelWriter 对象ExcelWriter excelWriter = EasyExcel.write(filePath).inMemory(true).build();// 写入第1个 SheetWriteSheet sheet1 = EasyExcel.writerSheet("Sheet1").registerWriteHandler(new CommonStyleHandler(MockDataUtil.getHeadReadColor(headList, dynamicList))).registerWriteHandler(new DropDownHandler(dropDownMap)).registerWriteHandler(new FreezePaneHandler(2)).build();excelWriter.write(sheet1DataList, sheet1);// 写入第2个 SheetWriteSheet sheet2 = EasyExcel.writerSheet("Sheet2").head(BrandModelExcel.class).registerWriteHandler(new FreezePaneHandler(1)).build();excelWriter.write(MockDataUtil.brandModelExcelList(), sheet2);// 写入第3个 SheetWriteSheet sheet3 = EasyExcel.writerSheet("Sheet3").head(VehicleNameExcel.class).registerWriteHandler(new FreezePaneHandler(1)).build();excelWriter.write(MockDataUtil.vehicleNameExcelList(), sheet3);// 非常重要：最后一定要关闭 excelWriterexcelWriter.finish();log.info("导出成功：{}", filePath);