当前位置：首页 > ops >正文

springboot+vue实现通过poi完成excel

ops 2025/8/15 8:26:33

前端

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;}

查看全文

http://www.xdnf.cn/news/17708.html