How to export from DataGridView to excel using VB.net

By: Issac Emailed: 1700 times Printed: 2205 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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.

  vb.net_export_datagridview_toexcel.GIF

First step is to Load the Product table data to DataGridView, and create new Excel file and write the data from Datagridview to Excel file.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String

        connectionString = "data source=servername;" & _
        "initial catalog=databasename;user id=username;password=password;"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click


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

        xlApp = New 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
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

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

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

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


VB.net Home | All VB.net Tutorials | Latest VB.net Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(20)


1. View Comment

It's very helpful to me... Thanks...

View Tutorial          By: Nicks Patel at 2010-03-24 23:37:04
2. View Comment

Hello,

Is there anyway the datagridview can export the column name too?

Douglas


View Tutorial          By: Douglas at 2010-05-13 22:10:09
3. View Comment

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("Feuil1")

For Each col As DataGridViewColumn In DG_ENCOURS.Columns
'noms des colonnes
xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText
For Each rowa As DataGridViewRow In DG_ENCOURS.Rows
xlWorkSheet.Cells(rowa.Index + 2, col.Index + 1) = rowa.Cells(col.Index).Value
Next
Next
xlApp.Visible = True


View Tutorial          By: Zarno at 2010-12-24 07:11:19
4. View Comment

It too good code

View Tutorial          By: Srinivas at 2011-02-20 03:58:19
5. View Comment

Thanks to OP and Zarno for finishing off this code

View Tutorial          By: Soapy Balls at 2011-05-27 05:26:13
6. View Comment

error :

Object reference not set to an instance of an object.


View Tutorial          By: Ramnath at 2011-05-30 04:01:32
7. View Comment

Hello all..
can anyone please help me.. i just got this error..

xlWorkBook = xlApp.Workbooks.Add(misValue) >> Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))


View Tutorial          By: Christoven at 2011-06-15 05:55:16
8. View Comment

I have use the above code, Am getting the below error

Unable to cast COM Object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' of Interface type 'Microsoft.Office.Interop.Excel._Application'. This Operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error : could not be found. (Exception from HRESULT: 0x80030002(STG_E_FILENOTFOUND)).

Kindly anyone give me the solution


View Tutorial          By: RameshKumar at 2011-07-25 05:02:12
9. View Comment

add reference -> .net -> 'Microsoft.Office.Interop.Excel
then import Microsoft.Office.Interop.


View Tutorial          By: Ravi at 2011-08-27 12:17:36
10. View Comment

hi,
while exporting data i am getting error on below statement saying "Null Reference Exception was unhandled"

xlWorkSheet.Cells(i + 1, j + 1) =_ DataGridView1(j,i).Value.ToString()

help me outta this.......


View Tutorial          By: akash at 2011-12-22 07:28:05
11. View Comment

Thanks for your code. Nice working but I have one problem. I have time in DataGridview , In that condition it is not working. Without time it is working fine. Plz suggest me. I have urgent required.

Thanks


View Tutorial          By: Ravinder Kumar at 2012-01-02 18:01:21
12. View Comment

Thank you... I have the same problem , I have date and time in my datagridview, could not exported and display in excel. Only shows ########. tnx

View Tutorial          By: makoy at 2012-01-16 09:06:34
13. View Comment

I have a problem , i cant see the Microsoft.Office.Interop. in add reference -> .net -> .. i am using Visual Basic Express Edition (2008) .. there is no "Microsoft.Office.Interop.Excel" there , dont know what to do to be able to use that code for my project.. please help.. Thanks.

View Tutorial          By: Japz at 2012-01-16 09:51:11
14. View Comment

I am using VS 2010 Express and MS Excel 2007 wherein I am trying to export data from the DataGridView into an Excel file.

xlWorkSheet.SaveAs(âC:\ExportTagDetails.xlsxâ)

the above line of code works for me. But if I again try to create a file, it obviously shows the Message : âA file named âC:\ExportTagDetails.xlsxâ already exists in this location. Do you want to replace it?â
When I press Yes it creates no problem and calmly replaces itself in place of the old file. But if I click on No or Cancel then it throws an exception stating : âSystem.Runtime.Interop.Services.COMException(0x800A03EC): Exception from HRESULT: 0x800A03ECâ

Here is the entire block of code :
Private Sub cmd_export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_export.Click

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

xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets(âSheet1â³)
xlWorkSheet.Cells(1, 1).Font.Bold = True
xlWorkSheet.Cells(1, 1).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
xlWorkSheet.Cells(1, 2).Font.Bold = True
xlWorkSheet.Cells(1, 2).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
xlWorkSheet.Columns(1).ColumnWidth = 45
xlWorkSheet.Columns(2).ColumnWidth = 30
xlWorkSheet.Range(âAâ & row).Value = âTag Nameâ
xlWorkSheet.Range(âBâ & row).Value = âStart Valueâ
row = row + 1
Try
For i = 0 To DataGridView1.RowCount â 2
For j = 0 To DataGridView1.ColumnCount â 1
xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
Next
Next

xlWorkSheet.SaveAs(âC:\ExportTagDetails.xlsxâ)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
xlWorkBook.Close()
xlApp.Quit()

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

MsgBox(âYou can find the file at C:\ExportTagDetails.xlsxâ)
End Sub

Is there any way I can handle the issue.
Or can you provide me with a solution where the user can himself enter the name of the file and also specify the location.
Thanks in advance.


View Tutorial          By: Alphin at 2012-07-06 10:57:07
15. View Comment

sorry guys: how can i retrieve a report or large text from the data base?

View Tutorial          By: aidid at 2013-05-04 06:07:42
16. View Comment

Imports Excel = Microsoft.Office.Interop.Excel
Above command not working i could not get office after microsoft


View Tutorial          By: Nilesh at 2014-12-01 13:45:25
17. View Comment

Only first row is exported, help to export all the row

View Tutorial          By: Achyut at 2014-12-18 04:26:26
18. View Comment

' REFERENCIA EN EL PROYECTO (Microsoft Excel 12.0 Object Library)
' BUSCARLA EN PESTAÑA COM (C:Program Files (x86)Microsoft OfficeOffice12EXCEL>EXE)


Imports Excel = Microsoft.Office.Interop.Excel Imports Microsoft.Vbe.Interop

Public Class Form1

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
DTB.Columns.Add(DGV.Columns(CLS).Name.ToString)
Next

Dim DRW As DataRow

For RWS = 0 To DGV.Rows.Count - 1
DRW = DTB.NewRow

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

End Try
End If
Next

DTB.Rows.Add(DRW)
Next

DTB.AcceptChanges()

Dim DST As New DataSet
DST.Tables.Add(DTB)
DTB.WriteXml("C:MMS FILESRESOURCESXML.xml") ' SE CREA UN XML CON LA INFO DEL DATAGRIDVIEW
MACRO("C:MMS FILESRESOURCESXML.xml") ' Y ESTE PROCEDIMIENTO LO ABRE EN EXCEL

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

End Sub

Private Sub MACRO(ByVal FLE As String)
Try
Dim xlApp As Object = New Microsoft.Office.Interop.Excel.Application

Try ' OPCIONALMENTE LE DAS TAMAÑO A TU APLICACION DE EXCEL
xlApp.Left = 250
xlApp.Top = 100
xlApp.Width = 900
xlApp.Height = 550
Catch ex As Exception
' PROTEGIENDO EL PROCESO SI LA ULTIMA APLICACION FUE EN FULL SCREEN
End Try

Dim xlWb As Excel.Workbook = xlApp.Workbooks.Add ' AGREGAS TU LIBRO DE EXCEL

Dim MT As Integer ' Y OPCIONALMENTE LE DAS ESTILO Y FORMATO A SUS 3 HOJAS

For MT = 3 To 1 Step -1
xlWb.Sheets(MT).Select()
xlWb.Sheets(MT).cells.select()
xlWb.Sheets(MT).cells.Font.Name = "Arial"
xlWb.Sheets(MT).cells.Font.Size = 8
xlWb.Sheets(MT).cells.Font.bold = True
xlWb.Sheets(MT).Range("A1").Select()
Next

' LE CREAS UN MODULO LO NOMBRAS Y LE AGREGAS UNA MACRO DE ARRANQUE AL ABRIR

Dim xlMod As Microsoft.Vbe.Interop.VBComponent = xlWb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule)
xlMod.Name = "Module1"

Dim macroCode As String = _
"Public Sub Main()" & vbCrLf & _
" ActiveWorkbook.XmlImport URL:=" & Chr(34) & FLE & Chr(34) & ", ImportMap:=Nothing, Overwrite:=True, Destination:=Range(" & Chr(34) & "$A$1" & Chr(34) & ")" & vbCrLf & _
" ActiveSheet.ListObjects(" & Chr(34) & "Table1" & Chr(34) & ").TableStyle = " & Chr(34) & "TableStyleMedium12" & vbCrLf & _
" Sheet1.Range(" & Chr(34) & "A2" & Chr(34) & ").Select" & vbCrLf & _
" ActiveWindow.FreezePanes = True" & vbCrLf & _
"End Sub"

xlMod.CodeModule.AddFromString(macroCode)

xlApp.Visible = True
xlApp.Application.Run("Main")

' OPCIONALMENTE (RECOMENDADO DIRIA YO) BORRAS TU MODULO SALVO QUE QUIERAS VERLO DURANTE LAS PRUEBAS

Dim MDL As Object = xlApp.Application.VBE.ActiveVBProject.VBComponents
MDL.Remove(VBComponent:=MDL.Item("Module1"))

' Y CIERRAS ESTE PROCEDIMIENTO LIBERANDO LAS VARIABLES

ReleaseObject(xlApp)
ReleaseObject(xlMod)
ReleaseObject(xlWb)
ReleaseObject(MDL)

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

End Sub

Private Sub ReleaseObject(ByVal OBJ As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(OBJ)
Catch ex As Exception

End Try

OBJ = Nothing
GC.Collect()
End Sub

End Class


View Tutorial          By: Anonymous at 2015-04-21 03:10:41
19. View Comment

i got error for below line

xlApp = New Excel.ApplicationClass

Error : Interop type 'ApplicationClass' cannot be embedded. Use the applicable interface instead.

kindly anyone solve this error. why this error came for this line


View Tutorial          By: Satheyaraaj at 2015-04-28 07:47:42
20. View Comment

when I exported the data to excel there is a value in the first column false. how can I delete this column when I export the file?

View Tutorial          By: mizhelle at 2015-10-10 13:17:56

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Issac
Save/Write/Read image file from/to a database using Java program
How to modify the objects using java classes
Java program for Cloning
Java program for changeable wrapper class
A tutorial on Chat Server and Chat Client in VB.net
Chat Server in VB.net
Chat client in VB.net
How to open and read an XML file in VB.net
How to create an XML file in VB.net
XML and VB.net
How to create an XML file in VB.NET using Dataset
Multi Threaded Client Socket Programming in VB.net
Multi Threaded Server Socket Programming in VB.net
Multi threaded Socket Programming in VB.net
Client Socket Program sample in VB.net

More Tutorials in VB.net
Scope in VB.net
Unstructured Exception Handling in VB.net
Structured Exception Handling in VB.net
Creating Sub Procedures in VB.net
Creating Functions in VB.net
Passing a Variable Number of Arguments to Procedures in VB.net
Specifying Optional Arguments with default values in Procedures in VB.net
Preserving a Variable's Values between Procedure Calls in VB.net
Procedure Delegates in VB.net
Properties in VB.net
Understanding Scope in VB.net
Using Resume Next and Resume Line in VB.net
Using On Error GoTo 0 in VB.net
Getting an Exception's Number and Description in VB.net
Raising an Exception Intentionally in VB.net

More Latest News
Most Viewed Articles (in VB.net )
What's New in VB .NET? A comparison of VB vs VB.net
Data types in VB.net
Arrays and Dynamic Arrays in VB.net
If…Else Statements in VB.net
How to export from DataGridView to excel using VB.net
Multi threaded Socket Programming in VB.net
Multi Threaded Client Socket Programming in VB.net
Scope in VB.net
Creating Sub Procedures in VB.net
“Using If with And” and Comparing two integers using If
Nested If and Single line if statement
The Select Case statement in VB.net
The For Loop in VB.net
What is .NET Framework and the Common Language Runtime?
Debug and Release Versions in .NET
Most Emailed Articles (in VB.net)
Chat Server in VB.net
Procedure Delegates in VB.net
File operations in VB.net
Excel 2007 Data Validation Input Message using VB.net
Multi threaded Socket Programming in VB.net
XML and VB.net
How to create an XML file in VB.net
A tutorial on Chat Server and Chat Client in VB.net
Creating a Web Service in VB.net
“Using If with And” and Comparing two integers using If
Nested If and Single line if statement
The Select Case statement in VB.net
The For Loop in VB.net
What's New in VB .NET? A comparison of VB vs VB.net
What is .NET Framework and the Common Language Runtime?