Monitor reports export Excel summary based on POI
Poi manipulating Excel objects
HSSF: Manipulating Excel (. xls) format
XSSF: Manipulating Excel 2007 OOXML (. xlsx) format to manipulate Excel memory usage above HSSF
SXSSF: Support from POI3.8 Beta3, based on XSSF, low memory footprint.
1. Principle of realization
Based on the Excel configuration template for report export, to achieve the export of Excel fields flexible configuration and format rich.
Template format
According to the template set by the program, the template is parsed first, the cell with the parameter (beginning with $p), the static data (without change) cell and the data column (beginning with the $f) are stored to the corresponding collection object. Then, based on the prepared Excel data, generate an Excel object, based on the set of parameter cells, static data cell collection and data column cell collection to fill the data, and finally the transformation of Excel into a stream of the form of response to the browser, provided to the user to download.
2. Monitor report Export Excel Course
Use POI HSSF object to generate Excel (. xls) format, database query does not paging, generated Excel is not compressed direct export.
Online problem: Load server forwarding request to application server blocking.
Database paging query, and in the server to the generated Excel compression after the export
Online problems: Unable to meet production requirements, configuration Framework-agent.jar, the list collection more than 100,000 exceptions, can not be exported.
use XSSF to operate Excel, increase the maximum number of bars, concurrent volume control
Online Problem: Memory overflow
Use the SXSSF object to manipulate Excel, optimize the program, check whether the file flow is turned off in extreme cases,
Online problem: No
3. Optimization Summary
When you export a report through a database query, the database is paged out, and the report is generated multiple times (not generating multiple Excel files or sheet pages, but appending to generating a sheet page).
If the page is not paged, once the tens of thousands of data, or even higher, a long time to occupy the database connection, resulting in a decrease in the amount of system concurrency. In addition, because too much data is loaded at one time (database query data is usually put into a Java set), a long time to occupy the virtual machine heap memory, until the completion of the production of Excel, the GC will be able to recycle this collection object, so high concurrency, it is easy to cause heap memory overflow. In the database paging query process, each page recommended in about 5000, the practice proved that the generation of Excel process, time is mainly spent on the database query, writing Excel is very fast, so if the number of pages per page is too small, to query multiple databases, database query is more time-consuming, This will take a long time to build excel overall.
If the system has large data volume report exports, consider using POI's SXSSF for Excel operations.
The HSSF-generated Excel (. xls) format itself has a limit of no more than 65,536 pages per sheet page.
XSSF generates Excel 2007 OOXML (. xlsx) format, the number of bars increases, but during the export process, the memory occupancy rate is higher than the HSSF.
SXSSF is an Excel object that is based on the low memory footprint provided by XSSF since the 3.8-BETA3 version. The principle is that you can set up or manually write an in-memory Excel row to your hard disk so that only a small number of Excel rows are saved in memory.
Export data volume, the server side to the export of Excel compression, and then into a binary stream, response to the browser
Excel's compression rate is particularly high, can reach the 80%,12m file compression only about 2M. If not compressed, will not only occupy user bandwidth, and will cause the load server (Apache) and the application server, a long time to occupy the connection (binary turnover), causing the load server request blocking, can not provide services.
If the application uses the Framework-agent.jar, try not to use the HSSF object to manipulate Excel
HSSF when you manipulate an Excel object, you use a list collection that holds all of the cell objects in Excel, and you should be aware that the number of Excel rows you are manipulating is not more than the number of columns that Framework-agent.jar sets.
Be sure to note that the file stream is closed
Open a stream file, you can not just want to normally shut down under normal circumstances, we must pay attention to all kinds of abnormal circumstances, the flow of files to shut down, otherwise it will easily lead to memory leaks. When you close a streaming file, it is recommended that you call the closing method and then place the file to null. Because it is found in use, after the internal file stream is closed, and then at the perimeter to determine whether the file stream object is empty, not for air-conditioning with the Close method, will still be executed. So to be foolproof, after you call the Close method, set it to null.
Prevent the foreground (page) from triggering the export Excel continuously
Export large data volume of Excel, in the background is more time-consuming, but also the consumption of IO resources, you should avoid users in the waiting process, triggering the export of Excel operation again, so after the trigger export Excel, Export button should be made unavailable state. (Once the export excel,tomcat is triggered, the export Excel thread does not stop because the user turns off the page or to another page, until the entire process finishes)
Export concurrent quantity limit and maximum limit of bar number
Export Excel procedures, IO Operations and looping write Excel consumption of operating system resources, so to limit the number of concurrency per instance export Excel, monitor report concurrency limit is 15, when more than 15 o'clock, the user will be prompted to reboot the export of Excel, which may load to other instances, For export.
The maximum number of bar limit is based on the business needs, make reasonable settings. Export of Excel, if the amount of data is too large, the export process takes a long time, occupy a lot of system resources, and lead out, the huge amount of data, open is more difficult, let alone the meaning of the view. Therefore, according to the user to provide the export parameters, to first detect the number of data, if the bar number is too large, direct to the user, to avoid in extreme situations or abnormal conditions, wasting system resources.
Monitoring reports through the above optimization, now export the report is basically stable, can support the maximum of 150,000 data export, no longer memory overflow, blocking response and so on.
I have a problem in doing, that is, if the data is empty, then it seems to have reported the exception of the null pointer, so it is a loop to give it a value, that is, this need to look at
