1、导入pom依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
先实现导出:
2、Controller类
@ApiOperation(value = "通过uuid导出")
@GetMapping(value = "/{uuid}")
public void exportExcel(@PathVariable("uuid") String uuid,
HttpServletResponse response) throws IOException {
try {
//导出的文件名前缀,尽量不要有中文
String prefix = "Aps";
//导出的文件名前缀中的日期
String today = new SimpleDateFormat("-yyyy-MM-dd").format(DateUtils.StartOfToday());
//拼在一起,导出为Aps-2021-08-26.xlsx
String filename = prefix.concat(today);
//MSEXCEL格式
response.setContentType("application/vnd.ms-excel");
//指定的类型是文件的扩展名xlsx
response.setHeader("Content-disposition",
"attachment; filename=" + filename + ExcelTypeEnum.XLSX.getValue());
//实现方法,需要加入到excel中的数据
apsService.writeToExcel(uuid, response.getOutputStream());
} catch (IOException e) {
log.error(String.format("导出异常:%s ", e.getMessage()));
}
}
3、ServiceImpl实现类(Service省略)
@Override
@Synchronized
public void outTemplate(String uuid,OutputStream outputStream) throws IOException {
//通过uuid找到导出的数据
List<Aps> apsList = aps.findbyuuid(uuid);
ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
//指定excel按照Aps类来显示
Sheet sheet1 = new Sheet(1, 0, Aps.class);
//将apsList放入sheet1中
writer.write(apsList, sheet1);
//写入完毕后关闭输出流
writer.finish();
outputStream.flush();
outputStream.close();
}
4、model类
@Data
@NoArgsConstructor
@AllArgsConstructor
//单元格宽度
@ColumnWidth(21)
//设置单元格数据居中
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
//设置标题格式,默认居中,背景色为灰色
@HeadStyle()
public class Aps extends BaseRowModel {
//导入Excel,index表示第几列
@ExcelProperty(value = "开始日期(2021/07/27)", index = 0)
private String beginDate;
@ExcelProperty(value = "开始时间(18:30)", index = 1)
private String beginTime;
@ExcelProperty(value = "名称", index = 2)
private String name;
}
再来看导入:
2、Controller类
@ApiOperation(value = "导入")
@ApiImplicitParam(name = "file", value = "MultipartFile", paramType = "file", required = true)
@PostMapping(value = "/import")
//RestResponse是自己写返回类
public RestResponse<String> excelUpload(@RequestParam("file") MultipartFile file) {
if (file.isEmpty()) {
return RestResponse.bad(-1, "file empty");
}
//后缀如果为xlsx或者xls才能导入
if (!file.getOriginalFilename().endsWith(ExcelTypeEnum.XLSX.getValue()) &&
!file.getOriginalFilename().endsWith(ExcelTypeEnum.XLS.getValue())) {
return RestResponse.bad(-1, "only excel file support");
}
File dest = new File(String.format("%s/upload-%s", UPLOAD_DEST_DIR, file.getOriginalFilename()));
try {
//调用自己写的方法,传入导入的文件
apsService.saveApsScheduleFromExcel(file, dest);
return RestResponse.good("ok");
} catch (Exception e) {
log.error("upload ERROR:" + e.getMessage());
return RestResponse.bad(500, "Internal Exception");
}
}
3、ServiceImpl实现类(Service省略)
@Override
public void saveApsScheduleFromExcel(MultipartFile file, File dest) throws IOException, ParseException, MAPEInputException {
file.transferTo(dest);
FileInputStream inputStream = new FileInputStream(dest);
//ExcelListener为自己写的监听类
ExcelListener listener = new ExcelListener();
ExcelReader excelReader = new ExcelReader(inputStream, null, listener);
//Aps为导入的数据对应的类
excelReader.read(new Sheet(1, 1, Aps.class));
//表格信息转换为list
List<Object> data = listener.data;
//excel中每行的数据封装为data,excel有多少条数据(行),就有多少个data
if (data.size() > 0) {
data.forEach(obj -> {
.......
//做一系列操作
});
//读取完后清除list中的数据
listener.clearData();
}else {
log.error("导入错误,数据为空");
}
4、ExcelListener类
/**
* 解析监听器,
* 每解析一行会回调invoke()方法。
* 整个excel解析结束会执行doAfterAllAnalysed()方法
*/
@Slf4j
public class ExcelListener extends AnalysisEventListener {
public List<Object> data = new ArrayList<>();
@Override
public void invoke(Object object, AnalysisContext context) {
System.out.println(object);
data.add(object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不用的资源
}
public void clearData() {
data.clear();
}
}