先引入依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
导出
@Override
public void exportDictData(HttpServletResponse response) {
try {
//设置下载信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("数据字典", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
//查询数据库
List<Dict> dictList = dictMapper.selectList(null);
//Dict-->DictEeVo
List<DictEeVo> dictEeVoList=new ArrayList<>();
for (Dict dict:dictList){
DictEeVo dictEeVo=new DictEeVo();
dictEeVo.setId(dict.getId());
BeanUtils.copyProperties(dict,dictEeVo);
dictEeVoList.add(dictEeVo);
}
//调用方法进行写操作
EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictEeVoList);
}catch (IOException e){
e.printStackTrace();
}
导入
@Override
public void importDictData(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(dictMapper)).sheet().doRead();
}catch (IOException e){
e.printStackTrace();
}
}
public class DictListener extends AnalysisEventListener<DictEeVo> {
private DictMapper dictMapper;
/**
* 因为AnalysisEventListener不能被Spring容器管理,所以需要有参构造器注入dictMapper
* @param dictMapper
*/
public DictListener(DictMapper dictMapper){
this.dictMapper=dictMapper;
}
/**
* 一行一行读取
* @param data
* @param context
*/
@Override
public void invoke(DictEeVo data, AnalysisContext context) {
//调用方法添加到数据库
Dict dict=new Dict();
//DictEeVo->Dict
BeanUtils.copyProperties(data,dict);
dictMapper.insert(dict);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}