Spring Boot集成EasyExcel实现数据导出

喜欢ヅ旅行 2024-03-08 08:57 149阅读 0赞

在本文中，我们将探讨如何使用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;
 
 /**
 * 导出动态参数 
 
 */
 @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;
 
 /**
 * EasyExcel导出动态单元格样式 
 
 */
 @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.*;
 
 /**
 * excel动态下拉框数据填充 
 
 */
 @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;
 }
 
 /**
 * excel动态下拉框数据服务提供者 
 
 */
 @Target(ElementType.TYPE)
 @Retention(RetentionPolicy.RUNTIME)
 public @interface ExcelDynamicData {
 
 /**
 * 提供数据的服务名
 *
 * @return
 */
 String name();
 
 }
 
 /**
 * 
 
 */
 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.*;
 
 /**
 * 自定义ExcelSelected注解解析 
 
 */
 @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;
 
 /**
 * excel设置动态列样式处理器 
 
 */
 @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;
 
 @Override
 public 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
 */
 @Override
 public 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;
 
 /**
 * excel设置下拉选项处理器 
 
 */
 
 @Data
 @AllArgsConstructor
 public class SelectedSheetWriteHandler implements SheetWriteHandler {
 private final Map<Integer, ExcelSelectedResolve> selectedMap;
 
 @Override
 public 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
 */
 @Override
 public 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：导出结果示例 ####

![44e0e31a542642a9abc74e61d7062222.png][]

### 示例二（多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等字段为动态值下拉列表，动态下拉列表数据提供示例如下：

#### 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：导出结果示例 ####

![2ff42c23633340a1afa0ee09963de755.png][]

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

[44e0e31a542642a9abc74e61d7062222.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/80d3a2f95e5744979865971675931d6e.png
[2ff42c23633340a1afa0ee09963de755.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/de86a947b2164f05a3b0be73b3ed2aa4.png