How to export datagridview to excel using vb.net?

I have a datagridview in vb.net that is filled up from the database. I've researched and I found out that there is no built in support to print directly from datagridview. I don't want to use crystal report because I'm not familiar with it.

I'm planning to export it to excel to enable me to generate report from the datagridview.

Can you provide me ways to do this?


Code below creates Excel File and saves it in D: drive It uses Microsoft office 2007

FIRST ADD REFERRANCE (Microsoft office 12.0 object library ) to your project

Then Add code given bellow to the Export button click event-

Private Sub Export_Button_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles VIEW_Button.Click

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

    xlApp = New Microsoft.Office.Interop.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
            For k As Integer = 1 To DataGridView1.Columns.Count
                xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
            Next
        Next
    Next

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

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

    MsgBox("You can find the file D:\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

How to Export datagridview to Excel, The following vb.net source code shows how to Export the content of a datagridview to an Excel file. Next : How to Import data from Excel to DataGridView. How to Export DataGridView to Excel In VB.Net. This tutorial is all about how to Export DataGridView Data to Excel in VB.Net. In this tutorial, you will expect that I will teach you everything you need to learn on how to Export DataGridView Data to excel using visual basic.net. With this tutorial, it can answer the question raised in StackOverflow.


Excel Method

This method is different than many you will see. Others use a loop to write each cell and write the cells with text data type.

This method creates an object array from a DataTable or DataGridView and then writes the array to Excel. This means I can write to Excel without a loop and retain data types.

I extracted this from my library and I think I changed it enough to work with this code only, but more minor tweaking might be necessary. If you get errors just let me know and I'll correct them for you. Normally, I create an instance of my class and call these methods. If you would like to use my library then use this link to download it and if you need help just let me know. https://zomp.co/Files.aspx?ID=zExcel


After copying the code to your solution you will use it like this.

In your button code add this and change the names to your controls.

WriteDataGrid("Sheet1", grid)

To open your file after exporting use this line

System.Diagnostics.Process.Start("The location and filename of your file")

In the WriteArray method you'll want to change the line that saves the workbook to where you want to save it. Probably makes sense to add this as a parameter.

wb.SaveAs("C:\MyWorkbook.xlsx")


Public Function WriteArray(Sheet As String, ByRef ObjectArray As Object(,)) As String
    Try
        Dim xl As Excel.Application = New Excel.Application
        Dim wb As Excel.Workbook = xl.Workbooks.Add()
        Dim ws As Excel.Worksheet = wb.Worksheets.Add()
        ws.Name = Sheet
        Dim range As Excel.Range = ws.Range("A1").Resize(ObjectArray.GetLength(0), ObjectArray.GetLength(1))
        range.Value = ObjectArray

        range = ws.Range("A1").Resize(1, ObjectArray.GetLength(1) - 1)

        range.Interior.Color = RGB(0, 70, 132)  'Con-way Blue
        range.Font.Color = RGB(Drawing.Color.White.R, Drawing.Color.White.G, Drawing.Color.White.B)
        range.Font.Bold = True
        range.WrapText = True

        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
        range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

        range.Application.ActiveWindow.SplitColumn = 0
        range.Application.ActiveWindow.SplitRow = 1
        range.Application.ActiveWindow.FreezePanes = True

        wb.SaveAs("C:\MyWorkbook.xlsx")
        wb.CLose()
        xl.Quit()
        xl = Nothing
        wb = Nothing
        ws  = Nothing
        range = Nothing
        ReleaseComObject(xl)
        ReleaseComObject(wb)
        ReleaseComObject(ws)
        ReleaseComObject(range)

        Return ""
    Catch ex As Exception
        Return "WriteArray()" & Environment.NewLine & Environment.NewLine & ex.Message
    End Try
End Function

Public Function WriteDataGrid(SheetName As String, ByRef dt As DataGridView) As String
        Try
            Dim l(dt.Rows.Count + 1, dt.Columns.Count) As Object
            For c As Integer = 0 To dt.Columns.Count - 1
                l(0, c) = dt.Columns(c).HeaderText
            Next

            For r As Integer = 1 To dt.Rows.Count
                For c As Integer = 0 To dt.Columns.Count - 1
                    l(r, c) = dt.Rows(r - 1).Cells(c)
                Next
            Next

            Dim errors As String = WriteArray(SheetName, l)
            If errors <> "" Then
                Return errors
            End If

            Return ""
        Catch ex As Exception
            Return "WriteDataGrid()" & Environment.NewLine & Environment.NewLine & ex.Message
        End Try
    End Function


 Public Function WriteDataTable(SheetName As String, ByRef dt As DataTable) As String
        Try
            Dim l(dt.Rows.Count + 1, dt.Columns.Count) As Object
            For c As Integer = 0 To dt.Columns.Count - 1
                l(0, c) = dt.Columns(c).ColumnName
            Next

            For r As Integer = 1 To dt.Rows.Count
                For c As Integer = 0 To dt.Columns.Count - 1
                    l(r, c) = dt.Rows(r - 1).Item(c)
                Next
            Next

            Dim errors As String = WriteArray(SheetName, l)
            If errors <> "" Then
                Return errors
            End If

            Return ""
        Catch ex As Exception
            Return "WriteDataTable()" & Environment.NewLine & Environment.NewLine & ex.Message
        End Try
    End Function

I actually don't use this method in my Database program because it's a slow method when you have a lot of rows/columns. I instead create a CSV from the DataGridView. Writing to Excel with Excel Automation is only useful if you need to format the data and cells otherwise you should use CSV. You can use the code after the image for CSV export.


CSV Method

Private Sub DataGridToCSV(ByRef dt As DataGridView, Qualifier As String)  
        Dim TempDirectory As String = "A temp Directory"  
        System.IO.Directory.CreateDirectory(TempDirectory)
        Dim oWrite As System.IO.StreamWriter
        Dim file As String = System.IO.Path.GetRandomFileName & ".csv"
        oWrite = IO.File.CreateText(TempDirectory & "\" & file)

        Dim CSV As StringBuilder = New StringBuilder()

        Dim i As Integer = 1
        Dim CSVHeader As StringBuilder = New StringBuilder()
        For Each c As DataGridViewColumn In dt.Columns
            If i = 1 Then
                CSVHeader.Append(Qualifier & c.HeaderText.ToString() & Qualifier)
            Else
                CSVHeader.Append("," & Qualifier & c.HeaderText.ToString() & Qualifier)
            End If
            i += 1
        Next

        'CSV.AppendLine(CSVHeader.ToString())
        oWrite.WriteLine(CSVHeader.ToString())
        oWrite.Flush()

        For r As Integer = 0 To dt.Rows.Count - 1

            Dim CSVLine As StringBuilder = New StringBuilder()
            Dim s As String = ""
            For c As Integer = 0 To dt.Columns.Count - 1
                If c = 0 Then
                    'CSVLine.Append(Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier)
                    s = s & Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier
                Else
                    'CSVLine.Append("," & Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier)
                    s = s & "," & Qualifier & gridResults.Rows(r).Cells(c).Value.ToString() & Qualifier
                End If

            Next
            oWrite.WriteLine(s)
            oWrite.Flush()
            'CSV.AppendLine(CSVLine.ToString())
            'CSVLine.Clear()
        Next

        'oWrite.Write(CSV.ToString())

        oWrite.Close()
        oWrite = Nothing    

        System.Diagnostics.Process.Start(TempDirectory & "\" & file)   

        GC.Collect()

    End Sub

Visual Basic .Net, Visual Basic .Net | Export DataGridView to Excel and Import Excel to DataGridView with VB Duration: 17:01 Posted: Feb 23, 2019 How to export from DataGridView to excel using VB.net By: Issac Printer Friendly Format 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.


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DATAGRIDVIEW_TO_EXCEL((DataGridView1)) ' PARAMETER: YOUR DATAGRIDVIEW
End Sub

Private Sub DATAGRIDVIEW_TO_EXCEL(ByVal DGV As DataGridView)
    Try
        Dim DTB = New DataTable, RWS As Integer, CLS As Integer

        For CLS = 0 To DGV.ColumnCount - 1 ' COLUMNS OF DTB
            DTB.Columns.Add(DGV.Columns(CLS).Name.ToString)
        Next

        Dim DRW As DataRow

        For RWS = 0 To DGV.Rows.Count - 1 ' FILL DTB WITH DATAGRIDVIEW
            DRW = DTB.NewRow

            For CLS = 0 To DGV.ColumnCount - 1
                Try
                    DRW(DTB.Columns(CLS).ColumnName.ToString) = DGV.Rows(RWS).Cells(CLS).Value.ToString
                Catch ex As Exception

                End Try
            Next

            DTB.Rows.Add(DRW)
        Next

        DTB.AcceptChanges()

        Dim DST As New DataSet
        DST.Tables.Add(DTB)
        Dim FLE As String = "" ' PATH AND FILE NAME WHERE THE XML WIL BE CREATED (EXEMPLE: C:\REPS\XML.xml)
        DTB.WriteXml(FLE)
        Dim EXL As String = "" ' PATH OF/ EXCEL.EXE IN YOUR MICROSOFT OFFICE
        Shell(Chr(34) & EXL & Chr(34) & " " & Chr(34) & FLE & Chr(34), vbNormalFocus) ' OPEN XML WITH EXCEL

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

How to export data from datagridview to excel in vb.net, NET How to export datagridview values to excel file in VB.NET Code download link Duration: 5:32 Posted: Nov 30, 2018 Export DataGridView to Excel in VB.net 2.0 Web Form. HOW TO EXPORT DATA FROM DATAGRIDVIEW TO EXCELL USE SCRIPT VB.NET. increase speed to export datagridview to excel.


Regarding your need to 'print directly from datagridview', check out this article on CodeProject:

The DataGridViewPrinter Class

There are a number of similar articles but I've had luck with the one I linked.

Export datagridview to excel vb.net, In the run time select all cells content (Ctrl+A) and copy (Ctrl+C) and paste to the Excel Program. Let the Excel do the rest :) Sorry for the inconvenient, I have been searching the method to print data directly from the datagridvew (create report from vb.net VB2012) and have not found the satisfaction result. How to export DataGridView to Excel file in C# and VB.NET: Concept. EasyXLS™ library allows you to export a DataGridView to an Excel file. The exported data in cells can be formatted. EasyXLS can be successfully used inclusively to export large Excel files having big volume of data with fast exporting time.


The following code works fine for me :)

Protected Sub ExportToExcel(sender As Object, e As EventArgs) Handles ExportExcel.Click
        Try
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", "attachment;filename=ExportEthias.xls")
            Response.Charset = ""
            Response.ContentType = "application/vnd.ms-excel"
            Using sw As New StringWriter()
                Dim hw As New HtmlTextWriter(sw)
                GvActifs.RenderControl(hw)
                'Le format de base est le texte pour éviter les problèmes d'arrondis des nombres
                Dim style As String = "<style> .textmode { } </style>"
                Response.Write(Style)
                Response.Output.Write(sw.ToString())
                Response.Flush()
                Response.End()
            End Using
        Catch ex As Exception
            lblMessage.Text = "Erreur export Excel : " & ex.Message
        End Try
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered
    End Sub

Hopes this help you.

Export to Excel using VB.NET, Export data from VB.NET Dataset to Excel without using Datagrid/DataTable. Here, you will show how can we export data from gridview to excel sheet.


How to export DataGridView to Excel file in C# and VB.NET?, Code samples: Export DataGridView to Excel in C# and VB.NET from windows application. XLSX, XLSM, XLSB, XLS spreadsheets in .NET, including large  For exporting data from Datagridview to Excel, connect database and load data from database to Datagridview and create a new excel file and write the data from Datagridview to Excel file.


[SOLVE] How to Export DataGridView to Excel In VB.Net 2019 Projects, This 2019 tutorial is all about how Export DataGridView to Excel using VB.Net. I will teach you all about how Export DataGridView Data to Excel  Here Mudassar Ahmed Khan has explained with an example, how to export DataGridView data to Excel file with formatting i.e. Colors and Styles in Windows Forms (WinForms) Applications using C# and VB.Net. DataGridView cannot be exported directly to Excel file and hence need to generate a DataTable and export the DataTable to Excel file.


Fastest way to export a Datagridview (VB.NET 2015) to MS Excel , This is the method that i am using. Thank's for you time, again. Public Function GridAExcel(ByVal grd As DataGridView) Dim fichero As New  Hi, I have this below code used in exporting contents of datagridview to excel using VB.Net and works fine. However, when dealing with many records, example is 300 records, it takes time before the process of exporting will be finished.