Click here to Skip to main content
14,766,539 members
Please Sign up or sign in to vote.
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:

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
Updated 14-Jun-20 4:26am
v2
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

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

// 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.
   
v3
Comments
Maciej Los 15-Jun-20 3:31am
   
5ed!
[no name] 15-Jun-20 6:42am
   
Thank you Maciej.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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