访问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;
public class JxlsUtils {
static {
//添加自定义指令（可覆盖jxls原指令）
// XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
// XlsCommentAreaBuilder.addCommandMapping("comment", CommentCommand.class);
}
public static void exportExcel(InputStream is, OutputStream os, Map 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 functionMap = new HashMap();
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 model)
throws FileNotFoundException, IOException {
exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
}
public static void exportExcel(String templatePath, OutputStream os, Map 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 map1 = new HashMap();
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 map2 = new HashMap();
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 map3 = new HashMap();
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 map4 = new HashMap();
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> data = Lists.newArrayList();
data.add(map1);
data.add(map2);
data.add(map3);
data.add(map4);
Map model = new HashMap();
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依赖：
org.jxls jxls-poi2.10.0
