-
Oct 15th, 2020, 07:30 PM
#1
Thread Starter
Member
[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
-
Oct 15th, 2020, 07:40 PM
#2
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.
-
Oct 15th, 2020, 07:43 PM
#3
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.
-
Oct 16th, 2020, 04:23 AM
#4
Re: Export Datagrid to Excel - MIssing First Row
Originally Posted by AMJADJ75
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)
-
Oct 26th, 2020, 01:21 PM
#5
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|