Springboot实现前后端分离excel下载
目录
- Springboot前后端分离excel下载
- 前后端分离Excle下载乱码问题
- 前端请求方式 : ajax请求
Springboot前后端分离excel下载
现在公司的技术栈是springboot作为后端,前端是vue, 现在要做excel的导出功能, 之前没做过,写一下记录下.
springboot版本是2.0.6 poi 3.14 ,jdk1.8
类上面的注解是: @RestController
/** * 导出excel * */ @GetMapping("export") public void exportExcel() { XSSFWorkbook workbook = placeStatService.exportExcel(); // 设置生成的Excel的文件名,并以中文进行编码 String fileName = null; try { fileName = URLEncoder.encode("房间预约使用统计表" + ".xlsx", "utf-8").replaceAll("\\+", "%20"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setCharacterEncoding("UTF-8"); response.setHeader("Content-type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); // 响应类型,编码 response.setContentType("application/octet-stream;charset=UTF-8"); try { // 形成输出流 OutputStream osOut = response.getOutputStream(); // 将指定的字节写入此输出流 workbook.write(osOut); // 刷新此输出流并强制将所有缓冲的输出字节被写出 osOut.flush(); // 关闭流 osOut.close(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } }
@Override public XSSFWorkbook exportExcel) { List<RoomOrderDetailModel> roomOrdersList = getRoomOrderList(); XSSFWorkbook data = ExcelUtil.setExcelData(roomOrdersList); return data; }
package com.util; import com.curefun.place.model.RoomOrderDetailModel; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; /** * @author excel 工具类. 导出功能 */ public class ExcelUtil { /** * 数据导出, 获取一个excel对象 * * @param */ public static XSSFWorkbook setExcelData(List<RoomOrderDetailModel> orderDetailModels) { //创建一个book,对应一个Excel文件 XSSFWorkbook workbook = new XSSFWorkbook(); //在book中添加一个sheet,对应Excel文件中的sheet XSSFSheet sheet = workbook.createSheet("教室预约使用记录"); //设置六列的宽度 sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3800); sheet.setColumnWidth(3, 2800); sheet.setColumnWidth(4, 3200); sheet.setColumnWidth(5, 3600); sheet.setColumnWidth(6, 2850); //居中的样式 XSSFCellStyle centerStyle = getCenterStyle(workbook); // 第三步,在sheet中添加表头第0行 XSSFRow row0 = sheet.createRow(0); setFirstRow(centerStyle, row0); int rowNum = 1; for (RoomOrderDetailModel model : orderDetailModels) { XSSFRow row = sheet.createRow(rowNum); row.createCell(0).setCellValue(rowNum); rowNum++; row.createCell(1).setCellValue(model.getBuildingName()); row.createCell(2).setCellValue(model.getRoomNo()); row.createCell(3).setCellValue(model.getRoomName()); row.createCell(4).setCellValue(model.getEventType()); row.createCell(5).setCellValue(model.getEventName()); row.createCell(6).setCellValue(model.getUserRealName()); } return workbook; } /** * 获取居中的样式. * * @param workbook * @return */ private static XSSFCellStyle getCenterStyle(XSSFWorkbook workbook) { XSSFCellStyle cellStyle = workbook.createCellStyle(); //设置水平对齐的样式为居中对齐; cellStyle.setAlignment(HorizontalAlignment.CENTER); //垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } /** * 设置第一行的表头 * * @param centerStyle * @param row */ private static void setFirstRow(XSSFCellStyle centerStyle, XSSFRow row) { XSSFCell cell0 = row.createCell(0); cell0.setCellValue("序号"); cell0.setCellStyle(centerStyle); XSSFCell cell1 = row.createCell(1); cell1.setCellValue("楼栋信息"); cell1.setCellStyle(centerStyle); XSSFCell cell2 = row.createCell(2); cell2.setCellValue("房号"); cell2.setCellStyle(centerStyle); XSSFCell cell3 = row.createCell(3); cell3.setCellValue("房间名称"); cell3.setCellStyle(centerStyle); XSSFCell cell4 = row.createCell(4); cell4.setCellValue("活动类型"); cell4.setCellStyle(centerStyle); XSSFCell cell5 = row.createCell(5); cell5.setCellValue("活动名称"); cell5.setCellStyle(centerStyle); XSSFCell cell6 = row.createCell(6); cell6.setCellValue("使用人"); cell6.setCellStyle(centerStyle); /** 其实完全使用这种方式, 会更加的简单,便于修改 List<String> title = Stream.of("序号", "专业", "班级", "课程名称", "课程内容", "授课教师", "授课时长", "授课时间", "学分", "授课房间") .collect(Collectors.toList()); for (int i = 0; i < title.size(); i++) { XSSFCell cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(centerStyle); } */ } }
其实使用很简单,就是excel的文件名需要进行编码,这个需要注意,其他没啥的了.
前后端分离Excle下载乱码问题
前端:vue+elementUI
后端:springCloud
前端请求方式 : ajax请求
this.$.ajax({ url :this.url + "/", type : 'post', data : formData, contentType: false, processData: false, xhrFields: {withCredentials: true, responseType:'arraybuffer'}, headers : {'Access-Control-Allow-Origin': '*', "Authorization": this.ajaxRequest.getToken()}, success: function (res, textStatus, request) { var downloadFile = document.createElement('a'); let blob = new Blob([res], {type : "application/vnd.ms-excel;charset=UTF-8"}); downloadFile.href = window.URL.createObjectURL(blob); console.log(request.getResponseHeader('Content-disposition')); downloadFile.download = decodeURI(request.getResponseHeader('Content-disposition').split('filename=')[1] ); downloadFile.click(); window.URL.revokeObjectURL(downloadFile.href); }, error : function (res) { console.log(res) } })
后端处理:导出文件处理
// 输出Excel文件 OutputStream out = null; out = response.getOutputStream(); response.reset(); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF8")); response.setContentType("application/vnd.ms-excel; charset=utf-8"); // 输出Excel内容,生成Excel文件 wb.write(out);
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)