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