VB.NET Save DataGridView to Excel with headers

Question

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()

Answer 1

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.

Answer 2

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.