springboot+vue實現通過poi完成excel

前端

1、按鈕

              <el-buttontype="text"size="mini"@click="handleExport">導出</el-button>

2、方法

 //導出async handleExport() {if (!this.activityId) {this.$message.warning('活動ID不存在');return;}try {this.loading = true;const res = await exportSignSheet({activityId: this.activityId});//文件名let fileName = `活動簽到表_${this.activity?.activityTitle || '未知活動'}_${Date.now()}.xlsx`;//創建下載鏈接const blob = new Blob([res.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});const url = window.URL.createObjectURL(blob);const link = document.createElement('a');link.href = url;link.download = fileName;document.body.appendChild(link);link.click();// 清理setTimeout(() => {document.body.removeChild(link);window.URL.revokeObjectURL(url);}, 100);this.$message.success('簽到表下載已開始');} catch (error) {console.error('導出失敗:', error);this.$message.error(`導出失敗: ${error.message}`);} finally {this.loading = false;}},

3、路由

export function exportSignSheet(data) {return request({url: '/association/detail/signSheet',//后端接口地址method: 'post',data,responseType: 'blob',//一定要指定類型為blob// 確保能獲取到headerstransformResponse: (data, headers) => {return {data,headers: headers || {}};}});
}

4、后端

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

 @PostMapping("/signSheet")public void exportSignSheet(@RequestBody Map<String, Long> request, // 前端參數HttpServletResponse response) throws IOException {// 設置CORS頭（如果存在跨域問題）response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");// 1. 驗證活動IDLong activityId = request.get("activityId");if (activityId == null) {throw new IllegalArgumentException("活動ID不能為空");}// 2. 獲取活動完整信息（復用已有查詢方法）ActivityDetailVO activityVO = activityDetailService.getActivityDetail(activityId);if (activityVO == null) {throw new RuntimeException("活動不存在或已刪除");}// 3. 處理文件名String activityName = activityVO.getActivityTitle() != null ? activityVO.getActivityTitle() : "未知活動";String fileName = "活動簽到表_" + activityName + "_" + System.currentTimeMillis() + ".xlsx";// 4. 處理文件名避免亂碼String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replace("+", "%20"); // 替換空格編碼// 5. 設置請求頭        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-Disposition","attachment; filename=\"" + encodedFileName + "\"; filename*=UTF-8''" + encodedFileName);// 6. 創建并填充Exceltry (XSSFWorkbook workbook = new XSSFWorkbook()) {buildExcelContent(workbook, activityVO);//可以將文件存入本地進行調試//workbook.write(Files.newOutputStream(Paths.get("debug.xlsx")));workbook.write(response.getOutputStream());} catch (Exception e) {response.reset();response.setContentType("application/json");response.setCharacterEncoding("UTF-8");response.getWriter().write("{\"error\":\"" + e.getMessage() + "\"}");}}

private void buildExcelContent(XSSFWorkbook workbook, ActivityDetailVO activityVO) {Sheet sheet = workbook.createSheet("活動簽到表");sheet.setDefaultColumnWidth(15);// 創建樣式（保持不變）CellStyle titleStyle = createTitleStyle(workbook);CellStyle borderStyle = createBorderStyle(workbook);CellStyle headerStyle = createHeaderStyle(workbook);// 標題行（第0行）- 修復合并單元格邊框Row titleRow = sheet.createRow(0);Cell titleCell = titleRow.createCell(0);titleCell.setCellValue("活動簽到表");CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 5);sheet.addMergedRegion(titleRegion);titleCell.setCellStyle(titleStyle);setRegionBorder(BorderStyle.THIN, titleRegion, sheet, workbook); // 新增：設置合并區域邊框// 第二行：活動名稱（第1行）- 修復合并單元格邊框Row nameRow = sheet.createRow(1);Cell nameCell = nameRow.createCell(0);nameCell.setCellValue(activityVO.getActivityTitle() != null ? activityVO.getActivityTitle() : "未設置");CellRangeAddress nameRegion = new CellRangeAddress(1, 1, 0, 5);sheet.addMergedRegion(nameRegion);nameCell.setCellStyle(borderStyle);setRegionBorder(BorderStyle.THIN, nameRegion, sheet, workbook); // 新增：設置合并區域邊框// 第三行：活動地點和活動時間（第2行）- 修復合并單元格邊框Row locationTimeRow = sheet.createRow(2);// 活動地點標簽Cell locationLabelCell = locationTimeRow.createCell(0);locationLabelCell.setCellValue("活動地點");locationLabelCell.setCellStyle(borderStyle);// 活動地點內容（合并1-2列）Cell locationCell = locationTimeRow.createCell(1);locationCell.setCellValue(getFirstActivityLocation(activityVO));CellRangeAddress locationRegion = new CellRangeAddress(2, 2, 1, 2);sheet.addMergedRegion(locationRegion);locationCell.setCellStyle(borderStyle);setRegionBorder(BorderStyle.THIN, locationRegion, sheet, workbook); // 新增：設置合并區域邊框// 活動時間標簽Cell timeLabelCell = locationTimeRow.createCell(3);timeLabelCell.setCellValue("活動時間");timeLabelCell.setCellStyle(borderStyle);// 活動時間內容（合并4-5列）Cell timeCell = locationTimeRow.createCell(4);timeCell.setCellValue(formatActivityTimeRange(activityVO.getStartTime(), activityVO.getEndTime()));CellRangeAddress timeRegion = new CellRangeAddress(2, 2, 4, 5);sheet.addMergedRegion(timeRegion);timeCell.setCellStyle(borderStyle);setRegionBorder(BorderStyle.THIN, timeRegion, sheet, workbook); // 新增：設置合并區域邊框// 第四行：活動召集人和報名人數（第3行）- 修復合并單元格邊框Row organizerCountRow = sheet.createRow(3);// 活動召集人標簽Cell organizerLabelCell = organizerCountRow.createCell(0);organizerLabelCell.setCellValue("活動召集人");organizerLabelCell.setCellStyle(borderStyle);// 活動召集人內容（合并1-2列）Cell organizerCell = organizerCountRow.createCell(1);organizerCell.setCellValue(activityVO.getEntrepreneurNames() != null ? activityVO.getEntrepreneurNames() : "未設置");CellRangeAddress organizerRegion = new CellRangeAddress(3, 3, 1, 2);sheet.addMergedRegion(organizerRegion);organizerCell.setCellStyle(borderStyle);setRegionBorder(BorderStyle.THIN, organizerRegion, sheet, workbook); // 新增：設置合并區域邊框// 報名人數標簽Cell countLabelCell = organizerCountRow.createCell(3);countLabelCell.setCellValue("報名人數");countLabelCell.setCellStyle(borderStyle);// 報名人數內容（合并4-5列）Cell countCell = organizerCountRow.createCell(4);countCell.setCellValue(String.valueOf(getParticipantCount(activityVO)));CellRangeAddress countRegion = new CellRangeAddress(3, 3, 4, 5);sheet.addMergedRegion(countRegion);countCell.setCellStyle(borderStyle);setRegionBorder(BorderStyle.THIN, countRegion, sheet, workbook); // 新增：設置合并區域邊框// 第五行：簽到處（第4行）- 修復合并單元格邊框Row signRow = sheet.createRow(4);Cell signCell = signRow.createCell(0);signCell.setCellValue("簽到處");CellRangeAddress signRegion = new CellRangeAddress(4, 4, 0, 5);sheet.addMergedRegion(signRegion);signCell.setCellStyle(borderStyle);setRegionBorder(BorderStyle.THIN, signRegion, sheet, workbook); // 新增：設置合并區域邊框// 表頭行（第5行）- 保持不變Row headerRow = sheet.createRow(5);String[] headers = {"序號", "報名人", "聯系電話", "序號", "報名人", "聯系電話"};for (int i = 0; i < headers.length; i++) {Cell cell = headerRow.createCell(i);cell.setCellValue(headers[i]);cell.setCellStyle(headerStyle);}}// 帶邊框的樣式private CellStyle createBorderStyle(XSSFWorkbook workbook) {CellStyle style = workbook.createCellStyle();// 設置邊框style.setBorderTop(BorderStyle.THIN);style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);// 設置邊框顏色（黑色）style.setTopBorderColor(IndexedColors.BLACK.getIndex());style.setBottomBorderColor(IndexedColors.BLACK.getIndex());style.setLeftBorderColor(IndexedColors.BLACK.getIndex());style.setRightBorderColor(IndexedColors.BLACK.getIndex());// 居中對齊style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}// 標題樣式（繼承邊框樣式并加大字體）private CellStyle createTitleStyle(XSSFWorkbook workbook) {CellStyle style = createBorderStyle(workbook);Font font = workbook.createFont();font.setBold(true);font.setFontHeightInPoints((short) 16);style.setFont(font);return style;}// 表頭樣式（繼承邊框樣式并加粗）private CellStyle createHeaderStyle(XSSFWorkbook workbook) {CellStyle style = createBorderStyle(workbook);Font font = workbook.createFont();font.setBold(true);style.setFont(font);return style;}