ASP.NET Web API OData does not work with Excel


Excel expects an XML service doc, but it doesn't send any Accept header. By default ASP.NET Web API OData sends back a JSON service doc when no Accept header is specified. As a result you cannot consume a default ASP.NET Web API OData service using Excel.

We should switch our default format for service documents to be XML.

file attachments

Closed Apr 26, 2013 at 8:11 AM by hongyes


AlexDJames wrote Jan 29, 2013 at 10:15 PM

+1 (but just for Service Docs).

HongmeiG wrote Jan 30, 2013 at 6:46 PM

The fix is to not having ODataMetadataController deriving from ODataController, rather has its own attribute ODataMetadataFormattingAttribute, and its implementation similar to what we do today with ODataFormatting Attribute except it reverse the json formatters and xml formatters.

TommyPaa wrote Feb 21, 2013 at 8:39 AM

Hi, is this solution really working? I'm installed the latest Microsoft.AspNet.WebApi.OData nuget wiht the following nuget command:
Install-Package Microsoft.AspNet.WebApi.OData -IncludePrerelease

But we still can't get our Odata to work in excel. And if i run it in notepad or a webbrowser the response is JSON and not XML. Is there any way to see if i'm using the correct version of ODataMediaTypeFormatters.cs?



danroth27 wrote Feb 21, 2013 at 9:08 PM

Please post the details of the issue you are seeing to the discussion forums and include the version of the assembly that you are running against.

ianw wrote Mar 4, 2013 at 3:39 PM

Sorry I can't provide input on the coding, but this fix should be confirmed against both Excel 2013, and 2010+PowerPivot, as it seems to have a different impact on each.

When we expose OData from our Azure site we can consume it in Excel 2013, but with Excel 2010 and PowerPivot we get the error message: "Data at the root level is invalid. Line 1, Position 1".

danroth27 wrote Mar 5, 2013 at 7:25 PM

The fix only addressed Excel 2013. We are working on publishing a workaround for earlier versions of Excel.

danroth27 wrote Mar 5, 2013 at 7:30 PM

One workaround is to use a custom message handler that detects if an Accept header was not specified and then adds a default Accept header for atom+xml.

danroth27 wrote Mar 5, 2013 at 10:14 PM

Alternate workaround:
        IList<ODataMediaTypeFormatter> odataFormatters = ODataMediaTypeFormatters.Create();
        var jsonFormatter = odataFormatters.First(f => f.SupportedMediaTypes.Contains(MediaTypeHeaderValue.Parse("application/json")));
        config.Formatters.InsertRange(0, odataFormatters);

JasonBSteele wrote Apr 29, 2013 at 6:57 PM

This message handler worked for me. It specifically deals with PowerPivot but could be made more generic for your needs:
public class MissingAcceptHeaderHandler : DelegatingHandler
        protected async override Task<HttpResponseMessage> SendAsync(
            HttpRequestMessage request,
            CancellationToken cancellationToken)
            if (request.Headers.Accept.Count == 0)
                if (request.Headers.UserAgent.Any(ua => ua.Product != null && ua.Product.Name == "PowerPivot"))
                    request.Headers.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/atom+xml"));

            var response = await base.SendAsync(request, cancellationToken);

            return response;

AndiRudi wrote Jul 29, 2014 at 5:30 PM