Excel export suggestions
-
Hello,
we have big Excel reports at our company. We are currently have them on jsreport and generating them through html-xls or writing them in pure excel. When we tried to write the bigger reports with a lot of columns the html-xls was very slow. We also couldn't figure out how we could apply proper DateTime styles / formatting in html-xls.
We have conditional formating where the time values are given in seconds and are then converted to decimal(HH.mm) or normal(HH:mm) format. We need them to be recognized by Excel so the clients can then further calculate with the values.
The pure xls files are very big and take a long time to write, so they are very inconvenient. Do you have any suggestions on how we could achieve generating big and small Excel reports with the same approach ?
-
When we tried to write the bigger reports with a lot of columns the html-xls was very slow
Did you check the performance section in the docs?
https://jsreport.net/learn/html-to-xlsx#performanceWe also couldn't figure out how we could apply proper DateTime styles / formatting in html-xls.
The support for cell types was implemented a few years ago in the
html-to-xlsxrecipe
https://playground.jsreport.net/w/anon/gN4DoxcF
https://jsreport.net/learn/html-to-xlsx#cells-with-data-typesDo you have any suggestions on how we could achieve generating big and small Excel reports with the same approach?
You can also try an approach when you write handlebars tags directly to the Excel template. This was added to the
xlsxrecipe last year.
https://jsreport.net/learn/xlsx#generation
https://playground.jsreport.net/w/admin/Lh8Kjc~f
-
Thank you for your reply, I'll try out the given suggestions!
-
Is there any way to use helper methods when using the Excel template approach?
-
Yes, here is the button.
-
Oh, thank you :D !
-
One more question... Is it possible to display columns conditionally according to json, using the Excel template approach?
-
What do you mean exactly?
Like that you would fill the cells in a column with tags like{{someCellVall}}and then somewhere else say that the whole column should be removed and everything shifted left? I am afraid you would need to implement this using thexlsxtransformation helpers and post-process your result.If you just need to hide values but keep an empty column, you can use
{{#if condition}}{{someCellValue}}{{/if}}or implement your own custom helper to do that.
-
Yes I need the whole column removed and everything shifted left, based on a true/false value in the JSON.
-
Can't you just don't print the column in the first place?
You can use for example a custom script that will modify your input data so it doesn't reach the xlsx template and you print only what you want.Otherwise you need to use the
xlsxtransformation helpers I am afraid.
-
If I understand you correctly: If I want to generate conditional columns in Excel, I can't use the Excel template method? Writing xls or html-xls would be the only option?
-
This post is deleted!
-
Anwser to your previous reply about optimizing html-xls preformance: The usage of #htmlToXlsxEachRows didn't quite work.
-
If I want to generate conditional columns in Excel, I can't use the Excel template method? Writing xls or html-xls would be the only option?
You can combine both approaches in xlsx. Type handlebars inside excel for simplicity and then do low-level transformation using xlsx helpers like here
https://playground.jsreport.net/w/anon/LFe64Z85
You may need a lot more low-level fixes after the cell deletion, but this should show you how to start.Or in some cases, you can use a custom script to modify your input data so you don't write data to columns you don't want in the first place.
The usage of #htmlToXlsxEachRows didn't quite work.
Please share playground what doesn't work.
-
Thank you for the Excel template advice! Would it be also possible to set the cell format? So let's say we have a global time format setting that can be decimal (HH.mm) or normal (HH:mm). I would need to change the cell formats according to the global setting so Excel can recognize the values correctly.
We also have a requirement to conditionally hide rows.
-
You can do everything with xlsx helpers. You just need to take your time and find how to do it in excel XML and then replicate the same with xlsx helpers.
-
Thanks, and here is the demo for the html-xls. The main problem we have is preformance. On the server It works, but here, on the playground it crashes.
https://playground.jsreport.net/w/AnejBradacVranc/0RJEPt1Q
It's the EXCEL_sample file.
-
On the server It works, but here, on the playground it crashes.
So it actually works, but the playground has limited memory so it fails there.
The main problem we have is performance
The problem is that by default, we need to load the HTML table in Chrome so we can get the cells and styles. Chrome is slow with long tables and this takes time. There is nothing we can do there to optimize it. On the other hand, this is very comfortable for developers to write quickly excel reports. So it is a tradeoff between fast implementation and slower runtime performance.
We have implemented another engine for parsing the HTML tables to speed this up. It's 2x faster, but has another tradeoff because it does support only inline styles.
https://jsreport.net/learn/html-to-xlsx#cheerio-html-engineUltimately, the xlsx recipe should fit better for very long excels where the performance is important.
-
Thanks!