feat(export): enhance Excel `valueParserCallback` with dataContext & new demo

ghiscoding commented 3 months ago

the excelExportOptions.valueParserCallback was missing the dataContext which can be useful when the exported data is not the current cell value but another cell value, for example our cell is Total but is a calculated value from 2 other column dataContext (Price & Qty)
fix a bug found with new demo, the excelExportOptions.style wasn't being applied on regular data cell
add a new Example 23 to demo Excel Export with Formula
the new demo was created with the following logic (there are 3 calculated fields: SubTotal, Taxes & Total)
- (in UI) - use Custom Formatters for calculated fields
- (Excel Export) - use Excel Formula for calculated fields (via excelExportOptions.valueParserCallback)

TODOs

[x] unit tests
[x] add Cypress E2E tests (for UI only though, we'll test the export to Excel manually)
[x] excelExportOptions.filename doesn't seem to work, need to investigate
[x] make grid editable with editor styling
[x] center alignment doesn't seem to work with custom export formatter
[x] improve existing docs
[x] might need to test with grouping, see what the new dataContext is filled with, I think it will be the group row object
- I forgot that for Groups, we actually have groupTotalsExcelExportOptions which I had to improve to do everything I wanted in this demo :)
[x] add more color styling to show full features
[x] also calculate all Totals Sums via Excel Formulas as well

use Excel Formulas to calculate Totals by using other dataContext props

use Excel Formulas to calculate Group Totals

stackblitz[bot] commented 3 months ago

Run & review this pull request in StackBlitz Codeflow.

codecov[bot] commented 3 months ago

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 99.8%. Comparing base (21d8d4c) to head (2e56902). Report is 5 commits behind head on master.

Additional details and impacted files

```diff @@ Coverage Diff @@ ## master #1543 +/- ## ======================================== + Coverage 99.8% 99.8% +0.1% ======================================== Files 198 198 Lines 21671 21680 +9 Branches 7241 7248 +7 ======================================== + Hits 21610 21619 +9 + Misses 61 55 -6 - Partials 0 6 +6 ```

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

ghiscoding commented 3 months ago

@zewa666 So I know we talked about this a while ago and I always wanted to eventually go in depth on the subject to see if it's doable to use Formula in Excel Export by using other props of the item data context, so here it is... just because.... we can 🚀 😆

Note that I was missing the dataContext argument in excelExportOptions.valueParserCallback, so I added it in here and tagged this PR as a feature because I don't think it would have been possible to do without the data context in this case (meaning creating a formula by using other props of the data context)

cc @jr01

zewa666 commented 3 months ago

damn don't show this to my boss 🤣

need to take some proper time to check this PR out but so far this looks amazing

ghiscoding commented 3 months ago

@zewa666 cherry on top 🍒, I even got the demo to calculate Grouping Totals Sum (see both print screens above), your boss really shouldn't see this lol 🚀

However please note that it takes a fair amount of code to get it all working, I also assume that perf is probably impacted as well a bit, but anyway the point was to show that it's possible. I assume that generic functions and code reuse should help to decrease the number of lines, I know that I have repeated code and that is to keep it readable and understandable by the users who looks at the code. Also another thing to note, I got it working for Group Sum with 1 group level depth, it would be much harder to implement with multiple depth (and that has to be implemented by the user like in the new demo)

Final note, I had to add couple more arguments to valueParserCallback and considering that we now 7 arguments to the callback, I am planning to regroup some of these arguments into a single arg object for simplicity and also easier to extend in the future. I know it's a breaking change but I'd be surprised if anyone started using valueParserCallback since it's a very custom way to export to Excel and new major wasn't long ago so not that many migrated yet too, so I will do that in another PR in the coming days, so don't go crazy on using it just yet 😉

https://github.com/ghiscoding/slickgrid-universal/blob/721f017e3aa47e13e0464182d4297d3ec3e9f977/packages/common/src/interfaces/columnExcelExportOption.interface.ts#L39-L40

At this point, even Ag-Grid doesn't even provide such custom export, I should be paid a large amount of money for this feature, but to be fair I'm not even sure anyone would go that deep in their Excel export customization 😆

zewa666 commented 3 months ago

so much good stuff in this example. I didnt know about setting the metadata for excel exports. thats really great. where can I read up on the options? do you perhaps have a link?

ghiscoding commented 3 months ago

@zewa666 indeed you can do a lot with the cell value parser, I updated the associated cell value parser docs, you can find some info there. But I got a lot of these ideas when I migrated Excel-Builder to native code, for example the Excel Formulas I really got it from there when I read more about the metadata, so you'll get more info by going to the excel-builder-vanilla repo, read the docs and also try all the demos which I purposely made them WYSIWYG (what you see in the UI, you'll get in the export). In summary, whatever you can do in Excel-Builder, you should be able to do in SlickGrid as well 🚀

ghiscoding / slickgrid-universal