Java-使用easyexcel读大文件

喜欢ヅ旅行 2022-05-08 23:24 653阅读 0赞

使用easyexcel读大excel文件，避免OOM错误。

依赖：

compile group: 'com.alibaba', name: 'easyexcel', version: '1.0.4'

代码：

package com.tsaoko.sched.service.task;
    
    import com.alibaba.excel.ExcelReader;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.read.context.AnalysisContext;
    import com.alibaba.excel.read.event.AnalysisEventListener;
    import com.alibaba.excel.support.ExcelTypeEnum;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author Walker
     * @since 1.0 2018-09-28
     */
    public class EasyExcelTest {
    
        public static void main(String[] args) {
            read();
           //write();
        }
    
        private static void read() {
            try(InputStream inputStream = new FileInputStream("C:\\shopify stores.xlsx")) {
                ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, new AnalysisEventListener<List<String>>() {
    
                    @Override
                    public void invoke(List<String> object, AnalysisContext context) {
                        System.out.println("当前sheet:" + context.getCurrentSheet().getSheetNo() + ",当前行:" +
                                context.getCurrentRowNum());
                    }
    
                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {
    
                    }
                });
                excelReader.read();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        private static void write() {
            try(OutputStream outputStream = new FileOutputStream("C:\\outputtest.xlsx")) {
                ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
                Sheet sheet = new Sheet(1, 0, ExcelPropertyIndexModel.class);
                writer.write(getData(), sheet);
                writer.finish();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        private static List<ExcelPropertyIndexModel> getData() {
            List<ExcelPropertyIndexModel> list = new ArrayList<>();
            list.add(new ExcelPropertyIndexModel("t1"));
            list.add(new ExcelPropertyIndexModel("t2"));
            list.add(new ExcelPropertyIndexModel("t3"));
            list.add(new ExcelPropertyIndexModel("t4"));
            list.add(new ExcelPropertyIndexModel("t5"));
            return list;
        }
    
    }

**虽然读大文件没有报OOM错误，但读取过程比较慢，可以换个思路，将excel文件转换为csv格式，再根据需要将大文件分隔成小文件，处理后合并结果。**

> [https://github.com/alibaba/easyexcel][https_github.com_alibaba_easyexcel]

[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel