easyExcel使用

參考
官方文檔
github地址
easyExcel 2.1.3 使用
推薦easyExcel簡單excel導(dǎo)出以及多sheet頁導(dǎo)出

1.簡介
2.官方文檔的地址
3.添加依賴
4.使用方法(模板導(dǎo)出), 單個(gè)sheet
5.使用方法(模板導(dǎo)出), 多個(gè)sheet
6.不用模板導(dǎo)出方式
1.簡介

Java 程序員在項(xiàng)目上一般會經(jīng)常遇到解析數(shù)據(jù)、生成Excel的需求，比較流行的就是Apache poi框架了，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。esayExcel在大數(shù)據(jù)量的時(shí)候是一行一行的解析，不同于POI的一次性解析，這樣避免了內(nèi)存的溢出。
我推薦使用的是2.1.2版本，也是我之前使用的最新版。

2.官方文檔的地址
https://github.com/alibaba/easyexcel
3.添加依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.2</version>
</dependency>
4.使用方法(模板導(dǎo)出),單個(gè)sheet

寫一個(gè)dto作為模型，在屬性的名稱加上注解，作為表頭顯示在表格中。@ContentRowHeight 單元格行高 @ColumnWidth 單元格列寬 @HeadRowHeight 頭的行高@ExcelProperty輸出在表格的字段value值代表輸出的值index代表是列數(shù)（列數(shù)是從0開始的所有第0列也就是第一列）@ExcelIgnore不輸出在表格中的字段

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.math.BigDecimal;
import java.util.Date;

@ContentRowHeight(20)
@HeadRowHeight(30)
@ColumnWidth(25)
public class PayApplyExcelDto {
    @ExcelProperty(
        value = {"公司名稱"},
        index = 0
    )
    private String comName;
    @ExcelProperty(
        value = {"制單部門"},
        index = 1
    )
    private String unitName;
    @ExcelProperty(
        value = {"申請編號"},
        index = 2
    )
    private String applyNum;
    @ExcelProperty(
        value = {"付款類別"},
        index = 3
    )
    private String typeCode;
    @ExcelProperty(
        value = {"付款類型"},
        index = 4
    )
    private String detTypeCode;
    @ExcelProperty(
        value = {"合同號"},
        index = 5
    )
    private String conNum;
    @ExcelProperty(
        value = {"供應(yīng)商"},
        index = 6
    )
    private String venName;
    @ExcelProperty(
        value = {"實(shí)際收款方"},
        index = 7
    )
    @ColumnWidth(30)
    private String actualVenName;
    @ExcelProperty(
        value = {"發(fā)票類型"},
        index = 8
    )
    private String invoiceType;
    @ExcelProperty(
        value = {"申請人"},
        index = 9
    )
    private String applicantName;
    @ExcelProperty(
        value = {"申請日期"},
        index = 10
    )
    @DateTimeFormat("yyyy-MM-dd")
    private Date applyDate;
    @ExcelProperty(
        value = {"申請金額"},
        index = 11
    )
    private BigDecimal applyAmt;
    @ExcelProperty(
        value = {"匯率"},
        index = 12
    )
    private BigDecimal rate;
    @ExcelProperty(
        value = {"發(fā)票幣種"},
        index = 13
    )
    private String invoCurrency;
    @ExcelProperty(
        value = {"付款幣種"},
        index = 14
    )
    private String payCurrency;
    @ExcelProperty(
        value = {"實(shí)際支付日期"},
        index = 15
    )
    @DateTimeFormat("yyyy-MM-dd")
    private Date actualDate;
    @ExcelProperty(
        value = {"狀態(tài)"},
        index = 16
    )
    private String status;
    @ExcelProperty(
        value = {"倉儲接口狀態(tài)"},
        index = 17
    )
    private String interStatus;
    @ExcelProperty(
        value = {"付款狀態(tài)"},
        index = 18
    )
    private String payStatus;
    @ExcelProperty(
        value = {"付款申請狀態(tài)"},
        index = 19
    )
    private String payInterStatus;
    @ExcelProperty(
        value = {"付款申請接口消息"},
        index = 20
    )
    private String payInterInfo;
    @ExcelProperty(
        value = {"備注"},
        index = 21
    )
    @ColumnWidth(60)
    private String remark;
    @ExcelIgnore
    private String attribute8;
    @ExcelIgnore
    private String attribute10;
    @ExcelIgnore
    private String attribute12;
    @ExcelIgnore
    private String attribute13;
    @ExcelIgnore
    private String attribute14;
    @ExcelIgnore
    private String attribute15;
    @ExcelIgnore
    private Long payId;
    @ExcelIgnore
    private Long comId;
    @ExcelIgnore
    private Long unitId;
    @ExcelIgnore
    private Long cunitId;
    @ExcelIgnore
    private Long venId;
    @ExcelIgnore
    private Long actualPayeeId;
    @ExcelIgnore
    private Long venSiteId;
    @ExcelIgnore
    private String payMethod;
    @ExcelIgnore
    private Long operatorId;
    @ExcelIgnore
    private Date payDate;
    @ExcelIgnore
    private Date postDate;
    @JsonFormat(
        pattern = "yyyy-MM-dd"
    )
    @ExcelIgnore
    private Date applyDateStart;
    @JsonFormat(
        pattern = "yyyy-MM-dd"
    )
    @ExcelIgnore
    private Date applyDateEnd;
    @ExcelIgnore
    private Long acctId;
    @ExcelIgnore
    private Long applicantId;
    @ExcelIgnore
    private String applyDateStr;
    @ExcelIgnore
    private String applyAmtCn;
    @ExcelIgnore
    private String creditNum;
    @ExcelIgnore
    private String interInfo;
    @ExcelIgnore
    private String interNum;
    @ExcelIgnore
    private String payInterNum;
    @ExcelIgnore
    private String finOpinion;
    @ExcelIgnore
    private String unbatchFlag;
    @ExcelIgnore
    private String taxCode;
    @ExcelIgnore
    private BigDecimal taxAmt;
    @ExcelIgnore
    private String invoiceCategory;
    @ExcelIgnore
    private String rateType;
    @ExcelIgnore
    private String acctNum;
    @ExcelIgnore
    private String actualFlag;
    @ExcelIgnore
    private Long bankBranchId;
    @ExcelIgnore
    private String bankName;
    @ExcelIgnore
    private Long postPeopleId;
    @ExcelIgnore
    private BigDecimal sapPayAmt;
    @ExcelIgnore
    private BigDecimal sapPayPamt;
    @ExcelIgnore
    private String bankArea;
    @ExcelIgnore
    private String urgencyDegree;
    @ExcelIgnore
    private String urgencyReason;
    @ExcelIgnore
    private Long busiPeopleId;
    @ExcelIgnore
    private String interAcctNum;
    @ExcelIgnore
    private String conFlag;
    @ExcelIgnore
    private String postPeopleName;
    @ExcelIgnore
    private String busiPeopleName;
    @ExcelIgnore
    private String categoryCode;
    @ExcelIgnore
    private String taxRate;
    @ExcelIgnore
    private String companyFullName;
    @ExcelIgnore
    private String operatorName;
    @ExcelIgnore
    private String costUnitName;
    @ExcelIgnore
    private String venNum;
    @ExcelIgnore
    private String mdmNum;
    @ExcelIgnore
    private String venMdmNum;
    @ExcelIgnore
    private String address;
    @ExcelIgnore
    private String bankAcctNum;
    @ExcelIgnore
    private BigDecimal applyAmtStart;
    @ExcelIgnore
    private BigDecimal applyAmtEnd;
    @ExcelIgnore
    private String costUnitCode;
    @ExcelIgnore
    private String unitCode;
    @ExcelIgnore
    private String bankBranchName;
    @ExcelIgnore
    private String bankBranchNum;
    @ExcelIgnore
    private String typeCodeMeaning;
    @ExcelIgnore
    private String detTypeCodeMeaning;
    @ExcelIgnore
    private String payMethodMeaning;
    @ExcelIgnore
    private String employeeName;
    @ExcelIgnore
    private String positionName;
    @ExcelIgnore
    private String workflowType;
    @ExcelIgnore
    private String currencyName;
    @ExcelIgnore
    private String decription;
    @ExcelIgnore
    private BigDecimal endAmt;
    @ExcelIgnore
    private String endAmtStr;
    @ExcelIgnore
    private String applyAmtStr;
    @ExcelIgnore
    private String taxAmtStr;
    @ExcelIgnore
    private Long conId;
    @ExcelIgnore
    private String extraNum;
    @ExcelIgnore
    private String blNum;
    @ExcelIgnore
    private String invoNum;
    @ExcelIgnore
    private String invoiceTypeMeaning;
    @ExcelIgnore
    private BigDecimal price;
    @ExcelIgnore
    private BigDecimal weight;
    @ExcelIgnore
    private String payDateStr;
    @ExcelIgnore
    private String bankAreaMeaning;
    @ExcelIgnore
    private String approvalDate;
    @ExcelIgnore
    private Long padMillVenId;
    @ExcelIgnore
    private String padMillVenName;
    @ExcelIgnore
    private BigDecimal outGooAmt;
    @ExcelIgnore
    private BigDecimal depoSumAmt;
    @ExcelIgnore
    private BigDecimal finaAmt;
    @ExcelIgnore
    private BigDecimal noReachAmt;
    @ExcelIgnore
    private String nonCancelFlag;
    @ExcelIgnore
    private String hasBill;
    @ExcelIgnore
    private String myApprovalFlag;
    @ExcelIgnore
    private String reAddress;
    @ExcelIgnore
    private String portCertificate;
//get和set方法省略
}

上述的是簡單的頭結(jié)構(gòu)，如果需要復(fù)雜的頭結(jié)構(gòu)或者多層頭結(jié)構(gòu)可以像下面示例一樣添加這樣的在value中寫出。

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import java.math.BigDecimal;

@ContentRowHeight(20)
@HeadRowHeight(30)
@ColumnWidth(25)
public class BatchSupply {
    @ExcelProperty(
        value = {"在庫貨源", "姓名"},
        index = 0
    )
    private String comName;
    @ExcelProperty(
        value = {"在庫貨源", "倉庫名稱"},
        index = 1
    )
    private String subinvName;
    @ExcelProperty(
        value = {"在庫貨源", "產(chǎn)地"},
        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 = {"在庫貨源", "強(qiáng)力"},
        index = 8
    )
    private String strong;
    @ExcelProperty(
        value = {"在庫貨源", "強(qiáng)力均值"},
        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 = {"在庫貨源", "入庫件數(shù)"},
        index = 13
    )
    private BigDecimal batchQty;
    @ExcelProperty(
        value = {"在庫貨源", "銷售合同號"},
        index = 14
    )
    private String conNum;
    @ExcelProperty(
        value = {"在庫貨源", "出庫單號"},
        index = 15
    )
    private String outNum;
    @ExcelProperty(
        value = {"在庫貨源", "出庫件數(shù)"},
        index = 16
    )
    private BigDecimal outBatchQty;
    @ExcelProperty(
        value = {"在庫貨源", "在庫箱數(shù)"},
        index = 17
    )
    private String outStatus;
    @ExcelProperty(
        value = {"在庫貨源", "在庫件數(shù)"},
        index = 18
    )
    private BigDecimal wareQty;
    @ExcelProperty(
        value = {"在庫貨源", "采購合同單價(jià)"},
        index = 19
    )
    private BigDecimal conPrice;
    @ExcelProperty(
        value = {"在庫貨源", "財(cái)務(wù)入庫成本"},
        index = 20
    )
    private BigDecimal finaPrice;
    @ExcelProperty(
        value = {"在庫貨源", "財(cái)務(wù)入庫成本"},
        index = 21
    )
    private BigDecimal firstUsdPrice;
    @ExcelProperty(
        value = {"在庫貨源", "初始財(cái)務(wù)入庫成本"},
        index = 22
    )
    private BigDecimal firstRmbPrice;
    @ExcelIgnore
    private String lengthMax;
    @ExcelIgnore
    private String lengthMin;
    @ExcelIgnore
    private String micronMax;
    @ExcelIgnore
    private String micronMin;
    @ExcelIgnore
    private String strongMax;
    @ExcelIgnore
    private String strongMin;
    @ExcelIgnore
    private String terrCode;
    @ExcelIgnore
    private String comCode;
    @ExcelIgnore
    private String subinvId;
    @ExcelIgnore
    private String isBatchFlag;
    @ExcelIgnore
    private Long specId;
}

然后在service層寫好邏輯，查詢出數(shù)據(jù)并輸出成Excel文件。

@Override
    public void export(IRequest requestContext,HttpServletRequest request, PayApplyExcelDto dto, HttpServletResponse httpServletResponse) throws IOException {
        try {
            String fileName = URLEncoder.encode("付款申請", "UTF-8");
            httpServletResponse.setContentType("application/vnd.ms-excel");
            httpServletResponse.setCharacterEncoding("utf-8");
            httpServletResponse.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(httpServletResponse.getOutputStream(), PayApplyExcelDto.class).autoCloseStream(Boolean.FALSE)
                    .sheet("sheet").doWrite(loadReportData(requestContext,dto));
        } catch (Exception e){
            httpServletResponse.reset();
            httpServletResponse.setContentType("application/json");
            httpServletResponse.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下載文件失敗" + e.getMessage());
            httpServletResponse.getWriter().println(JSON.toJSONString(map));
        }
    }

這樣一個(gè)非常簡單的excel導(dǎo)出就完成了，這種方法適合于常規(guī)的excle導(dǎo)出。能試用很多時(shí)候的基本報(bào)表導(dǎo)出。

5.使用方法(模板導(dǎo)出), 多個(gè)sheet

如果需要導(dǎo)出多sheet頁的文件，只需修改一下業(yè)務(wù)邏輯即可。如下所示

public void export(HttpServletRequest request, CustInfo custInfo, HttpServletResponse httpServletResponse) throws IOException {
        String fileName = URLEncoder.encode("客商信息表", "UTF-8");
        List<CustInfo> data = this.custInfoMapper.getCustData(custInfo);
        List data1 = this.custInfoMapper.getVendorData(custInfo);

        try {
            httpServletResponse.setContentType("application/vnd.ms-excel");
            httpServletResponse.setCharacterEncoding("utf-8");
            httpServletResponse.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            ExcelWriter excelWriter = EasyExcel.write(httpServletResponse.getOutputStream()).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "客戶信息").head(CustInfo.class).build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "供應(yīng)商信息").head(SupplierInfo.class).build();
            excelWriter.write(data, writeSheet1);
            excelWriter.write(data1, writeSheet2);
            excelWriter.finish();
        } catch (Exception var10) {
            httpServletResponse.reset();
            httpServletResponse.setContentType("application/json");
            httpServletResponse.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap();
            map.put("status", "failure");
            map.put("message", "下載文件失敗" + var10.getMessage());
            httpServletResponse.getWriter().println(JSON.toJSONString(map));
        } 
    }

上面就是單sheet頁和多sheet頁的導(dǎo)出，但是我用的都是模板導(dǎo)出，表頭都是固定死的，數(shù)據(jù)也必須跟模板中的屬性對應(yīng)，所以限制比較大，也不過靈活。

6. 不用模板導(dǎo)出方式

需要自己手寫一個(gè)方法，然后自己把表頭的數(shù)據(jù)插入進(jìn)去

 List<List<String>> createHeadList(){
        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>();

        headCoulumn1.add("客商信息表");headCoulumn1.add("供應(yīng)商名稱");
        headCoulumn2.add("客商信息表");headCoulumn2.add("供應(yīng)商類別");
        headCoulumn3.add("客商信息表");headCoulumn3.add("地區(qū)");
        headCoulumn4.add("客商信息表");headCoulumn4.add("注冊省");
        headCoulumn5.add("客商信息表"); headCoulumn5.add("供應(yīng)商分類");
        headCoulumn6.add("客商信息表"); headCoulumn6.add("信用代碼");

        head.add(headCoulumn1);
        head.add(headCoulumn2);
        head.add(headCoulumn3);
        head.add(headCoulumn4);
        head.add(headCoulumn5);
        head.add(headCoulumn6);

        return head;
    }

然后把EasyExcel.writerSheet(1, "供應(yīng)商信息").head(SupplierInfo.class).build()里面的SupplierInfo.class替換為方法名就好了。

 WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "供應(yīng)商信息").head(createHeadList() ).build();

以上就是兩種基本的導(dǎo)出方式以及多sheet頁導(dǎo)出方式。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容