Spring Boot集成EasyExcel实现数据导出

        在本文中，我们将探讨如何使用Spring Boot集成EasyExcel库来实现数据导出功能。我们将学习如何通过EasyExcel库生成Excel文件，并实现一些高级功能，如支持列下拉和自定义单元格样式，自适应列宽、行高，动态表头 ，以及如何同时导出多个sheet页的数据。

引入依赖

        首先，我们需要在pom.xml文件中添加EasyExcel和相关的依赖项

            <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency>

创建参数类

 动态生成EXCEL参数类

        支持sheet名称、模版类、动态表头、数据集、下拉列、单元格样式定义。

import lombok.Data;import java.io.Serializable;
import java.util.List;
import java.util.Map;/*** <p>  导出动态参数   </p>*/
@Data
public class EasyExcelExportDynamicParam implements Serializable {/*** sheet名称*/private String sheetName;/*** 模版*/private Class<?> template;/*** 数据集*/private List<?> dataList;/*** 动态表头*/private List<List<String>> dynamicHeaderList;/*** 单元格样式map,key为行下标，* Map<Integer,EasyExcelExportDynamicStyleParam> key为列下标*/private Map<Integer, Map<Integer, EasyExcelExportDynamicStyleParam>> styleMap;/*** 下拉选项 key为列下标*/private Map<Integer, ExcelSelectedResolve> selectedMap;
}

单元格样式参数类

        支持字体颜色、背景颜色、字体、字体大小、单元格内容对齐方式。

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;import java.io.Serializable;/*** <p>  EasyExcel导出动态单元格样式   </p>*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class EasyExcelExportDynamicStyleParam implements Serializable {/*** 字体颜色 IndexedColors.WHITE.getIndex()*/private Short fontColor;/*** 背景颜色*/private Short bgColor;/*** 字体*/private String fontName;/*** 字体大小*/private Short fontSize;/*** 单元格内容对齐方式*/private HorizontalAlignment alignment;}

 单元格添加下拉列表配置

        支持注解方式设置单元格下拉列表，起始行、结束行、固定下拉内容、动态下拉内容。

import java.lang.annotation.*;/*** <p>  excel动态下拉框数据填充   </p>*/
@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSelected {/*** 固定下拉内容*/String[] source() default {};/*** 动态下拉内容服务类*/String dynamicData() default "";/*** 动态下拉内容参数** @return*/String dynamicParam() default "";/*** 设置下拉框的起始行，默认为第二行*/int firstRow() default 1;/*** 设置下拉框的结束行，默认10000行*/int lastRow() default 5000;
}/*** <p>  excel动态下拉框数据服务提供者   </p>*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDynamicData {/*** 提供数据的服务名** @return*/String name();}/*** <p>     </p>*/
public interface ExcelDynamicSelectHandler {/*** 获取动态生成的下拉框可选数据* @return 动态生成的下拉框可选数据*/String[] getSource(String param);
}import com.alibaba.excel.annotation.ExcelProperty;
import com.yt.bi.goods.common.annotation.ExcelSelected;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;
import java.util.*;/*** <p>  自定义ExcelSelected注解解析  </p>*/
@Data
@Slf4j
public class ExcelSelectedResolve {/*** 下拉内容*/private String[] source;/*** 设置下拉框的起始行，默认为第二行*/private int firstRow = 1;/*** 设置下拉框的结束行*/private int lastRow = 2000;/*** 解析表头类中的下拉注解** @param head 表头类* @return Map<下拉框列索引, 下拉框内容> map*/public static Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<?> head) {Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();if (Objects.isNull(head)) {return selectedMap;}// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性Field[] fields = head.getDeclaredFields();for (int i = 0; i < fields.length; i++) {Field field = fields[i];// 解析注解信息ExcelSelected selected = field.getAnnotation(ExcelSelected.class);ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (selected == null) {continue;}String[] source = resolveSelectedSource(selected);if (source == null || source.length == 0) {continue;}ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();excelSelectedResolve.setSource(source);excelSelectedResolve.setFirstRow(selected.firstRow());excelSelectedResolve.setLastRow(selected.lastRow());if (property != null && property.index() >= 0) {selectedMap.put(property.index(), excelSelectedResolve);} else {selectedMap.put(i, excelSelectedResolve);}}return selectedMap;}/*** 解析表头类中的配置注解** @param head 表头类*/public static List<List<String>> resolvePropertyAnnotation(Class<?> head) {List<List<String>> list = new ArrayList<>();if (Objects.isNull(head)) {return list;}// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性Field[] fields = head.getDeclaredFields();for (Field field : fields) {ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (property != null) {list.add(Arrays.asList(property.value()));}}return list;}/*** 获取下拉框选项值** @param excelSelected* @return*/private static String[] resolveSelectedSource(ExcelSelected excelSelected) {if (excelSelected == null) {return null;}// 获取固定下拉框的内容String[] source = excelSelected.source();if (source.length > 0) {return source;}// 获取动态下拉框的内容ExcelDynamicSelectHandler excelDynamicSelectHandler = ExcelDynamicDataStrategyFactory.doStrategy(excelSelected.dynamicData());if (Objects.nonNull(excelDynamicSelectHandler)) {return excelDynamicSelectHandler.getSource(excelSelected.dynamicParam());}return null;}}

创建导出功能工具类

        为了实现高内聚和低耦合的设计，我们可以创建一个导出功能的工具类EasyExcelUtil，支持动态表头生成、多sheet、下拉列等功能

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import lombok.extern.slf4j.Slf4j;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;@Slf4j
public class EasyExcelUtil {/*** 生成多个sheet** @param response* @param paramList* @param fileName* @throws IOException*/public static void exportExcel(HttpServletResponse response, List<EasyExcelExportDynamicParam> paramList, String fileName) throws IOException {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {for (EasyExcelExportDynamicParam param : paramList) {ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.writerSheet(param.getSheetName()).head(param.getDynamicHeaderList()).head(param.getTemplate());// 样式Map<Integer, Map<Integer, EasyExcelExportDynamicStyleParam>> styleMap = param.getStyleMap();if (CollectionUtil.isNotEmpty(styleMap)) {writerSheetBuilder.registerWriteHandler(new CellStyleSheetWriteHandler(styleMap));}// 下拉选择Map<Integer, ExcelSelectedResolve> selectedMap =CollectionUtil.isNotEmpty(param.getSelectedMap()) ? param.getSelectedMap() : ExcelSelectedResolve.resolveSelectedAnnotation(param.getTemplate());if (CollectionUtil.isNotEmpty(selectedMap)) {writerSheetBuilder.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap));}excelWriter.write(param.getDataList(), writerSheetBuilder.build());}excelWriter.finish();}}}

使用CellWriteHandler实现自定义单元格样式

        EasyExcel提供了CellWriteHandler接口，其中的afterCellDispose方法在单元格写操作完成并销毁后被调用。我们可以通过实现该接口并重写afterCellDispose方法来实现自定义单元格样式。

        在重写的afterCellDispose方法中，我们可以获取到已经创建好的单元格，并添加自定义的样式。这个方法在每个单元格写操作完成后都会被调用，因此我们可以根据需要对特定的单元格或整个表格进行样式处理。

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.StyleUtil;
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 lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;import java.util.List;
import java.util.Map;
import java.util.Objects;/*** <p>  excel设置动态列样式处理器   </p>*/
@Data
@AllArgsConstructor
public class CellStyleSheetWriteHandler implements CellWriteHandler {private static final short DEFAULT_FONT_SIZE = 14;private static final String DEFAULT_FONT_NAME = "宋体";private static final short DEFAULT_FONT_COLOR = 8;private static final short DEFAULT_BG_COLOR = 22;private Map<Integer, Map<Integer, EasyExcelExportDynamicStyleParam>> styleMap;@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex,Boolean isHead) {int rowIndex = cell.getRowIndex();Sheet sheet = cell.getSheet();Workbook workbook = sheet.getWorkbook();Row row = sheet.getRow(rowIndex);int columnIndex = cell.getColumnIndex();if (isHead) {// 表头设置自适应列宽// 获取单元格内容长度（以字符为单位）String stringCellValue = cell.getStringCellValue();int contentLength = stringCellValue.length();// 计算自动调整后的列宽（加上一些额外空间）int newWidth = contentLength > 10 ? (contentLength + 35) * 256 : (contentLength + 12) * 256;sheet.setColumnWidth(columnIndex, newWidth);// 表头设置自适应行高String[] split = stringCellValue.split("\\n");if (split != null && split.length > 0) {setRowHeight(row, (short) ((split.length + 1.2) * 256));}}if (CollectionUtil.isEmpty(styleMap)) {return;}Map<Integer, EasyExcelExportDynamicStyleParam> indexes = styleMap.get(rowIndex);if (CollectionUtils.isEmpty(indexes)) {return;}// 自定义样式setCellStyle(row, cell, workbook, indexes.get(columnIndex));}/*** 自定义样式** @param cell* @param workbook* @param styleParam*/private void setCellStyle(Row row, Cell cell, Workbook workbook, EasyExcelExportDynamicStyleParam styleParam) {if (Objects.isNull(styleParam)) {return;}// 字体Font font = workbook.createFont();font.setFontName(StringUtils.isNotBlank(styleParam.getFontName()) ? styleParam.getFontName() : DEFAULT_FONT_NAME);font.setFontHeightInPoints(Objects.nonNull(styleParam.getFontSize()) ? styleParam.getFontSize() : DEFAULT_FONT_SIZE);font.setBold(true);font.setColor(Objects.nonNull(styleParam.getFontColor()) ? styleParam.getFontColor() : DEFAULT_FONT_COLOR);WriteCellStyle writeCellStyle = new WriteCellStyle();writeCellStyle.setFillForegroundColor(Objects.nonNull(styleParam.getBgColor()) ? styleParam.getBgColor() : DEFAULT_BG_COLOR);writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);writeCellStyle.setWrapped(true);CellStyle cellStyle = workbook.createCellStyle();// 克隆原有样式属性cellStyle.cloneStyleFrom(cell.getCellStyle());CellStyle newCellStyle = StyleUtil.buildCellStyle(workbook, cellStyle, writeCellStyle);newCellStyle.setFont(font);if (Objects.nonNull(styleParam.getAlignment())) {newCellStyle.setAlignment(styleParam.getAlignment());}// 设置新样式cell.setCellStyle(newCellStyle);}/*** 设置行高** @param row* @param height*/private void setRowHeight(Row row, short height) {if (row != null) {row.setHeight(height);}}/*** 写入器排序问题AbstractCellWriteHandler使用的默认序号是0，* EasyExcel自己的样式填充器FillStyleCellWriteHandler使用序号是50000（可在OrderConstant类中查到），* 也就是说我们在这个类中重写样式时又被easy excel重写回去了。* 解决方法是重写order方法使其大于50000 即可。** @return*/@Overridepublic int order() {return 1000000;}

 使用SheetWriteHandler实现自定义下拉列表处理

        建一个名为SelectedSheetWriteHandler的类，并实现com.alibaba.excel.write.handler.SheetWriteHandler接口。这个接口中定义了一些回调方法，允许你在生成Excel文件的过程中进行自定义处理。
        重写afterSheetCreate方法：在SelectedSheetWriteHandler类中，实现afterSheetCreate方法。这个方法会在每个Sheet创建完成后被调用，我们可以在这里进行下拉列表的处理。我们可以在每个Sheet创建完成后，为指定的单元格添加下拉列表，并设置数据源。这样，我们就能更好地控制用户在Excel中输入的数据，提高数据的准确性和一致性。

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
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;
import java.util.Objects;/*** <p>  excel设置下拉选项处理器   </p>*/@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {private final Map<Integer, ExcelSelectedResolve> selectedMap;@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (CollectionUtil.isEmpty(selectedMap)) {return;}// 这里可以对cell进行任何操作Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();selectedMap.forEach((k, v) -> {if (Objects.isNull(v)) {return;}// 设置下拉列表的行： 首行，末行，首列，末列CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);// 设置下拉列表的值DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());// 设置约束DataValidation validation = helper.createValidation(constraint, rangeList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "请输入下拉选项中的内容");sheet.addValidationData(validation);});}
}

使用示例

示例一（多sheet页固定表头，支持动态下拉列表）

1：定义模版类

import com.alibaba.excel.annotation.ExcelProperty;
import com.yt.bi.goods.common.annotation.ExcelSelected;
import com.yt.bi.goods.common.constant.ExcelConstants;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;import java.io.Serializable;@Data
public class ProductSkuUpdateBasicsTemplateDTO implements Serializable {private static final long serialVersionUID = 1L;// 字符串的头背景设置成黄色 IndexedColors.PINK.getIndex()@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*SKU"}, index = 0)private String sku;@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "产品名称"}, index = 1)private String productName;@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "末级品类编码"}, index = 2)private String categoryCodeLast;@ExcelSelected(dynamicData = "bi_dict", dynamicParam = "product_origin_receiving", firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "头程方式"}, index = 3)private String originReceiving;@ApiModelProperty("是否有配件 1是 0否")@ExcelSelected(source = {"是", "否"}, firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "是否包含配件"}, index = 4)private String haveParts;@ApiModelProperty("是否反倾销 1=是; 0=否;")@ExcelSelected(source = {"是", "否"}, firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "是否反倾销"}, index = 5)private String antiDumpingFlag;@ApiModelProperty("是否带电 1是 0否")@ExcelSelected(source = {"是", "否"}, firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "是否带电"}, index = 6)private String electrifyFlag;@ApiModelProperty("主项目组")@ExcelSelected(dynamicData = "erp_dict", dynamicParam = "main_project_team", firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "主项目组"}, index = 7)private String mainProjectTeam;@ApiModelProperty("输入电压")@ExcelSelected(dynamicData = "sku_voltage", firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "输入电压"}, index = 8)private String voltage;@ApiModelProperty("产品开发人员")@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "产品开发人员工号（多个人员请用&隔开）"}, index = 9)private String productDeveloper;}import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import lombok.Data;import java.io.Serializable;@HeadFontStyle(fontName = "宋体",color = Short.MAX_VALUE,fontHeightInPoints = 14
)
@HeadRowHeight(value = 30)
@ContentRowHeight(value = 20)
@ColumnWidth(value = 15)
@Data
public class DeveloperTemplateDTO implements Serializable {@ExcelProperty({"工号"})private String accountName;@ExcelProperty({"人员名称"})private String userName;
}

其中ProductSkuUpdateBasicsTemplateDTO模版类种的haveParts等字段为固定值下拉列表，mainProjectTeam等字段为动态值下拉列表，动态下拉列表数据提供示例如下：

/*** ERP数据字典处理类*/
@Slf4j
@Component
@ExcelDynamicData(name = "erp_dict")
public class ErpDictDataRpcHandle implements ExcelDynamicSelectHandler {/*** 查询字典信息** @param param* @return*/@Overridepublic String[] getSource(String dictType) {if (StringUtils.isBlank(param)) {return new String[0];}ErpDictDataQuery query = new ErpDictDataQuery();query.setTopFlag(Constants.ZERO);query.setDictType(dictType);List<ErpDictDataDTO> dictDataDTOList = erpDictList(query);// 查询数据库或其他方式获取数据if (CollectionUtils.isNotEmpty(dictDataDTOList)) {return dictDataDTOList.stream().map(ErpDictDataDTO::getDictLabel).toArray(String[]::new);}return new String[0];}
}

2：构建导出参数

public void batchUpdateSkuImportTemplate(HttpServletResponse response) throws IOException {// 导入数据页List<EasyExcelExportDynamicParam> paramList = new ArrayList<>();EasyExcelExportDynamicParam param = new EasyExcelExportDynamicParam();param.setSheetName("导入数据页");param.setTemplate(ProductSkuUpdateBasicsTemplateDTO.class);param.setDataList(new ArrayList<>());// 构建样式，第三行，第一列背景色黄色，字体红色buildStyle(param);paramList.add(param);// 人员对照表EasyExcelExportDynamicParam developerParam = new EasyExcelExportDynamicParam();developerParam.setSheetName("人员对照表");developerParam.setTemplate(DeveloperTemplateDTO.class);List<DeveloperTemplateDTO> templateDTOList = new ArrayList<>();developerParam.setDataList(templateDTOList);paramList.add(developerParam);EasyExcelUtil.exportExcel(response, paramList, "多sheet页导出");}/*** 样式** @param param*/private void buildStyle(EasyExcelExportDynamicParam param) {Map<Integer, Map<Integer, EasyExcelExportDynamicStyleParam>> rowStyleMap = new HashMap<>();Map<Integer, EasyExcelExportDynamicStyleParam> oneRowMap = new HashMap<>();Map<Integer, EasyExcelExportDynamicStyleParam> twoRowMap = new HashMap<>();Map<Integer, EasyExcelExportDynamicStyleParam> threeRowMap = new HashMap<>();EasyExcelExportDynamicStyleParam oneRowParam = EasyExcelExportDynamicStyleParam.builder().bgColor(IndexedColors.WHITE.getIndex()).alignment(HorizontalAlignment.LEFT).build();oneRowMap.put(0, oneRowParam);EasyExcelExportDynamicStyleParam twoRowParam = EasyExcelExportDynamicStyleParam.builder().bgColor(IndexedColors.PALE_BLUE.getIndex()).build();twoRowMap.put(0, twoRowParam);EasyExcelExportDynamicStyleParam threeRowParam = EasyExcelExportDynamicStyleParam.builder().bgColor(IndexedColors.YELLOW.getIndex()).fontColor(IndexedColors.RED.getIndex()).build();threeRowMap.put(0, threeRowParam);rowStyleMap.put(0, oneRowMap);rowStyleMap.put(1, twoRowMap);rowStyleMap.put(2, threeRowMap);param.setStyleMap(rowStyleMap);}

 3：导出结果示例

 示例二（多sheet页固定+动态表头，支持动态下拉列表，动态设置单元格格式）

1：定义固定表头模版类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.yt.bi.goods.common.annotation.ExcelSelected;
import com.yt.bi.goods.common.constant.ExcelConstants;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;import java.io.Serializable;@Data
public class ProductAddNormalSkuBasicsTemplateDTO implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*国家"}, index = 0)private String country;@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*序列"}, index = 1)private String series;@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*产品名称"}, index = 2)private String productName;@ExcelSelected(dynamicData = "bi_dict", dynamicParam = "product_origin_receiving", firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*头程方式"}, index = 3)private String originReceiving;@ApiModelProperty("是否有配件 1是 0否")@ExcelSelected(source = {"是", "否"}, firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*是否包含配件"}, index = 4)private String haveParts;@ApiModelProperty("是否反倾销 1=是; 0=否;")@ExcelSelected(source = {"是", "否"}, firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*是否反倾销"}, index = 5)private String antiDumpingFlag;@ApiModelProperty("主项目组")@ExcelSelected(dynamicData = "erp_dict", dynamicParam = "main_project_team", firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*主项目组"}, index = 6)private String mainProjectTeam;@ApiModelProperty("是否带电 1是 0否")@ExcelSelected(source = {"是", "否"}, firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "*是否带电"}, index = 7)private String electrifyFlag;@ExcelSelected(dynamicData = "sku_voltage", firstRow = 3)@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "输入电压"}, index = 8)private String voltage;@ApiModelProperty("产品开发人员")@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "产品开发人员工号（多个人员请用&隔开）"}, index = 9)private String productDeveloper;@ApiModelProperty("备注")@ExcelProperty(value = {"导入说明：\n" +, "基础信息", "备注"}, index = 10)private String remark;
}@HeadFontStyle(fontName = "宋体",color = Short.MAX_VALUE,fontHeightInPoints = 14
)
@HeadRowHeight(value = 30)
@ContentRowHeight(value = 20)
@ColumnWidth(value = 15)
@Data
public class DeveloperTemplateDTO implements Serializable {@ExcelProperty({"工号"})private String accountName;@ExcelProperty({"人员名称"})private String userName;
}

其中ProductAddNormalSkuBasicsTemplateDTO模版类种的haveParts等字段为固定值下拉列表，mainProjectTeam等字段为动态值下拉列表，动态下拉列表数据提供示例如下：

/*** ERP数据字典处理类*/
@Slf4j
@Component
@ExcelDynamicData(name = "erp_dict")
public class ErpDictDataRpcHandle implements ExcelDynamicSelectHandler {/*** 查询字典信息** @param param* @return*/@Overridepublic String[] getSource(String dictType) {if (StringUtils.isBlank(param)) {return new String[0];}ErpDictDataQuery query = new ErpDictDataQuery();query.setTopFlag(Constants.ZERO);query.setDictType(dictType);List<ErpDictDataDTO> dictDataDTOList = erpDictList(query);// 查询数据库或其他方式获取数据if (CollectionUtils.isNotEmpty(dictDataDTOList)) {return dictDataDTOList.stream().map(ErpDictDataDTO::getDictLabel).toArray(String[]::new);}return new String[0];}
}

2：构建导出参数

public void batchAddSkuImportTemplate(HttpServletResponse response) {List<EasyExcelExportDynamicParam> paramList = new ArrayList<>();EasyExcelExportDynamicParam param = new EasyExcelExportDynamicParam();// 生成基础信息表头List<List<String>> listList = ExcelSelectedResolve.resolvePropertyAnnotation(ProductAddNormalSkuBasicsTemplateDTO.class);// 查询品类属性ProductCategoryAttributeValueDTO attributeValueDTO = productCategoryAttributeService.queryCategoryAttributeByCategoryCode(categoryCodeLast);// 生成规格属性表头List<ProductCategoryAttributeDTO> specAttributeList = attributeValueDTO.getSpecAttributeList();if (CollectionUtil.isNotEmpty(specAttributeList)) {List<List<String>> attributeNameList = specAttributeList.stream().map(x -> Arrays.asList("导入说明：\n" +, "产品属性-规格属性", attributeNameRequiredFlag(x))).collect(Collectors.toList());listList.addAll(attributeNameList);}// 生成销售属性表头List<ProductCategoryAttributeDTO> salesAttributeList = attributeValueDTO.getSalesAttributeList();if (CollectionUtil.isNotEmpty(salesAttributeList)) {List<List<String>> attributeNameList = salesAttributeList.stream().map(x -> Arrays.asList("导入说明：\n" +, "产品属性-销售属性", attributeNameRequiredFlag(x))).collect(Collectors.toList());listList.addAll(attributeNameList);}// 生成标签属性表头List<ProductCategoryAttributeDTO> tagAttributeList = attributeValueDTO.getTagAttributeList();if (CollectionUtil.isNotEmpty(tagAttributeList)) {List<List<String>> attributeNameList = tagAttributeList.stream().map(x -> Arrays.asList("导入说明：\n" +, "产品属性-标签属性", attributeNameRequiredFlag(x))).collect(Collectors.toList());listList.addAll(attributeNameList);}// 设置颜色Map<Integer, Map<Integer, EasyExcelExportDynamicStyleParam>> styleMap = new HashMap<>();Map<Integer, EasyExcelExportDynamicStyleParam> paramMap = new HashMap<>();// 判断第三行带*号列明都加上颜色Integer num = Constants.ZERO;for (List<String> line : listList) {String secondLineName = line.get(2);if (secondLineName.startsWith("*")) {EasyExcelExportDynamicStyleParam styleParam = EasyExcelExportDynamicStyleParam.builder().bgColor(IndexedColors.YELLOW.getIndex()).fontColor(IndexedColors.RED.getIndex()).build();paramMap.put(num, styleParam);}num++;}styleMap.put(2, paramMap);Map<Integer, EasyExcelExportDynamicStyleParam> one = new HashMap<>();EasyExcelExportDynamicStyleParam oneParam = EasyExcelExportDynamicStyleParam.builder().bgColor(IndexedColors.WHITE.getIndex()).alignment(HorizontalAlignment.LEFT).build();one.put(0, oneParam);styleMap.put(0, one);param.setStyleMap(styleMap);param.setDataList(new ArrayList<>());param.setDynamicHeaderList(listList);// 生成基础信息下拉Map<Integer, ExcelSelectedResolve> head = ExcelSelectedResolve.resolveSelectedAnnotation(clazz);param.setSelectedMap(head);param.setSheetName("导入数据页");paramList.add(param);// 人员对照表EasyExcelExportDynamicParam developerParam = new EasyExcelExportDynamicParam();developerParam.setSheetName("人员对照表");developerParam.setTemplate(DeveloperTemplateDTO.class);List<DeveloperTemplateDTO> templateDTOList = new ArrayList<>();developerParam.setDataList(templateDTOList);paramList.add(developerParam);EasyExcelUtil.exportExcel(response, paramList, "多sheet页导出");}

 3：导出结果示例

注意：当使用动态表头和固定表头组合生成时，需要统一把表头单元格字段内容写入到List<List<String>> 当中。