asked on December 23, 2021
Hi Support,
When i have a pivot table with f.e. the following data:
projectname A >>> activity A >>> 8 hours
projectname A >>> activity B >>> 4 hours
The grid will show "projectname A" once, because it is repeated and is grouped, so the rows can be collapsed. This is ok.
But when exporting to excel or csv it makes an exact copy of the grid and therefore not filling the columns.
Is it possible when exporting data to excel or csv to fill in all the values? Or else the export to excel or csv misses values and therefore it is not usefull for data manipulation in excel.
regards Jaco
⋅ December 23, 2021
Hello, Jaco,
Thank you for writing to us.
Yes, it is possible to include collapsed data in the export. Our team suggests the following approach:
report configuration. We are going to restore it later.aftergriddraw event. In its handler, export the report and then restore the previously saved report configuration.
We have prepared a JSFiddle for illustration purposes: https://jsfiddle.net/flexmonster/dy95bfnj/.
In addition, it is also possible to change the Export -> To Excel Toolbar Tab's behavior by customizing the Toolbar.
Please let us know if this helps.
Kind regards,
Vera
⋅ December 23, 2021
Hi Vera,
Thanks for you answer and the example. This is good to know, but not what i need.
When looking at the following table, this output is fine for viewing in Flexmonster. But when i export this to excel or csv the column category is only filled in 1 row. Which is not good, because data manipulation in excel is now not possible...for example if i want to filter the category on "accessories" then it will only return 1 row...the others have empty values and will not be shown.
Category
Color
Total Sum of Price
Accessories
green
9336
red
41524
white
32732
yellow
21005
Is there a solution for this.
Regards Jaco
⋅ December 23, 2021
Hello, Jaco,
Thank you for your reply.
For such cases, the idea remains the same: change the report how you would like it to appear in the export and then restore the previous report.
In your scenario, you could programmatically remove any set filters just when the export occurs. This will ensure that none of the values are filtered out in the export result.
Here is a modified version of the provided example for demonstration: https://jsfiddle.net/flexmonster/ndeqp8jz/.
In the same way, you could modify the report by adding/removing any necessary settings using Flexmonster API before the export to adjust it to your use case.
For example, you could also switch the grid to classic form during export to provide an Excel-like layout of hierarchies. Here is an example of how the classic layout looks: https://jsfiddle.net/flexmonster/zkm26d6h/.
Please let us know if this helps.
Kind regards,
Vera
⋅ December 23, 2021
Hi Vera,
The first example (https://jsfiddle.net/flexmonster/ndeqp8jz/) still does not show all filtered out values, but the second does what i want.
So, i need to do what example 2 does before calling the export function.
I will give that a try, thanks for your answer.
Regards Jaco
⋅ December 23, 2021
Hello, Jaco,
Thank you for the update.
We are happy to hear that you've found our response helpful.
Should any other questions arise, feel free to reach out.
Kind regards,
Vera
⋅ February 18, 2022
Hi Vera,
I have a follow-up question. The solution i have now is as follows:
When clicking the "Export to excel" button:
- call a custom function with the following lines
1.this.report.customizeCell(this.customizeCellFunction);
This line will fill the empty columns with their correct value
2.this.report.exportTo('excel');
This line will do the export to excel with every column with a value
3.this.report.customizeCell(null);
This line will reset the columns back to empty if needed.
This does not work, because line 2 takes longer to process and line 3 will reset the columns before the excel is ready, there for giving empty columns in the excel....which i do not want
So when i change the code to:
1. this.report.customizeCell(this.customizeCellFunction);
2. this.report.exportTo('excel', {}, this.clearCustomizing);
This works perfectly and does exactly what i want ...but now because of the processing time of the excel file, you will see the empy columns being filled on screen....then when the excel is prompted for download, then the columns will be reset again.
This is not a very nice user experience.
Are there any other solutions?
- can i create a copy of the report, change the values in that object and export the copy to Excel? In this way the original report (on the screen) is not visible changed for the users)
- to not show the changes to fill the empty columns in the report itself
Regards,
Jaco
⋅ February 21, 2022
Hello, Jaco,
Thank you for your question.
Our team suggests one of the following solutions:
A possible solution would be to display a non-transparent loading screen on top of the grid, therefore, hiding the grid changes during export.
Please let us know if this helps.
Kind regards,
Vera
⋅ February 23, 2022
Thanks for the reply, Vera,
We will try the overlay first. Do you per chance allready have an example of using an overlay?
Regards Jaco
⋅ February 24, 2022
Hello, Jaco,
Thank you for the update.
One idea could be to change Flexmonster's default overlay's opacity to make it non-transparent via CSS:
/* Make Flexmonster's overlay non-transparent */
#fm-pivot-view div.fm-ui-modal-overlay {
opacity: 1 !important;
}
Still, there is a short timeout before Flexmonster displays the overlay. To handle such scenarios, you could also display your own overlay element in front of the grid during the export. We prepared a simple JSFiddle for demonstration purposes: https://jsfiddle.net/flexmonster/5kpdbj2e/.
We hope this helps.
Kind regards,
Vera
⋅ February 24, 2022
Hi Vera,
Thanks, that was exactly what i needed.
Regards Jaco