Springboot+vue前後端分離專案，poi匯出excel提供使用者下載的解決方案

簡易程式碼發表於2021-11-11
Spring BootVue後端Excel

因為我們做的是前後端分離專案 無法採用response.write直接將檔案流寫出

我們採用阿里雲oss 進行儲存 再返回的結果物件裡面儲存我們的檔案地址

廢話不多說，上程式碼

Springboot

第一步匯入poi相關依賴

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

第二步編寫批量匯出api

student物件

@Data
public class StudentDto {
    @JsonDeserialize(using = LongJsonDeserializer.class)
    @JsonSerialize(using = LongJsonSerializer.class)
    private Long id;
    private String name;
    private String studentId;
    private String collegename;
    private int collegeId;
    private int classId;
    private String classname;
    private Integer role;
    private String email;
    private String phone;
}

阿里雲oss檔案上傳的service（不會使用阿里雲oss的可以檢視 https://www.cnblogs.com/jydm/p/14745418.html）

    @Override
    public String uploadFileXlsx(InputStream inputStream, String own,String fileName) throws Exception {
        OSS ossClient = OssUtil.getossClient();
        //這裡最好對檔案路徑或名字做一下處理，防止檔名或路徑重複導致檔案丟失或者被覆蓋
        String url ="pm/"+own+"/"+fileName+".xlsx";
        //上傳
        ossClient.putObject(OssProperies.BUCKET_NAME, url, inputStream);
        // 關閉OSSClient。
        ossClient.shutdown();
        return "https://"+OssProperies.BUCKET_NAME+"."+OssProperies.END_POINT+"/"+url;
    }

 

Controller

    @ApiOperation("匯出學生資訊")
    @PostMapping("/exportStudents")
    @RequiresRoles("admin")
    public Result exportStudents(@RequestBody String students){
        //將前端傳遞的json資料轉換為物件陣列
        JSONObject jsonObject = JSONObject.parseObject(students);
        List<StudentDto> studentDtos = JSONObject.parseArray(jsonObject.getJSONArray("students").toJSONString(), StudentDto.class);
        //建立excel工作表
        Workbook workbook =new XSSFWorkbook();
        Sheet studentsheet = workbook.createSheet("學生資訊表");
        Row row = studentsheet.createRow(0);
        String[] title= {"學號","姓名","學院","班級","電話","郵箱"};
        for (int i = 0; i < 6; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        for (int i = 1; i < studentDtos.size()+1; i++) {
            StudentDto studentDto = studentDtos.get(i - 1);
            Row row1 = studentsheet.createRow(i);
            Cell cell0 = row1.createCell(0);
            cell0.setCellValue(studentDto.getStudentId());
            Cell cell1 = row1.createCell(1);
            cell1.setCellValue(studentDto.getName());
            Cell cell2 = row1.createCell(2);
            cell2.setCellValue(studentDto.getCollegename());
            Cell cell3 = row1.createCell(3);
            cell3.setCellValue(studentDto.getClassname());
            Cell cell4 = row1.createCell(4);
            cell4.setCellValue(studentDto.getPhone());
            Cell cell5 = row1.createCell(5);
            cell5.setCellValue(studentDto.getEmail());
        }
        InputStream excelStream = null;
        String path=null;
        try {
            //這裡就是io流的轉換  WorkBook需要寫入一個輸出流 阿里雲oss儲存檔案需要一個輸入流
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            out.close();
            excelStream= new ByteArrayInputStream(out.toByteArray());
            workbook.close();

            path = fileService.uploadFileXlsx(excelStream, "admin", "學生資訊表");
        } catch (Exception e) {
            e.printStackTrace();
        }
        HashMap<Object, Object> map = new HashMap<>();
        map.put("url",path);
        return Result.succ(map);
    }

這樣我們就返回給前端我們的一個檔案地址

vue前端處理

我們請求完成之後可以設定一個彈出框 詢問使用者是否需要下載 然後將 window.location.href 指向我們的檔案地址

或者請求結果返回為請求成功，直接將window.location.href 指向我們的檔案地址

async exportExcel(){
    const{data:res}=await  this.$axios.post("/student/exportStudents",{students:this.multipleSelection})
    console.log(res)
    if(res.code==200){
       this.$confirm('匯出成功,是否下載到電腦', '提示', {
          confirmButtonText: '確定',
          cancelButtonText: '取消',
          type: 'success'
        }).then(() => {
       window.location.href=res.data.url
        }).catch(() => {
          this.$message({
            type: 'info',
            message: '已取消'
          });          
        });
    }
 }

 這樣我們就實現了 springboot+vue前後端分離專案 批量匯出功能

附阿里雲oss購買渠道 https://www.aliyun.com/activity/1111?userCode=8crnx4b5 

 

相關文章