第一步当然是引入依赖啦
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
导入封装
/**
* 导入excel公共方法
* @param file 文件
* @param clazz 映射对象
* @param sheetName 读取的sheet名称
* @return
* @param <T>
* @throws IOException
*/
public static <T> List<T> uploadExcel(MultipartFile file, Class<T> clazz, String sheetName) throws IOException {
if (file == null || file.isEmpty()) {
throw new IllegalArgumentException("上传文件不能为空");
}
if (clazz == null) {
throw new IllegalArgumentException("映射类不能为空");
}
if (sheetName == null || sheetName.trim().isEmpty()) {
throw new IllegalArgumentException("工作表名称不能为空");
}
List<T> list = new ArrayList<>();
try(InputStream fileStream = file.getInputStream()) {
// 构建ExcelReader, 读取的每一行数据放入mpf中
ExcelReader reader = EasyExcel.read(fileStream, clazz, new AnalysisEventListener<T>() {
@Override
public void invoke(T t, AnalysisContext analysisContext) {
list.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("解析数据完成");
}
}).build();
// 读取Excel文件的指定sheet页
ReadSheet sheet = EasyExcel.readSheet(sheetName).build();
try{
reader.read(sheet);
}finally {
reader.finish();//确保流能正常的关闭，不会造成资源泄漏
}
} catch (Exception e) {
throw new IOException("解析excel文件失败:"+e.getMessage(),e);
}
if (CollectionUtils.isEmpty(list)) {
throw new IOException("解析excel文件失败,未读取到有效数据");
}
return list;
}
导出方法封装
/**
* 导出excel公共方法
* @param response
* @param fileName 文件名
* @param clazz 映射对象类
* @throws IOException
*/
public static <T> void exportExcelFile(HttpServletResponse response, String fileName, Class<T> clazz,List<T> list) throws IOException {
if (org.apache.commons.lang3.StringUtils.isEmpty(fileName)) {
fileName = "测试-" + getNowTime();
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz)
.sheet("数据").needHead(true).doWrite(list);
}
private static String getNowTime() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
return sdf.format(date);
}
映射对象类
映射对象就是你定义的excel实体类，如下所示
@Data
public class PointLocationUploadFileVo {
@ExcelProperty(value = "名称",index = 0)
private String nickName;
@ExcelProperty(value = "类型",index = 1)
private String type;
@ExcelProperty(value = "点位地址",index = 2)
private String pointLocation;
@ExcelProperty(value = "读写状态,0:只读，1：读写",index = 3)
private Integer rwStatus;
@ExcelProperty(value = "是否导入",index = 4)
private String is_insert;
}
那么封装完成了，如何调用呢？
导入文件
public void uploadExcelFile(MultipartFile file) throws IOException {
// 调用公共方法实现读取文件
List<PointLocationUploadFileVo> mpf = EasyExcelUtils.uploadExcel(file, PointLocationUploadFileVo.class, "Sheet1");
List<OldPortPointLocationDo> pointLocationDos = new ArrayList<>();
// 对读取的数据进行清洗操作。。。。。
//插入数据库操作
if (CollectionUtils.isNotEmpty(pointLocationDos)) {
tetsMapper.insertBatch(pointLocationDos);
}
}
导出文件
public void export(String fileName, HttpServletResponse response) throws IOException {
// 先查询处你想要导出的数据
List<PointLocationUploadFileVo> pointLocationUploadFileVos = testMapper.selectList(null);
EasyExcelUtils.exportExcelFile(response,fileName,PointLocationUploadFileVo.class,pointLocationUploadFileVos);
}
