Vue+SpringBoot实现前端Excel并下载 总结

前端页面代码

方法一：post请求：可设置token

<Button type="primary"  @click="exportData()"> 导出数据</Button>


<script>

    //下载数据
    exportData () {

      const peram = {
        title:this.activityApplicationPage.title,
        contactPhone:this.activityApplicationPage.contactPhone,
        applicationTime:this.activityApplicationPage.applicationTime,
        applicationOverTime:this.activityApplicationPage.applicationOverTime,
        enterpriseName:this.activityApplicationPage.enterpriseName,
      }
      this.$http({
        method: 'post',
        url: "http://localhost/8080"+"sa/api/activity/activityApplicationExcel",
        data:peram,
        responseType: 'blob'//这里的响应类型必须设置为blob
        //这里可以加token，我设置了全局的，所有的请求都默认加了。
      })
        .then(function(res) {
          console.log(res)
          const link = document.createElement('a')
          let blob = new Blob([res.data],{type: 'application/vnd.ms-excel'});
          link.style.display = 'none'
          link.href = URL.createObjectURL(blob);
          link.setAttribute('download', 'xxxxx文件名称.xls')
          document.body.appendChild(link)
          link.click()
          document.body.removeChild(link)
        })
        .catch(function(error) {
          console.log(error);
        });
    },

</script>

 后端代码

1.引入坐标

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

2.Contrpller

@PostMapping("/activity/activityApplicationExcel")
    public void activityApplicationExcel(HttpServletResponse response, @RequestBody ActivityApplicationPage page) {

         //条件的参数可以在这进行校验

        //设置表格表头字段
        String [] excelTitle = new String[]{"企业名称","联系人","联系电话","活动名称","报名人数","报名时间"};
        //根据查询条件查询  
        List<ActivityApplicationExcel> list = activityApplicationService.activityApplicationExcel(page);//查询数据
        //使用工具类创建表格
        ExcelUtil.export(response,"活动报名数据",excelTitle,list);

    }

3.工具类：最关键的是这几段
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;Filename=" + new String(sheetTitle.getBytes("utf-8"),"ISO-8859-1" ) + ".xls");
            out = response.getOutputStream();
            wb.write(out);

/**
 * @作者 zcq
 * @创建日期
 * @版本 V1.2
 * @描述 Excel 导出通用工具类
 */
public class ExcelUtil {

    public static void export(HttpServletResponse response, String sheetTitle, String[] title, List<ActivityApplicationExcel> list) {

        HSSFWorkbook wb = new HSSFWorkbook();//创建excel表
        HSSFSheet sheet = wb.createSheet(sheetTitle);
        sheet.setDefaultColumnWidth(20);//设置默认行宽

        //表头样式（加粗，水平居中，垂直居中）
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框样式
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        HSSFFont fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        cellStyle.setFont(fontStyle);

        //标题样式（加粗，垂直居中）
        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setFont(fontStyle);

        //设置边框样式
        cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        //字段样式（垂直居中）
        HSSFCellStyle cellStyle3 = wb.createCellStyle();
        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //设置边框样式
        cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        //创建表头
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(20);//行高

        HSSFCell cell = row.createCell(0);
        cell.setCellValue(sheetTitle);
        cell.setCellStyle(cellStyle);

        sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1)));

        //创建标题
        HSSFRow rowTitle = sheet.createRow(1);
        rowTitle.setHeightInPoints(20);

        HSSFCell hc;
        for (int i = 0; i < title.length; i++) {
            hc = rowTitle.createCell(i);
            hc.setCellValue(title[i]);
            hc.setCellStyle(cellStyle2);
        }

        byte result[] = null;

        OutputStream out = null;

        try {
            //创建表格数据
            Field[] fields;
            int i = 2;

            for (Object obj : list) {
                fields = obj.getClass().getDeclaredFields();

                HSSFRow rowBody = sheet.createRow(i);
                rowBody.setHeightInPoints(20);

                int j = 0;
                for (Field f : fields) {

                    f.setAccessible(true);

                    Object va = f.get(obj);
                    if (null == va) {
                        va = "";
                    }

                    hc = rowBody.createCell(j);
                    hc.setCellValue(va.toString());
                    hc.setCellStyle(cellStyle3);

                    j++;
                }

                i++;
            }
            //设置Http响应头告诉浏览器下载这个附件
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;Filename=" + new String(sheetTitle.getBytes("utf-8"),"ISO-8859-1" ) + ".xls");
            out = response.getOutputStream();
            wb.write(out);
        } catch (Exception ex) {
            Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                if(null != out){
                    out.close();
                }
            } catch (IOException ex) {
                Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            } finally{
                try {
                    wb.close();
                } catch (IOException ex) {
                    Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
}

 4.实体类

@Data
public class ActivityApplicationExcel {
    private String firmName;//企业名称
    private String contactPerson;//联系人
    private String contactPhone;//联系电话
    private String title;//活动名称
    private Integer amount;//报名人数
    private String createdDate;//报名时间

    public ActivityApplicationExcel() {
    }

    public ActivityApplicationExcel(String firmName, String contactPerson, String contactPhone, String title, Integer amount, String createdDate) {
        this.firmName = firmName;
        this.contactPerson = contactPerson;
        this.contactPhone = contactPhone;
        this.title = title;
        this.amount = amount;
        this.createdDate = createdDate;
    }
}

注意：表格的列要和实体类的中的个数一组，否则错位

方法2：请求方式无法加token

前端代码

<Button type="primary"  @click="exportData()"> 导出数据</Button>

<script>

//下载数据
    exportData () {

//参数全靠拼接，无法加token
      window.location.href =  http://localhost/8080 +"sa/api/activity/activityApplicationExcel?" +
        "title="+this.activityApplicationPage.title+"&contactPhone="+this.activityApplicationPage.contactPhone+
        "&applicationTime="+this.activityApplicationPage.applicationTime+"&applicationOverTime="+this.activityApplicationPage.applicationOverTime+
        "&enterpriseName="+this.activityApplicationPage.enterpriseName;
    },

</script>

后端代码

1.Contrpller

    /**
     * 管理端活动报名报表导出
     * @param page
     * @return
     */
    @RequestMapping("/activity/activityApplicationExcel")
    public void activityApplicationExcel(HttpServletResponse response,
                                         @RequestParam String title,
                                         @RequestParam String contactPhone,
                                         @RequestParam String applicationTime,
                                         @RequestParam String applicationOverTime,
                                         @RequestParam String enterpriseName) {
        ActivityApplicationPage page = new ActivityApplicationPage();
        page.setTitle(title);
        page.setContactPhone(contactPhone);
        page.setApplicationTime(applicationTime);
        page.setApplicationOverTime(applicationOverTime);
        page.setEnterpriseName(enterpriseName);
       
        //设置表格表头字段
        String [] excelTitle = new String[]{"企业名称","联系人","联系电话","活动名称","报名人数","报名时间"};
        //根据查询条件查询 
        List<ActivityApplicationExcel> list = activityApplicationService.activityApplicationExcel(page);//查询数据
        //创建表格
        ExcelUtil.export(response,"活动报名数据",excelTitle,list);

    }

其他的工具类和实体类都和 方法一 的一样