一,写操作
1,导入pom依赖
<!--easyExcel依赖-->
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
<!-- EasyExcel所依赖的Poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
2,创建实体类
注意:实体类的属性要对应Excel表中的表头字段
@Data
public class UserData {
@ExcelProperty("用户编号",index=0)//该注解设置Excel的表头名
//@ColumnWidth(15) //设置内容的宽度
private int uid;
@ExcelProperty("用户名",index=1)
private String userName;
}
3,实现写操作
public static void main(String[] args) {
//创建list集合对象
List<UserData> list = new ArrayList();
for (int i = 0; i < 10; i++) {
UserData data = new UserData();
data.setUid(i);
data.setUserName("lucy"+i);
list.add(data);
}
//写法一:文件流自动关闭
//设置excel文件路径和名称
String fileName = "D:\\excel\\01.xlsx";
//调用方法实现读写
EasyExcel.write(fileName,UserData.class).sheet("用户信息")
.doWrite(list);
//写法二:文件流手动关闭
String fileName = "D:\\excel\\01.xlsx";
// 这里 需要指定写用哪个class去写
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, Person.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
excelWriter.write(data(), writeSheet);
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
二,读操作
1,导入依赖,和写一样
2,创建实体类,和写一样
3,创建一个监听器ExcelListener.java继承AnalysisEventListListener<T> ,并实现所要的方法
public class ExcelListener extends AnalysisEventListener<UserData> {
//一行一行的读取excel里的内容,从第二行开始读取(第一行表头不会读取)
@Override
public void invoke(UserData userData, AnalysisContext analysisContext) {
System.out.println("表格内容"+userData);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头信息:"+headMap);
}
//读取之后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("读取后执行操作。");
}
}
4,实现读操作
public static void main(String[] args) {
//读取文件的名称
String filename = "D:\\excel\\01.xlsx";
EasyExcel.read(filename,UserData.class,new ExcelListener())
.sheet().doRead();
}
三、接口开发
读操作(下载)
1.controller
//导出数据字典接口,传一个参数,用户选择导出的路径
@ApiOperation("导出数据字典")
@GetMapping("exportData")
public void exportData(HttpServletResponse response){
dictService.exportDictData(response);
}
2. service
//导出(下载)数据字典
@Override
public void exportDictData(HttpServletResponse response) {
//设置下载信息
response.setContentType("application/vnd.ms-excel");//设置content类型为excel类型
response.setCharacterEncoding("utf-8");//设置编码格
//设置下载的文件名,也可以是中文
String fileName = "dict";
//设置中文文件名要加编码格式,捕捉异常
//String fileName1 = URLEncoder.encode("数据自带你","UTF-8");
//设置头部信息,"Content-disposition"设置操作为以下载的发送打开
response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
//查询数据库
List<Dict> dictList= baseMapper.selectList(null);
//Dict ——> DictEeVo
List<DictEeVo> dictEeVoList = new ArrayList<>();
for (Dict dict : dictList) {
DictEeVo dictEeVo = new DictEeVo();
BeanUtils.copyProperties(dict,dictEeVo);//等价于dictEeVo.setId(dict.getId())
dictEeVoList.add(dictEeVo);
}
//调用方法进行写操作,用流
try {
EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("dict")
.doWrite(dictEeVoList);
} catch (IOException e) {
e.printStackTrace();
}
}
3.实现:直接用浏览器路径访问http://localhost:8202/admin/cmn/dict/exportData,不建议用swagger
写操作 (上传)
1.controller
//导入数据字典接口
@ApiOperation("导入(上传)数据字典")
@PostMapping("upload")
public Result uploadDict(MultipartFile file){
if (ObjectUtils.isEmpty(file)) return Result.fail("不能为空");
dictService.uploadDict(file);
return Result.ok();
}
2.service
//导入(上传)数据字典
@Override
public void uploadDict(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper))
.sheet()
.doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
3.创建监听器
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) {
}
}