Error Exporting to Excel C#

Question

1.22/5 (2 votes)

See more:

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 :s

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

C#

// 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

C#

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();
    }

Posted 14-May-13 1:16am

Pedro Brito

Updated 14-May-13 1:19am

Johnny J.

v2

Add a Solution

Comments

Rockstar_ 14-May-13 7:19am

Put break point and check line by line...

Raja Soosai 14-May-13 7:20am

Yes. please let us know the problematic code line no atleast by debugging.

Pedro Brito 14-May-13 7:21am

The error is in this line : oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");

I changed to oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item(1); and now i can get the collumns name but i can´t get the data that is on the datagridview

CHill60 14-May-13 7:27am

If you use itemDataGridView[j, i].Value.ToString() does it make a difference?

Pedro Brito 14-May-13 7:35am

no , the same ... , just appear the title

CHill60 14-May-13 7:42am

Was worth a try. How about oSheet.Cells[i + 2, j + 1] = itemDataGridView.Rows[i].Cells[j].Value.ToString();

Pedro Brito 14-May-13 9:18am

Nothing changes :/

Pedro Brito 14-May-13 7:22am

I think it is a problem with the index on that line

Johnny J. 14-May-13 7:24am

If you put a try/catch around the line, what exception does it show then?

Pedro Brito 14-May-13 7:26am

I changed my code to oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item(1); and i don´t get any error . BUT i can only see the title of the collumns and not the data that is on the datagridview

3 solutions

Solution 3

By Default when you create an excel Workbook using C# visual Studio it can creates only 3 sheets, in order to avoid this error, you must add new sheet by using
method excelapp.Worksheets.Add(); it will resolve the exception.

Posted 19-Apr-15 21:29pm

Member 11042738

Solution 2

Thanks all for your answer , i discover my problem :)))

C#

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(1);

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

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

               }
           }

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

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

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

The error was in for (int i = 0; i <<big> itemDataGridView.Rows.Count ; i++)

Posted 14-May-13 3:26am

Pedro Brito

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 · Accepted Answer · 2013-05-14T01:33:00

Solution 1

Hi
HRESULT: 0x8002000B (DISP_E_BADINDEX)) this means invalid index, so within your for loop you are accessing a index that doesn't exists.

1) check where the index is going wrong;
2) make sure the cell is converted to range as below

C#

// Export titles
for (int j = 0; j < itemDataGridView.Columns.Count; j++)
{
    oRange = oSheet.Cells[1, j + 1] as Excel_12.Range;
    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 = oSheet.Cells[i + 2, j + 1] as Excel_12.Range;
        oRange.Value2 = itemDataGridView.Rows[i].Cells[j].Value;
    }
}

you can see I removed the cast and used the "as"; the reason for this is if the cell returns null, then you cannot cast them, but the "as" will assign it as null.

hope this helps.

Jegan

Posted 14-May-13 1:33am

Jegan Thiyagesan

Updated 14-May-13 1:58am

v2

Comments

Pedro Brito 14-May-13 7:39am

I get an error on Range , "the type or namespace range can not be found"

Jegan Thiyagesan 14-May-13 7:40am

ok, then give the full namespace Excel_12.Range.

Pedro Brito 14-May-13 7:45am

I am not understanding what to do , when i put Excel_12.Range on my namespace it returns an error to

This are my namespaces that i am using

using VBIDE_12 = Microsoft.Vbe.Interop;
using Office_12 = Microsoft.Office.Core;
using Excel_12 = Microsoft.Office.Interop.Excel;

Jegan Thiyagesan 14-May-13 7:49am

No! I didn't mean you add to the namespace it self, use the alias name i.e.

oRange = oSheet.Cells[1, j + 1] as Excel_12.Range;

oRange = oSheet.Cells[i + 2, j + 1] as Excel_12.Range;

Pedro Brito 14-May-13 7:53am

I get the same problem , it just give me the title and not the data that is on the datagridview :(

Jegan Thiyagesan 14-May-13 7:56am

Sorry, I missed one more thing;
second for loop should have
oRange.Value2 = itemDataGridView.Rows[i].Cells[j].Value;

Pedro Brito 14-May-13 9:04am

Problem solved :) thanks