Click here to Skip to main content
11,920,156 members (58,283 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# Windows Forms

i wonder if anyone can help me, i am exporting a datagridview to excel using sample code i received. the datagridview shows all rows correctly, but when i export to excel and check in excel there is data missing , any help will be appreciated as i am not sure what i am missing or doing wrong

string excel_file = directoryFinance;
            int cols;
            //open file
            StreamWriter wr = new StreamWriter(excel_file);
           //determine the number of columns and write columns to file
            cols = dataGridView1.Columns.Count;
            for (int i = 0; i < cols; i++)
                wr.Write(dataGridView1.Columns[i].Name.ToString().ToUpper() + "\t");
            //write rows to excel file
            for (int i = 0; i < (dataGridView1.Rows.Count - 1); i++)
                for (int j = 0; j < cols; j++)
                    if (dataGridView1.Rows[i].Cells[j].Value != null)
                        wr.Write(dataGridView1.Rows[i].Cells[j].Value + "\t");
            //close file

Thank you
Posted 28-Feb-13 23:19pm
CHill60 1-Mar-13 4:45am
When you say there is data missing do you mean entire rows are missing or columns or individual cells? Also which version of Excel are you using?
Member 9742322 1-Mar-13 4:53am
Hi i should have specified, there are whole rows missing i use excel 2003 to export to but opening it in 2007. i also dont have paging on my gridview it is all just one page
CHill60 1-Mar-13 5:03am
Do you have more than 65536 rows on your datagrid? That's the max for Excel2003
Member 9742322 1-Mar-13 5:06am
hi no the grid has an average 2172 rows . i have exported it again now and it seems the last row is missing
CHill60 1-Mar-13 5:20am
Think I've found it ... in your for loop you have i < (dataGridView1.Rows.Count - 1); I think it either has to be i <= ... count -1 OR i< ...Count (i.e. without the minus 1 - you're handling the start-from-0 bit twice. If that works I'll post this as a solution - if it doesn't work reply and I'll have another look
Member 9742322 1-Mar-13 5:44am
when i try i < (dataGridView1.Rows.Count) there is about 20 rows missing in the middle of the excel file.

when i try (int i = 0; i <= (dataGridView1.Rows.Count); i++) i get error index out of range. must be non-negative and less than the size of the collection on the following line if (dataGridView1.Rows[i].Cells[j].Value !=null)

also on my debug screen i can see i has the correct number of rows
CHill60 1-Mar-13 5:55am
with (int i = 0; i <= (dataGridView1.Rows.Count); i++) you will of course get an error ... the loop must be one of the following
(int i = 0; i < (dataGridView1.Rows.Count); i++)
(int i = 0; i <= (dataGridView1.Rows.Count) - 1; i++)
That will cure the loss of the last row.
Losing 20 rows in the middle of the file is strange - the only bit of your code that I can see would cause that is if the cell values were all null. I can't reproduce this behaviour.
You say in your debug screen you can see the correct number of rows... I would make the change to the loop then experiment with a smaller grid (e.g. 10 rows) and use your debugger to step through the entire process - you will then hopefully be able to see why rows are missing from the middle
Member 9742322 1-Mar-13 6:27am
this is very weird i have added this part to the script
(int i = 0; i <= (dataGridView1.Rows.Count) - 1; i++)
the first time i ran it the last row was there with an extra row which i couldn't figure out where it came from . i decided to close and then run it again just to double check. i changed nothing in the code and when i ran it again the same 20 rows were missing again. always at the same lines . i have checked and the data is definitely in the gridview and not null. i will see to decrease the data or maybe look at a different way of exporting to excel

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

As i see, you are trying to export data from DataGridView to tab-separated file.

Some tips:
1) Define variable, type of: DataGridViewCell[^], to use it inside second loop,
2) Value returned by DataGridViewCell.Value[^] is the same type, as its source. So, if DGVCell.Value is equal to DBNull.Value, then it can't be null[^].

In database applications, a null object is a valid value for a field. This class differentiates between a null value (a null object) and an uninitialized value (the DBNull.Value instance). For example, a table can have records with uninitialized fields. By default, these uninitialized fields have the DBNull value.

More about: null (C# reference)[^], Nullable Types (C#)[^] and DBNull Class[^].

Try to replace:
(dataGridView1.Rows[i].Cells[j].Value != null)

DataGridViewCell cell = this.dataGridView1.Rows[i].Cells[j];
if (cell.Value.ToString != DbNull.Value.ToString)
    wr.Write(cell.Value.ToString + "\t");

Have a look at below examples:[^][^][^]
More you'll find at CP Wink | ;) Use [Search] textbox in the right-top corner of this site.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 455
1 phil.o 170
2 CPallini 150
4 F-ES Sitecore 130
0 OriginalGriff 6,778
1 KrunalRohit 4,495
2 Sergey Alexandrovich Kryukov 3,153
3 George Jonsson 2,810
4 Suvendu Shekhar Giri 2,181

Advertise | Privacy | Mobile
Web03 | 2.8.151120.1 | Last Updated 3 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100