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.
<!-- XML : generated by JHighlight v1.0 (http://jhighlight.dev.java.net) --> <span class="xml_plain"></span><br /> <span class="xml_plain"> </span><br /> <span class="xml_plain"> </span><span class="xml_tag_symbols"><</span><span class="xml_tag_name">excel:excelFactory</span><span class="xml_tag_symbols">></span><span class="xml_plain"></span><br /> <span class="xml_plain"> </span><span class="xml_tag_symbols"><</span><span class="xml_tag_name">property</span><span class="xml_plain"> </span><span class="xml_attribute_name">name</span><span class="xml_tag_symbols">=</span><span class="xml_attribute_value">"implementations"</span><span class="xml_tag_symbols">></span><span class="xml_plain"></span><br /> <span class="xml_plain"> </span><span class="xml_tag_symbols"><</span><span class="xml_tag_name">key</span><span class="xml_tag_symbols">></span><span class="xml_plain">myExcelExporter</span><span class="xml_tag_symbols"></</span><span class="xml_tag_name">key</span><span class="xml_tag_symbols">></span><span class="xml_plain"></span><br /> <span class="xml_plain"> </span><span class="xml_tag_symbols"><</span><span class="xml_tag_name">value</span><span class="xml_tag_symbols">></span><span class="xml_plain">my.excel.exporter.ExcelExport</span><span class="xml_tag_symbols"></</span><span class="xml_tag_name">value</span><span class="xml_tag_symbols">></span><span class="xml_plain"></span><br /> <span class="xml_plain"> </span><span class="xml_tag_symbols"></</span><span class="xml_tag_name">property</span><span class="xml_tag_symbols">></span><span class="xml_plain"></span><br /> <span class="xml_plain"> </span><span class="xml_tag_symbols"></</span><span class="xml_tag_name">excel:excelFactory</span><span class="xml_tag_symbols">></span><span class="xml_plain"></span><br /> <span class="xml_plain"> </span><br /> <span class="xml_plain"> </span><br />
and register the excel namespace in the components tag with
<!-- XML : generated by JHighlight v1.0 (http://jhighlight.dev.java.net) --> <span class="xml_plain"></span><br /> <span class="xml_plain"> </span><br /> <span class="xml_plain"> xmlns:excel="http://jboss.com/products/seam/excel"</span><br /> <span class="xml_plain"> </span><br /> <span class="xml_plain"> </span><br />
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".
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, cell templates and worksheet templates.
|
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.
|
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. They can contain
fonts and other formattings and can also use pre-defined templates.
|
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
Fonts are nested inside cells, formulas, hyperlinks or cell templates. They determine the typeface of the cell data
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<e:cell value="#{person.age">
<e:font fontName="Times New Roman" color="red" bold="true"/>
</e:cell>
</e:column>
</e:worksheet>
</e:workbook>
defines a cell with a red, bold, Times New Roman font.
Backgrounds are nested inside cells, formulas, hyperlinks or cell templates. They determine the color and pattern of the cell.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<e:cell value="#{person.age">
<e:background color="green" pattern="grey_25"/>
</e:cell>
</e:column>
</e:worksheet>
</e:workbook>
defined a green cell background with a 25% grey mask.
Borders are nested inside cells, formulas, hyperlinks and cell templates. They determine the color and line style of the cell borders.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:column value="#{personList}" var="person">
<e:cell value="#{person.age">
<e:border border="left" color="green" lineStyle="thin"/>
</e:cell>
</e:column>
</e:worksheet>
</e:workbook>
defined a thin green border on the left edge of the cell.
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 endColumn/endRow
attributes)
|
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 worksheet templates and parents of header and footer commands They add headers and footers to printed worksheets.
|
Attributes
Child elemenents
Facets
|
The <e:headerFooterCommands>
is just a nesting container in order to support multiple header/footer
commands.
|
Attributes
Child elemenents
Facets
|
Header/footer commands adds actual commands to the header or footer in the location where the containing facet points.
|
Attributes
Child elemenents
Facets
|
<e:workbook>
<e:worksheet>
<e:headerFooter type="header">
<f:facet name="left">
<e:headerFooterCommands>
<e:headerFooterCommand command="page_number"/>
</e:headerFooterCommands>
</f:facet>
</e:headerFooter>
<e:worksheet>
</e:workbook>
Defines a header with the page number in the left corner.
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
Templates are a way of grouping common formatting under a name to be used later. They come in two flavors, cell level and worksheet settings level and they can be cascaded so that the end result is a union of the applied templates (overriden where applicable)
Cell templates are defined on workbook level using the following notation
<e:workbook>
<e:cellTemplate name="foo">
<font color="red"/>
</e:cellTemplate>
</e:workbook>
and are later used by referencing them in the templates attribute of a cell
<e:workbook>
<e:worksheet>
<e:cell templates="foo" column="0" row="0" value="ping"/>
</e:worksheet>
</e:workbook>
Cell templates have the same attributes as cells (see Section 19.6, “Cells” ). Note that the cellTemplate can also contain tags for font, border and background definitions and they are merged with the definitions of the cell using the templates so a more complex definition could look like
<e:workbook>
<e:cellTemplate name="foo" alignment="right">
<e:font name="Times New Roman"/>
<e:background color="blue"/>
</e:cellTemplate>
<e:cellTemplate name="bar" wrap="true">
<e:font color="red"/>
<e:border color="yellow" lineStyle="thick"/>
</e:cellTemplate>
</e:workbook>
so that a cell that later on applies the templates
<e:workbook>
<e:worksheet>
<e:cell templates="foo,bar" column="0" row="0" value="ping">
<e:border border="left" color="green" lineStyle="thin"/>
</e:cell>
</e:worksheet>
</e:workbook>
end up with a red Times New Roman font in a right-aligned, wrapping cell that has a solid blue background and thick yellow borders, with exception of the left border, which is thin and green.
Worksheet settings are defined and used in the same way as cell templates. On workbook level, you can place a
<e:workbook>
<e:worksheetTemplate name="foo" horizontalFreeze="5"/>
</e:workbook>
and when you later use it like
<e:workbook>
<e:worksheet templates="foo" verticalFreeze="5"/>
</e:workbook>
you end up with a worksheet that is frozen at column 5 and row 5. Note that a worksheetTemplate is essentially a named worksheet (see Section 19.4, “Worksheets” ).
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.
In order to provide formatting for the spreadsheet, you can utilize a number of xls-prefixed CSS:ish attributes that are placed in the style-attribute of the datatable to be exported:
<h:form id="theForm">
<h:dataTable
id="theDataTable"
value="#{personList.personList}"
var="person"
style="xlsFontName : Times New Roman; xlsBackgroundColor : red">
...
</h:dataTable>
</h:form>
which sets Times New Roman as the font and red as the cell
background. Just as in CSS, separate name and value with a
:
and multiple attributes with a
;
. Below is a list of the supported
style-attributes:
xlsFontName |
The name of the font |
xlsFontSize |
The font size |
xlsFontColor |
The color of the font (see jxl.format.Colour ). |
xlsFontBold |
Should the font be bold? Valid values are "true" and "false" |
xlsFontItalic |
Should the font be italic? Valid values are "true" and "false" |
xlsFontScriptStyle |
The script style of the font (see jxl.format.ScriptStyle ). |
xlsFontStruckOut |
Should the font be struck out? Valid values are "true" and "false" |
xlsFontUnderlineStyle |
The underline style of the font (see jxl.format.UnderlineStyle ). |
xlsBackgroundColor |
The background color of the cell (see jxl.format.Colour ). |
xlsBackgroundPattern |
The background pattern of the cell (see jxl.format.Pattern ). |
xlsAlignment |
The alignment of the cell value (see jxl.format.Alignment ). |
xlsColumnWidths |
The column widths. Note that the unit is internal to
the Microsoft® Excel® spreadsheet application, be prepared to use largeish numbers.
Separate columns with a
leaves the width on column 1 as default, sets the width of column 2 to 800 and leaves the rest of the columns (if any) as default. |
xlsBorderColor |
The the border color of the entire cell (see jxl.format.Colour ). |
xlsBorderColorLeft |
The the border color of the left edge of the cell (see jxl.format.Colour ). |
xlsBorderColorTop |
The the border color of the top edge of the cell (see jxl.format.Colour ). |
xlsBorderColorRight |
The the border color of the right edge of the cell (see jxl.format.Colour ). |
xlsBorderColorBottom |
The the border color of the bottom edge of the cell (see jxl.format.Colour ). |
xlsBorderLineStyle |
The the border line style of the entire cell (see jxl.format.Colour ). |
xlsBorderLineStyleLeft |
The the border line style of the left edge of the cell (see jxl.format.Colour ). |
xlsBorderLineStyleTop |
The the border line style of the top edge of the cell (see jxl.format.Colour ). |
xlsBorderLineStyleRight |
The the border line style of the right edge of the cell (see jxl.format.Colour ). styles |
xlsBorderLineStyleBottom |
The the border line style of the bottom edge of the cell (see jxl.format.Colour ). styles |
In order to provide different formatting for different kind of cells you can use templates. Templates are defined in the datatable style attribute as seen in the examples in the previous section by using a dot-notation on the xls-attribute names. Attributes without template names are considered global and are applied to all cells.
Templates are used by referring to them in the style attribute of
the UIOutput using the xlsTemplates attribute. If you wish to
cascade several templates, separate them with a
,
.
<h:dataTable style="xlsFontName : Times New Roman; xlsFontColor.foo : blue;
xlsFontColor.bar : red; xlsFontBold.tar : true">
<h:column>
<h:outputText style="xlsTemplates : foo"/>
</h:column>
<h:column>
<h:outputText style="xlsTemplates : bar,tar"/>
</h:column>
</h:dataTable>
results in the first column being Times New Roman, blue and the second column being Times New Roman red bold.
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.