This issue recommends an Ali open source Java-based Excel parsing tool——EasyExcel。

Java parsing, the more famous framework has Apache poi, jxl, but they all have a serious problem is memory consumption, poi has a special SAX pattern can solve some memory problems to a certain extent, but poi still has some defects, For example, some versions of Excel decompression and storage after decompression are completed in memory, memory is still a lot of consumption. easyexcel rewrites the poi analysis of Excel, a 3M Excel file using poi analysis still needs about 100M memory, after switching to easyexcel can be reduced to a few M, no matter how large excel will not appear memory calls.

latest version

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>easyexcel</artifactId>

<version>3.0.5</version>

</dependency>

give a typical example

• read Excel

object

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

audio monitor

// There's a very important point DemoDataListener Cannot be managed by spring，You want to call new every time you read excel,And then you use spring inside and you can pass constructors in
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {

    /**
     * Every 5 pieces of storage database, in actual use can be 100, and then clear the list, convenient memory recycling
     */
    private static final int BATCH_COUNT = 100;
    /**
     * Cached data
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * Let's say this is a DAO, and of course there's business logic and this could also be a service. Of course it's useless if you don't have to store this object.
     */
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // This is a demo, so just make a new one. If you are in spring, use the following parameterized constructor
        demoDAO = new DemoDAO();
    }

    /**
     * If spring is used, use this constructor. Each time you create a Listener, you need to pass in a Spring-managed class
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * This will be called for every piece of data parsing
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("Parse a piece of data:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // When BATCH_COUNT is reached, the database needs to be stored once to prevent tens of thousands of data from being stored in memory, which is easy to OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // Memory completion cleanup list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * It's called when all the data is parsed
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // This is also where data is saved to ensure that the last remaining data is also stored in the database
        saveData();
        log.info("All data analysis completed！");
    }

    /**
     * Plus storage database
     */
    private void saveData() {
        log.info("{}Start to store the database！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("Save database successfully！");
    }
}

persistent layer

/**
 * Let's say this is your DAO store. Of course you need this class for spring to manage, of course you don't need storage, and you don't need this class.
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // If it is mybatis, try not to call insert directly many times, write a new mapper method batchInsert, all the data is inserted at once
    }
}

The simplest read sample code

    /**
     * The easiest read
     * <p>
     * 1. To create an entity object corresponding to excel, go to {@link DemoData}
     * <p>
     * 2. Since excel is read line by line by default, you need to create a callback listener for excel line by line, see {@link DemoDataListener}
     * <p>
     * 3. Just read
     */
    @Test
    public void simpleRead() {
        // Script 1: JDK8+, no need to write an additional DemoDataListener
        // since: 3.0.0-beta1
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // Here you need to specify which class to read, and then read the first sheet file stream will automatically close
        // So you're going to read 3000 pieces of data at a time and then you're going to go back and you're just going to use the data
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                log.info("A piece of data was read{}", JSON.toJSONString(demoData));
            }
        })).sheet().doRead();

        // Writing Method 2:
        // Anonymous inner classes don't have to write an extra one DemoDataListener
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // Here you need to specify which class to read, and then read the first sheet file stream will automatically close
        EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
            /**
             * The amount of data cached at a time
             */
            public static final int BATCH_COUNT = 100;
            /**
             *temporary storage
             */
            private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(DemoData data, AnalysisContext context) {
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // Memory completion cleanup list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                saveData();
            }

            /**
             * Plus storage database
             */
            private void saveData() {
                log.info("{}Start to store the database！", cachedDataList.size());
                log.info("Save database successfully！");
            }
        }).sheet().doRead();

        // There's a very important point DemoDataListener It can't be managed by spring, you need to get new every time you read excel, and then you can use spring to build methods into it
        // Writing Method 3：
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // Here you need to specify which class to read, and then read the first sheet file stream will automatically close
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

        // Writing Method 4：
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // File by file reader
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
            // Build a sheet here you can specify a name or no
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            // Read one sheet
            excelReader.read(readSheet);
        } finally {
            if (excelReader != null) {
                // Don't forget to close it here, it will create a temporary file while reading, and the disk will crash
                excelReader.finish();
            }
        }
    }

64M内存20秒读取75M(46W行25列)的Excel：

Of course, the quick mode is faster, but the memory usage is a little more than 100M.

You can read more on your own.