Programming Tutorials

How to export from DataGridView to excel using VB.net

By: Issac in VB.net Tutorials on 2009-02-21  

For exporting data from Datagridview to Excel, connect the database and load data from the database to Datagridview and then create a new excel file and write the data from Datagridview to Excel file.

First step is to Load the Product table data to DataGridView, and create new Excel file and write the data from Datagridview to Excel file.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles
Button1.Click

 Dim cnn As SqlConnection
 Dim connectionString As String
 Dim sql As String

 connectionString = "data source=servername;"
& _
 "initial catalog=databasename;user
id=username;password=password;"
 cnn = New
SqlConnection(connectionString)
 cnn.Open()
 sql = "SELECT * FROM
Product"
 Dim dscmd As New SqlDataAdapter(sql,
cnn)
 Dim ds As New DataSet
 dscmd.Fill(ds)
 DataGridView1.DataSource =
ds.Tables(0)
 cnn.Close()
 End Sub

 Private Sub Button2_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button2.Click


 Dim xlApp As Excel.Application
 Dim xlWorkBook As Excel.Workbook
 Dim xlWorkSheet As Excel.Worksheet
 Dim misValue As Object =
System.Reflection.Missing.Value
 Dim i As Integer
 Dim j As Integer

 xlApp = New Excel.ApplicationClass
 xlWorkBook =
xlApp.Workbooks.Add(misValue)
 xlWorkSheet = xlWorkBook.Sheets("sheet1")

 For i = 0 To DataGridView1.RowCount -
2
 For j = 0 To
DataGridView1.ColumnCount - 1

xlWorkSheet.Cells(i + 1, j + 1) = _

DataGridView1(j, i).Value.ToString()
 Next
 Next

 xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
 xlWorkBook.Close()
 xlApp.Quit()

 releaseObject(xlApp)
 releaseObject(xlWorkBook)
 releaseObject(xlWorkSheet)

 MsgBox("You can find the file
C:\vbexcel.xlsx")
 End Sub

 Private Sub releaseObject(ByVal obj As Object)
 Try

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
 obj = Nothing
 Catch ex As Exception
 obj = Nothing
 Finally
 GC.Collect()
 End Try
 End Sub
End Class