It was a quiet Tuesday afternoon in Singapore. The sky was greyish with clouds. The air was cool with slow wind. I sat in front of my computer mindlessly seriously reading blog posts in the neighbouring blogs.
The suddenly out of the darkness his room, Buddie approached me complaining about his Web Application. His ASP.NET web application is using Interop.Excel to read data stored in XLS file. The problem is that those Excel processes are refusing to be closed, resulting tens mega bytes of memory hogged.
Just to illustrate how the Excel process is started, please take a look of the following code which shamelessly proudly copied from MSDN:
//----------------------------------------------------------------------- // This file is part of the Microsoft .NET Framework SDK Code Samples. // // Copyright (C) Microsoft Corporation. All rights reserved. // //This source code is intended only as a supplement to Microsoft //Development Tools and/or on-line documentation. See these other //materials for detailed information regarding Microsoft code samples. // //THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY //KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE //IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A //PARTICULAR PURPOSE. //----------------------------------------------------------------------- using System; using System.Reflection; // For Missing.Value and BindingFlags using System.Runtime.InteropServices; // For COMException using Microsoft.Office.Interop.Excel; class AutoExcel { public static int Main() { Console.WriteLine ("Creating new Excel.Application"); Application app = new Application(); if (app == null) { Console.WriteLine("ERROR: EXCEL couldn't be started!"); return 0; } Console.WriteLine ("Making application visible"); app.Visible = true; Console.WriteLine ("Getting the workbooks collection"); Workbooks workbooks = app.Workbooks; Console.WriteLine ("Adding a new workbook"); _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Console.WriteLine ("Getting the worksheets collection"); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet) sheets.get_Item(1); if (worksheet == null) { Console.WriteLine ("ERROR: worksheet == null"); } Console.WriteLine ("Setting the value for cell"); // This paragraph puts the value 5 to the cell G1 Range range1 = worksheet.get_Range("G1", Missing.Value); if (range1 == null) { Console.WriteLine ("ERROR: range == null"); } const int nCells = 5; range1.Value2 = nCells; // This paragraph sends single dimension array to Excel Range range2 = worksheet.get_Range("A1", "E1"); int[] array2 = new int [nCells]; for (int i=0; i < array2.GetLength(0); i++) { array2[i] = i+1; } range2.Value2 = array2; // This paragraph sends two dimension array to Excel Range range3 = worksheet.get_Range("A2", "E3"); int[,] array3 = new int [2, nCells]; for (int i=0; i < array3.GetLength(0); i++) { for (int j=0; j < array3.GetLength(1); j++) { array3[i, j] = i*10 + j; } } range3.Value2 = array3; // This paragraph reads two dimension array from Excel Range range4 = worksheet.get_Range("A2", "E3"); Object[,] array4; array4 = (Object[,])range4.Value2; for (int i=array4.GetLowerBound(0); i <= array4.GetUpperBound(0); i++) { for (int j=array4.GetLowerBound(1); j <= array4.GetUpperBound(1); j++) { if ((double)array4[i, j] != array3[i-1, j-1]) { Console.WriteLine ("ERROR: Comparison FAILED!"); return 0; } } } // This paragraph fills two dimension array with points for two curves and sends it to Excel Range range5 = worksheet.get_Range("A5", "J6"); double[,] array5 = new double[2, 10]; for (int j=0; j < array5.GetLength(1); j++) { double arg = Math.PI/array5.GetLength(1) * j; array5[0, j] = Math.Sin(arg); array5[1, j] = Math.Cos(arg); } range5.Value2 = array5; // The following code draws the chart range5.Select(); ChartObjects chartobjects = (ChartObjects) worksheet.ChartObjects(Missing.Value); ChartObject chartobject = (ChartObject) chartobjects.Add(10 /*Left*/, 100 /*Top*/, 450 /*Width*/, 250 /*Height*/); _Chart chart = (_Chart) chartobject.Chart; // Call to chart.ChartWizard() is shown using late binding technique solely for the demonstration purposes Object[] args7 = new Object[11]; args7[0] = range5; // Source args7[1] = XlChartType.xl3DColumn; // Gallery args7[2] = Missing.Value; // Format args7[3] = XlRowCol.xlRows; // PlotBy args7[4] = 0; // CategoryLabels args7[5] = 0; // SeriesLabels args7[6] = true; // HasLegend args7[7] = "Sample Chart"; // Title args7[8] = "Sample Category Type"; // CategoryTitle args7[9] = "Sample Value Type"; // ValueTitle args7[10] = Missing.Value; // ExtraTitle chart.GetType().InvokeMember("ChartWizard", BindingFlags.InvokeMethod, null, chart, args7); Console.WriteLine ("Press ENTER to finish the sample:"); Console.ReadLine(); try { // If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely workbook.Saved = true; app.UserControl = false; app.Quit(); } catch (COMException) { Console.WriteLine ("User closed Excel manually, so we don't have to do that"); } Console.WriteLine ("Sample successfully finished!"); return 100; } }
I don't know how the exact code in his ASPX file, but it should be similar to the example detailed above. Actually, Buddie inherited the code from previous programmer which use the following brutal method to terminate the Excel Process:
Process[] procs = Process.GetProcessesByName("EXCEL"); foreach (Process p in procs) { p.Kill(); }
When I was consulted about the code before, I said that this method might pose a problem with many concurrent accesses to the ASPX page. He then decided to comment the portion of code described above. 😀 Which of course, resulting the memory hogged by many unclosed Excel processes. 😀
After fooling around with the code (huhu.. I thought fooling around only used by a person to the opposite sex :-D), I came up with a solution:
Process[] procs = Process.GetProcessesByName("EXCEL"); foreach (Process p in procs) { int baseAdd = p.MainModule.BaseAddress.ToInt32(); //oXL is Excel.ApplicationClass object if (baseAdd == oXL.Hinstance) p.Kill(); }
Basically the idea is to compare the lowest memory address of the Excel Application and the Process obtained by GetProcessesByName(). This way, the ASPX page will terminate exclusively the Excel Application that started by it.
Comments?
UPDATE: Please check the track back on the comments below.
loading...
About Hardono
Incoming Search
excel process
Did u actually try to execute the above code when multiple instances (processes) of excel are running in task manager? p.MainModule.BaseAddress always returns the memory address of Excel.exe (not of the particular excel instance) and hence the above code kills all the excel processes running in the server.
It is interesting to note that the previous Excel version (we tested it on Office 2003) a new Excel.Exe process will be created everytime we open a document.
But in Office 2007, only one Excel.Exe exists in the memory. No matter how many documents you open.
I’ve tried your code and I noticed that Excel applications that were opened AFTER my application started are also being closed. Any suggestions?
Hardono: Please check the trackback link in post no. 2
Process[] procs = Process.GetProcessesByName(“EXCEL”);
foreach (Process p in procs)
{
p.Kill();
}
Thank you so much Sodeve,
This little part of code you wrote has saved my life. I really digged inside many different ebooks or even inside msdn and couldn’t find this simple solution.
The same solution for word (if needed):
foreach (Process p in Process.GetProcessesByName(“WINWORD”))
{
int baseAdd = p.MainModule.BaseAddress.ToInt32();
//oXL is Excel.ApplicationClass object
if (baseAdd == oXL.Hinstance)
p.Kill();
}
Many thanks again.. god bless you my friend..
Ata..
@Ata: Cheers mate! 🙂
Im afraid this doesn’t appear to be working when using excel 2003.
It seems that all the excel.exe processes share the same Hinstance.
This happens to me with excel opened before and after my application starts with this code.
I have a different approach which isn’t 100% but closes the correct excel application.
***
When creating the excel application instance i make a note of the time before and after the instance is created
Dim xlApplication As Application
Dim xlApplicationStartTime1 As DateTime
Dim xlApplicationStartTime2 As DateTime
xlApplicationStartTime1 = DateTime.Now()
xlApplication = New Application
xlApplicationStartTime2 = DateTime.Now()
” For the kill process, I check the StartTime of the process
For Each p As Process In Process.GetProcessesByName(“EXCEL”)
If p.StartTime >= xlApplicationStartTime1 And p.StartTime
@Adam: Cool trick 🙂
[DllImport(“user32.dll”, SetLastError = true)]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out IntPtr lpdwProcessId);
//oXL is Excel.Application object
IntPtr excelHwndIntPtr = new IntPtr(oXL.Hwnd);
IntPtr excelProcessIdIntPtr;
GetWindowThreadProcessId(excelHwndIntPtr, out excelProcessIdIntPtr);
Process xlProcess = Process.GetProcessById(excelProcessIdIntPtr.ToInt32());
xlProcess.Kill();
Awesome this works perfectly. Thank you for your contribution.