Getting started with the Excel Style

From Wiki

Introduction

The Excel Style has been developed in order to facilitate the user its usage. For this reason, all the templates that are “standard” are set as default for the entire style. This allows the user to simply change the Style property of a page in the Layout Tab of the Properties View in order to get an Excel spreadsheet from the page. You can see the result of this operation looking at the following image.

How to use the Excel Style

It’s also possible to customize a bit the Excel spreadsheet layout. Let’s see which are the options you have at your disposal:

• Page template

1. Block Padding. It permits to define a gap of cells between the units placed into the sheet. The default value is 1. The gap is both vertical and horizontal.

• Frame template. By default, the Excel frame is associated to each unit placed into the page. This allows you to set the following parameters:

1. Show Title. It defines the rendition of the Block title (unit or cell). The default value is “true”.
2. Title Font Weight. It defines the Block title (unit or cell) font weight. The possible values are normal and bold (default).
3. Title Background. It defines the Block title (unit or cell) background color.
4. Title Color. It defines the Block title (unit or cell) text color.
5. Title Borders. It defines the Block title (unit or cell) borders to render The value is a space separated combination of the following keywords: top, left, right, bottom.
6. Title Borders Style. It defines the Block title (unit or cell) borders style. The possible values are THIN, MEDIUM, DOUBLE, DASHED.
7. Title Border Color. It defines the Block title (unit or cell) borders color.

• Unit template. All the default unit templates have the same layout parameters that are the following:

1. Show Header. It defines the rendition of the unit header. The default value is “true”.
2. Header Font Weight. It defines the header font weight. The possible values are normal and bold (default).
3. Header Background. It defines the header background color.
4. Header Color. It defines the header text color.
5. Header Borders. It defines the header borders to render The value is a space separated combination of the following keywords: top, left, right, bottom.
6. Header Borders Style. It defines the header borders style. The possible values are THIN, MEDIUM, DOUBLE, DASHED.
7. Header Border Color. It defines the header borders color.
8. Show Break Line. It defines if the break lines between rows must be shown or not.

• Attribute template. The default attribute template has the following layout parameters that allows to give a style to each cell of the Excel file

1. Cell Font Weight. It defines the cell font weight. The possible values are normal and bold (default).
2. Cell Background. It defines the cell background color.
3. Text Color. It defines the cell text color.
4. Cell Borders. It defines the cell borders to render. The value is a space separated combination of the following keywords: top, left, right, bottom.
5. Cell Borders Style. It defines the cell borders style. The possible values are THIN, MEDIUM, DOUBLE, DASHED.
6. Cell Border Color. It defines the cell borders color.
7. Data Format. It specifies the data format used to present data in the cell. If not set the format used is the one specified in the Localization panel for the Data Patterns.

You can create different sheets in the same Excel file. This is done using subpages. Each subpage is a sheet and the subpage name represents the sheet name. As an example you can split the two list in different sheet modeling the page like the following (download result). Note that all the units must be placed in the subpages and not outside them, otherwise their content will be skipped.

Finally you can set a specific name for the Excel file, which is different from the name of the modeled page. To do this you have to use the “Excel File Name” custom location of the Excel page template and place a Script unit in it. The Script unit should have a Groovy script like the following

	return “CUSTOM EXCEL NAME”

The result is that the dialog asking to save the Excel file will propose as name “CUSTOM EXCEL NAME”.

How to create a custom template

The Excel Style has the goal to produce some XML code in the JSP page. This XML code is processed by Apache POI in order to get the resulting Excel file. If you want to create custom templates to improve this style you have to know the structure of this XML. This is a sample of the XML code.

 <Excel blockPadding="1">
   <FileName>foobar.xls</FileName>
   <Sheet name="Sheet 1"> 
     <Block position="0_0"> 
       <Title style="font-weight:bold">Product</Title>
       <Header>
          <Cell>name</Cell>
       </Header>
       <Row>	
         <Cell id="att2" type="string" style="font-weight:bold">Wilderness</Cell>
       </Row>
     </Block>
   </Sheet>  
 </Excel>

Suppose that you want to include in the Excel spreadsheet custom messages using the Multimessage unit. This is not possible right now since the Excel Style does not manage this type of unit. Let’s see how to write a custom template to do this. First of all have a look to the previous XML code. The new template has to produce only a part of it. In particolar, this is the portion of the code to consider:

     <Block position="0_0"> 
       <Title style="font-weight:bold">Product</Title>
       <Header>
          <Cell>name</Cell>
       </Header>
       <Row>	
         <Cell id="att2" type="string"  style="font-weight:bold">Wilderness</Cell>
       </Row>
     </Block>

The MultiMessage unit allows to print different messages, but each message does not have an “header”. So you just ignore that part. The template has to produce a <Row> element and a <Cell> element for each element. You can start creating the Excel template for the MultiMessage unit copying the “Localized” template of the WRDefault style.

#?delimiters [%, %], [%=, %]
[% setXMLOutput() %]

<Block position="[%= blockPosition%]"> 
  <c:if test="${not(empty <wr:UnitId/>) and (<wr:UnitId/>.dataSize gt 0)}">
    <wr:Frame>
	<c:forEach var="current" varStatus="status" items="${<wr:UnitId/>.data}">
	       <c:set var="index" value="${status.index}"/>

	       <Row>	
	         <Cell><bean:message key="${current}"/></Cell>
	       </Row>

        </c:forEach>
     </wr:Frame>
  </c:if>
</Block>

The previous code works perfectly but does not allow you to give a particular style to the message's cells. In order to have this chance we have to add a "style" attribute to the <Cell> element. The value of this attribute will be taken from different layout parameters. Just to make an example, let's add the possibility to set the font weight of the messages.

#?delimiters [%, %], [%=, %]

<wr:LayoutParameter label="Cell Font Weight" name="cellfont-weight" type="enum" values="normal|bold" default="normal">
Defines the rendering of a cell.
Allowed values are:
- normal (default)
- bold
</wr:LayoutParameter>

[% 

  setXMLOutput() 
  def fontWeight = params["cellfont-weight"]

%]

<Block position="[%= blockPosition%]"> 
  <c:if test="${not(empty <wr:UnitId/>) and (<wr:UnitId/>.dataSize gt 0)}">
    <wr:Frame>
	<c:forEach var="current" varStatus="status" items="${<wr:UnitId/>.data}">
	       <c:set var="index" value="${status.index}"/>

	       <Row>	
	         <Cell style="font-weight:[%= fontWeight %]"><bean:message key="${current}"/></Cell>
	       </Row>

        </c:forEach>
     </wr:Frame>
  </c:if>
</Block>

N.B. There are limitations to the value that the "style" attribute supports. All the things that you can do are implemented in the standard template of the Excel Style. To get a list of the possible values look at the previous section. Mainly you can set background-colors, borders, font weight and font color. You can see the complete list and documentation of the available style attributes in the WebRatio User Guide (Reference -> Default Style Reference -> Excel).

Moreover it's possible to associate to a cell a particular "type" in order to have the Excel cell match that type. To do this you have to add to the <Cell> element the "type" attribute. Let's see how to change the template.

#?delimiters [%, %], [%=, %]

<wr:LayoutParameter label="Cell Font Weight" name="cellfont-weight" type="enum" values="normal|bold" default="normal">
Defines the rendering of a cell.
Allowed values are:
- normal (default)
- bold
</wr:LayoutParameter>

[% 

  setXMLOutput() 
  def fontWeight = params["cellfont-weight"]

%]

<Block position="[%= blockPosition%]"> 
  <c:if test="${not(empty <wr:UnitId/>) and (<wr:UnitId/>.dataSize gt 0)}">
    <wr:Frame>
	<c:forEach var="current" varStatus="status" items="${<wr:UnitId/>.data}">
	       <c:set var="index" value="${status.index}"/>

	       <Row>	
	         <Cell type="string" style="font-weight:[%= fontWeight %]"><bean:message key="${current}"/></Cell>
	       </Row>

        </c:forEach>
     </wr:Frame>
  </c:if>
</Block>

Using Formulas

<Cell type="formula">=(A1 + B2)/B3</Cell>

where (A1 + B2)/B3 is the Excel formula. As you can see you have to know the cell codes necessary in order to retrieve the cells involved in the formula.

Suppose you want to create an Excel file showing the Acme product list with a column specifing the price of the product with the 20% of discount. You can model a page like the image on the right, in which the MultiDataUnit shows all the Product information. Then you have to write a custom Excel template for the MultiData Unit, to add another column with the discounted price. This is the resulting template:

[%
import org.apache.commons.lang.StringUtils

setXMLOutput()

def writeStyle(params, prefix){
   def style = params.entrySet().findAll{it.key.startsWith(prefix) && 
!StringUtils.isBlank(it.value)}.collect{StringUtils.uncapitalize(it.key - prefix) + ":" + it.value}.join(";")    
   if(!StringUtils.isBlank(style)){%] style="[%= style%]"[%}
}

Commons Problems

Empty Excel File

One of the most common problems when you are working with the Excel Style is to get an empty Excel file at runtime. This problem can be related to different reasons:

1. The Content Units in the Excel page do not have content. To verify this, just change the style of the page from the "Excel" to the "WRDefault" and check if the problem persists. If so, then you have to debug the Web application in order to understand why the units are not working as expected (see..... for more details).
2. The generated JSP page contains HTML code.To check this point, right click on the modeled page and choose the Open Generated Code -> Layout Page command. The JSP page has to contain only XML code. To solve this problem:
   1. Check if there is an element in the Grid that has a Frame Layout associated which is different from the default "Excel/Excel" one. If this Frame Layout prints HTML code, change it to a valid one.
      
   2. Check if the page has a Master Page associated. If so, the HTML code belongs to the Master Page. In order to solve the problem, flag the property "Ignore Master Page" in the Properties View of the page.