C# .NET - Calculate the Sum Totals in a DataTable.

Asked By Charvi on 26-Jan-17 02:44 PM

I have a DataTable that fetches the data in the below format:


Region  Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Asia ex-Japan Cash Equities 11500 11500 11500
Asia ex-Japan Cash Equities 20500 20500 20500
Asia ex-Japan Cash Equities 16200 16200 16200
Asia ex-Japan Convertibles 3000 3000 3000
Asia ex-Japan Convertibles 2000 2000 2000
Americas Cash Equities 18000 18000 18000
Americas Cash Equities 17000 17000 17000
Americas Bonds 6600 6600 6600
Americas Bonds 5700 5700 5700
Japan Convertibles 14000 14000 14000
Japan Convertibles 14500 14500 14500

What we need is the total for each group & sub-group within the above DataTable as shown below :


Region  Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Bonds Total 100000 100000 100000
Asia ex-Japan Cash Equities 11500 11500 11500
Asia ex-Japan Cash Equities 20500 20500 20500
Asia ex-Japan Cash Equities 16200 16200 16200
Cash Equities Total 48200 48200 48200
Asia ex-Japan Convertibles 3000 3000 3000
Asia ex-Japan Convertibles 2000 2000 2000
Convertibles Total 5000 5000 5000
Asia ex-Japan Total 153200 153200 153200
Americas Cash Equities 18000 18000 18000
Americas Cash Equities 17000 17000 17000
Cash Equities Total 35000 35000 35000
Americas Bonds 6600 6600 6600
Americas Bonds 5700 5700 5700
Bonds Total 12300 12300 12300
Americas Total 47300 47300 47300
Japan Convertibles 14000 14000 14000
Japan Convertibles 14500 14500 14500
Convertibles Total 28500 28500 28500
Japan Total 43000 43000 43000

Also, we need to print the above datatable to excel (i.e. we are not using any reporting tool where the totals and sub-totals could be easily computed).

How could we achieve this? Any help would be much appreciated.

Robbe Morris replied to Charvi on 26-Jan-17 02:42 PM
There is no native automatic functionality to perform what you want in .NET.  You'll have to write a query that sorts all of the regions and asset classes in the order you want.  Then iterate through each row comparing the current row's region/asset class with the previous record.  If the row is different region, write out a totals row, reset your region's counters, and move onto the next record.
Sundarasegaram Veerakuddy replied to Charvi on 30-Jan-17 04:46 AM
Hi,

If your operational environment does have MS Office installed, then you can use Excel Interop to get the excel output while looping through the rows and columns of the data table. You should get the data table ordered with necessary columns that can be used for grouping so that the totals could be calculated accordingly. A work-around could be:

            Excel.Application excel = new Excel.Application();   
            Excel.Workbook workbook = excel.Application.Workbooks.Add(true);

            int rowIndex = 0;
            foreach (DataRow row in data.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn column in data.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex + 1, colIndex] = row[column.ColumnName];
                }

//Check for your groups and add totals etc.
            }

    workbook.SaveAs(...);

You got a better and fastest way of exporting data table to excel without using Excel interop, but with ClosedXML component. If you have a resulted data table with all your totals in one, it could be used to export as:

        public bool ExportDataTableToExcel(DataTable dtExport, string fileName)
        {
            ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
            wb.Worksheets.Add(dtExport, "WorksheetName");
            wb.SaveAs(Path.ChangeExtension(fileName, ".xlsx"));
            return true;
        }

ClosedXML exports data table to excel with the extension XLSX only (ClosedXML is available for download).