Serverside Excel programing through WebService

[Peter Karlström]

Hi

I'm working in a VB.NET project in VS 2008 where a WebService is to create a
customized Excel 2007 spreadsheet.
The WebService recieves a tab-separated CSV file and returns a complete and
formatted Excel 2007 xlsx workbook.
Everything works fine except for the Excel-process which I start.
It won't quit no matter what I do.

This is the code:
Public Function CreateReport(ByVal CSVFile As String) As String

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlDoc As Workbook

Try
xlApp = New Microsoft.Office.Interop.Excel.Application
File.Delete("C:\Temp\tempfile.xlsx")
Catch ex As Exception
CreateReport = ""
Exit Function
End Try

Try

xlApp.DisplayAlerts = False
xlDoc = xlApp.Workbooks.Open(CSVFile)
ImportCSV(xlApp, CSVFile)
xlDoc.SaveAs(Filename:="C:\Temp\tempfile.xlsx",
FileFormat:=XlFileFormat.xlOpenXMLWorkbook, CreateBackup:=False)
xlDoc.Close()
xlApp.Quit()
CreateReport = "c:\Temp\Tempfile.xlsx"
Try
File.Delete(CSVFile)
Catch ex As Exception

End Try
Catch ex As Exception
System.Diagnostics.Debug.Print(ex.Message)
CreateReport = ""
xlApp.Quit()
End Try

Try
xlApp.Quit()
xlApp = Nothing
Catch ex As Exception
System.Diagnostics.Debug.Print(ex.Message)
End Try

Return CreateReport
End Function


The ImportCSV sub is only formatting data, and even id I omit to call it,
the Excel process still runs when I'm done.

Anybody have a clue?

Thanks in advance

 

[Colbert Zhou [MSFT]]

Hello Peter,

This looks like a well known problem of Office automation for me. It is
documented in the following KB article,
http://support.microsoft.com/kb/317109

"When Visual Studio .NET calls a COM object from managed code, it
automatically creates a Runtime Callable Wrapper (RCW). The RCW marshals
calls between the .NET application and the COM object. The RCW keeps a
reference count on the COM object. Therefore, if all references have not
been released on the RCW, the COM object does not quit."

Could you please follow it and see if the resolution works for you!


Best regards,
Ji Zhou
Microsoft Online Community Support

 

[Peter Karlström]

Hi Colbert

You are right. This is a well known problem with the Office products.
The reason I got confused about this, is that the exact same scenario works
when using Word as a COM Object. No problem closing Word, even if I open
several documents during the process.

But I looked into the KB article and spent some time testing everything
mentioned there.
I noticed that System.Runtime.InteropServices.Marshal.ReleaseComObject
returns the Reference count of the COM object. When I first run it, it has a
value of 5.
Even with GC.Collect and GC.WaitForPendingFinalizers() the Excel process is
still running after <app>.Quit and <app> = Nothing.
I also tried to "loop down" all references.
With a Reference count of 0 and 1, I get an exception when running <app>.quit.
Exception is complaining about accessing a component which has no references.
With all other reference counts, Excel will not quit.

I have disconneted all other Addins in Excel, to make sure no other Addin is
creating the problem. Excel is "clean" from COM Addins when running.

What I can see the KB article is targeting Visual Studio 2003 and Office
2002/2003. I'm working in Visual Studio 2008 (VB) and Office 2007. Does this
matter?

Please let me know if you want the complete WebService project for testing.

 

[Colbert Zhou [MSFT]]

Hello Peter,

Most time we see this issue is in Excel application. And it persists even
we are using VS 2008 and Excel 2007. I understand your concerns here.
Actually I remember I did also try my efforts to debug such an issue when
automating Excel to do some tasks.

At the first glance on your posted codes, the problem may at the line of,
xlDoc = xlApp.Workbooks.Open(CSVFile)

We use Workbooks property of Excel.Application, but did not keep a
reference of it. So .NET's GC cannot release the underlying RCW. Since the
RCW is not released, Excel application thinks that there are clients still
using it as COM server. That is why Excel does not Quit.

To prevent this problem, the KB has suggested a way. That is always
declaring each object as new variable. So our codes should be changed to,
Dim xlBooks as Excel.Workbooks
xlBooks = xlApp.Workbooks
xlDoc = xlBooks.Open(CSVFile)

Actually, in my test, I can prevent the problem after changing codes like
these. So I just suggest the KB article to you.

Do you try this approach in your side? After changing codes like above, if
you still encounter the issue, I think there should be some other codes
causing the problem. I have some experiences on debugging this issue. So if
you can share the whole codes with me, I believe I can do more help here.

You can access me via (e-mail address removed)

By the way, if you are interesting in this topic. Here is a nice article
explains the reasons as well as some debugging steps,
http://blogs.msdn.com/geoffda/archive/2007/09/07/the-designer-process-that-w
ould-not-terminate-part-2.aspx


Best regards,
Ji Zhou
Microsoft Online Community Support

 

[Peter Karlström]

Hi

Just want to inform the group what Colbert Zhou found out regarding my
problem, which was that Excel didn't quit when running the compiled
WebService, only when running i Debug mode.
Colbert recieved my code for testing and came to the conclusion below:

<Colbert>
First, our codes do the right thing to call GC.Collect which should release
the Excel. So what is the problem here? Considering two scenarios,

1. When we run the WebService in Visual Studio debug mode, the WebService is
hosted in a process named WebDev.WebServer.Exe. This process is executed
under the local account. (Interactive user). The automated Excel is also
executed under the same local user.

2. When we run the WebService after deploy it. The WebService is hosted in
IIS process, w3wp.exe. This process by default is executed under the account
of "Network Service". But, the automated Excel is executed under the local
account.

In the first scenario, WebDev.WebServer.Exe calls the GC.Collect which
releases the underlying RCWs correctly, the Excel quits correctly. In the
second scenario, although w3wp.exe releases all the underlying RCWs
correctly, but it seems for different accounts reason, the Excel process does
not release the COM interface count. So it does not quit. Currently, I cannot
tell whether this is by design or a potential bug.

The workaround I found is configuring the IIS to let the w3wp.exe runs under
the same local account. After w3wp.exe is configured to run under interactive
user account, the Excel quits correctly in my test. So you can follow this
article to configure IIS default application pool,
http://www.bluevisionsoftware.com/WebSite/TipsAndTricksDetails.aspx?Name=AspNetAccount
</Colbert>

I strongly recommend the URL above, which explains what to do on the
serverside, regardless of ASP-host, Win2000, WinXP or WinServer.

Thanks again Colbert for some excellent debugging.

--
Regards
Peter Karlström
Midrange AB, Sweden