pom
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
导出excel
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("模板").doWrite(data());
}
public List data(){
List<DemoData> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("a"+i);
data.setDoubleData(0.12);
data.setDate(new Date());
list.add(data);
}
return list;
}
导入excel
1 监听器
@Slf4j
@AllArgsConstructor
public class DemoDataLister extends AnalysisEventListener<DemoData> {
private static final Integer BATCH_SIZE = 100;
private DemoDao demoDao;
private static List<DemoData> list = new ArrayList<>();
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
// 每一行数据都会经过这个方法
if (list.size() >= BATCH_SIZE){
demoDao.save(list);
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 最后做的动作
demoDao.save(list);
}
}
2 controller层调用 EasyExcel 的 api 会找监听器
@PostMapping("insert")
public Result insert(){
String path = "C:\\Users\\1\\Desktop\\log\\log1675662978112.xlsx";
try{
EasyExcel.read(path,new DemoDataLister(demoDao)).doReadAll();
return Result.ok().code(ResultCodeEnum.SUCCESS.getCode());
}catch (RuntimeException e){
throw new MyException(ResultCodeEnum.CODE_ERROR.getCode(),"出现错误",e);
}
用到的实体类DemoData
@Getter
@Setter
@EqualsAndHashCode
public class DemoData implements Serializable{
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}