前后端分离下EasyExcel的使用
项目环境:SpringBoot+Vue
依赖导入
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>
tips
3.0.1版本 @ColumnWidth失效问题 用其他版本即可
实体类关联Excel
@ExcelProperty：value属性可用来设置表头名称
@ExcelPropertyvalue属性可用来设置表头名称
点击查看代码
@TableName(value = "five_insurances")
@Data
public class FiveInsurances implements Serializable {
/**
*
*/
@ExcelProperty("编号")
@ColumnWidth(10)
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 工号
*/
@ExcelProperty("工号")
@ColumnWidth(15)
@TableField(value = "number")
private String number;
/**
* 姓名
*/
@ExcelProperty("姓名")
@ColumnWidth(20)
@TableField(value = "name")
private String name;
/**
* 部门id
*/
@ExcelProperty("部门")
@ColumnWidth(20)
@TableField(value = "dept_id")
private Integer deptId;
/**
* 电话
*/
@ExcelProperty("电话")
@ColumnWidth(20)
@TableField(value = "phone")
private String phone;
/**
* 缴纳基数
*/
@ExcelProperty({"社保", "缴纳基数"})
@ColumnWidth(20)
@TableField(value = "base_payment")
private String basePayment;
/**
* 个人缴纳
*/
@ExcelProperty({"社保", "个人", "缴纳费用"})
@ColumnWidth(20)
@TableField(value = "self_payment")
private String selfPayment;
/**
* 工伤保险缴纳比例
*/
@ExcelProperty({"社保", "企业", "工伤保险缴纳比例"})
@ColumnWidth(20)
@TableField(value = "ratio")
private String ratio;
/**
* 企业缴纳
*/
@ExcelProperty({"社保", "企业", "缴纳费用"})
@ColumnWidth(20)
@TableField(value = "com_payment")
private String comPayment;
/**
* 备注
*/
@ExcelProperty({"社保", "备注"})
@ColumnWidth(30)
@TableField(value = "remarks")
private String remarks;
@ExcelIgnore
@TableField(exist = false)
private Dept dept;
@ExcelIgnore
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
导出Excel
Controller
@GetMapping("/fihf")
@ApiOperation(value = "导出五险一金列表Excel")
@ApiImplicitParams(
@ApiImplicitParam(dataType = "Interger",name = "page",value = "page==-1:查询所有;page==-2,返回空模板",required = false)
)
public void exportList(HttpServletResponse response, @RequestParam(value = "page", defaultValue = "1") Integer page) throws Exception {
PageBean<List<FiveInsurances>> pageBean = fiveInsurancesService.selectFiveInsurancesList(page);
ExcelUtils.exportToWeb(response,"sheet1",FiveInsurances.class,pageBean.getData());
}
前端Axios请求
exportFile(page = this.pageBean.current) {
//复选框选中则设置page为-1,表示导出全部
if (this.checked) {
page = -1;
}
//关闭对话框
this.dialogVisible = false;
this.axios({
method: 'get',
url: baseURL + "fileExport/fihf",
params: {
page: page,
},
responseType: 'blob' //响应类型须设置为二进制文件流
}).then((res) => {
if (!res) {
return
}
const link = document.createElement("a");//创建a标签
let blob = new Blob([res.data], { type: "multipart/form-data" }); //设置文件类型
link.style.display = "none";
let url = URL.createObjectURL(blob);
link.href = url; //给a标签href属性赋值
link.setAttribute("download", decodeURI(Date.now() + '.xlsx'));
document.body.appendChild(link);//挂载a标签
link.click();//a标签click事件
document.body.removeChild(link); //移除a标签
window.URL.revokeObjectURL(url); //销毁下载链接
console.log(res);
this.checked = false;
return this.$message.success("导出报表数据成功!")
})
},
导入Excel
Controller
@PostMapping("/fihf")
@ApiOperation(value = "导入五险一金列表Excel")
public ResultVO<FiveInsurances> FiveInsurances(@RequestPart("file") MultipartFile file) throws IOException {
if (file.getSize() < 0) {
return ResultVO.createFail(404, "导入数据失败");
}
List<FiveInsurances> list = ExcelUtils.importFile(file, FiveInsurances.class);
//持久化到数据库
int i = fiveInsurancesService.insertBatch(list);
if (i > 0) {
return ResultVO.createSuccess("读取excel成功", null);
} else {
return ResultVO.createFail();
}
}
前端Axios请求
handleBeforUpload(file) {
console.log("beforeUpload", file);
//创建文件附件
let formData = new FormData();
//添加到formdata
formData.append("file", file);
this.axios({
method: 'post',
url: baseURL + "fileImport/fihf",
data: formData,
Headers: {
"Content-Type": "multipart/form-data",
}
}).then((res) => {
console.log("读取excel", res);
if (res.data.data == 10000) {
this.$message.success(res.data.msg)
}
})
},
Excel导入导出工具类
点击查看代码
package com.self.salarymanagement.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.util.IOUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
/**
* Excel工具类
*/
@Slf4j
public class ExcelUtils {
/**
* 导出Excel到指定路径下
*
* @param path 路径
* @param excelName Excel名称
* @param sheetName sheet页名称
* @param clazz Excel要转换的类型
* @param data 要导出的数据
*/
public static void exportFileToLocal(String path, String excelName, String sheetName, Class clazz, List data) {
String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
}
/**
* 导出Excel到web
*
* @param response 响应
* @param sheetName sheet页名称
* @param clazz Excel要转换的类型
* @param data 要导出的数据
* @throws Exception
*/
public static void exportToWeb(HttpServletResponse response, String sheetName, Class clazz, List data) throws Exception {
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder .encode可以防止中文乱码
String excelName = URLEncoder.encode(String.valueOf(System.currentTimeMillis()), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(data);
}
/**
* 导出Excel到web
*
* @param response 响应
* @param excelName Excel名称
* @param sheetName sheet页名称
* @param clazz Excel要转换的类型
* @param data 要导出的数据
* @throws Exception
*/
public static void exportToWeb(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
}
/**
* 将指定位置指定名称的Excel导出到web
*
* @param response 响应
* @param path 文件路径
* @param excelName 文件名称
* @throws UnsupportedEncodingException
*/
public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
if (!file.exists()) {
return "文件不存在！";
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
try (
FileInputStream in = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream();
) {
IOUtils.copy(in, out);
return "导出成功！";
} catch (Exception e) {
log.error("导出文件异常：", e);
}
return "导出失败！";
}
public static <T> List<T> importFile(MultipartFile file, Class<T> clazz) throws IOException {
return EasyExcel.read(file.getInputStream())
.head(clazz)
.registerReadListener(new DefaultExcelListener<T>())
.sheet()
.doReadSync();
}
}
Excel导入监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* @author Liu
* @create 2023-03-08-11:44
* @description:
*/
@Slf4j
public class DefaultExcelListener<T> extends AnalysisEventListener<T> {
private final List<T> rows = new ArrayList<>();
/**
* 读取excel操作
*
* @param obj 数据
* @param analysisContext 上下文
*/
// 每读一样，会调用该invoke方法一次
@Override
public void invoke(T obj, AnalysisContext analysisContext) {
//添加到list
rows.add(obj);
log.info("list容量" + rows.size() + obj);
/** 数据量不是特别大，可以不需要打开
// 实际数据量比较大时，rows里的数据可以存到一定量之后进行批量处理（比如存到数据库），
// 然后清空列表，以防止内存占用过多造成OOM
if(rows.size() >= 500){
log.info("存入数据库ing");
try {
Thread.sleep(3000);
} catch (InterruptedException e) {
e.printStackTrace();
}
rows.clear();
}
*/
}
/**
* 读取玩excel后的操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("成功读取【" + rows.size() + "】条数据");
System.out.println("================================");
rows.forEach(System.out::println);
System.out.println("================================");
}
/**
* 在读取excel异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
}
}
/**
* @return 返回读取的总数据
*/
public List<T> getRows() {
return rows;
}
}
多级表头设置
嵌套el-table-column即可
<el-table-column label="社保" width="180">
<el-table-column label="缴纳基数" width="120">
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.basePayment }}</span>
</template>
</el-table-column>
<el-table-column label="个人" width="120">
<el-table-column label="缴纳费用" width="120">
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.selfPayment }}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column label="企业" width="180">
<el-table-column label="工伤保险缴纳比例" width="180">
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.ratio }}</span>
</template>
</el-table-column>
<el-table-column label="缴纳费用" width="180">
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.comPayment }}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column label="备注" width="120">
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.remarks }}</span>
</template>
</el-table-column>
</el-table-column>
