Error Exporting to Excel C#

Question

I'm getting an error when I'm exporting Excel to C#, I can't find where my code is wrong and the solution for my problem

Error :

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in GestãoSI.exe

Additional information: Índice inválido. (Excepção de HRESULT: 0x8002000B (DISP_E_BADINDEX))

The error appear when the code is running

// Add a workbook.
oBook = oExcel_12.Workbooks.Add(oMissing);

// Get worksheets collection 
oSheetsColl = oExcel_12.Worksheets;

// Get Worksheet "Sheet1"
oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");

Here is all my code

 public static void ExportDataGridViewTo_Excel12(DataGridView itemDataGridView)
 {
        Excel_12.Application oExcel_12 = null;                //Excel_12 Application
        Excel_12.Workbook oBook = null;                       // Excel_12 Workbook
        Excel_12.Sheets oSheetsColl = null;                   // Excel_12 Worksheets collection
        Excel_12.Worksheet oSheet = null;                     // Excel_12 Worksheet
        Excel_12.Range oRange = null;                         // Cell or Range in worksheet
        Object oMissing = System.Reflection.Missing.Value;

        // Create an instance of Excel_12.
        oExcel_12 = new Excel_12.Application();

        // Make Excel_12 visible to the user.
        oExcel_12.Visible = true;

        // Set the UserControl property so Excel_12 won't shut down.
        oExcel_12.UserControl = true;

        // System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");

        // Add a workbook.
        oBook = oExcel_12.Workbooks.Add(oMissing);

        // Get worksheets collection 
        oSheetsColl = oExcel_12.Worksheets;

        // Get Worksheet "Sheet1"
        oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");

        // Export titles
        for (int j = 0; j < itemDataGridView.Columns.Count; j++)
        {
            oRange = (Excel_12.Range)oSheet.Cells[1, j + 1];
            oRange.Value2 = itemDataGridView.Columns[j].HeaderText;
        }

        // Export data
        for (int i = 0; i < itemDataGridView.Rows.Count - 1; i++)
        {
            for (int j = 0; j < itemDataGridView.Columns.Count; j++)
            {
                oRange = (Excel_12.Range)oSheet.Cells[i + 2, j + 1];
                oRange.Value2 = itemDataGridView[j, i].Value;
            }
        }

        // Release the variables.
        //oBook.Close(false, oMissing, oMissing);
        oBook = null;

        //oExcel_12.Quit();
        oExcel_12 = null;

        // Collect garbage.
        GC.Collect();
    }

Well, you should post the line where exception is thrown...probably then you'll see the solution even by yourself — Adriano Repetti
– Adriano Repetti, Commented May 14, 2013 at 10:48
The line is this : oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1"); — José Pedro Brito
– José Pedro Brito, Commented May 14, 2013 at 10:51
Try this: oSheet = (Excel_12.Worksheet)oBook.Worksheets["Sheet1"]; — Adriano Repetti
– Adriano Repetti, Commented May 14, 2013 at 10:54

Cliveburr · Accepted Answer · 2013-05-14 12:03:55Z

1

this work for me..

oSheet = oBook.Worksheets.get_Item(index);

answered May 14, 2013 at 12:03

Cliveburr

1411 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cremor · Accepted Answer · 2013-05-14 10:56:14Z

0

Since you got a non-english exception text from Excel I assume there is no sheet that is named "Sheet1", instead it has the localized name. You have to either use the loclized name or, which would be way better, just use the sheet index (should start with 1) instead of the sheet name.

answered May 14, 2013 at 10:56

cremor

6,9351 gold badge34 silver badges78 bronze badges

3 Comments

José Pedro Brito Over a year ago

now i get something , but only appears on excel the name of the collumns and not the data.

José Pedro Brito Over a year ago

oSheet = (Excel_12.Worksheet)oBook.Worksheets[1];

cremor Over a year ago

@JoséPedroBrito There is no other obvious error. If you don't get an exception you have to debug your code (e.g. check if the nested loops are executed), we can't do that for you.

Collectives™ on Stack Overflow

Error Exporting to Excel C#

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related