Programmatically close excel application problem

Sep 2, 2013 at 8:50 AM
Hi everybody
Please excuse me if my question replicates something that was already answered, I didn't found the answer yet.

Agenda:
I have C# winforms application that works with log reports from hardware testing stations.
Those log reports are quite large in size. And they are not in simple formats, Excell cannot load them directly, they need to be preprocessed.

So, at first my program was doing the log processing first, and then I was using MS.Excel.Interop to create xlsx files. (Tried to use EPPlus, but it fails with large amounts of data) And what happens here is that I have two processes (my winforms and the Excel process) and I have to transfer the data between them and it takes ages.
This is how I came to using Excel-DNA.
Now I save preprocessed data to file (this is required anyway, so it is not lost time for IO) and then I use Excel add-in to open this file and import the data to xlsx.
end-of-agenda

Question:
In my C# Winforms application I have the following:
using ExcelApp = Microsoft.Office.Interop.Excel.Application;
....
var excel = new ExcelApp();
var ok = excel.RegisterXLL(addInDir + @"\LM.ExcelAddIn.xll");

if (ok)
   foreach (COMAddIn s in excel.COMAddIns)
   {
          if (s.Description.Contains("(COM Add-in Helper)"))
          {
                 var addin = s.Object;
                 if (addin != null)                        
                      var x = addin.Foo();                        
           }
    }

    excel.Quit();
    Marshal.FinalReleaseComObject(excel);
    excel = null;
So, if inside my Excel add-in i will not lock any excel com objects, those three lines should be able to close the Excel process, and I should not see it in my task manager. Am I correct?

Inside my Excel add-in I have the following class:
    [ComVisible(true)]
    public class AddInEngine
    {
        public AddInEngine()
        {
            _someString = " We love you!";
/*
Let's take exactly the same instance that loaded this add-in. It is the only instance of Excell process, we don't want to open another one. We will read the vast data from the same process that Excell application runs on, and it will save us from time-thirsty data marshaling
 */
            _application = (ExcelApp)ExcelDnaUtil.Application;
        }       

        private ExcelApp _application;        

        [ExcelFunction(Description = "This function does absolutely nothing")]
        public string Foo()
        {
            return "Hello there, can you read me?";
        }
}
As you can see I didn't use any COM objects from MS.Excel.Interop yet. But yes, I am defenetly going to use them. I know how to release any object that I use, so it will not be a problem. The problem is that now my application doesn't close, and I don't understand why. All I did was just a cast to Interop application.
Should I add destructor that releases the application inside my add-in?

Please, excuse me for a long and perhaps unclear description of my problem, English is my third language. Please ask for more details if you need to.

Regards, Artem
Coordinator
Sep 2, 2013 at 8:57 PM
Hi Artem

If it's any consolation, I don't understand the Excel COM / .NET reference management either.

Your driver might be problematic because you are getting many "COMAddIn" objects from the "COMAddIns" collection, and you are not releasing either the collection or the many you get in the foreach loop. These are all COM objects too, and if they were still alive, the Excel process would not terminate. But even if you change the driver code to just call a macro in the AddIn (via Application.Run), Excel still does not quit (if the object model is used in the add-in).

My understanding is that this should work:

Code in the add-in:
    public static class TestMacros
    {
        public static void WriteSomeStuff()
        {
            var xlApp = (Application)ExcelDnaUtil.Application;

            var newWb = xlApp.Workbooks.Add();
            var sheet1 = newWb.Worksheets["Sheet1"];
            sheet1.Range["A1"].Value = "Hello from the Add-In";
        }
    }
(Notice the 'static' - so it's registered as a macro that you can run with Application.Run.)

The driver code is in turn called from a method that invokes GC.Collect() and GC.WaitForPendingFinalizers() afterwards (it must be a separate method, due to how the debug version compiles local variable scopes):
        private void button1_Click(object sender, EventArgs e)
        {
            TestExcelAutomation.RunTest();
            // Force clean-up of all COM objects we no longer have references to:
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
calling
    class TestExcelAutomation
    {
        public static void RunTest()
        {
            var xlApp = new Application();
            xlApp.Visible = true;
            xlApp.RegisterXLL(@"C:\work\ExcelDna\Samples\ExcelAutomation\TestAddIn\bin\Debug\ExcelDnaTest.xll");
            xlApp.Run("WriteSomeStuff");
            xlApp.Quit();
        }
    }
I expect this to work but it doesn't - the Excel still sticks around.

So I don't have a useful suggestion for you yet - maybe someone else?

-Govert
Sep 3, 2013 at 7:33 AM
Hi Govert
This morning I continue my research on the matter. I made my test program as simple as I can.
My test program does only the following:
1) Creates instance of Excel application
2) Registers the add-in
3) Closes excel application

And to my disappointment the Excel stays alive.

I have worked with COM objects previously, and I am aware of "rule of two dots", so I am pretty sure that my code is clear.

This is the code for my main function in simple console C# application:
                var excel = new ExcelApp();
                var isRegistered = RegisterExcelAddin(excel, pathToAddInXLL);                
                excel.Quit();
                Marshal.FinalReleaseComObject(excel);
                excel = null;
                GC.Collect(GC.MaxGeneration,GCCollectionMode.Forced);
                GC.WaitForPendingFinalizers();
If I comment the line "var isRegistered = RegisterExcelAddin(excel, pathToAddInXLL);" then the Excel dies and disappears from Windows task manager.

Below is the code of my RegisterExcelAddin function:
private bool RegisterExcelAddin(ExcelApp excelApp, string pathToXLL)
        {
            try
            {
                var addIns = excelApp.COMAddIns;
                var ok = excelApp.RegisterXLL(pathToXLL);

                if (ok)
                    Console.WriteLine("The add-in was successfully registered ...");

                Marshal.FinalReleaseComObject(addIns);
                addIns = null;
                return ok;
            }
            catch (Exception)
            {
                return false;
            }
        }
This function works, since I can find my add-in in add-in collection afterwards. And I can call add-in functions. The function definitely releases all COM objects that it creates.

This leads me to the conclusion, that some COM objects are created in add-in code, and the are not released properly.
Lets look on the add-in code. It has three classes, very simple ones.
public class AddInRoot : IExcelAddIn
    {
        public void AutoOpen()
        {
            try
            {
                var comAddin = new AddInComRoot();
                comAddin.GetType().InvokeMember("DnaLibrary", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.SetProperty, null, comAddin, new object[] { DnaLibrary.CurrentLibrary });

                ExcelComAddInHelper.LoadComAddIn(comAddin);
            }
            catch (Exception e)
            {
                Debug.WriteLine("Error loading COM AddIn: " + e);
            }
        }
        public void AutoClose()
        {

        }
    }
 [ComVisible(true)]
    public class AddInComRoot : ExcelComAddIn
    {
        // : IDTExtensibility2, ie COM "AddIn".ExcelDNA finds this by magic.
        LMAddInEngine _helper;
        public AddInComRoot()
        {
        }

        public override void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
        {
            _helper = new LMAddInEngine();

            addInInst.GetType().InvokeMember("Object",
                                             BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty,
                                             null,
                                             addInInst,
                                             new object[] { _helper });
        }

        public override void OnDisconnection(ext_DisconnectMode removeMode, ref Array custom)
        {
        }
        public override void OnAddInsUpdate(ref Array custom)
        {
        }
        public override void OnStartupComplete(ref Array custom)
        {
        }
        public override void OnBeginShutdown(ref Array custom)
        {
        }
    }
[ComVisible(true)]
    public class LMAddInEngine
    {
        [ExcelFunction(Description = "Get simple string. This is test function", IsExceptionSafe = true)]
        public string GetStringFoo()
        {
            return "Hi there!";
        }
    }
If I have understand the example correctly, then all my desired functionality will be in LMAddInEngine class. And in future I will need to reference Excel application in it, but as I've sad before, I have simplified everything to find the problem.

Govert, from the above I assume that some COM objects are created and not released or in AddInComRoot or in AddInRoot class. And sadly, I don't have a clue what happens in those classes. But I hope all those narrows the search somehow.
I will continue to think, but if you have something - let me know.

Thanks for fast reply and regards,
Artem
Jan 8, 2014 at 9:13 PM
I have the same problem.
Currently using ExcelDna-79914.
In my add-in I do not explicitly do anything with COM objects; the Excel application I always get as ExcelDnaUtil.Application.
I implement asynchronous UDFs, and I also set minimum and maximum threads on ThreadPool. The Threadpool should not be a problem, because all threads created by it should be background threads, and they should not prevent process from terminating.
Is it still a bug in ExcelDna? Is there any workaround?
Looks like this is an old problem that somehow was re-introduced: I saw somewhere postings from 2009 about the same thing.
Coordinator
Jan 13, 2014 at 8:23 PM
Hi,

It does not sound like you are in the situation described by Artem.

Are you calling ExcelDnaUtil.Application from one of the ThreadPool threads created inside your add-in?

If so, you need to deal with the COM reference management yourself. A better approach is to always use ExcelAsyncUtil.QueueAsMacro to transition to the main thread before interacting with the Excel COM object model.

If not, it will help if you can post more details of what you are doing. Maybe it would be better to start a new discussion.

Regards,
Govert
May 4, 2015 at 3:59 PM
Hi,

This may already have been answered elsewhere and comes a little late for VerbukArtem but here is my 2 cents worth.
I think VerbukArtem is correct in his conclusions about the addins collection and addin objects not being released by Excel.
Generally it is my understanding that in most cases Excel interop should dispose of any objects once out of scope.

In the simple code below there is not a lot going on but if I quit excel in the attached code the usual way (excelApp.Quit()) then the excel process would remain open.
When the code uninstalling the addin is removed there is no problem.

I tried all sorts of voodoo to resolve the problem the "Two Dot Rule" and "Marshal.ReleaseComObject" ( which I have discovered in some posts is not advised) but nothing worked.

If the addin code is in place, excel is made visible and the excelApp.Quit() is removed the excel application can be manually quit. In this case no Excel process remains running. This got me to thinking that maybe the interop Quit() is not the same as quiting excel by the X button. So it tried using the Excel.Application.SendKeys("%{f4}",true) function. This did not do anything (at least in Microsoft.Office.Interop.Excel Version 11.0 - PIA 2003). I tried the Forms SendKeys.SendWait("%{f4}"). This at least closed the application but the Excel process remained.

Finally, I bit the bullet and just added code to kill Excel after Quit() is called. not very elegant but at least it works.
    public void UnregisterXll(string name)
        {
            Excel.Application excelApp = null;
            OpenExcel(out excelApp);
            //excelApp.AddToCleanupList(); // Voodo code to releasecom objects no effect on addin object not required
                                        // The procedure will work without it

            //if (!excelApp.Visible) excelApp.Visible = true;
            
            Excel.Workbook wkBk = null;

            if (excelApp.Workbooks.Count == 0)
            {
                try
                {

                    wkBk = (Excel.Workbook)excelApp.Workbooks.Add(Type.Missing);
                    //wkBk.AddToCleanupList();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "On adding workbook");
                }
            }
            
            
            // Now uninstall addin
            Excel.AddIns addins = excelApp.AddIns;

            Excel.AddIn addin = addins.get_Item(name);
            
            if (addin != null)
            {

                addin.Installed = false;
                addin.Installed = true;
                addin.Installed = false;


            }

            // Addin now removed
            

            if (!alreadyOpen)
            {
                try
                {


                    Process process = Process.GetProcessesByName("EXCEL").First();// Will only ever have one instance
                    excelApp.Quit();
                    process.Kill();
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            
        }