Jxls2 合并单元格 (横向循环、自定义函数)

booth.chen

已于 2024-01-05 15:23:14 修改

阅读量8k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： Java 文章标签： excel导出 jxls2

于 2020-11-10 14:09:16 首次发布

本文链接：https://blog.youkuaiyun.com/CHBOS/article/details/109597546

Java 专栏收录该内容

11 篇文章

订阅专栏

JXLS是一款基于模板的Java报表工具，支持Excel文件处理。它能够利用Excel模板创建报表，并提供丰富的指令集来操作数据。该工具支持遍历、条件判断、图片插入等功能，适用于批量数据处理场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文档 :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 as each command body
direction is a value of Direction enumeration which may have values DOWN or RIGHT to indicate how to repeat the command body - by rows or by columns. The default value is DOWN.
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