SeamFramework.orgCommunity Documentation

Chapter 19. the Microsoft® Excel® spreadsheet application

19.1. the Microsoft® Excel® spreadsheet application support
19.2. Creating a simple workbook
19.3. Workbooks
19.4. Worksheets
19.5. Columns
19.6. Cells
19.6.1. Fonts
19.6.2. Backgrounds
19.6.3. Borders
19.6.4. Validation
19.6.5. Format masks
19.7. Formulas
19.8. Images
19.9. Hyperlinks
19.10. Headers and footers
19.11. Print areas and titles
19.12. Worksheet Commands
19.12.1. Grouping
19.12.2. Page breaks
19.12.3. Merging
19.13. Templates
19.13.1. Cell templates
19.13.2. Worksheet setting templates
19.14. Datatable exporter
19.14.1. CSS
19.14.2. Templates
19.15. Links and further 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">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;</span><span class="xml_tag_name">excel:excelFactory</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;</span><span class="xml_tag_name">property</span><span class="xml_plain">&nbsp;</span><span class="xml_attribute_name">name</span><span class="xml_tag_symbols">=</span><span class="xml_attribute_value">&quot;implementations&quot;</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;</span><span class="xml_tag_name">key</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain">myExcelExporter</span><span class="xml_tag_symbols">&lt;/</span><span class="xml_tag_name">key</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;</span><span class="xml_tag_name">value</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain">my.excel.exporter.ExcelExport</span><span class="xml_tag_symbols">&lt;/</span><span class="xml_tag_name">value</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;/</span><span class="xml_tag_name">property</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;/</span><span class="xml_tag_name">excel:excelFactory</span><span class="xml_tag_symbols">&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;xmlns:excel=&quot;http://jboss.com/products/seam/excel&quot;</span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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.

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

Child elemenents

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

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.

  • templates —The comma-separated list of worksheetTemplates to cascade on. The value is a string (see Section 19.13.1, “Cell templates” ).

  • 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

Facets

  • none



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

<e:column>

Attributes

  • autoSize —Should the column be autosized? The value is a boolean.

  • hidden —Should the column be hidden? The value is a boolean.

  • width —The width of the column. The valus ia number (characters multiplied by 256)

Child elemenents

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.



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

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

  • templates —A comma-separated list of cascading, predefined templates to apply before the own formattings (see Section 19.13.1, “Cell templates” ).

  • forceType —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.

  • alignment —The alignment of the cell data. The value is a string that can be one of "centre", "fill", "general", "justify", "left" or "right".

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

  • indentation —The indentation of the cell. The value is a number (in pixels)

  • locked —Should the cell be locked? For this to have any effect, the sheet containing cells with this format must also be locked. The value is a boolean.

  • mask —A format mask (see Section 19.6.5, “Format masks” ).

  • orientation —The orientation of the cell data. The value is a string that can be one of "horizontal", "minus_45", "minus_90", "plus_45", "plus_90", "stacked" or "vertical".

  • shrinkToFit —Should the cell data be shrunk to fit? The value is a boolean.

  • verticalAlignment —The vertical aligment of the cell data. The value is a string that can be one of "bottom", "centre", "justify" or "top").

  • wrap —Should the data be wrapped so that it fits within the cell boundaries? The value is a boolean.

Child elemenents

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

Fonts are nested inside cells, formulas, hyperlinks or cell templates. They determine the typeface of the cell data

<e:font>

Attributes

  • fontName —The font name. The value is a string. Should be used with care, since the used font must be recognized by the Microsoft ® Excel® spreadsheet application

  • color —The color of the background. The value is a string that can be one of "blue", "red" etc (see jxl.format.Colour ).

  • pointSize —The point size of the font. The value is a number.

  • bold —Should the font be bold? The value is a boolean.

  • italic —Should the font be italic? The value is a boolean.

  • struckOut —Should the font be struck out? The value is a boolean.

  • scriptStyle —The script style of the font. The value is a string that can be one of "normal_script", "subscript" or "superscript".

  • underlineStyle —The underline style of the font. The value is a string that can be one of "double", "double_accounting", "no_underline", "single" or "single_accounting".

Child elemenents

  • none

Facets

  • none



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

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

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)

<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 number. Note that the value is 0-based.

  • rowSpan —The row span of the image. The default is the one resulting in the default height of the image. The value is a number. Note that the value is 0-based.

  • 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

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

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.

<e:headerFooter>

Attributes

  • type —The type of the header or footer. The value is string that can be either "header" or "footer".

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 <e:headerFooterCommands> is just a nesting container in order to support multiple header/footer commands.

<e:headerFooterCommands>

Attributes

  • none

Child elemenents

  • Zero or more header/footer commands.

Facets

  • none

Header/footer commands adds actual commands to the header or footer in the location where the containing facet points.

<e:headerFooterCommand>

Attributes

  • command —The command to execute. The value is a string

    • "append" - appends the text in the parameter
    • "date" - appends the current date
    • "page_number" - appends the page number
    • "time" - appends the time
    • "total_pages" - appends the total page count
    • "workbook_name" - appends the workbook name
    • "worksheet_name" - appends the worksheet name
    • "toggle_bold" - toggles bold font
    • "toggle_italics" - toggles italics
    • "toggle_double_underline" - toggles double underlining
    • "toggle_outline" - toggles outline
    • "toggle_shadow" - toggles shadow
    • "toggle_strikethrough" - toggles strikethrough
    • "toggle_subscript" - toggles subscript
    • "toggle_superscript" - toggles superscript
    • "toggle_underline" - toggles underline
    • "font_name" - sets the font name given in the parameter
    • "font_size" - sets the font size given in the parameter

  • parameter —The parameter for some commands. Used by the font_name, font_size and append commands. The font_name parameter is a string that represents the requested font name, the font_size parameter is a number that represents the requested font size and the append parameter is a string of text to be appended to the header/footer.

Child elemenents

  • none

Facets

  • none



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

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

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

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

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

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.

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 , and use * for not setting a value.



                           
                              <h:dataTable style="xlsColumnWidths : *,800">
                                 ...
                              </h:dataTable>
                           
                        

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

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.