Export to excel not exporting all rows

Question

0.00/5 (No votes)

See more:

Hi,

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

C#

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");
            }
            wr.WriteLine();
            //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");
                    else
                    {
                        wr.Write("\t");
                   }
                }
                wr.WriteLine();
            }
            //close file
            wr.Close();

Thank you
Kallie

Posted 28-Feb-13 22:19pm

Member 9742322

Add a Solution

Comments

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++)
or
(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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-03-03T09:13:00

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[^].

Quote:
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:

C#

(dataGridView1.Rows[i].Cells[j].Value != null)

with:

C#

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

Have a look at below examples:
http://dotnetask.com/Resource.aspx?Resourceid=644[^]
http://csharp.net-informations.com/excel/csharp-excel-datagridview.htm[^]
http://csharp.net-informations.com/datagridview/csharp-datagridview-export-excel.htm[^]
More you'll find at CP ;) Use [Search] textbox in the right-top corner of this site.