Hi fellows, this post is about exporting the content of your data source (dataset, data table, data grid) into MS-Excel using VB.NET. All you need to is to read the data from your data source into a 2-dimensional array and then send the content straight to excel. With this you don’t have to be adding rows to excel one after the other.
Let’s start, we have a data table called “dsstudents” with 5 columns “IndexNo, Name, Year Group, Program, Gender”. We then declare a variable Private Students(,) As String as our 2-dimensional array. We are then ready to go (code). Note that we are using late binding to the excel objects here, thus we are not referencing the excel object at the design time but it’s done in runtime.
Below are two methods that will get the job done. Cheers!!!!
Private Sub readContent()
If Me.dsStudent.Rows.Count > 0 Then
ReDim Me.courses(Me.dsStudent.Rows.Count + 2, 7)
For i As Integer = 0 To
Me.dsStudent.Rows.Count – 1
‘setting the column headers for each column
students(0, 0) = "Index No"
students(0, 1) = "Students’ Name"
students(0, 5) = "Year group"
students(0, 6) = "Program Name"
students(0, 11) = "Student Gender"
‘getting the actual content from our datasource
students(i + 2, 0) = Me.dsStudent.Rows(i)(0).ToString
students(i + 2, 1) = Me.dsStudent.Rows(i)(1).ToString
students(i + 2, 3) = Me.dsStudent.Rows(i)(2).ToString
students(i + 2, 4) = Me.dsStudent.Rows(i)(3).ToString
students(i + 2, 5) = Me.dsStudent.Rows(i)(4).ToString
The implementation of the createExcel() method
Private Sub createExcel()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
‘Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
‘Add headers to the worksheet on row 1.
oSheet = oBook.Worksheets(1)
oSheet.Range("c1").Value = "EXPORTING DATASOURCE CONTENT INTO EXCEL"
‘Sending the data to Excel by specifying where to start from (cell) and the rows and ‘columns need.
oSheet.Range("B8").Resize(Me.dsStudent.Rows.Count + 2, 7).Value = Me.setudent
oExcel.visible = True
oSheet = Nothing
oBook = Nothing
oExcel = Nothing