Click here to Skip to main content
15,892,298 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
Hello there,

I wrote a tool for importing and exporting data into ExcelSheets (better said: workbooks).

Some relevant parts of my code are:
using Excel = Microsoft.Office.Interop.Excel;<br />Excel.ApplicationClass excelApp = new Excel.ApplicationClass();<br />excelApp.ScreenUpdating = false;<br />excelApp.DisplayAlerts = false;


I then open some workbooks, open some sheets, write some stuff into them and close all of them.
I also dereference them if they are no longer needed (mysheet = null).

But after finally shutting down the ExcelApp doing so:

excelApp.Quit();<br />excelApp = null;


I have to wait quite a long time (about half a minute) until I see the EXCEL-thread disappear in my ProcessExplorer.

The problem is that I cannot open the just written xls-files if the application did not end because it has still got its hands on the files.

So I have to know when this happens.

My questions are:
Why does it take so long?
When does the Excel-thread really end?
How do I end it correctly?
How can I provoke the thread to be ended?
Or is it just a matter of garbage collection? (a field I did not yet explore)

Thanks in advance,

Harry
Posted
Updated 19-Aug-10 5:01am
v2
Comments
Dalek Dave 19-Aug-10 11:01am    
Edited for Readability, Grammar, Syntax and Code Blocks.

Put simply, there is no way to completely shut down Excel (close the task so that you don't see it in the task list anymore) when you want to. It's a COM<->gargabe collector issue.

In earlier versions of the framework, it could be done with a direct call. GC.Collect() after quitting and nulling, but starting from 2.0 or 3.0 (can't remember cleary) it's no use.

The bright side is if you open more than one Excel app, only one will stay in memory for a long time, while all others will close immediately.

 
Share this answer
 
hi
Use the following code:
oXL is Excel Object
oSheet is WorkSheet object
oWBook is Workbook Object


MIDL
oXL.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
                oSheet = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBook);
                oWBook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                oXL = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();


Regards
 
Share this answer
 
I've seen this approach used a few times, wrap the Excel functionality in a class that implements IDisposable as follows

C#
using System;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;
namespace ExcelTools
{
    /// <summary>
    /// Helper class for automating excel
    /// </summary>
    /// <remarks>
    /// Loads of issues with COM interop and ensuring that we release all variables
    /// to ensure we don't get orphaned excel processes floating about, this class helps
    /// out with that
    /// </remarks>
    internal class ExcelWrapper : IDisposable
    {
        private class Window
        {
            [DllImport("user32.dll", SetLastError = true)]
            static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
            [DllImport("user32.dll")]
            private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId);
            public static IntPtr GetWindowThreadProcessId(IntPtr hWnd)
            {
                IntPtr processId;
                IntPtr returnResult = GetWindowThreadProcessId(hWnd, out processId);
                return processId;
            }
            public static IntPtr FindExcel(string caption)
            {
                System.IntPtr hWnd = FindWindow("XLMAIN", caption);
                return hWnd;
            }
        }

        private Application excel;
        private IntPtr windowHandle;
        private IntPtr processId;
        public ExcelWrapper()
        {
            // Create the excel application
            excel = CreateExcelApplication();
            // Find the specific process we have created with the caption 'COExcel',
            // get the window handle
            windowHandle = Window.FindExcel("your instance title");
            // Now we can get the process ID from the hWnd
            processId = Window.GetWindowThreadProcessId(windowHandle);
        }
        /// <summary>
        /// Creates an EXCEL.EXE instance with some common properties set, ready for automation
        /// </summary>
        /// <returns>Excel Application object</returns>
        private Application CreateExcelApplication()
        {
            Application excel = new Application();
            excel.Caption = "your instance title";
            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;
            excel.AskToUpdateLinks = false;
            return excel;
        }
        /// <summary>
        /// Read only. Access to the application object
        /// </summary>
        public Application Excel
        {
            get { return this.excel; }
        }
        /// <summary>
        /// Read only. The process id that the automated instance of Excel is
        /// running as
        /// </summary>
        public int ProcessId
        {
            get { return this.processId.ToInt32(); }
        }
        /// <summary>
        /// Read only. The window handle of the automated Excel instance
        /// </summary>
        public int WindowHandle
        {
            get { return this.windowHandle.ToInt32(); }
        }
        #region IDisposable Members
        /// <summary>
        /// The dispose method will attempt to clean up any COM objects that we have used
        /// during automation
        /// </summary>
        public void Dispose()
        {
            if (excel != null)
            {
                excel.Workbooks.Close();
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                // The GC needs to be called twice in order to get the
                // Finalizers called - the first time in, it simply makes
                // a list of what is to be finalized, the second time in,
                // it actually  the finalizing.  Only then will the
                // object do its automatic ReleaseComObject.
                GC.Collect();
                GC.WaitForPendingFinalizers();
                // OK, hammer and egg time. If we've still got a process open
                // maybe due to accidentially incrementing the COM interfaces
                // without using an object reference, then we'll just kill
                // the process we have created since we know it's finished
                // now
                try
                {
                    Process process = Process.GetProcessById(this.ProcessId);
                    if (process != null)
                    {
                        process.Kill();
                    }
                }
                catch
                {
                }

            }
        }
        #endregion
    }
}


using (ExcelWrapper wrapper = new ExcelWrapper())
{
    // Do something with the wrapper.Excel object
}
// Instance should be terminate once code execution leaves using block


The idea is to grab the window handle and get the process ID of the instance of Excel. You try and shut down normally by doing a clearup & just terminate the process if you haven't managed to fully.

If you have correctly tidied up all your objects, you shouldn't need to terminate the process in this way, but it does offer a final 'fail safe'
 
Share this answer
 
v2
Comments
shivaprasadk 20-Aug-10 2:42am    
Reason for my vote of 5
Clear cut instructions for implementing
"If you are using any COM object in .Net application then you need to release COM object properly". Excel process is still running in your process explorer, the reasons can be

1. You are using range object, but not releasing properly
2. Not releasing COM object using Marshal.ReleaseComObject()
3. you're invoking members of a COM object without assigning it to a variable

For reference please visit this [http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c]
 
Share this answer
 
Thank you for your answers!

I haven't known about Marshal.ReleaseComObject(...):thumbsup:

I think there have been some Range-objects which I invoked indirectly using something like wb.mysheet.getRange(...) (not knowing about the rule of the two dots).

Well, after I put GC.Collect() at the end of my Excel operations and after
mysheet = null;
wb = null;
excelApp.Quit()
excelApp = null;

and just before I put a MessageBox "finished" onto the screen, everything was fine.
I think, there is plenty of time for the GC while the user has to click away the MessageBox ;)

btw: I did not use Marshal.ReleaseComObject(), I just set them to null.
Perhaps I also put this GC.WaitForPendingFinalizers(); at the end.
Though I'm not sure - it's been quite a long time since I touched this code :-\ :-O
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900