Chapter 19. The Microsoft® Excel® spreadsheet application

19.1. The Microsoft® Excel® spreadsheet application support

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.

19.2. Creating a simple workbook

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!

19.3. Workbooks

Workbooks are the top-level parents of worksheets and stylesheet links.

<e:workbook>

Attributes

type —Defines which export module to be used. The value is a string and can be either "jxl" or "csv". The default is "jxl".
templateURI —A template that should be used as a basis for the workbook. The value is a string (URI).
arrayGrowSize —The amount of memory by which to increase the amount of memory allocated to storing the workbook data. For processeses reading many small workbooks inside a WAS it might be necessary to reduce the default size Default value is 1 megabyte. The value is a number (bytes).
autoFilterDisabled —Should autofiltering be disabled?. The value is a boolean.
cellValidationDisabled —Shoule cell validation be ignored? The value is a boolean.
characterSet —The character set. This is only used when the spreadsheet is read, and has no effect when the spreadsheet is written. The value is a string (character set encoding).
drawingsDisabled —Should drawings be disabled? The value is a boolean.
excelDisplayLanguage —The language in which the generated file will display. The value is a string (two character ISO 3166 country code).
excelRegionalSettings —The regional settings for the generated excel file. The value is a string (two character ISO 3166 country code).
formulaAdjust —Should formulas be adjusted? The value is a boolean.
gcDisabled —Should garbage collection be disabled? The value is a boolean.
ignoreBlanks —Should blanks be ignored? The value is a boolean.
initialFileSize —The initial amount of memory allocated to store the workbook data when reading a worksheet. For processeses reading many small workbooks inside a WAS it might be necessary to reduce the default size Default value is 5 megabytes. The value is a number (bytes).
locale —The locale used by JExcelApi to generate the spreadsheet. Setting this value has no effect on the language or region of the generated excel file. The value is a string.
mergedCellCheckingDisabled —Should merged cell checking be disabled? The value is a boolean.
namesDisabled —Should handling of names be disabled? The value is a boolean.
propertySets —Should any property sets be enabled (such as macros) to be copied along with the workbook? Leaving this feature enabled will result in the JXL process using more memory. The value is a boolean.
rationalization —Should the cell formats be rationalized before writing out the sheet? The value is a boolean. Default is true.
supressWarnings —Should warnings be suppressed?. Due to the change in logging in version 2.4, this will now set the warning behaviour across the JVM (depending on the type of logger used). The value is a boolean.
temporaryFileDuringWriteDirectory —Used in conjunction with the useTemporaryFileDuringWrite setting to set the target directory for the temporary files. This value can be NULL, in which case the normal system default temporary directory is used instead. The value is a string (the directory to which temporary files should be written).
useTemporaryFileDuringWrite —Should a temporary file is used during the generation of the workbook. If not set, the workbook will take place entirely in memory. Setting this flag involves an assessment of the trade-offs between memory usage and performance. The value is a boolean.
workbookProtected —Should the workbook be protected? The value is a boolean.
filename —The filename to use for the download. The value is a string. Please not that if you map the DocumentServlet to some pattern, this file extension must also match.
exportKey —A key under which to store the resulting data in a DocumentData object under the event scope. If used, there is no redirection.

Child elemenents

<e:link/> —Zero or more stylesheet links (see Section 19.14.1, “Stylesheet links” ).
<e:worksheet/> —Zero or more worksheets (see Section 19.4, “Worksheets” ).

Facets

none




         

            <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

19.4. Worksheets

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.

<e:worksheet>

value —An EL-expression to the backing data. The value is a string.
var —The current row iterator variable name that can later be referenced in cell value attributes. The value is a string
name —The name of the worksheet. The valus is a string. Defaults to Sheet# where # is the worksheet index. If the given worksheet name exists, that sheet is selected. This can be used for merging several data sets into a single worksheet, just define the same name for them (using startRow and startCol to make sure that they don't occupy the same space).
startRow —Defines the starting row for the data. The value is a number. Used for placing the data in other places than the upper-left corner (especially useful if having multiple data sets for a single worksheet). The defaults is 0.
startColumn —Defines the starting column for the data. The value is a number. Used for placing the data in other places than the upper-left corner (especially useful if having multiple data sets for a single worksheet). The default is 0.
automaticFormulaCalculation —Should formulas be automatically calculated? The value is a boolean.
bottomMargin —The bottom margin. The value is a number (inches)
copies —The number of copies. The value is a number.
defaultColumnWidth —The default column width. The value is a number (characters * 256).
defaultRowHeight —The default row height. The value is a number (1/20ths of a point).
displayZeroValues —Should zero-values be displayed? The value is a boolean.
fitHeight —The number of pages vertically that this sheet will be printed into. The value is a number.
fitToPages —Should printing be fit to pages? The value is a boolean.
fitWidth —The number of pages widthwise which this sheet should be printed into. The value is a number.
footerMargin —The margin for any page footer. The value is a number (inches).
headerMargin —The margin for any page headers. The value is a number (inches).
hidden —Should the worksheet be hidden? The value is a boolean.
horizontalCentre —Should the worksheet be centered horizontally? The value is a boolean.
horizontalFreeze —The row at which the pane is frozen vertically. The value is a number.
horizontalPrintResolution —The horizontal print resolution. The value is a number.
leftMargin —The left margin. The value is a number (inches).
normalMagnification —The normal magnificaton factor (not zoom or scale factor). The value is a number (percentage).
orientation —The paper orientation for printing this sheet. The value is a string that can be either "landscape" or "portrait".
pageBreakPreviewMagnification —The page break preview magnificaton factor (not zoom or scale factors). the value is a number (percentage).
pageBreakPreviewMode —Show page in preview mode? The value is a boolean.
pageStart —The page number at which to commence printing. The value is a number.
paperSize —The paper size to be used when printing this sheet. The value is a string that can be one of "a4", "a3", "letter", "legal" etc (see jxl.format.PaperSize ).
password —The password for this sheet. The value is a string.
passwordHash —The password hash - used only when copying sheets. The value is a string.
printGridLines —Should grid lines be printed? The value is a boolean.
printHeaders —Should headers be printed? The value is a boolean.
sheetProtected —Should the sheet be protected (read-only)? The value is a boolean.
recalculateFormulasBeforeSave —Should the formulas be re-calculated when the sheet is saved? The value is a boolean. false
rightMargin —The right margin. The value is a number (inches).
scaleFactor —The scale factor for this sheet to be used when printing. The value is a number (percent).
selected —Should the sheet be selected when the workbook opens? The value is a boolean.
showGridLines —Should gridlines be shown? The value is a boolean.
topMargin —The top margin. The value is a number (inches).
verticalCentre —Center verically? The value is a boolean.
verticalFreeze —The row at which the pane is frozen vertically. The value is a number.
verticalPrintResolution —The vertical print resolution. The value is a number.
zoomFactor —T zoom factor. Do not confuse zoom factor (which relates to the on screen view) with scale factor (which refers to the scale factor when printing). The value is a number (percentage.

Child elemenents

<e:printArea/> —Zero or more print area definitions (see Section 19.11, “Print areas and titles” ).
<e:printTitle/> —Zero or more print title definitions (see Section 19.11, “Print areas and titles” ).
<e:headerFooter/> —Zero or more header/footer definitions ((see Section 19.10, “Headers and footers” )).
Zero or more worksheet commands (see Section 19.12, “Worksheet Commands” ).

Facets

header—Contents that will be placed at the top of the data block, above the column headers (if any)
footer—Contents that will be placed at the bottom of the data block, below the column footers (if any)




         

            <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.

19.5. Columns

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.

<e:column>

Attributes

none

Child elemenents

<e:cell/> —Zero or more cells (see Section 19.6, “Cells” ).
<e:formula/> —Zero or more formulas (see Section 19.7, “Formulas” ).
<e:image/> —Zero or more images (see Section 19.8, “Images” ).
<e:hyperLink/> —Zero or more hyperlinks (see Section 19.9, “Hyperlinks” ).

Facets

header —This facet can/will contain one <e:cell> , <e:formula> , <e:image> or <e:hyperLink> that will be used as header for the column.
footer —This facet can/will contain one <e:cell> , <e:formula> , <e:image> or <e:hyperLink> that will be used as footer for the column.




         

            <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

19.6. Cells

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 ???

<e:cell>

Attributes

column —The column where to place the cell. The default is the internal counter. The value is a number. Note that the value is 0-based.
row —The row where to place the cell. The default is the internal counter. The value is number. Note that the value is 0-based.
value —The value to display. Usually an EL-expression referencing the var-attribute of the containing datatable. The value is a string.
comment —A comment to add to the cell. The value is a string.
commentHeight —The height of the comment. The value is a number (in pixels).
commentWidth —A width of the comment. The value is a number (in pixels).

Child elemenents

<e:font/> —Zero or more font definitions (see ??? ).
<e:border/> —Zero or more border definitions (see ??? ).
<e:background/> —Zero or more background definitions (see ??? ).
Zero or more validation conditions (see Section 19.6.1, “Validation” ).

Facets

none




         

            <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

19.6.1. Validation

Validations are nested inside cells, formulas or cell templates. They add constrains for the cell data.

<e:numericValidation>

Attributes

value —The limit (or lower limit where applicable) of the validation. The value is a number.
value2 —The upper limit (where applicable) of the validation. The value is a number.
condition —The validation condition. The value is a string.
- "equal" - requires the cell value to match the one defined in the value-attribute
- "greater_equal" - requires the cell value to be greater than or equal to the value defined in the value-attribute
- "less_equal" - requires the cell value to be less than or equal to the value defined in the value-attribute
- "less_than" - requires the cell value to be less than the value defined in the value-attribute
- "not_equal" - requires the cell value to not match the one defined in the value-attribute
- "between" - requires the cell value to be between the values defined in the value- and value2 attributes
- "not_between" - requires the cell value not to be between the values defined in the value- and value2 attributes

Child elemenents

none

Facets

none




            

               <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.

<e:rangeValidation>

Attributes

startColumn —The starting column of the range of values to validate against. The value is a number.
startRow —The starting row of the range of values to validate against. The value is a number.
endColumn —The ending column of the range of values to validate against. The value is a number.
endRow —The ending row of the range of values to validate against. The value is a number.

Child elemenents

none

Facets

none




            

               <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.

<e:listValidation>

Attributes

none

Child elemenents

Zero or more list validation items.

Facets

none

e:listValidation is a just a container for holding multiple e:listValidationItem tags.

<e:listValidationItem>

Attributes

value —A values to validate against.

Child elemenents

none

Facets

none




            

               <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".

19.6.2. Format masks

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

19.6.2.1. Number masks

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.

19.6.2.2. Date masks

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.

19.7. Formulas

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

19.8. Images

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.

<e:image>

Attributes

startColumn —The starting column of the image. The default is the internal counter. The value is a number. Note that the value is 0-based.
startRow —The starting row of the image. The default is the internal counter. The value is a number. Note that the value is 0-based.
columnSpan —The column span of the image. The default is one resulting in the default width of the image. The value is a float.
rowSpan —The row span of the image. The default is the one resulting in the default height of the image. The value is a float.
URI —The URI to the image. The value is a string.

Child elemenents

none

Facets

none




         

            <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

19.9. Hyperlinks

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

<e:hyperlink>

Attributes

startColumn —The starting column of the hyperlink. The default is the internal counter. The value is a number. Note that the value is 0-based.
startRow —The starting row of the hyperlink. The default is the internal counter. The value is a number. Note that the value is 0-based.
endColumn —The ending column of the hyperlink. The default is the internal counter. The value is a number. Note that the value is 0-based.
endRow —The ending row of the hyperlink. The default is the internal counter. The value is a number. Note that the value is 0-based.
URL —The URL to link. The value is a string.
description —The description of the link. The value is a string.

Child elemenents

none

Facets

none




         

            <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

19.10. Headers and footers

Headers and footers are childrens of worksheets and contain facets which in turn contains a string with commands that are parsed.

<e:header>

Attributes

none

Child elemenents

none

Facets

left —The contents of the left header/footer part.
center —The contents of the center header/footer part.
right —The contents of the right header/footer part.

<e:footer>

Attributes

none

Child elemenents

none

Facets

left —The contents of the left header/footer part.
center —The contents of the center header/footer part.
right —The contents of the right header/footer part.

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>

19.11. Print areas and titles

Print areas and titles childrens of worksheets and worksheet templates and provide... print areas and titles.

<e:printArea>

Attributes

firstColumn —The column of the top-left corner of the area. The parameter is a number. Note that the value is 0-based.
firstRow —The row of the top-left corner of the area. The parameter is a number. Note that the value is 0-based.
lastColumn —The column of the bottom-right corner of the area. The parameter is a number. Note that the value is 0-based.
lastRow —The row of the bottom-right corner of the area. The parameter is a number. Note that the value is 0-based.

Child elemenents

none

Facets

none




         

            <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.

19.12. Worksheet Commands

Worksheet commands are children of workbooks and are usually executed only once.

19.12.1. Grouping

Provides grouping of columns and rows.

<e:groupRows>

Attributes

startRow —The row to start the grouping at. The value is a number. Note that the value is 0-based.
endRow —The row to end the grouping at. The value is a number. Note that the value is 0-based.
collapse —Should the grouping be collapsed initially? The value is a boolean.

Child elements

none

Facets

none

<e:groupColumns>

Attributes

startColumn —The column to start the grouping at. The value is a number. Note that the value is 0-based.
endColumn —The column to end the grouping at. The value is a number. Note that the value is 0-based.
collapse —Should the grouping be collapsed initially? The value is a boolean.

Child elements

none

Facets

none




            

               <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).

19.12.2. Page breaks

Provides page breaks

<e:rowPageBreak>

Attributes

row —The row to break at. The value is a number. Note that the value is 0-based.

Child elements

none

Facets

none




            

               <e:workbook>

                  <e:worksheet>            

                     <e:rowPageBreak row="4"/>

                  </e:worksheet>

               </e:workbook>

breaks page at row 5.

19.12.3. Merging

Provides cell merging

<e:mergeCells>

Attributes

startRow —The row to start the merging from. The value is a number. Note that the value is 0-based.
startColumn —The column to start the merging from. The value is a number. Note that the value is 0-based.
endRow —The row to end the merging at. The value is a number. Note that the value is 0-based.
endColumn —The column to end the merging at. The value is a number. Note that the value is 0-based.

Child elements

none

Facets

none




            

               <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

19.13. Datatable exporter

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.

19.14. Fonts and layout

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 styleattribute. 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.

19.14.1. Stylesheet links

External stylesheets are references with the e:link tag. They are placed as children of the workbook.

<e:link>

Attributes

URL —The URL to the stylesheet

Child elemenents

none

Facets

none




            

               <e:workbook>

                  <e:link URL="/css/excel.css"/>

               </e:workbook>

References a stylesheet that can be found at /css/excel.css

19.14.2. Fonts

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"

19.14.3. Borders

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"

19.14.4. Background

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.

19.14.5. Column settings

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".

19.14.6. Cell settings

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".

19.15. Links and further documentation

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.