c#Excel Interop替代等待处理0x800AC472错误 [英] c# Excel Interop alternatives to waiting to handle 0x800AC472 error
问题描述
我有一个应用程序，该应用程序多次写入公式/包含大量宏的工作簿.在每次迭代中，它会循环遍历一些数据，创建，填充，保存然后关闭一个Excel文件3次.当它是唯一运行的版本时，它可以很好地运行，但是如果有多个实例在运行，则会遇到麻烦.
I have an application that writes many times to a formula/macro-laden workbook. It loops through some data 3 times creating, filling, saving, then closing an excel file in each iteration. While it works fine when it's the only version of itself running, if there are multiple instances of it running it has trouble.
具体来说，将数据写入某些单元格时出现0x800AC472错误.每次都不是相同的单元格或值，但似乎每次都在第二遍.这是相关代码:
Specifically, I'm getting a 0x800AC472 error when writing data to certain cells. It isn't the same cell or value each time but it has seemed to be on the second pass through each time. This is the relevant code:
public void SetCellValue(int row, int col, string val)
{
if (_currWorkSheet != null)
{
string parms = string.Format("row={0}; col={1}; val={2}", row.ToString(), col.ToString(), val);
for (short i = 0; i < _maxRetries; i++)
{
try { (_currWorkSheet.Cells[row, col] as Range).Value2 = val; return; }
catch (Exception ex) { HandleError(ex, parms); }
}
Exception newExc = new Exception("Too many retries attempting to set cell value.");
newExc.Data.Add("parms", parms);
throw newExc;
}
}
private void HandleError(Exception exc, string parms)
{
if (exc != null && exc.Message != null)
{
// Excel error that just needs more time to complete. http://social.msdn.microsoft.com/forums/en-US/vsto/thread/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/
if (exc.Message.Contains("0x800AC472"))
Thread.Sleep(_threadSleepMs); // Give excel a chance to catch up, then keep processing.
else
{
Exception newExc = new Exception("Unexpected Error", exc);
newExc.Data.Add("parms", parms);
throw newExc;
}
}
}
我已将_maxRetries设置为10，将_threadSleepMs设置为500，并继续得到该错误，所以我认为不再增加它是有意义的.
I've set the _maxRetries to 10 and the _threadSleepMs to 500 and continue to get the error, so I don't think that increasing it anymore makes sense.
我想知道是否有其他方法可以使线程处于休眠状态，从而使它有机会像原来一样被释放".
I was wondering if there are alternatives to sleeping the thread to give it a chance to get "unstuck" as it were.
也许这将是第二个问题，但是我对此并不那么担心，但是当它崩溃时，我仍然在finally块中对其执行Close()，但是仍然有它的实例在徘徊.这是我关闭它的方式:
And maybe this would qualify as a second question but I'm not as concerned about this but, when it crashes I still perform a Close() on it in the finally block, but I still have instances of it hanging around. This is how I close it:
public void Dispose()
{
if (!_disposed)
{
if (_currWorkBook != null)
for (short i = 0; i < _maxRetries; i++)
{
try { _currWorkBook.Close(false, _missing, _missing); break; }
catch (Exception ex) { HandleError(ex, ""); }
}
if (_app != null)
{
if (_app.Workbooks != null)
for (short i = 0; i < _maxRetries; i++)
{
try { _app.Workbooks.Close(); break; }
catch (Exception ex) { HandleError(ex, ""); }
}
for (short i = 0; i < _maxRetries; i++)
{
try { _app.Quit(); break; }
catch (Exception ex) { HandleError(ex, ""); }
}
if (_currWorkSheet != null)
{
Marshal.ReleaseComObject(_currWorkSheet);
_currWorkSheet = null;
}
if (_currWorkBook != null)
{
Marshal.ReleaseComObject(_currWorkBook);
_currWorkBook = null;
}
Marshal.ReleaseComObject(_app);
_app = null;
}
GC.Collect();
_disposed = true;
}
}
它不会抛出错误，所以我只是想知道其中是否有任何孔?
It doesn't throw and error, so I was just wondering if there were any holes in it?
谢谢你，杰夫
推荐答案
我唯一能想到的解决方案是在需要使用excel功能时在线程上创建一个锁.这只是确保我一次只能使用excel进行一个过程.这不是完美的，特别是如果不相关的流程也尝试使用excel时，但这是我唯一能解决的问题.
The only solution I was able to come up with was to create a lock on the thread once it needs to use the excel functionality. This just ensured I only have one process using excel at one time. It's not perfect, especially if unrelated processes also try to use excel, but it was the only fix I could come up with.
这篇关于c#Excel Interop替代等待处理0x800AC472错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
