用HSSFWorkbook生成一个excel，并以流形式返回前端，并自动下载

约定不等于承诺〃 2023-10-08 20:58 1阅读 0赞

1 用 HSSFWorkbook 定义excel的格式，并填入数据。  
2 将这个文件以response流返回  
3 blob形式拿到文件

@Override
    	public void exportToExcelTable(List<FeedbackRecord> list, String[] columnName, HttpServletResponse response) throws IOException {
        
    		Long time = LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8"));
    
    		HSSFWorkbook book=new HSSFWorkbook();
    		HSSFSheet sheet=book.createSheet("表");
    		int num = 0;
    		String [][] arr = new String[list.size()][12];
    		for (FeedbackRecord feedbackRecord : list){
        
    			BeanMap beanMap = BeanMap.create(feedbackRecord);
    
    			beanMap = checkNullAndEnglishCode(beanMap);
    			arr [num][0] = beanMap.get("userName").toString();
    			arr [num][1] = beanMap.get("feedbackTime").toString().replace("T"," ");
    			arr [num][2] = beanMap.get("feedbackTitle").toString();
    			arr [num][3] = beanMap.get("type").toString();
    			arr [num][4] = beanMap.get("feedbackContent").toString();
    			arr [num][5] = beanMap.get("channel").toString();
    			arr [num][6] = beanMap.get("checkStatus").toString();
    			arr [num][7] = beanMap.get("adoptStatus").toString();
    			arr [num][8] = beanMap.get("realizeStatus").toString();
    			arr [num][9] = beanMap.get("replyStatus").toString();
    			arr [num][10] = beanMap.get("replyContent").toString();
    			arr [num][11] = beanMap.get("isPublic").toString();
    			num++;
    		}
    		//生成表单的第一行，即表头
    		HSSFRow row0=sheet.createRow(0);//创建第一行
    		for(int i=0;i<12;i++){
        
    			HSSFCell cel=row0.createCell(i);//创建第一行的第i列
    			cel.setCellValue(columnName[i]);
    		}
    		//从第二行开始，往里添加每列数据
    		for(int j=0;j<num;j++){
        
    			HSSFRow row=sheet.createRow(j+1);//创建非第一行的其他行
    			for(int i=0;i<12;i++){
        
    				HSSFCell cel=row.createCell(i);//创建第j+1行的第i列
    				cel.setCellValue(arr[j][i]);
    			}
    		}
    		//用文件输出流类创建Excel表格
    //		FileOutputStream out=new FileOutputStream(path);
    //		book.write(out);//将HSSFWorkBook中的表写入输出流中
    
    		File file = new File("E:/excel测试/反馈"+time.toString()+".xls");
    		
    		response.setContentType("application/octet-stream");
    		response.setHeader("content-type", "application/octet-stream");
    		response.setHeader("Content-Disposition", "attachment;fileName=download");// 设置文件名
    		OutputStream os = response.getOutputStream();
    		book.write(os);
    		//os.write(YaFileUtil.getBytesByFile(file));
    		os.flush();
    		book.close();
    	}

//下载方法
          async downloadUploadInstallPacFile(row) {
        
            const Obj = JSON.parse(row.attachmentAddressForm);
            if(Obj!=null){
        
              //for循环遍历
              const data = {
        
                //这里要注意，json解出来有可能有多个，下载一个文件的话，需要取数组的第一个Obj[0]
                attachmentFileOssLink: Base64.encode(Obj[0].attachmentFileOssLink),
                bucketName: "appversion"
              };
              const result = await downloadAttachedFile(data);
              const url = window.URL.createObjectURL(new Blob([result.data], {
        type: 'application/octet-stream'}));
              const link = document.createElement('a');
              link.href = url;
              link.setAttribute('download', Obj[0].attachmentFileName);
              document.body.appendChild(link);
              link.click();
            }
          },

附：通用的blob流形式的下载

downloadFile(file){
        
              let type = fileTypeMap[file.itemSuffix];//取文件名后缀，可忽略
              getFileByUrl(file.url).then(result => {
        
                const blob = new Blob([result.data], {
         type: type });
                let src = window.URL.createObjectURL(blob);//拿到blob地址
                var link = document.createElement('a')//建立a链接
                link.href = src;//配置url
                link.target = "_blank";
                link.setAttribute('download', file.itemName + new Date().toLocaleDateString())//下载类型，并配置文件名
                link.click();//开启下载
              })
            },