|
xelem.3.0 | |||||||||
| PREV NEXT | FRAMES NO FRAMES | |||||||||
See:
Description
| Packages | |
|---|---|
| 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.
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
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.
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:
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.
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"
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.
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.
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
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.
|
xelem.3.0 | |||||||||
| PREV NEXT | FRAMES NO FRAMES | |||||||||