Share via

VB.NET Save DataGridView to Excel with headers

jim brown 271 Reputation points
2021-05-14T19:30:28.343+00:00

I would like to export / Save DataGridView to Excel. The below code works but it does not export DGV headers. 

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

            Dim i As Int16, j As Int16

            '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

            xlApp.DisplayAlerts = False
            xlWorkBook.SaveAs(filename.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
Microsoft 365 and Office | Excel | For business | Windows
Developer technologies | VB

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Karen Payne MVP 35,606 Reputation points Volunteer Moderator
    2021-05-14T20:15:10.033+00:00

    A better way is shown in the following code sample which uses a completely free NuGet package SpreadSheetLight which does not require Excel to be installed.

    1 person found this answer helpful.
  2. Xingyu Zhao-MSFT 5,381 Reputation points
    2021-05-17T03:02:02.51+00:00

    Hi @jim brown ,
    Here's an example you can refer to. 

            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.Application  
        xlWorkBook = xlapp.Workbooks.Add(misValue)  
        xlWorkSheet = CType(xlWorkBook.Sheets("Sheet1"), Excel.Worksheet)  
  
        For k = 0 To dgv1.ColumnCount - 1  
            xlWorkSheet.Cells(1, k + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter  
            xlWorkSheet.Cells(1, k + 1) = dgv1.Columns(k).Name  
        Next  
        For i = 0 To dgv1.RowCount - 1  
            For j = 0 To dgv1.ColumnCount - 1  
                xlWorkSheet.Cells(i + 2, j + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter  
                xlWorkSheet.Cells(i + 2, j + 1) =  
                    dgv1(j, i).Value.ToString()  
            Next  
        Next  
  
        Dim SaveFileDialog1 As New SaveFileDialog()  
        SaveFileDialog1.Filter = "Execl files (*.xlsx)|*.xlsx"  
        SaveFileDialog1.FilterIndex = 2  
        SaveFileDialog1.RestoreDirectory = True  
        If SaveFileDialog1.ShowDialog() = DialogResult.OK Then  
            xlWorkSheet.SaveAs(SaveFileDialog1.FileName)  
            MsgBox("Save file success")  
        Else  
            Return  
        End If  
        xlWorkBook.Close()  
        xlapp.Quit()

    Best Regards,
    Xingyu Zhao
    *
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.