Click here to Skip to main content
14,421,436 members
Rate this:
Please Sign up or sign in to vote.
See more:

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
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:
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 ;) 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
Top Experts
Last 24hrsThis month

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