参考
官方文档
github地址
easyExcel 2.1.3 使用
推荐easyExcel简单excel导出以及多sheet页导出
1.简介
2.官方文档的地址
3.添加依赖
4.使用方法(模板导出), 单个sheet
5.使用方法(模板导出), 多个sheet
6.不用模板导出方式
1.简介
Java 程序员在项目上一般会经常遇到解析数据、生成Excel的需求，比较流行的就是Apache poi框架了，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。esayExcel在大数据量的时候是一行一行的解析，不同于POI的一次性解析，这样避免了内存的溢出。
我推荐使用的是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.使用方法(模板导出),单个sheet
写一个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导出。能试用很多时候的基本报表导出。
5.使用方法(模板导出), 多个sheet
如果需要导出多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页的导出，但是我用的都是模板导出，表头都是固定死的，数据也必须跟模板中的属性对应，所以限制比较大，也不过灵活。
6. 不用模板导出方式
需要自己手写一个方法，然后自己把表头的数据插入进去
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页导出方式。
网友评论