Solved

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

Posted on 2013-11-25
8
3,103 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

While working on Silverlight and WCF application, I faced one issue where fault exception occurred at WCF operation contract is not getting propagated to Silverlight client. So after searching net I came to know that it was behavior by default for s…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now