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

文档 :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

  • 3
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
好的,我可以回答您关于jxls合并单元格的问题。jxls是一个基于Java的开源Excel操作工具,可以通过Excel模板来实现数据的导出和导入等功能。下面是一个简单的jxls合并单元格示例: 1. 创建Excel模板 在Excel模板中,需要使用jxls提供的标记语言来标记出需要合并的单元格。例如,下面是一个包含合并单元格的模板示例: ``` <html xmlns:jxl="http://jxls.sf.net/1.0/mini"> <body> <table> <tr> <td jxl:merge="0,1,0,2">姓名</td> <td>年龄</td> <td>性别</td> </tr> <tr> <td></td> <td></td> <td></td> </tr> <jxl:each items="users" var="user"> <tr> <td>${user.name}</td> <td>${user.age}</td> <td>${user.gender}</td> </tr> </jxl:each> </table> </body> </html> ``` 在上面的模板中,我们使用jxl:merge标记来标记需要合并的单元格,其中0,1,0,2表示需要合并从第0行到第1行、从第0列到第2列的单元格。 2. 编写导出代码 在Java代码中,可以使用jxls提供的API来进行导出操作。例如: ``` // 加载Excel模板 InputStream is = new FileInputStream("template.xls"); Workbook workbook = WorkbookFactory.create(is); // 创建Excel上下文对象 Map<String, Object> context = new HashMap<>(); context.put("users", getUserList()); // 执行模板引擎,生成Excel文件 JxlsHelper.getInstance().processTemplate(context, workbook.getSheetAt(0)); // 保存Excel文件 OutputStream os = new FileOutputStream("output.xls"); workbook.write(os); os.close(); ``` 在上面的代码中,我们首先加载Excel模板,然后创建Excel上下文对象,并将需要合并单元格的数据添加到上下文中。最后,使用JxlsHelper的processTemplate方法执行模板引擎,生成Excel文件,并保存到本地。 3. 导出Excel文件 运行上面的代码后,会在项目根目录下生成一个名为output.xls的Excel文件,其中包含了我们导出的数据和合并的单元格。 以上就是一个简单的jxls合并单元格示例,希望能对您有所帮助。如果您有其他问题,可以继续咨询我。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值