2

I am having trouble releasing Excel Interop Com Objects which is causing my c# application to crash when I attempt to save and then close an Excel workbook created via Excel Interop. I feel the issue is that in some cases I am using '2 dots' with excel interop COM objects which from what I've read is not allowed. I have eliminated 2 dots from most lines of code, but I am having troulbe figuring out a way to recreate the following lines of code so that they only use one dot. If anyone has any suggestions I would greatly appreciate it.

workbook = (Excel.Workbook)app.Workbooks.Open(startForm.excelFileLocation,);

workbook = (Excel.Workbook)app.Workbooks.Add(1);

workSheet_range.Font.Color = System.Drawing.Color.FloralWhite.ToArgb();

workSheet_range.Font.Bold = font;

workSheet_range.Interior.Color = System.Drawing.Color.Red.ToArgb();
Improve this question
2
  • 1
    Related: stackoverflow.com/questions/2191489/… Commented Oct 25, 2012 at 13:16
  • 1
    You could investigate using NetOffice (netoffice.codeplex.com) as your interop library, instead of the PIA assemblies. NetOffice has an internal tracking mechanism that lets you clean up properly, without worrying about the intermediate COM references. Commented Oct 25, 2012 at 15:08

3 Answers 3

Reset to default
4

Verify the return type of each instruction and break them down separately.

Your first line starts with "app.Workbooks" which returns an object of type Workbooks. Then the Open instruction returns a Workbook:

workbooks = app.Workbooks;
workbook = workbooks.Open(startForm.excelFileLocation);

You can then split the 2nd like this:

workbook = workbooks.add(1);

It's ok to use multiple dots if you're not "dotting" the actual InterOp objects.

Here's a full sample:

Using Excel = Microsoft.Office.Interop.Excel;
public void Read()
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
    Excel.Workbook xlWorkBook = xlWorkBooks.Open(sourceFile);
    Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets[ 1 ];

    Excel.Range range = xlWorkSheet.UsedRange;
    range = range.Cells;
    Array myValues = ( Array )range.Value;    //now holds all the data in the sheet

    //The following is to ensure the EXCEL.EXE instance is released...
    //If you edit this code, know that using 2 dots (ex: range.Cells.Value) can create weird stuff!
    xlWorkBook.Close(false);
    xlWorkBooks.Close();
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlWorkBooks);
    releaseObject(xlApp);

    xlWorkSheet = null;
    xlWorkBooks = null;
    xlWorkBook = null;
    xlApp = null;
}

private static void releaseObject( object obj )
{
try
{
    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    obj = null;
}
catch (Exception ex)
{
    obj = null;
    Console.WriteLine("Unable to release the Object " + ex.ToString());
}
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Could you elaborate and give an example of what 'dotting' an actual InterOp object would look like. This is such a headache.
Just make sure you don't "double-dot" anything that's included in the Microsoft.Office.Interop.Excel assembly. I updated my post with an example that includes releasing the objects properly.
I tried removing the double-dot from the following lines of code below and it caused an unhandled exception occur. I verified that the changes made to the following code caused it by undoing the changes and having it work. I'm not sure what I did wrong
Unless you post your new code, we won't be able to help you much further I'm afraid. However, if you know that you will be solely working with files from Office 2007 and earlier, you should take a look at the OpenXML library which will let you manipulate Excel files (.xslx and not .xls) directly, without InterOp. msdn.microsoft.com/en-us/library/office/bb448854.aspx
1

Summarising all the info here.

  1. Dont use two dots when assigning.
  2. Use the AutoReleaseComObject class.
  3. Use the ReleaseObject method (that uses a while loop) described in Microsoft KB: Office application does not quit after automation from .NET client.
  4. Use GC.Collect and GC.WaitForPendingFinalizers.
  5. Don't be surprised if the Excel Process is kept alive when debugging, always test if the process is kept alive by just running the application.

eg:

using Microsoft.Office.Interop.Excel;
...
var missing = Type.Missing;
using (AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application> excelApplicationWrapper = new AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application>(new Microsoft.Office.Interop.Excel.Application()))
{
    var excelApplicationWrapperComObject = excelApplicationWrapper.ComObject;
    excelApplicationWrapperComObject.Visible = true;

    var excelApplicationWrapperComObjectWkBooks = excelApplicationWrapperComObject.Workbooks;
    try
    {
        using (AutoReleaseComObject<Workbook> workbookWrapper = new AutoReleaseComObject<Workbook>(excelApplicationWrapperComObjectWkBooks.Open(@"C:\Temp\ExcelMoveChart.xlsx", false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
        {
            var workbookComObject = workbookWrapper.ComObject;
            Worksheet sheetSource = workbookComObject.Sheets["Sheet1"];
            ChartObject chartObj = (ChartObject)sheetSource.ChartObjects("Chart 3");
            Chart chart = chartObj.Chart;
            chart.Location(XlChartLocation.xlLocationAsObject, "Sheet2");

            ReleaseObject(chart);
            ReleaseObject(chartObj);
            ReleaseObject(sheetSource);

            workbookComObject.Close(false);
        }
    }
    finally
    {
        excelApplicationWrapperComObjectWkBooks.Close();
        ReleaseObject(excelApplicationWrapperComObjectWkBooks);

        excelApplicationWrapper.ComObject.Application.Quit();
        excelApplicationWrapper.ComObject.Quit();
        ReleaseObject(excelApplicationWrapper.ComObject.Application);
        ReleaseObject(excelApplicationWrapper.ComObject);

        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();    
    }
}

private static void ReleaseObject(object obj)
{
    try
    {
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        Console.WriteLine("Unable to release the Object " + ex.ToString());
    }
}

I know Releasing all the Objects, using GC.Collect and not using two dots when assigning seems over the top but at least when I quit the instance of Excel the process is freed, I don't have to programmatically Kill the Excel Process!!

Improve this answer

2 Comments

Or even ClosedXML its much easier than OpenXML
+1 on ClosedXML (closedxml.codeplex.com) as well if all you need is read/write XLSX files from an app - no need to have Excel on the machine. I work a lot with Excel Add-Ins where my app runs inside the Excel process, so I actually need to interop and hence the NetOffice use.
-1

Using two dots is not "disallowed" but it certainly can have performance impacts, especially when running in a tight loop.

Each "dot" is a COM call to the Excel library, which can be significantly slower than normal CLR object access. In general, you want to reduce the number of COM calls to as few as possible.

Reducing from two dots to one by splitting into two lines is not going to have any impact unless you reuse the same variable. For example, changing 

workSheet_range.Interior.Color = System.Drawing.Color.Red.ToArgb();

to

var interior = workSheet_range.Interior;
interior.Color = System.Drawing.Color.Red.ToArgb();

will have ZERO impact on performance, and may even be "optimized" back to the original single-liner if you don't re-use the interior variable.

However, changing

var font = workSheet_range.Font;
font.Color = System.Drawing.Color.FloralWhite.ToArgb();
font.Bold = font;

will have one fewer call to workSheet_range.Font so you'll see an incremental benefit.

BOTTOM LINE

I wouldn't be too concerned about changing every two-dot call but instead use a good profiling tool to determine where your code is spending the most time, then tackle that area first.

Improve this answer

1 Comment

The main problem when using 2 dots with InterOp is that the Excel instance will not close itself upon quitting the application, it's as if it's creating anonymous objects that are not being released by the GC. I usually fix these problems by getting rid of all two dots + releasing the objects using System.Runtime.InteropServices.Marshal.ReleaseComObject(xcelObject) on each object.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.