我正在尝试通过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/