export datagridview header column to excel vb 2012

Question

i'm using visual studio 2012 and microsoft SQL server 2012 to export datagridview to excel using this coding:

Public Class Export

 Private Sub Export_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.AllowUserToAddRows = False
        ClassKoneksi.namadatabase = "KPIRWAN"
        Dim dssiswa As New DataSet
        Dim sql As String
        sql = "select*from Siswa order by NIS ASC"
        dssiswa = ClassSiswa.displayData(ClassSiswa.opencon, sql, "DataSiswa")
        DataGridView1.DataSource = dssiswa
        DataGridView1.DataMember = "DataSiswa"
        DataGridView1.ReadOnly = True
        ClassSiswa.closecon()
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally

            GC.Collect()

        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ExportExcel()
    End Sub

    Private Sub ExportExcel()
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim oValue As Object = System.Reflection.Missing.Value
        Dim sPath As String = String.Empty
        Dim dlgSave As New SaveFileDialog
        dlgSave.DefaultExt = "xls"
        dlgSave.Filter = "Microsoft Excel|*.xls"
        dlgSave.InitialDirectory = Application.StartupPath

        If dlgSave.ShowDialog = Windows.Forms.DialogResult.OK Then
            Try
                xlApp = New Microsoft.Office.Interop.Excel.Application
                xlBook = xlApp.Workbooks.Add(oValue)
                xlSheet = xlBook.Worksheets("sheet1")

                Dim xlRow As Long = 2
                Dim xlCol As Short = 1

                For k As Integer = 0 To DataGridView1.ColumnCount - 1
                    xlSheet.Cells(1, xlCol) = DataGridView1(k, 0).Value
                    xlCol += 1

                Next

                For k As Integer = 0 To DataGridView1.ColumnCount - 1
                    xlSheet.Cells(2, xlCol) = DataGridView1(k, 0).Value
                    xlCol += 1

                Next

                For i As Integer = 0 To DataGridView1.RowCount - 1
                    xlCol = 1

                    For k As Integer = 0 To DataGridView1.ColumnCount - 1
                        xlSheet.Cells(xlRow, xlCol) = DataGridView1(k, i).Value
                        xlCol += 1

                    Next

                    xlRow += 1

                Next

                xlSheet.Columns.AutoFit()
                Dim sFileName As String = Replace(dlgSave.FileName, ".xlsx", "xlx")

                xlSheet.SaveAs(sFileName)
                xlBook.Close()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlBook)
                releaseObject(xlSheet)

                MsgBox("Data successfully exported.", MsgBoxStyle.Information, "PRMS/SOB Date Tagging")
            Catch
                MsgBox(ErrorToString)
            Finally
            End Try
        End If
    End Sub
End Class

it work perfectly fine except when i exported the datagridview to excel the column header text in the datagridview is not exported to excel sheet only the gridview.

how do i make the coding to get the column header text to excel sheet?

Sastreen · Accepted Answer · 2015-05-26 14:31:35Z

0

I think you'll have to get the HeaderText from each column like this:

xlSheet.Cells(x,y).Value = DataGridView1.Columns(k).HeaderText

You would put this in your "k" loop. And then write it wherever you want in the file. x and y have to be the location where you write the header (maybe determined by k)

EDIT: writing the headers in first row of excel

For k As Integer = 0 To DataGridView1.ColumnCount - 1
    xlSheet.Cells(1,k+1).Value = DataGridView1.Columns(k).HeaderText
Next

edited May 26, 2015 at 14:31

answered May 26, 2015 at 11:55

Sastreen

5974 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Riki Salim Over a year ago

i put the DataGridView1.Columns(k).HeaderText in the k loop like this: For k As Integer = 0 To DataGridView1.Columns(k).HeaderText but a messagebox appear and said "conversion name from string to type integer is not valid"

Sastreen Over a year ago

No that isn't quite right. You want to use your loop to navigate to the cells where you want to write the headers. See my edit on how to write once you get there.

Riki Salim Over a year ago

what does x and y stand for? the program said it need to be declared.

Sastreen Over a year ago

@RikiSalim x and y have to be the location where you write the header (maybe determined by k). Basically they are going to be the cells (row and column) where you want to write the header. See my edit.

Riki Salim Over a year ago

i tried the coding but the header text still hasn't showed up on the excel.

Collectives™ on Stack Overflow

export datagridview header column to excel vb 2012

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related