本篇文章为你整理了前后端分离下EasyExcel的使用(前后端分离的项目怎么运行)的详细内容,包含有前后端分离 remember me 前后端分离的项目怎么运行 前后端分离如何提交表单 前后端分离数据格式 前后端分离下EasyExcel的使用,希望能帮助你了解 前后端分离下EasyExcel的使用。
@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 {{ 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 {{ 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 {{ scope.row.ratio }} /span
/template
/el-table-column
el-table-column label="缴纳费用" width="180"
template slot-scope="scope"
span {{ scope.row.comPayment }} /span
/template
/el-table-column
/el-table-column
el-table-column label="备注" width="120"
template slot-scope="scope"
span {{ scope.row.remarks }} /span
/template
/el-table-column
/el-table-column
以上就是前后端分离下EasyExcel的使用(前后端分离的项目怎么运行)的详细内容,想要了解更多 前后端分离下EasyExcel的使用的内容,请持续关注盛行IT软件开发工作室。
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。