Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Excel.exe process still running after quit and dispose. #346

Open
GeyserLaPunk opened this issue Apr 25, 2022 · 4 comments
Open

Excel.exe process still running after quit and dispose. #346

GeyserLaPunk opened this issue Apr 25, 2022 · 4 comments
Labels
ms-excel support

Comments

@GeyserLaPunk
Copy link

GeyserLaPunk commented Apr 25, 2022 •
edited
Loading

This is looks like the same as issue #220 (closed) but with a latest nuget.
I'm using:

  • Windows 10
  • Office 365 (Enterprise)
  • VS 2017
  • NetOffice Framework Nuget 1.8.1

Just like in original (#220 ) Excel.exe starts and end fine until I add a workbook.
Once workbook is added Excel.exe remains in the process list.

My code is very simple and is based on your Sample01.

using ACEReports.Helpers.Logging;
using NetOffice.ExcelApi.Tools.Contribution;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = NetOffice.ExcelApi;

namespace ACEReports.Helpers.MsOffice
{
    public class ExcelHelper : IDisposable
    {
        //readonly Excel.Application xlApp = null;


        public ExcelHelper()
        {
            if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Creating Excel Helper Instance..."); }
            //if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Creating Creating Excel App Object..."); }
            //DebugPrintProxyCount(10);
            //xlApp = new Excel.Application
            //{
            //    DisplayAlerts = false
            //};
            //DebugPrintProxyCount(11);
        }

        internal void CreateExcelFile()
        {
            if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Creating Excel File..."); }
            DebugPrintProxyCount(0);
            Excel.Application xlApp = new Excel.Application();

            xlApp.DisplayAlerts = false;
            xlApp.DefaultSaveFormat = Excel.Enums.XlFileFormat.xlWorkbookNormal;
            xlApp.Interactive = false;
            xlApp.Visible = false;
            
            DebugPrintProxyCount(1);

            CommonUtils utils = new CommonUtils(xlApp);
            DebugPrintProxyCount(2);

            try
            {
                Excel.Workbooks wbs = xlApp.Workbooks;
                DebugPrintProxyCount(3);
                Excel.Workbook wb = wbs.Add();
                DebugPrintProxyCount(31);

                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
                DebugPrintProxyCount(4);

                ws.Name = "ACE Report";
                DebugPrintProxyCount(5);

                string wbFileName = utils.File.Combine(@"D:\Users\UserName\Files\Temp\ACE-Reporting\Automate", $"TestXslxFile-{ DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") }", DocumentFormat.Normal);
                wb.SaveAs(wbFileName);
                DebugPrintProxyCount(6);

                wb.Close(false);
                DebugPrintProxyCount(7);

                wb.DisposeChildInstances(true);
                DebugPrintProxyCount(71);

                wb.Dispose(true);
                DebugPrintProxyCount(8);

                wbs.Close();
                wbs.DisposeChildInstances(true);
                wbs.Dispose(true);
                DebugPrintProxyCount(81);


            }
            catch (Exception ex)
            {
                LogHelper.LogError(ex);
            }
            finally
            {
                if (xlApp != null)
                {
                    if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Closing Excel App Object..."); }
                    utils.Dispose();
                    DebugPrintProxyCount(9);

                    xlApp.DisposeChildInstances();
                    xlApp.Quit();
                    DebugPrintProxyCount(10);

                    xlApp.Dispose(true);
                    DebugPrintProxyCount(11);
                }
            }
        }


        private void DebugPrintProxyCount(int marker)
        {
            if (LogHelper.IsDebugEnabled)
            {
                var count = NetOffice.Core.Default.ProxyCount;
                LogHelper.AppLogger.Debug($"NetOffice Proxy count: { marker } : { count }");
            }
        }

        #region IDisposable Support
        private bool disposedValue = false; // To detect redundant calls

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    // TODO: dispose managed state (managed objects).
                    //if (xlApp != null)
                    //{
                    //    if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Closing Excel App Object..."); }
                    //    //xlApp.Application.Quit();
                    //    xlApp.Quit();
                    //    DebugPrintProxyCount(9);
                    //    xlApp.Dispose();

                    //    DebugPrintProxyCount(0);
                    //}

                    //if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Releasing Excel App Object..."); }
                    //Marshal.FinalReleaseComObject(xlApp);
                    ////cleanup
                    //GC.Collect();
                    //GC.WaitForPendingFinalizers();
                }

                // TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
                // TODO: set large fields to null.

                disposedValue = true;
            }
        }

        // TODO: override a finalizer only if Dispose(bool disposing) above has code to free unmanaged resources.
        // ~ExcelHelper() {
        //   // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
        //   Dispose(false);
        // }

        // This code added to correctly implement the disposable pattern.
        public void Dispose()
        {
            if (LogHelper.IsInfoEnabled) { LogHelper.AppLogger.Info("Disposing Excel Helper Instance..."); }
            // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
            Dispose(true);
            // TODO: uncomment the following line if the finalizer is overridden above.
            // GC.SuppressFinalize(this);
        }
        #endregion
    }
}

Here is the log output with NetOffice Proxy counts:

2022-04-25 12:37:04.3889 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Program.Main:(16) - Starting Main Program...
2022-04-25 12:37:04.4159 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.App.AceReports..ctor:(18) - Creating AceReports instance...
2022-04-25 12:37:04.4159 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.App.AceReports.DoExcelWork:(43) - Doing Excel Work...
2022-04-25 12:37:04.4159 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper..ctor:(23) - Creating Excel Helper Instance...
2022-04-25 12:37:04.4489 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.CreateExcelFile:(35) - Creating Excel File...
2022-04-25 12:37:04.4539 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 0 : 0
2022-04-25 12:37:09.8665 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 1 : 1
2022-04-25 12:37:09.8665 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 2 : 1
2022-04-25 12:37:09.9384 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 3 : 2
2022-04-25 12:37:10.4301 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 31 : 3
2022-04-25 12:37:10.5491 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 4 : 5
2022-04-25 12:37:10.5491 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 5 : 5
2022-04-25 12:37:11.8089 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 6 : 5
2022-04-25 12:37:11.8629 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 7 : 5
2022-04-25 12:37:11.8629 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 71 : 3
2022-04-25 12:37:11.8629 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 8 : 2
2022-04-25 12:37:11.8819 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 81 : 1
2022-04-25 12:37:11.8819 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.CreateExcelFile:(90) - Closing Excel App Object...
2022-04-25 12:37:11.8819 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 9 : 1
2022-04-25 12:37:11.8819 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 10 : 1
2022-04-25 12:37:11.8979 [ DEBUG ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.DebugPrintProxyCount:(124) - NetOffice Proxy count: 11 : 0
2022-04-25 12:37:11.8979 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Helpers.MsOffice.ExcelHelper.Dispose:(172) - Disposing Excel Helper Instance...
2022-04-25 12:37:11.8979 [ INFO ] ( CVS\C117396 ) | ACEReports.Helpers.Logging.LogHelper | ACEReports.Program.Main:(44) - Program complete

Am I missing something?
@haroldcris
Copy link

Same here.

any workaround for this one?

@jozefizso
Copy link
Member

jozefizso commented Nov 16, 2022 •
edited
Loading

I ran the sample and Excel did quit at the end.

Creating Excel File...
NetOffice Proxy count: 0 : 0
NetOffice Proxy count: 1 : 1
NetOffice Proxy count: 2 : 1
NetOffice Proxy count: 3 : 2
NetOffice Proxy count: 31 : 3
NetOffice Proxy count: 4 : 5
NetOffice Proxy count: 5 : 5
NetOffice Proxy count: 6 : 5
NetOffice Proxy count: 7 : 5
NetOffice Proxy count: 71 : 3
NetOffice Proxy count: 8 : 2
NetOffice Proxy count: 81 : 1
Closing Excel App Object...
NetOffice Proxy count: 9 : 1
NetOffice Proxy count: 10 : 1
NetOffice Proxy count: 11 : 0

Please, use memory troubleshooting tools to debug issues on your system.

@rqian-kore
Copy link

rqian-kore commented Mar 23, 2023 •
edited
Loading

Two things to try:

  1. Use the "semi-annual" channel for excel. Instructions are here:

https://learn.microsoft.com/en-us/deployoffice/overview-office-deployment-tool

  1. OLE methods seem to leak references that Excel cares about that are not reflected in the proxy count for NetOffice. For instance, even running the string builder method using "OleDbConnectionStringBuilder" causes the late binding instance of Excel to linger upon using the .ToString method.

See:

https://learn.microsoft.com/en-us/archive/blogs/vsofficedeveloper/excel-ole-embedding-errors-if-you-have-managed-add-in-sinking-application-events-in-excel-2

Cause

The problem is the result on conflicting design behaviors between OLE and .NET over object lifetime. For OLE embedding, Excel uses an in-process handler (InprocHandler) which acts as a proxy to the embedded COM object. Excel is desgined to keep a workbook object alive based on the reference count to that object, and the OLE handler expects that its final release of the object will free the object when it is done. If you introduce a managed addin or component into Excel that sinks the Application event, the delegate handlers .NET uses for the events may artificailly increase the reference count on the workbook objects used by OLE. This occurs because many events (like WorkbookOpen, WorkbookActivate, WorkbookBeforeSave) pass a Workbook object as a parameter in the event, and these events can fire for OLE embedded workbooks as well as non-OLE workbooks. The .NET Framework uses garbage collection (GC) instead of reference counting, so the release of references cannot be predicted and may not occur for several minutes (or hours, or never in some cases), thereby keeping these OLE object alive when the host application intended them to be closed. This can cause problems for OLE32 (which does not know about the .NET addin) when it attempts to create a new object and bind to the same storage used by the previous object that did not close.

Eliminating OLE paths and using ODBC instead seems to resolve the issue. I understand that some client scenarios depend on the use of OLE, but that's where things are. Perhaps someone can take a deeper look for a work around...

@GeyserLaPunk
Copy link
Author

GeyserLaPunk commented Jul 13, 2023 •
edited
Loading

It appears, that with v1.9.2 and VS2022 the issue is no longer a problem.
Not sure which specific change "fixed" the issue but I'm happy to report that it works for me as intended.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
ms-excel support
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@jozefizso @haroldcris @GeyserLaPunk @rqian-kore