How to export datagridview to excel using vb.net?
Solution 1
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
Solution 2
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
Solution 3
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
Solution 4
Regarding your need to 'print directly from datagridview', check out this article on CodeProject:
There are a number of similar articles but I've had luck with the one I linked.
Solution 5
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.
Admin
Updated on August 26, 2020Comments
-
Admin over 3 years
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?
-
Michael Z. almost 8 yearsYou're writing everything to as text, why? Get rid of the .ToString
-
Michael Z. almost 8 yearsAlso, looping is the wrong approach. You need to create an object array and write that to a range in Excel. No loop necessary except to create the object array.
-
Ricardo González over 7 yearsAwesome options. I couldn't open the zip file. ReleaseComObject() is not defined in your posted code.
-
Michael Z. over 7 yearsJust go into the properties of the zip file and set it to safe or something. Windows does this because it came from the internet.
-
Michael Z. over 7 yearsReleaseCOMObject is a method in System.Runtime.InteropServices.Marshal
-
JT4U over 7 yearsThanks this helped!
-
BobSki over 7 years@michaelZ. do you possibly have an example?
-
Michael Z. over 7 yearsMy answer below has an example of creating an object array and then setting that to a range in Excel. If you don't need formatting then you should always export to CSV because it's significantly faster than Excel automation. I'm happy to help. Review my answer and let me know if you have any questions. The above answer is fine, but you should remove the .tostring so that The data type is correct within in Excel. Otherwise you have the equivalent of a CSV as everything is text.
-
nicolallias over 7 yearsHi, the CSV solution works great (after some tweaking). But I cannot get the Excel solution to work. And the link to your zip seems dead.
-
Michael Z. over 7 yearsMy son keeps restarting my machine that is running my server in a virtual machine. I will get it back up soon. Feel free to contact me directly for additional help.
-
Michael Z. over 7 yearsI moved my server VM to another machine so the downtime will be a lot less now.
-
can.do about 6 yearsGood job works well. gridResults definition was missing in the CSV.