Java EasyExcel export report memory overflow solution

Common methods for exporting large data volumes in EasyExcel

1. Write in batches

EasyExcel supports batch writing of data, which can load data into memory in batches and write to Excel files in batches, avoiding loading large amounts of data into memory at one time.
Sample code：

     String fileName = "large_data.xlsx";
     ExcelWriter excelWriter = (fileName).build();
     WriteSheet writeSheet = ("Sheet1").build();

     // Suppose 10,000 pieces of data are written each time     int batchSize = 10000;
     List&lt;Data&gt; dataList;
     int pageIndex = 0;
     do {
         // Get data by paging         dataList = getDataByPage(pageIndex++, batchSize);
         (dataList, writeSheet);
     } while (() == batchSize);

     // Close the resource     ();

2. Set up the appropriate JVM memory

For big data export scenarios, you can try to increase the memory allocation of the JVM, for example:

java -Xms512M -Xmx4G -jar

explain：
- -Xms512M: Set the initial heap size to 512MB.
- -Xmx4G: Set the maximum heap size to 4GB.

3. Reduce the complexity of data objects

When exporting data, try to simplify data objects as much as possible, avoid unnecessary nesting and loading of extra fields, so as to reduce the memory space occupied by the object.

4. Turn off automatic column width setting

EasyExcel's automatic column width function will occupy a lot of memory, especially when the amount of data is large. Turning off automatic column width can save memory.
Sample code：

     (fileName)
             .registerWriteHandler(new SimpleWriteHandler()) // No automatic column width             .sheet("Sheet1")
             .doWrite(dataList);

5. Export using Stream (suitable for big data)

useOutputStreamWrite data in batches to reduce memory consumption. passBufferedOutputStreamIt can further improve performance.
Sample code：

     try (OutputStream out = new BufferedOutputStream(new FileOutputStream(fileName))) {
         ExcelWriter excelWriter = (out).build();
         WriteSheet writeSheet = ("Sheet1").build();
         int pageIndex = 0;
         List<Data> dataList;
         do {
             dataList = getDataByPage(pageIndex++, batchSize);
             (dataList, writeSheet);
         } while (() == batchSize);
         ();
     } catch (IOException e) {
         ();
     }

6. Select the right data export tool

If the data volume is very large, you can consider switching to export tools that support higher performance (such as Apache POI).SXSSFWorkbook), suitable for exporting million-level data volumes, but configuration and use will be more complicated.

The highlight is here, so how do you use POI's SXSSFWorkbook to export million-level data?

Apache POI's SXSSFWorkbook implements export cases of millions of data

Using Apache POISXSSFWorkbookIt can handle the Excel export of large data volumes becauseSXSSFWorkbookBased on streaming writing, not all data is loaded into memory, but temporary files are used for cache, which can significantly reduce memory consumption and is suitable for exporting millions of data. Let's take a look at a complete implementation example.

The code is as follows

import .*;
import ;

import ;
import ;
import ;
import ;

public class LargeDataExportExample {

    public static void main(String[] args) {
        // File output path        String filePath = "vg_large_data_export.xlsx";
        
        // Export millions of data        exportLargeData(filePath);
    }

    private static void exportLargeData(String filePath) {
        // Batch size per write        final int batchSize = 10000;
        // Total number of data        final int totalRows = 1_000_000;

        // Create an SXSSFWorkbook object, only 100 lines are retained in memory, and the excess will be written to the temporary file        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        (true); // Enable temporary file compression
        // Create worksheets        Sheet sheet = ("Large Data");

        // Create a title line        Row headerRow = (0);
        String[] headers = {"ID", "Name", "Age"};
        for (int i = 0; i &lt; ; i++) {
            Cell cell = (i);
            (headers[i]);
        }

        int rowNum = 1; // The row number at which the data starts
        try {
            // Write data by batch            for (int i = 0; i &lt; totalRows / batchSize; i++) {
                // Simulate to obtain each batch of data                List&lt;Data&gt; dataList = getDataBatch(rowNum, batchSize);
                
                // Write data to Excel                for (Data data : dataList) {
                    Row row = (rowNum++);
                    (0).setCellValue(());
                    (1).setCellValue(());
                    (2).setCellValue(());
                }

                // After processing a batch of data, you can choose to clear cached data to prevent memory overflow                ((SXSSFSheet) sheet).flushRows(batchSize); // Clear the written line cache            }

            // Write data to a file            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                (fos);
            }
            ("Data export completed:" + filePath);

        } catch (IOException e) {
            ();
        } finally {
            // Close the workbook and delete temporary files            ();
        }
    }

    /**
      * Simulate paging to get data
      */
    private static List&lt;Data&gt; getDataBatch(int startId, int batchSize) {
        List&lt;Data&gt; dataList = new ArrayList&lt;&gt;(batchSize);
        for (int i = 0; i &lt; batchSize; i++) {
            (new Data(startId + i, "Name" + (startId + i), 20 + (startId + i) % 50));
        }
        return dataList;
    }

    // Data class    static class Data {
        private final int id;
        private final String name;
        private final int age;

        public Data(int id, String name, int age) {
             = id;
             = name;
             = age;
        }

        public int getId() {
            return id;
        }

        public String getName() {
            return name;
        }

        public int getAge() {
            return age;
        }
    }
}

Let's explain the code

SXSSFWorkbook：SXSSFWorkbook(100)It means that up to 100 lines of data are retained in memory, and the excess will be written to temporary files to save memory.
Batch processing:passbatchSizeControls the amount of data written per batch to reduce memory consumption.totalRowsSetting to 1,000,000 means exporting 1 million pieces of data.
Simulated data generation：getDataBatchMethod simulates pagination to obtain data, returning a batch of data at a time.
Clear cache line: After each time a batch of data is written,flushRows(batchSize)Clear cached lines from memory to control memory usage.
Compress temporary files：(true)Enable temporary file compression to further reduce disk space usage.

Things to note

Temporary documents: SXSSFWorkbook will generate temporary files in the system temporary folder, and it is necessary to ensure that the disk space is sufficient.
Resource release: It needs to be called after completing the data writing()to clean temporary files.
Performance optimization: Can be adjusted according to machine memorybatchSizeandSXSSFWorkbookCache the number of lines to avoid frequent refreshes and memory overflows.

The above is the detailed content of the solution to the export of Java EasyExcel to report memory overflow. For more information about Java EasyExcel export memory overflow, please pay attention to my other related articles!