how to add new row sum of column in excelsheet

Question

I am working with excelsheet I am trying to add sum of column in new row but not able to add sum of column Current Output: Expected Output(I want to below output): see code: private void AddExcelSheet(DataTable sDT, ExcelWorksheet excelWorksheet) { DataTable dtCustomDetail = new DataTable(); dtCustomDetail.Columns.Add( "Id" , typeof ( string )); dtCustomDetail.Columns.Add( "Name" , typeof ( string )); dtCustomDetail.Columns.Add( "Cust Name" , typeof ( string )); dtCustomDetail.Columns.Add( "Cust Qty" , typeof ( int )); if (sDT != null && sDT.Rows.Count > 0) { foreach (DataRow Dr in sDT.Rows) { DataRow DRowDetail = dtCustomDetail.NewRow(); DRowDetailCO[ "Id" ] = Dr[ "Cust_Id" ].ToString(); DRowDetailCO[ "Name" ] = Dr[ "Name" ].ToString(); DRowDetailCO[ "Cust Name" ] = Dr[ "CUST_NAME" ].ToString(); DRowDetailCO[ "Cust Qty" ] = Dr[ "Cust_QTY" ].ToString(); dtCustomDetail.Rows.Add(DRowDetails); } } excelWorksheet.Cells[2, 1].LoadFromDataTable(dtCustomDetail, true ); //here load my table excelWorksheet.Cells[].Formula = "=SUM(D3:D" + (6) + ")" ; //here I am trying to add some of column in new line but I am stuck why I am using foreach loop because I have not idea how many data in table so I use foreach loop now I want to add a new column for some but I have no idea please help

Leon D · Answer

You can use Free Spire.XLS ( https://www.nuget.org/packages/FreeSpire.XLS/ ) to insert datatable to excel and add sum of column to a new row, refer the following code example. Workbook workbook = new Workbook(); workbook.LoadFromFile(filePath); Worksheet sheet = workbook.Worksheets[0]; sheet.InsertDatatable( dtCustomDetail, true, 2, 1 ) sheet.Range[ "D7" ].Formula= "=SUM(D3:D6)" ; workbook.SaveToFile( "output.xlsx" , ExcelVersion.Version2013);