How to Export more than 50000 records to excel with minimal performance hit?
We are on Pega 7.1.9 and are using the Pega OOTB capabilities for our reporting.
Our application is data centric and it has a lot of data. Our business users, some times, need to create reports on huge amount of data and export the results to excel. In order to meet this requirement, we changed the "Maximum number of rows for export" to 50000 and "Maximum elapsed time in seconds for export" to 300 seconds in many Report definitions. Now, when there are more than 50000 records in the results, the excel exports 50000 records and the processing takes around 300 seconds and the size of the excel generated is over 40 MB. The excel file is generated with an .xls extension. Now, our business users want to to see if we can further increase the maximum limit beyond 50000. So, our questions here are:
1. Is there a way we can further optimize the excel generation?
2. As we mentioned above, the generated excel is of .xls format and is of 40 MB size. But, when we opened it and saved as with .xlsx extension, It's size became less than 5 MB. (We noticed that the files get compressed to atleast 1/12th of the initial size just with change of extension.) Is there a way we can directly generate the file with .xlsx format?
***Updated by Moderator: Marissa. Removed user added Ask the Expert group tag. Apologies for confusion, shouldn't have been an end-user option***