In the actual application will often need to export data into Excel, exported in addition to the original is exported, pagination sheet export and large amount of data export. For excel2003 version, limit the maximum number of rows and columns per sheet, the large amount of data export will default time multiple sheet, and excel2007 does not appear such a problem. These export methods have different interfaces in the Java program to implement each:
1. Export AS-is
Export is not preview direct export Excel
Its program interface code is as follows:
OutputStream = new FileOutputStream (New File ("E:\\excelexport.xls"));
Excelexporter Excel = new Excelexporter ();
Export version 2007 OutputStream = new FileOutputStream (New File ("e:\\excelexport.xlsx")); Excel excel2007exporter Excel = new Excel2007exporter ();
Effect Chart:
2. Pagination Export
Page export, if the report template is paginated, it will be exported as a result of pagination, and duplicate headings will also be duplicated.
Its program interface code is as follows:
OutputStream = new FileOutputStream (New File ("E:\\pageexcelexport.xls"));
Pageexcelexporter page = new Pageexcelexporter (Reportutils.getpapersettinglistfromworkbook (Rworkbook));
Export version 2007 OutputStream = new FileOutputStream (New File ("e:\\pageexcelexport.xlsx")); Excel pageexcel2007exporter page = new Pageexcel2007exporter (Reportutils.getpapersettinglistfromworkbook (Rworkbook) );
Effect Chart:
3, pagination sheet export
Each page of a report result when sheet export is saved in an Excel file for a sheet
The code is as follows:
OutputStream = new FileOutputStream (New File ("E:\\pagesheetexcelexport.xls"));
Pagetosheetexcelexporter sheet = new Pagetosheetexcelexporter (Reportutils.getpapersettinglistfromworkbook ( Rworkbook));
Export version 2007 OutputStream = new FileOutputStream (New File ("e:\\pagesheetexcelexport.xlsx")); Excel pagetosheetexcel2007exporter sheet = new Pagetosheetexcel2007exporter ( Reportutils.getpapersettinglistfromworkbook (Rworkbook));
Effect Chart:
4, large amount of data export
This is more common, for example, with 50000 behavior an Excel file
The code is as follows:
OutputStream = new FileOutputStream (New File ("E:\\largeexcelexport.zip"));
Largedatapageexcelexporter large = new Largedatapageexcelexporter (Reportutils.getpapersettinglistfromworkbook ( Rworkbook), true);
Export version 2007 OutputStream = new FileOutputStream (New File ("e:\\largeexcelexport.xlsx")); Excel largedatapageexcel2007exporter large = new Largedatapageexcel2007exporter ( Reportutils.getpapersettinglistfromworkbook (Rworkbook), true);
When you export a report for large amounts of data, the following two scenarios may occur:
First, the export is successful, the data will be displayed in multiple sheet;
Two, two, export failed, the exported Excel content is invalid. There are two main reasons for this situation:
4.1 Large data report export without a line engine enabled
In the absence of a row-style engine enabled, there are two scenarios: the server can withstand the export of success, the server cannot afford to export failure, below we look at the results:
4.1.1 Server to withstand range
Can withstand the range is: large data, more than the maximum number of Excel rows but not very very large, such as 100,000, and the data is not complex, the memory is sufficient and the network is not broken can be exported normally. When you export, data that exceeds the maximum row or column in Excel is displayed in the next sheet. However, the number of rows and columns in Excel is limited, such as Excel2003 with a maximum limit of 65536 rows and a maximum number of columns to 256 columns.
4.1.2 Server cannot withstand
Unbearable scope is mainly the large amount of data, and very large, such as hundreds Guibaivan even more, may be due to the limitations of memory and other export failure. When you export the results, open the export failed Excel, whose contents are not valid.
For both of these export results, the main reason is the amount of data and data complexity (number of columns) is how large, without the use of row engine there will be a critical point, less than the critical point when you can export success is exported content has multiple sheet, or export failed, the content is invalid. Therefore, for large data-volume reports, the row-style engine is typically used for presentation, in which case exporting Excel always succeeds. When you export Excel to a large data volume report that enables the line engine, you divide it into multiple. xls files, which are transferred to the client in the format of a compressed package file.
4.2 Large Data volume report export with line engine enabled
For large data-volume reports, the row engine is typically used to show, in which case exporting Excel always succeeds. Because large data reports that enable line engines export Excel, they are divided into. xls files that are transferred to the client in the format of a compressed package file.
For example, a large data report has 100w rows of data, and a row engine is set, showing 30 rows per page. The results of the export will be 41 Excel, the first 40 Excel each 24990 rows, the last Excel is 400 rows, and 41 Excel compresses the ZIP package to the client download.
The above content is for Java Finereport Report Tool export Excel Four ways, hope to help everyone!