Excel add-in : Cell Formula


In this tutorial i'm going to show you how to   set a Excel cells formula using C#
link of last tutorial : Excel add-in & SQl server in C# 


Get adress of  First row :
 Range range_1 = (Range)wrksh.Cells[2, 3];

Get address of last row 
  Range range_N = (Range)wrksh.Cells[liste.Rows.Count + 1, 3];

set a  cell formula 
 wrksh.Cells[liste.Rows.Count + 2, 3].Formula = "=SUM("+range_1.Address+":"+range_N.Address+")";

Full code : 
 Application app = (Application)Marshal.GetActiveObject("Excel.Application");  
       Workbook wrk = app.ActiveWorkbook;  
       Worksheet wrksh = wrk.Sheets[1];  
       wrksh.Cells[1, 1] = "ID";  
       wrksh.Cells[1, 2] = "Name";  
       wrksh.Cells[1, 3] = "Age";  
       data.DataTable liste = getdata();  
       if (liste.Rows.Count > 0)  
       {  
         Range range_1 = (Range)wrksh.Cells[2, 3]; // Get adress of First row  
         for (int i = 0; i < liste.Rows.Count; i++)  
         {  
           wrksh.Cells[i+2, 1] = liste.Rows[i].ItemArray[0].ToString();  
           wrksh.Cells[i + 2, 2] = liste.Rows[i].ItemArray[1].ToString();  
           wrksh.Cells[i + 2, 3] = liste.Rows[i].ItemArray[2].ToString();  
         }  
         Range range_N = (Range)wrksh.Cells[liste.Rows.Count + 1, 3]; // Get address of last row  
         wrksh.Cells[liste.Rows.Count + 2, 3].Formula = "=SUM("+range_1.Address+":"+range_N.Address+")";  
       }

Khebbou