Ajax + Easy Excel 通过Blob实现导出excel
前端代码
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<script src="./js/jquery-3.6.0.min.js"></script>
</head>
<body>
<div>
<button onclick="exportFile()">导出</button>
</div>
<script>
function exportFile(){
var params = {};
var url = "http://localhost:9000/exportFile/export"
getExport(url, function (res) {
// console.log(res)
downloadForBolb(res,"学生表.xls",'application/vnd.ms-excel; charset=utf-8')
})
}
function getExport(url, fn, errorCallback) {
$.ajax({
type: "GET",
url: url,
xhrFields: {
responseType: "arraybuffer",
},
error: function (XHR, textStatus, errorThrown) {
console.log("XHR=" + XHR + "
textStatus=" + textStatus + "
errorThrown=" + errorThrown);
if (errorCallback && typeof errorCallback == "function") {
alert("error", "系统异常，请联系管理员！");
errorCallback({});
}
},
success: function (data, textStatus) {
if (fn && typeof fn == "function") {
fn(data);
}
},
headers: {
"Authorization": window.sessionStorage.getItem("token")
}
});
};
function downloadForBolb(data, fileName, mineType) {
// 创建 blob
var blob = new Blob([data], {type: mineType});
// 创建 href 超链接，点击进行下载
window.URL = window.URL || window.webkitURL;
var href = URL.createObjectURL(blob);
var downA = document.createElement("a");
downA.href = href;
downA.download = fileName;
downA.click();
// 销毁超连接
window.URL.revokeObjectURL(href);
};
</script>
</body>
</html>
后台代码
- 引入Easy Excel依赖
// pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.1</version>
</dependency>
- excel 工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {
/**
* 将列表以 Excel 响应给前端
*
* @param response 响应
* @param filename 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表哦
* @param 泛型，保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
public static <T> void write(HttpServletResponse response, String filename, String sheetName,
Class<T> head, List<T> data) throws IOException {
// 输出 Excel
EasyExcel.write(response.getOutputStream(), head)
.autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
.sheet(sheetName).doWrite(data);
// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
}
public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
return EasyExcel.read(file.getInputStream(), head, null)
.autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
.doReadAllSync();
}
}
- 实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 1)
private int age;
@ExcelProperty(value = "地址", index = 2)
private String address;
}
- Controller
import com.boot.export.entity.Student;
import com.boot.util.ExcelUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/exportFile")
public class exportFile {
/**
* /exportFile/export
* @param params
* @param request
* @param response
*/
@RequestMapping("export")
public void export(Map<String, Object> params, HttpServletRequest request, HttpServletResponse response){
try {
// 获取list
List<Student> list = new ArrayList<>();
list.add(new Student("刘大海",54,"桃李村1号"));
list.add(new Student("王大富",34,"桃李村4号"));
list.add(new Student("王婆婆",82,"桃李村6号"));
list.add(new Student("李复",24,"桃李村9号"));
list.add(new Student("陈月",14,"桃李村3号"));
list.add(new Student("老村长",74,"桃李村3号"));
// 导出 Excel
ExcelUtil.write(response, "学生信息.xls", "数据", Student.class, list);
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}
}
Blob文件类型
