Projects:Support Xlsx Export Format/Specs
Contents |
Functional Requirements
The main functional requirement of this project is to allow generating Excel reports with more than 65,536 rows. To achieve this goal, this kind of reports will be generated using the xlsx format instead of the xls which has being used so far.
This change will be transparent for the user, because the xlsx format will be used as default when exporting any report using the Excel option.
Besides, in order to ensure backwards compatibility a new preference will be available. This new preference will allow users to decide the format to be used when exporting excel reports: xls or xlsx.
Technical Specs
Xls vs Xlsx
The xlsx format, available since Excel 2007, has a bigger working area than xls format:
| Format type | Max. Rows | Max. Columns | Max. Cols by letter |
|---|---|---|---|
| XLS | 65,536 | 256 | IV |
| XLSX | 1,048,576 | 16,384 | XFD |
Therefore, now with xlsx format, the new limits for the excel reports are 1,048,576 (2²⁰) rows and 16,384 (2¹⁴) columns or the column XFD.
Together with this, xlsx compresses the file, i.e, the same report generated with xls and xlsx takes up less disk space with xlsx format. This is due to the fact that the xlsx extension is written in XML standard.
Another consideration to be noted is that xls type supports only a limited set of colors. For this reason, when using colors in a xls report generated with JasperReports it was recommended to make use of the colors present in the standard excel color palette. This is because if the colors used in the a report design do not match any of those acceptable colors, the XLS exporter will use a special algorithm to determine which is the nearest one by comparing the RGB levels.
With xlsx the range of color is bigger: all combinations possible for RGB are available.
Increasing the Reporting Engine API
To be able to export xlsx reports, this new export type will be supported by the ReportingUtils class. Thus, it will use the JRXlsxExporter class provided by Jasper to generate this kind of reports.
Excel Export Format Preference
By default, all the excel reports in the application will be exported using the xlsx format. But in order to keep backwards compatibility, a new preference called Excel Export Format will be created. By setting this preference, the user can explicitly decide the format of the excel reports.
UI Text Updates
As part of this project, the labels for the buttons and processes used to export xls reports have been reviewed. Labels like Export to XLS do not apply anymore, so they have been replaced with Export to Excel.