VFP Automates OpenOffice.org

For a variety of reasons, computer users (our clients) have started looking at alternative office software. Some are concerned about rising licensing costs and liabilities, others about security, trustworthiness, or commercial viability. The good news is that we, as FoxPro developers, can continue to offer these clients the power of Visual FoxPro, even if they choose other office suites. FoxPro supports interoperability with a wide variety of other languages, applications, and platforms. In this article, I'll look at VFP interoperability with OpenOffice.org.

A little background history provides some perspective on how things got to be the way they are. OpenOffice.org started out life as StarOffice, developed by Star Division, Inc., in Germany. StarOffice was bought by Sun Microsystems, Inc., which continues to enhance and sell it as a commercial package (StarOffice) while also offering a freely distributable version as OpenOffice.org. The two products are available under two different licenses: StarOffice under the Sun Industry Standards Source License, for commercial entities to re-license, embed, or extend, and OpenOffice.org under the Lesser GNU General Public License (LGPL). Sun Microsystems, Inc., retains the copyright to all code in the project. StarOffice has a very reasonable retail MSRP of $75.95, with quantity discounts as low as $25 per package for 10,000 users. Larger companies will appreciate the more complete StarOffice package (fonts, templates) and support from Sun, while smaller organizations will appreciate the free price and open community support of the millions of OpenOffice.org users. This type of dual-licensing scheme is becoming more common: MySQL AB has commercial and open-source licenses, as do Mozilla and Perl. For simplicity, in the remainder of this article, I'll refer to OpenOffice.org, but the same comments should apply to StarOffice as well.

OpenOffice.org has the usual products you'd expect in an office suite: a word processor ("Writer"), spreadsheet ("Calc"), presentation package ("Impress"), structured drawings ("Draw"), and an equations editor ("Math"). OpenOffice.org reads a number of document formats, including those of Microsoft Office, as well as most industry standards: text, HTML, RTF, and previous OpenOffice.org and StarOffice formats. As OpenOffice.org supports a different (StarBasic) macro language and object model, Microsoft Office document macros aren't translated to the new format, and must be converted manually. OpenOffice.org's native document format is a compressed XML format. Unlike other proprietary office document packages, this format is openly documented. Open any document with a ZIP viewer, such as WinZIP, and you'll see a set of XML documents. These documents can be disassembled and reassembled to create new documents.

So, the good news is that your basic documents and templates will be readable without major changes. However, if you're moving over an automated system, you'll need to recode the Automation portions, as the language and object model is significantly different. For this reason, you'll probably want to start out by automating a new system, rather than converting an existing one.

Macros

StarBasic is a variant of the BASIC language with built-in objects for the OpenOffice.org environment. Listing 1 shows a simple example of a StarBasic macro. Macros can be created from the Tools | Macro menu item. As of the current version (1.0.2), there isn't a macro recorder built into the product—that's high on the priority list for a future version (there is a macro editor, with an IDE). StarBasic supports declared and undeclared variables (arrays must be DIMmed), one- and two-dimensional arrays, underscore for line continuation, and ampersands or plus signs for string concatenation. If you've worked with any variant of BASIC before, you should be able to pick this language up pretty quickly.

Listing 1. StarBasic macro to convert temperatures.

Sub FahrenheitToCelsius
'Accept Fahrenheit temperature, return Celsius
dim lcInput as String
dim lnFahrenheit as double
dim lnOutput as double
lcInput = inputbox("Enter a temperature " +_
                   "in Fahrenheit",_
                   "Fahrenheit to Celsius "+_
                   "Conversion","32")
lnFahrenheit = cDbl(lcInput)
lnCelsius = (lnFahrenheit -32) * 5/9
MsgBox("That is " + Cstr(lnCelsius) + _
       " degrees Celsius", 0 + 64 + 0, _
       "Temperature Conversion Result")
End Sub

Automation

OpenOffice.org has a different object model than other applications you may be familiar with. The OpenOffice.org development team examined the various industry standard formats (Microsoft's COM, Sun's RMI, CORBA), and found them all lacking in one aspect or another. Since they were interested from the beginning in creating a cross-platform application, they chose to create a superset of all of the office formats. The result is Universal Network Objects, their own object management system. The next step was to build "bridges" from each of the languages for invocation of the UNO objects from other systems: C++, Java, and COM-based languages. The UDK (development kit), currently in alpha version, has examples of creating OpenOffice.org scripts in VBScript and Java. A sample is shown as Listing 2. A more extensive demo from the OpenOffice.org UDK is included in the Download file as WriterDemo.vbs.

Listing 2. VBScript for manipulating OpenOffice.org Writer.

'The service manager is always the starting point
'If there is no office running then an office is started
Set objServiceManager= Wscript._
   CreateObject("com.sun.star.ServiceManager")
'Create the DesktopSet 
objDesktop= objServiceManager._
  createInstance("com.sun.star.frame.Desktop")
'Open a new empty writer document
Dim args()
Set objDocument=objDesktop._
 loadComponentFromURL("private:factory/swriter",_
 "_blank", 0, args)
'Create a text object
Set objText= objDocument.getText
'Create a cursor object
Set objCursor= objText.createTextCursor
'Inserting some Text
objText.insertString objCursor,_
 "The first line in the created text document."&_
       vbLf, false

Visual FoxPro developers should be pretty familiar with converting code from other languages, and this one presents only one tough problem. While most of the conversion is straightforward, the line "Dim args()" is the killer. This creates an array in VBScript, one with no elements. VFP doesn't have the concept of a completely empty (row count of zero) array. If you try to pass the args array to the next function, you'll get OLE error 0x80020005: Type mismatch. A little digging in the documentation will reveal the args array should be passing a series of PropertyValue objects to the function—settings that specify how the document should be created. Since we can't pass an empty array, we need to create a PropertyValue object and pass it in. How do you create a PropertyValue object? It's not a native VFP class, so we need to ask OpenOffice.org to do it for us using a technique called "reflection." Pass in a reference to a VFP object, and OpenOffice.org returns the specified object in its place. The resulting code, in Listing 3, shows Visual FoxPro invoking the OpenOffice.org Writer, and adding a bit of text to a new document.

Listing 3. The resulting VFP code to accomplish the same as Listing 2.

* OpenOffice.org automation
* Sample from http://udk.openoffice.org/common/
*           man/tutorial/office_automation.html
* Translated to VFP code
* Ted Roche, 30 Dec 2001

LOCAL loOfcMgr, loDesktop, loDocument, args(1), ;
      loCoreReflection, loPropertyValue, loText,;
      loCursor

* Create the Service Manger and Desktop
loOfcMgr = CreateObject(; 
   "com.sun.star.ServiceManager")
loDesktop = loOfcMgr.createInstance(;
   "com.sun.star.frame.Desktop")

* The args array is an array of "PropertyValue"
* objects - create by invoking OO.o reflection
loCoreReflection = loOfcMgr.createInstance( ;
  "com.sun.star.reflection.CoreReflection" )
loPropertyValue = CREATEOBJECT("Empty")
loCoreReflection.forName( ;
  "com.sun.star.beans.PropertyValue"). ;
   createobject(@loPropertyValue)
args[1] = loPropertyValue
args[1].name = "ReadOnly"
args[1].value = .F.

* Tell VFP to pass arrays to the loDesktop object
* as zero-based, by reference
COMARRAY(loDesktop,10)
* Open a new empty writer document
loDocument = loDesktop.loadComponentFromURL(;
  "private:factory/swriter","_blank", 0, @args)
                 
* Insert sample Text
loText=loDocument.getText() && Create text object
* Create a cursor object (position pointer)
loCursor= loText.createTextCursor()
loText.insertString(loCursor, ;
 "The first line in the created text document."+;
 CHR(10) + "Fox Rocks!", .F.)

For those of us who are used to COM Automation, working with OpenOffice.org has a ways to go to provide the same ease of use. There are no type libraries to load into the Object Browser, nor will IntelliSense let you explore the object model interactively. At this point, only the written and Web-based documentation is available, so a fair amount of trial and error is required to develop automated solutions in OpenOffice.org.

Conclusion

OpenOffice.org is a full-fledged office package with most (if not all) of the functionality your clients need for word processing, spreadsheets, and presentations. OpenOffice.org offers your clients the freedom to choose an office package that meets their support needs and budget. The developer's kit is in beta stage, so digging through the documentation, and making sense of it all, is the biggest challenge for us developers at this point. I encourage you to download, install, and evaluate the package and the SDK (it's all free!) for your clients who will be asking you about it. Test out the macro capability, work with the Automation interface, and consider whether you should be proposing OpenOffice.org for the next client needing office document Automation.

More Information

Download your copy of OpenOffice.org from the links at www.openoffice.org. The Sun Microsystems, Inc. StarOffice home page is at www.staroffice.com. The UNO Development Kit can be found at http://udk.openoffice.org. In-depth explanations of how the OLE Automation bridge is constructed can be found at http://udk.openoffice.org/common/man/spec/ole_bridge.html. A short form of the history of StarOffice and OpenOffice.org is at www.openoffice.org/about_us/milestones.html. An interesting white paper—subtitled "Why is Sun Doing this?"—can be found at www.openoffice.org/white_papers/OOo_project/OOo_project.html. The office Automation tutorial, on which the example is based, is located at http://udk.openoffice.org/common/man/tutorial/office_automation.html. Finally, thanks to Carlos Guzmán Alvarez, who provided the solution to the empty args() array problem in the December 2002 issue of FoxPress at www.fpress.com/revista/Num1202/Truco.htm.

Download 05TROCSC.ZIP