Easyexcel使用文档及动态列的实现

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。
我建议大家使用1.1.2-beta4版本，也就是官方给的最新版，maven仓库查到的最新版比这个版本略低，最显著的区别就是excel中数字的输出，最新版本到处excel会将数字专为数字格式，而maven库给出的最新版则会输出成文本格式。其他变化需要各位自己去发现。

官方文档地址

https://github.com/alibaba/easyexcel

添加依赖

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta4</version>
</dependency>

使用方法

1.表头带注解方法

这种方法首先需要一个继承BaseRowModel的模型，在Hap框架dto的角色就相当于这个模型，示例如下

package ect.report.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @ClassName: TraBilDetail
 * @Description: TODO
 * @Date: 2019-03-29 09:52
 * @Version 1.0
 */
public class TraBilDetail extends BaseRowModel {


    //开票申请号
    @ExcelProperty(value = {"开票申请号"}, index = 0)
    private String applyNum;

    //批次号
    @ExcelProperty(value = {"批次号"}, index = 1)
    private String batchNum;

    //制单部门
    @ExcelProperty(value = {"制单部门"}, index = 2)
    private String name;

    //客户名称
    @ExcelProperty(value = {"客户名称"}, index = 3)
    private String custName;

    //业务类型
    @ExcelProperty(value = {"业务类型"}, index = 4)
    private String busiType;

    //批次开票金额
    @ExcelProperty(value = {"批次开票金额"}, index = 5)
    private BigDecimal sumPrice;

    //批次开票重量
    @ExcelProperty(value = {"批次开票重量"}, index = 6)
    private BigDecimal invoWeight;

    //批次财务入总金额
    @ExcelProperty(value = {"批次财务入总金额"}, index = 7)
    private BigDecimal findInAmoundSum;

    //邮寄信息
    @ExcelProperty(value = {"邮寄信息"}, index = 8)
    private String mailAddress;

    private String contactName;

    private String telephone;



    /*=========================== 查询字段================================*/

    private Long comId;

    private Date applyDateStart;

    private Date applyDateEnd;
   /*=====================================*/
       get和set方法省略
   /*=====================================*/
   }

然后mapper.java,Service.java,～ServiceImpl.java都要去掉继承系统框架的base接口。mapper层写好查询出需要展示的数据，dto的注解中字段对应的显示。上述例子写的是单层头结构，如果想要写成双层或者多层，可以想一下示例一样添加。

package ect.report.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import java.math.BigDecimal;

/**
 * @ClassName:  BatchSupply
 * @Description: TODO
 * @Date:   2019-04-02 09:00
 * @Version 1.0
 */
public class BatchSupply extends BaseRowModel {

    //公司名称
    @ExcelProperty(value = {"在库货源","姓名"} ,index = 0)
    private String comName;

    //仓库名称
    @ExcelProperty(value = {"在库货源","仓库名称"} ,index = 1)
    private String subinvName;

    //产地
    @ExcelProperty(value = {"在库货源","产地"} ,index = 2)
    private String terrName;

    //等级
    @ExcelProperty(value = {"在库货源","等级"} ,index = 3)
    private String ctLevel;

    //长度
    @ExcelProperty(value = {"在库货源","长度"} ,index = 4)
    private String length;


    //长度均值
    @ExcelProperty(value = {"在库货源","长度均值"} ,index = 5)
    private String lengthMean;

    //马值
    @ExcelProperty(value = {"在库货源","马值"} ,index = 6)
    private String micron;

    //马值均值
    @ExcelProperty(value = {"在库货源","马值均值"} ,index = 7)
    private String micronMean;

    //强力
    @ExcelProperty(value = {"在库货源","强力"} ,index = 8)
    private String strong;

    //强力均值
    @ExcelProperty(value = {"在库货源","强力均值"} ,index = 9)
    private String strongMean;

    //提单号
    @ExcelProperty(value = {"在库货源","提单号"} ,index = 10)
    private String blNum;

    //入库单号
    @ExcelProperty(value = {"在库货源","入库单号"} ,index = 11)
    private String entryNum;


    //箱号
    @ExcelProperty(value = {"在库货源","箱号"} ,index = 12)
    private String batchNum;

    //入库件数
    @ExcelProperty(value = {"在库货源","入库件数"} ,index = 13)
    private BigDecimal batchQty;

    //销售合同号
    @ExcelProperty(value = {"在库货源","销售合同号"} ,index = 14)
    private String conNum;

    //出库单号
    @ExcelProperty(value = {"在库货源","出库单号"} ,index = 15)
    private String outNum;

    //出库件数
    @ExcelProperty(value = {"在库货源","出库件数"} ,index = 16)
    private BigDecimal outBatchQty;

    //在库箱数
    @ExcelProperty(value = {"在库货源","在库箱数"} ,index = 17)
    private String outStatus;

    //在库件数
    @ExcelProperty(value = {"在库货源","在库件数"} ,index = 18)
    private BigDecimal wareQty;

    //采购合同单价
    @ExcelProperty(value = {"在库货源","采购合同单价"} ,index = 19)
    private BigDecimal conPrice;

    //财务入库成本
    @ExcelProperty(value = {"在库货源","财务入库成本"} ,index = 20)
    private BigDecimal finaPrice;

    //初始财务入库成本(USD)
    @ExcelProperty(value = {"在库货源","财务入库成本"} ,index = 21)
    private BigDecimal firstUsdPrice;

    //初始财务入库成本(CNY)
    @ExcelProperty(value = {"在库货源","初始财务入库成本"} ,index = 22)
    private BigDecimal firstRmbPrice;

    private String lengthMax;

    private String lengthMin;

    private String micronMax;

    private String micronMin;

    private String strongMax;

    private String strongMin;

    private String terrCode;

    private String comCode;

    private String subinvId;

    private String isBatchFlag;


    private Long specId;

    /*=====================================*/
       get和set方法省略
   /*=====================================*/
}

然后在Service层组装业务逻辑并且输出excel文档。

package ect.report.service.impl;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import ect.inv.util.StringUtils;
import ect.report.dto.TraBilDetail;
import ect.report.mapper.TraBilDetailMapper;
import ect.report.utils.EasyEacelUtils;
import jodd.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import ect.report.service.ITraBilDetailService;
import org.springframework.transaction.annotation.Transactional;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @ClassName:  TraBilDetailServiceImpl
 * @Description: TODO
 * @Date:   2019-03-29 09:53
 * @Version 1.0
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class TraBilDetailServiceImpl implements ITraBilDetailService{


    @Autowired
    private TraBilDetailMapper traBilDetailMapper;

    @Override
    public void export(HttpServletRequest request, TraBilDetail traBilDetail, HttpServletResponse httpServletResponse) throws IOException {
        String fileName="内贸开票明细表";
        //组装输出流
        ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);
        try {
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, TraBilDetail.class);
            //自适应宽度
            sheet1.setAutoWidth(Boolean.TRUE);
            //添加数据
            writer.write(traBilDetailMapper.selectTraBilDetail(traBilDetail), sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

这样一个非常简单的excel导出就完成了，这种方法适合于常规的excle导出。如果sql查询的数据还不能瞒住需求，需要Java中继续添加业务逻辑的话，可以继续组装成一个返回List的方法。示例如下

package ect.report.service.impl;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import ect.co.mapper.ConContractMapper;
import ect.fs.mapper.FsPayAppExpMapper;
import ect.fs.mapper.FsPayAppGooMapper;
import ect.inv.mapper.WarehousingMapper;
import ect.report.dto.ConSheet;
import ect.report.mapper.ConSheetMapper;
import ect.report.service.IConSheetService;
import ect.report.utils.EasyEacelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;

@Service
@Transactional(rollbackFor = Exception.class)
public class ConSheetServiceImpl implements IConSheetService{
    @Autowired
    private ConSheetMapper sheetMapper;

    @Autowired
    private FsPayAppGooMapper fsPayAppGooMapper;
    @Autowired
    private FsPayAppExpMapper fsPayAppExpMapper;
    @Autowired
    private ConContractMapper conContractMapper;
    @Autowired
    private WarehousingMapper warehousingMapper;

    private static final String REMORK_FIX = "转仓单重量:";

    @Override
    public void export(HttpServletRequest request, ConSheet conSheet, HttpServletResponse httpServletResponse) throws IOException {
        String fileName="联营业务表";
        List<ConSheet> dataList = loadReportData(conSheet);
        ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);
        try {
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, ConSheet.class);
            //Table table = new Table(2);

            sheet1.setAutoWidth(Boolean.TRUE);
            writer.write(dataList, sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 将sql中查询出的数据添加合计逻辑
     * @param conSheet
     * @return
     */
    private List<ConSheet> loadReportData(ConSheet conSheet) {
        //汇总信息
        ConSheet conSheetSum = new ConSheet();

        BigDecimal sumEntryWeight = BigDecimal.ZERO;
        BigDecimal sumAppAmt = BigDecimal.ZERO;
        BigDecimal sumOutBatchWeight = BigDecimal.ZERO;
        BigDecimal sumNoOutBatchWeight = BigDecimal.ZERO;
        BigDecimal sumtBatchWeight = BigDecimal.ZERO;
        BigDecimal sumInvenWeight = BigDecimal.ZERO;
        BigDecimal sumEntryAmt = BigDecimal.ZERO;
        BigDecimal sumSumExp = BigDecimal.ZERO;
        BigDecimal sumInterest = BigDecimal.ZERO;
        BigDecimal sumRiskDegree = BigDecimal.ZERO;
        BigDecimal sumCheckAmt = BigDecimal.ZERO;
        BigDecimal sumInvoWeight = BigDecimal.ZERO;

        BigDecimal sumToBatchWeight = BigDecimal.ZERO;
        List<ConSheet> uniConExportList = sheetMapper.selectUniCon(conSheet);
        for (ConSheet uniConExport : uniConExportList) {
            Long conId = uniConExport.getConId();
            // 查询 我司支付
            BigDecimal amtSum = fsPayAppGooMapper.selectUniConAmtByConId(conId.floatValue());
            uniConExport.setAppAmt(amtSum);
            // 费用
            BigDecimal sumExp = fsPayAppExpMapper.selectSumExpByConId(conId.floatValue());
            uniConExport.setSumExp(sumExp);

            // 未销数量
            BigDecimal noOutBatchWeight = conContractMapper.selectQtyNotSoldByConId(conId.floatValue());
            uniConExport.setNoOutBatchWeight(noOutBatchWeight);
            // 求已销数量
            BigDecimal outBatchWeight = conContractMapper.selectQtySoldByConId(conId.floatValue());
            uniConExport.setOutBatchWeight(outBatchWeight);

            // 风险度
            BigDecimal invenWeight = uniConExport.getInvenWeight();
            if (invenWeight != null && invenWeight.compareTo(BigDecimal.ZERO) != 0) {
                // 我司支付
                BigDecimal appAmt = uniConExport.getAppAmt();

                //回笼资金
                BigDecimal entryAmt = uniConExport.getEntryAmt();
                // 费用
                BigDecimal exp = uniConExport.getSumExp();
                // 利息
                BigDecimal interest = uniConExport.getInterest();
                // 排除空
                appAmt = appAmt == null ? BigDecimal.ZERO : appAmt;
                entryAmt = entryAmt == null ? BigDecimal.ZERO : entryAmt;
                exp = exp == null ? BigDecimal.ZERO : exp;
                interest = interest == null ? BigDecimal.ZERO : interest;
                BigDecimal sum = appAmt.subtract(entryAmt).add(exp).add(interest);
                uniConExport.setRiskDegree(sum.divide(invenWeight, 2, BigDecimal.ROUND_HALF_UP));

            }

            // 备注
            BigDecimal toBatchWeight = warehousingMapper.selectToBatchWeightByConId(conId.floatValue());
            toBatchWeight = toBatchWeight == null ? BigDecimal.ZERO : toBatchWeight;
            uniConExport.setRemark(REMORK_FIX + toBatchWeight.toString());
            sumToBatchWeight = sumToBatchWeight.add(toBatchWeight);
            sumEntryWeight = sumEntryWeight.add(uniConExport.getEntryWeight());
            sumAppAmt = sumAppAmt.add(uniConExport.getAppAmt()==null?BigDecimal.ZERO:uniConExport.getAppAmt());
            sumOutBatchWeight = sumOutBatchWeight.add(uniConExport.getOutBatchWeight()==null?BigDecimal.ZERO:uniConExport.getOutBatchWeight());
            sumNoOutBatchWeight = sumNoOutBatchWeight.add(uniConExport.getNoOutBatchWeight()==null?BigDecimal.ZERO:uniConExport.getNoOutBatchWeight());
            sumtBatchWeight = sumtBatchWeight.add(uniConExport.getBatchWeight());
            sumInvenWeight = sumInvenWeight.add(uniConExport.getInvenWeight());
            sumEntryAmt = sumEntryAmt.add(uniConExport.getEntryAmt());
            sumSumExp = sumSumExp.add(uniConExport.getSumExp() == null ? BigDecimal.ZERO:uniConExport.getSumExp());
            sumInterest = sumInterest.add(uniConExport.getInterest()==null?BigDecimal.ZERO:uniConExport.getInterest());
            sumRiskDegree = sumRiskDegree.add(uniConExport.getRiskDegree()==null?BigDecimal.ZERO:uniConExport.getRiskDegree());
            sumCheckAmt = sumCheckAmt.add(uniConExport.getCheckAmt()==null?BigDecimal.ZERO:uniConExport.getCheckAmt());
            sumInvoWeight = sumInvoWeight.add(uniConExport.getInvoWeight());
        }
        List<ConSheet> sheetList = groupByParty(uniConExportList);
        //汇总信息

        //conSheetSum.setEntryWeight(sheetList.stream().map(ConSheet::getEntryWeight).reduce(BigDecimal.ZERO, BigDecimal::add));
        conSheetSum.setEntryWeight(sumEntryWeight);
        conSheetSum.setAppAmt(sumAppAmt);
        conSheetSum.setOutBatchWeight(sumOutBatchWeight);
        conSheetSum.setNoOutBatchWeight(sumNoOutBatchWeight);
        conSheetSum.setBatchWeight(sumtBatchWeight);

        conSheetSum.setInvenWeight(sumInvenWeight);
        conSheetSum.setEntryAmt(sumEntryAmt);
        conSheetSum.setSumExp(sumSumExp);
        conSheetSum.setInterest(sumInterest);

        conSheetSum.setRiskDegree(sumRiskDegree);
        conSheetSum.setCheckAmt(sumCheckAmt);
        conSheetSum.setInvoWeight(sumInvoWeight);
        conSheetSum.setRemark(REMORK_FIX +sumToBatchWeight);
        conSheetSum.setPartyName("合计: ");
        List<ConSheet> conSheets = groupByParty(uniConExportList);
        conSheets.add(conSheetSum);
        return conSheets;
    }

    /**
     * 根据部门进行分组
     * @param uniConExportList
     * @return
     */
    private List<ConSheet> groupByParty(List<ConSheet> uniConExportList) {
        LinkedList<ConSheet> finalList = new LinkedList<>();
        HashSet<String> partySet = new HashSet<>();
//        LinkedHashMap<String,Integer> partyMap = new LinkedHashMap<>();
        for(ConSheet uniConExport : uniConExportList){
            if (partySet.add(uniConExport.getPartyName())) {
                finalList.addLast(uniConExport);
            }else {
                int i =0;
                boolean findFlag = false;
                // 插入
                for(ConSheet export : finalList ){
                    if(export.getPartyName().equals(uniConExport.getPartyName())){
                        findFlag = true;
                    }else if(findFlag){
                        break;
                    }
                    i++;
                }
                finalList.add(i,uniConExport);
            }
        }
        return finalList;
    }
}

官方示例dome

OutputStream out = new FileOutputStream("/Users/jipengfei/2007.xlsx");
ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);

//写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
Sheet sheet1 = new Sheet(1, 3);
sheet1.setSheetName("第一个sheet");
//设置列宽 设置每列的宽度
Map columnWidth = new HashMap();
columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);
sheet1.setColumnWidthMap(columnWidth);
sheet1.setHead(createTestListStringHead());
//or 设置自适应宽度
//sheet1.setAutoWidth(Boolean.TRUE);
writer.write1(createTestListObject(), sheet1);

//写第二个sheet sheet2  模型上打有表头的注解，合并单元格
Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二个sheet", null);
sheet2.setTableStyle(createTableStyle());
writer.write(createTestListJavaMode(), sheet2);

//写第三个sheet包含多个table情况
Sheet sheet3 = new Sheet(3, 0);
sheet3.setSheetName("第三个sheet");
Table table1 = new Table(1);
table1.setHead(createTestListStringHead());
writer.write1(createTestListObject(), sheet3, table1);

//写sheet2  模型上打有表头的注解
Table table2 = new Table(2);
table2.setTableStyle(createTableStyle());
table2.setClazz(JavaModel1.class);
writer.write(createTestListJavaMode(), sheet3, table2);

//关闭资源
writer.finish();
out.close();

2.表头不带注解的方法

这种dto的写法跟上述的dto写法基本相同，就是去掉了注解。也可以不用dto，直接用List来代替dto的作用，其他都一样。表头带注解的方法不用自己写表头，不带注解的方法需要自己去组装表头，但是这种方法可以去调整表头字段的宽度。带注解的方法表头会自适应一个宽度出来。
Service方法示例如下

package ect.report.service.impl;

import ch.qos.logback.classic.Logger;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.hand.hap.core.IRequest;
import com.hand.hap.fnd.dto.Company;
import com.hand.hap.fnd.service.ICompanyService;
import ect.fs.dto.DailyRate;
import ect.fs.dto.FsComAttr;
import ect.fs.service.IDailyRateService;
import ect.fs.service.IFsComAttrService;
import ect.report.dto.InvomeCostDetail;
import ect.report.mapper.InvomeCostDetailMapper;
import ect.report.utils.EasyEacelUtils;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import ect.report.service.IInvomeCostDetailService;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClassName: InvomeCostDetailServiceImpl
 * @Description:TODO
 * @Date: 2019-03-26 16:14
 * @Version 1.0
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class InvomeCostDetailServiceImpl implements IInvomeCostDetailService {

    private Logger logger = (Logger) LoggerFactory.getLogger(InvomeCostDetailServiceImpl.class);

    @Autowired
    private InvomeCostDetailMapper invomeCostDetailMapper;

    @Autowired
    private IDailyRateService dailyRateService;

    @Autowired
    private ICompanyService companyService;

    @Autowired
    private IFsComAttrService fsComAttrService;


    //自营人民币销售合同
    private final static String RMB_SO_CON_NUM = "SESRMBSAL";

    //自营美元销售合同
    private final static String USD_SO_CON_NUM = "SESUSBSAL";

    public List<InvomeCostDetail> loadReportData(IRequest requestContext, InvomeCostDetail detail) {
        List<Company> companyList = companyService.selectComIdByRole(requestContext.getRoleId());
        String currencyCode = null;
        if (!companyList.isEmpty()) {
            Long comId = companyList.get(0).getCompanyId();
            if (comId != null) {
                FsComAttr fsComAttr = fsComAttrService.selectByComId(comId.floatValue());
                currencyCode = fsComAttr.getCurrencyCode();
            }
        }
        final String comCurrencyCode = currencyCode;
        DailyRate dailyRate = new DailyRate();
        dailyRate.setConversionDate(detail.getPostDateEnd());
        dailyRate.setFromCurrency(DailyRate.USD);
        dailyRate.setToCurrency(DailyRate.CNY);
        dailyRate.setConversionType(DailyRate.AVERAGE);
        List<DailyRate> dailyRates = dailyRateService.getRateByNew(dailyRate);
        if (dailyRates.size()>0){
            dailyRate=dailyRates.get(0);
        }
        final BigDecimal rate = dailyRate.getRate();

        List<InvomeCostDetail> incomeCostDetails = invomeCostDetailMapper.selectIncomDetail(detail);
        incomeCostDetails.stream().forEach(incomeCostDetail -> {
            if (RMB_SO_CON_NUM.equals(incomeCostDetail.getDetType())) {
                //发票金额（不含税）
                incomeCostDetail.setInvoApplyAmount(incomeCostDetail.getInvoApplyAmountRmb());
                //incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountRmb());
            }
            if (USD_SO_CON_NUM.equals(incomeCostDetail.getDetType())) {
                //发票金额（不含税）
                incomeCostDetail.setInvoApplyAmount(incomeCostDetail.getInvoApplyAmountUsd());
                //  incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountUsd());
            }

            //出库金额（元）
            if (incomeCostDetail.getInvoOutAmountCn() == null) {
                incomeCostDetail.setInvoOutAmountCn(incomeCostDetail.getInvoOutAmount());
            }
            //出库汇率
            if (incomeCostDetail.getInvoOutAmountCn() != null && incomeCostDetail.getInvoOutAmount() != null && incomeCostDetail.getInvoOutAmount().compareTo(BigDecimal.ZERO) != 0) {
                incomeCostDetail.setRate(incomeCostDetail.getInvoOutAmountCn().divide(incomeCostDetail.getInvoOutAmount(), 4, BigDecimal.ROUND_HALF_UP));
            }

            BigDecimal finaRate = incomeCostDetail.getRate() == null ? BigDecimal.ONE : incomeCostDetail.getRate();
            if ("进口".equals(incomeCostDetail.getBusiType())) {
                incomeCostDetail.setInvoApplyAmountYuan(incomeCostDetail.getInvoApplyAmount());
            } else if ("转口".equals(incomeCostDetail.getBusiType())) {
                if (finaRate.compareTo(BigDecimal.ONE) == 0) {
                    finaRate = rate;
                }
                incomeCostDetail.setInvoApplyAmountYuan(incomeCostDetail.getInvoApplyAmount().multiply(finaRate));
            }

            //财务出库单价（人民币/吨） =出库金额（元）/财务出库重量
            if (incomeCostDetail.getInvoOutAmountCn() != null && incomeCostDetail.getInvoOutWeight() != null && incomeCostDetail.getInvoOutWeight().compareTo(BigDecimal.ZERO) != 0) {
                incomeCostDetail.setInvoOutPriceCn(incomeCostDetail.getInvoOutAmountCn().divide(incomeCostDetail.getInvoOutWeight(), 10, BigDecimal.ROUND_HALF_UP));
            }
            ///财务出库单价
            if (incomeCostDetail.getInvoOutAmount() != null && incomeCostDetail.getInvoOutWeight() != null && incomeCostDetail.getInvoOutWeight().compareTo(BigDecimal.ZERO) != 0) {
                incomeCostDetail.setInvoOutPrice(incomeCostDetail.getInvoOutAmount().divide(incomeCostDetail.getInvoOutWeight(), 10, BigDecimal.ROUND_HALF_UP));
            }
            //对应初始采购成本金额
            incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountUsd());
            //对应初始采购成本金额（元）
            incomeCostDetail.setPoCostAmountCn(incomeCostDetail.getPoCostAmountRmb());

            //现货毛利（合并）原币 发票金额（不含税）-对应初始采购成本金额（原币）
            if (incomeCostDetail.getInvoApplyAmount() != null && incomeCostDetail.getPoCostAmount() != null) {
                if (DailyRate.USD.equals(incomeCostDetail.getInCurrencyCode()) && DailyRate.CNY.equals(incomeCostDetail.getOutCurrencyCode())) {
                    incomeCostDetail.setIncomeMeshAmount((incomeCostDetail.getInvoApplyAmount().divide(rate, 10, BigDecimal.ROUND_HALF_UP)).subtract(incomeCostDetail.getPoCostAmount()));
                } else {
                    incomeCostDetail.setIncomeMeshAmount(incomeCostDetail.getInvoApplyAmount().subtract(incomeCostDetail.getPoCostAmount()));
                }
            }
            //现货毛利（合并）人民币  发票金额（不含税）-对应初始采购成本金额（元）
            BigDecimal applyAmount = BigDecimal.ZERO;
            if ("进口".equals(incomeCostDetail.getBusiType())) {
                applyAmount = incomeCostDetail.getInvoApplyAmount();
            } else {
                applyAmount = incomeCostDetail.getSumRmb();
            }
            if (applyAmount != null && incomeCostDetail.getPoCostAmountCn() != null) {
                if (incomeCostDetail.getInvoApplyAmountYuan() != null && incomeCostDetail.getPoCostAmountCn() != null) {
                    incomeCostDetail.setIncomeMeshAmountCn(incomeCostDetail.getInvoApplyAmountYuan().subtract(incomeCostDetail.getPoCostAmountCn()));
                }
            }

            if ("转口".equals(incomeCostDetail.getBusiType()) && ("中纺棉花(香港)有限公司".equals(incomeCostDetail.getComName()))) {
                logger.info("2111");
            }

            //现货毛利（合并）人民币-财务口径  发票金额（不含税）-对应初始采购成本金额（元）
            if ("进口".equals(incomeCostDetail.getBusiType()) || ("转口".equals(incomeCostDetail.getBusiType()) && (!"1445".equals(incomeCostDetail.getComCode())))) {
                if (incomeCostDetail.getIncomeMeshAmountCn() != null) {
                    incomeCostDetail.setIncomeMeshAmountCnFina(incomeCostDetail.getIncomeMeshAmountCn());
                }
            } else if ("转口".equals(incomeCostDetail.getBusiType()) && ("1445".equals(incomeCostDetail.getComCode()))) {
                if (incomeCostDetail.getIncomeMeshAmountCn() != null && rate!=null) {
                    incomeCostDetail.setIncomeMeshAmountCnFina(incomeCostDetail.getIncomeMeshAmount().multiply(rate));
                }
            }


            //对应初始采购成本单价
            if (incomeCostDetail.getPoCostAmount() != null && incomeCostDetail.getPoCostBatchWeight() != null) {
                incomeCostDetail.setPoCostPricce(incomeCostDetail.getPoCostAmount().divide(incomeCostDetail.getPoCostBatchWeight(), 10, BigDecimal.ROUND_HALF_UP));
            }
            //对应初始采购成本单价（元/吨）
            if (incomeCostDetail.getPoCostAmountCn() != null && incomeCostDetail.getPoCostBatchWeight() != null) {
                incomeCostDetail.setPoCostPricceCn(incomeCostDetail.getPoCostAmountCn().divide(incomeCostDetail.getPoCostBatchWeight(), 10, BigDecimal.ROUND_HALF_UP));
            }
            //对应初始财务入库汇率
            if (incomeCostDetail.getPoCostAmountCn() != null && incomeCostDetail.getPoCostAmount() != null && BigDecimal.ZERO.compareTo(incomeCostDetail.getPoCostAmount()) != 0) {
                //对应初始采购成本金额/对应初始采购成本金额
                incomeCostDetail.setPoRate(incomeCostDetail.getPoCostAmountCn().divide(incomeCostDetail.getPoCostAmount(), 10, BigDecimal.ROUND_HALF_UP));
            }

            //现货毛利（独立）原币 发票金额（不含税）-财务出库金额（原币）
            if (incomeCostDetail.getInvoApplyAmount() != null && incomeCostDetail.getInvoOutAmount() != null) {
                if (DailyRate.USD.equals(incomeCostDetail.getInCurrencyCode()) && DailyRate.CNY.equals(incomeCostDetail.getOutCurrencyCode())) {
                    incomeCostDetail.setIncomeSelfAmount(incomeCostDetail.getInvoApplyAmount().divide(rate, 10, BigDecimal.ROUND_HALF_UP).subtract(incomeCostDetail.getInvoOutAmount()));
                } else {
                    incomeCostDetail.setIncomeSelfAmount(incomeCostDetail.getInvoApplyAmount().subtract(incomeCostDetail.getInvoOutAmount()));
                }
            }

            //现货毛利（独立）人民币 发票金额（不含税）-财务出库金额（元）
            if (applyAmount != null && incomeCostDetail.getInvoOutAmountCn() != null) {
                if (DailyRate.USD.equals(comCurrencyCode)) {
                    incomeCostDetail.setIncomeSelfAmountCn(applyAmount.multiply(rate).subtract(incomeCostDetail.getInvoOutAmountCn()));
                } else {
                    incomeCostDetail.setIncomeSelfAmountCn(applyAmount.subtract(incomeCostDetail.getInvoOutAmountCn()));
                }
            }

            //现货毛利（独立）人民币-财务口径  发票金额（不含税）-财务出库金额（元）
            if ("进口".equals(incomeCostDetail.getBusiType()) || ("转口".equals(incomeCostDetail.getBusiType()) && (!"1445".equals(incomeCostDetail.getComCode())))) {
                if (incomeCostDetail.getIncomeSelfAmountCn() != null) {
                    incomeCostDetail.setIncomeSelfAmountCnFina(incomeCostDetail.getIncomeSelfAmountCn());
                }
            } else if ("转口".equals(incomeCostDetail.getBusiType()) && ("1445".equals(incomeCostDetail.getComCode()))) {
                if (incomeCostDetail.getIncomeSelfAmount() != null && rate!=null) {
                    incomeCostDetail.setIncomeSelfAmountCnFina(incomeCostDetail.getIncomeSelfAmountCn().multiply(rate));
                }
            }


            if ("转口".equals(incomeCostDetail.getBusiType()) && incomeCostDetail.getRate() != null && incomeCostDetail.getRate().compareTo(BigDecimal.ONE) == 0) {
                incomeCostDetail.setInvoOutAmountCn(incomeCostDetail.getInvoOutAmountCn().multiply(incomeCostDetail.getRate()));
            }


        });

        return incomeCostDetails;
    }

    @Override
    public void export(IRequest requestContext, HttpServletRequest request, InvomeCostDetail costDetail, HttpServletResponse httpServletResponse) throws IOException {
        String fileName = "收入成本毛利表";
        ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);
        try {
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, InvomeCostDetail.class);
            Map columnWidth = new HashMap();
            columnWidth.put(0, 5000);
            columnWidth.put(1, 5000);
            columnWidth.put(2, 5000);
            columnWidth.put(3, 5000);
            columnWidth.put(4, 5000);
            columnWidth.put(5, 5000);
            columnWidth.put(6, 5000);
            columnWidth.put(7, 7000);
            columnWidth.put(8, 7000);
            columnWidth.put(9, 7000);
            columnWidth.put(10, 7000);
            columnWidth.put(11, 9000);
            columnWidth.put(12, 7000);
            columnWidth.put(13, 7000);
            columnWidth.put(14, 9000);
            columnWidth.put(15, 9000);
            columnWidth.put(16, 10000);
            columnWidth.put(17, 9000);
            columnWidth.put(18, 7000);
            columnWidth.put(19, 7000);
            columnWidth.put(20, 7000);
            columnWidth.put(21, 7000);
            columnWidth.put(22, 7000);
            sheet1.setColumnWidthMap(columnWidth);
            sheet1.setHead(createListHeading(costDetail.getPostDateStart() == null ? null : new SimpleDateFormat("yyyy-MM-dd").format(costDetail.getPostDateStart()),
                    costDetail.getPostDateEnd() == null ? null : new SimpleDateFormat("yyyy-MM-dd").format(costDetail.getPostDateEnd())));
            writer.write(loadReportData(requestContext, costDetail), sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private List<List<String>> createListHeading(String startDate, String endDate) {
        List<List<String>> head = new ArrayList<List<String>>();
        List<String> headCoulumn1 = new ArrayList<String>();
        List<String> headCoulumn2 = new ArrayList<String>();
        List<String> headCoulumn3 = new ArrayList<String>();
        List<String> headCoulumn4 = new ArrayList<String>();
        List<String> headCoulumn5 = new ArrayList<String>();
        List<String> headCoulumn6 = new ArrayList<String>();
        List<String> headCoulumn7 = new ArrayList<String>();
        List<String> headCoulumn8 = new ArrayList<String>();
        List<String> headCoulumn9 = new ArrayList<String>();
        List<String> headCoulumn10 = new ArrayList<String>();
        List<String> headCoulumn11 = new ArrayList<String>();
        List<String> headCoulumn12 = new ArrayList<String>();
        List<String> headCoulumn13 = new ArrayList<String>();
        List<String> headCoulumn14 = new ArrayList<String>();
        List<String> headCoulumn15 = new ArrayList<String>();
        List<String> headCoulumn16 = new ArrayList<String>();
        List<String> headCoulumn17 = new ArrayList<String>();
        List<String> headCoulumn18 = new ArrayList<String>();
        List<String> headCoulumn19 = new ArrayList<String>();
        List<String> headCoulumn20 = new ArrayList<String>();
        List<String> headCoulumn21 = new ArrayList<String>();
        List<String> headCoulumn22 = new ArrayList<String>();
        List<String> headCoulumn23 = new ArrayList<String>();
        List<String> headCoulumn24 = new ArrayList<String>();
        List<String> headCoulumn25 = new ArrayList<String>();
        List<String> headCoulumn26 = new ArrayList<String>();


        headCoulumn1.add("收入成本毛利表");
        headCoulumn1.add("开票起始日");
        headCoulumn1.add("公司名称");
        headCoulumn2.add("收入成本毛利表");
        headCoulumn2.add(startDate);
        headCoulumn2.add("销售客户");
        headCoulumn3.add("收入成本毛利表");
        headCoulumn3.add("");
        headCoulumn3.add("合同号");
        headCoulumn4.add("收入成本毛利表");
        headCoulumn4.add("");
        headCoulumn4.add("开票申请号");
        headCoulumn5.add("收入成本毛利表");
        headCoulumn5.add("");
        headCoulumn5.add("过账日期");
        headCoulumn6.add("收入成本毛利表");
        headCoulumn6.add("");
        headCoulumn6.add("业务类型");
        headCoulumn7.add("收入成本毛利表");
        headCoulumn7.add("开票截止日");
        headCoulumn7.add("发票数量");
        headCoulumn8.add("收入成本毛利表");
        headCoulumn8.add(endDate);
        headCoulumn8.add("发票金额(不含税)");
        headCoulumn9.add("收入成本毛利表");
        headCoulumn9.add(endDate);
        headCoulumn9.add("发票金额(不含税-人民币)");
        headCoulumn10.add("收入成本毛利表");
        headCoulumn10.add("");
        headCoulumn10.add("财务出库数量");
        headCoulumn11.add("收入成本毛利表");
        headCoulumn11.add("");
        headCoulumn11.add("财务出库单价(原币)");
        headCoulumn12.add("收入成本毛利表");
        headCoulumn12.add("");
        headCoulumn12.add("财务出库金额(原币)");
        headCoulumn13.add("收入成本毛利表");
        headCoulumn13.add("");
        headCoulumn13.add("财务出库单价(人民币/吨)");
        headCoulumn14.add("收入成本毛利表");
        headCoulumn14.add("");
        headCoulumn14.add("财务出库金额(元)");
        headCoulumn15.add("收入成本毛利表");
        headCoulumn15.add("");
        headCoulumn15.add("财务出库汇率");
        headCoulumn16.add("收入成本毛利表");
        headCoulumn16.add("");
        headCoulumn16.add("对应初始采购成本单价(原币)");
        headCoulumn17.add("收入成本毛利表");
        headCoulumn17.add("");
        headCoulumn17.add("对应初始采购成本金额(原币)");
        headCoulumn18.add("收入成本毛利表");
        headCoulumn18.add("");
        headCoulumn18.add("对应初始采购成本金额(元)");
        headCoulumn19.add("收入成本毛利表");
        headCoulumn19.add("");
        headCoulumn19.add("对应初始采购成本单价（元/吨）");
        headCoulumn20.add("收入成本毛利表");
        headCoulumn20.add("");
        headCoulumn20.add("对应初始财务入库汇率");
        headCoulumn21.add("收入成本毛利表");
        headCoulumn21.add("");
        headCoulumn21.add("现货毛利(合并)原币");
        headCoulumn22.add("收入成本毛利表");
        headCoulumn22.add("");
        headCoulumn22.add("现货毛利(合并)人民币");
        headCoulumn23.add("收入成本毛利表");
        headCoulumn23.add("");
        headCoulumn23.add("现货毛利(合并)人民币-财务口径");
        headCoulumn24.add("收入成本毛利表");
        headCoulumn24.add("");
        headCoulumn24.add("现货毛利(独立)原币");
        headCoulumn25.add("收入成本毛利表");
        headCoulumn25.add("");
        headCoulumn25.add("现货毛利(独立)人民币");
        headCoulumn26.add("收入成本毛利表");
        headCoulumn26.add("");
        headCoulumn26.add("现货毛利(独立)人民币-财务口径");
        head.add(headCoulumn1);
        head.add(headCoulumn2);
        head.add(headCoulumn3);
        head.add(headCoulumn4);
        head.add(headCoulumn5);
        head.add(headCoulumn6);
        head.add(headCoulumn7);
        head.add(headCoulumn8);
        head.add(headCoulumn9);
        head.add(headCoulumn10);
        head.add(headCoulumn11);
        head.add(headCoulumn12);
        head.add(headCoulumn13);
        head.add(headCoulumn14);
        head.add(headCoulumn15);
        head.add(headCoulumn16);
        head.add(headCoulumn17);
        head.add(headCoulumn18);
        head.add(headCoulumn19);
        head.add(headCoulumn20);
        head.add(headCoulumn21);
        head.add(headCoulumn22);
        head.add(headCoulumn23);
        head.add(headCoulumn24);
        head.add(headCoulumn25);
        head.add(headCoulumn26);
        return head;
    }
}

官方示例demo

ExcelWriter writer = EasyExcelFactory.getWriter(out);

//写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
Sheet sheet1 = new Sheet(1, 3);
sheet1.setSheetName("第一个sheet");
//设置列宽 设置每列的宽度
Map columnWidth = new HashMap();
columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);
sheet1.setColumnWidthMap(columnWidth);
sheet1.setHead(createTestListStringHead());
//or 设置自适应宽度
//sheet1.setAutoWidth(Boolean.TRUE);
writer.write1(createTestListObject(), sheet1);

//写第二个sheet sheet2  模型上打有表头的注解，合并单元格
Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二个sheet", null);
sheet2.setTableStyle(createTableStyle());
writer.write(createTestListJavaMode(), sheet2);

//写第三个sheet包含多个table情况
Sheet sheet3 = new Sheet(3, 0);
sheet3.setSheetName("第三个sheet");
Table table1 = new Table(1);
table1.setHead(createTestListStringHead());
writer.write1(createTestListObject(), sheet3, table1);

//写sheet2  模型上打有表头的注解
Table table2 = new Table(2);
table2.setTableStyle(createTableStyle());
table2.setClazz(JavaModel1.class);
writer.write(createTestListJavaMode(), sheet3, table2);

//关闭资源
writer.finish();
out.close();

总结

以上举例是常用的两种组装数据成excel的方法，第一种适用常规的excel导出，但是表头不能控制，数据也必须跟dto中对应，限制相对大一点。第二种方法灵活行比较大，表头可以自己组装，数据也不用局限dto。在实际运用中就最大的不同就是第二种可以做动态列，但是由于第一种因为其局限性就无法完成动态列的实现，但是它却省了很多功夫来组装数据。两者各有优劣，就看实际的业务需求了。
当然还有同一个sheet中使用table来实现两部分数据块的展示方法等等，这些都可以从官方文档中找个具体的使用方法。这里就不做详细阐述了，本文主要就是讲解数据的组装逻辑方法。

作者：LCF_全博
链接：https://www.jianshu.com/p/efe2fff4ea69
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。