How to export millions of data using POI in JAVA

How to export millions of data using POI in JAVA

Excel users often know that excel2007 and later versions can easily store millions of data records, however, how to import a large amount of data in the system to excel quickly and accurately seems to be a difficult problem. For general web systems, we basically use entry-level web Server tomcat to solve the cost, jdk 32 supports no more than 2 GB of memory, but there is no limit on the 64-bit system. However, in a 64-bit system, the performance is not very good, so in order to solve the appeal problem, we need to solve the problem for our code. After POI3.8, a new class was added, SXSSFWorkbook, which can control the memory occupied by excel Data, it manages resources by controlling the number of rows in the memory. When the number of set rows is exceeded, it will automatically refresh the memory and write data as files. But some people will say that I have used this class, and it seems that it cannot be completely solved. When the data volume exceeds a certain amount, the memory will still overflow, and it will take a long time. I only used this class for you, but you didn't design it for your needs. It was just for use. So the question I want to talk about next is, if you use SXSSFWorkbook and the corresponding write design, you can quickly write millions of data.

Let me give you an example. We used to query a large amount of data in our database. What should we do? This is the case when we did not design it. Write a set, execute jdbc, assign the returned result to the list, and then return it to the page, however, when the data volume is large, the data cannot be returned and the memory overflows. Therefore, we display the data one page by one in a limited time and space, this can avoid the memory usage of large data volumes and improve user experience. The reason is that the amount of data we want to export is also a sudden memory usage, we can limit the memory occupied by the exported data. We create a container which is a list with 10000 rows of storage space in the list. Each time we store 10000 rows, after it is used up, the content is cleared and reused, so that the memory can be effectively controlled, so our design idea is basically formed. Therefore, there are three steps to export paging data:

Calculate the number of rows of data to be exported in the database. Calculate the number of data extraction times based on the number of rows. Write the data to the file by the number of times.

The above steps have greatly improved the efficiency and user experience.

 

Public void exportAmountExcelData (ValueDataDto valueDataDto, String path) throws IOException {SXSSFWorkbook wb = new SXSSFWorkbook (15); Sheet sh = wb. createSheet (); Row row = sh. createRow (0); // ---------------------------------------------------- Cell cel0 = row. createCell (0); cel0.setCellValue ("1"); Cell cel2 = row. createCell (1); cel2.setCellValue ("2"); Cell cel3 = row. createCell (2); cel3.setCellValue ("3"); Cell cel4 = row. createCell (3); // ------------------ define the header ------------------------------------------ List
 
  
List = new ArrayList
  
   
(); Int page_size = 10000; // number of data rows stored in the database int list_count = this. daoUtils. queryListCount (this. valueDataDao. queryExportSQL (valueDataDto ). get ("count_ SQL"); int export_times = list_count % page_size> 0? List_count/page_size + 1: list_count/page_size; for (int j = 0; j <export_times; j ++) {list = this. valueDataDao. queryPageList (this. valueDataDao. queryExportSQL (valueDataDto ). get ("list_ SQL"), j + 1, page_size); int len = list. size () <page_size? List. size (): page_size; for (int I = 0; I <len; I ++) {Row row_value = sh. createRow (j * page_size + I + 1); Cell cel0_value = row_value.createCell (0); cel0_value.setCellValue (list. get (I ). getaa (); Cell cel2_value = row_value.createCell (1); cel2_value.setCellValue (list. get (I ). getaa (); Cell cel3_value = row_value.createCell (2); cel3_value.setCellValue (list. get (I ). getaa_person ();} list. clear ();} FileOutputStream fileOut = new FileOutputStream (path); wb. write (fileOut); fileOut. close (); wb. dispose ();}