Excel Services Known Issues and Tips
Applies to: SharePoint Server 2010
In this article
Excel Web Service
Understanding Security
Visual Studio
Excel Services User-Defined Functions
General
The following are a known issues and tips for working with Excel Services.
Excel Web Service
Viewing the WSDL Location
You can view the Excel Web Services Web Services Description Language (WSDL) page by navigating to the following URL on the server: http://<server>/<customsite>/_vti_bin/excelservice.asmx?WSDL
If you do not have a custom site, you can view the WSDL by using the following URL:
http://<server>/_vti_bin/excelservice.asmx?WSDL
For more information, see Accessing the SOAP API.
Understanding Excel Web Services and Namespaces
The following are Excel web services and namespaces:
The single web service object that contains all the API methods: ExcelService
The schema namespace: https://schemas.microsoft.com/office/excel/server/webservices
The web service page name: ExcelService.asmx
Linking Locally or to a Web Service
In certain scenarios, you should link directly to Microsoft.Office.Excel.Server.WebServices.dll and access it as you would any local assembly, instead of calling it as a web service through SOAP over HTTP.
For more information and guidelines on when to use direct linking, see Loop-Back SOAP Calls and Direct Linking.
Understanding Invalid Characters
The calls to the GetCell and GetRange methods will fail if the workbook cells contain characters that are invalid in an XML response.
For example, if a cell contains characters with hexadecimal values 0x1, 0x2 ... 0x8, the ASP.NET parser will throw an exception that the value of the character being written to the XML response is invalid:
System.InvalidOperationException: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title>' ', hexadecimal value 0x01, is an invalid character.</title>
This behavior is expected. The XML specification that defines which characters are allowed in a valid XML response specifies that hexadecimal values 0x1, 0x2 ... 0x8 are invalid XML characters:
Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF] /* any Unicode character, excluding the surrogate blocks, FFFE, and FFFF. */
For more information, see W3C Extensible Markup Language (XML) Specification (http://www.w3.org/TR/REC-xml\#NT-Char).
Saving a Workbook
When you make changes to a workbook—for example, by setting values to a range using Excel Web Services—the changes to the workbook are preserved only for that particular session. The changes are not saved or persisted back to the original workbook. When the current workbook session ends (for example, when you call the CloseWorkbook method, or the session times out), changes you made will be lost.
If you want to save changes to a workbook, you can use the GetWorkbook method and then save the workbook using the API of the destination file store. For more information, see How to: Get an Entire Workbook or a Snapshot and How to: Save a Workbook.
Understanding the Url Property of an Excel Web Services Proxy Class
Do not use the Url property of an Excel Web Services proxy for the location of the workbook you want to open. The Url property of a web service proxy class generated by Visual Studio gets or sets the base URL of the XML web service the client is requesting. In the case of Excel Web Services, this is usually:
http://<server name>/_vti_bin/ExcelService.asmx
To specify the location of a workbook, use the OpenWorkbook method instead of the Url property as shown in the following code example.
//Instantiate the web service and make a status array object.
ExcelService xlservice = new ExcelService();
string sheetName = "Sheet1";
//Set the path to the workbook to open.
//TODO: Change the path to the workbook
//to point to a workbook you have access to.
//The workbook must be in a trusted location.
string targetWorkbookPath =
"http://myserver02/example/Shared%20Documents/Book1.xlsx";
//Set credentials for requests.
xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials;
//Call the open workbook, and point to the trusted
//location of the workbook to open.
string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US",
"en-US", out outStatus);
For more information, see WebClientProtocol.Url Property (https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebServicesProtocolsWebClientProtocolClassUrlTopic.asp).
Understanding Security
Using Workbook Permissions
Beware of the following issues regarding workbook permissions:
Excel Web Services uses the Microsoft SharePoint Foundation authorization scheme to verify that the caller has the right to call APIs (that is, make web service calls) on the SharePoint Foundation site (that is, the Web site where Excel Web Services is located) remotely. If the caller does not have the "Use Remote API" right, the Excel Web Services returns an "HTTP 401 (Unauthorized)" error, and logs an "API authorization failed" event. Excel Web Services performs these authorization checks only for calls that originate as SOAP calls. Calls from applications that link locally to Microsoft.Office.Excel.Server.WebServices.dll are not considered remote calls. Therefore, they are not subject to authorization checks. However, if the application that links locally to Microsoft.Office.Excel.Server.WebServices.dll is itself a SOAP service, and handles the service's SOAP calls, the call to Excel Web Services will seem like a SOAP call (even though the application links directly to Microsoft.Office.Excel.Server.WebServices.dll). In this scenario, Excel Web Services will perform the authorization checks.
To get the entire workbook (for example, by calling the GetWorkbook method using the WorkbookType.FullWorkbook argument), the caller needs "open" permission for the workbook or "read" permission in a file share.
To call the GetApiVersion method, no permission is necessary.
For the rest of the Excel Web Services methods, besides credentials, the caller needs "view" permission (in SharePoint Foundation) or "read" permission (in a file share) for the workbook.
Trusted Location
The workbooks you want to open in Excel Services must be placed in a trusted location. If not, the Excel Web Services calls to open the workbook will fail.
For information about how to trust a location, see How to: Trust a Location and How to: Trust Workbook Locations Using Script.
Visual Studio
Microsoft Visual Studio Proxy Behavior
When Microsoft Visual Studio creates a proxy class for a client project that calls Excel Web Services, it has the following behavior:
If a method has no return value, and one or more out arguments, the first out argument is moved to become the return value. That is, the method in the proxy class will have one less out argument in the method signature. But the signature will have a return value with the type and content of what used to be the first out argument.
The affected Excel Web Services methods are:
Calculate
CalculateA1
CalculateWorkbook
CancelRequest
CloseWorkbook
GetSessionInformation
Refresh
SetCell
SetCellA1
SetRange
SetRangeA1
Excel Services User-Defined Functions
Global Assembly Cache is Checked First, Then the Local Folder
By design in the Microsoft .NET Framework, an assembly in a global assembly cache will be loaded instead of the same assembly in a local folder. The common language runtime will look for an assembly in the global assembly cache first before searching in the local folders.
Therefore, if an assembly is installed in the global assembly cache and is in the UDF list but disabled (or removed from the UDF list altogether), and an identical assembly is installed in a local folder and enabled, the assembly in the global assembly cache will still get loaded and used instead of the same assembly in the local folder.
This does not affect upgrade scenarios in which the assembly version has been modified, which means the assembly is not the same anymore.
General
Order of Strings in Sharedstring.xml is Not Maintained
Excel Services does not maintain the original order of strings in a workbook shared-string table (the Sharedstrings.xml part within the Microsoft Office Excel XML Format file). For example, execute the following steps:
Open a file using Excel.
Save the file in .xlsx file format.
Upload the file to a document library that is a trusted location.
Open the file in the document library by using Excel Web Access.
Click Open in Excel.
Save the file in .xlsx file format.
If you compare the Sharedstrings.xml file created in Step 2 with the one created in Step 6, you will find the order of the Sharedstrings.xml parts might be different.
You should not write an application that assumes the order of strings in the shared-string table is fixed. For example, you cannot replace the shared-string table with an existing localized translation table. You must adjust to the new ordering of strings in the shared-string table.