Click here to Skip to main content
15,917,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I found a great code snippet here on TCP on how to export a datagrid to an excel file, the code works great except it will not export the headers.

VB
Dim i, j As Integer
Dim xlApp As excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
Dim ds As DataSet
ds = MultiExportDataSet
For i = 0 To ds.Tables(0).Rows.Count - 1
    For j = 0 To ds.Tables(0).Columns.Count - 1
        xlWorkSheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)
    Next
Next
Dim saveFileDialog1 As New SaveFileDialog()
saveFileDialog1.Filter = "Excel Files (*.xlsx)|*.xlsx"
saveFileDialog1.FilterIndex = 2
saveFileDialog1.RestoreDirectory = True
If saveFileDialog1.ShowDialog() = DialogResult.OK Then
    xlSave = saveFileDialog1.FileName.ToString
End If

xlWorkSheet.SaveAs(xlSave)
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)




does anyone know how to get the datagrid headers to export into the excel file? they do not have to be the "Headers" in the excel sheet, they can be the first row values.
Posted

1 solution

Before you loop through the data, loop through the columns once:

VB
For i = 0 To ds.Tables(0).Columns.Count - 1
   dxlWorkSheet.Cells(1, i) = ds.Tables(0).Columns.ColumnName
Next


Change the loop you wrote for the data to start one row below this.

Change This
<br />
xlWorkSheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)<br />


To This
<br />
xlWorkSheet.Cells(i + <big>2</big>, j + 1) = ds.Tables(0).Rows(i).Item(j)<br />
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900