none
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 
    Friday, April 14, 2017 8:06 AM

All replies

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jayanta Modak Friday, April 14, 2017 12:25 PM
    • Unmarked as answer by Jayanta Modak Friday, April 14, 2017 12:25 PM
    Friday, April 14, 2017 8:33 AM
    Moderator
  •  

    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

    Friday, April 14, 2017 12:30 PM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 14, 2017 2:05 PM
    Moderator
  • i do it but some error show

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

    Friday, April 14, 2017 4:14 PM
  • 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 MSDNFSF@microsoft.com.

    Monday, April 17, 2017 6:42 AM
    Moderator
  • 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 MSDNFSF@microsoft.com.

    Wednesday, April 19, 2017 8:50 AM
    Moderator