Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3628
  • Last Modified:

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

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
jxbma
Asked:
jxbma
  • 5
  • 2
1 Solution
 
AndyAinscowCommented:
>>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
 
jxbmaSoftware ConsultantAuthor Commented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
jxbmaSoftware ConsultantAuthor Commented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
jxbmaSoftware ConsultantAuthor Commented:
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
 
jxbmaSoftware ConsultantAuthor Commented:
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
 
jxbmaSoftware ConsultantAuthor Commented:
This is the solution.
The feedback I was getting was no where near close to solving the issue.

JB
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now