写模板

模板图片：

实体类（这里没有用@Data 是因为Lombok和easyExcal的版本冲突，在导入读取的时候获取不到值）

package cn.iocoder.yudao.module.project.controller.admin.goods.vo;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import jdk.nashorn.internal.ir.annotations.Ignore;
import lombok.Data;
import lombok.NoArgsConstructor;import java.math.BigDecimal;
import java.util.List;
import java.util.Map;/*** 商品下载模板* @author px*/
public class GoodsTemplateExcelVO {@ExcelProperty(index = 0)private String bn;@ExcelProperty(index = 1)private String productName;@ExcelProperty(index = 2)private String brandNameCn;@ExcelProperty(index = 3)private String brandNameEn;@ExcelProperty(index = 4)private String brandLogo;@ExcelProperty(index = 5)private String url;@ExcelProperty(index = 6)private String jingle;@ExcelProperty(index = 7)private String categoryName1;@ExcelProperty(index = 8)private String categoryName2;@ExcelProperty(index = 9)private String categoryName3;@ExcelProperty(index = 10)private String unit;@ExcelProperty(index = 11)private String model;@ExcelProperty(index = 12)private String pakeageSpec;@ExcelProperty(index = 13)private String taxCode;@ExcelProperty(index = 14)private String outputTaxRate;@ExcelProperty(index = 15)private String codeBar;@ExcelProperty(index = 16)private BigDecimal price;@ExcelProperty(index = 17)private BigDecimal actualPrice;@ExcelProperty(index = 18)private String discountRate;@ExcelProperty(index = 19)private String isReturnStr;@ExcelProperty(index = 20)private String onlineStateStr;@ExcelProperty(index = 21)private Integer deliveryTime;@ExcelProperty(index = 22)private Integer arrivalTime;@ExcelProperty(index = 23)private Integer batchNum;@ExcelProperty(index = 24)private String firstAreaName;@ExcelProperty(index = 25)private String secondAreaName;@ExcelProperty(index = 26)private String thirdAreaName;@ExcelProperty(index = 27)private String storageArea;@ExcelProperty(index = 28)private Integer storage;@ExcelProperty(index = 29)private String attrJson;@ExcelProperty(index = 30)private String exceptionList;@ExcelIgnoreprivate String projectName;@ExcelIgnoreprivate Integer queueId;public String getBn() {return bn;}public void setBn(String bn) {this.bn = bn;}public String getProductName() {return productName;}public void setProductName(String productName) {this.productName = productName;}public String getBrandNameCn() {return brandNameCn;}public void setBrandNameCn(String brandNameCn) {this.brandNameCn = brandNameCn;}public String getBrandNameEn() {return brandNameEn;}public void setBrandNameEn(String brandNameEn) {this.brandNameEn = brandNameEn;}public String getBrandLogo() {return brandLogo;}public void setBrandLogo(String brandLogo) {this.brandLogo = brandLogo;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getJingle() {return jingle;}public void setJingle(String jingle) {this.jingle = jingle;}public String getCategoryName1() {return categoryName1;}public void setCategoryName1(String categoryName1) {this.categoryName1 = categoryName1;}public String getCategoryName2() {return categoryName2;}public void setCategoryName2(String categoryName2) {this.categoryName2 = categoryName2;}public String getCategoryName3() {return categoryName3;}public void setCategoryName3(String categoryName3) {this.categoryName3 = categoryName3;}public String getUnit() {return unit;}public void setUnit(String unit) {this.unit = unit;}public String getModel() {return model;}public void setModel(String model) {this.model = model;}public String getPakeageSpec() {return pakeageSpec;}public void setPakeageSpec(String pakeageSpec) {this.pakeageSpec = pakeageSpec;}public String getTaxCode() {return taxCode;}public void setTaxCode(String taxCode) {this.taxCode = taxCode;}public String getOutputTaxRate() {return outputTaxRate;}public void setOutputTaxRate(String outputTaxRate) {this.outputTaxRate = outputTaxRate;}public String getCodeBar() {return codeBar;}public void setCodeBar(String codeBar) {this.codeBar = codeBar;}public BigDecimal getPrice() {return price;}public void setPrice(BigDecimal price) {this.price = price;}public BigDecimal getActualPrice() {return actualPrice;}public void setActualPrice(BigDecimal actualPrice) {this.actualPrice = actualPrice;}public String getDiscountRate() {return discountRate;}public void setDiscountRate(String discountRate) {this.discountRate = discountRate;}public String getIsReturnStr() {return isReturnStr;}public void setIsReturnStr(String isReturnStr) {this.isReturnStr = isReturnStr;}public String getOnlineStateStr() {return onlineStateStr;}public void setOnlineStateStr(String onlineStateStr) {this.onlineStateStr = onlineStateStr;}public Integer getDeliveryTime() {return deliveryTime;}public void setDeliveryTime(Integer deliveryTime) {this.deliveryTime = deliveryTime;}public Integer getArrivalTime() {return arrivalTime;}public void setArrivalTime(Integer arrivalTime) {this.arrivalTime = arrivalTime;}public Integer getBatchNum() {return batchNum;}public void setBatchNum(Integer batchNum) {this.batchNum = batchNum;}public String getFirstAreaName() {return firstAreaName;}public void setFirstAreaName(String firstAreaName) {this.firstAreaName = firstAreaName;}public String getSecondAreaName() {return secondAreaName;}public void setSecondAreaName(String secondAreaName) {this.secondAreaName = secondAreaName;}public String getThirdAreaName() {return thirdAreaName;}public void setThirdAreaName(String thirdAreaName) {this.thirdAreaName = thirdAreaName;}public String getStorageArea() {return storageArea;}public void setStorageArea(String storageArea) {this.storageArea = storageArea;}public Integer getStorage() {return storage;}public void setStorage(Integer storage) {this.storage = storage;}public String getAttrJson() {return attrJson;}public void setAttrJson(String attrJson) {this.attrJson = attrJson;}public String getExceptionList() {return exceptionList;}public void setExceptionList(String exceptionList) {this.exceptionList = exceptionList;}public String getProjectName() {return projectName;}public void setProjectName(String projectName) {this.projectName = projectName;}public Integer getQueueId() {return queueId;}public void setQueueId(Integer queueId) {this.queueId = queueId;}public GoodsTemplateExcelVO(){}public GoodsTemplateExcelVO(String bn) {this.bn = bn;this.productName = "测试商品";this.brandNameCn = "品牌";this.brandNameEn = "pingpai";this.brandLogo = "https://img30.360buyimg.com/jgsq-productsoa/jfs/t1…5298/88691/63da0918Fb060cb06/6929f345feb64ed2.jpg";this.url = "https://www.ofs.cn/product-394804.html";this.jingle = "介绍";this.categoryName1 = "工具/工具耗材";this.categoryName2 = "手动工具";this.categoryName3 = "扳手";this.unit = "单位";this.model = "T88110-27";this.pakeageSpec = "1*1*1";this.taxCode = "";this.outputTaxRate = "13%";this.codeBar = "";this.discountRate = "20%";this.isReturnStr = "允许";this.onlineStateStr = "上架";this.deliveryTime = 1;this.arrivalTime = 1;this.batchNum = 2;this.firstAreaName = "全国";this.secondAreaName = "";this.thirdAreaName = "";this.storageArea = "重庆";this.storage = 999;this.attrJson = "保质期:12个月;" +"商品简介:徐福记 卷心酥 105g 香浓奶油味";}
}

public void downloadGoodsTemplateFile(HttpServletResponse response) {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("UTF-8");response.setHeader("Content-disposition", "attachment;filename=商品模板.xls");GoodsTemplateExcelVO goodsTemplateExcelVO = new GoodsTemplateExcelVO("123");List<GoodsTemplateExcelVO> goodsTemplateExcelVOList = new ArrayList<>();goodsTemplateExcelVOList.add(goodsTemplateExcelVO);try {// 这里需要设置不关闭流WriteCellStyle headWriteCellStyle = new WriteCellStyle();//设置背景颜色headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//内容策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);EasyExcel.write(response.getOutputStream()).autoCloseStream(Boolean.FALSE).registerWriteHandler(horizontalCellStyleStrategy).head(mergeGoodsDetailHead()).sheet("商品信息")//获取数据填充.doWrite(goodsTemplateExcelVOList);} catch (Exception e) {}}

自定义头部

private List<List<String>> mergeGoodsDetailHead() {String title = "XXX信息统计报表";List<List<String>> list = new ArrayList<>();String erTitle = "MRO标品SKU 商品名称 品牌中文名称 品牌英文名称 品牌图片链接 商品链接 商品介绍 一级分类名称 二级分类名称 " +"三级分类名称 商品单位 商品型号 包装规格 税收编码 销售税税率 条形码 官网价 协议价 折扣率 允许退换货(允许，不允许) 上下架状态(下架，上架) 预计发货时间(天) " +"预计补货时间(天) 最小起订量 一级供货地址 二级供货地址 三级供货地址 库存所在地 库存 类目属性";Arrays.stream(erTitle.split(" ")).forEach(name -> {List<String> head = new ArrayList<>();head.add(title);head.add(name);list.add(head);});return list;}