How to Export DataGridView Data to Excel using VB.Net

0
420

This tutorial is all about how Export DataGridView Data to Excel using VB.Net
I will teach you today everything you need to learn on how to Export DataGridView Data to excel. So let’s get started:

  • First, Open the Visual Basic, Select File on the menu, then click New and create a new project.

 

  • Then a New Project Dialog will appear. You can rename your project, depending on what you like to name it. After that click OK.

  • Design your form just like this one I’ve shown you below.
    Drag a Datagridview and a Button from the toolbox.

  • Add a reference to Microsoft Excel 12.0 Object Library, In the project menu click on Project – Add Reference – go to COM tab,
    Add Microsoft Excel 12.0 Object Library
  • After that, add this following references above the Public Class Form1 Line.
  1. Imports System.Data.OleDb
  2. Imports Excel = Microsoft.Office.Interop.Excel
  • Then add this following declarations below the Public Class Form1 line.
  1. Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Clive\Documents\Visual Studio 2008\clive projects\Exportdatagridtoexcel\Exportdatagridtoexcel\bin\Debug\test.accdb"
  2.  
  3. Dim excelLocation As String = "C:\Users\Clive\Documents\Visual Studio 2008\clive projects\Exportdatagridtoexcel\Exportdatagridtoexcel\bin\Debug\test.xlsx"
  4. Dim Conn As OleDbConnection
  5. Dim da As OleDbDataAdapter
  6. Dim ds As DataSet
  7. Dim tables As DataTableCollection
  8. Dim source1 As New BindingSource
  9. Dim APP As New Excel.Application
  10. Dim worksheet As Excel.Worksheet
  11. Dim workbook As Excel.Workbook
  12.  
  13.  
  • Add this code to the form load event.
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2. workbook = APP.Workbooks.Open(excelLocation)
  3. worksheet = workbook.Worksheets("sheet1")
  4. Conn = New OleDbConnection
  5. Conn.ConnectionString = connString
  6. ds = New DataSet
  7. tables = ds.Tables
  8. da = New OleDbDataAdapter("Select * from [tblbio]", Conn)
  9. da.Fill(ds, "tblbio")
  10. Dim view As New DataView(tables(0))
  11. source1.DataSource = view
  12. DataGridView1.DataSource = view
  13. DataGridView1.AllowUserToAddRows = False
  • Next, add this code to the export button.
  1. Private Sub Btnexport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnexport.Click
  2.  
  3. Dim columnsCount As Integer = DataGridView1.Columns.Count
  4. For Each column In DataGridView1.Columns
  5. worksheet.Cells(1, column.Index + 1).Value = column.Name
  6. Next
  7. For i As Integer = 0 To DataGridView1.Rows.Count - 1
  8. Dim columnIndex As Integer = 0
  9. Do Until columnIndex = columnsCount
  10. worksheet.Cells(i + 2, columnIndex + 1).Value = DataGridView1.Item(columnIndex, i).Value.ToString
  11. columnIndex += 1
  12. Loop
  13. Next
  14. End Sub
  • Finally, add the following code to save the excel file when closing the form.
  1. Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
  2. workbook.Save()
  3. workbook.Close()
  4. APP.Quit()
  5. End Sub
  • Click F5 to run your project.

Output:

 

If you have any questions or suggestions about Export DataGridView Data to Excel using VB.Net please contact me through our contact page.

Download Export DataGridView Data to Excel using VB.Net Source code Here

 

 

Readers might read also:

 

 

 

Facebook Comments
(Visited 445 times, 4 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here