添加 pom 依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
一、导入 Excel
1. 控制器中接口代码
@PostMapping("/importExcel")
public R<?> importExcel(){
String path = "C:\\Users\\YKT\\Desktop\\临时\\car.xls";
ExcelReader build = EasyExcel.read(path,Excel.class,new ExcelListener(excelMapper)).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
build.read(readSheet);
build.finish();
return R.ok("导入成功!");
}
2. 监听器 ExcelListener
package com.example.car.carmall.Listenner;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.car.carmall.domain.Excel;
import com.example.car.carmall.mapper.ExcelMapper;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelListener extends AnalysisEventListener<Excel> {
ExcelMapper mapper;
public ExcelListener(ExcelMapper mapper){
this.mapper = mapper;
}
@Override
public void invoke(Excel excel, AnalysisContext analysisContext) {
SimpleDateFormat dfs = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
System.out.println(dfs.format(new Date()) +" ====== "+excel);
mapper.insertIntoExcel(excel);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("finish");
}
}
二、导出 Excel
1. Excel 实体类 ,字段表数据库表中的字段保持一致
package com.example.car.carmall.domain;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "out_car_manage")
public class Excel implements Serializable {
private static final long serialVersionUID = 256489227461452689L;
@ExcelProperty(index = 0,value = "数据编号")
private String id;
@ExcelProperty(index = 1,value = "车牌")
private String carNo;
@ExcelProperty(index = 2,value = "车型")
private String carType;
@ExcelProperty(index = 3,value = "车辆来源")
private String carResources;
@ExcelProperty(index = 4,value = "借调电话")
private String carPhone;
@ExcelProperty(index = 5,value = "借调时长")
private String day;
@ExcelProperty(index = 6,value = "价格")
private String price;
@ExcelProperty(index = 7,value = "使用天数")
private String useTime;
@ExcelProperty(index = 8,value = "价格")
private String outPrice;
@ExcelProperty(index = 9,value = "订单总价")
private String totalPrice;
@ExcelProperty(index = 10,value = "顾客姓名")
private String custName;
@ExcelProperty(index = 11,value = "顾客电话")
private String custPhone;
@ExcelProperty(index = 12,value = "订单来源")
private String orderResource;
@ExcelProperty(index = 13,value = "目的地")
private String address;
@ExcelProperty(index = 14,value = "开始时间")
private String startTime;
@ExcelIgnore
private String createTime;
@ExcelProperty(index = 15,value = "结束时间")
private String endTime;
@ExcelProperty(index = 16,value = "押金")
private String deposit;
@ExcelProperty(index = 17,value = "押金退还时间")
private String backTime;
@ExcelProperty(index = 18,value = "备注")
private String remark;
@ExcelIgnore
private String status;
}
2. 控制器中逻辑代码
@PostMapping("/export")
public R<?> export(){
String path = "C:\\Users\\YKT\\Desktop\\临时\\car.xls";
List<Excel> list = excelService.getList();
ExcelWriter excelWriter = EasyExcel.write(path,Excel.class).build();
WriteSheet sheet = EasyExcel.writerSheet("out_car_manage").build();
excelWriter.write(list,sheet);
excelWriter.finish();
return R.ok("导出成功!");
}
以上代码可以完成使用 EasyExcel 进行导入导出,该内容是在B站上看视频学习 使用的,运用到实际的开发工作中,在此作为笔记记录一下,如有侵权,请联系删除