EasyExcel
引入依赖
添加EasyExcel依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
对应于Excel中的列设置实体类
@Data
public class UserData {
//在这个注解下标注Excel文件中的表名
@ExcelProperty("用户id")
private int uid;
@ExcelProperty("用户名")
private String username;
}
写操作的实现
public class TestWrite {
public static void main(String[] args) {
//要传入的数据(封装为实体类)
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:\\Learning_Java\\Project\\SYT\\Excel\\01.xlsx";
//调用方法实现写操作
//.write中传入文件路径以及实体类的反射对象,、
//.sheet中传入Excel中的sheet名
//.doWrite中传入之前创建的实体类的集合
EasyExcel.write(fileName, UserData.class).sheet("用户信息").doWrite(list);
}
}
读操作的实现
创建ExcelListener监听器
创建监听器并继承AnalysisEventListener,重写其方法
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) {
}
}
读操作的调用:
public class TestRead {
public static void main(String[] args) {
//读取文件路径
String fileName = "D:\\Learning_Java\\Project\\SYT\\Excel\\01.xlsx";
//调用读方法实现读取操作
EasyExcel.read(fileName, UserData.class, new ExcelListener()).sheet().doRead();
}
}
案例:实现一个写Excel的功能
Controller层中:
//导出数据字典的接口
@GetMapping("exportData")
public Result exportDict(HttpServletResponse response) {
dictService.exportDictData(response);
return Result.ok();
}
Service层中:
void exportDictData(HttpServletResponse response);
ServiceImpl中:
//导出Excel信息
@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 + ".xlsx");
//查询数据库
List<Dict> dictList = baseMapper.selectList(null);
//将Dict对象转换成DictEeVo对象再进行处理
//创建一个DictEeVo的List
List<DictEeVo> dictVoList = new ArrayList<>();
for (Dict dict : dictList) {
DictEeVo dictEeVo = new DictEeVo();
BeanUtils.copyProperties(dict, dictEeVo);
dictVoList.add(dictEeVo);
}
//调用方法进行写操作,第一个参数传输出流,第二个参数传Excel表的实体类的反射,.sheet是sheet名,doWrite传入的是写入的列表
try {
EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("dict").doWrite(dictList);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
案例:实现读Excel的功能
Controller层:
//导入数据字典的接口
@PostMapping("importData")
public Result importDict(MultipartFile file) {
dictService.importDictData(file);
return Result.ok();
}
Service层:
void importDictData(MultipartFile file);
ServiceImpl中(注意这里需要一个监听器):
@Override
public void importDictData(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(), DictEeVo.class, new DictListener(baseMapper)).sheet().doRead();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
监听器类,创建在listen文件夹下:
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) {
}
}