Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Windows Forms
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
 
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 23:19pm
Comments
CHill60 at 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 at 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 at 1-Mar-13 5:03am
   
Do you have more than 65536 rows on your datagrid? That's the max for Excel2003
Member 9742322 at 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 at 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 at 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 at 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 at 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
good
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[^].
 
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:
(dataGridView1.Rows[i].Cells[j].Value != null)
with:
 
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 Wink | ;) Use [Search] textbox in the right-top corner of this site.
  Permalink  

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



Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 3 Mar 2013
Copyright © CodeProject, 1999-2014
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