Diificulty in releasing Excel COM objects

Question

4.00/5 (1 vote)

See more:

Hi,

I have exported data from database into excel in my Windows Service.
I have used Interop objects for exporting data into excel but unfortunately I am not able to release the objects and the processes in the task manager shows up a list of excels.

Please suggest what can be done.

Following is the code:

C++

public void automate()
       {

           string filepath,filename, fileExcel,renamedfile;


           string ReportCount = "select count(rs.contactID) from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546)";
           string UserCount = "select count(distinct(rs.contactID)) from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546)";
           Random nRandom = new Random(DateTime.Now.Millisecond);

           //Create a random file name.
           fileExcel = "t" + nRandom.Next().ToString() + ".xls";
           filepath = "C:\\test";
           filename = filepath + "\\" + "test.xlsx";
           renamedfile = System.IO.Path.GetFileNameWithoutExtension(filename);
           renamedfile = System.IO.Path.GetPathRoot(filename) + "test\\" + renamedfile + '_' + DateTime.Now.ToString("ddMMyyyy hhmm") + System.IO.Path.GetExtension(filename);
           Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();
           Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

           try
           {
               SqlConnection conn = new SqlConnection();
               conn.ConnectionString = ConfigurationManager.AppSettings["Connection"];
               SqlCommand cmd = new SqlCommand("select rs.contactID,SurveyType,ModuleName,LogDate from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546) order by LogDate ", conn);
               SqlCommand cmd1 = new SqlCommand(ReportCount, conn);
               SqlCommand cmd2 = new SqlCommand(UserCount, conn);
               SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(cmd);
               DataSet d = new DataSet();
               mySqlDataAdapter.Fill(d, "dataset");
               conn.Open();

               string reports = Convert.ToString(cmd1.ExecuteScalar());
               string users = Convert.ToString(cmd2.ExecuteScalar());


               int sheetIndex = 0;

               // Copy each DataTable
               foreach (System.Data.DataTable dt in d.Tables)
               {

                   // Copy the DataTable to an object array
                   object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                   // Copy the column names to the first row of the object array

                   for (int col = 0; col < dt.Columns.Count; col++)
                   {
                      // if(dt.Columns[col].ColumnName).
                       rawData[0, col] = dt.Columns[col].ColumnName;

                   }

                   // Copy the values to the object array

                   for (int col = 0; col < dt.Columns.Count; col++)
                   {
                       for (int row = 0; row < dt.Rows.Count; row++)
                       {
                           rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                       }
                   }

                   // Calculate the final column letter

                   string finalColLetter = string.Empty;
                   string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                   int colCharsetLen = colCharset.Length;
                   if (dt.Columns.Count > colCharsetLen)
                   {
                       finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                   }
                   finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);

                   // Create a new Sheet
                   Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
                   excelSheet.Name = dt.TableName;
                   WriteToLog("Create a new Sheet");

                   // Fast data export to Excel
                   string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
                   excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
                   Excel.Range rg,rg1;
                   rg = excelSheet.get_Range("D:D", Type.Missing);
                   rg.NumberFormat = "MM-DD-YYYY hh:mm";
                   rg1 = excelSheet.get_Range("B:B", Type.Missing);


                   rg1.Cells.Replace("SCC", "SC",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                   rg1.Cells.Replace("SPC", "SP", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                   rg1.Cells.Replace("TADay", "STA Day", Excel.XlLookAt.xlWhole, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                   rg1.Cells.Replace("TA", "STA Hour", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                   //rg1.Cells.Replace("TAC", "STA Hour", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                   //rg1.Cells.Replace("SCCMTS", "SC Comment", Excel.XlLookAt.xlWhole, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                   //rg1.Cells.Replace("TACDay", "STA Day", Excel.XlLookAt.xlWhole, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                   Marshal.FinalReleaseComObject(excelSheet);
                   Marshal.FinalReleaseComObject(rg);
               }


               // Save and Close the Workbook

               WriteToLog("save workbook");
               excelWorkbook.SaveCopyAs(renamedfile);//, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

               WriteToLog("Control cleared");
               excelWorkbook.Close(true, Type.Missing, Type.Missing);

               WriteToLog("file creation check");


           }

           catch (Exception ex)
           {
               WriteToLog(ex.Message);
           }

           finally
           {

              // Collect the unreferenced objects
               GC.Collect();
               GC.WaitForPendingFinalizers();

              // Release the Application object
                //Marshal.ReleaseComObject(excelSheet);
                //   Marshal.ReleaseComObject(excelRange);
                //   Marshal.ReleaseComObject(rg);
                //   Marshal.ReleaseComObject(rg1);

               Marshal.FinalReleaseComObject(excelWorkbook);
               Marshal.FinalReleaseComObject(excelApp);
               excelApp.Quit();
               excelApp = null;
               excelWorkbook = null;
           }
       }

Posted 2-Mar-11 23:02pm

Aksh@169

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dylan Morley · Answer 1 · 2011-03-02T23:36:00

Excel can be quite a pain when automating, it's difficult to keep track of all the COM references since much of the access is collection based.

Rule of thumb: When accessing a collection property on an Excel reference, first take a reference to the collection, don't access it by it's parent property.

e.g. instead of doing...

ExcelApplication excel = new ExcelApplication();
excel.Workbooks[0].Worksheets...

etc etc..use as follows..

ExcelApplication excel = new ExcelApplication(rm);
ExcelWorkbooks xlBooks = excel.Workbooks();
ExcelWorkbook xlBook = xlBooks.Open("c:\\sales.xls");
ExcelWorksheets xlSheets = xlBook.Worksheets();
ExcelWorksheet xlSheet = xlSheets.Item(1);
ExcelRange xlRange = xlSheet.Cells();

Each one of these increments a COM reference and you need to release them all in order for excel to be correctly disposed at the end of your interop. Otherwise, you'll end of with hanging Excel processes.

I can see in the code you've posted that you're not doing this and it's probably what's caused the hanging process.

Something you may want is to use an Excel Wrapper. While I still say make sure you use Excel carefully and follow the rules for COM interop, the Excel Wrapper class offers a solution to make sure there are no orphaned Excel objects

Usage of this class is as follows...

using (var wrapper = new ExcelWrapper())
{
    // Access to the Excel.Application instance
    var application = wrapper.Application;
    
    // Any Interop code to automate excel goes here
}

When the using block is finished, Excel will be terminated. Look at the Dispose method for implementation

C#

namespace Excel.Helpers
{
    using System;
    using System.Runtime.InteropServices;
    using System.Diagnostics;
    using Microsoft.Office.Interop.Excel;
    /// <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;
        private const string ExcelWindowCaption = "MyUniqueExcelCaption";
        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(ExcelWindowCaption);
            // 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 = ExcelWindowCaption;
            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
    }
}

Albin Abel · Answer 2 · 2011-03-02T23:24:00

You can't release a COM object if it is still in use. So before release make sure those object got free from doing any work. If close methods available then use it before releasing.

You are calling excelApp after releasing

MIDL

Marshal.FinalReleaseComObject(excelApp);
               excelApp.Quit();

So if that works then the excelApp not released. if excelApp.Quit() not self dispose, then call the release after calling this function.

Let me know if that works.

Diificulty in releasing Excel COM objects

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0