xelem - Java to SpreadsheetML

Classes and interfaces for reading and writing SpreadsheetML.


nl.fountain.xelem Provides classes that assist in the creation of SpreadsheetML.
nl.fountain.xelem.excel Provides interfaces and classes that represent elements in SpreadsheetML.
nl.fountain.xelem.excel.o Provides classes that represent elements of the office namespace.
nl.fountain.xelem.excel.ss Provides classes that represent elements of the spreadsheet namespace.
nl.fountain.xelem.excel.x Provides classes that represent elements of the excel namespace.
nl.fountain.xelem.expat Provides classes that do not belong to the core of xelem.
nl.fountain.xelem.lex Provides classes that enable reading SpreadsheetML.


Classes and interfaces for reading and writing SpreadsheetML.


The architecture of xelem stays close to the architecture of SpreadsheetML. Each element in the xml can have it's equivalent in a Java class that implements the XLElement-interface. Though for you, the user of this software, these details are often obscured: you simply add a 'Cell' to a 'Worksheet' and bypass the underlying structures of 'Table' and 'Row'. You'd call on a method like freezePanesAt(int row, int column) and don't have to bother about the settings of 'Pane'-elements that go with the show. Xelem takes care of that. The root of the XML spreadsheet is the Workbook-element, the hub and starting point of xelem is the XLWorkbook.class.

Understanding the global structure of SpreadsheetML is critical in using some of the methods of xelem's classes. These methods are often not documented in this javadoc; a notion of their working should be obtained by looking up the corresponding documentation on the xml-element. An overview of SpreadsheetML, the xml-dialect for Excel, can be obtained at: Office 2003 XML Schemas

creating a workbook

Typically, the XLWorkbook is at the start of creating an Excel workbook in SpreadsheetML. Mostly all of the other objects in xelem can be obtained from it or through it by one of the addXxx- or getXxx-methods. After setting up the workbook, you can obtain the org.w3c.dom.Document from it or serialize the workbook by means of one of the serialize-methods in the XSerializer.class.

Here's a small example:

        import nl.fountain.xelem.XSerializer;
        import nl.fountain.xelem.XelemException;
        import nl.fountain.xelem.excel.Workbook;
        import nl.fountain.xelem.excel.ss.XLWorkbook;
        public class HelloExcel {
            public static void main(String[] args) throws XelemException {
                Workbook wb = new XLWorkbook("HelloExcel");
                wb.addSheet().addCell("Hello Excel!");
                new XSerializer().serialize(wb);
The first line constructs a new workbook with the name "HelloExcel". The last line serializes the workbook, in this case to a file called "HelloExcel.xls", since we didn't set a filename on the workbook explicitly. The setting up of the workbook's contents took place between these lines: here we added a sheet and a cell who's contents read "Hello Excel!". In a real-world example the setting up of the workbook's contents and formatting would probably take more lines of code, but would typically be between the construction and the serialization of the workbook.


The XLElement-interface is at the heart of xelem. Each SpreadsheetML-element can have it's equivalent in a Java class that implements this interface. Besides methods for setting attributes and adding children, instances of these classes are capable of assembling their state into an Element and attach this Element to a parent-element in a Document. Assembly of the complete workbook takes place after a call to the Workbook's createDocument-method, wich is called by the XSerializer.class, before serializaton.

Most instances of XLElements can be obtained through one of the addXxx- or getXxx-methods; some of them are dealt with behind the scenes by xelem. A concatenation of such add's and get's can be legal code:

XLElements are unaware of their parent until the time of creation of the Document. This makes it possible to apply the same instance at different places:
        Worksheet sheet = workbook.addSheet();
        Cell cell = sheet.addCellAt(11, 2); // adds a cell at row 11, column 2
        for (int i = 3; i < 9; i++) {
The preceding code-stub configures a cell at row 11, column 2, sets a style and a relative formula, and this cell is distributed in row 11, over columns 3 through 8 inclusive.

the XFactory

The XFactory is an intermediary to the configuration file, it's main productline being SpreadsheetML Style elements. Normally this class expects a configuration file at the location config/xelem.xml, relative to the classloader of the main application. If desired a different location may be set by calling the static XFactory.setConfigurationFileName(String).

What's in the configuration file?
Comments and styles up to this far.

When is the XFactory employed?
The workbook uses an instance of XFactory during the creation of the document and delegates the merging of styles to it when you call mergeStyles on the workbook.

What happens if the configuration file is not found or is corrupt?
Nothing serious. The XFactory will throw Exceptions under these circumstances, but -since they're not fatal errors- the workbook will catch them and register these exceptions as warnings. You should allways check if there are any warnings after creating a document or passing the workbook to the XSerializer. See Workbook.getWarnings().

What's an <f:comment>?
An <f:comment> is an element in the configuration file and is most confusingly known in these java-interfaces as a docComment. DocComments are the comments that will be passed in at the start of the Workbook document, just after the processing instruction.

What about Styles?
Read all about Styles in the next chapter.

Where can I find an example of a configuration file?
An example of the configuration file is included in the distribution of this library and can be found at ../lib/config/xelem.xml. Here's an another example:

    <?xml version="1.0" encoding="UTF-8"?>
    <Xelem   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
      <f:comment>      THIS IS AN EXCEL-FILE OF TYPE SpreadsheetML.        </f:comment>
      <f:comment>                   IF YOU READ THIS                       </f:comment>
      <f:comment>    YOU PROBABLY NEED A MORE RECENT VERSION OF EXCEL.     </f:comment>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Vertical="Bottom"/>
        <Style ss:ID="blue">
          <Interior ss:Color="#0000FF" ss:Pattern="Solid"/>


A 'Style' is a SpreadsheetML-element, but it does not have an equivalent in a java-class in xelem (yet). It's more practical to have the definition of used styles in a file: the configuration file (see above). This way you can use your Excel-application to create styles wysiwyg-like and copy them to the mentioned file. You should not include a prefix on the Style-element nor it's children, though the attributes should have prefixes. Styles should not have an attribute 'ss:ParentID', xelem cannot handle these correctly.

Cells, Rows, Columns and Tables all have a method setStyleID(String id). When you use these methods, you should either have a definition with the same ss:ID-attribute as the passed id in the configuration file or you should have merged a style with such an id before calling the createDocument-method on the workbook.

reading SpreadsheetML

Xelem can read. The nl.fountain.xelem.lex-package (since xelem.2.0) now offers an easy to use API to read spreadsheetML.

ExcelReader is the basic class for reading Excel-xml. It delivers the contents of an xml-file or an xml-InputSource as a fully populated Workbook. The package also offers an event-based model with the ExcelReaderListener-interface. There's a more detailled discussion on the xelem.lex API on the package overview page.

tips & tricks

xelem and webApplications

Using xelem in a webApplication can offer your clients the oppertunity to download Excel-files that are dynamically created upon request. Here's an example of a Servlet-method that responds to a request for download:
        protected void sendWorkbook(HttpServletResponse response, Workbook wb) 
                throws IOException {
            wb.setFileName(wb.getName() + "_" + ++excelCounter + ".xls");
                    "attachment; filename=" + wb.getFileName());
            PrintWriter out = response.getWriter();
            XSerializer xs = new XSerializer(XSerializer.US_ASCII);
            try {
                xs.serialize(wb, out);
            } catch (XelemException e) {
            } finally {
Now the first line of code may be puzzling. The excelCounter is a static int that is increased by 1, each time an xml workbook-file is send over the line. But why is it there? Ever seen the message "A file with the name 'foo.xls' is allready open"? That's why.
Immagine your webApplication serves Excel-files for download entitled "Volume of Business.xls". Your CEO sets some parameters on a form and downloads the "Volume of Business.xls" for Europe for the passed month. She wants to know more, returns to your site, changes some parameters and expects to download the "Volume of Business.xls", this time for the US and for the past year. Now she's in trouble: Excel won't open a second file with the same name.
Here's where the excelCounter comes in. With the line of code the first downloaded file would have been named "Volume of Business_303.xls" or something and the second one "Volume of Business_305.xls". Never a double name and never a problem.

Allthough the produced xml contains a processing-instruction, some browsers only look at the extension when deciding what to do with a downloaded file. My Mozilla browser is effectively (mis)lead by the extension '.xls' and suggests to use Excel as the application to open the downloaded file.

The setContentType- and addHeader-lines serve equally to guarantee the correct transfer of the file.

The XSerializer uses a javax.xml.transform.Transformer to serialize the workbook. Standard this comes with an UTF-8 encoding. Things go wrong when you send this over the wire. Excel won't open the downloaded file. My knowledge of encoding is as profound as the pool in the backyard, but it does help if you switch to US-ASCII encoding. The XSerializer used in the example above is constructed with a constructor that takes an encoding-string as parameter.

The rest of the code is clear enough.