Web-based spreadsheets with OpenOffice.org and Dojo

Learn about the server-side capabilities of OpenOffice.org

As functionality traditionally associated with desktop applications moves to the Web, developers are looking for new ways to handle that computational heavy lifting on the server side. But if you need to create a Web-based application that behaves like an office suite, there's no need to reinvent the wheel: the open source OpenOffice.org suite can actually serve as the powerhouse behind a Web application. In this article, you'll learn how to combine OpenOffice.org and Dojo to create a simple Ajax-based spreadsheet application much like Google Spreadsheets.

Many Web applications these days aim to replace a corresponding desktop application in one way or another. For instance, most Web grids and tables, such as those in Google Spreadsheets, essentially mimic desktop office spreadsheets. In this article, you'll learn about the server capabilities of the OpenOffice.org office suite, which enable it to work as a back end for any Web application that provides functionality usually found in office suites. OpenOffice.org can work with files saved in the OpenDocument format, as well as most of the file formats used by Microsoft Office.

OpenOffice.org is more a platform than a single application. Due to its GPL licensing, a number of office products use OpenOffice.org code under the hood, most notably Star Office, Lotus Symphony, and NeoOffice. (Although Star Office was ultimately the parent of OpenOffice.org, the current version is actually a derivative of the OpenOffice.org codebase.) Since all of the OpenOffice.org derivatives share the same component model, the principles in this article could apply to any of them, though we'll focus on OpenOffice.org.

This article will walk through the steps of developing the simple Web-based spreadsheet application diagrammed in Figure 1.

Spreadsheet application diagram
Figure 1. Sample application diagram (click to enlarge)

The sample application uses the grid component from the Dojo JavaScript framework as a front end. We chose Dojo due to its great AOP-like extensibility. As you can see in Figure 1, servlets are used as middleware, connecting front end and back end. An Ajax request to fetch data from the given spreadsheet is sent to the middleware. The middleware communicates with OpenOffice.org (through a helper class). The back end directs the spreadsheet data to the middleware, and the middleware sends the data back to the front end in JavaScript Object Notation (JSON) format. After fetching the data response, the front end creates a structure for Dojo grid component and displays it. At this stage, any change in any cell of the grid will fire an Ajax request to update the cell value. Upon receiving this request, the middleware communicates with the back end, which sets the cell value to match the corresponding cell in the spreadsheet.

The example is an analog to Google Spreadsheets, albeit one that can only perform two actions: getting data from the server and updating cell text. The example is structured so that adding new behavior to it is easy with a help of an AOP-like approach -- but keep in mind that even with Excel, 80 percent of users use only 20 percent of its features!

1 2 3 4 5 Page 1