Possible to dynamically add "Totals' (SUM) row

Question

Good day,If possible, I would like to be able to dynamically add a row to the bottom of a populated GridControl that would display the totals (sums) of any column that includes numerical values and ignore columns with text values. The grid can be of any size, row and column counts. I know the GridGroupingControl is probably better for this purpose but the regular GridControl is best for other purposes import to me so I would like to use that if at all possible. is there a straight forward way to add a Summing row dynamically like this?Thanks!

Arulpriya Ramalingam · Answer

Hi Travis,  
  
Thanks for contacting Syncfusion support.  
  
The GridControl does not have the support to add summary for a column. However, this can be achieved by summing the cell values using the excel formula(=(SUM(range))). We already provided a KB to add summary row and sum the values in GridControl. Please make use of the below KB link,  
  
KB link: https://www.syncfusion.com/kb/6992/how-to-add-summary-row-in-gridcontrol   
  
Note  
You can set the formula for multiple integer type columns by using QueryCellInfo event. Please refer to the below code example,  
  
Code example  
  




//Event triggering  
this.gridControl1.QueryCellInfo += GridControl1_QueryCellInfo;  
  
//Event customiation  
private void GridControl1_QueryCellInfo(object sender, GridQueryCellInfoEventArgs e)  
{  
    //For only the integer columns  
    if (e.RowIndex > 0 && e.ColIndex > 0   
        && e.Style.CellValueType == typeof(int) && e.RowIndex == gridControl1.RowCount)  
    {  
        //To set the CellType  
        e.Style.CellType = GridCellTypeName.FormulaCell;  
        //To add the formula for the cell.  
        e.Style.CellValue = "=SUM(" + GridRangeInfo.GetAlphaLabel(e.ColIndex) + "1:" +GridRangeInfo.GetAlphaLabel(e.ColIndex) + (gridControl1.RowCount - 1) + ")";  
    }  
}  
  
Please let us know, if you have any other queries.  
  
Regards,  
Arulpriya

Travis Chambers · Answer

Thank you very much. This is great info. I do have another question though. To get the original data into the grid I am exporting it from spreadsheetcontrol to data table. This works great with one caveat. It seems that only one option can be selected when exporting (i.e. Column Types, etc.). However, I am need of exporting the column names and calculated formula values. Is there any way to export with both settings at once?

Thanks!

Arulpriya Ramalingam · Answer

Hi Travis,  
  
Thanks for the update.  
  
We have analyzed your requirement and suspect that, you are trying to import the data from SpreadSheet control to grid with calculated formulas. By default, the formulas in the grid/sheet will be Exported/Imported with the formula text and its calculated value. If you want to retrieve the display text of the FormulaCell, the FormattedText property ofGridStyleInfo can be used and CellValue property of GridStyleInfo can be used to get the Formula of the cell. Please refer to the below code, UG and sample,  
  
Code example  
  




private void formulaBtn_Click(object sender, EventArgs e)  
{  
    //To get the Formula string.  
    string cellValue = this.gridControl1[rowIndex, colIndex].CellValue.ToString();  
    //To get the calculated value.  
    string ComputedValue = this.gridControl1[rowIndex, colIndex].FormattedText;  
    MessageBox.Show("Formula : " + cellValue + "
ComputedValue : " + ComputedValue);  
}  
  
Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/CS_(2)-1700650929   
  
UG link: Importing , Exporting   
  
Note  
Please let us know your exact requirement with simple screenshot, if we misunderstood your scenario. So, that we could provide the solution at the earliest.  
  
Regards,  
Arulpriya

Travis Chambers · Answer

This is not quite what I need. I am not importing directly from spreadsheet to Grid, but instead I must export from spreadsheet to datatable, and then datatable to grid. The issue is I need to export the ColumnHeaders and the CalculatedValues, but there seems to be only one overload for exporting options in the export to datatable from spreadsheet method. Essentially I need to be able to "ExportDataTable(range, ExcelExportDataTableOptions.ComputedFormulaValues + ExcelExportDataTableOptions.ColumnHeaders)" somehow.

Thanks.

Travis Chambers · Answer

Nevermind! I just realized I can use "|" to combine multiple export options and that seems to work well.Thank you!

Arulpriya Ramalingam · Answer

Hi Travis, 
 
Thanks for the update. 
 
We are glad that your reported issue has been resolved by yourself. Please let us know, if you need any further assistance on this. 
 
Regards, Arulpriya