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. Right mouse clicking on the References in the Solution Explorer and choosing Add Reference from pop up menu can do this. 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.
Dim excel As 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 Function GetData() As System.Data.DataTable
Dim conn As New SqlConnection("server=(local)\vste;uid=sa;pwd=;database=northwind;")
Dim adapter As New SqlDataAdapter("select * from Customers", conn) '
Dim myDataSet As New DataSet
Try
adapter.Fill(myDataSet, "Customer")
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Return myDataSet.Tables(0)
End Function 'GetData
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.
Dim col As DataColumn
For Each col In table.Columns
colIndex += 1
excel.Cells(1, colIndex) = col.ColumnName
Next col
Dim row As DataRow
For Each row In table.Rows
rowIndex += 1
colIndex = 0
Dim col As DataColumn
For Each col In table.Columns
colIndex += 1
excel.Cells(rowIndex, colIndex) = row(col.ColumnName).ToString()
Next col
Next row
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!!!!!