Excel Application Not Exiting in Interop

Question

I am using the following piece of code to write into the excel file dynamically on a ASP.net form that can save the data table to excel.

//Create Excel Object
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(target);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

worksheet.Name = "Worksheet1";
excel.Visible = false;
//Generate Fields Name
foreach (DataColumn col in dataTable.Columns)
{
    colIndex++;
    excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNo, columnNo];
for (int row = 0; row < rowNo; row++)
{
    for (int col = 0; col < columnNo; col++)
    {
        objData[row, col] = dataTable.Rows[row][col];
    }
}
Microsoft.Office.Interop.Excel.Range range;
range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNo + 1, columnNo]];
range.Value2 = objData;
worksheet.Columns.AutoFit();

workbook.Save();
workbook.Close();

IntPtr hwnd = new IntPtr(excel.Hwnd);
IntPtr processId;
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill();

//excel.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();

The data is written fine into the excel file. However, when I try to kill the process using proc.Kill() as above, I get the following exception

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ComponentModel.Win32Exception: Access is denied

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Win32Exception (0x80004005): Access is denied]
   System.Diagnostics.Process.GetProcessHandle(Int32 access, Boolean throwIfExited) +1985316
   System.Diagnostics.Process.Kill() +49
   Faculty_Report1.FetchData_EXCEL(String prog, String cls, String spcln, String fromdate, String todate) +2413
   Faculty_Report1.fetchselectedvalues_EXCEL() +1044
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +187
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +165
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3707

Instead of using the proc.Kill() method, I have tried using excel.Quit() method but the process does not terminate. When I do this, I end up with a new excel process everytime a user tries to generate a report.

I have user impersonation in the web.config file, but this does not change anything.

  <system.web>
    <identity impersonate="true" userName="xxxxxx" password="xxxxxxxx" />
  </system.web>

Any pointers for me on how to avoid the exception and make sure that the excel process terminates fine? I have tried the COM clean up solution which I found on here but it doesn't work..

Answer 1

To create simple excel data tables there are several options besides interop that might be interesting, however there are some methods that can help with excel when interop has to be used.

First thing's first. If you're sure you can reuse existing applications, you can use 'oldschool' getactiveobject to get an existing instance:

    using System.Runtime.InteropServices;

    Microsoft.Office.Interop.Excel.Application excel;
            try
            {
                excel = (Microsoft.Office.Interop.Excel)Marshal.GetActiveObject("Excel.Application");
            }
            catch
            {
                 excel = new Microsoft.Office.Interop.Excel.Application();
            }

For an excel application to quit successfully, all handles must be destroyed , but I guess that's what you mean with the COM clean up? It can be a pain to make sure all handles are released with the marshal.releasecomobject (perhaps that's not necessary anymore in modern interop? ), that's why I wrap every office interop 'handle' such as worksheet, workbook, range, etc in a wrapper. The code I have for that isn't much use separated, but I'll post it here anyway to give a general idea:

public class WrapperBase<T>: IWrapper
{

    protected T obj;
    protected internal WrapperBase(IWrapper Owner)
    {
        SetOwner(Owner);
    }

    protected WrapperBase() { }


    CultureSwitcher cultswitch;
    public CultureSwitcher CultureSwitcher
    {
        get
        {
            if (cultswitch == null)
            {
                if (owner != null) return owner.CultureSwitcher;
                cultswitch = new CultureSwitcher();
            }
            return cultswitch;
        }
    }

    public T Object { get { return obj; } internal set { obj = value; } }


    //public abstract void Save();

    public bool IsDisposed { get { return disposed; } }

    bool disposed;
    public void Dispose()
    {
        Dispose(false);
    }

    void Dispose(bool fromfinalize)
    {
        if (disposed) return;
        disposed = true;
        if (!fromfinalize)
            GC.SuppressFinalize(this);

        CultureSwitcher.Try(OnDisposing);

        if (obj != null)
        {
            while (Marshal.ReleaseComObject(obj) > 0) { }
            obj = default(T);
        }

        if (cultswitch != null)
        {
            cultswitch.Dispose();
            cultswitch = null;
        }

        SetOwner(null);
    }

    protected virtual void OnDisposing()
    {
        if (children != null)
        {
            foreach (var c in children)
                c.Dispose();
            children = null;
        }
    }

    ~WrapperBase()
    {
        Dispose(true);
    }

    List<IWrapper> children;
    IWrapper owner;

    public IWrapper Owner
    {
        get { return owner; }
    }

    protected void SetOwner(IWrapper Owner)
    {
        if (Owner == owner) return;
        if (owner != null)
        {
            owner.DeRegister(this);
        }
        owner = Owner;
        if (owner != null)
        {
            owner.Register(this);
        }
    }

    public void Register(IWrapper Child)
    {
        if (disposed)
            throw new ObjectDisposedException("Cannot register children after disposing");
        if (children == null)
            children = new List<IWrapper>();
        children.Add(Child);
    }

    public void DeRegister(IWrapper Child)
    {
        if (disposed) return;
        children.Remove(Child);
    }

    protected IEnumerable<IWrapper> GetChildren()
    {
        return children;
    }
}

Answer 2

I have also had problems in the past getting Excel applications to truely close using Interop, but found the solution to be releasing the instance of the application. Try the following:

excel.Quit();
excel = null;

It sounds too simple to work, but it successfully prevented ghost instances of Excel from lingering in the OS in my application.

Answer 3

Using Interop on the server (like ASP.NET) is NOT supported by MS - see http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

Since Windows Vista MS introduced several security-related measures which prevent a Windows Service from doing "desktop-like" things... which means you would have to circumvent several security measures to get it to work (NOT recommended!).

To deal with Excel in a server-scenario there are several options (free and commercial) out there:

I can recommend Aspose.Cells and Flexcel... didn't try SpreadsheetGear but hear+read lots of good things about it...

Free options (though for the newer xlsx format only!) are for example OpenXML 2 from MS and EPPlus.