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).