最近了解到这个工具,阿里开源的,其实官方示例也演示的很清楚了,我只是做了demo。
官方文档地址:EasyExcel · 语雀
下面是我自己做的demo,多sheet导出。
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class TestExcel {
public static void main(String[] args) {
ExcelWriter excelWriter = null;
try {
String fileName = "E:/file/测试EasyExcel" + System.currentTimeMillis() + ".xlsx";
excelWriter = EasyExcel.write(fileName).build();
// 第一个sheet
WriteSheet sheet0 = EasyExcel.writerSheet(0, "Report模板").head(ExcelReport.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
for (int i = 0; i < 10; i++) {
List<ExcelReport> sheet1Data = sheet0Data();
excelWriter.write(sheet1Data, sheet0);
}
// 第二个sheet
WriteSheet sheet1 = EasyExcel.writerSheet(1, "Order模板").head(ExcelOrder.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
for (int i = 0; i < 10; i++) {
List<ExcelOrder> sheet1Data = sheet1Data();
excelWriter.write(sheet1Data, sheet1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
protected static List<ExcelReport> sheet0Data() {
Date date = new Date();
List<ExcelReport> result = new ArrayList<>();
for (int i = 0; i < 50000; i++) {
ExcelReport report = new ExcelReport();
report.setPeriod("2021-10");
report.setReportDate(date);
report.setQty(i);
report.setReportAmount(new BigDecimal(i + "." + i));
result.add(report);
}
return result;
}
protected static List<ExcelOrder> sheet1Data() {
Date date = new Date();
List<ExcelOrder> result = new ArrayList<>();
for (int i = 0; i < 50000; i++) {
ExcelOrder order = new ExcelOrder();
order.setPeriod("2021-10");
order.setOrderDate(date);
order.setOrderQty(i);
order.setOrderAmount(new BigDecimal("10" + i));
result.add(order);
}
return result;
}
}
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
@Data
public class ExcelReport {
@ExcelProperty(index = 0, value = "期间")
private String period;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@ExcelProperty(index = 1, value = "报表时间")
private Date reportDate;
@ExcelProperty(index = 2, value = "数量")
private int qty;
@NumberFormat("0.0000")
@ExcelProperty(index = 3, value = "报表金额")
private BigDecimal reportAmount;
}
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
@Data
public class ExcelOrder {
@ExcelProperty(index = 0, value = "期间")
private String period;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@ExcelProperty(index = 1, value = "订单时间")
private Date orderDate;
@ExcelProperty(index = 2, value = "订单数量")
private int orderQty;
@NumberFormat(value = "#,###")
@ExcelProperty(index = 3, value = "订单金额")
private BigDecimal orderAmount;
}
补充一个,页面导出excel
public void export(HttpServletResponse response) throws Exception {
ExcelWriter excelWriter = null;
try {
String fileName = URLEncoder.encode("测试EasyExcel" + System.currentTimeMillis() + ".xlsx", "utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
excelWriter = EasyExcel.write(response.getOutputStream()).build();
// 第一个sheet
WriteSheet sheet0 = EasyExcel.writerSheet(0, "Report模板").head(ExcelReport.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
for (int i = 0; i < 10; i++) {
List<ExcelReport> sheet1Data = sheet0Data();
excelWriter.write(sheet1Data, sheet0);
}
// 第二个sheet
WriteSheet sheet1 = EasyExcel.writerSheet(1, "Order模板").head(ExcelOrder.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
for (int i = 0; i < 10; i++) {
List<ExcelOrder> sheet1Data = sheet1Data();
excelWriter.write(sheet1Data, sheet1);
}
} catch (Exception e) {
throw e;
} finally {
if (excelWriter != null) {
logger.info("执行完毕");
excelWriter.finish();
}
}
}
用2.2.6版本测试,如果实体类为内部类,会多出一列this$0,不采用内部类即可,不确定后续的版本有没有修复这个问题。