jxls结合spring mvc生成excel

jxls结合spring mvc生成excel

Apache jxls是生成excle的模板引擎，结合spring mvc生成excel

public class JxlsExcelView extends AbstractView {


    private String exportFileName;
    private String templateName;
    private static final String CONTENT_TYPE = "application/vnd.ms-excel";
    private static final String TPL_PATH = "template/";

    public JxlsExcelView(String exportFileName, String templateName) {
        if (exportFileName != null) {
            try {
                exportFileName = URLEncoder.encode(exportFileName, "UTF-8");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        this.exportFileName = exportFileName;
        this.templateName = templateName;
    }

    @Override
    protected void renderMergedOutputModel(Map<String, Object> map, HttpServletRequest request, HttpServletResponse response) throws Exception {

        Context context = new Context(map);
        response.setContentType(CONTENT_TYPE);
        response.setHeader("content-disposition", "attachment;filename=" + exportFileName + ".xls");
        InputStream is = getClass().getClassLoader().getResourceAsStream(TPL_PATH+templateName);
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        ServletOutputStream os = response.getOutputStream();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        JexlExpressionEvaluator expressionEvaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
        Map<String, Object> func = new HashMap<>();
        func.put("utils", new JxlsUtils());
        expressionEvaluator.getJexlEngine().setFunctions(func);
        jxlsHelper.processTemplate(context, transformer);
        is.close();
    }
}

JxlsUtils工具类:

public class JxlsUtils {


    static{
        //合并单元格,自定义实现
        XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
    }

    public String dateFmt(Date date, String pattern) {
        if (date == null) {
            return "";
        }

        try {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            return sdf.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
}

在controller调用:

@RequestMapping("/download")
public ModelAndView download(HttpServletRequest request ,HttpServletResponse response) throws IOException {
   try {
       Map<String, Object> model = new HashMap<>();
       model.put("data", result);
       return new ModelAndView(new JxlsExcelView("xxxx报表", "download_template.xlsx"), model);
   } catch (Throwable e) {
       LOGGER.error("导出报表失败: ", e);
   }
   return null;
}