Web services with Open and Microsoft Office

While much of the talk around SOA revolves around the reuse of services bound to enterprise applications, the same ubiquity of these services in the "cloud" gives end users the potential of reusing data from Office productivity suites. In this tip, we will discuss how you can put all those services available in the "cloud" to good using two such suites: Microsoft Office and Open Office, the latter being a royalty free license suite compatible with the former platform.

Given that most services in the "cloud" are data intensive, our focus will be on the spreadsheet application's offered by both suites: Excel for Microsoft Office and Calc for Open Office. Before we discuss the actual process of accessing services though, you should be aware that both suites also support the process of Web scraping, that, while apparently similar to accessing services, is a different process all together.

Web scraping simply consists of obtaining raw information off a Web page and laying it out on a document for further manipulation, a process that is both inefficient and cumbersome when compared to that of using services. In the case of Web scraping, both suites rely on end users to provide additional formatting instructions in order to reliably extract data, generally from HTML pages. Add to this that scraping tends to be on addresses that don't or can't receive input values, and the approach to using services inside Office productivity suites becomes a lot more compelling.

If you're interested in further exploring the process of scraping, in Open Office you can do so through the Insert->Link to External Data option, introducing a Web address and following the presented Wizard's instructions. In Microsoft Excel, you can make use of the option New Web Query, located under the Data->Get External Data menu option.

As far as accessing services is concerned, the first thing you need to realize is that both suites rely on the presence of Macros, a term used to describe a small program that performs a more elaborate operation than those included natively. Both Office suites support Macros written in a wide variety of programming languages, not to mention Macros can be pre-packaged and simply accessed by anyone without any programming background.

For Open Office we will rely on the older stalwart programming language: Basic. Assuming you have a Calc spreadsheet containing business addresses, and you wanted to obtain the nearest hospitals to each address for emergency purposes, listing 1.1 contains what an Open Office Macro written in Basic would look like for accessing a Yahoo Local Web service providing such information.

source:searchsoa.techtarget.com