Feedback for Excel Automation and execution of long running macro

Feedback for Excel Automation and execution of long running macro

I have the most god-awful task of automating an excel workbook in C#. Part
of the process is to call a macro in the workbook which takes around 2
minutes to execute. This all happens in a Windows service and happily runs
from start to finish. At present it writes events to a database table just
before calling the macro and when the macro has finished executing. It
does a lot of calculations and exporting data to a text file within the
macro code.
As it takes so long the users have asked if they can be notified at
various parts of the process.
My initial thought with reservation was to periodically poll the
Application.StatusBar which gets update whilst running the macro using
System.Timers.Timer. I thought there might be some kind of thread issues
with this - which I think is happening as the call from the timer to get
the StatusBar does not return/complete for quite a large period of time
(tens of seconds).
I have my workbook wrapped up in the following class which makes sure
Excel closes correctly and runs the macro:
internal class myWorkbook : IDisposable
{
private Microsoft.Office.Interop.Excel.Application app = null;
private Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
private string _myWorkbookUri;
public myWorkbook(string myWorkbookUri, string)
{
_myWorkbookUri = myWorkbookUri;
}
public string Export(DateTime date)
{
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
app.DisplayAlerts = false;
app.Interactive = false;
app.AskToUpdateLinks = false;
myWorkbook = app.Workbooks.Open(_myWorkbookUri, 0, true, 5, "",
"", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"", true, false, 0, true, true, false);
return (string)app.Run("GenerateTextFile", date);
}
/// <summary>
/// Disposes the object instance and all unmanaged resources.
/// </summary>
void IDisposable.Dispose()
{
if (myWorkbook != null)
{
myWorkbook.Close(false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkbook);
}
if (app != null)
{
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
public string Status
{
get
{
if (myWorkbook == null)
return string.Empty;
else
{
return myWorkbook.Application.StatusBar.ToString();
}
}
}
}
Then I have tried to do the monitoring using the following nested/internal
class in my report processing class:
private class MyMonitor : System.Timers.Timer
{
private MyWorkbook _wb;
private ReportGeneratorProcess _parent;
private string _lastStatus = string.Empty;
private bool handlingTimer = false;
public MyMonitor(MyWorkbook wb, ReportGeneratorProcess parent)
{
_wb = wb;
_parent = parent;
this.AutoReset = true;
this.Elapsed += new
System.Timers.ElapsedEventHandler(this.timer_Elapsed);
}
private void timer_Elapsed(object sender,
System.Timers.ElapsedEventArgs e)
{
if (_My != null && !handlingTimer)
{
try
{
handlingTimer = true;
string status = _wb.Status;
if (status != _lastStatus && status.ToLower() != "false")
_parent.AddEvent(MSG_TITLE_RUN_My, status);
_lastStatus = status;
}
finally
{
handlingTimer = false;
}
}
}
}
This is all triggered by executing the following code in the
ReportGeneratorProcess class (I've omitted the unnecessary code)
string outputFilename = null;
MyMonitor monitor = null;
try
{
using (MyWorkbook wb = new MyWorkbook(_MyWorkbookUri))
{
monitor = new MyMonitor(wb, this);
monitor.Start();
outputFilename = wb.Export(month);
monitor.Stop();
}
AddEvent("My Complete", "Generated file " + outputFilename);
return outputFilename;
}
catch (Exception ex)
{
AddEvent("", "failed");
throw ex;
}
finally
{
if (monitor != null)
{
monitor.Stop();
}
}
AddEvent simply adds a new event to the database using the main class.
I remain defeated at the moment trying to think of an alternative
solution/nice way around this. Anyone got any hints?
Unfortunately the process HAS to operate as is. There is no scope to move
anything out of excel.