Grid CustomSummaryValue

Question

Having some trouble calculating CustomSummaryValue from code behind to export Grid.

Looking at this sample:

https://www.syncfusion.com/kb/7452/how-to-export-grid-with-custom-summary

private object QueryCellInfo(IQueryable arg1, SummaryColumn arg2)
{
//How to sum a field here based on arg1
var rs = 100000;
var dol = 0.017;
return (rs * dol);
}

Thanks!

Farveen Sulthana Thameeztheen Basha · Answer

Hi Jose,

Thanks for contacting Syncfusion Support

We have checked your query and calculated the sum of the columns while on Exporting. Initially, the Excel WorkBook is saved as a IWorkbook and calculated sum of two columns. Please refer to the code example:-

Client side:-

<ej:Grid ID="FlatGrid" runat="server" OnServerPdfExporting="FlatGrid_ServerPdfExporting" OnServerWordExporting="FlatGrid_ServerWordExporting" OnServerExcelExporting="FlatGrid_ServerExcelExporting">

<ej:SummaryRow Title="Currency" >

. . .

</ej:SummaryRow>

</SummaryRows>

. . .

</Columns>

</ej:Grid>

Serverside:-

protected void FlatGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

{

ExcelExport exp = new ExcelExport();

IWorkbook book = exp.Export(FlatGrid.Model, (IEnumerable)FlatGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, false, "flat-lime", true);

IWorksheet worksheet = book.Worksheets[0];

//Get the index of the column

int inx = GetColIndex("CustomerID") + 1;

IConditionalFormats condition = worksheet[1, inx, worksheet.UsedRange.LastRow, inx].ConditionalFormats;

IConditionalFormat condition1 = condition.AddCondition();

condition1.FormatType = ExcelCFType.Formula;

//A1 refers to the first Column value i.e. OrderID

//We have taken Sum value

condition1.FirstFormula = "=Sum($A1,2)";

condition1.BackColor = ExcelKnownColors.Red;

book.SaveAs("Export.xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response, ExcelDownloadType.Open);

}

Please get back to us if you need any further assistance.

Regards,

Farveen sulthana T

v

Jose Salamanca · Answer

You don't have a custom summary in your Grid Example:

Please see the attached sample project (GridFeatures.aspx). link(https://cannypartner-my.sharepoint.com/personal/salamancaj_cannypartner_com/_layouts/15/guestaccess.aspx?docid=163795ede1e52480dac61ac39c32a642b&authkey=AZpWxw2KqDEHw3tpkI88ejM&e=2141e6b8a2d24e918d51f046d72bc477)

The example has a Grid which 3 summary columns, with 1 column being a custom summary. Custom summary is handled at the client side. When we want to export the data if you using the normal export code:

//Original Export

//ExcelExport exp = new ExcelExport();

//exp.Export(gdCatReview.Model, (IEnumerable)gdCatReview.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime");

You get an exception, if you remove the custom summary column ( <ej:SummaryColumn SummaryType="Custom" CustomSummaryValue="GetCustomSummaryValue1" Format="{0:P2}" DisplayColumn="Freight" Prefix="PCT = " />), it exports normally.

this kb almost explains how to do it: https://www.syncfusion.com/kb/7452/how-to-export-grid-with-custom-summary

With the exception that it doesn't show how to calculate the value via the IQueryable.

If I do it the way you're suggesting, I'll have to do a lot of formatting on code behind to match the other SummaryColumns that export correctly.

Thank you,

Jose

Farveen Sulthana Thameeztheen Basha · Answer

Hi Jose,

We have checked your code example and according to your requirement we have calculated summary column as like you have performed the customSummaryValue in the clientside while on Exporting in server end.

Please refer to the code example:-

protected void gdCatReview_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

{

string ExportFileName = ("Export_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");

ExcelExport exp = new ExcelExport();

GridExcelExport exp1 = new GridExcelExport();

ExcelEngine excel = new ExcelEngine();

IApplication application = excel.Excel;

IWorkbook workbook = application.Workbooks.Create(2);

exp1.QueryCustomSummaryInfo = QueryCellInfo;

exp1.Theme = "default-theme";

. . .

workbook.SaveAs(ExportFileName, ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.Open);

}

private object QueryCellInfo(IQueryable arg1, SummaryColumn arg2)

{

IQueryable<Orders> Items = arg1 as IQueryable<Orders>;

var TotalSalesData = GetTotalSalesData1(Items);

var TotalGPDollarData = GetTotalGPDollarData1(Items);

return TotalGPDollarData / TotalSalesData;

}

public double GetTotalGPDollarData1(IQueryable<Orders> Items)

{

double TotalGPDollarData = 0;

foreach (var item in Items)

TotalGPDollarData += Convert.ToDouble(item.Freight);

return TotalGPDollarData;

}

public double GetTotalSalesData1(IQueryable<Orders> Items)

{

double TotalSalesData = 0;

foreach (var item in Items)

TotalSalesData += Convert.ToDouble(item.EmployeeID);

return TotalSalesData;

}

Please refer to the sample:- https://www.syncfusion.com/downloads/support/forum/133499/ze/Tab-1505304163_110117-666564385

Please get back to us if you need any further assistance.

Regards,

Farveen sulthana T

Jose Salamanca · Answer

Thank you for the sample, I was able to get it working on my application.Jose

Seeni Sakthi Kumar Seeni Raj · Answer

Hi Jose,  
 
Thanks for the update. We are happy to hear that your requirement has been achieved. Please get back to us, if you require further assistance on this.  
 
Regards,  
Seeni Sakthi Kumar S.