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

错误:


  为此RuntimeCallableWrapper转换到COM上下文0x56b098失败,并出现以下错误:系统调用失败。 (来自HRESULT的异常:0x80010100(RPC_E_SYS_CALL_FAILED))。这通常是因为创建此RuntimeCallableWrapper的COM上下文0x56b098已断开连接,或者它正忙于执行其他操作。从当前COM上下文(COM上下文0x56af28)释放接口。这可能会导致损坏或数据丢失。为避免此问题,请确保所有COM上下文/公寓/线程都保持活动状态并且可用于上下文转换,直到应用程序完全通过代表其中包含的COM组件的RuntimeCallableWrappers完成。


下面是我正在使用的代码

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");

    }


我需要这个问题的帮助,并期待着它。

最佳答案

在我看来,通过完全删除数据表,您可能会获得很多效率。数据表很棒,但它们确实有开销,我想知道在数据表中填充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();
}


这会将您的main方法中的所有代码简化为:

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


这才是真正的重头戏。内置在Excel中的MS Query实际上可以为您完成所有这些工作。通常,您使用ODBC,但是使用MS SQL Server,您可以直接访问服务器而无需使用ODBC-微软到微软同居的特权。老实说,我从未尝试过使用存储的proc进行尝试,但是通过查询它会出色地工作。我没有理由怀疑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();


而且您会发现它非常快-我敢说它可以与您可以手写的任何内容相媲美。

关于c# - 通过C#创建Excel时出错:过渡到COM上下文0x56b098,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39111166/

10-12 12:43
查看更多