Dealing with multiple instance of Excel (or any other COM object)
In an application, I have to call many functions of Excel for my financial and statistical calculation. For this, I created an class named ExcelObject. This class has static members like CalculateFInv which inturn calls Excel's function.
This code is running successfully but with one issue. If the application exists abnormally (like I stop execution during debugging), the instance of Excel objects keep running in the memory. And when I run application again, it started a new instance. So I got many instance of excel.exe running in the memory. At one time I found twelve instances of excel.exe in the task manager. This had bad impact on the performance and is practically unacceptable.
So I decide to fix the issue. I created a static constructor of the class which checks if there is any running instance of Excel.exe. If any instance of Excel.exe is running, it uses this instance otherwise it creates new instance. It works fine even if Excel application is open on the system, it uses the same instance. So effectively I has only one instance of Excel.exe running at any time.
And here is the entire code of the class.
using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
using System.Diagnostics;
namespace Yanesh.Utilities
{
/// <summary>
/// This class provides the functionality to call excel functions for distribution.
/// </summary>
public class ExcelObject
{
static Excel.Application excelApplication = null;
static ExcelObject()
{
try
{
excelApplication = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (System.Runtime.InteropServices.COMException ex)
{
excelApplication = new Excel.ApplicationClass();
}
}
/// <summary>
/// Returns the inverse of the F probability distribution using MS-Excel.
/// </summary>
/// <param name="arg1">probability</param>
/// <param name="arg2">Deg_freedom1</param>
/// <param name="arg3">Deg_freedom2</param>
/// <returns></returns>
public static double CalculateFInv(double arg1, double arg2, double arg3)
{
return excelApplication.WorksheetFunction.FInv(arg1, arg2, arg3);
}
}
}
