If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
This posting over at ScottishDevelopers got me thinking, especially since I’ve used Excel and Word from a number of Delphi applications over the last eight years. I have come across this problem in the past (Win32), so I thought it should be fairly easy to chase.
After a bit of research, The Code Project suggested that the Excel process be killed off through code:
Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
pProcess[0].Kill();
Well, that would work, but it might kill off the wrong instance, or kill off the user’s precious ‘expenses’ spreadsheet instead of the one we created.
To help me trace the problem, I used the following code:
using System.Diagnostics;
using System.Runtime.InteropServices;
Excel.Application excelApp = null;
Excel.Workbook excelWorkbook = null;
Excel.Sheets excelSheets = null;
Excel.Worksheet excelWorksheet = null;
Excel.Workbooks excelWorkbooks = null;
private void button3_Click(object sender, System.EventArgs e)
{
excelApp = new Excel.ApplicationClass();
excelApp.Visible = true;
excelWorkbooks = excelApp.Workbooks;
excelWorkbook = excelWorkbooks.Add(System.Reflection.Missing.Value);
excelSheets = excelWorkbook.Worksheets;
excelWorksheet = (Excel.Worksheet) excelSheets.get_Item(1);
excelWorksheet.Cells[1,1] = "42";
}
private void button4_Click(object sender, System.EventArgs e)
{
excelWorkbook.Close (false, System.Reflection.Missing.Value,System.Reflection.Missing.Value) ;
excelWorkbooks.Close();
excelApp.Quit();
Marshal.ReleaseComObject(excelWorksheet);
Marshal.ReleaseComObject(excelSheets);
Marshal.ReleaseComObject(excelWorkbooks);
Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(excelApp);
excelWorksheet = null;
excelSheets = null;
excelWorkbooks = null;
excelWorkbook = null;
excelApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
This code creates an Excel application, populates it, then lets us close it. Invoking the button3 code creates it, button4 removes it.
Now, without the calls to the garbage collector, excel.exe hung around if the code behind button3, button4, button3, button4 is invoked.
In the short-term, I would suggest adding the additional calls to the garbage collector (although, and this confirms it, memory reclamation is not guaranteed). It’s time to read up on the garbage collector and COM Interop.
I used Visual Studio 2003 and Office 2003 to test this.
Technorati Tags: Excel, InterOp, Kill Process, C#
RSS 2.0
8:54 am on July 20th, 2005 1
You don’t know how long i searched to find a solution to this problem that actually works! Thanks very much for this blog!
2:45 am on February 1st, 2006 2
I cannot get this to work, i have carefully called Marshall.Release on every object and am calling GC.Collect … NO LUCK!
I am using JScript .Net … can you offer assistance?
5:39 pm on February 7th, 2007 3
i implemented this solution. now the first excel.exe process keeps running. the following are finished successfull. any ideas why the first one keeps running?
12:09 am on November 6th, 2007 4
this does not work.
3:07 pm on January 10th, 2008 5
Many thanks! this solution does work – you have to be very careful about releasing all excel objects (including ranges etc.) & setting to nothing before the GC code
11:06 pm on July 21st, 2008 6
Your sample incorrectly shows how to get the Excel process to quit.
You indicate that the Garbage Collector should be used to force object cleanup. This is almost NEVER the way to solve problems.
The correct way is to make explicit references to any and all Excel objects so that those references can be passed to Marshal.ReleaseComObject(obj). When ALL Excel object references have been passed to ReleaseComObject(), except for the Excel object reference, use that remaining reference to call its .Quit() method and then pass that object reference to the ReleaseComObject() method.
The reason so many people have problems with this is that the various Excel methods and properties return Excel object references that aren’t being explicitly referenced and therefor there is no way to pass them to Marshal.ReleaseComObject().
-Scott M.
9:44 am on July 23rd, 2008 7
@ScottM – I agree, the garbage collector should be left to its own devices. This example is posted here merely as a learning experience, not a recommendation. I would be happy to update this post with any code you may provide as noted in your penultimate paragraph. Thanks!
8:31 pm on November 5th, 2008 8
It works! I use Visual Studio 2005 with SSIS on Office 2003. Thanks!
4:38 pm on November 14th, 2008 9
It is not necessary to use “GC.GetTotalMemory()” at all and it will still work wonders.
4:00 pm on February 19th, 2009 10
I’m having the same problem in VB.NET 3.5 SP1 and the following code STILL leaves the EXCEL.EXE hanging around:
xlWorkbook.Close(SaveChanges:=False)
xlApplication.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApplication)
xlRange = Nothing
xlWorksheet = Nothing
xlSheets = Nothing
xlWorkbook = Nothing
xlApplication = Nothing
GC.GetTotalMemory(False)
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.GetTotalMemory(True)
2:02 pm on May 13th, 2009 11
Hi;
I guess it is your answer:
using System.Diagnostics;
using System.Collections;
…
…
…
Hashtable myHashtable;
private void btnExport_Click(object sender, EventArgs e)
{
// get process ids before running the excel codes
CheckExcellProcesses();
// export to excel
ExportDataToExcel();
// kill the right process after export completed
KillExcel();
}
private void ExportDataToExcel()
{
// your export process is here…
}
private void CheckExcellProcesses()
{
Process[] AllProcesses = Process.GetProcessesByName(”excel”);
myHashtable = new Hashtable();
int iCount = 0;
foreach ( Process ExcelProcess in AllProcesses) {
myHashtable.Add(ExcelProcess.Id, iCount);
iCount = iCount + 1;
}
}
private void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName(”excel”);
// check to kill the right process
foreach ( Process ExcelProcess in AllProcesses) {
if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
ExcelProcess.Kill();
}
AllProcesses = null;
}
Hope this helps.
10:01 am on May 23rd, 2009 12
You don’t need
# excelWorksheet = null;
# excelSheets = null;
# excelWorkbooks = null;
# excelWorkbook = null;
# excelApp = null;
#
# GC.GetTotalMemory(false);
# GC.Collect();
# GC.WaitForPendingFinalizers();
# GC.Collect();
# GC.GetTotalMemory(true);
The problem in this line excelWorksheet.Cells[1,1] = “42″;
Correct solution #1
Range range = excelWorksheet.Cells;
range[1,1] = “42″;
Marshal.ReleaseComObject(range);
Correct solution #2
To set/get properties use
obj.GetType().InvokeMember(name, BindingFlags.GetProperty, null, obj, null);
Correct solution #3
Create AppDomain (AppDomain.CreateDomain) and work with COM in created domain.
If you can read in Russian http://blog.byndyu.ru/2009/05/excelexe-interopexcel.html =)
10:47 pm on June 3rd, 2009 13
Well the problem it’s quite simple to fix, the problem is in the definition and dispose of the excel objects (and I hope you’ll excuse me for my preference in programing VB.NET)
Dim oExcel As New Microsoft.Office.Interop.Excel.Application()
Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks = oExcel.Workbooks
Dim oBook2 As Microsoft.Office.Interop.Excel.Workbook = oBooks.Add
Dim oWorksheet As Microsoft.Office.Interop.Excel.Worksheet = oExcel.ActiveSheet
oExcel.DisplayAlerts = False
oBook2 = oExcel.Workbooks.Add
Dim Cont As Integer
For Cont = 0 To Total_Hojas – 1 Step 1
oWorksheet = oBook2.Worksheets.Add
oWorksheet.Name = Nombre_Hoja(Cont)
oWorksheet.Activate()
If Cont = 0 Then
oWorksheet = oBook2.Worksheets.Item(”hoja1″)
oWorksheet.Delete()
oWorksheet = oBook2.Worksheets.Item(”hoja2″)
oWorksheet.Delete()
oWorksheet = oBook2.Worksheets.Item(”hoja3″)
oWorksheet.Delete()
oWorksheet = oBook2.Worksheets.Item(Nombre_Hoja(Cont))
oWorksheet.Activate()
End If
‘
oWorksheet.Range(”A1″).Value = “Id”
oWorksheet.Range(”B1″).Value = “BUC”
oWorksheet.Range(”C1″).Value = “N_Solic”
oWorksheet.Range(”D1″).Value = “N_Credito”
oWorksheet.Range(”E1″).Value = “Fecha_Form”
oWorksheet.Range(”F1″).Value = “Hora_Form”
oWorksheet.Range(”G1″).Value = “Analista_Contr”
oWorksheet.Range(”H1″).Value = “Tipo_Cred_Form”
oWorksheet.Range(”I1″).Value = “Monto_Formalizado”
oWorksheet.Range(”J1″).Value = “Monto_Aut_Admision”
oWorksheet.Range(”K1″).Value = “Fecha_Carga”
oWorksheet.Range(”L1″).Value = “Tipo_Folio”
oWorksheet.Range(”M1″).Value = “BUC390″
oWorksheet.Range(”N1″).Value = “FECHA_RIES”
oWorksheet.Range(”O1″).Value = “Comentario”
Next
oWorksheet.SaveAs(Nombre_Archivo)
oBook2.Save()
Do While Not oBook2.Saved
System.Windows.Forms.Application.DoEvents()
Loop
oBook2.Saved = True
oBook2.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorksheet)
oWorksheet = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook2)
oBook2 = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing
oExcel.Workbooks.Close()
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
Hope to work for you
12:41 pm on June 5th, 2009 14
You _don’t_ need this code:
oWorksheet = Nothing
oBooks = Nothing
oExcel = Nothing
…
And this is absolutely useless code:
GC.Collect()
GC.WaitForPendingFinalizers()
If your Excel process ‘get stuck’ in memory then GC will not remove it anyway.
Use this code to 100% sure:
Application application = new Application {Visible = false, DisplayAlerts = false};
int applicationHwnd = application.Hwnd;
…
Process.GetProcesses()
.Where(p => p.MainWindowHandle.ToInt32() == applicationHwnd)
.ToList()
.ForEach(process => process.Kill());
2:07 pm on July 14th, 2009 15
Hi,
to avoid all this problems you can try using GemBox.Spreadsheet – .NET component for Excel. Component is free for commercial use (limit of 150 rows).
http://www.gemboxsoftware.com/GBSpreadsheet.htm
5:22 pm on July 19th, 2009 16
Thanks Mahdi. Your code is working really good!
11:52 am on August 27th, 2009 17
My solution:
——————————–calling class—————————————-
private void button3_Click(object sender, EventArgs e)
{
UpdateExcel.InExcel excel = new UpdateExcel.InExcel();
excel.SetDato(AppDomain.CurrentDomain.BaseDirectory + “poblacionporedad.xls”, “poblacionporedad”, “N5″, “prueba2sssss”);
}
————————————————————————
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
namespace UpdateExcel
{
class InExcel
{
Microsoft.Office.Interop.Excel.Application aplicacion = null;
public int SetDato(string excelfile, string sheet, string celd, string chain)
{
aplicacion = (Microsoft.Office.Interop.Excel.Application)new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
Microsoft.Office.Interop.Excel.Workbook book = null;
book = aplicacion.Workbooks.Open(excelfile,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[sheet];
Microsoft.Office.Interop.Excel.Range rng2 = ws.get_Range(celd, celd);
rng2.Value2 = chain;
book.Save();//.Close(true, null, null);
return 1;
}
catch
{
return 0;
}
finally
{
if (aplicacion != null)
{
try
{
aplicacion.Visible = true;
killerexplicit(”Excel”, new IntPtr(aplicacion.Hwnd));
System.GC.Collect();
}
catch
{
}
}
}
}
private void killerexplicit(string ProcessName, IntPtr puntero)
{
foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName(ProcessName))
{
if (proc.MainWindowHandle == puntero)
proc.Kill();
}
}
}
}
11:07 pm on August 28th, 2009 18
Shout out to Alexander: Your solution was probably the most accurate. I guess being they’re right that C# made me a lazy programmer.
I had a rather large excel document, so I just replaced
objPrinterListing.Cells[1, 1] = “Locations and Printers”;
with
SetCell(objPrinterListing, 1, 1, “Locations and Printers”);
The method in question was as such:
private void SetCell(Microsoft.Office.Interop.Excel.Worksheet objWorksheet, object rowNum, object colNum, object Value)
{
Range objRange = objWorksheet.Cells;
objRange[rowNum, colNum] = Value;
Marshal.ReleaseComObject(objRange);
}
Since everything occurred within a try…catch…finally clause, I simply modified the finally clause to close the Excel.Workbook and Excel.Workbooks objects, exit the Excel.Application, then proceeded to call Marshal.ReleaseComObject in order from least to greatest: Excel.Worksheet, Excel.Worksheets, Excel.Workbook, Excel.Workbooks, Excel.Application. As soon as I released the Application, Excel disappeared from my processes.
Here’s my finally clause:
finally
{
if (objWorkbook != null)
{
objWorkbook.Close(false, Type.Missing, Type.Missing);
}
if (objWorkbooks != null)
{
objWorkbooks.Close();
}
if (theApp != null)
{
theApp.Quit();
}
if (objPrinterListing != null)
{
while (Marshal.ReleaseComObject(objPrinterListing) > 0)
{
}
}
if (objProposal != null)
{
while (Marshal.ReleaseComObject(objProposal) > 0)
{
}
}
if (objOrderItems != null)
{
while (Marshal.ReleaseComObject(objOrderItems) > 0)
{
}
}
if (objWorksheets != null)
{
while (Marshal.ReleaseComObject(objWorksheets) > 0)
{
}
}
if (objWorkbook != null)
{
while (Marshal.ReleaseComObject(objWorkbook) > 0)
{
}
}
if (objWorkbooks != null)
{
while (Marshal.ReleaseComObject(objWorkbooks) > 0)
{
}
}
if (theApp != null)
{
while (Marshal.ReleaseComObject(theApp) > 0)
{
}
}
}
This was tested with IIS7 with Office 2007. It could probably use some improvement, so feel free to expand upon this and modify it to your needs.
8:56 am on January 9th, 2010 19
how to use this?
1:59 am on January 30th, 2010 20
OTACON’s source worked well for me. The single excel application seemed to continue until the application was closed itself, but once my app shut down, that excel process terminated too.