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/