Description
The Interoperability services help our life very easy to work with COM Applications like Excel. .NET platform provides necessary services to use existent application written in visual basic, C++ and so on. In this application, we will see how we can transfer data from Microsoft SQL Server into Excel spreadsheet. To use the Excel in your .NET application. The first step is to create a reference in your project to Excel 9.0 Objects Library. This can be done by right mouse clicking on the References in the Solution Explorer and choosing Add Reference from pop up menu. Then you can choose the COM Tab and pick Microsoft Excel 9.0 Objects Library.
This will add necessary interop files into bin folder and into References folder in the Solution Explorer.
Now, we can start writing code, starting with instantiating Excel object. the following code will create an instance of Excel object.
Excel.Application excel= new Excel.Application();
After that, we need to add WorkBook into Excel object. the following code will do it.
excel.Application.Workbooks.Add(true);
Now we know that we have Excel object and its workbook to work and run our simple application. the next step is to get the Data From database. the following code will retrieve customer data from NorthWind database.
private System.Data.DataTable GetData()
{
SqlConnection conn= new SqlConnection(@"server=(local)\vste;uid=sa;pwd=;database=northwind;");
SqlDataAdapter adapter= new SqlDataAdapter("select * from Customers",conn);
DataSet myDataSet= new DataSet();
try {
adapter.Fill(myDataSet,"Customer");
}
catch(Exception ex) {
MessageBox.Show(ex.ToString());
}
return myDataSet.Tables[0];
}
What we are doing here is that We have to instantiate a connection to the Database server by using SqlConnection. Note please make sure that when you run the application, change the Connection String.
After Connection has been setup, we should use SqlDataAdapter object to fill DataSet object. DataSet object will hold all the information about Customers Table.
now What, We have to insert these values into Excel Cells. How we can do it. The following code shows you how you can do it.
foreach(DataColumn col in table.Columns)
{
colIndex++; excel.Cells[1,colIndex]=col.ColumnName;
}
foreach(DataRow row in table.Rows) {
rowIndex++; colIndex=0;
foreach(DataColumn col in table.Columns) {
colIndex++; excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
as you can see that first we insert the Field names into first row of Excel object. After that, we can start inserting data to Excel by starting from second row. For each row, we have to iterate DataColumns in DataTable and insert them into Excel columns.
That is all folks. Have fun!!!!