Shulou(Shulou.com)06/03 Report--

In practical applications, it is often necessary to export data to excel. In addition to original export, there are paging export, paging sheet export and large amount of data export. For excel version 2003, due to the limit on the maximum number of rows and columns per sheet, there will be multiple sheet by default when exporting large amounts of data, while excel2007 will not have such a problem. These export methods are implemented with different interfaces in JAVA programs:

1. Export as is

To export as is is to export excel directly without preview.

The program interface code is as follows:

OutputStream = new FileOutputStream ("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)

Effect picture:

2. Paging export

Paged export, if the report template is paged, it will be exported in the form of paged results, and duplicate headings will be repeated.

The program interface code is as follows:

OutputStream = new FileOutputStream ("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)

Effect picture:

3. Paging and sheet export

Paging each page of the report result when Sheet is exported is saved as a Sheet in the Excel file

The code is as follows:

OutputStream = new FileOutputStream ("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)

Effect picture:

4. Export a large amount of data

This is quite common, for example, to act as an excel file with 50000

The code is as follows:

OutputStream = new FileOutputStream ("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)

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

If the export is successful, the data will be displayed in multiple Sheet

The Excel content is not valid. There are two main reasons for this:

Large data report export without row engine enabled

If the row engine is not enabled, there will also be two situations: the export succeeds if the server can afford it, and the export fails if the server cannot bear it. Let's take a look at the results:

4.1.1 within the scope of server tolerance

The tolerable range is: a large amount of data, exceeding the maximum number of rows of EXCEL, but not very large, such as 100000, and the data is not complex, and it can be exported normally when there is enough memory and the network is not broken. When exporting, data that exceeds the maximum row or column of the Excel is displayed in the next Sheet. However, the number of rows and columns of Excel is limited, for example, the maximum number of rows of Excel2003 is 65536 rows and the maximum number of columns is 256columns.

4.1.2 the server cannot bear it

The unbearable range is mainly a large amount of data, and very large, such as hundreds of thousands, millions or more, may fail to export due to memory and other limitations. When you open the Excel that failed to export, the content of the exported result is invalid.

For these two kinds of export results, the main reason is that the amount of data and the complexity of the data (the number of columns), there will be a critical point without using the row engine, below the critical point, the content can be exported successfully, that is, there are multiple sheet, otherwise the export fails and the content is invalid. Therefore, for reports with large amounts of data, row engines are usually used to present them, in which case the export of Excel is always successful. When a large data volume report of a row engine is enabled to export Excel, it is divided into .xls files and transferred to the client in the form of a compressed package file.

4.2 enable large data report export of row engine

For reports with large amounts of data, the row engine is usually used to display, in which case the export of the Excel is always successful. Because when a large data volume report with row engine enabled exports Excel, it is divided into multiple .xls files and transferred to the client in the format of a compressed package file.

For example, a large data volume report has 100w rows of data, and a row engine is set up to display 30 rows per page. The result of the export will be 41 Excel, 24990 lines each for the first 40 Excel, 400 lines for the last Excel, and 41 Excel compressed as zip packages to be downloaded to the client.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.