Excel Export Flexgrid with custom formatter calculation is incorrect value

Excel Export Flexgrid with custom formatter calculation is incorrect value

Posted by: elaas on 29 August 2022, 8:19 am EST

• 

  elaas

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 29 August 2022, 8:19 am EST

  Hi Support,

  I’m experiencing an issue that I hope I can describe without a demo. I can’t share my code due to company restrictions. If needed, I can create a demo, but it will likely be rather time consuming to do so. Here are the circumstances:

  • "
    • "We have a pretty complex ```
      
      FlexGrid

  
  	* "The grid has a ```
  GroupPanel
  ``` attached, allowing custom groupings"
  
  	* "We also have a very complex formatting system attached to the ```
  formatItem
  ``` event handler, which affects both grouped and detail rows.  Some of these ```
  GroupRow
  ```s calculate a completely custom value to display in the grid, using the ```
  formatItem
  ``` event handler.  Values are calculated and injected into the ```
  innerHTML
  ``` of the cell.."
  
  	* "This grid is downloadable to Excel using the ```
  FlexGridXlsxConverter
  ```"
  
  "
  
  
  All of this works fine, except when we export to Excel.   When we export, the ```
  GroupRow
  ``` cells with a custom formatter calculation return the default group calculation (i.e. SUM), instead of the calculated value determined in the ```
  formatItem
  ``` callback.   To be clear, the number is calculated.  I see the debugger step into the ```
  formatItem
  ``` handler during export, but the number in Excel is the sum of the detail rows, instead of the custom calculation.
  
  Any idea how I can resolve this?  
  
  Thank you!
  
  Eric

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 29 August 2022, 8:28 am EST

  sorry in advance for the poor formatting. I thought that the CODE tag behaved differently (inline), and there’s no way for me to edit it after i’ve posted.

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 29 August 2022, 8:32 am EST

  This small piece I can share, as it’s pretty generic. Here is the code we use to export to Excel.

  
  exportExcel() {
              var book = wjGridXlsx.FlexGridXlsxConverter.save(this.grid, {
                  includeColumnHeaders: true,
                  includeRowHeaders: true,
                  includeCellStyles : true
              });
              const filename = this.name.toLowerCase() + ".xlsx"
              book.sheets[0].name = this.name;
              book.saveAsync(filename);
          },            
  
  
  

• 

  sonu.pandey

  

  • Post Options:
  • Link
    Copy

  Posted 29 August 2022, 6:30 pm EST

  Hello,

  You may pass the formatItem to the IFlexGridXlsxOptions and set the grouped row’s cell data again during export as per your requirement to apply the formatting changes done in the FlexGrid into the exported excel as well. Please refer to the sample link below demonstrating the same:

  https://stackblitz.com/edit/angular-228o9h?file=src%2Fapp%2Fapp.component.ts

  Regards

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 30 August 2022, 3:10 am EST

  Thank you for your reply and example. Your example does demonstrate the problem. It shows that the formatItem handler for the FlexGridXlsxExporter and FlexGrid are not the same. It has been some time and I forgot about this. The arguments are different, and in the FlexGridXlsxExporter, you must write directly to the Excel cell, which is (obviously) not available during the FlexGrid’s formatItem execution.

  For me, because the formatItem logic is very complex for the FlexGrid, it was not a good fit to modify the functions used to format the FlexGrid to also accommodate the export. Instead, I simply detected which cell values needed to be “copied” and wrote the innerHTML value to the Excel value in a small function i supplied as the IFlexGridXlsxOptions.formatItem parameter.

  Thank you!