easyExcel简单excel导出以及多sheet页导出

Java 程序员在项目上一般会经常遇到解析数据、生成Excel的需求，比较流行的就是Apache poi框架了，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。esayExcel在大数据量的时候是一行一行的解析，不同于POI的一次性解析，这样避免了内存的溢出。

我推荐使用的是2.1.2版本，也是我之前使用的最新版。

官方文档的地址

https://github.com/alibaba/easyexcel

添加依赖

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>easyexcel</artifactId>

<version>2.1.2</version>

</dependency>

使用方法(模板导出)

写一个dto作为模型，在属性的名称加上注解，作为表头显示在表格中。@ContentRowHeight 单元格行高 @ColumnWidth 单元格列宽 @HeadRowHeight 头的行高@ExcelProperty输出在表格的字段value值代表输出的值index代表是列数（列数是从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 = {"供应商"},
        index = 6
    )
    private String venName;
    @ExcelProperty(
        value = {"实际收款方"},
        index = 7
    )
    @ColumnWidth(30)
    private String actualVenName;
    @ExcelProperty(
        value = {"发票类型"},
        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 = {"发票币种"},
        index = 13
    )
    private String invoCurrency;
    @ExcelProperty(
        value = {"付款币种"},
        index = 14
    )
    private String payCurrency;
    @ExcelProperty(
        value = {"实际支付日期"},
        index = 15
    )
    @DateTimeFormat("yyyy-MM-dd")
    private Date actualDate;
    @ExcelProperty(
        value = {"状态"},
        index = 16
    )
    private String status;
    @ExcelProperty(
        value = {"仓储接口状态"},
        index = 17
    )
    private String interStatus;
    @ExcelProperty(
        value = {"付款状态"},
        index = 18
    )
    private String payStatus;
    @ExcelProperty(
        value = {"付款申请状态"},
        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方法省略
}

上述的是简单的头结构，如果需要复杂的头结构或者多层头结构可以像下面示例一样添加这样的在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 = {"在库货源", "产地"},
        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;
    @ExcelProperty(
        value = {"在库货源", "财务入库成本"},
        index = 21
    )
    private BigDecimal firstUsdPrice;
    @ExcelProperty(
        value = {"在库货源", "初始财务入库成本"},
        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层写好逻辑，查询出数据并输出成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));
        }

    }

这样一个非常简单的excel导出就完成了，这种方法适合于常规的excle导出。能试用很多时候的基本报表导出。如果需要导出多sheet页的文件，只需修改一下业务逻辑即可。如下所示

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, "供应商信息").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页的导出，但是我用的都是模板导出，表头都是固定死的，数据也必须跟模板中的属性对应，所以限制比较大，也不过灵活。

不用模板导出方式

需要自己手写一个方法，然后自己把表头的数据插入进去

 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("供应商名称");
        headCoulumn2.add("客商信息表");headCoulumn2.add("供应商类别");
        headCoulumn3.add("客商信息表");headCoulumn3.add("地区");
        headCoulumn4.add("客商信息表");headCoulumn4.add("注册省");
        headCoulumn5.add("客商信息表"); headCoulumn5.add("供应商分类");
        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, "供应商信息").head(SupplierInfo.class).build()里面的SupplierInfo.class替换为方法名就好了。

 WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "供应商信息").head(createHeadList() ).build();

以上就是两种基本的导出方式以及多sheet页导出方式。