當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

easyexcel 列头合并_Easyexcel使用文档及动态列的实现

發(fā)布時(shí)間：2024/8/23 编程问答 43 豆豆

生活随笔收集整理的這篇文章主要介紹了 easyexcel 列头合并_Easyexcel使用文档及动态列的实现小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個(gè)嚴(yán)重的問題就是非常的耗內(nèi)存，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲(chǔ)都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。easyexcel重寫了poi對07版Excel的解析，能夠原本一個(gè)3M的excel用POI sax依然需要100M左右內(nèi)存降低到KB級(jí)別，并且再大的excel不會(huì)出現(xiàn)內(nèi)存溢出，03版依賴POI的sax模式。在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡單方便。

我建議大家使用1.1.2-beta4版本，也就是官方給的最新版，maven倉庫查到的最新版比這個(gè)版本略低，最顯著的區(qū)別就是excel中數(shù)字的輸出，最新版本到處excel會(huì)將數(shù)字專為數(shù)字格式，而maven庫給出的最新版則會(huì)輸出成文本格式。其他變化需要各位自己去發(fā)現(xiàn)。

官方文檔地址

https://github.com/alibaba/easyexcel

添加依賴

com.alibaba

easyexcel

1.1.2-beta4

使用方法

1.表頭帶注解方法

這種方法首先需要一個(gè)繼承BaseRowModel的模型，在Hap框架dto的角色就相當(dāng)于這個(gè)模型，示例如下

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 {

//開票申請?zhí)?/p>

@ExcelProperty(value = {"開票申請?zhí)?#34;}, index = 0)

private String applyNum;

//批次號(hào)

@ExcelProperty(value = {"批次號(hào)"}, index = 1)

private String batchNum;

//制單部門

@ExcelProperty(value = {"制單部門"}, index = 2)

private String name;

//客戶名稱

@ExcelProperty(value = {"客戶名稱"}, index = 3)

private String custName;

//業(yè)務(wù)類型

@ExcelProperty(value = {"業(yè)務(wù)類型"}, index = 4)

private String busiType;

//批次開票金額

@ExcelProperty(value = {"批次開票金額"}, index = 5)

private BigDecimal sumPrice;

//批次開票重量

@ExcelProperty(value = {"批次開票重量"}, index = 6)

private BigDecimal invoWeight;

//批次財(cái)務(wù)入總金額

@ExcelProperty(value = {"批次財(cái)務(wù)入總金額"}, 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都要去掉繼承系統(tǒng)框架的base接口。mapper層寫好查詢出需要展示的數(shù)據(jù)，dto的注解中字段對應(yīng)的顯示。上述例子寫的是單層頭結(jié)構(gòu)，如果想要寫成雙層或者多層，可以想一下示例一樣添加。

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;

//產(chǎn)地

@ExcelProperty(value = {"在庫貨源","產(chǎn)地"} ,index = 2)

private String terrName;

//等級(jí)

@ExcelProperty(value = {"在庫貨源","等級(jí)"} ,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;

//強(qiáng)力

@ExcelProperty(value = {"在庫貨源","強(qiáng)力"} ,index = 8)

private String strong;

//強(qiáng)力均值

@ExcelProperty(value = {"在庫貨源","強(qiáng)力均值"} ,index = 9)

private String strongMean;

//提單號(hào)

@ExcelProperty(value = {"在庫貨源","提單號(hào)"} ,index = 10)

private String blNum;

//入庫單號(hào)

@ExcelProperty(value = {"在庫貨源","入庫單號(hào)"} ,index = 11)

private String entryNum;

//箱號(hào)

@ExcelProperty(value = {"在庫貨源","箱號(hào)"} ,index = 12)

private String batchNum;

//入庫件數(shù)

@ExcelProperty(value = {"在庫貨源","入庫件數(shù)"} ,index = 13)

private BigDecimal batchQty;

//銷售合同號(hào)

@ExcelProperty(value = {"在庫貨源","銷售合同號(hào)"} ,index = 14)

private String conNum;

//出庫單號(hào)

@ExcelProperty(value = {"在庫貨源","出庫單號(hào)"} ,index = 15)

private String outNum;

//出庫件數(shù)

@ExcelProperty(value = {"在庫貨源","出庫件數(shù)"} ,index = 16)

private BigDecimal outBatchQty;

//在庫箱數(shù)

@ExcelProperty(value = {"在庫貨源","在庫箱數(shù)"} ,index = 17)

private String outStatus;

//在庫件數(shù)

@ExcelProperty(value = {"在庫貨源","在庫件數(shù)"} ,index = 18)

private BigDecimal wareQty;

//采購合同單價(jià)

@ExcelProperty(value = {"在庫貨源","采購合同單價(jià)"} ,index = 19)

private BigDecimal conPrice;

//財(cái)務(wù)入庫成本

@ExcelProperty(value = {"在庫貨源","財(cái)務(wù)入庫成本"} ,index = 20)

private BigDecimal finaPrice;

//初始財(cái)務(wù)入庫成本(USD)

@ExcelProperty(value = {"在庫貨源","財(cái)務(wù)入庫成本"} ,index = 21)

private BigDecimal firstUsdPrice;

//初始財(cái)務(wù)入庫成本(CNY)

@ExcelProperty(value = {"在庫貨源","初始財(cái)務(wù)入庫成本"} ,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層組裝業(yè)務(wù)邏輯并且輸出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="內(nèi)貿(mào)開票明細(xì)表";

//組裝輸出流

ServletOutputStream outputStream = EasyEacelUtils.createOutputStream(request, httpServletResponse, fileName);

try {

ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);

Sheet sheet1 = new Sheet(1, 0, TraBilDetail.class);

//自適應(yīng)寬度

sheet1.setAutoWidth(Boolean.TRUE);

//添加數(shù)據(jù)

writer.write(traBilDetailMapper.selectTraBilDetail(traBilDetail), sheet1);

writer.finish();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

outputStream.close();

} catch (IOException e) {

e.printStackTrace();

}

這樣一個(gè)非常簡單的excel導(dǎo)出就完成了，這種方法適合于常規(guī)的excle導(dǎo)出。如果sql查詢的數(shù)據(jù)還不能瞞住需求，需要Java中繼續(xù)添加業(yè)務(wù)邏輯的話，可以繼續(xù)組裝成一個(gè)返回List的方法。示例如下

package ect.report.service.impl;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.metadata.Sheet;

import com.alibaba.excel.support.ExcelTypeEnum;

import ect.co.mapper.ConContractMapper;

import ect.fs.mapper.FsPayAppExpMapper;

import ect.fs.mapper.FsPayAppGooMapper;

import ect.inv.mapper.WarehousingMapper;

import ect.report.dto.ConSheet;

import ect.report.mapper.ConSheetMapper;

import ect.report.service.IConSheetService;

import ect.report.utils.EasyEacelUtils;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import org.springframework.transaction.annotation.Transactional;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.math.BigDecimal;

import java.util.HashSet;

import java.util.LinkedList;

import java.util.List;

@Service

@Transactional(rollbackFor = Exception.class)

public class ConSheetServiceImpl implements IConSheetService{

@Autowired

private ConSheetMapper sheetMapper;

@Autowired

private FsPayAppGooMapper fsPayAppGooMapper;

@Autowired

private FsPayAppExpMapper fsPayAppExpMapper;

@Autowired

private ConContractMapper conContractMapper;

@Autowired

private WarehousingMapper warehousingMapper;

private static final String REMORK_FIX = "轉(zhuǎn)倉單重量:";

@Override

public void export(HttpServletRequest request, ConSheet conSheet, HttpServletResponse httpServletResponse) throws IOException {

String fileName="聯(lián)營業(yè)務(wù)表";

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中查詢出的數(shù)據(jù)添加合計(jì)邏輯

* @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);

// 費(fèi)用

BigDecimal sumExp = fsPayAppExpMapper.selectSumExpByConId(conId.floatValue());

uniConExport.setSumExp(sumExp);

// 未銷數(shù)量

BigDecimal noOutBatchWeight = conContractMapper.selectQtyNotSoldByConId(conId.floatValue());

uniConExport.setNoOutBatchWeight(noOutBatchWeight);

// 求已銷數(shù)量

BigDecimal outBatchWeight = conContractMapper.selectQtySoldByConId(conId.floatValue());

uniConExport.setOutBatchWeight(outBatchWeight);

// 風(fēng)險(xiǎn)度

BigDecimal invenWeight = uniConExport.getInvenWeight();

if (invenWeight != null && invenWeight.compareTo(BigDecimal.ZERO) != 0) {

// 我司支付

BigDecimal appAmt = uniConExport.getAppAmt();

//回籠資金

BigDecimal entryAmt = uniConExport.getEntryAmt();

// 費(fèi)用

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("合計(jì): ");

List conSheets = groupByParty(uniConExportList);

conSheets.add(conSheetSum);

return conSheets;

}

/**

* 根據(jù)部門進(jìn)行分組

* @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;

}

官方示例dome

OutputStream out = new FileOutputStream("/Users/jipengfei/2007.xlsx");

ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);

//寫第一個(gè)sheet, sheet1 數(shù)據(jù)全是List 無模型映射關(guān)系

Sheet sheet1 = new Sheet(1, 3);

sheet1.setSheetName("第一個(gè)sheet");

//設(shè)置列寬設(shè)置每列的寬度

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 設(shè)置自適應(yīng)寬度

//sheet1.setAutoWidth(Boolean.TRUE);

writer.write1(createTestListObject(), sheet1);

//寫第二個(gè)sheet sheet2 模型上打有表頭的注解，合并單元格

Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二個(gè)sheet", null);

sheet2.setTableStyle(createTableStyle());

writer.write(createTestListJavaMode(), sheet2);

//寫第三個(gè)sheet包含多個(gè)table情況

Sheet sheet3 = new Sheet(3, 0);

sheet3.setSheetName("第三個(gè)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);

//關(guān)閉資源

writer.finish();

out.close();

2.表頭不帶注解的方法

這種dto的寫法跟上述的dto寫法基本相同，就是去掉了注解。也可以不用dto，直接用List來代替dto的作用，其他都一樣。表頭帶注解的方法不用自己寫表頭，不帶注解的方法需要自己去組裝表頭，但是這種方法可以去調(diào)整表頭字段的寬度。帶注解的方法表頭會(huì)自適應(yīng)一個(gè)寬度出來。

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 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){

dailyRate=dailyRates.get(0);

}

final BigDecimal rate = dailyRate.getRate();

List incomeCostDetails = invomeCostDetailMapper.selectIncomDetail(detail);

incomeCostDetails.stream().forEach(incomeCostDetail -> {

if (RMB_SO_CON_NUM.equals(incomeCostDetail.getDetType())) {

//發(fā)票金額(不含稅)

incomeCostDetail.setInvoApplyAmount(incomeCostDetail.getInvoApplyAmountRmb());

//incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountRmb());

}

if (USD_SO_CON_NUM.equals(incomeCostDetail.getDetType())) {

//發(fā)票金額(不含稅)

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 ("進(jìn)口".equals(incomeCostDetail.getBusiType())) {

incomeCostDetail.setInvoApplyAmountYuan(incomeCostDetail.getInvoApplyAmount());

} else if ("轉(zhuǎn)口".equals(incomeCostDetail.getBusiType())) {

if (finaRate.compareTo(BigDecimal.ONE) == 0) {

finaRate = rate;

}

incomeCostDetail.setInvoApplyAmountYuan(incomeCostDetail.getInvoApplyAmount().multiply(finaRate));

}

//財(cái)務(wù)出庫單價(jià)(人民幣/噸) =出庫金額(元)/財(cái)務(wù)出庫重量

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));

}

///財(cái)務(wù)出庫單價(jià)

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));

}

//對應(yīng)初始采購成本金額

incomeCostDetail.setPoCostAmount(incomeCostDetail.getPoCostAmountUsd());

//對應(yīng)初始采購成本金額(元)

incomeCostDetail.setPoCostAmountCn(incomeCostDetail.getPoCostAmountRmb());

//現(xiàn)貨毛利(合并)原幣發(fā)票金額(不含稅)-對應(yīng)初始采購成本金額(原幣)

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()));

}

//現(xiàn)貨毛利(合并)人民幣發(fā)票金額(不含稅)-對應(yīng)初始采購成本金額(元)

BigDecimal applyAmount = BigDecimal.ZERO;

if ("進(jìn)口".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 ("轉(zhuǎn)口".equals(incomeCostDetail.getBusiType()) && ("中紡棉花(香港)有限公司".equals(incomeCostDetail.getComName()))) {

logger.info("2111");

}

//現(xiàn)貨毛利(合并)人民幣-財(cái)務(wù)口徑發(fā)票金額(不含稅)-對應(yīng)初始采購成本金額(元)

if ("進(jìn)口".equals(incomeCostDetail.getBusiType()) || ("轉(zhuǎn)口".equals(incomeCostDetail.getBusiType()) && (!"1445".equals(incomeCostDetail.getComCode())))) {

if (incomeCostDetail.getIncomeMeshAmountCn() != null) {

incomeCostDetail.setIncomeMeshAmountCnFina(incomeCostDetail.getIncomeMeshAmountCn());

}

} else if ("轉(zhuǎn)口".equals(incomeCostDetail.getBusiType()) && ("1445".equals(incomeCostDetail.getComCode()))) {

if (incomeCostDetail.getIncomeMeshAmountCn() != null && rate!=null) {

incomeCostDetail.setIncomeMeshAmountCnFina(incomeCostDetail.getIncomeMeshAmount().multiply(rate));

}

//對應(yīng)初始采購成本單價(jià)

if (incomeCostDetail.getPoCostAmount() != null && incomeCostDetail.getPoCostBatchWeight() != null) {

incomeCostDetail.setPoCostPricce(incomeCostDetail.getPoCostAmount().divide(incomeCostDetail.getPoCostBatchWeight(), 10, BigDecimal.ROUND_HALF_UP));

}

//對應(yīng)初始采購成本單價(jià)(元/噸)

if (incomeCostDetail.getPoCostAmountCn() != null && incomeCostDetail.getPoCostBatchWeight() != null) {

incomeCostDetail.setPoCostPricceCn(incomeCostDetail.getPoCostAmountCn().divide(incomeCostDetail.getPoCostBatchWeight(), 10, BigDecimal.ROUND_HALF_UP));

}

//對應(yīng)初始財(cái)務(wù)入庫匯率

if (incomeCostDetail.getPoCostAmountCn() != null && incomeCostDetail.getPoCostAmount() != null && BigDecimal.ZERO.compareTo(incomeCostDetail.getPoCostAmount()) != 0) {

//對應(yīng)初始采購成本金額/對應(yīng)初始采購成本金額

incomeCostDetail.setPoRate(incomeCostDetail.getPoCostAmountCn().divide(incomeCostDetail.getPoCostAmount(), 10, BigDecimal.ROUND_HALF_UP));

}

//現(xiàn)貨毛利(獨(dú)立)原幣發(fā)票金額(不含稅)-財(cái)務(wù)出庫金額(原幣)

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()));

}

//現(xiàn)貨毛利(獨(dú)立)人民幣發(fā)票金額(不含稅)-財(cái)務(wù)出庫金額(元)

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()));

}

//現(xiàn)貨毛利(獨(dú)立)人民幣-財(cái)務(wù)口徑發(fā)票金額(不含稅)-財(cái)務(wù)出庫金額(元)

if ("進(jìn)口".equals(incomeCostDetail.getBusiType()) || ("轉(zhuǎn)口".equals(incomeCostDetail.getBusiType()) && (!"1445".equals(incomeCostDetail.getComCode())))) {

if (incomeCostDetail.getIncomeSelfAmountCn() != null) {

incomeCostDetail.setIncomeSelfAmountCnFina(incomeCostDetail.getIncomeSelfAmountCn());

}

} else if ("轉(zhuǎn)口".equals(incomeCostDetail.getBusiType()) && ("1445".equals(incomeCostDetail.getComCode()))) {

if (incomeCostDetail.getIncomeSelfAmount() != null && rate!=null) {

incomeCostDetail.setIncomeSelfAmountCnFina(incomeCostDetail.getIncomeSelfAmountCn().multiply(rate));

}

if ("轉(zhuǎn)口".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> 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("合同號(hào)");

headCoulumn4.add("收入成本毛利表");

headCoulumn4.add("");

headCoulumn4.add("開票申請?zhí)?#34;);

headCoulumn5.add("收入成本毛利表");

headCoulumn5.add("");

headCoulumn5.add("過賬日期");

headCoulumn6.add("收入成本毛利表");

headCoulumn6.add("");

headCoulumn6.add("業(yè)務(wù)類型");

headCoulumn7.add("收入成本毛利表");

headCoulumn7.add("開票截止日");

headCoulumn7.add("發(fā)票數(shù)量");

headCoulumn8.add("收入成本毛利表");

headCoulumn8.add(endDate);

headCoulumn8.add("發(fā)票金額(不含稅)");

headCoulumn9.add("收入成本毛利表");

headCoulumn9.add(endDate);

headCoulumn9.add("發(fā)票金額(不含稅-人民幣)");

headCoulumn10.add("收入成本毛利表");

headCoulumn10.add("");

headCoulumn10.add("財(cái)務(wù)出庫數(shù)量");

headCoulumn11.add("收入成本毛利表");

headCoulumn11.add("");

headCoulumn11.add("財(cái)務(wù)出庫單價(jià)(原幣)");

headCoulumn12.add("收入成本毛利表");

headCoulumn12.add("");

headCoulumn12.add("財(cái)務(wù)出庫金額(原幣)");

headCoulumn13.add("收入成本毛利表");

headCoulumn13.add("");

headCoulumn13.add("財(cái)務(wù)出庫單價(jià)(人民幣/噸)");

headCoulumn14.add("收入成本毛利表");

headCoulumn14.add("");

headCoulumn14.add("財(cái)務(wù)出庫金額(元)");

headCoulumn15.add("收入成本毛利表");

headCoulumn15.add("");

headCoulumn15.add("財(cái)務(wù)出庫匯率");

headCoulumn16.add("收入成本毛利表");

headCoulumn16.add("");

headCoulumn16.add("對應(yīng)初始采購成本單價(jià)(原幣)");

headCoulumn17.add("收入成本毛利表");

headCoulumn17.add("");

headCoulumn17.add("對應(yīng)初始采購成本金額(原幣)");

headCoulumn18.add("收入成本毛利表");

headCoulumn18.add("");

headCoulumn18.add("對應(yīng)初始采購成本金額(元)");

headCoulumn19.add("收入成本毛利表");

headCoulumn19.add("");

headCoulumn19.add("對應(yīng)初始采購成本單價(jià)(元/噸)");

headCoulumn20.add("收入成本毛利表");

headCoulumn20.add("");

headCoulumn20.add("對應(yīng)初始財(cái)務(wù)入庫匯率");

headCoulumn21.add("收入成本毛利表");

headCoulumn21.add("");

headCoulumn21.add("現(xiàn)貨毛利(合并)原幣");

headCoulumn22.add("收入成本毛利表");

headCoulumn22.add("");

headCoulumn22.add("現(xiàn)貨毛利(合并)人民幣");

headCoulumn23.add("收入成本毛利表");

headCoulumn23.add("");

headCoulumn23.add("現(xiàn)貨毛利(合并)人民幣-財(cái)務(wù)口徑");

headCoulumn24.add("收入成本毛利表");

headCoulumn24.add("");

headCoulumn24.add("現(xiàn)貨毛利(獨(dú)立)原幣");

headCoulumn25.add("收入成本毛利表");

headCoulumn25.add("");

headCoulumn25.add("現(xiàn)貨毛利(獨(dú)立)人民幣");

headCoulumn26.add("收入成本毛利表");

headCoulumn26.add("");

headCoulumn26.add("現(xiàn)貨毛利(獨(dú)立)人民幣-財(cái)務(wù)口徑");

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);

//寫第一個(gè)sheet, sheet1 數(shù)據(jù)全是List 無模型映射關(guān)系

Sheet sheet1 = new Sheet(1, 3);

sheet1.setSheetName("第一個(gè)sheet");

//設(shè)置列寬設(shè)置每列的寬度

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 設(shè)置自適應(yīng)寬度

//sheet1.setAutoWidth(Boolean.TRUE);

writer.write1(createTestListObject(), sheet1);

//寫第二個(gè)sheet sheet2 模型上打有表頭的注解，合并單元格

Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二個(gè)sheet", null);

sheet2.setTableStyle(createTableStyle());

writer.write(createTestListJavaMode(), sheet2);

//寫第三個(gè)sheet包含多個(gè)table情況

Sheet sheet3 = new Sheet(3, 0);

sheet3.setSheetName("第三個(gè)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);

//關(guān)閉資源

writer.finish();

out.close();

總結(jié)

以上舉例是常用的兩種組裝數(shù)據(jù)成excel的方法，第一種適用常規(guī)的excel導(dǎo)出，但是表頭不能控制，數(shù)據(jù)也必須跟dto中對應(yīng)，限制相對大一點(diǎn)。第二種方法靈活行比較大，表頭可以自己組裝，數(shù)據(jù)也不用局限dto。在實(shí)際運(yùn)用中就最大的不同就是第二種可以做動(dòng)態(tài)列，但是由于第一種因?yàn)槠渚窒扌跃蜔o法完成動(dòng)態(tài)列的實(shí)現(xiàn)，但是它卻省了很多功夫來組裝數(shù)據(jù)。兩者各有優(yōu)劣，就看實(shí)際的業(yè)務(wù)需求了。

當(dāng)然還有同一個(gè)sheet中使用table來實(shí)現(xiàn)兩部分?jǐn)?shù)據(jù)塊的展示方法等等，這些都可以從官方文檔中找個(gè)具體的使用方法。這里就不做詳細(xì)闡述了，本文主要就是講解數(shù)據(jù)的組裝邏輯方法。

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的easyexcel 列头合并_Easyexcel使用文档及动态列的实现的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： php 加载redise_PHP Red
下一篇：怎样设计访谈提纲_论访谈提纲的设计