Java reporting tools Finereport Four ways to export Excel

in real-world applications, it is often necessary to export data into Excel , the exported method is exported in addition to the original, pagination export, pagination sheet export and large data volume export. For version excel2003, the maximum number of rows and columns per sheet is limited , and when large data is exported it will default to multiple sheet excel2007 There is no such problem. These export methods are implemented in Java programs with different interfaces:

1. Export AS-is

Export as is without previewing 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 (); Excel.export (OutputStream, Rworkbook);

：

2. Paging Export

Page export, if the report template is paginated, it will be exported according to the results of pagination, duplicate headings and so on will be repeated.

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)); Page.export (OutputStream, Rworkbook);

：

3, pagination sub-sheet export

Sub-page points Sheet each page of the report results when exporting is a Sheet saved in Excel in the file

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)); Sheet.export (OutputStream, Rworkbook);

：

4. Large Data Volume export

This is more common, for example with 50000 behavior of a 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); Large.export (OutputStream, Rworkbook);

There are two things you might see when you export a report with a large volume of data:

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 the above situation:

4.1 Large Data volume report export with no line engine enabled

In the absence of a row engine enabled, there are two cases: the server can withstand the export success, the server cannot afford to export failure, the following we see the results:

4.1.1 Server Withstand Range

Can withstand the scope is: large amount of data, more than the maximum number of Excel lines but not very large, such as 100,000, and the data is not complex, in memory enough and the network does not break the situation can be exported normally. When exporting, data that exceeds the maximum row or column of Excel is displayed in the next sheet. However, there is a limit to the number of rows and columns in Excel, such as Excel2003 with a maximum of 65536 rows and a maximum of 256 columns.

4.1.2 Server cannot withstand

The amount of data that cannot be sustained is primarily large, and very large, such as hundreds stadiums or even more, and may fail to export due to memory limitations. While exporting the results, open the export failed Excel, whose contents are not valid.

For these two export results, the main reason is the amount of data and data complexity (number of columns) how much, in the absence of a row engine, there will be a critical point, below the critical point when you can export the success of the exported content has more than sheet, otherwise the export failed, the content is invalid. Therefore, for large data volumes, it is common to use the row engine to represent, in which case the export Excel is always successful. When you export excel in a large data volume report that has the row engine enabled, it is split into multiple. xls files and transferred to the client in a compressed package file format.

4.2 Large Data volume report export with line engine enabled

For large data volume reports, it is common to use a row engine to present, in which case the export Excel is always successful. Because a large data volume report that enables the row engine is exported to Excel, it is split into multiple. xls files and transferred to the client in a compressed package file format.

For example, a large data volume report has 100w rows of data, and a row engine that displays 30 rows per page. The result of the export is 41 Excel, the first 40 Excel each 24990 lines, the last Excel 400 lines, and 41 Excel compression for the ZIP package to the client download.

Java reporting tools Finereport Four ways to export Excel