控制层
@PostMapping("test/excel/import")
public void modelImport(MultipartFile serviceFile) throws IOException {
//输入流
InputStream inputStream = serviceFile.getInputStream();
//监视器
ExcelListener listener = new ExcelListener();
ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
// 第一个sheet读取类型
ReadSheet readSheet1 = EasyExcel.readSheet(0).head(DictEeVo.class).build();
// 第二个sheet读取类型
ReadSheet readSheet2 = EasyExcel.readSheet(1).head(ExpertVo.class).build();
// 开始读取第一个sheet
excelReader.read(readSheet1);
//excel sheet0 信息
List<Object> list = listener.getDatas();
//List<object> 转 List<实体类>
List<Dict> dtoList = new ArrayList<>();
//List object for 转换 实体类
for (Object objects : list) {
DictEeVo dto = (DictEeVo) objects;
Dict dict = new Dict();
BeanUtil.copyProperties(dto,dict);
dtoList.add(dict);
}
//List 转JOSN
String json = JSON.toJSONString(dtoList);
System.out.println("json = " + json);
//保存第一个sheet页中的数据
dictService.saveBatch(dtoList);
// 清空之前的数据
listener.getDatas().clear();
// 开始读取第二个sheet
excelReader.read(readSheet2);
//excel sheet1 信息
List<Object> entry = listener.getDatas();
//copy上面作法
//List<object> 转 List<实体类>
List<Dict> dtoList22 = new ArrayList<>();
//List object for 转换 实体类
List<Dict> dtoList1 = new ArrayList<>();
//List object for 转换 实体类
for (Object objects : list) {
ExpertVo dto = (ExpertVo) objects;
Dict dict = new Dict();
BeanUtil.copyProperties(dto,dict);
dtoList1.add(dict);
}
//保存第二个sheet页中的数据
dictService.saveBatch(dtoList1);
//List 转JOSN
String json1 = JSON.toJSONString(dtoList);
System.out.println("json2222 = " + json1);
}
业务层
万能监听器
package com.atguigu.yygh.cmn.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* excel表格读取监视器
*/
public class ExcelListener extends AnalysisEventListener {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);
doSomething(o);
}
private void doSomething(Object object) {
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
javabean
package com.atguigu.yygh.vo.cmn;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;
import java.util.Date;
/**
* <p>
* Dict
* </p>
*
* @author qy
*/
@Data
public class ExpertVo {
@ExcelProperty(value = "id")
@ExcelIgnore
private Long id;
@ExcelProperty(value = "名称")
private String name;
@ExcelProperty(value = "日期")
private Date date;
}
postman
excel中的数据结构
数据库结果