Data export to excel but header text is not come on the excel, Please Help me...............

Question

Data export to excel but header text is not come on the excel, can you help me. i use the 

Dim ExcelApp As Object, ExcelBook As Object
        Dim ExcelSheet As Object
        Dim i As Integer
        Dim j As Integer

        'create object of excel
        ExcelApp = CreateObject("Excel.Application")
        ExcelBook = ExcelApp.WorkBooks.Add
        ExcelSheet = ExcelBook.WorkSheets(1)

        With ExcelSheet

            For i = 1 To Me.DataGridView1.RowCount
                .cells(i, 1) = Me.DataGridView1.Rows(i - 1).Cells(0).Value
                For j = 1 To DataGridView1.Columns.Count - 1
                    .cells(i, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
                Next
            Next

        End With

        ExcelApp.Visible = True
        '
        ExcelSheet = Nothing
        ExcelBook = Nothing
        ExcelApp = Nothing

this coding 

Answer 1

This code is for getting column text for the first row of an Excel sheet. Replace Console.WriteLine with your excel code.

For colIndex As Integer = 0 To DataGridView1.Columns.Count - 1
    Console.WriteLine(DataGridView1.Columns(colIndex).HeaderText)
Next

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 2

 

I AM NEW IN VB.NET I DO NOT UNDERSTAND YOUR CODE WHERE I WRIGHT IN MY CODING AND WHAT LINE I REPLACE WITH YOUR CODE. 

THANKS

Answer 3

Place the code into the area below, replace the console code with excel code.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 4

i do it but some error show

https://social.msdn.microsoft.com/Forums/getfile/1040247

Answer 5

Hi Jayanta,

According to your description, I suggest you to loop through all the Columns and add the headertext to the ExcelSheet Before you loop through all the rows in your DataGridView, I modify you code below, please refer to.

Code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
       LoadData()
        Insrtdata()
    End Sub
    Private Sub LoadData()
        Dim dt As New DataTable
        dt.Columns.Add("ColumnID", GetType(Int32))
        dt.Columns.Add("ColumnName", GetType(String))
        dt.Columns.Add("ColumnSex", GetType(String))
        dt.Columns.Add("ColumnNumber", GetType(String))
        Dim dr As DataRow = dt.NewRow()
        dr("ColumnID") = Convert.ToString(1)
        dr("ColumnName") = "Cherry"
        dr("ColumnSex") = "Woman"
        dr("ColumnNumber") = "0001"
        dt.Rows.Add(dr)
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.DataSource = dt
    End Sub
    Private Sub Insrtdata()
        Dim ExcelApp As Object, ExcelBook As Object
        Dim ExcelSheet As Object
        Dim i As Integer
        Dim j As Integer
        'create object of excel
        ExcelApp = CreateObject("Excel.Application")
        ExcelBook = ExcelApp.WorkBooks.Add
        ExcelSheet = ExcelBook.WorkSheets(1)
        With ExcelSheet
            For Each column As DataGridViewColumn In DataGridView1.Columns
                .cells(1, column.Index + 1) = column.HeaderText
            Next
            For i = 1 To Me.DataGridView1.RowCount
                .cells(i + 1, 1) = Me.DataGridView1.Rows(i - 1).Cells("ColumnID").Value
                For j = 1 To DataGridView1.Columns.Count - 1
                    .cells(i + 1, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
                Next
            Next
        End With
        'For i As Integer = 0 To DataGridView1.Rows.Count - 2
        '    For j As Integer = 0 To DataGridView1.Columns.Count - 1
        '        If cellRowIndex = 1 Then
        '            worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Columns(j).HeaderText
        '        Else
        '            worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(j).Value
        '        End If
        '        cellColumnIndex += 1
        '    Next
        '    cellColumnIndex = 1
        '    cellRowIndex += 1
        'Next
        ExcelApp.Visible = True
        ExcelSheet = Nothing
        ExcelBook = Nothing
        ExcelApp = Nothing
End Sub

Hope it is helpful to you.

Best Regards,
Cherry Bu

Hope it is helpful to you.

Best Regards,

Cherry Bu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi Jayanta,

Do you have solved your issue now? if yes, please remember to close your thread by marking helpful post as answer, it is very beneficial to the other communities who face the same issue.

Thanks for your understanding.

Best regards,

Cherry Bu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].