Solved

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

Posted on 2013-11-25
8
3,180 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 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