Export DataGridView Data To Excel - Visual Basic .NET

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This is a tutorial on how to export data in datagridview to an excel sheet in visual basic .net

In this tutorial, first I populate Access table into a datagridview, then I export to my excel sheet (I use Excel 2007)

For this tutorial, you need to place a datagridview control and a button to your form

export datagridview to excel in visual basic .net

Result:

visual basic .net export datagridview to excel

First add a reference to Microsoft Excel 12.0 Object Library

In your project menu click on Project - Add Reference - go to COM tab

add excel reference in visual basic

Add Microsoft Excel 12.0 Object Library

Go to your code page and add the following references above your Public Class Form1 Line

Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel

Then add the following declarations below your Public Class Form1 line

	'Change "C:\Users\Jimmy\Documents\Merchandise.accdb" to your database location
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Jimmy\Desktop\test.accdb"
    'Change "C:\Users\Jimmy\Desktop\test.xlsx" to your excel file location
    Dim excelLocation As String = "C:\Users\Jimmy\Desktop\test.xlsx"
    Dim MyConn As OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook

Add the following form load event code:

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        workbook = APP.Workbooks.Open(excelLocation)
        worksheet = workbook.Worksheets("sheet1")
        MyConn = New OleDbConnection
        MyConn.ConnectionString = connString
        ds = New DataSet
        tables = ds.Tables
        da = New OleDbDataAdapter("Select * from [Items]", MyConn) 'Change items to your database name
        da.Fill(ds, "Items") 'Change items to your database name
        Dim view As New DataView(tables(0))
        source1.DataSource = view
        DataGridView1.DataSource = view
        DataGridView1.AllowUserToAddRows = False
    End Sub

Then add the following code that will handle your Export button click event

Private Sub Export_Click(sender As System.Object, e As System.EventArgs) Handles Export.Click
        'Export Header Names Start
        Dim columnsCount As Integer = DataGridView1.Columns.Count
        For Each column In DataGridView1.Columns
            worksheet.Cells(1, column.Index + 1).Value = column.Name
        Next
        'Export Header Name End


        'Export Each Row Start
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            Dim columnIndex As Integer = 0
            Do Until columnIndex = columnsCount
                worksheet.Cells(i + 2, columnIndex + 1).Value = DataGridView1.Item(columnIndex, i).Value.ToString
                columnIndex += 1
            Loop
        Next
        'Export Each Row End
    End Sub

Then add the following code to save your excel file when your close your form

 Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        workbook.Save()
        workbook.Close()
        APP.Quit()
    End Sub


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.