文档 :JXLS -
源码:GitHub - jxlsteam/jxls: Java library for creating Excel reports using Excel templates
基本使用方法
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>2.9.0</version>
</dependency>
public void test() throws ParseException, IOException {
logger.info("Running Formula Copy demo");
List<Org> orgs = Org.generate(3, 3);
try(InputStream is = Demo.class.getResourceAsStream("formula_copy_template.xls")) {
try (OutputStream os = new FileOutputStream("target/formula_copy_output.xls")) {
Context context = new Context();
context.putVar("orgs", orgs);
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
jxlsHelper.setUseFastFormulaProcessor(false);
jxlsHelper.processTemplate(is, os, context);
}
}
}
基本语法
Each-Command (横向:direction="RIGHT")
-
var
is a name of the variable in Jxls context to put each new collection item when iterating -
varIndex is a name of a context variable containing the index to iterate
-
items
is a name of a context variable containing the collection (Iterable<?>) or array to iterate -
area
is a reference to XLS Area used aseach command
body -
direction
is a value ofDirection
enumeration which may have valuesDOWN
orRIGHT
to indicate how to repeat the command body - by rows or by columns. The default value isDOWN
. -
select
is an expression selector to filter out collection items during the iteration -
groupBy
is a property to do the grouping -
groupOrder
indicates ordering for groups (‘desc’ or ‘asc’) -
orderBy
contains the names separated with comma and each with an optional postfix “ ASC” (default) or “ DESC” for the sort order -
cellRefGenerator
is a custom strategy for target cell references creation -
multisheet
is a name of a context variable containing a list of sheet names to output the collection -
lastCell
is a common attribute for any command pointing to the last cell of the command area
If-Command
-
condition
is a conditional expression to test -
ifArea
is a reference to an area to output when this command condition evaluates to true -
elseArea
is a reference to an area to output when this command condition evaluates to false -
lastCell
is a common attribute for any command pointing to the last cell of the command area
eg.
jx:if(condition="employee.payment <= 2000", lastCell="F9", areas=["A9:F9","A18:F18"])
Grid-Command
可以自定义各列单元格格式
Grid-Command has the following attributes
-
headers
- name of a context variable containing a collection of headers (Collection<Object>) -
data
- name of a context variable containing a collection of data (Collection<Collection<Object>>) -
props
- comma separated list of object properties for each grid row (required only if each grid row is an Object) -
formatCells
- comma-separated list of type-format map cells e.g. formatCells=“Double:E1, Date:F1” -
headerArea
- source xls area for headers -
bodyArea
- source xls area for body -
lastCell
is a common attribute for any command pointing to the last cell of the command area
${header}
${cell}
Image-Command
eg.
InputStream imageInputStream = ImageDemo.class.getResourceAsStream("business.png");
byte[] imageBytes = Util.toByteArray(imageInputStream);
context.putVar("image", imageBytes);
jx:image(lastCell="D10" src="image" imageType="PNG")
MergeCells-Command
jx:mergeCells(
lastCell="Merge cell ranges"
[, cols="Number of columns combined"]
[, rows="Number of rows combined"]
[, minCols="Minimum number of columns to merge"]
[, minRows="Minimum number of rows to merge"]
)
其他
Multiple sheets
在jx:each
命令中添加属性 multisheet
eg.
jx:each(items="departments", var="dep", multisheet="sheetnames", lastCell="D4")
Excel Formulas
-
公式默认值
jx:params(defaultValue="1")
-
拷贝公式
jx:params(formulaStrategy="BY_COLUMN")
-
Joined cell (同一行对应多个模板行时)
$[SUM(U_(D9,D18))]
Custom Function
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
Map<String, Object> functionMap = new HashMap<>();
functionMap.put("fn", new MyCustomFunctions());
JexlEngine customJexlEngine = new JexlBuilder().namespaces(functionMap).create();
evaluator.setJexlEngine(customJexlEngine);
jxlsHelper.processTemplate(context, transformer);
public static class MyCustomFunctions {
public Object ifelse(boolean b, Object o1, Object o2) {
return b ? o1 : o2;
}
public boolean contains(Collection o1, Object o2) {
return o1.contains(o2);
}
public String concat(Object o1, Object o2) {
return String.valueOf(o1).concat(String.valueOf(o2));
}
}
${fn:ifelse(fn:contains(data.ext,"prevQyoy"),"上季度增速","上年度增速")}
UpdateCell-Command
见官方文档
Custom Commands
见官方文档
示例
Report template
Excel output