1.导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
2.修改实体类
给实体类加上注解。@ExcelProperty(index,value),index指定位置，value指定列名，@ExcelIgnore则是在操作时忽略该字段。
public class EduTeacher{
//讲师ID
@ExcelProperty(index = 0, value = "id")
private String id;
//讲师姓名
@ExcelProperty(index = 1, value = "姓名")
private String name;
//讲师简介
@ExcelProperty(index = 2, value = "讲师简介")
@ExcelIgnore // 忽略该字段
private String intro;
//讲师资历,一句话说明讲师
@ExcelProperty(index = 3, value = "讲师资历")
private String career;
//头衔 1高级讲师 2首席讲师
@ExcelProperty(index = 4, value = "头衔")
private Integer level;
//讲师头像
@ExcelProperty(index = 5, value = "讲师头像")
private String avatar;
//排序
@ExcelProperty(index = 6, value = "讲师头像")
private Integer sort;
//逻辑删除 1（true）已删除， 0（false）未删除
@ExcelProperty(index = 7, value = "讲师头像")
private Integer isDeleted;
//创建时间
@ExcelProperty(index = 8, value = "讲师头像")
@TableField(fill = FieldFill.INSERT)
private Date gmtCreate;
//更新时间
@ExcelProperty(index = 9, value = "讲师头像")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date gmtModified;
}
3.编写导入方法
public void export(HttpServletRequest request,HttpServletResponse response){
try {
List<EduTeacher> list = eduTeacherService.list(new QueryWrapper<>());
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + "excelName" + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), EduTeacher.class).sheet("导出测试123").doWrite(list);
} catch (Exception e) {
}
}
4.导入
4.1 配置一个导入监听类
public class eduExcelLinster extends AnalysisEventListener<EduTeacher> {
public EduTeacherService eduTeacherService;
private List<EduTeacher> list = new ArrayList<EduTeacher>();
public eduExcelLinster(EduTeacherService eduTeacherService){
this.eduTeacherService = eduTeacherService;
}
@Override
public void invoke(EduTeacher eduTeacher, AnalysisContext analysisContext) {
// 这里是一条一条数据执行的
list.add(eduTeacher);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 最后在这里批量插入
eduTeacherService.saveBatch(list);
}
}
4.2 编写导入方法
@RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
@ResponseBody
public void importMemberList(@RequestPart("file") MultipartFile file) {
try {
InputStream fileInputStream = file.getInputStream();
EasyExcel.read(fileInputStream, EduTeacherExcel.class, new eduExcelLinster(eduTeacherService))
.sheet().doRead();
}catch (Exception e) {
}
}
5.类型转换
下面这种数据库存的值是int，但需要导出String怎么办呢？
//头衔 1高级讲师 2首席讲师
@ExcelProperty(index = 4, value = "头衔")
private Integer level;
5.1 定义转换类
public class GenderConverter implements Converter<Integer> {
//在java中性别是用 0 1 来标识的 所以是int
@Override
public Class supportJavaTypeKey() {return Integer.class;}
// 在excel中是男女 所以是string
@Override
public CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}
//将excel的数据类型转为java数据类型
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String stringValue = cellData.getStringValue();
if (stringValue == null) {
throw new RuntimeException("暂无头衔");
}
if ("高级讲师".equals(stringValue)) {
return 1;
}
return 2;
}
//将java的数据类型转为excel数据类型
@Override
public CellData convertToExcelData(Integer s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (s == 1){
return new CellData("高级讲师");
}
return new CellData("首席讲师");
}
}
5.2 添加注解
//头衔 1高级讲师 2首席讲师
@ExcelProperty(index = 4, value = "头衔",converter = GenderConverter.class)
private Integer level;
