DataTabale To Excel

Question

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.

Nilesh Shah · Answer

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 + ")" ;

Leon D · Answer

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.

Sandy · Answer

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