记录一下今天导出Excel的过程
后端
1、添加依赖
com.wuwenze
ExcelKit
2.0.72
xml-apis
xml-apis
1.4.01
2、创建VO类,并添加字段注解
@Data
@Excel(value = "咨询")
public class ConsultationExport {
/** 行业 */
@ExcelField(value = "行业", name="industry")
private String industry;
/** 服务 */
@ExcelField(value = "服务", name="service")
private String service;
......
3、使用ExcelKit类导出数据
@PostMapping("export")
@ApiOperation("导出")
public void export(@RequestBody ExportParam exportParam, HttpServletResponse response) throws IOException {
List consultationList = consultationService.selectByExportParam(exportParam);
ExcelKit.$Export(ConsultationExport.class, response).downXlsx(consultationList, false);
}
我整理了一个导入导出的工具类如下
public class ExcelKitUtils {
/**
* 下载文档
* @param clazz
* @param response
* @param target
* @param isTemplate
*/
public static void download(Class clazz, HttpServletResponse response, List target, boolean isTemplate){
if (Objects.isNull(clazz) || CollectionUtils.isEmpty(target)){
return;
}
ExcelKit.$Export(clazz,response).downXlsx(target, isTemplate);
}
/**
* 上传文档
*/
public static List upload(Class T, MultipartFile file) throws IOException {
ArrayList successList = Lists.newArrayList();
ArrayList> errorList = Lists.newArrayList();
ExcelKit.$Import(T).readXlsx(file.getInputStream(), new ExcelReadHandler() {
@Override
public void onSuccess(int sheetIndex, int rowIndex, T t) {
successList.add(t); // 单行读取成功,加入入库队列。
}
@Override
public void onError(int sheetIndex, int rowIndex, List errorFields) {
// 读取数据失败,记录了当前行所有失败的数据
HashMap map = new HashMap<>();
map.put("sheetIndex", sheetIndex);
map.put("rowIndex", rowIndex);
map.put("errorFields", errorFields);
errorList.add(map);
}
});
if ( !CollectionUtils.isEmpty(errorList)){
throw new GlobalException(CodeMsg.UPLOAD_SAVEBATCH);
}
return successList;
}
}
VUE 前端
此时虽然后端返回了流数据,但是我们用PostMan 接收了看来都是一串看不懂的乱码,需要前端处理一下。虽然vue里有封装好的请求接口的方法,但这里要单独用axios,我创建了一个util.js中的方法单独处理这个导出的逻辑,记得先导入axios import axios from 'axios'
export function exportSth(data) {
axios({
method: 'POST',
url: `http://localhost:8080/web/consultation/export`,
data: data.params,
responseType: 'blob'
}).then((res) => {
const link = document.createElement('a')
let blob = new Blob([res.data], {type: 'application/vnd.ms-excel'})
link.style.display = 'none'
link.href = URL.createObjectURL(blob)
// link.download = res.headers['content-disposition'] //下载后文件名
link.download = data.fileName //下载的文件名
document.body.appendChild(link)
link.click()
document.body.removeChild(link)
}).catch(error => {
this.$message.error({ title: '错误', desc: '网络连接错误' })
console.log(error)
})
}
最后在具体的模块中调用该方法即可
performExport () {
if (!this.preTimes || this.preTimes.length < 1) {
this.$alert('请选择时间范围', '警告', {
confirmButtonText: '确定',
callback: action => { }
})
return
}
let param = {}
param.startTime = new Date(this.preTimes[0]).getTime()
param.endTime = new Date(this.preTimes[1]).getTime()
param.hasConnectionInfo = parseInt(this.exportForm.hasConnectionInfo)
let data = { fileName: '咨询统计', params: param }
exportSth(data)
this.exportDialogVisible = false
}