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#
You don’t know how long i searched to find a solution to this problem that actually works! Thanks very much for this blog!
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?
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?
this does not work.
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
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.
@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!
It works! I use Visual Studio 2005 with SSIS on Office 2003. Thanks!
It is not necessary to use “GC.GetTotalMemory()” at all and it will still work wonders.
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)
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.
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 =)
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
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());
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
Thanks Mahdi. Your code is working really good!
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();
}
}
}
}
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.
how to use this?
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.
It works ! Great !
Thank you
Sorry to revive this post, but it appeared on the first page of my search and I thought it was worth addressing some of the mistakes that the above comments seem to be making.
Calling GC for any reason is generally a sign that you’ve missed something somewhere else – especially in this example.
The problem the above comments show is that they’ve made calls to object references that they’ve never released.
As Scott Marcus said almost two years ago, you have to release EVERY object.
The following code (while simple) should always close Excel properly:
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks wbks = xl.Workbooks;
wbks.Add(System.Reflection.Missing.Value); // New, could be Open
Marshal.ReleaseComObject(wbks);
Excel.Worksheet ws = (Excel.Worksheet)xl.ActiveSheet;
Excel.Range r = ws.get_Range(“A1”, “B2”);
Marshal.ReleaseComObject(ws);
Console.WriteLine(“A1: ” + ((object[,])r.Value2)[1, 1]);
Marshal.ReleaseComObject(r);
xl.Quit();
Marshal.ReleaseComObject(xl);
The reason? I know that every Excel object I’ve used in it has been properly released with MarshalReleaseComObject; INCLUDING the call to the Workbooks collection. If you were to call xl.Workbooks.Add (etc) in one line, you’d leave the reference in place and prevent the Excel instance from dying. You MUST use the internal object through variables.
Similarly, calling Marshal.ReleaseComObject(xl.Workbooks) won’t solve it as it will release a new reference of the object, leaving all previous references in place.
So, to sum up, if you handle each reference properly and make sure you release every single one, you’ll never need dirty GC hacks.
I’ve got a small library which wraps all of the Excel functionality I need and handles this for me – one implementation, unlimited uses.
Hope that helps someone in the future.
Use the Mahdi code post number 11.
You are done !!!
@Ashraf: Mahdi code’s code will create problem in case there is another application which also uses the “Excel.exe”. This code will kill all the “Excel” processes which are not even created by it :(.
I use a best tool for creating an Excel Spreadsheet with C#, it can reading and writing to excel 97 ~ 2010 from c#,extremely easy to use,I use it long time quit good.
http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html
Thank you very much. Keep posting like this useful articles.
@Ian Yates
I finally got your solution to work after about an hour. It seems like the cleanest way of going about this.
The key is to, like you said, release every single object and I do mean every single object that you use from Microsoft.Office.Interop.Excel. If you do something like, range.EntireColumn.AutoFit();, not only do need to store the range object, you also need to store range.EntireColumn in an object and then release those two objects.
I have the principle behind the code from post 22 working, however, although it doesnt delete the excel processes for others that might be running which arnt related to the application, it only deletes the relevant process when the application is minimised or closed.
I’d like to keep the form shwoing and the application running so a valid selection and query can be made. How can this be done without closing the appllication ?
To completly remove Excel from memory we must remove ALL used objects.
Code like:
Excel.Workbook wBook = oApp.Workbooks.Add(Missing.Value);
Excel.Application oApp = new Excel.ApplicationClass();
…
Marshal.ReleaseComObject(wBook);
Marshal.ReleaseComObject(oApp);
is incorrect because we don’t release Workbooks object 😛
Code like:
Excel.Application oApp = new Excel.ApplicationClass();
Excel.Workbooks wBooks = oApp.Workbooks;
Excel.Workbook wBook = wBooks.Add(Missing.Value);
…
Marshal.ReleaseComObject(wBook);
Marshal.ReleaseComObject(wBooks);
Marshal.ReleaseComObject(oApp);
is correct and works.
Next correct sample:
…..
Excel.Font font = oRng.Font;
font.Bold = FontBold;
font.Size = FontSize;
font.Color = Kolor;
not:
oRng.Font.Bold = FontBold;
oRng.Font.Size = FontSize;
oRng.Font.Color = Kolor;
best regards,
maxon
Sorry, I skipped Marshal.ReleaseComObject in last sample:
….
Excel.Font font = oRng.Font;
font.Bold = FontBold;
font.Size = FontSize;
font.Color = Kolor;
Marshal.ReleaseComObject(font);
Marshal.ReleaseComObject(oRng);
Actually I found out, that this solution will only work if your app is processed as admin, working with windows 7. If I don’t start my visual studio 2010 explicit as admin, i’m getting no errors, but the code doesn’t work. The excel.exe process remains in the stack. So if it’s not working for you, first check if you’re having the same issues and try again with admin rights 😉
Mahdi’s solution is simple and it works in all circumstances. I tried to close COM objects, but I obviously was unable to find a right combination of calls. So, I just used KillExcel() to remove all EXCEL.EXE PIDs, I have created in my app. Thanks.
good one
Thank YOU!!!!
It is well-known that it is only you just only you that can write the aticle perfect!
As is known to all, Burberry Rain Boots are popular for the wonderful design. Now Burberry Outlet London in the new season offer various discounts for you. Buy great items from Burberry Outlet shops.
you have a good taste. the people in the USA thank for that god give you writting gift that so good comment you send to me that very useful !
measles.* Certain holed stones in Cornwall are resorted to by the peasantry
Estonia people now it is no doult that you are a gift!
Below is the code which open and close the excel instance. Have good day.
string strFilePath = @”C:\Sample.xlsx”;
try
{
Excel.Application excelApp = null;
Excel.Workbook excelWorkbook = null;
Excel.Sheets excelSheets = null;
Excel.Worksheet excelWorksheet = null;
Excel.Workbooks excelWorkbooks = null;
Excel.Range excelUsedRange = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
int nData = excelApp.Hwnd;
// excelApp = new Excel.ApplicationClass();
//excelApp.Visible = true;
excelWorkbooks = excelApp.Workbooks;
excelWorkbook = excelWorkbooks.Add(System.Reflection.Missing.Value);
excelWorkbook = excelApp.Workbooks.Open(strFilePath, 2, false);
//excelWorkbook = excelApp.Workbooks.Open(strFilePath,
// 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);
excelSheets = excelWorkbook.Worksheets;
// excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);
excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[“Dem0_1”];
excelUsedRange = excelWorksheet.UsedRange;
//Excel.Range lastCell = usedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
//int lastRow = lastCell.Row;
//int lastCol = lastCell.Column;
//int rowMin = lastRow + 1;
//int colMin = lastCol + 1;
int nRowsCount = excelUsedRange.Rows.Count;
int nColCount = excelUsedRange.Columns.Count;
int N_Quality_Header = -1;
int N_Measurement_Name = -1;
int N_Lower_Tolerance = -1;
int N_Upper_Tolerance = -1;
//Read the Columns Index
for (int nColIndex = 1; nColIndex -1)
N_Measurement_Name = nColIndex;
else if (strCellValue.IndexOf(“Lower Tolerance”, StringComparison.OrdinalIgnoreCase) > -1)
N_Lower_Tolerance = nColIndex;
else if (strCellValue.IndexOf(“Upper Tolerance”, StringComparison.OrdinalIgnoreCase) > -1)
N_Upper_Tolerance = nColIndex;
}
//Read all rows to get the values
for (int nRowIndex = 2; nRowIndex <= nRowsCount; nRowIndex++)
{
Excel.Range cellQualityHeader = usedRange.Cells[nRowIndex, N_Quality_Header] as Excel.Range;
String strValue = cellQualityHeader.Value2.ToString();
if (strValue == String_Empty)
continue;
}
}
catch (Exception oException)
{
}
finally
{
excelUsedRange.Clear();
//excelWorkbook.Save();
excelWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelWorkbooks.Close();
excelApp.Quit();
Marshal.ReleaseComObject(excelUsedRange);
Marshal.ReleaseComObject(excelWorksheet);
Marshal.ReleaseComObject(excelSheets);
Marshal.ReleaseComObject(excelWorkbooks);
Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(excelApp);
excelUsedRange = null;
excelWorksheet = null;
excelSheets = null;
excelWorkbooks = null;
excelWorkbook = null;
excelApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
Thanks a lot for such a good solution…
You have done excellent job.
Keep it up…
its great!!!! working perfect!!!!