How to Export DataGridView to Excel in VB.NET

Getting your data from a VB.NET application into a format users can easily work with is a common requirement, and there’s no destination more common than Microsoft Excel. This article will guide you through three different methods for exporting a DataGridView to an Excel file. We'll cover everything from a quick and simple CSV export to a more powerful approach that gives you full control over formatting.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Set Up Your Sample Project

Before we start, let's assume you have a simple VB.NET Windows Forms project with a DataGridView control on a form. For this tutorial, we'll populate it with some sample data when the form loads.

In your form's Load event, add this code to create a simple table:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' Set up some columns
    DataGridView1.Columns.Add("ID", "Product ID")
    DataGridView1.Columns.Add("Name", "Product Name")
    DataGridView1.Columns.Add("Price", "Price")
    DataGridView1.Columns.Add("Stock", "Units in Stock")

    ' Add some sample data rows
    DataGridView1.Rows.Add("P101", "Laptop", "999.00", "50")
    DataGridView1.Rows.Add("P102", "Keyboard", "49.99", "250")
    DataGridView1.Rows.Add("P103", "Mouse", "25.50", "300")
    DataGridView1.Rows.Add("P104", "Monitor", "249.95", "120")
    DataGridView1.Rows.Add("P105", "Webcam, Premium", "89.00", "75")

    ' Let's also add an "Export" button to the form.
    ' We will add the code to its click event later.
End Sub

Method 1: The Simple CSV Export (No External Libraries Needed)

The fastest and most straightforward way to get data into a format Excel can read is by exporting it as a CSV (Comma-Separated Values) file. This method doesn't require any third-party libraries or even Excel to be installed on the machine. It simply writes the data to a text file with commas separating each value.

Pros:

• Extremely simple and fast.
• No dependencies needed.
• The resulting file can be opened by almost any spreadsheet program.

Cons:

• No formatting (fonts, colors, cell sizes) is preserved.
• All data is exported as text.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step for CSV Export

Add a button named btnExportCSV to your form and double-click it to create its Click event handler. Paste the following code inside:

Private Sub btnExportCsv_Click(sender As Object, e As EventArgs) Handles btnExportCSV.Click
    If DataGridView1.Rows.Count > 0 Then
        Dim sb As New System.Text.StringBuilder()

        '--- Get Headers ---'
        Dim headers = (From
            header As DataGridViewColumn
            In
            DataGridView1.Columns.Cast(Of DataGridViewColumn)()
            Select header.HeaderText.Replace(",", String.Empty)).ToArray()
        sb.AppendLine(String.Join(",", headers))

        '--- Get Rows ---'
        For Each row As DataGridViewRow In DataGridView1.Rows
            ' Exclude the new row at the end if it's there
            If Not row.IsNewRow Then
                Dim cells = (From
                    cell As DataGridViewCell
                    In
                    row.Cells.Cast(Of DataGridViewCell)()
                    Select If(cell.Value IsNot Nothing, EncloseInQuotes(cell.Value.ToString()), "")).ToArray()
                sb.AppendLine(String.Join(",", cells))
            End If
        Next

        '--- Save the file ---'
        Try
            System.IO.File.WriteAllText("D:\exports\datagrid.csv", sb.ToString())
            MessageBox.Show("CSV file saved successfully!", "Export Complete")
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message, "Export Error")
        End Try
    End If
End Sub

Helper function to handle commas within your data:

Private Function EncloseInQuotes(ByVal value As String) As String
    If value.Contains(",") Then
        Return String.Format("""{0}""", value)
    End If
    Return value
End Function

This code loops through the headers and then each row of the DataGridView. It uses a StringBuilder for efficiency, joins the cell values with commas, and writes the final string to a file. The helper function EncloseInQuotes is important because it prevents commas in your data (like in "Webcam, Premium") from breaking the CSV structure.

Method 2: Using the Excel Interop Library for Full Control

If you need an actual .xlsx file and want to control things like font styles, column widths, or even add formulas, you can automate Excel directly using the Office Interop library. This gives you immense power but comes with a major dependency: Excel must be installed on the machine running the code.

Pros:

• Creates a native Excel (.xlsx) file.
• Gives you complete control over formatting, formulas, and charts.
• You can automate almost any task you could do manually in Excel.

Cons:

• Requires Excel to be installed on the user's computer.
• Can be slower with very large datasets.
• Requires careful memory management to avoid leaving Excel processes running in the background.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step for Interop Export

1. Add the Reference

First, you need to tell your project how to talk to Excel.

Steps:

1. In the Solution Explorer, right-click on your project and select "Add Reference..."
2. Click on the "COM" tab on the left.
3. Scroll down and find "Microsoft Excel X.X Object Library" (the version number will depend on your installed version of Office). Check the box and click OK.

2. Write the Export Code

Add a new button named btnExportInterop and place this code in its Click event. Note the use of Try...Finally to ensure the Excel objects are released properly, which is crucial!

Imports Microsoft.Office.Interop

Private Sub btnExportInterop_Click(sender As Object, e As EventArgs) Handles btnExportInterop.Click
    If DataGridView1.Rows.Count = 0 Then
        MessageBox.Show("No data to export.", "Warning")
        Return
    End If

    Dim xlApp As Excel.Application = Nothing
    Dim xlWorkBook As Excel.Workbook = Nothing
    Dim xlWorkSheet As Excel.Worksheet = Nothing

    Try
        xlApp = New Excel.Application()
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)

        '--- Add Headers ---'
        For i As Integer = 0 To DataGridView1.Columns.Count - 1
            xlWorkSheet.Cells(1, i + 1) = DataGridView1.Columns(i).HeaderText
            ' Optional: Make headers bold
            CType(xlWorkSheet.Cells(1, i + 1), Excel.Range).Font.Bold = True
        Next

        '--- Add Rows ---'
        For r As Integer = 0 To DataGridView1.Rows.Count - 2 ' -2 to skip the empty new row
            For c As Integer = 0 To DataGridView1.Columns.Count - 1
                Dim cellValue = DataGridView1.Rows(r).Cells(c).Value
                If cellValue IsNot Nothing Then
                    xlWorkSheet.Cells(r + 2, c + 1) = cellValue.ToString()
                End If
            Next
        Next

        ' Optional: Auto-fit columns for better readability
        xlWorkSheet.Columns.AutoFit()

        ' Save the workbook or make it visible to the user
        ' Option 1: Save directly
        ' xlWorkBook.SaveAs("D:\exports\datagrid.xlsx")
        
        ' Option 2: Make it visible to the user
        xlApp.Visible = True

    Catch ex As Exception
        MessageBox.Show("Error while exporting: " & ex.Message, "Error")
    Finally
        '--- CRUCIAL: Release the COM objects to avoid leaving Excel.exe running ---'
        If xlWorkSheet IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
        If xlWorkBook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
        If xlApp IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

        xlWorkSheet = Nothing
        xlWorkBook = Nothing
        xlApp = Nothing

        GC.Collect()
    End Try
End Sub

Method 3: Using a Third-Party Library (The Modern Approach)

This method is often the best of both worlds. Using a third-party library like ClosedXML, you can create fully-featured .xlsx files without needing Excel installed on the machine. These libraries are generally faster, more reliable, and perfect for applications that might run on a server or on machines where you can't guarantee Office is present.

Pros:

• No dependency on having Excel installed.
• Fast and resource-efficient.
• Can create complex, fully formatted XLSX files.
• Avoids the complexity of managing COM objects.

Cons:

• Requires adding a NuGet package to your project.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step for ClosedXML Export

1. Install the NuGet Package

Steps:

1. Go to "Tools" -> "NuGet Package Manager" -> "Manage NuGet Packages for Solution..."
2. Click the "Browse" tab.
3. Search for "ClosedXML".
4. Select it, check your project on the right, and click "Install".

2. Write the Export Code

ClosedXML works particularly well with DataTable objects. So, we'll create a small helper function to convert our DataGridView into a DataTable, then easily pass that to ClosedXML.

Add yet another button, btnExportClosedXML, and use the following code:

Imports ClosedXML.Excel

Private Sub btnExportClosedXML_Click(sender As Object, e As EventArgs) Handles btnExportClosedXML.Click
    If DataGridView1.Rows.Count = 0 Then Return

    Try
        '--- Convert DataGridView to DataTable ---'
        Dim dt As New DataTable("ProductData")
        ' Add columns
        For Each col As DataGridViewColumn In DataGridView1.Columns
            dt.Columns.Add(col.HeaderText)
        Next
        'Add rows
        For Each row As DataGridViewRow In DataGridView1.Rows
            If Not row.IsNewRow Then
                Dim dataRow = dt.NewRow()
                For i As Integer = 0 To DataGridView1.Columns.Count - 1
                    dataRow(i) = If(row.Cells(i).Value IsNot Nothing, row.Cells(i).Value, DBNull.Value)
                Next
                dt.Rows.Add(dataRow)
            End If
        Next
        
        '--- Use ClosedXML to export DataTable ---'
        Using wb As New XLWorkbook()
            Dim ws = wb.Worksheets.Add(dt)
            
            ' Make header row bold and add a background color
            ws.Row(1).Style.Font.Bold = True
            ws.Row(1).Style.Fill.BackgroundColor = XLColor.LightGray
            
            ' Auto-adjust column widths
            ws.Columns().AdjustToContents()
            
            ' Save the document
            wb.SaveAs("D:\exports\datagrid-closedxml.xlsx")
        End Using

        MessageBox.Show("Excel file saved successfully using ClosedXML!", "Export Complete")

    Catch ex As Exception
        MessageBox.Show("Error: " & ex.Message, "ClosedXML Error")
    End Try
End Sub

Which Method Should You Choose?

• Use the CSV Export when you just need a quick data dump and have no formatting requirements. It's the simplest and most foolproof method.
• Use the Interop Library when building in-house tools for a corporate environment where you can guarantee every user has Excel installed, and you need advanced features like charts or macros.
• Use a Third-Party Library like ClosedXML for almost everything else. It provides the power and flexibility of a true .xlsx file without the dependencies and pitfalls of Interop, making it the most robust and professional choice for modern applications.

Final Thoughts

Learning how to export your DataGridView to Excel is a fundamental skill for any VB.NET developer building line-of-business applications. As we've seen, you have multiple ways to accomplish this, each with unique strengths. By choosing the right method, you can provide users with the data they need in a format they already know and use.

For one-off exports, these manual methods work perfectly. But if you find your team constantly exporting CSVs, managing spreadsheets, and trying to stitch together reports from different software, the process itself becomes a major bottleneck. To address this, we built Graphed to automate the entire reporting pipeline. It connects directly to your marketing and sales data sources (like Google Analytics, Salesforce, Shopify, etc.) and allows you to build real-time, shareable dashboards instantly by just describing what you want to see in plain English. No more exporting, no more wrangling - just live data and clear answers.