Easyexcel使用文档及动态列的实现
2019-05-03 本文已影响0人
LCF_全博
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<Dto>的方法。示例如下
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<Object>来代替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来实现两部分数据块的展示方法等等，这些都可以从官方文档中找个具体的使用方法。这里就不做详细阐述了，本文主要就是讲解数据的组装逻辑方法。
