jxls导出Excel表格

一、单个表格导出（一个sheet）  

1、导入依赖

<!-- Execl工具包 -->
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.4.6</version>
        </dependency>

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

2、编写excel模板

3、相关代码 

        String jxlsTemplateName="info";
        String header="信息表";
        String fileName="信息表格";
        Map<String, Object> model = new HashMap<>();
        model.put("dataList", infoList);
        model.put("header", header);
        CalculateUtils.createExcel(response, model, jxlsTemplateName, fileName);

/**
     * Excel模板加载和导出
     *
     * @param response
     * @param model
     * @param jxlsTemplateName
     * @param fileName
     */
    public static void createExcel(HttpServletResponse response, Map<String, Object> model,
                                   String jxlsTemplateName, String fileName) {
        InputStream is = null;
        OutputStream os = null;
        try {
            log.info("export excel file name {}.", fileName);
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx");
            os = response.getOutputStream();
            log.info("export excel file name {}.", fileName);
            Resource resource = new ClassPathResource("jxls-template/" + jxlsTemplateName + ".xlsx");
            is = resource.getInputStream();
            JxlsUtils.exportExcel(is, os, model);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    /*e.printStackTrace();*/
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                }
            }
        }
    }

二、多sheet导出到同一个excel表格中

表格模板： 

相关代码：

String jxlsTemplateName = "info";
Map<String, Object> model = new HashMap<>(2);
//sheet1
model.put("dataList", infoList);
//sheet2
model.put("tradeList",detailList);

createMutilSheetTempExcel(model,jxlsTemplateName,"信息表");

private static void createMutilSheetTempExcel(Map<String, Object> model, String jxlsTemplateName, String tempExcelName) throws Exception {
        OutputStream os = new BufferedOutputStream(new FileOutputStream(tempExcelName + ".xlsx"));
        Resource resource = new ClassPathResource(jxlsTemplateName + ".xlsx");
        InputStream is = resource.getInputStream();
        if (os != null && jxlsTemplateName != null) {
            Map<Integer, String> sheet = new HashMap<>();
            sheet.put(0, "sheet1");
            sheet.put(1, "sheet2");
            JxlsUtils.exportMultSheetExcel(is, os, model, sheet);
            os.close();
            is.close();
        }
    }

public static boolean exportMultSheetExcel(InputStream is, OutputStream os, Map<String, Object> model, Map<Integer, String> sheet) {
        Context context = PoiTransformer.createInitialContext();
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        try {
            //获得配置
            JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
            Map<String, Object> functionMap = new HashMap<String, Object>();
            functionMap.put("utils", new JxlsUtils());
            evaluator.getJexlEngine().setFunctions(functionMap);
            List<Area> xlsAreaList = areaBuilder.build();
            if (sheet != null) {
                for (Integer index : sheet.keySet()) {
                    Area xlsArea = xlsAreaList.get(index);
                    xlsArea.applyAt(new CellRef(sheet.get(index) + "!A1"), context);
                }
            }
            transformer.write();
            is.close();
        } catch (Exception e) {
            log.error("批量写文件错误", e);
            return false;
        }
        return true;
    }