Datagridview to Excel C#

Question

2.00/5 (1 vote)

See more:

Hello,

I have a question and hope someone could help me with it.
It`s quite simple: I want to export my datagridview to excel sheet. It goes well.
Using this piece of code:

C#

try
{

    Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

    Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

    app.Visible = true;

    worksheet = workbook.Sheets["Sheet1"];

    worksheet = workbook.ActiveSheet;

    worksheet.Name = "Export from datagrid";

    for (int i = 3; i < gv_gsk.Columns.Count + 1; i++)
    {

        worksheet.Cells[1, i] = gv_gsk.Columns[i - 1].HeaderText;

    }

    for (int i = 0; i < gv_gsk.Rows.Count; i++)
    {

        for (int j = 2; j < gv_gsk.Columns.Count; j++)
        {

            worksheet.Cells[i + 2, j + 1] = gv_gsk.Rows[i].Cells[j].Value.ToString();

        }

    }





    app.Quit();
}
catch (Exception)
{ }

The problem is, my datagridview contains few rows which are empty (I have just name of product, but not all the other details), and when I export to excel- in excel workbook are shown not all products but the first ones before an empty row.
Is there any trick to prevent that? I want all rows to be in excel.

Thank you very much.

Posted 7-Mar-13 7:58am

ellisbay

Add a Solution

3 solutions

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)

Jegan Thiyagesan · Answer 1 · 2013-03-07T09:42:00

Hi,
I don't know your requriemetns on which column to to be displayed, but there seems some flaws in your for loops.

On the first for loop

C#

for (int i = 3; i < gv_gsk.Columns.Count + 1; i++)
{
    worksheet.Cells[1, i] = gv_gsk.Columns[i - 1].HeaderText;
}

you are starting with column 3 and magically adding an extra ghost column to the count and then you are decrementing the column by 1 so actually you are starting from column 2. I believe you want to add the column 2 to the column 3 in your excel. In that case you could rewrite the code as follow:

C#

for (int i = 2; i < gv_gsk.Columns.Count; i++)
{
    Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[1, (i + 1)] as Range;
    range.Value2 = gv_gsk.Columns[i].HeaderText;
}

So here you are starting from the second column that you wanted to exported to the third column in Excel.

And on your third for loop:

C#

for (int i = 0; i < gv_gsk.Rows.Count; i++)
{
    for (int j = 2; j < gv_gsk.Columns.Count; j++)
    {
        worksheet.Cells[i + 2, j + 1] = gv_gsk.Rows[i].Cells[j].Value.ToString();
    }
}

For each row, now you are starting the column at 2, which is correct. Then on the excel you are inserting then in the wrong place. you want add the data row under the heading that is i + 1 not 2.

C#

for (int i = 0; i < gv_gsk.Rows.Count; i++)
{
    for (int j = 2; j < gv_gsk.Columns.Count; j++)
    {
        Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[(i + 1), (j + 1)] as Range;
        range.Value2 = gv_gsk.Rows[i].Cells[j].Value.ToString();
    }
}

Now your column header and the values should match in your excel as well as you are not adding an extra row between the header and the data.

I hope this helps.

Regards
Jegan

Maciej Los · Answer 2 · 2013-03-07T09:01:00

Solution 1

To export all columns, change code as follows:
First loop
Replace:

C#

for (int i = 3; i < gv_gsk.Columns.Count; i++)

with:

C#

for (int i = 0; i < gv_gsk.Columns.Count; i++)

Third loop (inside second loop)
Relace:

C#

for (int j = 2; j < gv_gsk.Columns.Count; j++)

with

C#

for (int j = 0; j < gv_gsk.Columns.Count; j++)

Posted 7-Mar-13 9:01am

Maciej Los

Comments

ellisbay 7-Mar-13 15:20pm

Not working. I`m starting to loop from 3 because I dont want to show first two columns in header.
Any other idea?

Maciej Los 7-Mar-13 15:23pm

Try this one:
worksheet.Cells[i + 2, j + 1].Value = gv_gsk.Rows[i].Cells[j].Value.ToString();
or
worksheet.Cells[i + 2, j + 1].Text = gv_gsk.Rows[i].Cells[j].Value.ToString();
Do you have any exception message?

ellisbay 7-Mar-13 15:30pm

Nothing..
Look, everything works well- program doesn`t break- but instead of 123 rows, I get 23 in excel because 23th row in datagridview had several empty cells.

Maciej Los 7-Mar-13 15:36pm

The only way to check code execution is to debug it...

ellisbay · Answer 3 · 2013-03-07T11:15:00

C#

for (int i = 0; i < gv_alkaloid.Rows.Count; i++)
              {
                  for (int j = 2; j < gv_alkaloid.Columns.Count; j++)
                  {
                      worksheet.Cells[i + 2, j + 1] = gv_alkaloid.Rows[i].Cells[j].Value == null ? string.Empty : gv_alkaloid.Rows[i].Cells[j].Value.ToString();
                  }
              }

Datagridview to Excel C#

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0