tags:

views:

1980

answers:

18

Suppose a ASP.NET web application generates automated Excel Reports on the server. How do we kill a server-side Excel.EXE once the processing is over. I am raising this purposely, because I believe that the Garbage Collecter does not clean the Excel executable even after the Excel file is closed.

Any pointers would be helpful?

+1  A: 

The command you need is "taskkill".

http://technet.microsoft.com/en-us/library/bb491009.aspx

> taskkill excel.exe
Mark Ingram
I just tried this in VB.Net, and it worksSystem.Diagnostics.Process.Start("taskkill.exe", "-im excel.exe /f")
JonnyGold
+3  A: 

Are you using VSTO? You can close the Excel app after you finished with excelobject.Quit(); It worked for me, but I don't use Excel on server-side anymore.

You can have a look on Excel's XML schema to build the Excel file without Excel itself. Check out CarlosAg Excel Writer, which does exactly the same.

Biri
+15  A: 

Sorry to say this, and I'm not trying to be smart, but... don't put office on the server!!!

That's if I've understood correctly! :)

EDIT: Even though I've been marked down for this, I will never ever advocate running Office on the server - it has proven way too much of a pain in the ass for me in the past.

Having said that, the same now goes for me and Crystal Reports ;-)

Galwegian
As a general principle I agree, but in the real world there are many instances where Excel is a key part of a business and many business processes have been built around it. When these need to be scaled, the only option often is to run Excel as a server side component.
John Channing
Don't worry, some of us UP-voted you :) I totally agree - running Excel as some kind of data-processor (server OR client side) seems insane. Excel is a (bloated) front-end user application, not something that should be used for backend processing.
Bobby Jack
This is actually one good example of 'code-reuse' in Joel's article "Why are the Microsoft Office file formats so complicated?"
Huppie
Sometimes there's absolutely no choice...
code4life
+1  A: 

Like this

John Channing
+2  A: 

:). I jotted down my skirmish with Excel here. It also has some links that I found after some heavy searching. Hope it helps.
Basically Excel is a pain even though it can be automated.

Gishu
A: 

You need safely dispose all COM interop objects after you end your work. By "all" I mean absolutely all: collections property values and so on. I've created stack object and pushed objects during their setup:

Stack<object> comObjectsToRelease = new Stack<object>();
...
Log("Creating VBProject object.");
VBProject vbProject = workbook.VBProject;
comObjectsToRelease.Push(vbProject);
...
finally
{
    if(excel != null)
    {
        Log("Quiting Excel.");
        excel.Quit();
        excel = null;
    }
    while (comObjectsToRelease.Count > 0)
    {
        Log("Releasing {0} COM object.", comObjectsToRelease.GetType().Name);
        Marshal.FinalReleaseComObject(comObjectsToRelease.Pop());
    }               
    Log("Invoking garbage collection.");
    GC.Collect();
}

If Excel is still there you have to kill it manually.

Artem Tikhomirov
A: 

What I gather from all folks [ skirmishing and non-skirmishing :) ] here is that Excel is a pain when it comes to server-side automation.

Although I had looked into what @Biri and @John has recommended, it has not worked for me. Nonetheless thanks for the pointer.

Thanks to all you guys.

Codeslayer
This is a comment, not an answer, either post a comment or edit your question.
Geoffrey Chetwood
@Geoffrey Chetwood ~ He's the question asker. Granted, not much of an answer, but maybe @Codeslayer intended to mark this as the answer and he forgot?
drachenstern
A: 

I had a similar problem and used the following code:

System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcesses();
for (int i = 0; i < procs.Length; i++)
{
  if(procs[i].ProcessName == "EXCEL")
  {
    procs[i].Kill();
  }
}

This worked pretty well, but I would really think about working with Office on a server.

Fabian
+1  A: 

I also would not recommend using office apps on a server except for data access to mdb files.

I can definitely understand that there are times where it is necessary. In thoses cases I would recommend the following:

  • Create a separate server where that is the only function. (Let's you reboot with minimum impact).
  • Have the server implement a mechanism of queuing requests
  • Keep a single thread processing the queue. This gives you the ability to keep track of the office app, kill it if necessary, and continue on without impacting any queued up jobs or other applications.

If you absolutely need to do it on the same server, then at least implement the above in it's own app pool.

Limiting yourself keeping a queue of work and only one instance of Excel (or any other office app) let's you kill it with abandon with TaskKill or .Kill() and not lose work.

I believe if you keep it to a single thread then you would rarely have a need to kill it.

bruceatk
+4  A: 

I agree with not running Office on a server. Not that I have any choice in the matter :)

One thing to keep in mind with the taskkill option, is that unless you specifically plan for it (aka - singleton), you may have multiple copies of Excel (or any other Office app) running, and unintentionally close the wrong instance.

Also note that per http://support.microsoft.com/kb/257757

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

As an alternative, there is a product called Aspose Cells that offers a product that is designed to allow you to programmatically work with an Excel sheet in a server environment. As a disclaimer, I have never personally used this product, but I have heard about it from several people I worked with in the past.

Jason Z
A: 

I actually had a question that was similar to this awhile back - Check for hung Office process when using Office Automation - some of the responses to that question might be useful for you.

Also, I have to agree with what everyone else is saying in regards to keeping any Office products off of a server; however, since you are doing Excel, it might be feasible for you to generate Excel XML documents. You can do this without having to do any Office automation and the process is fairly straightforward. For simple grid based spreadsheets I have found it to be a bit easier than trying to automate it using Excel. The Office Open XML is quite powerful and allows for more complex reports are possible as well some more effort.

Rob
+2  A: 

I've had a similar problem. While 'taskkill excel.exe' or enumerating all "excel" processes and killing them does work, this kills ALL running Excel processes. You're better off killing only the instance you're currently working with.

Here's the code I used to accomplish that. It uses a PInvoke (see here) to get the ProcessID from the Excel.Application instance (Me.ExcelInstance in the example below).

Dim ExcelPID As Integer
GetWindowThreadProcessId(New IntPtr(Me.ExcelInstance.Hwnd), ExcelPID)

If ExcelPID > 0 Then
   Dim ExcelProc As Process = Process.GetProcessById(ExcelPID)
   If ExcelProc IsNot Nothing Then ExcelProc.Kill()
End If

Please not this might not work on all platforms because of the PInvoke... To date, this is the only method I have found to be reliable. I have also tried to find the correct PID by enumarating all Excel processes and comparing the Process.MainModule.BaseAddress to the Excel.Application.Hinstance.

'DO NOT USE THIS METHOD, for demonstration only    
For Each p as Process in ExcelProcesses
        Dim BaseAddr As Integer = p.MainModule.BaseAddress.ToInt32()
        If BaseAddr = Me.ExcelInstance.Hinstance Then
            p.Kill()
            Exit For
        End If
Next

This is not a reliable way to find the correct process, as the BaseAddress sometimes seems to be the same for several processes (resulting in killing the wrong PID).

Vincent Van Den Berghe
+1  A: 

I have used spreadsheetgear to generate XL reports on the server and it works really well. We don't have to worry about the EXCEL process..

MOZILLA
A: 

I found a wonderful solution on this link: http://www.antionline.com/showthread.php?t=277640

It really worked for me.

stefano m
+3  A: 

I've had more time to think about this answer, and would now recommend using an XML approach with the Open XML Office spreadsheet format.

Heres some good links to get started on building a office document with code. http://msdn.microsoft.com/en-us/magazine/cc163478.aspx http://msdn.microsoft.com/en-us/library/bb735940(office.12).aspx

Just use SSIS on SQL Server. It provides the ability to export to Excel. Don't run office on the server. Alteranatively waste money on aspose or spreadsheetgear.

GC does work your just not using it properly follow this pattern...

   private void killExcel()
        {
          xlApp.Quit();
          Marshal.ReleaseCOMObject(xlApp);
          if(xlApp != null)
          {
            xlApp = null;
          }
          GC.WaitForPendingFinalizers();
          GC.Collect();
          GC.WaitForPendingFinalizers();
          GC.Collect();
        }

get your Excel operational class to implement IDisposable, and then stick killExcel() in the Dispose method.

UPDATE: Also note that sometimes dev will still see Excel.exe running in task manager. Before assuming the above code isn't working, check that the process that is running the code is also closed. In the case of a VSTO or COM addin, check that Word/powerpoint/other excel instance is also closed as there is still a GC root back to the launching process. Once that is closed the Excel.exe process will close.

Anonymous Type
SSIS is an absolute nightmare when it comes to anything more than **basic simple table-like** Excel reporting. I wouldn't recommend SSIS for Excel reports, especially if your Excel report uses advanced features like graphs, multiple tabular layouts, multiple sheets, or pivot tabs.
code4life
yeah I would agree with that. For advanced scenarios requiring graphing or pivots there are better solutions. However for exporting and dumping data tables I disagree with the assertion that its an absolute nightmare. Still horses for courses, plenty of reporting solutions available.
Anonymous Type
A: 

I have just answered this question here:

Killing excel process by its main window hWnd

nightcoder
A: 

Thanks ! I'll contribute later.

Nigel
A: 

The best approach is to use a purpose built library such as the one from Aspose to generate the spreadsheets or populate templates. The next best approach is to use the xml formats for office if practical for your needs. A lightweight approach that is sometimes suitable is to create an HTML file with one table in it and name it with an .xls extension. Excel will happily read that, but it is very limited in what it can do.

Those are the options I've used (but not much). There's also a thing called Microsoft Office Sharepoint Server, but I've no idea how much it really lets you do.

That said, your problem is happening because when you invoke the regular Excel libraries, you're actually spinning up Excel completely independently of .Net and actually just working with a proxy library to talk to it. This is pretty much the same kind of thing you'd have with WCF and a service. You wouldn't expect the service to die just because the client application was done using it. Worse, Excel is an unmanaged resource and will not be disposed/ finalized/ garbage collected at all. The .Net Runtime doesn't know about Excel, it just knows about those proxies. Application.quit is what you need and also you may need to explicitly release the com objects that are created.

Jim Leonardo