question

jimbrown-net avatar image
0 Votes"
jimbrown-net asked CarlosCordero-8958 published

VB.NET Save DataGridView to Excel with headers

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

dotnet-visual-basicoffice-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jimbrown-net ,
May I know whether your issue has been solved or not? Please let me know if you need further assistance.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered Hobbyistprogrammer-7674 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is there a way to export with datagrid view colors and formatting. I mean exactly what i see in datagridview

0 Votes 0 ·
XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered CarlosCordero-8958 published

Hi @jimbrown-net ,
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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is there a way to export with datagrid view colors and formatting. I mean exactly what i see in datagridview

0 Votes 0 ·

awesome. works perfectly. thank you for this.

0 Votes 0 ·