快捷導(dǎo)航

Java EasyExcel讀寫excel如何解決poi讀取大文件內(nèi)存溢出問題

更新時間：2024年06月17日 10:48:44 作者：編程經(jīng)驗分享

這篇文章主要介紹了Java EasyExcel讀寫excel如何解決poi讀取大文件內(nèi)存溢出問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

問題

以前項目使用 poi 讀寫 excel，但是 excel 中的數(shù)據(jù)量太大的話，用 poi 讀取時就會導(dǎo)致 OOM 異常，這是因為 poi 在讀取數(shù)據(jù)時，是將全部數(shù)據(jù)一次性都加載到內(nèi)存中。

如何解決

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

使用示例

pom

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

監(jiān)聽器

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("數(shù)據(jù)同步完成,總數(shù)量為:%s%n",totalCount);
    }


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

    public void insertIgnore(Product product) {
        try {
            // 執(zhí)行數(shù)據(jù)庫操作
            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);
    }
}