Using Excel in app causing double notifyicon issue

[Suzie999]

Hi.

In my application I'm using Excel to get some data into an array from a worksheet.
Here is my method.

public string[,] WorksheetToArray(string path, bool visible = false)
        {
            oExcel oXL = new oExcel(); // here new icon appears.
            if (oXL.Error > 0)
            {
                MessageBox.Show("An Error Occurred" + "\r" + "Error: " + oXL.Error.ToString(),
                    "TestApp");
                Application.Exit();
            }

            Excel.Worksheet sheet = oXL.OpenWorkSheet(path);

            oXL.oWB.Close(false);
            oXL.oXL.Quit(); // expected new icon to disappear


            return null;
        }

When the line oExcel oXL = new oExcel(); is executed, I get a second system tray icon of my application which does not close after Excel object has quit.

Any ideas how I might solve this?

[DaveAmour]

I wrote an article a few years back which might shed some light on this if you can bare to read it as its quite long!

https://www.paxium.co.uk/PublicArticle/Article/497

Just realised you are already cleaning things up so whilst my article may not help its certainly worth reading especially if you have insomnia!

[Suzie999]

Thanks.

Here is my oExcel class.

class oExcel : Form1
    {
        public Excel.Application oXL;
        public Excel.Workbook oWB;
        public Excel.Worksheet oSheet;
        public Excel.Range oRng;

        public string[,] sRet;
        private int error = 0;

        public int Error
        {
            get { return error; }
            set { error = value; }
        }

        public oExcel() // as soon as this constructor is stepped into, the second icon appears.
        {
            //Console.Beep(700, 100);
            try
            {
                oXL = new Excel.Application();
                oXL.Visible = false;
            }
            catch (Exception ex)
            {
                error = 1;
                Debug.WriteLine(ex.Message.ToString());
            }
            //Console.Beep(400, 100); // testing creation time.
        }

        public Excel.Worksheet OpenWorkSheet(string path)
        {

            try
            {
                oWB = oXL.Workbooks.Open(path);

            }
            catch (Exception ex)
            {
                oXL.Quit();
                error = 2;
                Debug.WriteLine("Error: " + error.ToString() + " - " + ex.Message.ToString());
                return null;
            }

            try
            {
                //Get a reference to the active sheet of the workbook.
                //objSheets = oWB.Worksheets;
                //oSheet = (Excel.Worksheet)objSheets.get_Item(1);
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;
            }
            catch(Exception ex)
            {
                oXL.Quit();
                error = 3;
                Debug.WriteLine("Error: " + error.ToString() + " - " + ex.Message.ToString());
                return null;
            }

            object[,] obj = GetRange();

            return oSheet;
        }

        public object[,] GetRange()
        {

            object[,] saRet;
            try
            {
                oRng = oSheet.UsedRange;
                saRet = (System.Object[,])oRng.get_Value(Type.Missing);
            }
            catch (Exception ex)
            {
                oWB.Close();
                oXL.Quit();
                error = 4;
                Debug.WriteLine("Error: " + error.ToString() + " - " + ex.Message.ToString());

                return null;
            }


            //Determine the dimensions of the array.
            long iRows;
            long iCols;
            iRows = saRet.GetUpperBound(0);
            iCols = saRet.GetUpperBound(1);
            sRet = new string[iRows, iCols];

            for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
            {
                for (long colCounter = 1; colCounter <= iCols; colCounter++)
                {
                    sRet[rowCounter - 1, colCounter - 1] = saRet[rowCounter, colCounter].ToString();
                }
            }
            // Debug.WriteLine(sRet.GetUpperBound(0).ToString());
            //Debug.WriteLine(sRet[sRet.GetUpperBound(0), 0]);
            //Debug.WriteLine(sRet[sRet.GetUpperBound(0), 1]);
            //Debug.WriteLine(sRet[sRet.GetUpperBound(0), 2]);
            //Report the value of the array.
            //Debug.Write(valueString);

            return saRet;
        }

        ~oExcel()
        {
            try
            {
                oWB.Close(false);
                oXL.Quit();
            }
            catch (Exception ex)
            {

            }
        }
    }

[Suzie999]

On the face of it, I think I might have solved this with simply Disposing my class instance after quitting excel.

oXL.oWB.Close(false);
oXL.oXL.Quit();
oXL.Dispose();

No 100& certain though, all I'm sure of is the second icon disappears.

[DaveAmour]

I'm happy to take a closer look if you want.

If so do I need more code or the above all I need to make it all work?

[Suzie999]

Hi DaveAmour.

I'm not sure what information or personal details are passed along with a complete project, and a little weary of doing so.

I hope you understand, and thank you very much for such a kind offer of help.

[DaveAmour]

Yes sure I wouldn't trust me if I saw my picture too!

[ddanbe]

I was blue, even before the film Avatar came out!
But serious. I noticed your use of a tilde, which, to my knowledge is destructor syntax in C++ and I guess never needed in C#. I once wrote a small C# program to process some chemistry data for a colleague of mine. And I used this Excel class:

// ****************************************************
//
// Class to manipulate and work with Excel files in C#
// DM 2.2.2009
//
// ****************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Peaker
{
    class ExcelC

    {
        const int FirstColWidth = 50;

        private Excel.Application  app = null;
        private Excel.Workbook workbook = null;
        private Excel.Worksheet worksheet = null;
        //private Excel.Range range = null;

        public ExcelC()
        {            
        }

        private int WSCount;

        public int WorkSheetCount
        {
            get { return WSCount = workbook.Worksheets.Count; }//if null?           
        }

        public void CreateADoc()
        {
            try
            {
                app = new Excel.Application();
                app.Visible = false;
                workbook = app.Workbooks.Add(1); 
                worksheet = (Excel.Worksheet)workbook.Sheets[1];
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message);
            }
        }

        public void OpenADoc(string fileName)
        {
            try
            {
                app = new Excel.Application();
                workbook = app.Workbooks.Open
                    ( Filename : fileName, 
                    UpdateLinks : Type.Missing, 
                    ReadOnly : false, 
                    Format : 5, 
                    Password : Type.Missing,
                    WriteResPassword : Type.Missing,
                    IgnoreReadOnlyRecommended : true, 
                    Origin : Excel.XlPlatform.xlWindows,
                    Delimiter : "\t", 
                    Editable : true, 
                    Notify : false, 
                    Converter : Type.Missing,
                    AddToMru : true,
                    Local : false,
                    CorruptLoad : Type.Missing);
                worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);

            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message);
            }
        }

        public void Show()
        {
            app.Visible = true;
        }

        public void Hide()
        {
            app.Visible = false;
        }

        public void CloseADoc()
        {
            workbook.Close(true, Type.Missing, Type.Missing);           
        }

        public void Quit()
        {
            app.Quit();
            releaseObject(worksheet);
            releaseObject(workbook);
            releaseObject(app);
        }

        // Put data in an excel cell
        public void PutData(int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;
        }

        // Get data as string out of an excel cell
        public string GetData(int row, int col)
        {
            string Val = string.Empty;
            if (worksheet.Cells[row, col].Value2 != null)
            {
                Val = worksheet.Cells[row, col].Value2.ToString();
            }
            return Val;
        }

        // Get data from a specific sheet
        public string GetData(int worksheetNr, int row, int col)
        {
            worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(worksheetNr);
            return GetData(row, col);
        }

        // Get data as double out of an excel cell
        public double GetDouble(int row, int col)
        {
            double d = -999.0;
            if (worksheet.Cells[row, col].Value2 != null)
            {
                d = worksheet.Cells[row, col].Value2;
            }
            return d;
        }

        // Write a series of strings to the active excel sheet
        public void PutHeader(int startrow, int startcol, List<string> H)
        {
            foreach (string item in H)
            {
                PutData(startrow, startcol, item);
                startcol++;
            }
        }

        public void WidenCols(int Ncols) //start with col nr 1
        {
            const int B_charASCIIcode = 66;

            worksheet.Range["A:A", Type.Missing].ColumnWidth = FirstColWidth;
            char ch = (char)(B_charASCIIcode + Ncols - 1);
            worksheet.Range["B:" + ch.ToString(), Type.Missing].ColumnWidth = 17;
        }

        public int GetLastFreeRowNr(int worksheetNr)
        {
            worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(worksheetNr);
            return worksheet.UsedRange.Rows.Count + 1; 
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}

I never had any problems with it.
Perhaps the use of a tilde here, got the C# compiler confused?

[Suzie999]

I've often wondered what that charater was called, and I don't believe that destructor is ever called, the app acts the same with or without it.

Just artifacts of things I was trying really.

Thanks for the class ddanbe, I'll have a study of it.

[ddanbe]

@Suzie999: just a litle warning. You may use this code to your own liking. Just remember that here and there it is tailored to what I needed at the time. Maybe you would delete or add some methods.

[Suzie999]

Yes, thanks.

Just looking at the way it's tidying up, same issues again.
The second icon remains, until the class which creates the excel app is disposed.

[DaveAmour]

Destructors are used - read my article if you have time