SeamFramework.orgCommunity Documentation
Seam also supports generation of the Microsoft® Excel® spreadsheet application spreadsheets through the excellent JExcelAPI library. The generated document is compatible with the Microsoft® Excel® spreadsheet application versions 95, 97, 2000, XP and 2003. Currently a limited subset of the library functionality is exposed but the ultimate goal is to be able to do everything the library allows for. Please refer to the JExcelAPI documentation for more information on capabilities and limitations.
The Microsoft® Excel® spreadsheet application jboss-seam-excel.jar
. This JAR contains
the the Microsoft® Excel® spreadsheet application JSF controls, which are used to construct views that can
render the document, and the DocumentStore component, which serves
the rendered document to the user. To include the Microsoft® Excel® spreadsheet application support in
your application, included jboss-seam-excel.jar
in your WEB-INF/lib
directory along with the
jxl.jar
JAR file. Furthermore, you need to
configure the DocumentStore servlet in your web.xml
The Microsoft® Excel® spreadsheet application Seam module requires the use of Facelets as the view technology. Additionally, it requires the use of the seam-ui package.
The examples/excel
project contains an example of
the Microsoft® Excel® spreadsheet application support in action. It demonstrates proper deployment
packaging, and it shows the exposed functionality.
Customizing the module to support other kinds of the Microsoft® Excel® spreadsheet application spreadsheet
API's has been made very easy. Implement the ExcelWorkbook
interface, and register in components.xml.
<excel:excelFactory>
<property name="implementations">
<key>myExcelExporter</key>
<value>my.excel.exporter.ExcelExport</value>
</property>
</excel:excelFactory>
and register the excel namespace in the components tag with
xmlns:excel="http://jboss.com/products/seam/excel"
Then set the UIWorkbook type to myExcelExporter
and your
own exporter will be used. Default is "jxl", but support for CSV has also been
added, using the type "csv".
See Section 18.5, “Configuring iText” for information on how to configure the document servlet for serving the documents with an .xls extension.
If you are having problems accessing the generated file under IE (especially with https), make sure you are not using too strict restrictions in the browser (see http://www.nwnetworks.com/iezones.htm/), too strict security constraint in web.xml or a combination of both.
Basic usage of the worksheet support is simple; it is used like a
familiar <h:dataTable>
and you can bind to a
List
, Set
,
Map
, Array
or
DataModel
.
<e:workbook xmlns:e="http://jboss.com/products/seam/excel">
<e:worksheet>
<e:cell column="0" row="0" value="Hello world!"/>
</e:worksheet>
</e:workbook>
That's not terribly useful, so lets have a look at a more common case:
<e:workbook xmlns:e="http://jboss.com/products/seam/excel">
<e:worksheet value="#{data}" var="item">
<e:column>
<e:cell value="#{item.value}"/>
</e:column>
</e:worksheet>
</e:workbook>
First we have the top-level workbook element which serves as the container and it doesn't have any attributes. The child-element worksheet has two attributes; value="#{data}" is the EL-binding to the data and var="item" is the name of the current item. Nested inside the worksheet is a single column and within it you see the cell which is the final bind to the data within the currently iterated item
This is all you know to get started dumping your data to worksheets!
Workbooks are the top-level parents of worksheets and stylesheet links.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:cell value="Hello World" row="0" column="0"/>
</e:worksheet>
<e:workbook>
defines a workbook with a worksheet and a greeting at A1
Worksheets are the children of workbooks and the parent of columns and worksheet commands They can also contain explicitly placed cells, formulas, images and hyperlinks. They are the pages that make up the workbook.
|
Child elemenents
Facets
|
<e:workbook>
<e:worksheet name="foo" startColumn="1" startRow="1">
<e:column value="#{personList}" var="person">
<f:facet name="header">
<e:cell value="Last name"/>
</f:facet>
<e:cell value="#{person.lastName}"/>
</e:column>
</e:worksheet>
<e:workbook>
defines a worksheet with the name "foo", starting at B2.
Columns are the children of worksheets and the parents of cells, images, formulas and hyperlinks. They are the structure that control the iteration of the worksheet data. See Section 19.14.5, “Column settings” for formatting.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<f:facet name="header">
<e:cell value="Last name"/>
</f:facet>
<e:cell value="#{person.lastName}"/>
</e:column>
</e:worksheet>
<e:workbook>
defines a column with a header and an iterated output
Cells are nested within columns (for iteration) or inside worksheets
(for direct placement using the column
and
row
attributes) and are responsible for outputting
the value (usually though en EL-expression involving the
var
-attribute of the datatable. See
???
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<f:facet name="header">
<e:cell value="Last name"/>
</f:facet>
<e:cell value="#{person.lastName}"/>
</e:column>
</e:worksheet>
</e:workbook>
defines a column with a header and an iterated output
Validations are nested inside cells, formulas or cell templates. They add constrains for the cell data.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<e:cell value="#{person.age">
<e:numericValidation condition="between" value="4"
value2="18"/>
</e:cell>
</e:column>
</e:worksheet>
</e:workbook>
adds numeric validation to a cell specifying that the value must be between 4 and 18.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<e:cell value="#{person.position">
<e:rangeValidation startColumn="0" startRow="0"
endColumn="0" endRow="10"/>
</e:cell>
</e:column>
</e:worksheet>
</e:workbook>
adds validation to a cell specifying that the value must be in the values specified in range A1:A10.
|
Attributes
Child elemenents
Facets
|
e:listValidation is a just a container for holding multiple e:listValidationItem tags.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<e:cell value="#{person.position">
<e:listValidation>
<e:listValidationItem value="manager"/>
<e:listValidationItem value="employee"/>
</e:listValidation>
</e:cell>
</e:column>
</e:worksheet>
</e:workbook>
adds validation to a cell specifying that the value must be "manager" or "employee".
Format masks are defined in the mask attribute in cell templates,
cells or formulas. Note that when using templates, the
format mask must be placed in the first template
to be cascaded since the constructor hierarchy in
JExcelAPI
used for copying cell formats makes
it hard to change the format mask at a later stage. There are two
types of format masks, one for numbers and one for dates
When encountering a format mask, first it is checked if it is in internal form, e.g "format1", "accounting_float" and so on (see jxl.write.NumberFormats ).
if the mask is not in the list, it is treated as a custom mask (see java.text.DecimalFormat ). e.g "0.00" and automatically converted to the closest match.
When encountering a format mask, first it is checked if it is in internal form, e.g "format1", "format2" and so on (see jxl.write.DecimalFormats ).
if the mask is not in the list, it is treated as a custom mask (see java.text.DateFormat )., e.g "dd.MM.yyyy" and automatically converted to the closest match.
Formulas are nested within columns (for iteration) or inside worksheets
(for direct placement using the column
and
row
attributes) and add calculations or functions to
ranges of cells. They are essentially cells, see
Section 19.6, “Cells” for available attributes. Note that they
can apply templates and have own font definitions etc just as normal
cells.
The formula of the cell in placed in the value
-attribute as a normal the Microsoft® Excel® spreadsheet application notation. Note that when doing
cross-sheet formulas, the worksheets must exist before referencing
a formula against them. The value is a string.
<e:workbook>
<e:cellTemplate name="fooTemplate">
<e:font color="red"/>
</e:cellTemplate>
<e:worksheet name="fooSheet">
<e:cell column="0" row="0" value="1"/>
</e:worksheet>
<e:worksheet name="barSheet">
<e:cell column="0" row="0" value="2"/>
<e:formula column="0" row="1"
value="fooSheet!A1+barSheet1!A1"
templates="fooTemplate">
<e:font fontSize="12"/>
</e:formula>
</e:worksheet>
</e:workbook>
defines an formula in B2 summing cells A1 in worksheets FooSheet and BarSheet
Images are nested within columns (for iteration) or inside worksheets
(for direct placement using the startColumn/startRow
and rowSpan/columnSpan
attributes). The spans are
optional and if omitted, the image will be inserted without resizing.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:image startRow="0" startColumn="0" rowSpan="4"
columnSpan="4" URI="http://foo.org/logo.jpg"/>
</e:worksheet>
</e:workbook>
defines an image in A1:E5 based on the given data
Hyperlinks are nested within columns (for iteration) or inside
worksheets (for direct placement using the
startColumn/startRow
and
endColumn/endRow
attributes). They add link
navigation to URIs
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:hyperLink startRow="0" startColumn="0" endRow="4"
endColumn="4" URL="http://seamframework.org"
description="The Seam Framework"/>
</e:worksheet>
</e:workbook>
defines a described hyperlink pointing to SFWK in the area A1:E5
Headers and footers are childrens of worksheets and contain facets which in turn contains a string with commands that are parsed.
|
Attributes
Child elemenents
Facets
|
|
Attributes
Child elemenents
Facets
|
The content of the facets is a string that can contain various #-delimited commands as follows:
#date# |
Inserts the current date |
#page_number# |
Inserts the current page number |
#time# |
Inserts the current time |
#total_pages# |
Inserts the total page count |
#worksheet_name# |
Inserts the worksheet name |
#workbook_name# |
Inserts the workbook name |
#bold# |
Toggles bold font, use another #bold# to turn it off |
#italics# |
Toggles italic font, use another #italic# to turn it off |
#underline# |
Toggles underlining, use another #underline# to turn it off |
#double_underline# |
Toggles double underlining, use another #double_underline# to turn it off |
#outline# |
Toggles outlined font, use another #outline# to turn it off |
#shadow# |
Toggles shadowed font, use another #shadow# to turn it off |
#strikethrough# |
Toggles strikethrough font, use another #strikethrough# to turn it off |
#subscript# |
Toggles subscripted font, use another #subscript# to turn it off |
#superscript# |
Toggles superscript font, use another #superscript# to turn it off |
#font_name# |
Sets font name, used like #font_name=Verdana" |
#font_size# |
Sets font size, use like #font_size=12# |
<e:workbook>
<e:worksheet>
<e:header>
<f:facet name="left">
This document was made on #date# and has #total_pages# pages
</f:facet>
<f:facet name="right">
#time#
</f:facet>
</e:header>
<e:worksheet>
</e:workbook>
Print areas and titles childrens of worksheets and worksheet templates and provide... print areas and titles.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:printTitles firstRow="0" firstColumn="0"
lastRow="0" lastColumn="9"/>
<e:printArea firstRow="1" firstColumn="0"
lastRow="9" lastColumn="9"/>
</e:worksheet>
</e:workbook>
defines a print title between A1:A10 and a print area between B2:J10.
Worksheet commands are children of workbooks and are usually executed only once.
Provides grouping of columns and rows.
|
Attributes
Child elements
Facets
|
|
Attributes
Child elements
Facets
|
<e:workbook>
<e:worksheet>
<e:groupRows startRow="4" endRow="9" collapse="true"/>
<e:groupColumns startColumn="0" endColumn="9" collapse="false"/>
</e:worksheet>
</e:workbook>
groups rows 5 trough 10 and columns 5 through 10 so that the rows are initially collapsed (but not the columns).
Provides page breaks
|
Attributes
Child elements
Facets
|
<e:workbook>
<e:worksheet>
<e:rowPageBreak row="4"/>
</e:worksheet>
</e:workbook>
breaks page at row 5.
Provides cell merging
|
Attributes
Child elements
Facets
|
<e:workbook>
<e:worksheet>
<e:mergeCells startRow="0" startColumn="0" endRow="9" endColumn="9"/>
</e:worksheet>
</e:workbook>
merges the cells in the range A1:J10
If you prefer to export an existing JSF datatable instead of writing a
dedicated XHTML document, this can also be achieved easily by executing
the org.jboss.seam.excel.excelExporter.export
component, passing in the id of the datatable as an Seam EL parameter.
Consider you have a data table
<h:form id="theForm">
<h:dataTable id="theDataTable" value="#{personList.personList}"
var="person">
...
</h:dataTable>
</h:form>
that you want to view as an Microsoft® Excel® spreadsheet. Place a
<h:commandLink
value="Export"
action="#{excelExporter.export('theForm:theDataTable')}"
/>
in the form and you're done. You can of course execute the exporter with a button, s:link or other preferred method. There are also plans for a dedicated export tag that can be placed inside the datatable tag so you won't have to refer to the datatable by ID.
See Section 19.14, “Fonts and layout” for formatting.
Controlling how the output look is done with a combination of CSSish style attributes and tag attributes. The most common ones (fonts, borders, backgrounds etc) are CSS and some more general settings are in tag attributes.
The CSS attributes cascade down from parent to children and within one tag
cascades over the CSS classes referenced in the styleClass
attributes and finally over the CSS attributes defined in the
style
attribute. You can place them pretty much anywhere but
e.g. placing a column width setting in a cell nested within that column makes
little sense.
External stylesheets are references with the e:link tag. They are placed as children of the workbook.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:link URL="/css/excel.css"/>
</e:workbook>
References a stylesheet that can be found at /css/excel.css
This group of XLS-CSS attributes define a font and its attributes
xls-font-family |
The name of the font. Make sure that it's one that is supported by your system. |
xls-font-size |
The font size. Use a plain number |
xls-font-color |
The color of the font (see jxl.format.Colour ). |
xls-font-bold |
Should the font be bold? Valid values are "true" and "false" |
xls-font-italic |
Should the font be italic? Valid values are "true" and "false" |
xls-font-script-style |
The script style of the font (see jxl.format.ScriptStyle ). |
xls-font-underline-style |
The underline style of the font (see jxl.format.UnderlineStyle ). |
xls-font-struck-out |
Should the font be struck out? Valid values are "true" and "false" |
xls-font |
A shorthand notation for setting all the values. Place the font name last and use tick marks for fonts with spaces in them, e.g. 'Times New Roman'. Use "italic", "bold" and "struckout". Example style="xls-font: red bold italic 22 Verdana" |
This group of XLS-CSS attributes define the borders of the cell
xls-border-left-color |
The border color of the left edge of the cell (see jxl.format.Colour ). |
xls-border-left-line-style |
The border line style of the left edge of the cell (see jxl.format.LineStyle ). |
xls-border-left |
A shorthand for setting color and line style of the left edge of the cell, e.g style="xls-border-left: red thick" |
xls-border-top-color |
The border color of the top edge of the cell (see jxl.format.Colour ). |
xls-border-top-line-style |
The border line style of the top edge of the cell (see jxl.format.LineStyle ). |
xls-border-top |
A shorthand for setting color and line style of the top edge of the cell, e.g style="xls-border-left: red thick" |
xls-border-right-color |
The border color of the right edge of the cell (see jxl.format.Colour ). |
xls-border-right-line-style |
The border line style of the right edge of the cell (see jxl.format.LineStyle ). |
xls-border-right |
A shorthand for setting color and line style of the right edge of the cell, e.g style="xls-border-right: red thick" |
xls-border-bottom-color |
The border color of the bottom edge of the cell (see jxl.format.Colour ). |
xls-border-bottom-line-style |
The border line style of the bottom edge of the cell (see jxl.format.LineStyle ). |
xls-border-bottom |
A shorthand for setting color and line style of the bottom edge of the cell, e.g style="xls-border-bottom: red thick" |
xls-border |
A shorthand for setting color and line style all edges of the cell, e.g style="xls-border: red thick" |
This group of XLS-CSS attributes define the background of the cell
xls-background-color |
The color of the background (see jxl.format.LineStyle ). |
xls-background-pattern |
The pattern of the background (see jxl.format.Pattern ). |
xls-background |
A shorthand for setting the background color and pattern. See above for rules. |
This group of XLS-CSS attributes define the column widths etc.
xls-column-width |
The width of the column. Use largeish values (~5000) to start with. Used by the e:column in xhtml mode. |
xls-column-widths |
The width of the column. Use largeish values (~5000) to start with. Used by the excel exporter, placed in the datatable style attribute. Use numerical values or * to bypass a column. Example style="xls-column-widths: 5000, 5000, *, 10000" |
xls-column-autosize |
Should an attempt be made to autosize the column? Valid values are "true" and "false". |
xls-column-hidden |
Should the column be hidden? Valid values are "true" and "false". |
This group of XLS-CSS attributes define the cell properties
xls-alignment |
The alignment of the cell value (see jxl.format.Alignment ). |
xls-force-type |
The forced type of the cell data. The value is a string that can be one of "general", "number", "text", "date", "formula" or "bool". The type is automatically detected so there is rarely any use for this attribute. |
xls-format-mask |
The format mask of the cell, see Section 19.6.2, “Format masks” |
xls-indentation |
The indentation of the cell value. The value is numeric. |
xls-locked |
Should the cell be locked. Use with workbook level locked. Valid values are "true" and "false". |
xls-orientation |
The orientation of the cell value (see jxl.format.Orientation ). |
xls-vertical-alignment |
The vertical alignment of the cell value (see jxl.format.VerticalAlignment ). |
xls-shrink-to-fit |
Should the cell values shrink to fit? Valid values are "true" and "false". |
xls-wrap |
Should the cell wrap with newlines? Valid values are "true" and "false". |
The datatable exporter uses the same xls-css attributes as the xhtml
document with the exception that column widths are defined with the
xls-column-widths
attribute on the datatable (since
the UIColumn doesn't support the style or styleClass attributes).
In the current version there are some known limitations regarding CSS support
When using .xhtml documents, stylesheets must be referenced
through the <e:link> tag
When using the datatable exporter, CSS must be entered through style-attributes, external stylesheets are not supported
The core of the the Microsoft® Excel® spreadsheet application functionality is based on the excellent JExcelAPI library which can be found on http://jexcelapi.sourceforge.net/ and most features and possible limitations are inherited from here.
If you use the forum or mailing list, please remember that they don't know anything about Seam and the usage of their library, any issues are best reported in the JBoss Seam JIRA under the "excel" module.