Results 1 to 5 of 5

Thread: [RESOLVED] Export Datagrid to Excel - MIssing First Row

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Resolved [RESOLVED] Export Datagrid to Excel - MIssing First Row

    Hi All

    Pretty sure this is a simple correction but just can't spot it I am exporting from a datagrid into excel with the "insertion" code below. For some reason, It is missing the first row in the datagrid and copying everything else into excel... can someone please spot the school boy error I have made! I have played around with the I J K start values but to no avail.

    Thank you in advance,,

    Code:
            'Export the data from the datagrid to an excel spreadsheet
            For i = 0 To DataGridView1.RowCount - 1
                For j = 0 To DataGridView1.ColumnCount - 1
                    For k As Integer = 1 To DataGridView1.Columns.Count
    
                        xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                        xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value
                    Next
                Next
            Next

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Export Datagrid to Excel - MIssing First Row

    Having three nested loops is wrong in the first place. You should have one loop to write the headers, then two nested loops to write the table of data. I suggest that you do that first and then actually debug your code, using a breakpoint and by stepping through it. That's how you work out exactly what the code is doing and exactly what it's doing wrong. You don't just read the code and then look at the results. You watch the code and the state of the app as it is executing.

  3. #3
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Export Datagrid to Excel - MIssing First Row

    I'm not following the need for the third loop, and I'm not following why you need to write and rewrite the column headers for each row. As I see it, there is a lot of optimization that can be made to the code.

    That being said, read-bugging your code as presented, I think if you change i + 1 to i + 2 in your second line of code inside of your For k loop, you might get the results you want.

  4. #4
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Export Datagrid to Excel - MIssing First Row

    Quote Originally Posted by AMJADJ75 View Post
    Hi All



    Thank you in advance,,

    Code:
            'Export the data from the datagrid to an excel spreadsheet
            For i = 0 To DataGridView1.RowCount - 1
                For j = 0 To DataGridView1.ColumnCount - 1
                    For k As Integer = 1 To DataGridView1.Columns.Count
    
                        xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                        xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value
                    Next
                Next
            Next
    at i= 0 you write the header then on the same line you write the values..

    Code:
      xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
      xlWorkSheet.Cells(0+ 1 =1, j + 1) = DataGridView1(j, i).Value
    then on i=1 you write the headers on the first line and so erase the value you wrote before...
    As said by OB1 ,
    Code:
    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value
    should solve the problem and as said by jmcilhinney, you should first put the header line then fill your rows in two separates steps.

    regards
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Export Datagrid to Excel - MIssing First Row

    apologies for the late response.

    Thank you Delaney, that did indeed work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width