springboot vue導出excel

處理后端返回的文件流，下載成excl文件

vue

<el-button class="el-icon-download" type="success" @click="exportExcel()">導出</el-button>

沒封裝axios

exportExcel() {axios({url: 請求地址, //URL,根據實際情況來method: "get", responseType: "blob" //這里必須設置 responseType: "blob"}).then(function (res) {const link = document.createElement("a");let blob = new Blob([res.data], { type: res.data.type });// let blob = new Blob([res.data], { type: "application/vnd.ms-excel" }); //知道type也可以直接填link.style.display = "none";//設置連接let url = URL.createObjectURL(blob);link.href = url;//導出文件名稱link.download = "客戶表格"; //模擬點擊事件link.click();document.body.removeChild(link);});}

沒封裝axios的請求返回值res let blob = new Blob([res.data], { type: res.data.type }); 這里的res.data 或者 res.data.type 必須和這里對應

導出表格內容可能顯示【Object object】或者 undefined 大概率這里填的有誤

封裝axios

export function exportUser(params) {return service({url: "/xxx", //自己后臺請求地址method: "get",responseType: 'blob', //這里必須設置 responseType: "blob"params: params}); } async exportExcel() {const res = await exportExcel();if (res) {const link = document.createElement("a");let blob = new Blob([res], { type: res.type });link.style.display = "none";//設置連接link.href = URL.createObjectURL(blob);link.download = "客戶表格";document.body.appendChild(link);//模擬點擊事件link.click();document.body.removeChild(link);}}

封裝過的返回值可能不一樣 let blob = new Blob([res], { type: res.type }); 這里就填寫對應的 返回值

后端

用的easypoi

首先引入pom依賴

<!--easypoi導入導出--><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.3</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.3</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.3</version></dependency>

編寫實體類

@ExcelTarget("user") @Data public class User {private Integer id;/*** 郵箱*/@Excel(name = "郵箱")private String mailbox;/*** 用戶名*/@Excel(name = "用戶名")private String userName;@Excel(name = "ip")private String ip;@Excel(name = "備注")private String remarks;

這里使用了 @Excel 是關鍵注解，必不可少，name表示指定生成的excel的對應列明，更多用法請求官方文檔查看或者百度使用。

控制層

@GetMapping(value = "/getUser")public void getUser(HttpServletResponse response) {List<User> users = userService.getUser();Workbook workbook = null;ServletOutputStream outputStream = null;try {workbook = ExcelExportUtil.exportExcel(new ExportParams("表格首行名稱", "sheet名稱"), User.class, users);response.setCharacterEncoding("utf-8");response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("content-Disposition", "attachment;fileName="+ URLEncoder.encode("導出excel名稱", "UTF-8"));// 出現跨域問題 可以加這倆行 // response.setHeader("Access-Control-Allow-Origin", "前臺地址");// response.setHeader("Access-Control-Allow-Credentials", "true");outputStream = response.getOutputStream();workbook.write(outputStream);outputStream.flush();} catch (IOException e) {e.printStackTrace();} finally {try {outputStream.close();workbook.close();} catch (IOException e) {e.printStackTrace();}}// 或者直接用封裝好的工具類 網上也有很多的// ExcelUtil.exportExcel(數據list, "表格首行名稱", "sheet名稱", User.class, "導出excel名稱", response);}

excel能夠正常導出 打開也沒有亂碼 但是控制臺可能會報錯

org.springframework.http.converter.HttpMessageNotWritableException: No converter for [xxx] with preset Content-Type 'application/vnd.ms-excel;charset=utf-8'

controller的方法用放回值 方法 改成void即可

工具類

public class ExcelUtil {/*** Map集合導出** @param list 需要導出的數據* @param fileName 導出的文件名* @param response HttpServletResponse對象*/public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception{defaultExport(list, fileName, response);}/*** 復雜導出Excel，包括文件名以及表名（不創建表頭）** @param list 需要導出的數據* @param title 表格首行標題（不需要就傳null）* @param sheetName 工作表名稱* @param pojoClass 映射的實體類* @param fileName 導出的文件名（如果為null，則默認文件名為當前時間戳）* @param response HttpServletResponse對象*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,HttpServletResponse response) throws Exception{defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));}/*** 復雜導出Excel，包括文件名以及表名（創建表頭）** @param list 需要導出的數據* @param title 表格首行標題（不需要就傳null）* @param sheetName 工作表名稱* @param pojoClass 映射的實體類* @param fileName 導出的文件名* @param isCreateHeader 是否創建表頭* @param response HttpServletResponse對象*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,boolean isCreateHeader, HttpServletResponse response) throws Exception{ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setCreateHeadRows(isCreateHeader);defaultExport(list, pojoClass, fileName, response, exportParams);}/*** 默認導出方法** @param list 需要導出的數據* @param pojoClass 對應的實體類* @param fileName 導出的文件名* @param response HttpServletResponse對象* @param exportParams 導出參數實體*/private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,ExportParams exportParams) throws Exception{Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);downloadExcel(fileName, workbook, response);}/*** 默認導出方法** @param list Map集合* @param fileName 導出的文件名* @param response HttpServletResponse對象*/private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response)throws Exception {Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);if (null != workbook) {downloadExcel(fileName, workbook, response);}}/*** Excel導出** @param fileName Excel導出* @param workbook Excel對象* @param response HttpServletResponse對象*/public static void downloadExcel(String fileName, Workbook workbook, HttpServletResponse response) throws Exception{ServletOutputStream outputStream = null;try {if (StringUtils.isEmpty(fileName)) {throw new RuntimeException("導出文件名不能為空");}response.setCharacterEncoding("utf-8");response.setHeader("content-Type", "application/vnd.ms-excel; charset=utf-8");response.setHeader("content-disposition", "attachment;fileName="+ URLEncoder.encode(fileName+".xls", "UTF-8"));// response.setHeader("Access-Control-Allow-Origin", "前臺ip"); // response.setHeader("Access-Control-Allow-Credentials", "true");outputStream = response.getOutputStream();workbook.write(outputStream);outputStream.flush();} catch (Exception e) {log.error(e.getMessage(), e);} finally {outputStream.close();workbook.close();}}/*** 根據文件路徑來導入Excel** @param filePath 文件路徑* @param titleRows 表標題的行數* @param headerRows 表頭行數* @param pojoClass 映射的實體類* @return*/public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception{//判斷文件是否存在if (StringUtils.isBlank(filePath)) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);} catch (NoSuchElementException e) {log.error("模板不能為空", e);} catch (Exception e) {log.error(e.getMessage(), e);}return list;}/*** 根據接收的Excel文件來導入Excel,并封裝成實體類** @param file 上傳的文件* @param titleRows 表標題的行數* @param headerRows 表頭行數* @param pojoClass 映射的實體類* @return*/public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception{if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);} catch (NoSuchElementException e) {log.error("excel文件不能為空", e);} catch (Exception e) {log.error(e.getMessage(), e);}return list;}/*** 文件轉List** @param file* @param pojoClass* @param <T>* @return*/public static <T> List<T> fileToList(MultipartFile file, Class<T> pojoClass) throws Exception{if (file.isEmpty()) {throw new RuntimeException("文件為空");}List<T> list = ExcelUtil.importExcel(file, 1, 1, pojoClass);if (CollectionUtils.isEmpty(list)) {throw new RuntimeException("未解析到表格數據");}return list;} }

總結

以上是生活随笔為你收集整理的springboot vue导出excel 使用easypoi的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。