browse by category or date

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.

GD Star Rating
loading...

Possibly relevant:

About Hardono

Howdy! I'm Hardono. I am working as a Software Developer. I am working mostly in Windows, dealing with .NET, conversing in C#. But I know a bit of Linux, mainly because I need to keep this blog operational. I've been working in Logistics/Transport industry for more than 11 years.

Incoming Search

excel process

1 Trackback

 

10 comments so far

Add Your Comment
  1. 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.

  2. 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.

  3. 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

  4. Process[] procs = Process.GetProcessesByName(“EXCEL”);
    foreach (Process p in procs)
    {
    p.Kill();
    }

  5. 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..

  6. @Ata: Cheers mate! 🙂

  7. 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

  8. @Adam: Cool trick 🙂

  9. [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.