Make summary row a real formula in excel export from sfdatagrid?

[David A]

Hi, I have a nice SFdatagrid I am populating from a rest web service.  It has a total that is a tablesummary row that is a sum of hours.  When I export, I see I have the option to export this, and it does work.  However, it would be great if it was a formula and not the text value.

I read up and see that I have what appears to be two options, 

1) Open using XLSio the spreadsheet and make my own formulas.  Turn off table summary exporting.

2) Somehow use OptionsOnCellExporting to trap the summary row and replace it with sum(above).

Is there a preferred method?  Any samples to do something like this?

[David A]

ok, I added this to turn my summary into a real formula.  It works well.  I wonder though if there is a means to get column in letter notation any other way than trimming off n numbers - e.Range.AddressLocal.  Would be nice to have a e.Range.AddressLocalColumn :)

        private void OptionsOnCellExporting(object sender, DataGridCellExcelExportingEventArgs e)

        {

            if (e.CellType == ExportCellType.TableSummaryCell && e.ColumnName == "hours")

            {

                var lastRow = e.Range.Row;

                

                if (lastRow > 2)

                {

                    var colName = e.Range.AddressLocal;

                    colName = colName.TrimEnd('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');

                    e.Range.Cells[0].Formula = $"sum({colName}2:{colName}{lastRow-1})";

                }

                e.Handled = true;

            }

        }

[Mohanram Anbukkarasu]

Hi David, 

Thanks for contacting Syncfusion support.  

We are glad to know that you have found solution for the reported problem. In XlsIO, there is no property to get the alphabetical column name of the cell. We can get the column index by using e.Range.Column property. You have perform string processing to get the column name to achieve your requirement your requirement as you have achieved. Please let us know if you require any other assistance from us.  

Regards, 

Mohanram A.