Java与JXLS(使用XLS或XLSX模板（JXLS）导出Excel并下载)

POI到处Excel适合不太复杂的excel，如果遇到复杂的excel，可以使用模板进行导出。

本文参考学习的链接：
1. 使用XLS模板到处excel并下载
2. excel模板合并单元格(jxls 合并单元格)
3. 利用POI到处excel详细介绍

jxls是一个简单的、轻量级的excel到处库，使用特定的标记再excel模板文件中来定义输出格式和布局。
模板到处excel步骤解析：

先读取加载模板xls或xlsx文件，然后根据模板里面的jxls来将数据定向写入jxls中指定的对应位置。

1. 引入依赖

需要注意不能只引入poi，而不引入依赖poi-ooxml

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
        </dependency>

2. controller

    @GetMapping("/export")
    public void export() {
        try {
            List<User> users = userMapper.selectList(new QueryWrapper<User>().eq("isDelete", 0));
            List<Map<String, Object>> listUsers = userService.getUsers();
            String tplName = "/userTemplate.xlsx";
            Map<String, Object> mapList = new HashMap<>();
            mapList.put("list",listUsers);
            Workbook wb = userService.getWorkbookByTpl(tplName, mapList);
            String fileName = "用户列表";
            userService.renderExcel(wb, tplName, fileName + "_" + DateUtil.getDays() + ".xls");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

3. Workbook创建-根据模板文件和获取的用户数据

    public Workbook getWorkbookByTpl(String tplName, Map data) throws FileNotFoundException {
        XLSTransformer transformer = new XLSTransformer();
        String templatePath = "D:/File_chiyan/Code_File" + tplName;
        File file = new File(templatePath);
        FileInputStream in = new FileInputStream(file);

        Workbook wb = null;
        try {
            wb = transformer.transformXLS(in, data);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return wb;
        }
    }

4. 利用JXLS，根据XLSX模板导出excel

    public void renderExcel(Workbook wb, String tplName, String fileName) {
        if (fileName == null || "".equals(fileName)) {
            fileName = tplName.substring(tplName.lastIndexOf("/") + 1);
        }
        try {
            fileName = new String(fileName.getBytes("gbk"),"ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition","attachment;filename=\""+ fileName + "\"");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

XLSX模板如下：

5. OTHER

自己写的查询用户的数据

    <select id="getUsers" resultType="com.dy.usercenter.vo.UserVO">
        SELECT 	`id`,
            `username`,
            `userAccount`,
            (CASE gender
                WHEN 0 THEN '男'
                WHEN 1 THEN '女'
            END)gender,
            `phone`,
            `email`,
            (CASE userStatus
                WHEN 0 THEN '正常'
                ELSE '异常'
            END)userStatus,
            DATE_FORMAT(createTime,'%Y-%m-%d')createTime,
            `isDelete`,
            (CASE userRole
                WHEN 0 THEN '普通用户'
                WHEN 1 THEN '管理员'
            END)userRole
        FROM
        `user`
        LIMIT 0, 1000;
    </select>

导出的excel表格