导出到Excel C＃时出错 [英] Error Exporting to Excel C#

问题描述

当我将Excel导出到C＃时出现错误，我无法找到我的代码错误的位置以及我的问题的解决方案：s



错误：


$ b $bGestãoSI.exe中出现未处理的System.Runtime.InteropServices.COMException类型异常



其他信息：Índiceinválido。 （ExcepçãodeHRESULT：0x8002000B（DISP_E_BADINDEX））



代码运行时出现错误

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

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

推荐答案

嗨


HRESULT：0x8002000B（DISP_E_BADINDEX））这个表示无效索引，因此在你的for循环中你正在访问一个不存在的索引。



1）检查索引出错的地方;

2）确保单元转换为以下范围

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

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

你可以看到我删除了演员并使用了as;这样做的原因是如果单元格返回null，那么你就不能抛出它们，但是as会将它指定为null。



希望这会有所帮助。 />


Jegan

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

默认情况下，当您使用C＃visual Studio创建Excel工作簿时，它只能创建3张工作表，以避免这种情况错误，您必须使用

方法添加新工作表excelapp.Worksheets.Add（）;它将解决异常。

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.

感谢大家的回答，我发现我的问题:)））

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

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

错误是for（int i = 0; i<< big> itemDataGridView.Rows.Count; i ++）

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

这篇关于导出到Excel C＃时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！