Freigeben über

Excel 2007 - How to connect to and import data from an XML Web Service

  • Artikel

Many organizations have adopted a Service-Oriented Architecture (SOA) as the architecture of choice. SOA allows a company to expose its services and data in a manner that is compliant with industry standards. SOA is the buzzword these days. Down the wire of SOA is XML data that is exchanged between entities.

Microsoft Office Excel 2007 has built-in features that allow a user to connect to and consume XML data. Such data can be stored in an XML file on the local hard drive, shared in a portal or returned from an XML Web Service.

If you’d like to use Excel to import XML data from a Web Service, follow these step-by-step guidelines. For the sake of simplicity, this document will be using a web service that is available on the Internet called Teach a Techie (https://teachatechie.com). This web service has a function named GetLocation and the URL we’ll be using is https://teachatechie.com/GJTTWebServices/ZipCode.asmx/GetLocation?ZipCode=11413

  1. Start Excel 2007.

  2. Select the Data Tab:

  3. Click on the Get External Data button, then select From Other Sources, then select From XML Data Import:

  4. Enter the URL above into the File Name box, then click on the Open button:

  5. Wait for Excel to open the XML schema and the data. In our example, the web service does not have an associated XML Schema. Excel will prompt you to create one for you. Click on OK to accept this action.

  6. Excel will prompt you for the cell where you want to import the data. Select the location and press on OK.

  7. Now the data is imported into Excel. You can utilize all the features of Excel to analyze and report on your data.

Happy analysis!

Comments

  • Anonymous
    February 09, 2008
    The example you have show is very interesting, and i tried to do the same example, but i have a problem, i made a web service and it does not work as fine as your web service, could you send me the code of your web service and if it were posible who did you do that. I show the answer of the query like a dataset but i think the problem is with the url. Thanks

  • Anonymous
    February 22, 2008
    Thanks for your comment, JC All you need to do in your Web Service in order to make this example work is to return a well-defined (i.e. Strongly Typed) DataSet object from your webmethod. The example web service is not my work, so sorry, but I cannot provide the source code. Please refer to the following article for ways to create a strongly typed dataset in your Web Service: http://msdn2.microsoft.com/en-us/library/1as0t7ff(VS.80).aspx

  • Anonymous
    June 17, 2008
    I know this is an older article, but have you done this dynamically.  I want to retrieve data from a web service based on data entered in another cell as a parameter.  Is this doable?

  • Anonymous
    September 01, 2010
    Are you able to advise me, I have a simple ASPX page that returns an XML document, content type "text/xml". If I point Excel at the URL for this ASPX page, it wants to import the result as text. If I open the same URL using, notepad, save the result and open that the Excel, it treats it as XML as per you blog post. Do you have any suggestions?

  • Anonymous
    September 01, 2010
    Hello, Andy Try to change the MIME Type to application/xml, or maybe application/excel. This could solve your issue. I haven't tried it yet. Is your ASPX available? I can try to call it and get it to work in Excel.