Excel Method
This method is different from many that you will see. Others use a loop to write each cell and write cells with the text data type.
This method creates an array of objects from a DataTable or DataGridView and then writes the array to Excel. This means that I can write in Excel without a loop and save data types.
I extracted this from my library and think that I have changed it so that it only works with this code, but it may require more fine-tuning. If you get errors, just let me know and I will fix them for you. I usually create an instance of my class and call these methods. If you want to use my library, 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 into your solution, you will use it as follows.
In your button code, add this and change the names for your controls.
WriteDataGrid("Sheet1", grid)
To open a file after export, use this line
System.Diagnostics.Process.Start("The location and filename of your file")
In the WriteArray method WriteArray you will want to change the line in which the workbook is saved to where you want to save it. 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 do not use this method in my database program because it is a slow method when you have many rows / columns. Instead, I create a CSV from a DataGridView. Writing to Excel using Excel Automation is only useful if you need to format data and cells, otherwise you should use CSV. You can use the code after the image to export to CSV.
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