jxls使用

    //导出
        InputStream inputStream = null;
        try {
            inputStream = new ClassPathResource("jxls_templates/export-scheduler-report.xls").getInputStream();
        } catch (IOException e2) {
            e2.printStackTrace();
        }
        InputStream in = null;
        OutputStream out = null;
        // 设置响应
        String encode = null;
        try {
            encode = URLEncoder.encode(docFileName, Charsets.UTF_8.name());
        } catch (UnsupportedEncodingException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + encode);
        try {
            in = new BufferedInputStream(inputStream);
            out = response.getOutputStream();
            try {
                //需要导出的数据
                JxlsBuilder
                        .getBuilder("export-scheduler-report.xls")
                        .out(out)
                        .putVar("dateList", dateList).putVar("weekList", weekList)
                        .putVar("schedulerList",schedulerList)
                        .addFunction("utils", new JxlsUtil()).ignoreImageMiss(true).build();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                }
            }
        }

1.exls语法

each 循环
items 上下文中集合的变量名
var 在遍历集合的时候每一条记录的变量名
area XLS Command的解析区域
direction 数据在excel中填充的方向，默认向下(down)
select 一个表达式，用来过滤数据    

2.jxls中自定义函数的使用

//获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
//设置静默模式，不报警告
evaluator.getJexlEngine().setSilent(true);
//函数强制，自定义功能
Map<String, Object> funcs = new HashMap<String, Object>();
funcs.put("utils", new JxlsUtils());    //添加自定义功能
evaluator.getJexlEngine().setFunctions(funcs);
//必须要这个，否者表格函数统计会错乱
jxlsHelper.processTemplate(context, transformer);




//定义工具类
public class JxlsUtils {	
    /**
     * 字符串为空转换为特定字符
     * @param target  需要验证的字符串
     * @param fit     匹配的字符串
     * @param change  匹配成功要转换的字符串
     */
    public String fitToChange(String target, String fit, String change) {
        if (fit.equals(target)) {
            return change;
        }
        return target;
    }
}


在excel模板使用
    ${utils:fitToChange(需要验证的数据,'','111')}

3.jxls使用例子

jx:each(items="dateList" var="date"  direction="RIGHT" lastCell="B2")

//使用双循环遍历数据    
Administrator:
jx:each(items="schedulerList" var="item" lastCell="B4")
    
Administrator:
jx:each(items="item.schedulers" var="childer"  direction="RIGHT" lastCell="B4") 
    
//分组的排列    
<jx:forEachitems="${group.items}" var="employee">
    ${employee.name}| ${employee.payment} | ${employee.bonus}
</jx:forEach>
    

//jx:if标签

<jx:iftest="${department.chief.payment > 2000.0}">
 Chief Name: ${department.chief.name}
</jx:if>