apache poi实现excel导出功能

第一步：添加依赖

<!--poi导入导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10.1</version>
        </dependency>

第二步：查询要导出的信息

<select id="exportUserInfo" resultType="java.util.Map">
    select
        user_id as userId,
        username,
        email,
        mobile,
        status,
        create_user_id as createUserId,
        create_time as createTime
    from
        sys_user
  </select>

这里的返回类型设置为了map类型，这是为了方便获取查询结果

第三步： 服务层开发

@Override
    public Workbook exportUserInfo() {
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建一个sheet
        Sheet sheet = workbook.createSheet();
        String[] titles = {"用户id", "用户名", "邮箱", "手机", "创建时间"};
        String[] columns = {"userId", "username", "email", "mobile", "createTime"};
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        // 设置标题行的值
        for (int i = 0; i < titles.length; i++){
            // 创建标题行的单元格
            Cell cell = titleRow.createCell(i);
            // 设置单元格的值
            cell.setCellValue(titles[i]);
        }
        // 设置每一行的值
        List<Map<String, Object>> mapList = this.sysUserMapper.exportUserInfo();
        for (int i = 0; i < mapList.size(); i++){
            Row row = sheet.createRow(i + 1);
            // 一个List元素代表一行的数据
            Map<String, Object> map = mapList.get(i);
            for (int j = 0; j < columns.length; j++){
                Cell cell = row.createCell(j);
                // 这里就是为什么要将mapper.xml的查询结果设置为Map类型的原因，方便得到查询结果
                Object obj = map.get(columns[j]);
                cell.setCellValue(obj + "");
            }
        }
        return workbook;
    }

第四步：开发控制层

@RequestMapping("/sys/user/export")
    public void exportUserInfo(HttpServletResponse response){
        Workbook workbook = this.sysUserService.exportUserInfo();
        // 设置相应内容为二进制流文件，一般用于下载文件时使用
        response.setContentType("application/octet-stream");
        String fileName = "员工信息表.xls";
        try {
            fileName = URLEncoder.encode(fileName, "utf-8");
            response.setHeader("content-disposition", "attachment;filename="+fileName);
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

第五步：页面开发

<button class="btn btn-info " type="button" @click="exportFun">
                <i class="fa fa-cloud-download"></i>&nbsp;&nbsp;<span class="bold">导出</span>
 </button>

exportFun:function(){
            window.location.href="/sys/user/export";
        }