Click here to Skip to main content
15,798,066 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

1 solution

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.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900