Writing to Excel cells while releasing all COM objects

Question

I am following this posts advice and trying to release all of my COM objects, including all Ranges while writing a List of string tuples to an Excel spreadsheet (using his ComObjectManager class).

I have the solution below which works, but looks like is not stacking the Range COM objects introduced by the

cells[j, column] = strings[i].Item1;

(et al) in the ComObjectManager object and therefore not releasing them. I have the commented out solution which would release the objects but I must not be understanding the syntax correctly to set the cells because it will not compile. I have also tried using

var roomNameCell = com.Get<object>(() => cells[j, column]);

for roomNameCell and areaCell, but this results in nothing being written to the cell.

I have researched the MSDN guides but can't figure out how to do this, please help.

internal void InsertStrings(List<Tuple<string, string>> strings, Excel.Range selection, ComObjectManager com)
    {
        const int singleRowCell = 1;
        const int columnsToArea = 2;

        int firstRow = selection.Row;
        int column = selection.Column;

        for (int i = 0, j = firstRow; i < strings.Count; )
        {
            selection = com.Get<Excel.Range>(() => app.Cells[j, column]);

            var mergeArea = com.Get<Excel.Range>(() => selection.MergeArea);
            var mergeAreaRows = com.Get<Excel.Range>(() => mergeArea.Rows);

            var cells = com.Get<Excel.Range>(() => app.Cells);

            if (selection.MergeCells && mergeAreaRows.Count > singleRowCell)
            {
                //var roomNameCell = com.Get<Excel.Range>(() => cells[j, column]);
                //var areaCell = com.Get<Excel.Range>(() => cells[j, column + columnsToArea]);

                //doesn't compile - cannont explicity convert from string to Range
                //roomNameCell = strings[i].Item1;
                //areaCell = strings[i].Item2;

                //works but the Range COM objects aren't being disposed?
                cells[j, column] = strings[i].Item1;
                cells[j, column + columnsToArea] = strings[i].Item2;
                ++i;
            }
            j += mergeAreaRows.Count;
        }
    }

There's a hidden Range that you can't see. Just don't try to write code like that, it's like reviving the bad old days of C and explicit memory management. Works about as well. If there is something wrong with the garbage collector and you can't fix it for some reason then call GC.Collect() + GC.WaitForPendingFinalizers() after you're done. — Hans Passant, May 10 '13 at 23:56
From what I can gather I have no choice but to explicitly release COM objects unless I want to cause a memory leak? — Steve M, May 11 '13 at 00:34

score 0 · Accepted Answer · answered May 11 '13 at 00:40

I figured it out, I just needed to set the Values property like this:

internal void InsertStrings(List<Tuple<string, string>> strings, Excel.Range selection, ComObjectManager com)
    {
        const int singleRowCell = 1;
        const int columnsToArea = 2;

        int firstRow = selection.Row;
        int column = selection.Column;

        for (int i = 0, j = firstRow; i < strings.Count; )
        {
            selection = com.Get<Excel.Range>(() => app.Cells[j, column]);

            var mergeArea = com.Get<Excel.Range>(() => selection.MergeArea);
            var mergeAreaRows = com.Get<Excel.Range>(() => mergeArea.Rows);

            var cells = com.Get<Excel.Range>(() => app.Cells);

            if (selection.MergeCells && mergeAreaRows.Count > singleRowCell)
            {
                var roomNameCell = com.Get<Excel.Range>(() => cells[j, column]);
                var areaCell = com.Get<Excel.Range>(() => cells[j, column + columnsToArea]);

                roomNameCell.Value = strings[i].Item1;
                areaCell.Value = strings[i].Item2;

                ++i;
            }
            j += mergeAreaRows.Count;
        }
    }

Writing to Excel cells while releasing all COM objects

1 Answers1