EasyExcel导出excel使用教程

最近了解到这个工具，阿里开源的，其实官方示例也演示的很清楚了，我只是做了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，不采用内部类即可，不确定后续的版本有没有修复这个问题。