Spring Boot集成EasyExcel实现数据导出

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

引入依赖

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


            <dependency>
                <groupId>org.apache.poigroupId>
                <artifactId>poi-ooxmlartifactId>
                <version>4.1.2version>
            dependency>
            <dependency>
                <groupId>com.alibabagroupId>
                <artifactId>easyexcelartifactId>
                <version>3.2.1version>
            dependency>
            <dependency>
                <groupId>org.apache.poigroupId>
                <artifactId>poiartifactId>
                <version>4.1.2version>
            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> dynamicHeaderList;
 
    /**
     * 单元格样式map,key为行下标，
     * Map key为列下标
     */
    private Map> styleMap;
 
    /**
     * 下拉选项 key为列下标
     */
    private Map 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);
}
 
 
@Slf4j
@Component
public class ExcelDynamicDataStrategyFactory {
    /**
     * 初始化BEAN_MAP，获取ExcelDynamicSelectService接口下所有实现类
     */
    private static Map BEAN_MAP;
    /**
     * 执行策略
     *
     * @param name
     * @return
     */
    public static ExcelDynamicSelectHandler doStrategy(String name) {
        if(CollectionUtils.isEmpty(BEAN_MAP)){
            BEAN_MAP = SpringUtils.getBeansOfType(ExcelDynamicSelectHandler.class);
        }
        // 1：ExcelDynamicSelect接口实现类为空
        if (StringUtils.isBlank(name) || CollectionUtils.isEmpty(BEAN_MAP)) {
            log.warn("策略实现类不存在，type = {}", name);
            return null;
        }
        try {
            // 2：循环ExcelDynamicSelect接口实现类
            for (Map.Entry entry : BEAN_MAP.entrySet()) {
                Class targetClass = AopUtils.getTargetClass(entry.getValue());
                // 3：获取类上的excel动态下拉框数据服务策略注解
                ExcelDynamicData annotation = targetClass.getAnnotation(ExcelDynamicData.class);
                if (null == annotation) {
                    continue;
                }
                // 4：当前name等注解指定的name时返回实现类
                if (name.equals(annotation.name())) {
                    return entry.getValue();
                }
            }
        } catch (Exception e) {
            log.error("获取excel动态下拉框数据服务策略实现类失败，name = {}", name, e);
        }
        return null;
    }
 
}
 
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 resolveSelectedAnnotation(Class head) {
        Map 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> resolvePropertyAnnotation(Class head) {
        List> 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 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> styleMap = param.getStyleMap();
                if (CollectionUtil.isNotEmpty(styleMap)) {
                    writerSheetBuilder.registerWriteHandler(new CellStyleSheetWriteHandler(styleMap));
                }
                // 下拉选择
                Map 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> styleMap;
 
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List> 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 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 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 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 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 templateDTOList = new ArrayList<>();
        developerParam.setDataList(templateDTOList);
        paramList.add(developerParam);
 
        EasyExcelUtil.exportExcel(response, paramList, "多sheet页导出");
    }
 
    /**
     * 样式
     *
     * @param param
     */
    private void buildStyle(EasyExcelExportDynamicParam param) {
        Map> rowStyleMap = new HashMap<>();
        Map oneRowMap = new HashMap<>();
        Map twoRowMap = new HashMap<>();
        Map 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
     */
    @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 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 paramList = new ArrayList<>();
        EasyExcelExportDynamicParam param = new EasyExcelExportDynamicParam();
  
 
        // 生成基础信息表头
        List> listList = ExcelSelectedResolve.resolvePropertyAnnotation(ProductAddNormalSkuBasicsTemplateDTO.class);
        // 查询品类属性
        ProductCategoryAttributeValueDTO attributeValueDTO = productCategoryAttributeService.queryCategoryAttributeByCategoryCode(categoryCodeLast);
        // 生成规格属性表头
        List specAttributeList = attributeValueDTO.getSpecAttributeList();
        if (CollectionUtil.isNotEmpty(specAttributeList)) {
            List> attributeNameList = specAttributeList.stream().map(x -> Arrays.asList("导入说明：\n" +, "产品属性-规格属性", attributeNameRequiredFlag(x))).collect(Collectors.toList());
            listList.addAll(attributeNameList);
        }
        // 生成销售属性表头
        List salesAttributeList = attributeValueDTO.getSalesAttributeList();
        if (CollectionUtil.isNotEmpty(salesAttributeList)) {
            List> attributeNameList = salesAttributeList.stream().map(x -> Arrays.asList("导入说明：\n" +, "产品属性-销售属性", attributeNameRequiredFlag(x))).collect(Collectors.toList());
            listList.addAll(attributeNameList);
        }
        // 生成标签属性表头
        List tagAttributeList = attributeValueDTO.getTagAttributeList();
        if (CollectionUtil.isNotEmpty(tagAttributeList)) {
            List> attributeNameList = tagAttributeList.stream().map(x -> Arrays.asList("导入说明：\n" +, "产品属性-标签属性", attributeNameRequiredFlag(x))).collect(Collectors.toList());
            listList.addAll(attributeNameList);
        }
        // 设置颜色
        Map> styleMap = new HashMap<>();
        Map paramMap = new HashMap<>();
        // 判断第三行带*号列明都加上颜色
        Integer num = Constants.ZERO;
        for (List 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 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 head = ExcelSelectedResolve.resolveSelectedAnnotation(clazz);
        param.setSelectedMap(head);
        param.setSheetName("导入数据页");
        paramList.add(param);
        
		 // 人员对照表
        EasyExcelExportDynamicParam developerParam = new EasyExcelExportDynamicParam();
        developerParam.setSheetName("人员对照表");
        developerParam.setTemplate(DeveloperTemplateDTO.class);
        List templateDTOList = new ArrayList<>();
        developerParam.setDataList(templateDTOList);
        paramList.add(developerParam);
 
        EasyExcelUtil.exportExcel(response, paramList, "多sheet页导出");
 
    }

3：导出结果示例

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

相关阅读:
如何使用 ABAP 代码发送邮件到指定邮箱试读版
 宽瞬时带宽放大器SKY66051-11、SKY66052-11、SKY66041-11、SKY66317-11（RF）适用于通讯网络
 Python爬虫：aiohttp的介绍和基本使用
 live555 音视频处理相关文档解读，
1、2快速生成
 论文浅尝 | 训练语言模型遵循人类反馈的指令
 基于ubuntu 22, jdk 8x64搭建图数据库环境 hugegraph
深入理解JVM虚拟机第九篇：详细介绍JVM中的双亲委派机制工作原理和机制
 scrapy数据提取-实战-起点
 Java多线程按顺序输出10以内的奇偶数
原文地址：https://blog.csdn.net/Ascend1977/article/details/132835270