解决方法：
使用easyexcel解决超大数据量的导入导出xlsx文件
easyexcel最大支持行数 1048576。
官网地址：
https://alibaba-easyexcel.github.io/
GitHub地址：
https://github.com/alibaba/easyexcel
使用示例：
Java数据类【重点是属性上的注解】：
package com.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.proengine.domain.common.enums.PromotionStatusEnum;
import com.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;
}
}
View Code
生成xlsx文件：
private static final String UPLOAD_TEMP_FILE_NAME = "导出xlsx文件-%s.xlsx";
private File createXlsxFile2(List
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;
}
使用过程中报异常和处理的方法：
https://www.cnblogs.com/sxdcgaq8080/p/11791900.html
发表评论