Usually when I’m dealing with reports, I use ReportViewer and RDLC to show and print my data. From there, I can either print it on demand, or export it on any form I want (Excel, CSV, WORD, PDF). But in this case, we will save DataGridView data directly to Excel without using ReportViewer.
Prerequisite: You should know how to show data from Database to DataGridView via SQL queries. Once you have shown your desired DataTable, its time to export this data.
- By default, your DataGridView control name is DataGridView1.
- Create a button, name it cmdExport.
- Copy the following codes:
Private Sub cmdExport_Click(sender As Object, e As EventArgs) Handles cmdExport.Click
'declare excel
Dim excelApplication As Microsoft.Office.Interop.Excel.Application
Dim excelWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim excelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim missingVal As Object = System.Reflection.Missing.Value
Dim dgRowCount As Integer
Dim dgColCount As Integer
excelApplication = New Microsoft.Office.Interop.Excel.Application
excelWorkBook = excelApplication.Workbooks.Add(missingVal)
excelWorkSheet = excelWorkBook.Sheets("Sheet1")
'loop on every row
For dgRowCount = 0 To DataGridView1.RowCount - 2
'loop on every column
For dgColCount = 0 To DataGridView1.ColumnCount - 1
'loop on every column
For dgCols As Integer = 1 To DataGridView1.Columns.Count
excelWorkSheet.Cells(1, dgCols) = DataGridView1.Columns(dgCols - 1).HeaderText
excelWorkSheet.Cells(dgRowCount + 2, dgColCount + 1) = DataGridView1(dgColCount, dgRowCount).Value.ToString()
Next
Next
Next
'save the data
excelWorkSheet.SaveAs("C:\Exported Data\data.xlsx")
excelWorkBook.Close()
excelApplication.Quit()
'release the file to prevent file lock error
releaseObject(excelApplication)
releaseObject(excelWorkBook)
releaseObject(excelWorkSheet)
'congrats!
MsgBox("DatagridView successfully exported!", vbOKOnly, "Curbside Coder")
End Sub
Curbside Coder 