Java EasyExcel 读写 excel 解决 poi 读取大文件内存溢出问题

目录

问题

如何解决

使用示例

pom

监听器

实体类

读写方法

测试类

---

问题

以前项目使用 poi 读写 excel，但是 excel 中的数据量太大的话，用 poi 读取时就会导致 OOM 异常，这是因为 poi 在读取数据时，是将全部数据一次性都加载到内存中。

如何解决

使用EasyExcel，以下是使用示例。

使用示例

pom

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>

监听器

public class ProductListener extends AnalysisEventListener<Product> {

    private static final int BATCH_COUNT = 1000;

    private final List<Product> list = new ArrayList<>();

    private int totalCount;

    public ProductListener() {}

    @Override
    public void invoke(Product product, AnalysisContext analysisContext) {
        list.add(product);
        if(list.size() >= BATCH_COUNT){
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        System.out.printf("数据同步完成,总数量为:%s%n",totalCount);
    }


    public void saveData(){
        if(!list.isEmpty()){
            for (Product product : list) {
                insertIgnore(product);
            }
        }
    }

    public void insertIgnore(Product product) {
        try {
            // 执行数据库操作
            System.out.println(product.getName());
            ++totalCount;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

实体类

public class Product {

    private Long id;

    @ExcelProperty("name")
    private String name;

    @ExcelProperty("quantity")
    private Long quantity;

    @ExcelProperty("desc")
    private String desc;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getQuantity() {
        return quantity;
    }

    public void setQuantity(Long quantity) {
        this.quantity = quantity;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }
}

读写方法

public class MyEasyExcel {

    public static void read(String filePath) {

        try (InputStream inputStream = Files.newInputStream(Paths.get(filePath))) {
            EasyExcel.read(inputStream, Product.class, new ProductListener())
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void write(List<Product> data, String filePath) {

        try (OutputStream outputStream = Files.newOutputStream(Paths.get(filePath))) {
            ExcelWriterBuilder writerBuilder = EasyExcel.write(outputStream, Product.class);
            writerBuilder.sheet("Data").doWrite(data);
        } catch (Exception e) {
            // 处理异常
        }
    }
}

测试类

class MyEasyExcelTest {

    @Test
    void read() {
        String filePath = "C:\\Users\\XXX\\Desktop\\excel.xlsx";
        MyEasyExcel.read(filePath);
    }

    @Test
    void write() {
        List<Product> products = new ArrayList<>();
        Product product = new Product();
        product.setId(1L);
        product.setName("qwe");
        product.setDesc("abc");
        product.setQuantity(1342L);
        products.add(product);

        String filePath = "C:\\Users\\XXX\\Desktop\\excel.xlsx";

        MyEasyExcel.write(products, filePath);
    }
}