Wednesday, March 23, 2011

Releasing Excel after using Interop

Some time ago, I wrote a simple application to fill in and write on disk an Excel spreadsheet. Something like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Net;
using System.IO;

namespace ExcelInterop
{
class Program
{
static void Main(string[] args)
{
Application oXL = null;
_Workbook oWB = null;
Workbooks oWBs = null;
_Worksheet oSheet = null;
Range oRange = null;

oXL = new Application();
oXL.Visible = false;
oXL.DisplayAlerts = false;

oWBs = oXL.Workbooks;

oWB = oWBs.Add(1);

oSheet = oWB.ActiveSheet;

oRange = oSheet.Cells[1,1];
oRange.Value = "Test";

oWB.SaveAs(@"C:\Temp\Test.xls", XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlShared,
false, false, null, null, null);

Console.WriteLine("Before cleanup.");
Console.ReadKey(false);

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

System.Runtime.InteropServices.Marshal.ReleaseComObject(oRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);

Console.WriteLine("After cleanup.");
Console.ReadKey(false);
}
}
}

Although the program worked as expected, I encountered an annoying problem. The Excel application kept running even after releasing all Interop objects. After some investigation, I realized that the problem was caused by the following two lines of code:

oRange = oSheet.Cells[1,1];
oRange.Value = "Test"

If I comment them out, then after the cleanup, the Excel application will disappear from the list of running processes. But obviously, I needed to set the right value into the cell. So after some Googling, I ended up with the following alternative:

oRange = oSheet.Cells;
oRange.set_Item(1, 1, "Test");

This solution works perfectly, and releases the Excel application after clean up.