How do I ensure cleanup of a BackgroundWorker thread in C#?

Question

I have an Excel XLL addin built in C# (using ExcelDNA) and one of the tasks it does (upon the user clicking a button) is a call to an external DLL that does a database request that can take a few minutes to complete. To avoid the main GUI thread locking up for the duration, I have wrapped that call out to the external DLL inside a BackgroundWorker. It all seems to work as expected, EXCEPT that now Excel does not close properly, I assume because that background thread is never getting tidied up?

internal class GetStructure
{
    private DataCache cache;
    public GetStructure(DataCache cache)
    {
        this.cache = cache;
    }

    public void RetrieveStructure()
    { // This is the wrapper for the actual function, as it needs to find and load the Bridging Tool DLLs first

        // ... some code to get the inputs and build a string array called request[]

        ForceDLLLoad(); // Make sure the assembly is loaded before calling the Aucotec DLL
        BackgroundWorker bw = new BackgroundWorker();
        bw.WorkerSupportsCancellation = true;
        bw.DoWork += new DoWorkEventHandler(bw_DoWork);
        bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_RunWorkerCompleted);
        bw.RunWorkerAsync(request);
        bw = null;
    }

    private void bw_DoWork(object sender, DoWorkEventArgs e)
    { // This is the worker thread code - unpack the argument and call the method that gets data
        // Do not attempt to access anything on the main thread in this call
        string[] request = (string[])e.Argument;
        string[] outputs = this.RetrieveStructureFromEB(request);
        e.Result = outputs;
    }
    private string[] RetrieveStructureFromEB(string[] request)
    { // This method is buried privately because a caller needs to make sure the DLLs are loaded first!

        SIMExporterEbmlImport.BridgingTool EB = new BridgingTool();

        string[] outputs = EB.GetStructuredData(cache.Workbook.FullName, request);
        EB = null;
        return outputs;
    }

    private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    { // Unpack the results from the worker thread into an Excel sheet
        string[] outputs = (string[])e.Result;
        if (outputs.Length < 1)
        {
            MessageBox.Show("Unable to retrieve structure from EB.", "DataTool - Get EB Structure", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            Common.ExitToExcel();
            return;
        }

        // ... some code that writes the outputs[] array to an Excel sheet via COM calls with Microsoft.Office.Interop.Excel

        MessageBox.Show("EB Structure Data has been retrieved and written to the Config sheet.", "DataTool - Get EB Structure", MessageBoxButtons.OK, MessageBoxIcon.Information);
        Common.ExitToExcel();
        BackgroundWorker worker = (BackgroundWorker)sender;
        worker.Dispose(); worker = null;
        GC.Collect()
    }

The code does finish execution as expected, because I get my MessageBox popup (success or failure) that are triggered in the RunWorkerCompleted event; and I get the data written to the Excel sheet. The issue is that Excel just turns into a zombie when I try to close it.

The 'zombie' Excel.exe that I am left with if I try to close Excel (the GUI disappears but Task Manager keeps a headless Excel.exe running in the background) is claiming to be waiting on splwow64.exe, but I have no idea why.

Finally, the external DLL that I call to make the time-consuming request consumes a LOT of RAM (>500 MB) - I force that to get cleared up using GC.Collect() to force that RAM to get freed asap (which it does). But my Excel.exe still will not close properly.

Answer 1

What do you do in // ... some code that writes the outputs[] array to an Excel sheet?

All calls to the Excel COM object model must be made on the main Excel thread. You really, really have to follow this rule. (COM object model calls made from the main thread might still fail for various reasons, since Excel sometimes 'suspends' the object model. So this is a necessary but not sufficient condition for happy COM calls.)

If you don't follow this rule, you get COM objects that are never get a chance to be cleaned up. Then the Excel process will become a zombie waiting for these objects to be released. There is no reliable way to track the COM objects associated with other threads, and conversely, COM objects used only on the main thread will not keep Excel running.

You don't show enough code to know whether this is the problem, but I'm guessing that you are using the COM object model to write the results to Excel, from the thread that is calling the completion callback. You can check whether code is running on the main Excel thread by confirming whether Thread.CurrentThread.ManagedId == 1.

You might expect the BackgroundWorker completion callback to run on the main thread, since the BackgroundWorker is probably being created on the main thread. But since you're not running in a GUI application, there will be no ambient SynchronizationContext to capture and use for the callbacks.

Your code can 'transition' to the main thread from some worker thread completion with a call to the Excel-DNA helper method ExcelAsyncUtil.QueueAsMacro. The code in the delegate you pass to this method will always run in a macro context on the main thread, where calls to the COM object model (and C API) are likely to work, and COM objects won't keep your process alive after closing Excel.

For this approach you can structure your code in the completion handler like this

// This must run on the main Excel thread - use helper to transition
ExcelAsyncUtil.QueueAsMacro(() =>
{
    // ... some code that writes the outputs[] array to an Excel sheet 
    var app = ExcelDnaUtil.Application as Application;
    app.Range["A1"].Value = 123;
});

Another approach is to install a SynchronizationContext that will do this transition as the Current one, before creating the BackGroundWorker. This approach is a bit more 'magical', but should work too.

    // Set up a SynchronizationContext that will post the completion callbacks back to the main Excel thread
    SynchronizationContext.Current = new ExcelSynchronizationContext();
    BackgroundWorker bw = new BackgroundWorker();

The ExcelSynchronizationContext class is defined by Excel-DNA and internally handles the 'Post' method by calling ExcelAsyncUtil.QueueAsMacro. You can confirm that this works right by checking that CurrentThread.ManagedThreadId == 1 inside the completion callback.