Datagridview columns not show in export data in excel C# winform

Question

0.00/5 (No votes)

See more:

i try use this code for datagridview data export in excel but every time i export excel only datagridview row export

What I have tried:

C#

private void button1_Click(object sender, EventArgs e)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    int i = 0;
    int j = 0;

    for (i = 0; i <= dataGridView1.RowCount - 1; i++)
    {
        for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
        {
            DataGridViewCell cell = dataGridView1[j, i];
            xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
        }
    }
    
    xlWorkBook.SaveAs("Test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured While releasing object" + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

Posted 14-Jun-20 0:33am

Amar chand123

Updated 14-Jun-20 3:26am

Richard MacCutchan

v2

Add a Solution

Comments

Richard MacCutchan 14-Jun-20 6:54am

Run the code in your debugger to check that it gets the column count right.

Amar chand123 14-Jun-20 7:03am

Column Count is right

Datagridview1.RowCount show 8

Richard MacCutchan 14-Jun-20 7:46am

Well you need to give us some more clues. We cannot guess what happens when you run the code.

Amar chand123 14-Jun-20 8:21am

I have a datagridview in datagridview data show from access database. And in datagridview i have 8 columns and 12 rows and if i use closedxml for export then data export correctly, but i don't want use closedxml because i dont know how i export same textbox data before export datagridview data

Richard MacCutchan 14-Jun-20 8:30am

Very interesting, but that still does not explain what actually happens when you run the code. I have an application that does much the same: it creates a DataGridView and populates it with data from various sources. On pressing a button it then saves all the information in an Excel workbook. So your code should do the same, but i have no way of guessing why it does not.

Amar chand123 14-Jun-20 8:38am

Sir do you have any good suggestions? Like same codes for export textbox and datagridview in export

[no name] 14-Jun-20 8:27am

Not really clear what the problem is. Do you mean you don't see the column names in excel?If this is the case, you need to access column names by dataGridView1.Columns

Richard MacCutchan 14-Jun-20 8:32am

DataGridViewCell cell = dataGridView1[j, i]; gets the cell at row i, column j, so it should capture all the cells.

[no name] 14-Jun-20 8:36am

Including the column names, I mean the header row? I don't think so, maybe I will try it. I would say OP's code only returns data rows.

[no name] 14-Jun-20 8:44am

So I would say the header row needs to be processed separately like described here: Export DataGridView To Excel In C#[^]


    // storing header part in Excel  
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{  
   worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;  
}

Amar chand123 14-Jun-20 9:04am

I use this but when i use this column show but first row replace by column header text

[no name] 14-Jun-20 9:06am

Yes of course. You need to add another +1 for the row index afterwards for the excel row.


xlWorkSheet.Cells[i + 1 + 1, j + 1] = cell.Value;

Amar chand123 14-Jun-20 9:10am

Not work i already try but problem is when i use this in my excel file in first row column show and second row empty show

[no name] 14-Jun-20 9:14am

I'm pretty sure it works if you don't mix up with the index. Check the source code again carefully.

Amar chand123 14-Jun-20 9:06am

here my code

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

for (int i = 1; i <= dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j <= dataGridView1.Columns.Count - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}

[no name] 14-Jun-20 9:07am

+1 more for the excel row will solve it:


xlWorkSheet.Cells[i + 1 + 1, j + 1] = cell.Value;

Amar chand123 14-Jun-20 9:22am

thank you for remind i understand my mistake
for (int i = 1; i <= dataGridView1.Rows.Count - 1; i++)
replace with
for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)

and it's work fine

[no name] 14-Jun-20 9:48am

You are very welcome. And thank you for accepting the answer.

Amar chand123 14-Jun-20 10:18am

Thank you both sir without your suggestions i my self not realise problem

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)

User 11060979 · Accepted Answer · 2020-06-14T03:26:00

After all comments to the question here the code which works.

C#

// Column titles to Excel
for (int i= 0; i < dataGridView1.Columns.Count; i++)
{
   // Here i + 1 because Excel cell index starts at 1
   xlWorkSheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}

// Data to Excel
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
   for (int j = 0; j < dataGridView1.Columns.Count; j++)
   {
      DataGridViewCell cell = dataGridView1[j, i];
      // Here i + 1 + 1. One +1 for excel index and another +1 for header row offset
      xlWorkSheet.Cells[i + 1 + 1, j + 1] = cell.Value;
   }
}

I hope it helps.