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);