excel文件输出文件流到前端下载_xssfworkbook返回文件流给前端

1、使用poi生成excel

 mvn:

org.apache.poipoi4.0.1org.apache.poipoi-ooxml4.0.1

code:

public HSSFWorkbook createMineExcelHk(String firstRowName, List titils, List data) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    //建立新的sheet对象（excel的表单）
    HSSFSheet sheet = wb.createSheet("excel文件");
    //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
    HSSFRow row1 = sheet.createRow(0);
    row1.setHeightInPoints(30);
    //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
    HSSFCell cell = row1.createCell(0);
    //设置单元格内容
    cell.setCellValue(firstRowName);
    HSSFCellStyle cellTitleStyle = wb.createCellStyle();
    cellTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
    cellTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
    HSSFFont fontStyle = wb.createFont();
    fontStyle.setColor(HSSFColor.BLUE.index);
    fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontStyle.setFontHeightInPoints((short) 15);
    cellTitleStyle.setFont(fontStyle);
    cell.setCellStyle(cellTitleStyle);
    //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titils.size() - 1));
    sheet.setColumnWidth(0, 100 * 20);
    sheet.setColumnWidth(1, 100 * 40);
    sheet.setColumnWidth(2, 100 * 40);
    ..........
    //在sheet里创建第二行(标题栏)
    HSSFCellStyle cellTitle2Style = wb.createCellStyle();
    cellTitle2Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
    cellTitle2Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
    HSSFFont font2Style = wb.createFont();
    font2Style.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font2Style.setFontHeightInPoints((short) 10);
    cellTitle2Style.setFont(font2Style);
    HSSFRow row2 = sheet.createRow(1);
    row2.setHeightInPoints(20);
    for (int i = 0; i < titils.size(); i++) {
        HSSFCell cellTitle = row2.createCell(i);
        cellTitle.setCellStyle(cellTitle2Style);
        cellTitle.setCellValue(titils.get(i));
    }
    HSSFCellStyle rowStyle = wb.createCellStyle();
    rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
    rowStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
    for (int i = 0; i < data.size(); i++) {
        HSSFRow datarow = sheet.createRow(i + 2);
        EsSearchLog esSearchLog = data.get(i);
        //设置值
        setRow(datarow, 0, rowStyle, i + 1 + "");
        .........
        //插图
        String strBase64 = data.getAsString("base64");
        HSSFClientAnchor anchor;
        //  datarow.createCell(7).setCellValue(Keys[0]);
        // base64转BufferedImage
        BufferedImage buffer_Img;
        buffer_Img = base64ToBufferedImage(strBase64);
        ByteArrayOutputStream byteArrayOut = null;//字符输出对象
        byteArrayOut = new ByteArrayOutputStream();
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        ImageIO.write(buffer_Img, "png", byteArrayOut);// 写入
        //图片位置
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, i + 2, (short) 2, i + 3);
        // 插入图片
 
        patriarch.createPicture((HSSFClientAnchor) anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        datarow.setHeight((short) 1400);// 设置行高
 
        //保存至本地目录
        String strFilePath = excelConfig.getPath() + fileName;
        FileOutputStream output = new FileOutputStream(strFilePath);
        wb.write(output);//写入磁盘
        output.close();
        System.out.println("写excel文件完成:" + System.currentTimeMillis());
        excelExportInfoMapper.insert(uuid, strFilePath, sdf.format(new Date()), SecurityUtils.getCurrentUserLogin(), logId);*/
        return wb;
    }

2、controller

 @RequestMapping(value = "/excelExport", method = RequestMethod.POST)
    void excelExport(HttpServletResponse response) {
        System.out.println("开始写excel文件:"+System.currentTimeMillis());
        List mineTitils = new ArrayList();
        mineTitils.add("序号");
        mineTitils.add("列名1");
        String fileName = "excelExport.xls";
        try {
            HSSFWorkbook wb= createMineExcelHk("excel导出",mineTitils,data);
            response.reset();
            OutputStream out = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Pragma", "No-cache");
            wb.write(out);
            out.flush();
            out.close();
            System.out.println("完成excel文件:"+System.currentTimeMillis());
              /*  String strFilePath = "D://" + fileName;
            FileOutputStream output = new FileOutputStream(strFilePath);
            wb.write(output);//写入磁盘*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        }

3、前端代码

axios({
          headers: {
            // 'Content-Type': 'application/vnd.ms-excel',
            'authorization': 'Bearer ' + getToken()
          },
          responseType: 'blob',
          method: 'post',
          url: '/excelExport',
          data: this.formData
        }).then(res => {
          this.loading = false;
          console.log(res.data)
          const objectURL = URL.createObjectURL(new Blob([res.data], {
            type: 'application/ms-excel'
          })) // chrome不受文件你大小限制导出文件
          let a = document.createElement("a");
          a.download = "excel导出.xls";
          a.href = objectURL
          a.click();
        }).catch(e => {
          this.loading = false;
          this.$message.error('导出失败');
        })

4、nginx默认请求大小需要调整

 client_max_body_size 50m;