You sometimes have to generate a nicely presented report in excel format.
For such reports, the csv format is quite limited. From version 5.5,
OpenXava offers the possibility to generate real xls files.
Setup
1- If you are using an OpenXava version previous to v5.6 modify the
servlets.xml in /web/WEB-INF/ folder of your application:
<servlet>
<servlet-name>generateCustomXLSReport</servlet-name>
<servlet-class>org.openxava.web.servlets.ReportXLSServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>generateCustomXLSReport</servlet-name>
<url-pattern>/xava/report.xls</url-pattern>
</servlet-mapping>
This will forward any URI ending by /xava/reports.xls to the
ReportXLSServlet.
2- Add the action in your controllers.xml file
<controller name="MyController">
<extends controller="TypicalRealExcel"/>
<action name="scenario" mode="detail" by-default="if-possible" class="org.openxava.actions.CreateXlsFileAction" />
</controller>
Basic template
for the action
package org.openxava.actions;
import java.util.*;
import org.openxava.actions.*;
import org.openxava.util.*;
import org.openxava.util.jxls.*;
import org.openxava.web.servlets.*;
public class CreateXlsFileAction extends ViewBaseAction
implements IForwardAction, JxlsConstants { // 1
private String forwardURI = null;
public void execute() throws Exception {
try {
JxlsWorkbook scenario = createScenario();
getRequest().getSession().setAttribute(ReportXLSServlet.SESSION_XLS_REPORT, scenario); // 2
setForwardURI("/xava/report.xls?time=" + System.currentTimeMillis()); // 3
} catch (Exception e) {
addError(e.getMessage());
}
}
private JxlsWorkbook createScenario() throws Exception {
JxlsWorkbook scenarioWB = new JxlsWorkbook("Scenario"); // 4
JxlsSheet scenario = scenarioWB.addSheet("Scenario"); // 5
scenario.setValue(1, 1, "Date:"); // 6
scenario.setValue(2, 1, new Date()); // 7
scenario.setValue(1, 2, "Value:");
scenario.setValue(2, 2, 3.1415); // 8
return scenarioWB;
}
public String getForwardURI() {
return forwardURI;
}
public boolean inNewWindow() {
if (forwardURI == null) return false;
return true;
}
public void setForwardURI(String forwardURI) {
this.forwardURI = forwardURI;
}
}
1- IForwardAction will open a new window with the xls file, JxlsConstants
will allow easy use of styles for cell later
2- You have to store your JxlsWorkbook in the Servlet session object for
the forward to work
3- Forward to /xava/report.xls with a time to ensure the file is recreated
every time you click the action
4- Create a JxlsWorkbook: the name parameter will be the file name when
the xls window will pop-up
5- Add a sheet in the workbook
6- Set the cell at column 1, row 1 (in Excel A1) to the text value Date.
Beware, columns and rows start at 1 to mimic the numbering of Excel.
7- Set the cell at column 2, row 1 (in Excel B1) to the current date.
8- Set the cell at column 2, row 2 (in Excel B2) to a float value of
3.1415.
Resulting
xls file
Date:
|
2016-04-27
|
Value:
|
3.14
|
The date has the default non ambiguous date format and the value appears
with only 2 digits.
These formats can easily be changed for the whole workbook or an
individual cell.
Formating the file
You can define the way number and dates will appear for the whole
workbook, for a given column of for an individual cell.
Workbook
formats
You can define the format at the JxlsWorkbook level:
private JxlsWorkbook createScenario() throws Exception {
JxlsWorkbook scenarioWB = new JxlsWorkbook("Scenario");
scenarioWB.setFloatFormat("### ### ##0.0000"); // 1
scenarioWB.setDateFormat("dd/MM/yyyy"); // 2
JxlsSheet scenario = scenarioWB.addSheet("Scenario");
scenario.setValue(1, 1, "Date");
scenario.setValue(2, 1, new Date());
scenario.setValue(1, 2, "Value");
scenario.setValue(2, 2, 3.1415);
return scenarioWB;
}
Resulting
xls file
Date:
|
27/04/2016
|
Value:
|
3.1415
|
Cell formats
Each cell can be formated using reusable styles.
private JxlsWorkbook createScenario() throws Exception {
JxlsWorkbook scenarioWB = new JxlsWorkbook("Scenario");
JxlsStyle boldRStyle = scenarioWB.addStyle(TEXT).setBold().setAlign(RIGHT);
JxlsStyle pctStyle = scenarioWB.addStyle("0.0%").setAlign(RIGHT);
JxlsStyle f1Style = scenarioWB.addStyle("0.0").setAlign(RIGHT);
JxlsSheet scenario = scenarioWB.addSheet("Scenario");
scenario.setValue(1, 1, "Date:", boldRStyle);
scenario.setValue(2, 1, new Date());
scenario.setValue(1, 2, "Value:", boldRStyle);
scenario.setValue(2, 2, 3.14, f1Style);
scenario.setValue(3, 2, 0.123, pctStyle);
return scenarioWB;
}
Resulting
xls file
Date:
|
2016-04-27
|
|
Value:
|
3.1
|
12.3%
|
Column
formats
You can set up column with and style for every sheet.
private JxlsWorkbook createScenario() throws Exception {
JxlsWorkbook scenarioWB = new JxlsWorkbook("Scenario");
JxlsStyle boldRStyle = scenarioWB.addStyle(TEXT).setBold().setAlign(RIGHT);
JxlsStyle f1Style = scenarioWB.addStyle("0.0");
JxlsStyle f2Style = scenarioWB.addStyle("0.00");
JxlsSheet scenario = scenarioWB.addSheet("Scenario");
scenario.setColumnStyles(1, boldRStyle, f1Style); // 1
scenario.setColumnWidth(1, AUTO_SIZE, 12); // 2
scenario.setValue(1, 1, "Value:");
scenario.setValue(2, 1, 2.54);
scenario.setValue(1, 2, "Value:");
scenario.setValue(2, 2, 3.14);
scenario.setValue(1, 3, "Value:");
scenario.setValue(2, 3, 3.1415, f2Style); // 3
return scenarioWB;
}
1- Set up the stylefor each column, starting with column 1. Column 1 cells
will have boldRStyle and column 2 will have f1Style.
2- Set up the width for each column, starting with column 1. Column 1
cells will auto size and column 2 will be 12 character wide.
3- You can overide the column setup at the cell level.
Resulting
xls file
Value:
|
2.5
|
Value:
|
3.1
|
Value:
|
3.14
|
Creating Styles
Type of styles
Styles are created through the use of workbook.addStyle(type) or
workbook.addStyle(format).
Styles can also be named workbook.addStyle(name, type)for easy retrieval
later in a sheet: sheet.getWorkbook().getStyle(name).
Style type can be TEXT, INT, FLOAT and DATE. INT and FLOAT will store
number values in Excel. When using addStyle(format), format represents a
formating in Excel and will result in Excel trying to store a number.
JxlsStyle pctStyle = scenarioWB.addStyle("0.0%"); // 1
JxlsStyle textStyle = scenarioWB.addStyle(TEXT); // 2
JxlsStyle intStyle = scenarioWB.addStyle(INT); // 3
JxlsStyle floatStyle = scenarioWB.addStyle(FLOAT); // 4
1- Excel will store a number format as a one digit percentage.
2- Excel will store a text. Even if a number is put in the cell having
this format, the number will be stored as text.
3- Excel will store a number, using the format returned by
workbook.getDefaultIntegerFormat(). You can set the format through
workbook.setDefaultIntegerFormat(format).
3- Excel will store a number, using the format returned by
workbook.getDefaultFloatFormat().
You can set the format through
workbook.setDefaultFloatFormat(format).
Setting
properties for styles
Each property setting returns the style, making the settings chainable.
Styles can be cloned and new settings added to the clone.
JxlsStyle labelCenterStyle = scenarioWB.addStyle(TEXT).setAlign(CENTER).setBold(); // 1
JxlsStyle uLabelStyle = scenarioWB.addStyle(TEXT).setBorder(BOTTOM, BORDER_THIN); // 2
JxlsStyle labelRightStyle = scenarioWB.addStyle(TEXT).setAlign(RIGHT).setBorder(BOTTOM, BORDER_THIN); // 3
JxlsStyle blueLabelStyle = scenarioWB.addStyle(TEXT).setTextColor(BLUE); // 4
JxlsStyle floatStyle = scenarioWB.addStyle(FLOAT).setAlign(RIGHT); // 5
JxlsStyle floatGreenStyle = scenarioWB.addClonedStyle(floatStyle).setCellColor(LIGHT_GREEN); // 6
1- Value will be stored as text and the value will show bold aligned in
the center of the cell.
2- Value will be stored as text and the cell will have a thin border at
the bottom and the value will be left aligned.
3- Value will be stored as text and the cell will have a thin border at
the bottom and the value will be right aligned.
4- Value will be stored as text and the value will be left aligned and
appear in blue.
5- Value will be stored as a number and the value will be right aligned.
6- Since we are starting from the previous style, value will be stored as
a number, will be right aligned and the cell background will appear light
green.
Available
properties for styles
setFontName(String fontName)
setFontSize(int fontSize)
setFormat(String format)
setBold()
setBold(boolean isBold)
setWrap(boolean wraps)
setAlign(short align)
setBorders(short topBorderStyle, short bottomBorderStyle, short leftBorderStyle, short rightBorderStyle)
setBorder(short place, short borderStyle)
setTextColor(short textColor)
setCellColor(short bgColor)
setBorderColor(short borderColor)Where
Some of the possible values for function parameters are defined in
JxlsConstants interface
- align is one of LEFT, RIGHT, CENTER
- place is one of TOP, BOTTOM, LEFT, RIGHT, TOP_BOTTOM, RIGHT_LEFT or
ALL
- *BorderStyle is one of BORDER_NONE, BORDER_THIN, BORDER_THICK
Working with formulas
Reference
systems
JxlsWorkbook support formulas and as multiple way of writing them.
private JxlsWorkbook createScenario() throws Exception {
JxlsWorkbook scenarioWB = new JxlsWorkbook("Scenario");
JxlsSheet scenario = scenarioWB.addSheet("Scenario");
scenario.setValue(1, 1, "Pi:");
scenario.setFormula(2, 1, "=PI()"); // 1
scenario.setValue(1, 2, "Pi*2:");
scenario.setValue(2, 2, "=A2*2"); // 2
scenario.setValue(3, 2, "=R1C2*2"); // 3
scenario.setValue(4, 2, "=$R1$C2*2"); // 4
return scenarioWB;
}
1- You can use most of the function of Excel
2- You can use the basic A1 system, such as A1, $A1, A$1 or $A$1
3- Or the Row Column system
4- JxlsWorkbook introduces absolute values for R1C1 type annotation, you
can use
For 3 and 4, OpenXava will transform the R1C1 into A1 and $R1$C1 into $A$1
before writing the Excel file.
Example
of formula
private JxlsWorkbook createScenario() throws Exception {
JxlsWorkbook scenarioWB = new JxlsWorkbook("Scenario");
JxlsSheet scenario = scenarioWB.addSheet("Scenario");
int firstRow = 1;
int sumRow = 10;
for (int i=firstRow; i<sumRow; i++) {
scenario.setValue(1, i, i);
scenario.setFormula(2, i, "=R" + i + "C1*2");
}
scenario.setFormula(1, sumRow, "=SUM(R" + firstRow + "C1:R" + (sumRow-1) + "C1)");
scenario.setFormula(2, sumRow, "=SUM(R" + firstRow + "C2:R" + (sumRow-1) + "C2)");
return scenarioWB;
}
Using the R1C1 system, it is very easy to create formula.
Behind the scene
OpenXava uses a small set of files (in
org.openxava.util.jxls package) to
create a JxlsWorkbook (a place holder of data behaving like an Excel
workbook). This JxlsWorkbook is then transformed into an
Apache
POI HSSFWorkbook when the user requests the writing of the workbook
to a File or to an HttpServletResponse.