Solved

How to resolve Excel.Application.Workbooks.Open failing when called from WCF Service?

Posted on 2013-11-25
8
3,250 Views
Last Modified: 2013-11-30
Hi:

I hope things are going well as you're getting ready for the Holiday Season.

I was wondering if you had a couple of minutes to read over a WCF issue I've been  working on trying to resolve.

I have a WCF service which utilizes XL Spreadsheets on the backend as part of it's calculation engine. I'm using a 3rd party  .Net based component called Aspose to access, manipulate and retrieve data from the spreadsheet.

Unfortunately, Aspose ran into a couple of calculation bugs. In in order to overcome these issues,  as a temporary fix, I save a temporary copy of the current spreadsheet. I then open the spreadsheet (which forces a recalc) with the Microsoft Excel .Net library - Microsoft.Office.Interop.Excel. Then I reopen that temp spreadsheet using Aspose to collect  the result values

This fix has worked fine as I've been implementing/debugging the solution locally.

When I deployed the service to IS 7.0, the MS .Net Excel library fails when attempting to open the file.

I think this some kind of permissions issue with the deployed IIS service.

This is a snippet of the code that is failing
String fullPath1 = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data", "BCBSDiabetesCalculator.xlsm");

// Open to force Excel to perform calculation itself
Microsoft.Office.Interop.Excel.Application excelApp1 = new Microsoft.Office.Interop.Excel.Application();

var excelWorkbook1 = excelApp1.Workbooks.Open(fullPath1);

Open in new window


The following code block where I opening a filestream and passing it to the Aspose library call works.
//Get the Excel file into stream
FileStream stream = new FileStream(fileName, FileMode.Open);

//Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);

//Create a Workbook object and opening the file from the stream
_workbook = new Workbook(stream, loadOptions);

if (_workbook == null)
{
}

// Make sure that we close the stream.
stream.Close();

Open in new window


Do you have any insight/ideas on how to resolve this?


Thanks,
JohnB
0
Comment
Question by:jxbma
  • 5
  • 2
8 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39675099
>>When I deployed the service to IS 7.0, the MS .Net Excel library fails when attempting to open the file.

Is MS Excel actually installed on that machine?  If it isn't then it isn't going to work.  (Your third party app might not use excel but manipulate the file directly).
0
 
LVL 1

Author Comment

by:jxbma
ID: 39675187
Hi Andy:

Yes, Excel is installed on the machine.
I can use the Excel interop library to open a spreadsheet.
I tested this in my client application.

JB
0
 
LVL 40
ID: 39675773
You might have problems if the version of Excel on the server is not the same as the one that was referenced when you created the application.

The solution in such cases is to make sure to match the versions, or to work with late binding, using Object variables instead of specific Excel types variables.

If the application is to be run only on the server, then I would advise matching the versions of Excel, because working early binding (specific types) is a lot less error prone than working with Object variables.

If the application is to be installed on different stations that might have different versions of Excel, then late binding is usually the best solution.
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 1

Author Comment

by:jxbma
ID: 39675988
This is not a versioning issue.
Both the WCF service and the test application are both on my local dev machine.

The issue isn't instantiating the interop dll. It is when I attempt to open the spreadsheet file.
0
 
LVL 40
ID: 39676127
You do not specify a path for your .xlsx file, so it might not be seen from the deployed service. What is the exact error message you are receiving?
0
 
LVL 1

Author Comment

by:jxbma
ID: 39676644
Yes, I am specifying a path. The calls to the Excel and Aspose libraries are in succession using the same path. Aspose call works; Excel call does not.

Please look at the code snippets I provided with the original posting:
String fullPath1 = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data", "BCBSDiabetesCalculatorSaved.xlsm");

Open in new window


Excel, Aspose DLL,  WCF service and test application are being all run on my local dev environment.

When I allowed MS VS to host the service while I was debugging, the call worked fine.

When I switched to running on IIS 7 on the same Windows 7 dev box, I started getting the error.

This is the error that I am getting:
Microsoft Excel cannot access the file 'C:\inetpub\wwwroot\BCBSCalculatorService\App_Data\BCBSDiabetesCalculator.xlsm'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

The error occurs when I attempt to open the spreadsheet file using Excel interop call.

I created a separate application pool for the service to run with.
It is using the .Net 4.0 Framework.

For the application pool settings, I have
Process Model --> Identity --> LocalSystem

This allows me & the Aspose Dll to open local files where the WCF Service is hosted.


JB
0
 
LVL 1

Accepted Solution

by:
jxbma earned 0 total points
ID: 39676687
This solution is ...

·Windows 2008 Server x64

Please make this folder.

C:\Windows\SysWOW64\config\systemprofile\Desktop

·Windows 2008 Server x86

Please make this folder.

C:\Windows\System32\config\systemprofile\Desktop

...instead of dcomcnfg.exe.

This operation took away office automation problems in my system.

A Desktop folder seems to be necessary in the systemprofile folder to open file by Excel.

It disappears from Windows2008, Windows2003 had the folder,
and I think it cause this error.

I've tested and works fine...I mean tested from within a Windows service...
Here's the link...
http://social.msdn.microsoft.com/For...6-44421818ef91 

The reference for the fix is here (3/4 of the way down the page)

http://bytes.com/topic/c-sharp/answers/819740-c-service-excel-application-workbooks-open-fails-when-called-service
0
 
LVL 1

Author Closing Comment

by:jxbma
ID: 39686703
This is the solution.
The feedback I was getting was no where near close to solving the issue.

JB
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question