Tech Ads
Back to Article List
Originally published June 2008 [ Publisher Link ]
Open Office and Microsoft Office Web services
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.
| Listing 1.1 Basic Macro for Open Office 2.x using Yahoo Local Web service |
REM ***** BASIC ***** Sub Main End Sub Private hospitalName As String Private hospitalPhone As String Private hospitalNameMatch As Boolean Private hospitalPhoneMatch As Boolean Function get_closest_hospital(ByVal zipcode As String) As String oSimpleFileAccess = createUnoService '================================================== ' Xml Sax functions '================================================== Private goLocator As Object Private glLocatorSet As Boolean ' This creates an object which implements the interface ' com.sun.star.xml.sax.XDocumentHandler. ' The doucment handler is returned as the function result. Function CreateDocumentHandler() oDocHandler = CreateUnoListener Sub DocHandler_startDocument() End Sub Sub DocHandler_endDocument() End Sub Sub DocHandler_startElement Sub DocHandler_characters( cChars As String ) If hospitalNameMatch = True Then hospitalName = cChars hospitalNameMatch = False EndIf If hospitalPhoneMatch = True Then hospitalPhone = cChars hospitalPhoneMatch = False EndIf End Sub Sub DocHandler_ignorableWhitespace( cWhitespace As String ) End Sub Sub DocHandler_processingInstruction( cTarget As String, cData As String ) End Sub Sub DocHandler_setDocumentLocator |
What this Macro does is take one input parameter -- a ZIP code -- passing it onto a Yahoo Local service request, which then obtains the closest registered hospital to the provided ZIP code. The values returned by the Macro are then provided as text, hence they will be placed as such inside whatever cell makes a call to the Macro. The underlying foundations behind this Macro are based on Universal Network Objects (UNO), which is the component model used in Open Office to support everything from accessing files to parsing XML data. If you're familiar with the COM model used in Microsoft Office and other Microsoft products, UNO is the equivalent for Open Office.
In order to deploy such a Macro in Open Office, you will need to go to the Tools->Macros->Organize Macros>Open Office.org Basic menu, upon selecting this last menu you will observe a pop-up window, inside this window click on the My Macros icon followed by the New button on the right hand side, this process will open the Macro definition window. At this point, if you copy listing 1.1 to this last window and save your changes, the Macro will become available as a spreadsheet function.
Assuming your Calc spreadsheet contains zip codes in column A, a definition like =get_closest_hospital(A[No]) in column B's cells would automatically populate the column with values extracted from Yahoo's Local service based on the zip code values in column A. Note that the function name declared inside the cell is identical to the Macro function definition, Open Office automatically makes Macro functions available for such purposes.
As it turns out, in the case of Microsoft Excel performing similar service calls to the one just described in Open Office, these can be achieved using the same Web query mechanism outlined earlier as a Web scraping. The reason for this is that Web queries in Microsoft Excel can be created to take input variables from spreadsheet cells, as well as filter returned XML payloads in a more straightforward way. In essence, there is no need to create a Macro unless you pretend to make a more elaborate service call, since the platform already has built-in support for such functionality.
For more sophisticated requirements in which an Office suite might require access to a SOAP/WSDL based service -- the earlier process dealt with a RESTful type service -- both Microsoft Office and Open Office also have support for such scenarios, with Microsoft Office making use of the Office Web Services Toolkit and Open Office relying on the use of the UNO Web service proxy.
Services composing an SOA don't necessarily have to be reserved for usage inside overtly elaborate development projects created for the enterprise. End users are fully capable of leveraging the same information and raising their productivity level by creating applications directly within their favorite Office suite, whether it's in a dominant commercial suite like Microsoft Office or its royalty-free counterpart Open Office.