Can anybody help me how to import data from datagridview to Microsoft Excel along with the column names and the data. And another thing is the date becomes hashtags. i have to expand the column to show the date.
i have a button and when i press it Microsoft excel should open with the data exported.
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
Here is another solution that you can try, it uses GemBox.Spreadsheet library and it will keep the data type and the formatting that is used in the DataGridView:
Dim ef As New ExcelFile()
Dim ws = ef.Worksheets.Add("DGV Sheet")
' From DataGridView to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(ws, dataGridView1,
New ImportFromDataGridViewOptions() With {.ColumnHeaders = True})
' Auto fit excel columns.
Dim count As Integer = ws.CalculateMaxUsedColumns()
For index As Integer = 0 To count - 1
ws.Columns(index).AutoFit()
Next
ef.Save("DGV Book.xlsx")
Also here you can find another sample.
