SpringBoot实现数据excel导出

本案例将演示基于springboot或者ssm等框架数据导出为excel文件的操作，简单快捷易上手，在实际项目开发中粘贴即可使用。
一、准备环境

<dependency>
	<groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
      <version>3.17</version>
</dependency>
<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

二、前端代码

html代码：
<button id="button2" class="btn-primary" onclick="exportExcel()">导出签约信息</button>

js代码：
    function exportExcel() {
        window.location.href="../exportExcelData";//这里是接口的地址
    }

三、后端代码

	@RequestMapping("/exportExcelData")
    public void excel2(HttpServletResponse response){
        response.setCharacterEncoding("UTF-8");
        //1-查询数据，这里是查询全部数据
        List<HynCompanyContactPo> list = hynOperAccountService.getAllCompanyContactList();
        HSSFWorkbook wb = new HSSFWorkbook();
        //2-创建sheet页,设置sheet页的名字
        HSSFSheet sheet = wb.createSheet("合同签约信息表");
        //3-创建标题行
        HSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("企业代码");
        titleRow.createCell(1).setCellValue("企业名称");
        titleRow.createCell(2).setCellValue("海关编码");
        titleRow.createCell(3).setCellValue("授信金额（支用额度）");
        titleRow.createCell(4).setCellValue("合同编号");
        titleRow.createCell(5).setCellValue("合同有效期起");
        titleRow.createCell(6).setCellValue("合同有效期止");
        titleRow.createCell(7).setCellValue("合同状态");
        titleRow.createCell(8).setCellValue("额度状态");
        titleRow.createCell(9).setCellValue("签约金额（合同金额）");
        //4-遍历将数据集合将数据放到对应的列中
        for (HynCompanyContactPo info : list) {
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);
            dataRow.createCell(0).setCellValue(info.getCompanyCode());
            dataRow.createCell(1).setCellValue(info.getCompanyName());
            dataRow.createCell(2).setCellValue(info.getCustomsCode());
            dataRow.createCell(3).setCellValue(info.getCreditMoney());
            dataRow.createCell(4).setCellValue(info.getContactStatus());
            dataRow.createCell(5).setCellValue(info.getContactStart());
            dataRow.createCell(6).setCellValue(info.getContactEnd());
            String contactStatus = "";
            switch (info.getContactStatus()){
                case "0" : contactStatus = "建立"; break;
                case "1" : contactStatus = "生效"; break;
                case "2" : contactStatus = "冻结"; break;
                case "3" : contactStatus = "过期"; break;
                case "4" : contactStatus = "销额"; break;
            }
            String quotaStatus = "";
            switch (info.getQuotaStatus()){
                case "0" : quotaStatus = "建立"; break;
                case "1" : quotaStatus = "生效"; break;
                case "2" : quotaStatus = "冻结"; break;
                case "3" : quotaStatus = "过期"; break;
                case "4" : quotaStatus = "销额"; break;
            }
            dataRow.createCell(7).setCellValue(contactStatus);
            dataRow.createCell(8).setCellValue(quotaStatus);
            dataRow.createCell(9).setCellValue(info.getConractMoney());
        }
        //5-创建流写出文件
        OutputStream os = null;
        try{
            //6-设置Excel的名称
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String("合同签约信息".getBytes(),"iso-8859-1") + ".xls");
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
        }catch(Exception e){
            e.printStackTrace();
        } finally {
            try {
                if(os != null){
                    os.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

以上就实现了一个导出功能，这里把业务逻辑写在了controller层只是为了方便展示，大家开发的时候还是尽可能规范一点，在controller里避免业务逻辑开发，本案同学们自行开发service，dao层代码可以直接使用，亦可封装工具类使用。