【Easyexcel】java导入导出超大数据量的xlsx文件 解决方法

 

解决方法：

使用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;
    }
}

 

 

生成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;
    }

 

 

 

使用过程中报异常和处理的方法：

https://www.cnblogs.com/sxdcgaq8080/p/11791900.html