建设银行个人网站登陆,网站自己做,同城招聘工作信息附近,视频号最新动作Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存#xff0c;poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题#xff0c;但POI还是有一些缺陷#xff0c;比如07版Excel解压缩以及解压后存储都是在内存中完成的…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添加依赖com.alibabaeasyexcel1.1.2-beta4使用方法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*/ServiceTransactional(rollbackFor Exception.class)public class TraBilDetailServiceImpl implements ITraBilDetailService{Autowiredprivate TraBilDetailMapper traBilDetailMapper;Overridepublic 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;ServiceTransactional(rollbackFor Exception.class)public class ConSheetServiceImpl implements IConSheetService{Autowiredprivate ConSheetMapper sheetMapper;Autowiredprivate FsPayAppGooMapper fsPayAppGooMapper;Autowiredprivate FsPayAppExpMapper fsPayAppExpMapper;Autowiredprivate ConContractMapper conContractMapper;Autowiredprivate WarehousingMapper warehousingMapper;private static final String REMORK_FIX 转仓单重量:;Overridepublic void export(HttpServletRequest request, ConSheet conSheet, HttpServletResponse httpServletResponse) throws IOException {String fileName联营业务表;List 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 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 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 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 conSheets groupByParty(uniConExportList);conSheets.add(conSheetSum);return conSheets;}/*** 根据部门进行分组* param uniConExportList* return*/private List groupByParty(List uniConExportList) {LinkedList finalList new LinkedList();HashSet partySet new HashSet();// LinkedHashMap 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;}}官方示例domeOutputStream out new FileOutputStream(/Users/jipengfei/2007.xlsx);ExcelWriter writer EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);//写第一个sheet, sheet1 数据全是List 无模型映射关系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*/ServiceTransactional(rollbackFor Exception.class)public class InvomeCostDetailServiceImpl implements IInvomeCostDetailService {private Logger logger (Logger) LoggerFactory.getLogger(InvomeCostDetailServiceImpl.class);Autowiredprivate InvomeCostDetailMapper invomeCostDetailMapper;Autowiredprivate IDailyRateService dailyRateService;Autowiredprivate ICompanyService companyService;Autowiredprivate IFsComAttrService fsComAttrService;//自营人民币销售合同private final static String RMB_SO_CON_NUM SESRMBSAL;//自营美元销售合同private final static String USD_SO_CON_NUM SESUSBSAL;public List loadReportData(IRequest requestContext, InvomeCostDetail detail) {List 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 dailyRates dailyRateService.getRateByNew(dailyRate);if (dailyRates.size()0){dailyRatedailyRates.get(0);}final BigDecimal rate dailyRate.getRate();List 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;}Overridepublic 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 createListHeading(String startDate, String endDate) {List head new ArrayList();List headCoulumn1 new ArrayList();List headCoulumn2 new ArrayList();List headCoulumn3 new ArrayList();List headCoulumn4 new ArrayList();List headCoulumn5 new ArrayList();List headCoulumn6 new ArrayList();List headCoulumn7 new ArrayList();List headCoulumn8 new ArrayList();List headCoulumn9 new ArrayList();List headCoulumn10 new ArrayList();List headCoulumn11 new ArrayList();List headCoulumn12 new ArrayList();List headCoulumn13 new ArrayList();List headCoulumn14 new ArrayList();List headCoulumn15 new ArrayList();List headCoulumn16 new ArrayList();List headCoulumn17 new ArrayList();List headCoulumn18 new ArrayList();List headCoulumn19 new ArrayList();List headCoulumn20 new ArrayList();List headCoulumn21 new ArrayList();List headCoulumn22 new ArrayList();List headCoulumn23 new ArrayList();List headCoulumn24 new ArrayList();List headCoulumn25 new ArrayList();List headCoulumn26 new ArrayList();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;}}官方示例demoExcelWriter writer EasyExcelFactory.getWriter(out);//写第一个sheet, sheet1 数据全是List 无模型映射关系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来实现两部分数据块的展示方法等等这些都可以从官方文档中找个具体的使用方法。这里就不做详细阐述了本文主要就是讲解数据的组装逻辑方法。
