background

Today I needed to write an exported Excel function, but I found that when the data volume reached 30,000 pieces and the number of columns was 23, the memory ran out and the CPU used 100%, so the test environment was directly exploded. In a local test, we found that the heap memory increased by about 500M when exporting 3000 data items. Then I discovered the SXSSFWorkbook class.

Introduction to the

The XSSFWorkbook class (Excel2007) is an XSSFWorkbook class that uses hard disk space to significantly reduce heap memory footprint. Create a temporary file in the temporary folder directory of the system, and store all data exceeding the specified number of lines in the temporary file instead of the memory. The memory stores only the latest data of the specified number of lines in the temporary file. In this way, hard disk space can be exchanged for memory space to avoid memory overflow

use

There is no difference from the normal Excel export method, except that the instantiated class is replaced with SXSSFWorkbook

SXSSFWorkbook workbook = null; OutputStream outputStream = null; try { outputStream = response.getOutputStream(); // Create workbook workbook = new SXSSFWorkbook(); / / open the compression function To prevent to take up too much disk workbook. SetCompressTempFiles (true); // Create a Sheet Sheet Sheet Sheet = workbook.createsheet ("The name of the table"); // Create a Row titleRow = sheet.createrow (0); // Create a Cell Cell Cell = titlerow.createcell (0); // Assign cell.setcellValue ("Content"); // write the workbook to the outputStream workbook.write(outputStream); } catch (Exception e) { e.printStackTrace(); }finally {if(workbook ! // Dispose of workbook.dispose(); }if (outputStream != null) {
					outputStream.close();
					
				}


			}

Copy the code

Pay attention to the point

  • Best open compression mode workbook. SetCompressTempFiles (true); This can greatly reduce the size of temporary files

  • Dispose workbook.dispose() after use; Prevents temporary files from bursting hard disk all the time