Search code examples
excelodataolingo

Olingo and Excel

I've downloaded the Olingo server sample (Odata4), compiled and deployed on my local tomcat

browsing the url http://localhost:8080/odata-server-sample/cars.svc/ i get

<?xml version="1.0" encoding="UTF-8"?>
<app:service xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:metadata="http://docs.oasis-open.org/odata/ns/metadata" metadata:context="$metadata">
   <app:workspace>
      <atom:title>olingo.odata.sample.Container</atom:title>
      <app:collection href="Cars" metadata:name="Cars">
         <atom:title>Cars</atom:title>
      </app:collection>
      <app:collection href="Manufacturers" metadata:name="Manufacturers">
         <atom:title>Manufacturers</atom:title>
      </app:collection>
   </app:workspace>
</app:service>

I don't know the OData protocol, but it seem legit...

Now if i i feed this to Excel 2016, using PowerQuery: Data > New Query > From Other Source > From OData Feed

i get the error Invalid URI: The hostname could not be parsed.

If i use instead this vba code:

Option Explicit

' References that need to be added:
' Microsoft XML, v6.0
' Microsoft Scripting Runtime

Const ODataErrorFirst As Long = 100
Const ODataCannotReadUrlError As Long = ODataErrorFirst + 1
Const ODataParseError As Long = ODataErrorFirst + 2

Sub test()
    ODataReadUrl ("http://localhost:8080/odata-server-sample/cars.svc/")
End Sub

' Given a URL, reads an OData feed or entry into an XML document.
Function ODataReadUrl(ByVal strUrl As String) As MSXML2.DOMDocument60
    Dim objXmlHttp As MSXML2.XMLHTTP60
    Dim objResult As MSXML2.DOMDocument60
    Dim strText As String

    ' Make a request for the URL.
    Set objXmlHttp = New MSXML2.XMLHTTP60
    objXmlHttp.Open "GET", strUrl, False
    objXmlHttp.send

    If objXmlHttp.Status <> 200 Then
        Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' – status code: " & objXmlHttp.Status
    End If

    ' Get the result as text.
    strText = objXmlHttp.responseText
    Set objXmlHttp = Nothing

    ' Create a document from the text.
    Set objResult = New MSXML2.DOMDocument60
    objResult.LoadXML strText
    If objResult.parseError.ErrorCode <> 0 Then
        Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' – " & objResult.parseError.reason
    End If

    Set ODataReadUrl = objResult
End Function

i get Not valid at root level. (in my Italian Excel: Non valido al primo livello del documento.)

What does this mean? Olingo isn't compatible with excel?

Solution

  • I've followed some suggestions and come up with a working sample

    you can find the patched code here: https://github.com/lelmarir/olingo-odata4/commit/8a947d3b7afab88c04177bfb361e9689ea91defc

    References: