通过c#创建Excel时出错:转换到COM上下文0x56b098

本文关键字：转换 COM 上下文 0x56b098 出错 创建 Excel 通过 | 更新日期: 2023-09-27 18:03:44

我试图通过c#代码创建一个Excel文件，场景是我有一个存储过程，返回15000条记录，我正在通过SqlDataAdapter读取数据，然后在DataTable中填充数据，然后我将数据填充到Excel文件中，但过了一会儿应用程序抛出以下错误。

错误:

这个RuntimeCallableWrapper转换到COM上下文0x56b098失败，出现以下错误:系统调用失败。(Exception from HRESULT: 0x80010100 (rpce_sys_call_failed)).)这通常是因为创建RuntimeCallableWrapper的COM上下文0x56b098已经断开连接，或者它正在忙着做其他事情。从当前COM上下文(COM context 0x56af28)中释放接口。这可能会导致损坏或数据丢失。为了避免这个问题，请确保所有的COM上下文/公寓/线程保持活动状态，并可用于上下文转换，直到应用程序完全完成了RuntimeCallableWrappers，它代表了驻留在它们内部的COM组件。

下面是我使用的代码 
public DataTable getData(string query,string year,string month)
{
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand(query,con);
        cmd.Parameters.AddWithValue("@YR", year);
        cmd.Parameters.AddWithValue("@MN", month);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandTimeout = 0;
        da.SelectCommand = cmd;
        da.Fill(dt);
        return dt;
    }

主方法

private void btn_MRRRetention_Click(object sender, EventArgs e)
    {
        // Working for creating MRR Retention Excel File
        DataTable dt_Mrr;
        string Yr, mn;
        int tot_rows;

        Yr = Cmb_Yr.SelectedItem.ToString();
        mn = Cmb_Mnth.SelectedItem.ToString();
        if (xlApp == null)
        {
            MessageBox.Show("Excel is not properly installed!");
            return;
        }
        excel.Workbook xlWorkBook;
        excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        // Data Reader Code start for collecting data from DB and pasting into Excel 
        pictureBox2.Visible = true;
        dt_Mrr = func.getData("sp_MRR_Retention_APAC", Yr, mn);
        //string text = "exec sp_Mrr_Retention" + "'" + Yr + "','" + mn + "'";
        tot_rows = dt_Mrr.Rows.Count;
        int row = 2;
        int i=0;
        xlWorkSheet.Cells[1, 1] = "MONTH";
        xlWorkSheet.Cells[1, 2] = "Parent Name";
        xlWorkSheet.Cells[1, 3] = "Customer_Name";
        xlWorkSheet.Cells[1, 4] = "Customer_Account_No";
        xlWorkSheet.Cells[1, 5] = "Item_Category";
        xlWorkSheet.Cells[1, 6] = "Item_Description_Summary";
        xlWorkSheet.Cells[1, 7] = "Item_Number";
        xlWorkSheet.Cells[1, 8] = "Date_Range";
        xlWorkSheet.Cells[1, 9] = "Activity_Type";
        xlWorkSheet.Cells[1, 10] = "Line_Type";
        xlWorkSheet.Cells[1, 11] = "IBX_Code";
        xlWorkSheet.Cells[1, 12] = "IBX_Country";
        xlWorkSheet.Cells[1, 13] = "IBX_Region";
        xlWorkSheet.Cells[1, 14] = "Primary_Sales_Rep";
        xlWorkSheet.Cells[1, 15] = "MRC_Amount_USD_Budget_Rate";
        xlWorkSheet.Cells[1, 16] = "Entered_Currency_Code";
        xlWorkSheet.Cells[1, 17] = "MRC_Amount_LC";
        xlWorkSheet.Cells[1, 18] = "UCM ID";
        xlWorkSheet.Cells[1, 19] = "GAM_TAG";
        xlWorkSheet.Cells[1, 20] = "Client Services Manager";
        xlWorkSheet.Cells[1, 21] = "Sales Program Type";
        xlWorkSheet.Cells[1, 22] = "SFDC Account Id";
        xlWorkSheet.Cells[1, 23] = "Account Owner";
        //rs = func.getReader("sp_MRR_Retention '" + Yr + "','" + mn + "'");
        while (tot_rows>i)
        {
            xlWorkSheet.Cells[row, 1]   = dt_Mrr.Rows[i]["MONTH"];
            xlWorkSheet.Cells[row, 2]   = dt_Mrr.Rows[i]["Parent Name"];
            xlWorkSheet.Cells[row, 3]   = dt_Mrr.Rows[i]["Customer_Name"];
            xlWorkSheet.Cells[row, 4]   = dt_Mrr.Rows[i]["Customer_Account_No"];
            xlWorkSheet.Cells[row, 5]   = dt_Mrr.Rows[i]["Item_Category"];
            xlWorkSheet.Cells[row, 6]   = dt_Mrr.Rows[i]["Item_Description_Summary"];
            xlWorkSheet.Cells[row, 7]   = dt_Mrr.Rows[i]["Item_Number"];
            xlWorkSheet.Cells[row, 8]   = dt_Mrr.Rows[i]["Date_Range"];
            xlWorkSheet.Cells[row, 9]   = dt_Mrr.Rows[i]["Activity_Type"];
            xlWorkSheet.Cells[row, 10]  = dt_Mrr.Rows[i]["Line_Type"];
            xlWorkSheet.Cells[row, 11]  = dt_Mrr.Rows[i]["IBX_Code"];
            xlWorkSheet.Cells[row, 12]  = dt_Mrr.Rows[i]["IBX_Country"];
            xlWorkSheet.Cells[row, 13]  = dt_Mrr.Rows[i]["IBX_Region"];
            xlWorkSheet.Cells[row, 14]  = dt_Mrr.Rows[i]["Primary_Sales_Rep"];
            xlWorkSheet.Cells[row, 15]  = dt_Mrr.Rows[i]["MRC_Amount_USD_Budget_Rate"];
            xlWorkSheet.Cells[row, 16]  = dt_Mrr.Rows[i]["Entered_Currency_Code"];
            xlWorkSheet.Cells[row, 17]  = dt_Mrr.Rows[i]["MRC_Amount_LC"];
            xlWorkSheet.Cells[row, 18]  = dt_Mrr.Rows[i]["UCM ID"];
            xlWorkSheet.Cells[row, 19]  = dt_Mrr.Rows[i]["GAM_TAG"];
            xlWorkSheet.Cells[row, 20]  = dt_Mrr.Rows[i]["Client Services Manager"];
            xlWorkSheet.Cells[row, 21]  = dt_Mrr.Rows[i]["Sales Program Type"];
            xlWorkSheet.Cells[row, 22]  = dt_Mrr.Rows[i]["SFDC Account Id"];
            xlWorkSheet.Cells[row, 23]  = dt_Mrr.Rows[i]["Account Owner"];
            row++;
            i++;
            //For Checking purpose!
            //if (i == 1000) 
            //{ 
            //    break; 
            //}
        }
        // Data Reader Code Ends Here
        xlWorkBook.SaveAs("D:''MRR_Retention_Auto.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);
        pictureBox2.Visible = false;
        MessageBox.Show("Excel file created , you can find the file D:''MRR_Retention_Auto.xls");
    }

在这个问题上我需要帮助，并期待您的帮助。

通过c#创建Excel时出错:转换到COM上下文0x56b098

在我看来，完全放弃数据表可能会获得很多效率。数据表很棒，但它们确实有开销，我想知道在数据表中填充15,000行的开销是否会影响您的COM通信。

这是一个避免使用数据表的解决方案。它假定存储过程将按照您希望在Excel中看到的顺序转储列。

首先，让你的getData方法返回一个SqlCommand对象而不是一个数据表:

public SqlCommand getData(string query, string year, string month)
{
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.AddWithValue("@YR", year);
    cmd.Parameters.AddWithValue("@MN", month);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 0;
    return cmd;
}

从这里，你的函数调用和输出到Excel可以简单地看起来像这样:

SqlCommand cmd = func.getData("sp_MRR_Retention_APAC", Yr, mn);
SqlDataReader reader = cmd.ExecuteReader();
for (int col = 0; col < reader.FieldCount; col++)
    xlWorkSheet.Cells[col + 1, 1].Value2 = reader.GetName(col);
while (reader.Read())
{
    for (int col = 0; col < reader.FieldCount; col++)
        if (!reader.IsDBNull(col))
            xlWorkSheet.Cells[row, col + 1] = reader.GetValue(col);
    row++;
}
reader.Close();

你甚至可以把它包装在一个方法中来做Excel输入:

public void getData(string query, string year, string month, excel.Worksheet Ws)
{
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.AddWithValue("@YR", year);
    cmd.Parameters.AddWithValue("@MN", month);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 0;
    int row = 1;
    SqlDataReader reader = cmd.ExecuteReader();
    for (int col = 0; col < reader.FieldCount; col++)
        Ws.Cells[col + 1, 1].Value2 = reader.GetName(col);
    while (reader.Read())
    {
        for (int col = 0; col < reader.FieldCount; col++)
            if (!reader.IsDBNull(col))
                Ws.Cells[row, col + 1] = reader.GetValue(col);
        row++;
    }
    reader.Close();
}

这将简化主方法中的所有代码为:

func.getData("sp_MRR_Retention_APAC", Yr, mn, xlWorkSheet);

真正的问题是……MS Query内置于Excel中，实际上为你做了所有这些。通常您使用ODBC，但是使用MS SQL Server，您可以直接访问服务器而不需要ODBC——这是微软与微软共同使用的好处。老实说，我从来没有在存储过程中尝试过，但是在查询中，它会工作得很好。我没有理由怀疑MS Query将在一个过程中运行。

c#中的MS Query调用看起来像这样: 
excel.ListObject lo = sheet.ListObjects.AddEx(excel.XlListObjectSourceType.xlSrcQuery,
    connectionString, true, Excel.XlYesNoGuess.xlGuess, range);
lo.QueryTable.CommandText = queryText;
lo.Refresh();

你会发现它非常快——我敢说它可以和你手写的任何东西相媲美。

相关文章：