Excel add-in & SQl server in C#




In this post I will be showing a simple ways to  get data from sql server using an Excel
 add-in developed in C#



Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.(From Wikipedia)

Under visual studio create a new Excel add-in project  and add  a Ribbon then add a new button.

The Bloc code below contains  a method (GetData) to receive data from SQL server

GetData() Function :
 public data.DataTable getdata()  
     {  
       data.DataTable DATA = new data.DataTable();  
       string SQL = " Select * from T1 ";  
       SqlDataAdapter dat = new SqlDataAdapter(SQL, cnnx);  
       dat.Fill(DATA);  
       return DATA;  
     }  

In the button click event past the code beneath:
 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)  
       {  
         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();  
         }  
       }  





Download



For more information on Ajax: wikipedia.org, http://office.microsoft.com/