首先,在maven项目的pom.xml引入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
excel的导出:
controller层:
@GetMapping("exportData")
public void exportDict(HttpServletResponse response){
dictService.exportDictData(response);
}
服务层:
void exportDictData(HttpServletResponse response);
服务实现层:
//导出数据字典
@Override
public void exportDictData(HttpServletResponse response) {
//设置下载信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "dict";
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
//查询数据库
List<Dict> dictList = baseMapper.selectList(null);
//把Dict对象转换成DictEeVo对象
List<DictEeVo> dictVoList = new ArrayList<>(dictList.size());
for(Dict dict :dictList){
DictEeVo dictEeVo = new DictEeVo();
BeanUtils.copyProperties(dict,dictEeVo);
dictVoList.add(dictEeVo);
}
//调用方法进行写操作
try {
EasyExcel.write(response.getOutputStream(), DictEeVo.class)
.sheet("dict")
.doWrite(dictVoList);
} catch (IOException e) {
e.printStackTrace();
}
}
注意:采用swagger测试接口时,下载下来的文件不可打开,如果直接在浏览器输入对应的接口地址,文件可以下载,下载后文件可读可写。
导入文件:
首先,建立一个listener包,在其下建立相关类(注意采用了构造方法):
package com.atguigu.yygh.cmn.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.yygh.cmn.mapper.DictMapper;
import com.atguigu.yygh.model.cmn.Dict;
import com.atguigu.yygh.vo.cmn.DictEeVo;
import org.springframework.beans.BeanUtils;
public class DictListener extends AnalysisEventListener<DictEeVo> {
private DictMapper dictMapper;
public DictListener(DictMapper dictMapper) {
this.dictMapper = dictMapper;
}
//一行一行开始读取,从第二行开始
@Override
public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
Dict dict = new Dict();
BeanUtils.copyProperties(dictEeVo,dict);
dictMapper.insert(dict);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
Controller层:
使用post上传方法,文件上传方式使用MultipartFile:
//导入数据字典
@PostMapping("importData")
public Result importData(MultipartFile file){
dictService.importDictData(file);
return Result.ok();
}
Sercice层:
void importDictData(MultipartFile file);
ServiceImpl(这里的方法用到了listener相关类):
//导入数据字典
@Override
@CacheEvict(value = "dict", allEntries=true)
public void importDictData(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}