在本文中，我们将探讨如何使用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);
}
@Slf4j
@Component
public class ExcelDynamicDataStrategyFactory {
/**
* 初始化BEAN_MAP，获取ExcelDynamicSelectService接口下所有实现类
*/
private static Map<String, ExcelDynamicSelectHandler> 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<String, ExcelDynamicSelectHandler> 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.*;
/**
* <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;
@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;
/**
* <p> excel设置下拉选项处理器 </p>
*/
@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：导出结果示例
示例二（多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<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>> 当中。
扫一扫
358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
