Shulou(Shulou.com)06/03 Report--

This article will explain in detail how Java solves the memory overflow problem by exporting super-large Excel files. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Using SXSSFWorkbook in Poi

When implementing excel export, memory overflow is always easy to occur when the amount of data is too large. You can use the SXSSFWorkbook class provided by POI to avoid memory overflows.

Introduction of Poi org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml into 2.maven 4.1.2 org.apache.poi poi-ooxml-schemas 4.1.2 3. Testing process

First use the normal writing test (XSSFWorkbook), write the writeNormalExcelTest test method, if too many rows are written, memory overflow will be reported (in the case of setting-server-Xmx64m-Xms64m-Xmn32m).

Then write a test for SXSSFWorkbook to operate excel, and the test method writeHugeExcelTest (also in the case of setting-server-Xmx64m-Xms64m-Xmn32m). The results show that there is no memory overflow and can export 1000000 lines of test data intact. The whole Java class code is as follows:

4. Unit test Java code package cn.gzsendi.exceltest;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.junit.Test;public class HugeExcelExportTest {private int totalRowNumber = 1000000 / / the number of excel rows written is private int totalCellNumber = 40; / / excel has 40 columns per row / / the normal method of writing excel consumes memory. If the number of rows written is too large, memory overflow @ Test public void writeNormalExcelTest () {Workbook wb = null; FileOutputStream out = null will be reported. Try {long startTime = System.currentTimeMillis (); wb = new XSSFWorkbook (); Sheet sheet = wb.createSheet ("Sheet 1"); / / define Row and Cell variables, starting with 0. Row row; Cell cell; for (int rowNumber = 0; rowNumber < totalRowNumber; rowNumber++) {row = sheet.createRow (rowNumber); for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {cell = row.createCell (cellNumber); cell.setCellValue (Math.random ()) / / write a random number} / / print test, if (rowNumber% 10000 = = 0) {System.out.println (rowNumber) }} / / Write excel to a file out = new FileOutputStream ("d:\\ temp\\ normalExcel_" + totalRowNumber + ".xlsx"); wb.write (out); long endTime = System.currentTimeMillis () System.out.println ("process" + totalRowNumber + "spent time:" + (endTime-startTime) + "ms.");} catch (Exception e) {e.printStackTrace () } finally {try {if (out! = null) out.close ();} catch (IOException e) {e.printStackTrace () } try {if (wb! = null) wb.close ();} catch (IOException e) {e.printStackTrace () } / / write to excel with temporary file compression, etc. By default, if more than 100 lines are written to the temporary file, memory overflow @ Test public void writeHugeExcelTest () {SXSSFWorkbook wb = null; FileOutputStream out = null will not be reported. Try {long startTime = System.currentTimeMillis (); wb = new SXSSFWorkbook (); / / by default, 100 lines will be written to the temporary file wb.setCompressTempFiles (false) / / whether to compress the temporary file, otherwise the write speed is faster, but it takes up more disk, but the program finally deletes the temporary file Sheet sheet = wb.createSheet ("Sheet 1"); / / defines the Row and Cell variables, Rows starts at 0. Row row; Cell cell; for (int rowNumber = 0; rowNumber < totalRowNumber; rowNumber++) {row = sheet.createRow (rowNumber); for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {cell = row.createCell (cellNumber) Cell.setCellValue (Math.random ()); / / write a random number} / / print test, if (rowNumber% 10000 = = 0) {System.out.println (rowNumber) }} / / Write excel to a file out = new FileOutputStream ("d:\\ temp\\ hugeExcel_" + totalRowNumber + ".xlsx"); wb.write (out); long endTime = System.currentTimeMillis () System.out.println ("process" + totalRowNumber + "spent time:" + (endTime-startTime) + "ms.")} catch (Exception ex) {ex.printStackTrace () } finally {if (wb! = null) {wb.dispose (); / / it is important to delete temporary files, otherwise the disk may be full} try {if (out! = null) out.close () } catch (IOException e) {e.printStackTrace ();} try {if (wb! = null) wb.close () } catch (IOException e) {e.printStackTrace () This is the end of the article on "how Java solves the memory overflow problem by exporting very large Excel files". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.