I was exploring jett as an option for our requirement, which involves generating excel out of paginated data. I want to append data to an excel generated by jett. Is such a feature supported by jett currently?
JETT will read all the data to fill it in the template, so you will need to "wrap" your paginated data in a Collection first before passing it to JETT to generate the Excel file. This could be as simple as retrieving all your data (page after page) and put it in a single List, but you might also wrap it in a custom class that will iterate over the pages without having to load everything in memory at once.
That being said, since JETT is based on "Standard" POI API, it means that even if your data is paginated, the generated Excel file will be stored as one big XSSF document in memory, so you must ensure that the generated workbook size will not exceed the available memory. With XSSF, this size can grow very fast - a good estimate is to take your xlsx file, rename it to zip, unzip it, and look at the size of the extracted XML files.
From my experience JETT works really well if you have a few thousands of rows - eventually a few tens of thousands if the template is not overly complicated.
However, if you need to generate huge spreadsheets with hundreds of thousands of rows, you'll need to use POI Streamed XSSF API (SXSSF: https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html) and insert the data yourself.
One approach that I've leveraged for very large spreadsheets is that I first render the document using JETT - and can thus leverage all the features and flexibility of JETT - to generate all the headers and structure of the spreadsheet, but I don't insert the data. I only insert some placeholders on the last line of spreadsheets where very large amounts of data have to be inserted. Once this is done, I take the generated POI document, and edit it with SXSSF to insert the rows at the end of the sheets. This approach scales very well and allows to reach XLSX limit (1 million row per sheet) with acceptable performance and minimal memory usage.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I was exploring jett as an option for our requirement, which involves generating excel out of paginated data. I want to append data to an excel generated by jett. Is such a feature supported by jett currently?
Thanks,
Souvik
Hi,
JETT will read all the data to fill it in the template, so you will need to "wrap" your paginated data in a Collection first before passing it to JETT to generate the Excel file. This could be as simple as retrieving all your data (page after page) and put it in a single List, but you might also wrap it in a custom class that will iterate over the pages without having to load everything in memory at once.
That being said, since JETT is based on "Standard" POI API, it means that even if your data is paginated, the generated Excel file will be stored as one big XSSF document in memory, so you must ensure that the generated workbook size will not exceed the available memory. With XSSF, this size can grow very fast - a good estimate is to take your xlsx file, rename it to zip, unzip it, and look at the size of the extracted XML files.
From my experience JETT works really well if you have a few thousands of rows - eventually a few tens of thousands if the template is not overly complicated.
However, if you need to generate huge spreadsheets with hundreds of thousands of rows, you'll need to use POI Streamed XSSF API (SXSSF: https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html) and insert the data yourself.
One approach that I've leveraged for very large spreadsheets is that I first render the document using JETT - and can thus leverage all the features and flexibility of JETT - to generate all the headers and structure of the spreadsheet, but I don't insert the data. I only insert some placeholders on the last line of spreadsheets where very large amounts of data have to be inserted. Once this is done, I take the generated POI document, and edit it with SXSSF to insert the rows at the end of the sheets. This approach scales very well and allows to reach XLSX limit (1 million row per sheet) with acceptable performance and minimal memory usage.
Thanks Etienne for the response. I have tried out the sxssf API and the merging capabilities works great for my use case.