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
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.
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:
sheet.getTable().addColumnAt(3).setStyleID("blue");
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
cell.setStyleID("currency");
cell.setFormula("=SUBTOTAL(9;R[-10]C:R[-1]C)");
for (int i = 3; i < 9; i++) {
sheet.addCell(cell);
}
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.
config/xelem.xml, relative to
the classloader of the main application. If desired a different location may be
set by calling the static
{@link nl.fountain.xelem.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 {@link nl.fountain.xelem.excel.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"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:f="urn:schemas-fountain-nl:xelem:config"
xmlns:html="http://www.w3.org/TR/REC-html40">
<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>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="blue">
<Interior ss:Color="#0000FF" ss:Pattern="Solid"/>
</Style>
</Styles>
</Xelem>
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.
{@link nl.fountain.xelem.lex.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 {@link nl.fountain.xelem.excel.Workbook}. The package also offers an event-based model with the {@link nl.fountain.xelem.lex.ExcelReaderListener ExcelReaderListener-interface}. There's a more detailled discussion on the xelem.lex API on the package overview page.
protected void sendWorkbook(HttpServletResponse response, Workbook wb)
throws IOException {
wb.setFileName(wb.getName() + "_" + ++excelCounter + ".xls");
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition",
"attachment; filename=" + wb.getFileName());
PrintWriter out = response.getWriter();
XSerializer xs = new XSerializer(XSerializer.US_ASCII);
try {
xs.serialize(wb, out);
out.flush();
} catch (XelemException e) {
e.printStackTrace();
} finally {
out.close();
}
}
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.
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 {@link javax.xml.transform.Transformer 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.