Java Solution for POI export Excel memory Overflow

Java Solution for POI export Excel memory Overflow

In our use of Java development process, we often want to export the data obtained by the query, which is generally stored in Excel, so we export the data to use the Java POI Library, which is mainly for the various Windows Platform data format operation, here, We are working on Excel.

The process of generating Excel is like this, first of all, we query the database, get the corresponding result set, is generally a list collection, and then generate the workbook object, according to the generated workbook object gets the sheet object, according to this sheet object to get the Row object, Then we get the cell object according to the row object, finally set the cell cell value, loop to build the row and cell object, write the value in the list to the cell, write the workbook object to the corresponding stream object.

In the above process, if the list does not have a lot of data, there will be no problem, if the list of data enough (more than tens of thousands of), it will cause memory overflow, the export failed.

The solution is that we export it in batches, each time we query a fixed number of list data sets, write the data into a Workbook object, loop through it, and then write all of the data as workbook, which generates multiple workbook objects. There is also a problem when it comes to downloading the browser, so we will also pack and compress the generated multiple Excel files, that is, the tar command that invokes the operating system (which is typically deployed in a Linux environment) to generate a compressed package that can be downloaded.

Note: In the OutputStream stream where the Workbook object is written, its path cannot be a path in the Web container when the OutputStream is built, otherwise a workbook object can never be generated for unknown reasons

Java Solution for POI export Excel memory Overflow