Sharepoint 2010 Custom WCF service returns 400 - "bad request" with OpenXML

Question

I'm developing a custom Sharepoint 2010 service to work with Excel files. I'm using VS2015 on my local workstation.

The service works and debugs just fine getting the SPFile, reading it's properties and converting it into a stream. However, as soon as I include the code to create the SpreadsheetDocument using SpreadsheetDocument.Open() it doesn't even debug anymore but simply retuns a response of 400 "Bad Request".

Service Code

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.SharePoint;
using System;
using System.IO;
using System.ServiceModel.Activation;

namespace Lifeco.Corp.Sharepoint
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class ExcelItemToSapService : IExcelItemToSapService
    {

        public ServiceResult SubmitSpreadsheet(string documentUrl)
        {
            // Ensure we have the neccessary information.
            if (string.IsNullOrEmpty(documentUrl))
            {
                return new ServiceResult() { Status = "error", Message = "List item url is required as the 'documentUrl' parameter." };
            }


            SPFile doc = SPContext.Current.Web.GetFile(documentUrl);

            if (doc == null || !doc.Exists)
            {
                return new ServiceResult() { Status = "error", Message = string.Format("Document item at '{0}' was not found.", documentUrl) };
            }

            using (Stream dataStream = doc.OpenBinaryStream())
            {

                // As is this works.  Uncommenting the following 'using' block and I receive 400 - Bad Request without even getting to step into the code and debug.
                //using (SpreadsheetDocument document = SpreadsheetDocument.Open(dataStream, false))
                //{
                //    // work with spreadsheet...
                //}
            }

            ServiceResult response = new ServiceResult() { Status = "success" };
            response.Message = string.Format("Title: {0} | Version: {1} | Modified By: {2}", doc.Title, doc.UIVersionLabel, doc.ModifiedBy.Name);

            return response;
        }
    }
}

.svc

@ ServiceHost 
    Language="C#" 
    Debug="true" 
    Service="Lifeco.Corp.Sharepoint.ExcelItemToSapService, $SharePoint.Project.AssemblyFullName$" 
    CodeBehind="ExcelItemToSapService.svc.cs" 
    Factory="Microsoft.SharePoint.Client.Services.MultipleBaseAddressWebServiceHostFactory, Microsoft.SharePoint.Client.ServerRuntime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

The error is received the same whether calling the service directly in the browser or with the following jquery on a Sharepoint page

$.ajax({
                type: "GET",
                url: webServerRelativeUrl + '/_vti_bin/ExcelItemToSapService/ExcelItemToSapService.svc/SubmitSpreadsheet',
                contentType: "application/json; charset=utf-8",
                data: { "documentUrl": "http://s040997/Corporate/Insurance Risk Sample 2.xlsm" },
                dataType: 'json',
                success: function (data) {
                    //alert('Success\n' + JSON.stringify(data));
                    $resultEl.html('<pre>' + JSON.stringify(data) + '</pre>');
                },
                error: function (jqXHR, status, error) {
                    $resultEl.html('');
                    alert('Error\n' + jqXHR.responseText + '\nstatus: ' + status);
                    //alert('Error\n' + jqXHR.responseText + '\nheader: ' + jqXHR.getResponseHeader() + '\nerror: ' + error);
                }
            });

Any thoughts?

Thanks

Answer 1

Figured out the issue.

I needed to add the DocumentFormat.OpenXml.dll as an Additional Assembly to my Sharepoint package.

1. Open /Package/Package.package from Solution Explorer
2. Advanced tab
3. Add -> Add Existing assembly
4. Entered the source path to the DocumentFormat.OpenXml.dll
5. Selected Deployment Target = GlobalAssemblyCache
6. OK

And the next test succeeded.