关于jxls2.6.0后的版本使用的jexl3学习使用

访问jxls官网：http://jxls.sourceforge.net/

官网中也有例子：https://bitbucket.org/leonate/jxls-demo/src/master/。

按照官网中的顺序。第一个例子居然是自定义函数。而不是if each这样的命令。通过官网给出的例子。笔者这里进行了测试

package com.xkcoding.helloworld.util.jxls;


import org.apache.commons.compress.utils.Lists;
import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;


import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/***
 *  jxls excel模版导出工具类
 *
 */
public class JxlsUtils {
    static {
        //添加自定义指令（可覆盖jxls原指令）
//        XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
//        XlsCommentAreaBuilder.addCommandMapping("comment", CommentCommand.class);
    }

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        // 获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()
                .getExpressionEvaluator();
        // 设置静默模式，不报警告
        // evaluator.getJexlEngine().setSilent(true);
        // 函数强制，自定义功能
        Map<String, Object> functionMap = new HashMap<String, Object>();
        functionMap.put("utils", new JxlsUtils()); // 添加自定义功能
//        evaluator.getJexlEngine().setFunctions(funcs);//jexlEngine使用的setFunctions是jexl2中的方法
        JexlBuilder jb = new JexlBuilder();
        jb.namespaces(functionMap);
        JexlEngine je = jb.create();
        evaluator.setJexlEngine(je);

        // 必须要这个，否者表格函数统计会错乱
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model)
            throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = getTemplate(templatePath);
        if (template != null) {
            exportExcel(new FileInputStream(template), os, model);
        } else {
            throw new Exception("Excel 模板未找到。");
        }
    }

    // 获取jxls模版文件
    public static File getTemplate(String path) {
        File template = new File(path);
        if (template.exists()) {
            return template;
        }
        return null;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

    public static ByteArrayOutputStream fileToBos(File file) throws IOException {
        BufferedInputStream br = new BufferedInputStream(new FileInputStream(file));
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        int ch = 0;
        while ((ch = br.read()) != -1) {
            bos.write(ch);
        }
        return bos;
    }

    public static File getResource(String path) {
        if (path.contains("classpath")) {
            return new File(path.replace("classpath:", getClassPath()));
        }
        return new File(path);
    }

    public static String getClassPath() {
        return Thread.currentThread().getContextClassLoader().getResource("").getPath();
    }

    public static void main(String[] args) throws Exception {
//        String template = "classpath:template.xlsx";
        String output = "E:/事业部品牌推广费用(1).xlsx";
        Map<String, Object> map1 = new HashMap<String, Object>();
        map1.put("cc", "**事业部品牌部");
        map1.put("dd", "推广费用");
        map1.put("ee", "加盟推介会");
        map1.put("ff", 260000);
        map1.put("gg", 206701);
        map1.put("hh", 53000);
        map1.put("ii", 259701);

        Map<String, Object> map2 = new HashMap<String, Object>();
        map2.put("cc", "**事业部品牌部");
        map2.put("dd", "推广费用");
        map2.put("ee", "旗舰店开业推广");
        map2.put("ff", 200000);
        map2.put("gg", "");
        map2.put("hh", 150000);
        map2.put("ii", 150000);

        Map<String, Object> map3 = new HashMap<String, Object>();
        map3.put("cc", "**事业部品牌部");
        map3.put("dd", "推广费用");
        map3.put("ee", "**百店省推广");
        map3.put("ff", 200000);
        map3.put("gg", 70000);
        map3.put("hh", "");
        map3.put("ii", 70000);

        Map<String, Object> map4 = new HashMap<String, Object>();
        map4.put("cc", "**事业部品牌部");
        map4.put("dd", "专业服务费");
        map4.put("ee", "**品牌策略");
        map4.put("ff", 400000);
        map4.put("gg", 400000);
        map4.put("hh", "");
        map4.put("ii", 400000);
        List<Map<String, Object>> data = Lists.newArrayList();
        data.add(map1);
        data.add(map2);
        data.add(map3);
        data.add(map4);

        Map<String, Object> model = new HashMap<String, Object>();
        model.put("dataLs",data);
        model.put("title1","成本中心");
//        InputStream is = JxlsUtils.class.getResourceAsStream(template);
        InputStream is = new FileInputStream(JxlsUtils.getTemplate("C:/Users/85450/Desktop/aa.xlsx"));
        OutputStream os = new FileOutputStream(output);
        JxlsUtils.exportExcel(is, os, model);
        os.close();
    }
}

这里之前的evaluator.getJexlEngine().setFunctions(functionMap);是有问题的，从下载下来的例子中也可以看到此句话是被注释的。因为这里的jexlEngine使用的setFunctions是jexl2中的方法，而2.6.0后开始使用的是jexl3。我用的version是2.10.0

jexl3：        JexlBuilder jb = new JexlBuilder();
        jb.namespaces(functionMap);
        JexlEngine je = jb.create();
        evaluator.setJexlEngine(je);

需要将1位置的代码换为2中代码段 

maven依赖：

    <dependency>
      <groupId>org.jxls</groupId>
      <artifactId>jxls-poi</artifactId>
      <version>2.10.0</version>
    </dependency>