How to export DatagridView table values to Excel in VB.Net?

Usually when I’m dealing with reports, I use ReportViewer and RDLC to show and print my data. From there, I can either print it on demand, or export it on any form I want (Excel, CSV, WORD, PDF). But in this case, we will save DataGridView data directly to Excel without using ReportViewer.

Prerequisite: You should know how to show data from Database to DataGridView via SQL queries. Once you have shown your desired DataTable, its time to export this data.

  1. By default, your DataGridView control name is DataGridView1.
  2. Create a button, name it cmdExport.
  3. Copy the following codes:
Private Sub cmdExport_Click(sender As Object, e As EventArgs) Handles cmdExport.Click
        'declare excel
        Dim excelApplication As Microsoft.Office.Interop.Excel.Application
        Dim excelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim excelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim missingVal As Object = System.Reflection.Missing.Value
        Dim dgRowCount As Integer
        Dim dgColCount As Integer

        excelApplication = New Microsoft.Office.Interop.Excel.Application
        excelWorkBook = excelApplication.Workbooks.Add(missingVal)
        excelWorkSheet = excelWorkBook.Sheets("Sheet1")

        'loop on every row
        For dgRowCount = 0 To DataGridView1.RowCount - 2
            'loop on every column
            For dgColCount = 0 To DataGridView1.ColumnCount - 1
                'loop on every column
                For dgCols As Integer = 1 To DataGridView1.Columns.Count
                    excelWorkSheet.Cells(1, dgCols) = DataGridView1.Columns(dgCols - 1).HeaderText
                    excelWorkSheet.Cells(dgRowCount + 2, dgColCount + 1) = DataGridView1(dgColCount, dgRowCount).Value.ToString()
                Next
            Next
        Next

        'save the data
        excelWorkSheet.SaveAs("C:\Exported Data\data.xlsx")
        excelWorkBook.Close()
        excelApplication.Quit()

        'release the file to prevent file lock error
        releaseObject(excelApplication)
        releaseObject(excelWorkBook)
        releaseObject(excelWorkSheet)

        'congrats!
        MsgBox("DatagridView successfully exported!", vbOKOnly, "Curbside Coder")
    End Sub

Published by Christian Foster

Code-blooded, coffee-lover, tall, dark and chubby. I love to draw, has motion-sickness and a sleepy-head. BTW, graduate of BS Computer Science, Associate in Computer Science and certified UiPath RPA Developer. Loyal to my partner and a father of a cute bouncing baby daughter!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website with WordPress.com
Get started
%d bloggers like this: