Error Exportinng To Excel From DatagridView

[Ziggynet]

Hey Developers

I have made an application that takes data from sql 2005 using a stored procedure and displays them on a datagridview. And then now i have written code to export the data to an excel file using Microsoft Interop and Excel Core Services.

When i click on the export button it throws a System.ArgumentOutOfRangeException error, which says index was out of range. Must be non negative and less than the size of the collection.

System.Windows.Forms.DataGridviewCellCollection.get_Item(Int32 index)

Here's my Code:

Excel._Application xApp;
                    Excel.Workbook xWork;
                    Excel.Worksheet xSheet;

                    object misValue = System.Reflection.Missing.Value;

                    xApp = new Excel.ApplicationClass();
                    xWork = xApp.Workbooks.Add(misValue);
                    xSheet = (Excel.Worksheet)xWork.Worksheets.get_Item(1);

                    int i = 0;
                    int j = 0;

                    for (i = 0; i <= dtGrid.Rows.Count; i++)
                    {
                        DataGridViewRow row = dtGrid.Rows[i];

                        for (j = 0; j <= row.Cells.Count; j++)
                        {
                            xApp.Cells[i + 1, j + 1] = row.Cells[j].ToString();
                        }
                    }


                    xWork.SaveAs(misValue, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xWork.Close(true, misValue, misValue);
                    xApp.Quit();

                    releaseObject(xSheet);
                    releaseObject(xWork);
                    releaseObject(xApp);

[kvprajapati]

Correction : i <= dtGrid.Rows.Count

for (i = 0; i < dtGrid.Rows.Count; i++)
                    {
                        DataGridViewRow row = dtGrid.Rows[i];

                        for (j = 0; j <row.Cells.Count; j++)
                        {
                            xApp.Cells[i + 1, j + 1] = row.Cells[j].ToString();
                        }
                    }

[Ziggynet]

Thanks a lot adatapost. The program is now up ad running correctly . thank you a lot for your help.

for (i = 0; i <= dtGrid.RowCount - 1; i++)
                    {
                        for (j = 0; j <= dtGrid.ColumnCount - 1; j++)
                        {
                            DataGridViewCell cell = dtGrid[j, i];
                            xSheet.Cells[i + 1, j + 1] = [B]cell.Value.ToString();[/B]
                        }
                    }

[Ziggynet]

I now can export the datagridview to excel but i cannot export the headers from the datagridview into my excel document and when i open the excel document it does not autofit the data into the containing cells.

I tried adding them manually using

excelWorksheet.Cells[1,2] = "Heading Name";

But the excel document comes with an empty column at the end.

[firesock]

Hello I´m new here and I'm also trying to learn how to export gridviews to excel. I'm using the following code maybe it will give you some help.

You pass the argument, which is the gridview you want to export ("gv") in this example.

string attachment = "attachment; filename=Contacts.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

I also have a question is it possible to export two gridviews to the same exel but in different sheets?

Thanks