In actual applications, data is often needed to be exported into excel. In addition to exporting as-is, there are also pagination export, pagination sheet export and large data export. For excel2003, the maximum number of rows and columns of each sheet is limited. When exporting large data volumes, multiple sheets will be divided by default, and excel2007 will not have such problems. These export methods have different interfaces to implement in JAVA programs:
1. Export as is
Export as is just to export excel without previewing
The program interface code is as follows:
outputStream = new FileOutputStream(new File("E://ExcelExport.xls")); ExcelExporter excel = new ExcelExporter(); //Export 2007 outputStream = new FileOutputStream(new File("E://ExcelExport.xlsx")); excel Excel2007Exporter excel = new Excel2007Exporter(); excel.export(outputStream, rworkbook);Reproduction image:
2. Pagination Export
Pagination export. If the report template is paginated, it will be exported in the form of pagination results, and duplicate titles, etc. will also be repeated.
The program interface code is as follows:
outputStream = new FileOutputStream(new File("E://PageExcelExport.xls")); PageExcelExporter page = new PageExcelExporter(ReportUtils.getPaperSettingListFromWorkBook(rworkbook)); //Export 2007 outputStream = new FileOutputStream(new File("E://PageExcelExport.xlsx")); excel PageExcel2007Exporter page = new PageExcel2007Exporter(ReportUtils.getPaperSettingListFromWorkBook(rworkbook)); page.export(outputStream, rworkbook);Reproduction image:
3. Export pages in sheets
When the page is exported, each page of the report result is saved in an Excel file.
The code is as follows:
outputStream = new FileOutputStream(new File("E://PageSheetExcelExport.xls")); PageToSheetExcelExporter sheet = new PageToSheetExcelExporter(ReportUtils.getPaperSettingListFromWorkBook(rworkbook)); //Export 2007 version outputStream = new FileOutputStream(new File("E://PageSheetExcelExport.xlsx")); excel PageToSheetExcel2007Exporter sheet = new PageToSheetExcel2007Exporter(ReportUtils.getPaperSettingListFromWorkBook(rworkbook)); sheet.export(outputStream, rworkbook);Reproduction image:
4. Export of large data volume
This is more common, for example, a Excel file with 50000 behavior
The code is as follows:
outputStream = new FileOutputStream(new File("E://LargeExcelExport.zip")); LargeDataPageExcelExporter large = new LargeDataPageExcelExporter(ReportUtils.getPaperSettingListFromWorkBook(rworkbook), true); //Export 2007 version outputStream = new FileOutputStream(new File("E://LargeExcelExport.xlsx")); excel LargeDataPageExcel2007Exporter large = new LargeDataPageExcel2007Exporter(ReportUtils.getPaperSettingListFromWorkBook(rworkbook), true); large.export(outputStream, rworkbook);When exporting reports from large data volumes, the following two situations may occur:
1. If the export is successful, the data will be displayed in multiple sheets;
2. Two. Export failed and the exported Excel content is invalid. There are two main reasons for the above situation:
4.1 Export of large data volume reports without line engine enabled
Without the line engine enabled, two situations will also occur: the export is successful if the server can bear it, and the export is failed if the server cannot bear it. Let's take a look at the results below:
4.1.1 The server is within the scope of
The acceptable range is: the amount of data is large, exceeding the maximum number of rows of EXCEL but not very, very large, such as 100,000 pieces, and the data is not complicated, and it can be exported normally if the memory is sufficient and the network is not interrupted. When exporting, data exceeding the maximum row or column of Excel will be displayed in the next sheet. However, Excel has a limit on the number of rows and columns. For example, Excel 2003 has a maximum limit of 65536 rows, and the maximum limit on the number of columns is 256 columns.
4.1.2 The server cannot bear it
The unbearable range is that the data volume is large and very large, such as hundreds of thousands or millions or even more, and the export failure may occur due to memory restrictions. When the result is exported, the content of Excel that failed to export is invalid.
For these two export results, the main reason is how much data volume and data complexity (number of columns) are. There will be a critical point without a row engine. When the export is below the critical point, the exported content will have multiple sheets, otherwise the export will fail and the content will be invalid. Therefore, for large data volume reports, it is usually displayed using a row engine. In this case, exporting Excel is always successful. When the line engine-enabled large data volume reports are exported to Excel, they are divided into multiple .xls files and are transferred to the client in the format of a compressed package file.
4.2 Enable line engine export of large data volume reports
For large data volume reports, it is usually presented using a row engine, and in this case, exporting Excel is always successful. Because when exporting Excel with the line engine's large data volume report, it is divided into multiple .xls files and is 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, and 30 rows are displayed per page. The exported result will be 41 Excel, the first 40 Excel will be 24990 rows each, the last Excel will be 400 rows, and the 41 Excel will be compressed into zip packages and uploaded to the client to download.
The above content is four ways to export EXCEL for the Java FineReport report tool. I hope it will be helpful to everyone!