电影网站html模板,wordpress标题重复,html博客网页完整代码,银川网站建设效果在本文中#xff0c;我们将探讨如何使用Spring Boot集成EasyExcel库来实现数据导出功能。我们将学习如何通过EasyExcel库生成Excel文件#xff0c;并实现一些高级功能#xff0c;如支持列下拉和自定义单元格样式#xff0c;自适应列宽、行高#xff0c;动态表头 #xff… 在本文中我们将探讨如何使用Spring Boot集成EasyExcel库来实现数据导出功能。我们将学习如何通过EasyExcel库生成Excel文件并实现一些高级功能如支持列下拉和自定义单元格样式自适应列宽、行高动态表头 以及如何同时导出多个sheet页的数据。
引入依赖 首先我们需要在pom.xml文件中添加EasyExcel和相关的依赖项 dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion4.1.2/version/dependencydependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.2.1/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion4.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 ListListString dynamicHeaderList;/*** 单元格样式map,key为行下标* MapInteger,EasyExcelExportDynamicStyleParam key为列下标*/private MapInteger, MapInteger, EasyExcelExportDynamicStyleParam styleMap;/*** 下拉选项 key为列下标*/private MapInteger, 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 MapInteger, ExcelSelectedResolve resolveSelectedAnnotation(Class? head) {MapInteger, 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 ListListString resolvePropertyAnnotation(Class? head) {ListListString 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, ListEasyExcelExportDynamicParam 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());// 样式MapInteger, MapInteger, EasyExcelExportDynamicStyleParam styleMap param.getStyleMap();if (CollectionUtil.isNotEmpty(styleMap)) {writerSheetBuilder.registerWriteHandler(new CellStyleSheetWriteHandler(styleMap));}// 下拉选择MapInteger, 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 MapInteger, MapInteger, EasyExcelExportDynamicStyleParam styleMap;Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,ListWriteCellData? 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;}MapInteger, 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 MapInteger, 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);ListErpDictDataDTO 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 {// 导入数据页ListEasyExcelExportDynamicParam 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);ListDeveloperTemplateDTO templateDTOList new ArrayList();developerParam.setDataList(templateDTOList);paramList.add(developerParam);EasyExcelUtil.exportExcel(response, paramList, 多sheet页导出);}/*** 样式** param param*/private void buildStyle(EasyExcelExportDynamicParam param) {MapInteger, MapInteger, EasyExcelExportDynamicStyleParam rowStyleMap new HashMap();MapInteger, EasyExcelExportDynamicStyleParam oneRowMap new HashMap();MapInteger, EasyExcelExportDynamicStyleParam twoRowMap new HashMap();MapInteger, 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);ListErpDictDataDTO 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) {ListEasyExcelExportDynamicParam paramList new ArrayList();EasyExcelExportDynamicParam param new EasyExcelExportDynamicParam();// 生成基础信息表头ListListString listList ExcelSelectedResolve.resolvePropertyAnnotation(ProductAddNormalSkuBasicsTemplateDTO.class);// 查询品类属性ProductCategoryAttributeValueDTO attributeValueDTO productCategoryAttributeService.queryCategoryAttributeByCategoryCode(categoryCodeLast);// 生成规格属性表头ListProductCategoryAttributeDTO specAttributeList attributeValueDTO.getSpecAttributeList();if (CollectionUtil.isNotEmpty(specAttributeList)) {ListListString attributeNameList specAttributeList.stream().map(x - Arrays.asList(导入说明\n , 产品属性-规格属性, attributeNameRequiredFlag(x))).collect(Collectors.toList());listList.addAll(attributeNameList);}// 生成销售属性表头ListProductCategoryAttributeDTO salesAttributeList attributeValueDTO.getSalesAttributeList();if (CollectionUtil.isNotEmpty(salesAttributeList)) {ListListString attributeNameList salesAttributeList.stream().map(x - Arrays.asList(导入说明\n , 产品属性-销售属性, attributeNameRequiredFlag(x))).collect(Collectors.toList());listList.addAll(attributeNameList);}// 生成标签属性表头ListProductCategoryAttributeDTO tagAttributeList attributeValueDTO.getTagAttributeList();if (CollectionUtil.isNotEmpty(tagAttributeList)) {ListListString attributeNameList tagAttributeList.stream().map(x - Arrays.asList(导入说明\n , 产品属性-标签属性, attributeNameRequiredFlag(x))).collect(Collectors.toList());listList.addAll(attributeNameList);}// 设置颜色MapInteger, MapInteger, EasyExcelExportDynamicStyleParam styleMap new HashMap();MapInteger, EasyExcelExportDynamicStyleParam paramMap new HashMap();// 判断第三行带*号列明都加上颜色Integer num Constants.ZERO;for (ListString 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);MapInteger, 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);// 生成基础信息下拉MapInteger, ExcelSelectedResolve head ExcelSelectedResolve.resolveSelectedAnnotation(clazz);param.setSelectedMap(head);param.setSheetName(导入数据页);paramList.add(param);// 人员对照表EasyExcelExportDynamicParam developerParam new EasyExcelExportDynamicParam();developerParam.setSheetName(人员对照表);developerParam.setTemplate(DeveloperTemplateDTO.class);ListDeveloperTemplateDTO templateDTOList new ArrayList();developerParam.setDataList(templateDTOList);paramList.add(developerParam);EasyExcelUtil.exportExcel(response, paramList, 多sheet页导出);}3导出结果示例 注意当使用动态表头和固定表头组合生成时需要统一把表头单元格字段内容写入到ListListString 当中。
