This code writes to Excel using the COM interface. The general issue is that any exception handling has to handle the "Excel is busy" exception. This occurs if information is sent to Excel quicker than it can handle it - eg. latency when a workbook is loaded/created, or the user is playing with the scrollbars (there are good reasons for letting this happen).
This is probably the only example I know of which is simpler and cleaner in VB6 than in C#! In VB6 an ON ERROR would be used. The error handler would then create an error for most cases. But if the error code is a "busy" then it will sleep a short period of time (typically half a second) and then try again with a "RESUME". Don't get me wrong, ON ERROR is generally messier than C#'s try...catch and it is easier to produce awful code; however, this is one example where the VB6 ON ERROR works better. A long sequence of Excel calls can be trapped with one handler. The "RESUME" will then send control back to the line where the 'busy' occurred - this avoids duplicate calls or skipped calls.
The solution I have in C# is to create a while loop with a flag. The flag indicates a repeat of the loop is required due to a 'busy' return from Excel. See the code below.
Is there a simpler, more elegant way of doing this? The main problem is that this requires a method for each type of Excel call. To avoid too many duplicate Excel calls in the busy scenario, the contents of each method is atomic or close to atomic - eg. "write this formatted value"; "apply this formatting to this row". This results in lots of methods. And/or methods with lots of parameters (the example below is a short one with just one format option, but there could be more - colors, decimal points, etc).
private void WriteDoubleValue(Excel.Worksheet sh, int x, int y, double lfval, bool bBold)
{
bool bNotSuccess = true;
while (bNotSuccess)
{
try
{
((Excel.Range)sh.Cells[y,x]).set_Value(Missing.Value, lfval);
((Excel.Range)sh.Cells[y, x]).Font.Bold = bBold;
bNotSuccess = false;
}
catch (System.Runtime.InteropServices.COMException e)
{
if ((e.ErrorCode & 0xFFFF) == 0xC472)
{ // Excel is busy
Thread.Sleep(500); // Wait, and...
bNotSuccess = true; // ...try again
}
else
{ // Re-throw!
throw e;
}
}
}
}