欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

EasyExcel导出excel使用教程

程序员文章站 2024-03-20 14:08:04
...

最近了解到这个工具,阿里开源的,其实官方示例也演示的很清楚了,我只是做了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;

}