Four ways of exporting excel with Java finereport report report tool

In practical applications, it is often necessary to export data into excel. In addition to exporting as is, there are also paging export, paging sheet export and large amount of data export. For Excel 2003, the maximum number of rows and columns of each sheet is limited. When exporting large amounts of data, multiple sheets will be divided by default, which will not occur in Excel 2007. These export methods have different interfaces in Java programs:

1. Export as is

Exporting as is means exporting excel directly without preview

The program interface code is as follows:

design sketch:

2. Paging export

Pagination export: if the report template is paginated, it will be exported in the form of paginated results, and duplicate titles will also be repeated.

The program interface code is as follows:

design sketch:

3. Paging sheet export

When exporting by sheet, each page of report results is saved in Excel file as a sheet

Its code is as follows:

design sketch:

4. Large data export

This is more common, for example, an excel file with 50000 lines

Its code is as follows:

When exporting a report with a large amount of data, the following two situations may occur:

1、 If the export is successful, the data will be displayed in multiple sheets;

2、 2. Export failed. The exported excel content is invalid. There are two main reasons for the above situation:

4.1 large data report export without line engine

When the line engine is not enabled, there are two situations: the export succeeds when the server can afford it, and the export fails when the server cannot afford it. Let's see the results below:

4.1. 1. Within the scope of the server

The acceptable range is: the amount of data is large, exceeding the maximum number of lines in Excel, but not very, very large, such as 100000, and the data is not complex. It can be exported normally when the memory is enough and the network is not disconnected. When exporting, data exceeding the maximum row or column in Excel will be displayed in the next sheet. However, the number of rows and columns in excel is limited. For example, Excel 2003 is limited to 65536 rows and 256 columns.

4.1. 2. The server cannot afford it

The unbearable range is mainly due to the large amount of data, such as tens of millions or even more, which may fail to export due to memory constraints. When exporting the results, open the excel that failed to export, and its content is invalid.

For both export results, The main reason lies in the amount and complexity of data (number of columns) how big it is, there will be a critical point when the row engine is not used. If it is lower than the critical point, it can be exported successfully, that is, the exported content has multiple sheets, otherwise the export fails and the content is invalid. Therefore, for large data reports, the row engine is usually used to display them. In this case, the export of Excel is always successful. When the row engine is enabled, the large number When exporting a quantity report to excel, it is divided into multiple Xls file, which is transmitted to the client in the form of a compressed package file.

4.2 large data report export with line engine enabled

For reports with large amount of data, the line engine is usually used to display them. In this case, exporting excel is always successful. Because when exporting large data reports with line engine to excel, they are divided into multiple Xls file, which is transmitted to the client in the form of a compressed package file.

For example, a large data report has 100W rows of data, and a line engine is set to display 30 rows per page. The exported result will be 41 excel, with 24990 rows for each of the first 40 excel and 400 rows for the last excel. The 41 excel will be compressed into a zip package and sent to the client for download.

The above content is aimed at the four ways of exporting excel from Java finereport report report tool. I hope it will be helpful to you!