CODEWITHSUNDEEP
c#excelcom-interopinterruptterminate
anonymouse
anonymouse

Reputation: 125

Exiting Interop controlled Excel when code running

I have written a wrapper for Excel in C# in order to allow the running of Excel workbooks in a batch process (under JobScheduler). My problem is this...

If the code is taking too long to run or requires terminating through the job scheduler the wrapper needs to handle this termination event. To this end I have added 

SetConsoleCtrlHandler(new HandlerRoutine(ConsoleCtrlCheck), true);

into the code. In the ConsoleCtrlCheck method I call a common exit routine (used in normal or termination circumstances). This routing does the following as per MS recommendations..

Closes and releases Workbooks
Closes and releases Excel
Garbage collects

and then exits with the return code of the Excel method that was called (the entry point). This works well.

However, if the VBA code is still running the Interop object will not respond to workbook close or application quit calls. This could be because everything is going slow or because a modal dialog has been issued. To cope with this I have added the following to the start of this common routine...

private static void Cleanup()
{
    // Give this X seconds then terminate
    mKillThread = new Thread(KillExcel);
    mKillThread.IsBackground = false;
    mKillThread.Start();
    ...
    // close the workbooks and excel application
    // Marshal.FinalReleaseComObject etc

    GC.Collect();
    GC.WaitForPendingFinalizers();

    // Not sure if necessary but makes sure Process gone
    try
    {
        Process p = Process.GetProcessById(mExcelPid);
        p.WaitForExit();
    }
    catch(ArgumentException)
    {}

    mKillThread.Abort();
}

private static void KillExcel()
{
    Thread.Sleep(Settings.Default.KillWaitMilliSeconds);

    if (mLog.IsInfoEnabled)
        mLog.Info(string.Format("Waited {0} seconds, killing Excel process [{1}]",Settings.Default.KillWaitMilliSeconds/1000, mExcelPid));

    try
    {
        Process p = Process.GetProcessById(mExcelPid);

        if (!p.HasExited)
            p.Kill();
    }
    catch(ArgumentException)
    {
    }
}

My question is, is there a better way of going about this or is this the way it has to be in order to ensure the excel process is removed on a job terminate event?

Upvotes: 4

Views: 352

Answers (2)

Christian Sauer
Christian Sauer

Reputation: 10899

Is there a chance that you can replace the VBA logic with a third party library like EPPLUS? That would be magnitudes faster....

Upvotes: 0

JustShrey
JustShrey

Reputation: 72

What you are doing is pretty much the standard way on totally ensuring the Excel quits. Given that there is COM Interop involved, and that excel has a nasty habit of leaving orphan instances, the method that you follow is pretty much foolproof.

However, if you have access to vba code and can control it there is a one fundamental thing you can do to ensure that the VBA code does allow other code to run. 

DoEvents

In my experience, placing that one line of code before and after running heavy calculations usually allows excel to process routine events properly. While usually this is recommended for UI related operation, it also works in letting Worksheet/Application events work properly.

Hope this helps.

Upvotes: 1

Related Questions