This question had been asked a plethora of times, and I just want to get to know what is the best way to do this.
After reading articles and posts I found out that to correctly release COM object (I'll be using Excel), you need to release all references to COM objects. This means that whenever you use some object's property and the methods (or property) of this property, you need to release them all. I will explain it in the "way 1".
There's another way I've come up with, and it's rude - kill Excel process. However, there's a catch which I'll explain it in the "way 2".
Let's start with the initial attempt.
Say, you have the following code which merely outputs some numbers into cells and saves a workbook. Your attempt is the following:
using Excel = Microsoft.Office.Interop.Excel;
static void UseExcelNotReleased()
{
var app = new Excel.Application { Visible = false };
var book = app.Workbooks.Add();
var sheet = book.Worksheets[1] as Excel.Worksheet;
new List<int> { 1, 2, 3, 4, 5 }
.ForEach(row =>
{
sheet.Cells[row, "A"].Resize[1, 2] = GetValues();
});
book.SaveAs("C:\\excel1.xlsx");
book.Close();
app.Quit();
GC.Collect();
GC.WaitForFullGCComplete();
Marshal.FinalReleaseComObject(app);
Marshal.FinalReleaseComObject(book);
Marshal.FinalReleaseComObject(sheet);
}
This code does its job, but if we take a look at Task Manager, we'll see EXCEL.EXE process is lurking. You seem to have released all the variables which used COM objects. How come EXCEL.EXE is still in processes?
The problem starts on this line:
var book = app.Workbooks.Add();
The thing is that you used Excel.Workbooks object! When you said app.Workbooks, the Excel.Workbooks was returned to you, but you didn't store it in a variable. Thus, the reference flew away. In the end of code, where you release COM objects, this object is not mentioned and the counter is still in the game.
This way I have come up with two ways to release:
Way 1: Store all references in variables
As I mentioned above, you need to store all references to all COM objects you have used. For instance, you have this code:
rng.Interior.Color = 255; //rng is "A1" cell
Marshal.FinalReleaseComObject(rng);
The problem is that rng.Interior returns Excel.Interior object, reference to which isn't stored anywhere. So, you must write the following code:
var interior = rng.Interior;
interior.Color = 255;
Marshal.FinalReleaseComObject(interior);
Marshal.FinalReleaseComObject(rng);
So, our example must look like this:
static void UseExcelReleaseCOM()
{
var app = new Excel.Application { Visible = false };
var books = app.Workbooks; //Store reference
var book = books.Add();
var sheets = book.Worksheets; //Store reference
var sheet = sheets[1] as Excel.Worksheet;
Excel.Range rng = null; //Store reference
new List<int> { 1, 2, 3, 4, 5 }
.ForEach(row =>
{
var values = new[] { 1, 2 };
rng = sheet.Cells[row, "A"];
rng.Value = values[0];
rng = sheet.Cells[row, "B"];
rng.Value = values[1];
});
book.SaveAs("C:\\excel1.xlsx");
book.Close();
app.Quit();
GC.Collect();
GC.WaitForFullGCComplete();
Marshal.FinalReleaseComObject(app);
Marshal.FinalReleaseComObject(books);
Marshal.FinalReleaseComObject(book);
Marshal.FinalReleaseComObject(sheets);
Marshal.FinalReleaseComObject(sheet);
Marshal.FinalReleaseComObject(rng);
}
In this case we store all references in variables and release them. Now EXCEL.EXE process is gone completely.
Way 2: Killing EXCEL.EXE process
The idea is simple: as soon as we start Excel, we store its handler in a variable and search for EXCEL.EXE process using it.
static void UseExcelKillProcess()
{
var app = new Excel.Application { Visible = true };
var xlHandle = new IntPtr(app.Hwnd);
var xlProc = Process.GetProcesses().First(proc => proc.MainWindowHandle == xlHandle);
var book = app.Workbooks.Add();
var sheet = book.Worksheets[1] as Excel.Worksheet;
new List<int> { 1, 2, 3, 4, 5 }
.ForEach(row =>
{
sheet.Cells[row, "A"].Resize[1, 2] = new[] { 1, 2 };
});
book.SaveAs("C:\\excel1.xlsx");
book.Close();
app.Quit();
xlProc.Kill();
}
It's much shorter (for instance, we use Resize property to assign values in one fell swoop), but it has a catch: the visibility of Excel must be set to true - otherwise, you won't get it's handler!
So, here's the question: should I use explicit objects references assignments to variables or I can just terminate EXCEL.EXE process? If I choose terminate, will its handlers stay in the system?
