Web-based spreadsheets with OpenOffice.org and Dojo

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.

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!

Preparing OpenOffice.org

OpenOffice.org is a cross-platform office suite. It is based on a component model called Universal Network Objects, or UNO, which allows components to communicate across the network oblivious to the platform they run on and the language they were written in. Though it’s usually thought of as a desktop application, OpenOffice.org can be also run in server mode. In this mode, OpenOffice.org listens to a network port for connections. You can connect to an OpenOffice.org server running either on a local or remote computer and use the UNO environment to work with documents. UNO libraries for both client and server modes are part of the standard OpenOffice.org distribution. The libraries required for a client to access an OpenOffice.org server are:

• juh.jar
• jurt.jar
• ridl.jar
• unoil.jar

You can find these JAR files in the directory where you installed OpenOffice.org.

OpenOffice.org can be started in server mode and prepared to accept network connections with the following command line:

soffice -accept=socket,host=localhost,port=8100;urp

With the client libraries in the classpath, it then takes the client side several lines of code to construct an access point to the running OpenOffice.org — an instance of the Java class com.sun.star.frame.Desktop. All OpenOffice.org application windows are organized in a hierarchy of frames that contain viewable components. The desktop is the root frame for this hierarchy. From the desktop, you can load viewable components, access frames and components, terminate the application, traverse the frame hierarchy, and dispatch command requests. The com.sun.star.frame.Desktop class, which is a part of UNO, represents this desktop; thus, it is the root management point for all OpenOffice.org application frameworks.

If OpenOffice.org is running on a local computer, things are much easier; client libraries do most of the job, and they will even start OpenOffice.org for you, as shown in Listing 1.

Listing 1. Getting a Desktop object from UNO

XComponentContext xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
XMultiComponentFactory xServiceManager = xContext.getServiceManager();
Object desktop = xServiceManager.createInstanceWithContext(
     "com.sun.star.frame.Desktop", xContext );

To make the code in Listing 1 work, the client JARs mentioned above need to be in the classpath. If they reside in a location other than their original one in the OpenOffice.org installation (which will be the case in Web applications), you must make sure that the location of the OpenOffice.org program folder is in the classpath. If you’re using Tomcat, this can be done by editing the catalina.properties file (in the Tomcat conf folder) and appending the OpenOffice.org folder location (which will be something like /usr/local/OO2.3/program) to the common.loader variable.

We are now going to create a SpreadsheetDoc class with helper methods operating with OpenOffice.org. It should have two private fields: com.sun.star.lang.XComponent xComponent and com.sun.star.sheet.XspreadsheetDocument xSpreadsheetDoc. During its construction, it will connect to OpenOffice.org and open a spreadsheet document from a hard-coded location. Note how casting works with UNO: to cast a UNO object to some type, you must use the UnoRuntime.queryInterface(class, object) method. We are also passing a property value Hidden so that OpenOffice.org will stay invisible. Listing 2 demonstrates a constructor of class SpreadsheetDoc().

Listing 2. SpreadsheetDoc() class constructor

public SpreadsheetDoc() throws Exception {
    XComponentContext xCompContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
    XMultiComponentFactory xMCF = xCompContext.getServiceManager();
    Object oDesktop = xMCF.createInstanceWithContext("com.sun.star.frame.Desktop", xCompContext);
    XComponentLoader aLoader = (XComponentLoader) UnoRuntime.queryInterface(XComponentLoader.class, oDesktop);
    PropertyValue[] loadProps = new PropertyValue[]{new PropertyValue("Hidden", 0, true, null)};
    xComponent = aLoader.loadComponentFromURL("file:///tmp/demo.ods", "_blank", 0, loadProps);
    xSpreadsheetDoc = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xComponent);
}

At this point, we have a reference to the OpenOffice.org spreadsheet document. Now we can get the concrete spreadsheet from the document, either by its name or by its position. In Listing 3, we take the latter route.

Listing 3. Getting the spreadsheet by position

public XSpreadsheet getSpreadsheet(int nIndex) throws Exception {
    XSpreadsheets xSheets = xSpreadsheetDoc.getSheets();
    return (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSheets.getByName(xSheets.getElementNames()[nIndex]));
}

The next step is to get the data from the sheet as a two-dimensional array so that we can pass it to the front end. This is achieved with the getDataArray() method found in the XCellRangeData that represents the range we want to work with. It takes a little science to determine the XCellRangeData, though, because it is defined by the lower-right cell having a value in it. In order to detect the coordinates of this key cell, we need to query the spreadsheet for all the cells with values of any supported type and iterate through them, as in Listing 4.

Listing 4. Getting spreadsheet data

public Object[][] getSheetContentData(XSpreadsheet xSheet) throws Exception {
    XCellRangesQuery xRangesQuery = (XCellRangesQuery) UnoRuntime.queryInterface(XCellRangesQuery.class, xSheet);
    XSheetCellRanges xCellRanges = xRangesQuery.queryContentCells((short) (CellFlags.DATETIME
            | CellFlags.FORMULA | CellFlags.STRING | CellFlags.VALUE));
    CellRangeAddress[] addrs = xCellRanges.getRangeAddresses();
    int startR = 0, startC = 0, endR = 0, endC = 0;
    for (CellRangeAddress addr : addrs) {
        if (addr.StartRow < startR) startR = addr.StartRow;
        if (addr.StartColumn < startC) startC = addr.StartColumn;
        if (addr.EndRow > endR) endR = addr.EndRow;
        if (addr.EndColumn > endC) endC = addr.EndColumn;
    }
    XCellRange xRange = xSheet.getCellRangeByPosition(startC, startR, endC, endR);
    return ((XCellRangeData) UnoRuntime.queryInterface(XCellRangeData.class, xRange)).getDataArray();
}

Now we have an array of spreadsheet cell data. In the next section, you’ll see how to pass this array to the front end in JSON format; before we can do that, though, we still need to implement some more methods in our SpreadsheetDoc class to be able to change cell values, to save, and to close the document. Setting cell values is a straightforward operation, as you can see in Listing 5; the only possible problem is to determine the value type. Like Microsoft Excel, OpenOffice.org works with values of types Empty, Value, Text, or Formula; to keep things simple, though, our example considers all values to be of type Text.

Listing 5. Setting cell text value

public void setText(XSpreadsheet xSheet, int nRow, int nColumn, String text) throws Exception {
    XCell cell = xSheet.getCellByPosition(nColumn, nRow);
    XText xText = (XText) UnoRuntime.queryInterface(XText.class, cell);
    xText.setString(text);
}

Finally, we need to save and close our document, as shown in Listing 6.

Listing 6. Saving and closing a document

public void save() throws Exception {
    XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDoc);
    XModifiable xModified = (XModifiable) UnoRuntime.queryInterface(XModifiable.class, xModel);
    if (xModified.isModified()) {
        XStorable xStore = (XStorable) UnoRuntime.queryInterface(XStorable.class, xModel);
        xStore.store();
    }
}

public void close() throws Exception {
    XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xComponent);
    XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xModel);
    xCloseable.close(true);
}

Building the servlet

Now that the OpenOffice.org portion of the application is ready to handle operations on data in the spreadsheet, we need to build middleware that connects the Java-based back end and Web-based front end. We are going to create a servlet capable of processing Web-originated commands transmitted via a simple JSON-based protocol (on top of HTTP, of course).

We will need to convert spreadsheet data to JSON-formatted data, and vice-versa. Let’s create a JsonUtil class with helper methods. The first task to implement would be the conversion of column numbers to column names. An OpenOffice.org spreadsheet has a maximum of 256 columns, named alphabetically from A to IV; thus, we will need a method to convert a number to a name — the number 30 to AE, for instance. This method is outlined in Listing 7.

Listing 7. Converting a column number to a column name

public static String getColumnName(int column) {
    StringBuilder sb = new StringBuilder();
    int value = column;
    do {
        int digit = value % 26; // 26 is the number of letters in English
        sb.insert(0, (char) ('A' + digit));
        value = value / 26 - 1;
    } while (value >= 0);
    return sb.toString();
}

In Listing 8, we prepare the spreadsheet data that we get with SpreadsheetDoc.getSheetContentData() to be sent to the front end in JSON format.

Listing 8. Wrapping spreadsheet data into JSON format

public static String arrayToJson(Object[][] data) {
    StringBuilder sb = new StringBuilder();
    sb.append("{ items: [ntt");
    int h = data.length;
    int w = data[0].length;
    for (int i = 0; i < h; i++) {
        sb.append("{ ");
        for (int j = 0; j < w; j++) {
            sb.append(getColumnName(j)).
                    append(": "").append(data[i][j].toString()).append(""").
                    append(j == w - 1 ? "" : ", ");
        }
        sb.append(" }").append(i == h - 1 ? "" : ",").append("ntt");
    }
    sb.append("]n}");
    return sb.toString();
}

We need to create an OOSynchServlet to handle requests from the front end that fetch spreadsheet data and to update cell text values. We will use the request parameter type to distinguish between these two request types. We will also need to pass cell coordinates and cell text values with the parameters rowIndex, columnIndex, and cellValue, respectively. The OOSynchServlet service() method is shown in Listing 9.

Listing 9. OOSynchServlet service() method

protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
        PrintWriter out = response.getWriter();
        String sType = request.getParameter("type");
        SpreadsheetDoc doc = new SpreadsheetDoc();
        XSpreadsheet xSheet;
        if ("FetchData".equals(sType)) {
            xSheet = doc.getSpreadsheet(0);
            out.println(JsonUtil.arrayToJson(doc.getSheetContentData(xSheet)));
        } else if ("CellContentChange".equals(sType)) {
            xSheet = doc.getSpreadsheet(0);
            doc.setText(xSheet,
                    Integer.parseInt(request.getParameter("rowIndex")),
                    Integer.parseInt(request.getParameter("columnIndex")),
                    request.getParameter("cellValue"));
            doc.save();
        }
        doc.close();
    } catch (Exception e) {
        throw new ServletException(e);
    }
}

Next up, we map OOSynchServlet to the URL /oosynch.do in our Web application’s web.xml file, as shown in Listing 10.

Listing 10. OOSynchServlet servlet mapping

<servlet>
    <servlet-name>OOSynch</servlet-name>
    <servlet-class>oodemo.OOSynchServlet</servlet-class>
</servlet>

<servlet-mapping>
    <servlet-name>OOSynch</servlet-name>
    <url-pattern>/oosynch.do</url-pattern>
</servlet-mapping>

Dojo on the front end

Dojo is a popular Ajax framework. The distribution is rather large for a JavaScript framework because it contains lots of components, but we will use just one: Dojo grid. For projects going to production and using a restricted set of Dojo features, you should create a custom build containing only the dependencies required. (More details on custom builds and optimization can be found in Resources.)

For the front end of our application, we will need one HTML file with Dojo imports and initialization, along with two JavaScript files. The first JavaScript file, OOSynchronizer.js, is shown in Listing 11. It defines an OOSynchronizer class that formats and populates the Dojo grid with data.

Listing 11. OOSynchronizer.js

if (!dojo._hasResource["oodemo.OOSynchronizer"]) {
dojo._hasResource["oodemo.OOSynchronizer"] = true;

dojo.provide("oodemo.OOSynchronizer");
dojo.require("dojox.grid.Grid");
dojo.require("dojox.grid._data.model");
dojo.require("dojo.data.ItemFileWriteStore");
dojo.require("oodemo.UIAspects");

dojo.declare("oodemo.OOSynchronizer", null, {
    uiAspects: null,

    constructor: function() {
        this.uiAspects = new oodemo.UIAspects();
    },

    fetchData: function() {
        var kwp = {
            url: "/SpreadsheetAccess/oosynch.do",
            handleAs: "json",
            load: this.fetchDataResponse,
            content: { type: "FetchData" }
        }
        dojo.xhrPost(kwp);
    },

    fetchDataResponse: function(response) {
        var jsonStore = new dojo.data.ItemFileWriteStore({data: response});
        var newModel = new dojox.grid.data.DojoData(null, jsonStore, {query: {}});
        newModel.sort = function() {}; //Disable sort functionality
        grid.setModel(newModel);
        var attrs = [];
        for (var key in response.items[0]) attrs.push(key);
        grid.setStructure(createStructure(attrs));
        oosynch.uiAspects.addBuildRowContentAspect();
        grid.render();
    }
})

createStructure = function(columnHeaders) {
    var rightView = {
        defaultCell: { width: 32, editor: dojox.grid.editors.Input, styles: 'text-align: right;' }
    };
    rightView.rows = [];
    var rowsContent = [];
    for (var i = 0; i < columnHeaders.length; i++) {
        rowsContent.push({ name: columnHeaders[i], styles: '', width: '70px' });
    }
    rightView.rows.push(rowsContent);
    return [{ type: 'dojox.GridRowView' }, rightView];
}
}

The constructor creates an instance of the oodemo.UIAspects class, which we’ll discuss later. The fetchData() method makes an Ajax call for spreadsheet data; upon completion, it passes control to fetchDataResponse(), which takes care of grid formatting and data population.

The grid object mentioned here is an instance of Dojo grid, dojox.Grid (we’ll define it in the HTML file discussed later). Two main Dojo grid concepts are the model and the structure. The Model object is an instance of a dojox.grid.data.Model subclass. Dojo grid uses the model to retrieve data and manipulate it. In our case, we use dojox.grid.data.DojoData, created in the fetchDataResponse() method. To construct it, we need to supply it with two objects. First, it needs a store object jsonStore, an object of class dojo.data.ItemFileWriteStore that contains our JSON data received from server. It also needs an object that the model will use to fetch data from the store ({query: {}} means that the model will fetch all data from the store).

The structure defines cell rendering properties, such as editor and column names. The fetchDataResponse() method calls the createStructure() helper method to create the grid structure. (See Resources for more details about grid and its behavior.)

One powerful feature of Dojo is its AOP-like extension mechanisms, which help to connect custom behavior (aspects) to Dojo components. We need to add UI functionality to the grid, such as row numbering behavior and the communication of cell content to the server. The file UIAspects.js, shown in Listing 12, contains cellContentChangeAdvice, which is connected to the grid in the doApplyCellEdit() method, which in turn is called when the cell edit operation is completed. UIAspects.js also contains buildRowContentAdvice, which encloses the row numbering behavior that the grid lacks.

Listing 12. UIAspects.js

if (!dojo._hasResource["oodemo.UIAspects"]) {
dojo._hasResource["oodemo.UIAspects"] = true;
dojo.provide("oodemo.UIAspects");

dojo.declare("oodemo.UIAspects", null, {

    constructor: function() {
        this.addCellContentChangeAspect();
    },

    addCellContentChangeAspect: function() {
        dojo.connect(grid, "doApplyCellEdit", this, "cellContentChangeAdvice");
    },

    addBuildRowContentAspect: function() {
        dojo.connect(grid.views.views[0], "buildRowContent", this, "buildRowContentAdvice");
    },

    cellContentChangeAdvice: function(datum, rowIndex, colIndex) {
        var kwp = {
            url: "/SpreadsheetAccess/oosynch.do",
            handleAs: "json",
            content: {
                type: "CellContentChange",
                cellValue: datum,
                rowIndex: rowIndex,
                columnIndex: colIndex
            }
        }
        dojo.xhrPost(kwp);
    },

    buildRowContentAdvice: function(idx, node) {
        node.childNodes[0].childNodes[0].childNodes[0].childNodes[0].innerHTML = idx + 1;
    }
});
}

Finally, Listing 13 contains the HTML file sheetdemo.html, where Dojo initialization is started, global JavaScript variables are defined, and the grid itself is put. Dojo is instructed to parse HTML on load (with parseOnLoad: true), which produces the global JavaScript variable grid out of div (by its jsId property value).

Listing 13. sheetdemo.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <style type="text/css">
            @import "js/dojo/dojox/grid/_grid/Grid.css";
        </style>
        <script type="text/javascript" src="js/dojo/dojo/dojo.js" djConfig="isDebug:false, parseOnLoad: true"></script>
        <script type="text/javascript">
            dojo.registerModulePath("oodemo", "../../oodemo");
            dojo.require("oodemo.OOSynchronizer");
            dojo.require("dojox.grid.Grid");
            dojo.require("dojox.grid._data.model");
            dojo.require("dojo.parser");
            var oosynch;
            dojo.addOnLoad(function() {
                oosynch = new oodemo.OOSynchronizer();
                oosynch.fetchData();
            });
        </script>
    </head>
    <body>
        <h2>Synchronization with OO Demo</h2><br>
        <div id="grid" dojoType="dojox.Grid" jsId="grid"></div>
    </body>
</html>

Connecting the pieces

In order to get everything up and running, you’ll need Dojo 1.0.x, OpenOffice.org 2.x, and Tomcat 5.5.x, with the catalina.properties file modified as described in the first section of this article. (You could also use another Servlet-compatible container, but the instructions here are tailored to work with Tomcat.)

To begin, you need to create a new Web application. Create a folder named SpreadsheetAccess in the Tomcat webapps folder and copy sheetdemo.html (Listing 13) there. Create and compile the three Java classes described above: SpreadsheetDoc (Listings 2, 3, 4, 5, and 6), JsonUtil (Listings 7 and 8), and OOSynchServlet (Listings 9 and 10). Don’t forget to change the path to a specific OpenOffice.org spreadsheet file in SpreadsheetDoc — any file type recognized by OpenOffice.org will do. Move the compiled Java classes to SpreadsheetAccess/WEB-INF/classes.

Copy the four JAR files mentioned in the first section of this article from the OpenOffice.org installation to SpreadsheetAccess/WEB-INF/lib. Create a folder at SpreadsheetAccess/js and unpack Dojo 1.0.x into its dojo subfolder so that dojo.js is in SpreadsheetAccess/js/dojo/dojo. Create a folder at SpreadsheetAccess/js/oodemo and put the JavaScript files from Listings 11 and 12 into it.

Now open your browser and point it to http://localhost:8080/SpreadsheetAccess/sheetdemo.html. You will see the application in action! It should look something like Figure 2.

In conclusion

This article has demonstrated the server capabilities of OpenOffice.org. OpenOffice.org is a unique office suite that can work with most office document formats in a platform-independent way. It is a perfect choice for solutions requiring remote document processing.

The example application in the article used Dojo and its grid component as a front end. Dojo is a powerful JavaScript framework with lots of components ready to be used with almost no development effort. It also provides an AOP-like mechanism to add custom behavior to its components.

The combination of OpenOffice.org and Dojo resulted in a working application resembling Google Spreadsheets and capable of displaying and editing cell values — and all that with minimal development effort spent.