DataTabale To Excel

Feroz Khan

Dear All,

I prepaid below Excel sheel through c# converted from DataTable to Excel thorugh Loop but the Last row I did manually in Excel.

What i need when my loop will finish then in Last row should be the Total in Excel and the row is not fixed my sometime row will be 5 may 50 whenever finish my loop then last should be Total like below.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sandy8y ago
HI Feroz,

You can use EPplus library for exporting DataTable to Excel.

For more information :-
using (ExcelPackage pck = new ExcelPackage(newFile))
{ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");ws.Cells["A1"].LoadFromDataTable(dataTable, true);pck.Save();
}
and add total line at last Manually .

Let me know if you want example for this.

Thanks
Sandy
Leon D8y ago
Just another alternative way:
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

//Insert datatable to excel
sheet.InsertDataTable(datatable, true, 1, 1);

string formula = "=Sum(" + sheet.Range[2, sheet.LastColumn].RangeAddress +
        ":" + sheet.Range[sheet.LastRow, sheet.LastColumn].RangeAddress + ")";

//Add formula
sheet.Range[sheet.LastRow + 1, sheet.LastColumn].Formula = formula;

//Calculate formula
workbook.CaculateFormulaValue(formula);

//Save the file
workbook.SaveToFile("Data.xlsx", ExcelVersion.Version2013);
Fisrt you need to add reference to Free Spire.XLS dll via NuGet Package Manager and then use the above code.
Nilesh Shah8y ago
it would have been better if you have posted your current code.

your no. of rows are not fixed, but your no. of columns is - I here assume the column to be 5.

when you loop thru your DB records, and write the same to excel, you must be storing somewhere the row index or row number to indicate current row in excel. I here assume it is rowIndex.

so when you completed your loop to write all rows, write this code.
I assume your data starts from row 2, that's why I have taken fixed "2", other wise change to proper variable like starting row index or something.
Worksheet.Cells[rowIndex + 1, 5].Formula =
        "=Sum(" + Worksheet.Cells[2, 5].Address +
        ":" + Worksheet.Cells[rowIndex, 5].Address + ")";