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库给出的最新版则会输出成文本格式。其他变化需要各位自己去发现。

官方文档地址

  1. https://github.com/alibaba/easyexcel

添加依赖

  1.  <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
  2. <dependency>
  3.     <groupId>com.alibaba</groupId>
  4.     <artifactId>easyexcel</artifactId>
  5.     <version>1.1.2-beta4</version>
  6. </dependency>

使用方法

1.表头带注解方法

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

  1. package ect.report.dto;
  3. import com.alibaba.excel.annotation.ExcelProperty;
  4. import com.alibaba.excel.metadata.BaseRowModel;
  5. import java.math.BigDecimal;
  6. import java.util.Date;
  8. /**
  9.  * @ClassName: TraBilDetail
  10.  * @Description: 内贸开票明细模型
  11.  * @Author: bo.quan@hand-china.com (全博)
  12.  * @Date: 2019-03-29 09:52
  13.  * @Version 1.0
  14.  */
  15. public class TraBilDetail extends BaseRowModel {
  18.     //开票申请号
  19.     @ExcelProperty(value = {"开票申请号"}, index = 0)
  20.     private String applyNum;
  22.     //批次号
  23.     @ExcelProperty(value = {"批次号"}, index = 1)
  24.     private String batchNum;
  26.     //制单部门
  27.     @ExcelProperty(value = {"制单部门"}, index = 2)
  28.     private String name;
  30.     //客户名称
  31.     @ExcelProperty(value = {"客户名称"}, index = 3)
  32.     private String custName;
  34.     //业务类型
  35.     @ExcelProperty(value = {"业务类型"}, index = 4)
  36.     private String busiType;
  38.     //批次开票金额
  39.     @ExcelProperty(value = {"批次开票金额"}, index = 5)
  40.     private BigDecimal sumPrice;
  42.     //批次开票重量
  43.     @ExcelProperty(value = {"批次开票重量"}, index = 6)
  44.     private BigDecimal invoWeight;
  46.     //批次财务入总金额
  47.     @ExcelProperty(value = {"批次财务入总金额"}, index = 7)
  48.     private BigDecimal findInAmoundSum;
  50.     //邮寄信息
  51.     @ExcelProperty(value = {"邮寄信息"}, index = 8)
  52.     private String mailAddress;
  54.     private String contactName;
  56.     private String telephone;
  60.     /*=========================== 查询字段================================*/
  62.     private Long comId;
  64.     private Date applyDateStart;
  66.     private Date applyDateEnd;
  67.    /*=====================================*/
  68.        get和set方法省略
  69.    /*=====================================*/
  70.    }

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

  1.    package ect.report.dto;
  3. import com.alibaba.excel.annotation.ExcelProperty;
  4. import com.alibaba.excel.metadata.BaseRowModel;
  5. import java.math.BigDecimal;
  7. /**
  8.  * @ClassName:  BatchSupply
  9.  * @Description: 在库货源表
  10.  * @Author: bo.quan@hand-china.com (全博)
  11.  * @Date:   2019-04-02 09:00
  12.  * @Version 1.0
  13.  */
  14. public class BatchSupply extends BaseRowModel {
  16.     //公司名称
  17.     @ExcelProperty(value = {"在库货源","姓名"} ,index = 0)
  18.     private String comName;
  20.     //仓库名称
  21.     @ExcelProperty(value = {"在库货源","仓库名称"} ,index = 1)
  22.     private String subinvName;
  24.     //产地
  25.     @ExcelProperty(value = {"在库货源","产地"} ,index = 2)
  26.     private String terrName;
  28.     //等级
  29.     @ExcelProperty(value = {"在库货源","等级"} ,index = 3)
  30.     private String ctLevel;
  32.     //长度
  33.     @ExcelProperty(value = {"在库货源","长度"} ,index = 4)
  34.     private String length;
  37.     //长度均值
  38.     @ExcelProperty(value = {"在库货源","长度均值"} ,index = 5)
  39.     private String lengthMean;
  41.     //马值
  42.     @ExcelProperty(value = {"在库货源","马值"} ,index = 6)
  43.     private String micron;
  45.     //马值均值
  46.     @ExcelProperty(value = {"在库货源","马值均值"} ,index = 7)
  47.     private String micronMean;
  49.     //强力
  50.     @ExcelProperty(value = {"在库货源","强力"} ,index = 8)
  51.     private String strong;
  53.     //强力均值
  54.     @ExcelProperty(value = {"在库货源","强力均值"} ,index = 9)
  55.     private String strongMean;
  57.     //提单号
  58.     @ExcelProperty(value = {"在库货源","提单号"} ,index = 10)
  59.     private String blNum;
  61.     //入库单号
  62.     @ExcelProperty(value = {"在库货源","入库单号"} ,index = 11)
  63.     private String entryNum;
  66.     //箱号
  67.     @ExcelProperty(value = {"在库货源","箱号"} ,index = 12)
  68.     private String batchNum;
  70.     //入库件数
  71.     @ExcelProperty(value = {"在库货源","入库件数"} ,index = 13)
  72.     private BigDecimal batchQty;
  74.     //销售合同号
  75.     @ExcelProperty(value = {"在库货源","销售合同号"} ,index = 14)
  76.     private String conNum;
  78.     //出库单号
  79.     @ExcelProperty(value = {"在库货源","出库单号"} ,index = 15)
  80.     private String outNum;
  82.     //出库件数
  83.     @ExcelProperty(value = {"在库货源","出库件数"} ,index = 16)
  84.     private BigDecimal outBatchQty;
  86.     //在库箱数
  87.     @ExcelProperty(value = {"在库货源","在库箱数"} ,index = 17)
  88.     private String outStatus;
  90.     //在库件数
  91.     @ExcelProperty(value = {"在库货源","在库件数"} ,index = 18)
  92.     private BigDecimal wareQty;
  94.     //采购合同单价
  95.     @ExcelProperty(value = {"在库货源","采购合同单价"} ,index = 19)
  96.     private BigDecimal conPrice;
  98.     //财务入库成本
  99.     @ExcelProperty(value = {"在库货源","财务入库成本"} ,index = 20)
  100.     private BigDecimal finaPrice;
  102.     //初始财务入库成本(USD)
  103.     @ExcelProperty(value = {"在库货源","财务入库成本"} ,index = 21)
  104.     private BigDecimal firstUsdPrice;
  106.     //初始财务入库成本(CNY)
  107.     @ExcelProperty(value = {"在库货源","初始财务入库成本"} ,index = 22)
  108.     private BigDecimal firstRmbPrice;
  110.     private String lengthMax;
  112.     private String lengthMin;
  114.     private String micronMax;
  116.     private String micronMin;
  118.     private String strongMax;
  120.     private String strongMin;
  122.     private String terrCode;
  124.     private String comCode;
  126.     private String subinvId;
  128.     private String isBatchFlag;
  131.     private Long specId;
  133.     /*=====================================*/
  134.        get和set方法省略
  135.    /*=====================================*/
  136. }

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

  1. package ect.report.service.impl;
  3. import com.alibaba.excel.ExcelWriter;
  4. import com.alibaba.excel.metadata.Sheet;
  5. import com.alibaba.excel.support.ExcelTypeEnum;
  6. import ect.inv.util.StringUtils;
  7. import ect.report.dto.TraBilDetail;
  8. import ect.report.mapper.TraBilDetailMapper;
  9. import ect.report.utils.EasyEacelUtils;
  10. import jodd.util.StringUtil;
  11. import org.springframework.beans.factory.annotation.Autowired;
  12. import org.springframework.stereotype.Service;
  13. import ect.report.service.ITraBilDetailService;
  14. import org.springframework.transaction.annotation.Transactional;
  15. import javax.servlet.ServletOutputStream;
  16. import javax.servlet.http.HttpServletRequest;
  17. import javax.servlet.http.HttpServletResponse;
  18. import java.io.IOException;
  19. import java.util.List;
  21. /**
  22.  * @ClassName:  TraBilDetailServiceImpl
  23.  * @Description: 内贸开票明细实现层
  24.  * @Author: bo.quan@hand-china.com (全博)
  25.  * @Date:   2019-03-29 09:53
  26.  * @Version 1.0
  27.  */
  28. @Service
  29. @Transactional(rollbackFor = Exception.class)
  30. public class TraBilDetailServiceImpl implements ITraBilDetailService{
  33.     @Autowired
  34.     private TraBilDetailMapper traBilDetailMapper;
  36.     @Override
  37.     public void export(HttpServletRequest request, TraBilDetail traBilDetail, HttpServletResponse httpServletResponse) throws IOException {
  38.         String fileName="内贸开票明细表";
  39.         //组装输出流
  40.         ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);
  41.         try {
  42.             ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
  43.             Sheet sheet1 = new Sheet(1, 0, TraBilDetail.class);
  44.             //自适应宽度
  45.             sheet1.setAutoWidth(Boolean.TRUE);
  46.             //添加数据
  47.             writer.write(traBilDetailMapper.selectTraBilDetail(traBilDetail), sheet1);
  48.             writer.finish();
  49.         } catch (Exception e) {
  50.             e.printStackTrace();
  51.         } finally {
  52.             try {
  53.                 outputStream.close();
  54.             } catch (IOException e) {
  55.                 e.printStackTrace();
  56.             }
  57.         }
  58.     }
  59. }

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

  1. package ect.report.service.impl;
  3. import com.alibaba.excel.ExcelWriter;
  4. import com.alibaba.excel.metadata.Sheet;
  5. import com.alibaba.excel.support.ExcelTypeEnum;
  6. import ect.co.mapper.ConContractMapper;
  7. import ect.fs.mapper.FsPayAppExpMapper;
  8. import ect.fs.mapper.FsPayAppGooMapper;
  9. import ect.inv.mapper.WarehousingMapper;
  10. import ect.report.dto.ConSheet;
  11. import ect.report.mapper.ConSheetMapper;
  12. import ect.report.service.IConSheetService;
  13. import ect.report.utils.EasyEacelUtils;
  14. import org.springframework.beans.factory.annotation.Autowired;
  15. import org.springframework.stereotype.Service;
  16. import org.springframework.transaction.annotation.Transactional;
  18. import javax.servlet.ServletOutputStream;
  19. import javax.servlet.http.HttpServletRequest;
  20. import javax.servlet.http.HttpServletResponse;
  21. import java.io.IOException;
  22. import java.math.BigDecimal;
  23. import java.util.HashSet;
  24. import java.util.LinkedList;
  25. import java.util.List;
  27. @Service
  28. @Transactional(rollbackFor = Exception.class)
  29. public class ConSheetServiceImpl implements IConSheetService{
  30.     @Autowired
  31.     private ConSheetMapper sheetMapper;
  33.     @Autowired
  34.     private FsPayAppGooMapper fsPayAppGooMapper;
  35.     @Autowired
  36.     private FsPayAppExpMapper fsPayAppExpMapper;
  37.     @Autowired
  38.     private ConContractMapper conContractMapper;
  39.     @Autowired
  40.     private WarehousingMapper warehousingMapper;
  42.     private static final String REMORK_FIX = "转仓单重量:";
  44.     @Override
  45.     public void export(HttpServletRequest request, ConSheet conSheet, HttpServletResponse httpServletResponse) throws IOException {
  46.         String fileName="联营业务表";
  47.         List<ConSheet> dataList = loadReportData(conSheet);
  48.         ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);
  49.         try {
  50.             ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
  51.             Sheet sheet1 = new Sheet(1, 0, ConSheet.class);
  52.             //Table table = new Table(2);
  54.             sheet1.setAutoWidth(Boolean.TRUE);
  55.             writer.write(dataList, sheet1);
  56.             writer.finish();
  57.         } catch (Exception e) {
  58.             e.printStackTrace();
  59.         } finally {
  60.             try {
  61.                 outputStream.close();
  62.             } catch (IOException e) {
  63.                 e.printStackTrace();
  64.             }
  65.         }
  66.     }
  67.     /**
  68.      * 将sql中查询出的数据添加合计逻辑
  69.      * @param conSheet
  70.      * @return
  71.      */
  72.     private List<ConSheet> loadReportData(ConSheet conSheet) {
  73.         //汇总信息
  74.         ConSheet conSheetSum = new ConSheet();
  76.         BigDecimal sumEntryWeight = BigDecimal.ZERO;
  77.         BigDecimal sumAppAmt = BigDecimal.ZERO;
  78.         BigDecimal sumOutBatchWeight = BigDecimal.ZERO;
  79.         BigDecimal sumNoOutBatchWeight = BigDecimal.ZERO;
  80.         BigDecimal sumtBatchWeight = BigDecimal.ZERO;
  81.         BigDecimal sumInvenWeight = BigDecimal.ZERO;
  82.         BigDecimal sumEntryAmt = BigDecimal.ZERO;
  83.         BigDecimal sumSumExp = BigDecimal.ZERO;
  84.         BigDecimal sumInterest = BigDecimal.ZERO;
  85.         BigDecimal sumRiskDegree = BigDecimal.ZERO;
  86.         BigDecimal sumCheckAmt = BigDecimal.ZERO;
  87.         BigDecimal sumInvoWeight = BigDecimal.ZERO;
  89.         BigDecimal sumToBatchWeight = BigDecimal.ZERO;
  90.         List<ConSheet> uniConExportList = sheetMapper.selectUniCon(conSheet);
  91.         for (ConSheet uniConExport : uniConExportList) {
  92.             Long conId = uniConExport.getConId();
  93.             // 查询 我司支付
  94.             BigDecimal amtSum = fsPayAppGooMapper.selectUniConAmtByConId(conId.floatValue());
  95.             uniConExport.setAppAmt(amtSum);
  96.             // 费用
  97.             BigDecimal sumExp = fsPayAppExpMapper.selectSumExpByConId(conId.floatValue());
  98.             uniConExport.setSumExp(sumExp);
  100.             // 未销数量
  101.             BigDecimal noOutBatchWeight = conContractMapper.selectQtyNotSoldByConId(conId.floatValue());
  102.             uniConExport.setNoOutBatchWeight(noOutBatchWeight);
  103.             // 求已销数量
  104.             BigDecimal outBatchWeight = conContractMapper.selectQtySoldByConId(conId.floatValue());
  105.             uniConExport.setOutBatchWeight(outBatchWeight);
  107.             // 风险度
  108.             BigDecimal invenWeight = uniConExport.getInvenWeight();
  109.             if (invenWeight != null && invenWeight.compareTo(BigDecimal.ZERO) != 0) {
  110.                 // 我司支付
  111.                 BigDecimal appAmt = uniConExport.getAppAmt();
  113.                 //回笼资金
  114.                 BigDecimal entryAmt = uniConExport.getEntryAmt();
  115.                 // 费用
  116.                 BigDecimal exp = uniConExport.getSumExp();
  117.                 // 利息
  118.                 BigDecimal interest = uniConExport.getInterest();
  119.                 // 排除空
  120.                 appAmt = appAmt == null ? BigDecimal.ZERO : appAmt;
  121.                 entryAmt = entryAmt == null ? BigDecimal.ZERO : entryAmt;
  122.                 exp = exp == null ? BigDecimal.ZERO : exp;
  123.                 interest = interest == null ? BigDecimal.ZERO : interest;
  124.                 BigDecimal sum = appAmt.subtract(entryAmt).add(exp).add(interest);
  125.                 uniConExport.setRiskDegree(sum.divide(invenWeight, 2, BigDecimal.ROUND_HALF_UP));
  127.             }
  129.             // 备注
  130.             BigDecimal toBatchWeight = warehousingMapper.selectToBatchWeightByConId(conId.floatValue());
  131.             toBatchWeight = toBatchWeight == null ? BigDecimal.ZERO : toBatchWeight;
  132.             uniConExport.setRemark(REMORK_FIX + toBatchWeight.toString());
  133.             sumToBatchWeight = sumToBatchWeight.add(toBatchWeight);
  134.             sumEntryWeight = sumEntryWeight.add(uniConExport.getEntryWeight());
  135.             sumAppAmt = sumAppAmt.add(uniConExport.getAppAmt()==null?BigDecimal.ZERO:uniConExport.getAppAmt());
  136.             sumOutBatchWeight = sumOutBatchWeight.add(uniConExport.getOutBatchWeight()==null?BigDecimal.ZERO:uniConExport.getOutBatchWeight());
  137.             sumNoOutBatchWeight = sumNoOutBatchWeight.add(uniConExport.getNoOutBatchWeight()==null?BigDecimal.ZERO:uniConExport.getNoOutBatchWeight());
  138.             sumtBatchWeight = sumtBatchWeight.add(uniConExport.getBatchWeight());
  139.             sumInvenWeight = sumInvenWeight.add(uniConExport.getInvenWeight());
  140.             sumEntryAmt = sumEntryAmt.add(uniConExport.getEntryAmt());
  141.             sumSumExp = sumSumExp.add(uniConExport.getSumExp() == null ? BigDecimal.ZERO:uniConExport.getSumExp());
  142.             sumInterest = sumInterest.add(uniConExport.getInterest()==null?BigDecimal.ZERO:uniConExport.getInterest());
  143.             sumRiskDegree = sumRiskDegree.add(uniConExport.getRiskDegree()==null?BigDecimal.ZERO:uniConExport.getRiskDegree());
  144.             sumCheckAmt = sumCheckAmt.add(uniConExport.getCheckAmt()==null?BigDecimal.ZERO:uniConExport.getCheckAmt());
  145.             sumInvoWeight = sumInvoWeight.add(uniConExport.getInvoWeight());
  146.         }
  147.         List<ConSheet> sheetList = groupByParty(uniConExportList);
  148.         //汇总信息
  150.         //conSheetSum.setEntryWeight(sheetList.stream().map(ConSheet::getEntryWeight).reduce(BigDecimal.ZERO, BigDecimal::add));
  151.         conSheetSum.setEntryWeight(sumEntryWeight);
  152.         conSheetSum.setAppAmt(sumAppAmt);
  153.         conSheetSum.setOutBatchWeight(sumOutBatchWeight);
  154.         conSheetSum.setNoOutBatchWeight(sumNoOutBatchWeight);
  155.         conSheetSum.setBatchWeight(sumtBatchWeight);
  157.         conSheetSum.setInvenWeight(sumInvenWeight);
  158.         conSheetSum.setEntryAmt(sumEntryAmt);
  159.         conSheetSum.setSumExp(sumSumExp);
  160.         conSheetSum.setInterest(sumInterest);
  162.         conSheetSum.setRiskDegree(sumRiskDegree);
  163.         conSheetSum.setCheckAmt(sumCheckAmt);
  164.         conSheetSum.setInvoWeight(sumInvoWeight);
  165.         conSheetSum.setRemark(REMORK_FIX +sumToBatchWeight);
  166.         conSheetSum.setPartyName("合计: ");
  167.         List<ConSheet> conSheets = groupByParty(uniConExportList);
  168.         conSheets.add(conSheetSum);
  169.         return conSheets;
  170.     }
  172.     /**
  173.      * 根据部门进行分组
  174.      * @param uniConExportList
  175.      * @return
  176.      */
  177.     private List<ConSheet> groupByParty(List<ConSheet> uniConExportList) {
  178.         LinkedList<ConSheet> finalList = new LinkedList<>();
  179.         HashSet<String> partySet = new HashSet<>();
  180. //        LinkedHashMap<String,Integer> partyMap = new LinkedHashMap<>();
  181.         for(ConSheet uniConExport : uniConExportList){
  182.             if (partySet.add(uniConExport.getPartyName())) {
  183.                 finalList.addLast(uniConExport);
  184.             }else {
  185.                 int i =0;
  186.                 boolean findFlag = false;
  187.                 // 插入
  188.                 for(ConSheet export : finalList ){
  189.                     if(export.getPartyName().equals(uniConExport.getPartyName())){
  190.                         findFlag = true;
  191.                     }else if(findFlag){
  192.                         break;
  193.                     }
  194.                     i++;
  195.                 }
  196.                 finalList.add(i,uniConExport);
  197.             }
  198.         }
  199.         return finalList;
  200.     }
  201. }

官方示例dome

  1. OutputStream out = new FileOutputStream("/Users/jipengfei/2007.xlsx");
  2. ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);
  4. //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
  5. Sheet sheet1 = new Sheet(1, 3);
  6. sheet1.setSheetName("第一个sheet");
  7. //设置列宽 设置每列的宽度
  8. Map columnWidth = new HashMap();
  9. columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);
  10. sheet1.setColumnWidthMap(columnWidth);
  11. sheet1.setHead(createTestListStringHead());
  12. //or 设置自适应宽度
  13. //sheet1.setAutoWidth(Boolean.TRUE);
  14. writer.write1(createTestListObject(), sheet1);
  16. //写第二个sheet sheet2  模型上打有表头的注解，合并单元格
  17. Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二个sheet", null);
  18. sheet2.setTableStyle(createTableStyle());
  19. writer.write(createTestListJavaMode(), sheet2);
  21. //写第三个sheet包含多个table情况
  22. Sheet sheet3 = new Sheet(3, 0);
  23. sheet3.setSheetName("第三个sheet");
  24. Table table1 = new Table(1);
  25. table1.setHead(createTestListStringHead());
  26. writer.write1(createTestListObject(), sheet3, table1);
  28. //写sheet2  模型上打有表头的注解
  29. Table table2 = new Table(2);
  30. table2.setTableStyle(createTableStyle());
  31. table2.setClazz(JavaModel1.class);
  32. writer.write(createTestListJavaMode(), sheet3, table2);
  34. //关闭资源
  35. writer.finish();
  36. out.close();

2.表头不带注解的方法

这种dto的写法跟上述的dto写法基本相同，就是去掉了注解。也可以不用dto，直接用List

  1. package ect.report.service.impl;
  3. import ch.qos.logback.classic.Logger;
  4. import com.alibaba.excel.ExcelWriter;
  5. import com.alibaba.excel.metadata.Sheet;
  6. import com.alibaba.excel.support.ExcelTypeEnum;
  7. import com.hand.hap.core.IRequest;
  8. import com.hand.hap.fnd.dto.Company;
  9. import com.hand.hap.fnd.service.ICompanyService;
  10. import ect.fs.dto.DailyRate;
  11. import ect.fs.dto.FsComAttr;
  12. import ect.fs.service.IDailyRateService;
  13. import ect.fs.service.IFsComAttrService;
  14. import ect.report.dto.InvomeCostDetail;
  15. import ect.report.mapper.InvomeCostDetailMapper;
  16. import ect.report.utils.EasyEacelUtils;
  17. import org.slf4j.LoggerFactory;
  18. import org.springframework.beans.factory.annotation.Autowired;
  19. import org.springframework.stereotype.Service;
  20. import ect.report.service.IInvomeCostDetailService;
  21. import org.springframework.transaction.annotation.Transactional;
  23. import javax.servlet.ServletOutputStream;
  24. import javax.servlet.http.HttpServletRequest;
  25. import javax.servlet.http.HttpServletResponse;
  26. import java.io.IOException;
  27. import java.math.BigDecimal;
  28. import java.text.SimpleDateFormat;
  29. import java.util.*;
  31. /**
  32.  * @ClassName: InvomeCostDetailServiceImpl
  33.  * @Description:收入成本表实现类
  34.  * @Author: bo.quan@hand-china.com (全博)
  35.  * @Date: 2019-03-26 16:14
  36.  * @Version 1.0
  37.  */
  38. @Service
  39. @Transactional(rollbackFor = Exception.class)
  40. public class InvomeCostDetailServiceImpl implements IInvomeCostDetailService {
  42.     private Logger logger = (Logger) LoggerFactory.getLogger(InvomeCostDetailServiceImpl.class);
  44.     @Autowired
  45.     private InvomeCostDetailMapper invomeCostDetailMapper;
  47.     @Autowired
  48.     private IDailyRateService dailyRateService;
  50.     @Autowired
  51.     private ICompanyService companyService;
  53.     @Autowired
  54.     private IFsComAttrService fsComAttrService;
  57.     //自营人民币销售合同
  58.     private final static String RMB_SO_CON_NUM = "SESRMBSAL";
  60.     //自营美元销售合同
  61.     private final static String USD_SO_CON_NUM = "SESUSBSAL";
  63.     public List<InvomeCostDetail> loadReportData(IRequest requestContext, InvomeCostDetail detail) {
  64.         List<Company> companyList = companyService.selectComIdByRole(requestContext.getRoleId());
  65.         String currencyCode = null;
  66.         if (!companyList.isEmpty()) {
  67.             Long comId = companyList.get(0).getCompanyId();
  68.             if (comId != null) {
  69.                 FsComAttr fsComAttr = fsComAttrService.selectByComId(comId.floatValue());
  70.                 currencyCode = fsComAttr.getCurrencyCode();
  71.             }
  72.         }
  73.         final String comCurrencyCode = currencyCode;
  74.         DailyRate dailyRate = new DailyRate();
  75.         dailyRate.setConversionDate(detail.getPostDateEnd());
  76.         dailyRate.setFromCurrency(DailyRate.USD);
  77.         dailyRate.setToCurrency(DailyRate.CNY);
  78.         dailyRate.setConversionType(DailyRate.AVERAGE);
  79.         List<DailyRate> dailyRates = dailyRateService.getRateByNew(dailyRate);
  80.         if (dailyRates.size()>0){
  81.             dailyRate=dailyRates.get(0);
  82.         }
  83.         final BigDecimal rate = dailyRate.getRate();
  85.         List<InvomeCostDetail> incomeCostDetails = invomeCostDetailMapper.selectIncomDetail(detail);
  86.         incomeCostDetails.stream().forEach(incomeCostDetail -> {
  87.             if (RMB_SO_CON_NUM.equals(incomeCostDetail.getDetType())) {
  88.                 //发票金额（不含税）
  89.                 incomeCostDetail.setInvoApplyAmount(incomeCostDetail.getInvoApplyAmountRmb());
  90.                 //incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountRmb());
  91.             }
  92.             if (USD_SO_CON_NUM.equals(incomeCostDetail.getDetType())) {
  93.                 //发票金额（不含税）
  94.                 incomeCostDetail.setInvoApplyAmount(incomeCostDetail.getInvoApplyAmountUsd());
  95.                 //  incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountUsd());
  96.             }
  98.             //出库金额（元）
  99.             if (incomeCostDetail.getInvoOutAmountCn() == null) {
  100.                 incomeCostDetail.setInvoOutAmountCn(incomeCostDetail.getInvoOutAmount());
  101.             }
  102.             //出库汇率
  103.             if (incomeCostDetail.getInvoOutAmountCn() != null && incomeCostDetail.getInvoOutAmount() != null && incomeCostDetail.getInvoOutAmount().compareTo(BigDecimal.ZERO) != 0) {
  104.                 incomeCostDetail.setRate(incomeCostDetail.getInvoOutAmountCn().divide(incomeCostDetail.getInvoOutAmount(), 4, BigDecimal.ROUND_HALF_UP));
  105.             }
  107.             BigDecimal finaRate = incomeCostDetail.getRate() == null ? BigDecimal.ONE : incomeCostDetail.getRate();
  108.             if ("进口".equals(incomeCostDetail.getBusiType())) {
  109.                 incomeCostDetail.setInvoApplyAmountYuan(incomeCostDetail.getInvoApplyAmount());
  110.             } else if ("转口".equals(incomeCostDetail.getBusiType())) {
  111.                 if (finaRate.compareTo(BigDecimal.ONE) == 0) {
  112.                     finaRate = rate;
  113.                 }
  114.                 incomeCostDetail.setInvoApplyAmountYuan(incomeCostDetail.getInvoApplyAmount().multiply(finaRate));
  115.             }
  117.             //财务出库单价（人民币/吨） =出库金额（元）/财务出库重量
  118.             if (incomeCostDetail.getInvoOutAmountCn() != null && incomeCostDetail.getInvoOutWeight() != null && incomeCostDetail.getInvoOutWeight().compareTo(BigDecimal.ZERO) != 0) {
  119.                 incomeCostDetail.setInvoOutPriceCn(incomeCostDetail.getInvoOutAmountCn().divide(incomeCostDetail.getInvoOutWeight(), 10, BigDecimal.ROUND_HALF_UP));
  120.             }
  121.             ///财务出库单价
  122.             if (incomeCostDetail.getInvoOutAmount() != null && incomeCostDetail.getInvoOutWeight() != null && incomeCostDetail.getInvoOutWeight().compareTo(BigDecimal.ZERO) != 0) {
  123.                 incomeCostDetail.setInvoOutPrice(incomeCostDetail.getInvoOutAmount().divide(incomeCostDetail.getInvoOutWeight(), 10, BigDecimal.ROUND_HALF_UP));
  124.             }
  125.             //对应初始采购成本金额
  126.             incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountUsd());
  127.             //对应初始采购成本金额（元）
  128.             incomeCostDetail.setPoCostAmountCn(incomeCostDetail.getPoCostAmountRmb());
  130.             //现货毛利（合并）原币 发票金额（不含税）-对应初始采购成本金额（原币）
  131.             if (incomeCostDetail.getInvoApplyAmount() != null && incomeCostDetail.getPoCostAmount() != null) {
  132.                 if (DailyRate.USD.equals(incomeCostDetail.getInCurrencyCode()) && DailyRate.CNY.equals(incomeCostDetail.getOutCurrencyCode())) {
  133.                     incomeCostDetail.setIncomeMeshAmount((incomeCostDetail.getInvoApplyAmount().divide(rate, 10, BigDecimal.ROUND_HALF_UP)).subtract(incomeCostDetail.getPoCostAmount()));
  134.                 } else {
  135.                     incomeCostDetail.setIncomeMeshAmount(incomeCostDetail.getInvoApplyAmount().subtract(incomeCostDetail.getPoCostAmount()));
  136.                 }
  137.             }
  138.             //现货毛利（合并）人民币  发票金额（不含税）-对应初始采购成本金额（元）
  139.             BigDecimal applyAmount = BigDecimal.ZERO;
  140.             if ("进口".equals(incomeCostDetail.getBusiType())) {
  141.                 applyAmount = incomeCostDetail.getInvoApplyAmount();
  142.             } else {
  143.                 applyAmount = incomeCostDetail.getSumRmb();
  144.             }
  145.             if (applyAmount != null && incomeCostDetail.getPoCostAmountCn() != null) {
  146.                 if (incomeCostDetail.getInvoApplyAmountYuan() != null && incomeCostDetail.getPoCostAmountCn() != null) {
  147.                     incomeCostDetail.setIncomeMeshAmountCn(incomeCostDetail.getInvoApplyAmountYuan().subtract(incomeCostDetail.getPoCostAmountCn()));
  148.                 }
  149.             }
  151.             if ("转口".equals(incomeCostDetail.getBusiType()) && ("中纺棉花(香港)有限公司".equals(incomeCostDetail.getComName()))) {
  152.                 logger.info("2111");
  153.             }
  155.             //现货毛利（合并）人民币-财务口径  发票金额（不含税）-对应初始采购成本金额（元）
  156.             if ("进口".equals(incomeCostDetail.getBusiType()) || ("转口".equals(incomeCostDetail.getBusiType()) && (!"1445".equals(incomeCostDetail.getComCode())))) {
  157.                 if (incomeCostDetail.getIncomeMeshAmountCn() != null) {
  158.                     incomeCostDetail.setIncomeMeshAmountCnFina(incomeCostDetail.getIncomeMeshAmountCn());
  159.                 }
  160.             } else if ("转口".equals(incomeCostDetail.getBusiType()) && ("1445".equals(incomeCostDetail.getComCode()))) {
  161.                 if (incomeCostDetail.getIncomeMeshAmountCn() != null && rate!=null) {
  162.                     incomeCostDetail.setIncomeMeshAmountCnFina(incomeCostDetail.getIncomeMeshAmount().multiply(rate));
  163.                 }
  164.             }
  167.             //对应初始采购成本单价
  168.             if (incomeCostDetail.getPoCostAmount() != null && incomeCostDetail.getPoCostBatchWeight() != null) {
  169.                 incomeCostDetail.setPoCostPricce(incomeCostDetail.getPoCostAmount().divide(incomeCostDetail.getPoCostBatchWeight(), 10, BigDecimal.ROUND_HALF_UP));
  170.             }
  171.             //对应初始采购成本单价（元/吨）
  172.             if (incomeCostDetail.getPoCostAmountCn() != null && incomeCostDetail.getPoCostBatchWeight() != null) {
  173.                 incomeCostDetail.setPoCostPricceCn(incomeCostDetail.getPoCostAmountCn().divide(incomeCostDetail.getPoCostBatchWeight(), 10, BigDecimal.ROUND_HALF_UP));
  174.             }
  175.             //对应初始财务入库汇率
  176.             if (incomeCostDetail.getPoCostAmountCn() != null && incomeCostDetail.getPoCostAmount() != null && BigDecimal.ZERO.compareTo(incomeCostDetail.getPoCostAmount()) != 0) {
  177.                 //对应初始采购成本金额/对应初始采购成本金额
  178.                 incomeCostDetail.setPoRate(incomeCostDetail.getPoCostAmountCn().divide(incomeCostDetail.getPoCostAmount(), 10, BigDecimal.ROUND_HALF_UP));
  179.             }
  181.             //现货毛利（独立）原币 发票金额（不含税）-财务出库金额（原币）
  182.             if (incomeCostDetail.getInvoApplyAmount() != null && incomeCostDetail.getInvoOutAmount() != null) {
  183.                 if (DailyRate.USD.equals(incomeCostDetail.getInCurrencyCode()) && DailyRate.CNY.equals(incomeCostDetail.getOutCurrencyCode())) {
  184.                     incomeCostDetail.setIncomeSelfAmount(incomeCostDetail.getInvoApplyAmount().divide(rate, 10, BigDecimal.ROUND_HALF_UP).subtract(incomeCostDetail.getInvoOutAmount()));
  185.                 } else {
  186.                     incomeCostDetail.setIncomeSelfAmount(incomeCostDetail.getInvoApplyAmount().subtract(incomeCostDetail.getInvoOutAmount()));
  187.                 }
  188.             }
  190.             //现货毛利（独立）人民币 发票金额（不含税）-财务出库金额（元）
  191.             if (applyAmount != null && incomeCostDetail.getInvoOutAmountCn() != null) {
  192.                 if (DailyRate.USD.equals(comCurrencyCode)) {
  193.                     incomeCostDetail.setIncomeSelfAmountCn(applyAmount.multiply(rate).subtract(incomeCostDetail.getInvoOutAmountCn()));
  194.                 } else {
  195.                     incomeCostDetail.setIncomeSelfAmountCn(applyAmount.subtract(incomeCostDetail.getInvoOutAmountCn()));
  196.                 }
  197.             }
  199.             //现货毛利（独立）人民币-财务口径  发票金额（不含税）-财务出库金额（元）
  200.             if ("进口".equals(incomeCostDetail.getBusiType()) || ("转口".equals(incomeCostDetail.getBusiType()) && (!"1445".equals(incomeCostDetail.getComCode())))) {
  201.                 if (incomeCostDetail.getIncomeSelfAmountCn() != null) {
  202.                     incomeCostDetail.setIncomeSelfAmountCnFina(incomeCostDetail.getIncomeSelfAmountCn());
  203.                 }
  204.             } else if ("转口".equals(incomeCostDetail.getBusiType()) && ("1445".equals(incomeCostDetail.getComCode()))) {
  205.                 if (incomeCostDetail.getIncomeSelfAmount() != null && rate!=null) {
  206.                     incomeCostDetail.setIncomeSelfAmountCnFina(incomeCostDetail.getIncomeSelfAmountCn().multiply(rate));
  207.                 }
  208.             }
  211.             if ("转口".equals(incomeCostDetail.getBusiType()) && incomeCostDetail.getRate() != null && incomeCostDetail.getRate().compareTo(BigDecimal.ONE) == 0) {
  212.                 incomeCostDetail.setInvoOutAmountCn(incomeCostDetail.getInvoOutAmountCn().multiply(incomeCostDetail.getRate()));
  213.             }
  216.         });
  218.         return incomeCostDetails;
  219.     }
  221.     @Override
  222.     public void export(IRequest requestContext, HttpServletRequest request, InvomeCostDetail costDetail, HttpServletResponse httpServletResponse) throws IOException {
  223.         String fileName = "收入成本毛利表";
  224.         ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);
  225.         try {
  226.             ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
  227.             Sheet sheet1 = new Sheet(1, 0, InvomeCostDetail.class);
  228.             Map columnWidth = new HashMap();
  229.             columnWidth.put(0, 5000);
  230.             columnWidth.put(1, 5000);
  231.             columnWidth.put(2, 5000);
  232.             columnWidth.put(3, 5000);
  233.             columnWidth.put(4, 5000);
  234.             columnWidth.put(5, 5000);
  235.             columnWidth.put(6, 5000);
  236.             columnWidth.put(7, 7000);
  237.             columnWidth.put(8, 7000);
  238.             columnWidth.put(9, 7000);
  239.             columnWidth.put(10, 7000);
  240.             columnWidth.put(11, 9000);
  241.             columnWidth.put(12, 7000);
  242.             columnWidth.put(13, 7000);
  243.             columnWidth.put(14, 9000);
  244.             columnWidth.put(15, 9000);
  245.             columnWidth.put(16, 10000);
  246.             columnWidth.put(17, 9000);
  247.             columnWidth.put(18, 7000);
  248.             columnWidth.put(19, 7000);
  249.             columnWidth.put(20, 7000);
  250.             columnWidth.put(21, 7000);
  251.             columnWidth.put(22, 7000);
  252.             sheet1.setColumnWidthMap(columnWidth);
  253.             sheet1.setHead(createListHeading(costDetail.getPostDateStart() == null ? null : new SimpleDateFormat("yyyy-MM-dd").format(costDetail.getPostDateStart()),
  254.                     costDetail.getPostDateEnd() == null ? null : new SimpleDateFormat("yyyy-MM-dd").format(costDetail.getPostDateEnd())));
  255.             writer.write(loadReportData(requestContext, costDetail), sheet1);
  256.             writer.finish();
  257.         } catch (Exception e) {
  258.             e.printStackTrace();
  259.         } finally {
  260.             try {
  261.                 outputStream.close();
  262.             } catch (IOException e) {
  263.                 e.printStackTrace();
  264.             }
  265.         }
  266.     }
  268.     private List<List<String>> createListHeading(String startDate, String endDate) {
  269.         List<List<String>> head = new ArrayList<List<String>>();
  270.         List<String> headCoulumn1 = new ArrayList<String>();
  271.         List<String> headCoulumn2 = new ArrayList<String>();
  272.         List<String> headCoulumn3 = new ArrayList<String>();
  273.         List<String> headCoulumn4 = new ArrayList<String>();
  274.         List<String> headCoulumn5 = new ArrayList<String>();
  275.         List<String> headCoulumn6 = new ArrayList<String>();
  276.         List<String> headCoulumn7 = new ArrayList<String>();
  277.         List<String> headCoulumn8 = new ArrayList<String>();
  278.         List<String> headCoulumn9 = new ArrayList<String>();
  279.         List<String> headCoulumn10 = new ArrayList<String>();
  280.         List<String> headCoulumn11 = new ArrayList<String>();
  281.         List<String> headCoulumn12 = new ArrayList<String>();
  282.         List<String> headCoulumn13 = new ArrayList<String>();
  283.         List<String> headCoulumn14 = new ArrayList<String>();
  284.         List<String> headCoulumn15 = new ArrayList<String>();
  285.         List<String> headCoulumn16 = new ArrayList<String>();
  286.         List<String> headCoulumn17 = new ArrayList<String>();
  287.         List<String> headCoulumn18 = new ArrayList<String>();
  288.         List<String> headCoulumn19 = new ArrayList<String>();
  289.         List<String> headCoulumn20 = new ArrayList<String>();
  290.         List<String> headCoulumn21 = new ArrayList<String>();
  291.         List<String> headCoulumn22 = new ArrayList<String>();
  292.         List<String> headCoulumn23 = new ArrayList<String>();
  293.         List<String> headCoulumn24 = new ArrayList<String>();
  294.         List<String> headCoulumn25 = new ArrayList<String>();
  295.         List<String> headCoulumn26 = new ArrayList<String>();
  298.         headCoulumn1.add("收入成本毛利表");
  299.         headCoulumn1.add("开票起始日");
  300.         headCoulumn1.add("公司名称");
  301.         headCoulumn2.add("收入成本毛利表");
  302.         headCoulumn2.add(startDate);
  303.         headCoulumn2.add("销售客户");
  304.         headCoulumn3.add("收入成本毛利表");
  305.         headCoulumn3.add("");
  306.         headCoulumn3.add("合同号");
  307.         headCoulumn4.add("收入成本毛利表");
  308.         headCoulumn4.add("");
  309.         headCoulumn4.add("开票申请号");
  310.         headCoulumn5.add("收入成本毛利表");
  311.         headCoulumn5.add("");
  312.         headCoulumn5.add("过账日期");
  313.         headCoulumn6.add("收入成本毛利表");
  314.         headCoulumn6.add("");
  315.         headCoulumn6.add("业务类型");
  316.         headCoulumn7.add("收入成本毛利表");
  317.         headCoulumn7.add("开票截止日");
  318.         headCoulumn7.add("发票数量");
  319.         headCoulumn8.add("收入成本毛利表");
  320.         headCoulumn8.add(endDate);
  321.         headCoulumn8.add("发票金额(不含税)");
  322.         headCoulumn9.add("收入成本毛利表");
  323.         headCoulumn9.add(endDate);
  324.         headCoulumn9.add("发票金额(不含税-人民币)");
  325.         headCoulumn10.add("收入成本毛利表");
  326.         headCoulumn10.add("");
  327.         headCoulumn10.add("财务出库数量");
  328.         headCoulumn11.add("收入成本毛利表");
  329.         headCoulumn11.add("");
  330.         headCoulumn11.add("财务出库单价(原币)");
  331.         headCoulumn12.add("收入成本毛利表");
  332.         headCoulumn12.add("");
  333.         headCoulumn12.add("财务出库金额(原币)");
  334.         headCoulumn13.add("收入成本毛利表");
  335.         headCoulumn13.add("");
  336.         headCoulumn13.add("财务出库单价(人民币/吨)");
  337.         headCoulumn14.add("收入成本毛利表");
  338.         headCoulumn14.add("");
  339.         headCoulumn14.add("财务出库金额(元)");
  340.         headCoulumn15.add("收入成本毛利表");
  341.         headCoulumn15.add("");
  342.         headCoulumn15.add("财务出库汇率");
  343.         headCoulumn16.add("收入成本毛利表");
  344.         headCoulumn16.add("");
  345.         headCoulumn16.add("对应初始采购成本单价(原币)");
  346.         headCoulumn17.add("收入成本毛利表");
  347.         headCoulumn17.add("");
  348.         headCoulumn17.add("对应初始采购成本金额(原币)");
  349.         headCoulumn18.add("收入成本毛利表");
  350.         headCoulumn18.add("");
  351.         headCoulumn18.add("对应初始采购成本金额(元)");
  352.         headCoulumn19.add("收入成本毛利表");
  353.         headCoulumn19.add("");
  354.         headCoulumn19.add("对应初始采购成本单价（元/吨）");
  355.         headCoulumn20.add("收入成本毛利表");
  356.         headCoulumn20.add("");
  357.         headCoulumn20.add("对应初始财务入库汇率");
  358.         headCoulumn21.add("收入成本毛利表");
  359.         headCoulumn21.add("");
  360.         headCoulumn21.add("现货毛利(合并)原币");
  361.         headCoulumn22.add("收入成本毛利表");
  362.         headCoulumn22.add("");
  363.         headCoulumn22.add("现货毛利(合并)人民币");
  364.         headCoulumn23.add("收入成本毛利表");
  365.         headCoulumn23.add("");
  366.         headCoulumn23.add("现货毛利(合并)人民币-财务口径");
  367.         headCoulumn24.add("收入成本毛利表");
  368.         headCoulumn24.add("");
  369.         headCoulumn24.add("现货毛利(独立)原币");
  370.         headCoulumn25.add("收入成本毛利表");
  371.         headCoulumn25.add("");
  372.         headCoulumn25.add("现货毛利(独立)人民币");
  373.         headCoulumn26.add("收入成本毛利表");
  374.         headCoulumn26.add("");
  375.         headCoulumn26.add("现货毛利(独立)人民币-财务口径");
  376.         head.add(headCoulumn1);
  377.         head.add(headCoulumn2);
  378.         head.add(headCoulumn3);
  379.         head.add(headCoulumn4);
  380.         head.add(headCoulumn5);
  381.         head.add(headCoulumn6);
  382.         head.add(headCoulumn7);
  383.         head.add(headCoulumn8);
  384.         head.add(headCoulumn9);
  385.         head.add(headCoulumn10);
  386.         head.add(headCoulumn11);
  387.         head.add(headCoulumn12);
  388.         head.add(headCoulumn13);
  389.         head.add(headCoulumn14);
  390.         head.add(headCoulumn15);
  391.         head.add(headCoulumn16);
  392.         head.add(headCoulumn17);
  393.         head.add(headCoulumn18);
  394.         head.add(headCoulumn19);
  395.         head.add(headCoulumn20);
  396.         head.add(headCoulumn21);
  397.         head.add(headCoulumn22);
  398.         head.add(headCoulumn23);
  399.         head.add(headCoulumn24);
  400.         head.add(headCoulumn25);
  401.         head.add(headCoulumn26);
  402.         return head;
  403.     }
  404. }

官方示例demo

  1. ExcelWriter writer = EasyExcelFactory.getWriter(out);
  3. //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
  4. Sheet sheet1 = new Sheet(1, 3);
  5. sheet1.setSheetName("第一个sheet");
  6. //设置列宽 设置每列的宽度
  7. Map columnWidth = new HashMap();
  8. columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);
  9. sheet1.setColumnWidthMap(columnWidth);
  10. sheet1.setHead(createTestListStringHead());
  11. //or 设置自适应宽度
  12. //sheet1.setAutoWidth(Boolean.TRUE);
  13. writer.write1(createTestListObject(), sheet1);
  15. //写第二个sheet sheet2  模型上打有表头的注解，合并单元格
  16. Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二个sheet", null);
  17. sheet2.setTableStyle(createTableStyle());
  18. writer.write(createTestListJavaMode(), sheet2);
  20. //写第三个sheet包含多个table情况
  21. Sheet sheet3 = new Sheet(3, 0);
  22. sheet3.setSheetName("第三个sheet");
  23. Table table1 = new Table(1);
  24. table1.setHead(createTestListStringHead());
  25. writer.write1(createTestListObject(), sheet3, table1);
  27. //写sheet2  模型上打有表头的注解
  28. Table table2 = new Table(2);
  29. table2.setTableStyle(createTableStyle());
  30. table2.setClazz(JavaModel1.class);
  31. writer.write(createTestListJavaMode(), sheet3, table2);
  33. //关闭资源
  34. writer.finish();
  35. out.close();

总结

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