//Try This
var _result = from r1 in DT.AsEnumerable()
group r1 by new
{
RegionAsset = r1.Field<string>("Region Asset"),
Class = r1.Field<string>("Class"),
} into g
select new
{
RegionAsset = g.Key.RegionAsset,
Class = g.Key.Class,
TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
TotalPnL = g.Sum(x => x.Field<int>("PnL")),
TotalBonus = g.Sum(x => x.Field<int>("Bonus"))
};
//For Excel Download
using Excel = Microsoft.Office.Interop.Excel;
public static bool ExportDataTableToExcel(DataTable dt, string filepath)
{
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
try
{
oXL = new Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
oWB = oXL.Workbooks.Add(Missing.Value);
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "Data";
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
oWB.SaveAs(filepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oWB = null;
oXL.Quit();
}
catch
{
throw;
}
finally
{
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
return true;
}