2

I'm developing c# excel VSTO project solution. I have a excelutil class like the following:

public class ExcelUtil : IDisposable
{
    /// <summary>Excel Application</summary>
    private Excel.Application xlApp = null;

    /// <summary>Excel Book</summary>
    private Excel.Workbook xlBook = null;

    /// <summary>Excel Sheets</summary>
    private Excel.Sheets xlSheets = null;

    /// <summary>Excel Worksheet</summary>
    private Excel.Worksheet xlWorksheet = null;

    /// <summary>Sheet Name List</summary>
    private List<string> mSheetNameList = null;

    /// <summary>
    /// Sheet Name List
    /// </summary>
    public List<string> SheetNameList
    {
        get
        {
            if (mSheetNameList == null)
            {

                mSheetNameList = new List<string>();

                foreach (Excel.Worksheet sheet in xlSheets)
                {
                    mSheetNameList.Add(sheet.Name);
                }
            }

            return mSheetNameList;
        }
    }

    public ExcelUtil()
    {
        xlApp = Globals.ThisAddIn.Application;
        xlBook = xlApp.ActiveWorkbook;
        xlSheets = xlBook.Sheets;
    }

    public void SetWorkSheet(string sheetName)
    {
        xlWorksheet = xlSheets[sheetName];
    }

    public void SetTextForRange(string range, string text)
    {
        Excel.Range xrange = xlWorksheet.get_Range(range);
        xrange.Value2 = text;
        xlBook.Save();
    }

    public void Dispose()
    {
        releaseObject(xlApp);
        releaseObject(xlBook);
        releaseObject(xlSheets);
        releaseObject(xlWorksheet);
    }

    private void releaseObject(object obj)
    {
        if (obj == null)
        {
            return;
        }

        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();
        }
    }
}

In Add-In project, I add a Windows Forms in it. In the Ribbon, I have a button and it will show the Form when click the button.

public partial class Ribbon1
{
    private void button2_Click(object sender, RibbonControlEventArgs e)
    {
        Form2 form = new Form2();
        form.Show();
    }
}

In the Form2, there's a button. When clicking the button, I will call the excelUtil's method to do some operations for the worksheet.

public partial class Form2 : Form
{
    public Form2()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        ExcelUtil excelUtil = new ExcelUtil();
        var sheetNames = excelUtil.SheetNameList;

        excelUtil.SetWorkSheet(sheetNames.FirstOrDefault());
        excelUtil.SetTextForRange("A1", "hello.");

        excelUtil.Dispose();
    }
}

At the first time, it worked well. But if I close the Form2 and open it again, when I click the button, it throws System. Runtime. Interopservices. Invalidcomobjectexception: 'COM objects separated from the underlying RCW cannot be used.

So, I do not know when it's appropriate to relase the excel COM objects.

Xie Steven
  • 8,544
  • 1
  • 9
  • 23
  • 1
    Apparently you have released your globals. See https://stackoverflow.com/a/25135685/11683 and stop doing that. – GSerg Jan 21 '22 at 09:58
  • 2
    Obviously it is not. Looks like you killed off Globals.ThisAddIn.Application – Hans Passant Jan 21 '22 at 10:12
  • 1
    In general, you need to release/dispose/unregister all objects your class creates, registers, or take explicit ownership over. In this case it does not look like you are creating anything, so you should probably not release anything either. If anything, this demonstrates the problems with mutable global state. – JonasH Jan 21 '22 at 10:14

0 Answers0