解决方法:
使用easyexcel解决超大数据量的导入导出xlsx文件
官网地址:
https://alibaba-easyexcel.github.io/
GitHub地址:
https://github.com/alibaba/easyexcel
使用示例:
Java数据类【重点是属性上的注解】:
package com.dmall.proengine.domain.man.partner.bean; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.dmall.proengine.domain.common.enums.PromotionStatusEnum; import com.dmall.proengine.sdk.enums.PromotionSubTypeEnum; import java.math.BigDecimal; import java.util.Date; /** * @Author: SXD * @Description: * @Date: create in 2019/9/26 11:43 */ @ColumnWidth(25) public class ProSkuSearchInfoDisplay { /** * 促销ID */ @ExcelProperty(value = "促销编码",index = 6) private String proId; /** * 参与类型 */ @ExcelIgnore private Integer itemType; /** * 商品sku 或 商品mc * DB查询出来的值 */ @ExcelIgnore private String itemCode; /** * 商品sku * 最终结果值 */ @ExcelProperty(value = "商品sku",index = 0) private Long sku; /** * 物料编码 */ @ExcelProperty(value = "物料编码",index = 1) private String matnrCode; /** * 商品名称 */ @ExcelProperty(value = "商品名称",index = 2) private String skuName; /** * 国条码 */ @ExcelProperty(value = "国条码",index = 3) private String barCode; /** * 商品MC * 最终结果值 */ @ExcelProperty(value = "商品MC",index = 4) private String skuMc; /** * 商品MC Name */ @ExcelIgnore private String skuMcName; /** * 促销档期 */ @ExcelProperty(value = "促销档期",index = 5) private String proSchedule; /** * 促销编码 */ @ExcelIgnore private String proCode; /** * 促销名称 */ @ExcelProperty(value = "促销名称",index =7) private String proName; /** * 促销详情 */ @ColumnWidth(50) @ExcelProperty(value = "促销详情",index =8) private String proDetail; /** * 促销类型 */ @ExcelIgnore private Integer proType; /** * 促销子类型 */ @ExcelIgnore private Integer proSubType; /** * 促销类型名称 */ @ExcelProperty(value = "促销类型",index =9) private String proTypeName; /** * 促销售价 单位:分 */ @ExcelProperty(value = "促销售价",index =10) private Double proPrice; /** * 促销折扣值 * 仅单品促销实际应用本字段 * 单品直降 101 skuPrice-rewardValue = proPrice * 单品特价 102 proPrice = rewardValue * 单品折扣 103 skuPrice*(rewardValue/10000) = proPrice */ @ExcelIgnore private Long rewardValue; /** * 商品原价 单位:分 */ @ExcelProperty(value = "商品原价",index =11) private Double skuPrice; /** * 促销状态 */ @ExcelIgnore private Integer proStatus; /** * 促销状态名称 */ @ExcelProperty(value = "促销状态",index =12) private String proStatusName; /** * PO订单号 暂无 * 采销系统相关 */ @ExcelIgnore private String poOrderCode; /** * STO订单号 暂无 * 采销系统相关 */ @ExcelIgnore private String stoOrderCode; /** * 预期到店日 暂无 * 采销系统相关 */ @ExcelIgnore private Date expectedDate; /** * 促销开始时间 */ @ExcelProperty(value = "促销开始时间",index =13) private Date proStartTime; /** * 促销结束时间 */ @ExcelProperty(value = "促销结束时间",index =14) private Date proEndTime; /** * 单品 三种折扣 计算促销价格 */ public void calcuProPrice(){ if (skuPrice != null){ if (proSubType == PromotionSubTypeEnum.SINGLE_CUT_PRICE.getValue()){ proPrice = skuPrice-rewardValue; } if (proSubType == PromotionSubTypeEnum.SINGLE_SPECIAL_PRICE.getValue()){ proPrice = (double)rewardValue; } if (proSubType == PromotionSubTypeEnum.SINGLE_REBATE.getValue()){ proPrice = skuPrice - BigDecimal.valueOf(skuPrice).subtract(BigDecimal.valueOf(rewardValue * skuPrice).divide(BigDecimal.valueOf(10000), 2, BigDecimal.ROUND_HALF_UP)).setScale(0, BigDecimal.ROUND_HALF_UP).longValue(); } } } /** * 组装最终展示数据 */ public void assembleParams(){ proTypeName = PromotionSubTypeEnum.getDesc(proSubType); proStatusName = PromotionStatusEnum.getDesc(proStatus); proPrice = proPrice != null ? proPrice/(double)100 : null; skuPrice = skuPrice != null ? skuPrice/(double)100 : null; } public String getBarCode() { return barCode; } public void setBarCode(String barCode) { this.barCode = barCode; } public String getProTypeName() { return proTypeName; } public void setProTypeName(String proTypeName) { this.proTypeName = proTypeName; } public String getProStatusName() { return proStatusName; } public void setProStatusName(String proStatusName) { this.proStatusName = proStatusName; } public Date getProStartTime() { return proStartTime; } public void setProStartTime(Date proStartTime) { this.proStartTime = proStartTime; } public Date getProEndTime() { return proEndTime; } public void setProEndTime(Date proEndTime) { this.proEndTime = proEndTime; } public Long getRewardValue() { return rewardValue; } public void setRewardValue(Long rewardValue) { this.rewardValue = rewardValue; } public String getProId() { return proId; } public void setProId(String proId) { this.proId = proId; } public Long getSku() { if (sku == null){ setSkuFromItemCode(); } return sku; } public void setSkuFromItemCode(){ setSku(Long.parseLong(itemCode)); } public void setSku(Long sku) { this.sku = sku; } public Integer getItemType() { return itemType; } public void setItemType(Integer itemType) { this.itemType = itemType; } public String getItemCode() { return itemCode; } public void setItemCode(String itemCode) { this.itemCode = itemCode; } public String getMatnrCode() { return matnrCode; } public void setMatnrCode(String matnrCode) { this.matnrCode = matnrCode; } public String getSkuName() { return skuName; } public void setSkuName(String skuName) { this.skuName = skuName; } public String getSkuMc() { return skuMc; } public void setSkuMc(String skuMc) { this.skuMc = skuMc; } public String getSkuMcName() { return skuMcName; } public void setSkuMcName(String skuMcName) { this.skuMcName = skuMcName; } public String getProSchedule() { return proSchedule; } public void setProSchedule(String proSchedule) { this.proSchedule = proSchedule; } public String getProCode() { return proCode; } public void setProCode(String proCode) { this.proCode = proCode; } public String getProName() { return proName; } public void setProName(String proName) { this.proName = proName; } public String getProDetail() { return proDetail; } public void setProDetail(String proDetail) { this.proDetail = proDetail; } public Integer getProType() { return proType; } public void setProType(Integer proType) { this.proType = proType; } public Integer getProSubType() { return proSubType; } public void setProSubType(Integer proSubType) { this.proSubType = proSubType; } public Double getProPrice() { return proPrice; } public void setProPrice(Double proPrice) { this.proPrice = proPrice; } public Double getSkuPrice() { return skuPrice; } public void setSkuPrice(Double skuPrice) { this.skuPrice = skuPrice; } public Integer getProStatus() { return proStatus; } public void setProStatus(Integer proStatus) { this.proStatus = proStatus; } public String getPoOrderCode() { return poOrderCode; } public void setPoOrderCode(String poOrderCode) { this.poOrderCode = poOrderCode; } public String getStoOrderCode() { return stoOrderCode; } public void setStoOrderCode(String stoOrderCode) { this.stoOrderCode = stoOrderCode; } public Date getExpectedDate() { return expectedDate; } public void setExpectedDate(Date expectedDate) { this.expectedDate = expectedDate; } }
生成xlsx文件:
private static final String UPLOAD_TEMP_FILE_NAME = "导出xlsx文件-%s.xlsx"; private File createXlsxFile2(List<ProSkuSearchInfoDisplay> list,String recordKey){ String filePath = getFilePath(recordKey); ExcelWriter excelWriter = EasyExcel.write(filePath, ProSkuSearchInfoDisplay.class).build(); WriteSheet writeSheet = EasyExcel.writerSheet("促销商品数据").build(); excelWriter.write(list, writeSheet); /// 千万别忘记finish 会帮忙关闭流 excelWriter.finish(); return new File(filePath); } /** * 获取临时文件路径 * @return */ private String getFilePath(String recordKey){ String path = ProExportSkuDataJob.class.getResource("/").getPath()+String.format(UPLOAD_TEMP_FILE_NAME, recordKey.substring(recordKey.lastIndexOf(":")+1)); DpeLogUtil.info("dpePartner#ProExportSkuDataJob createFilePath={"+path+"}"); return path; }