WebSheetDataAnnotation

Use Case : Nested Collections

Tiefaces supports Apache JEXL as an expression language in the excel template.

For example, if you want to input/output employee name in a cell, you can put ${employee.name} in the cell (assuming the employee object is avaliable in the context). As you can see, the definition is JEXL expression warpped with ${ and }.

Tiefaces also use special commands in the cell comment to support enhanced features:

  • Form command (tie:form): define form format
  • Fach command (tie:each): populate recusive data
  • Widget: difine widget name and attributes
Demo

In this example, one company contain multiple departments while each department contain multiple employee.

Note: the "Sub total" and "Total" are excel formula. After collection data populated, they have been changed.

Excel Template

ABCDEFG
1
2Department:${department.name}
3DIRECTOR:${department.chief.name}
4HEADCOUNT:0
5URL:${department.link}
6
7EmployeeBirthdaySexWorking TimePaymentBonusTotal
8${employee.name}${employee.birthDate}${employee.sex}${employee.worktime}${employee.payment}${employee.bonus}
9SUB TOTALS0
10Total

Populated Web Sheet (Editable)

ABCDEFG
1
2Department:
3DIRECTOR:
4HEADCOUNT:5
5URL:
6
7EmployeeBirthdaySexWorking TimePaymentBonusTotal
8
  • Male
  • Female
1725
9
  • Male
  • Female
2875
10
  • Male
  • Female
2500
11
  • Male
  • Female
1955
12
  • Male
  • Female
3360
13SUB TOTALS1080012415
14Department:
15DIRECTOR:
16HEADCOUNT:4
17URL:
18
19EmployeeBirthdaySexWorking TimePaymentBonusTotal
20
  • Male
  • Female
1680
21
  • Male
  • Female
2310
22
  • Male
  • Female
2070
23
  • Male
  • Female
2185
24SUB TOTALS72008245
25Department:
26DIRECTOR:
27HEADCOUNT:4
28URL:
29
30EmployeeBirthdaySexWorking TimePaymentBonusTotal
31
  • Male
  • Female
2880
32
  • Male
  • Female
2530
33
  • Male
  • Female
2860
34
  • Male
  • Female
2687.5
35SUB TOTALS935010957.5
36Total31617.5
Source code
<p>
    In this example, one company contain multiple departments while each department contain multiple employee.    
    </p>
    <p>
    Note: the "Sub total" and "Total" are excel formula. After collection data populated, they have been changed. 
    </p>
<h:form id="form1">
<h3>Excel Template</h3>        
    <tie:websheet id="sheet1" webSheetBean="#{webSheetDataAnnotation_Config}"
        maxRowsPerPage="30" readOnly="true">
    </tie:websheet>                
        
    <p:commandButton id="cmdExport1" value="Download the Excel Template"
        ajax="false" actionListener="#{webSheetDataAnnotation_Config.doExport()}">
        <p:fileDownload value="#{webSheetDataAnnotation_Config.exportFile}" />
    </p:commandButton>
</h:form>

<h:form id="form2">
    <h3>Populated Web Sheet (Editable)</h3>        
    <p:messages id="messages" showDetail="true" autoUpdate="true" closable="true" />
        
    <tie:websheet id="sheet2" webSheetBean="#{webSheetDataAnnotation}"
        maxRowsPerPage="100" >
    </tie:websheet>
    
    <p:commandButton id="cmdExport2" value="Download the Excel Sheet"
        ajax="false" actionListener="#{webSheetDataAnnotation.doExport()}">
        <p:fileDownload value="#{webSheetDataAnnotation.exportFile}" />
    </p:commandButton>
    
</h:form>