1.文件导入
- 导入校验
public class BoyListener extends AnalysisEventListener {List<String> names = new ArrayList<>();/*** 每解析一行，回调该方法**/@Overridepublic void invoke(Object data, AnalysisContext analysisContext) {//校验名称String name = ((Boy) data).getName();if (StringUtils.isBlank(name)){throw new ServiceException(1,"名称不能为空");}if (names.contains(name)){throw new ServiceException(1,"名称已存在");}else {names.add(name);}}/*** 解析异常回调该方法* @param exception* @param context* @throws Exception*/@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {if (exception instanceof ExcelDataConvertException){//从0开始ExcelDataConvertException ex= (ExcelDataConvertException) exception;//获取行号int rowIndex = ex.getRowIndex() + 1;//获取列号int colIndex = ex.getColumnIndex() + 1;throw new ServiceException(1,"第"+rowIndex+"行，第"+colIndex+"列数据格式错误");} else if (exception instanceof RuntimeException) {throw exception;}else {super.onException(exception, context);}}/*** 解析完回调该方法* @param analysisContext*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {names.clear();}
}
- 导入代码
@PostMapping("/in")public void importIn(MultipartFile file) {try {InputStream fileInputStream = file.getInputStream();List<Boy> sheet1 = EasyExcel.read(fileInputStream).registerReadListener(new BoyListener())//导入的实体类型.head(Boy.class).sheet("Sheet1")//跳过第一行，头行.headRowNumber(1).doReadSync();if (!CollectionUtils.isEmpty(sheet1)){for (Boy boy : sheet1) {System.out.println(JSON.toJSONString(boy));}}} catch (IOException e) {throw new RuntimeException(e);}}2.导出
- 包括导出到本地、响应、文件服务器以及多个sheet导出。
@PostMapping("/out")public void exportOut(HttpServletResponse response) {try {ServletOutputStream outputStream = response.getOutputStream();this.setExcelResponse(response,"测试导出");//创建一个list用于存放30个Boy对象-适用于单个sheet
// List<Boy> boyList = new java.util.ArrayList<>();
// for (int i = 0; i < 30; i++) {
// Boy boy = new Boy();
// boy.setId(i);
// boy.setName("张三" + i);
// boy.setSalary((double) (i + 1));
// boy.setBirthday(LocalDateTime.now());
// boyList.add(boy);
// }//写出到服务器
// HashMap<String, Object> paramMap = new HashMap<>();//文件上传只需将参数中的键指定（默认file），值设为文件对象即可，对于使用者来说，文件上传与普通表单提交并无区别
// paramMap.put("Filedata", FileUtil.file(filePath));
// String result = HttpUtil.post("http://" + url, paramMap);//写出至本地
// EasyExcel.write("src/main/resources/export.xlsx", Boy.class)
// .sheet("Sheet1")
// .doWrite(boyList);//写出至浏览器
// EasyExcel.write(outputStream, Boy.class)
// .excelType(ExcelTypeEnum.XLSX)
// .sheet("Sheet1")
// .doWrite(boyList);//多个sheet导出ExcelWriter writer = EasyExcel.write(outputStream, Boy.class).excelType(ExcelTypeEnum.XLSX).build();try {this.setExcelResponse(response, "多个sheet导出");//分页查询数据for(int j=1;j<=5;j++){List<Boy> boyList = new ArrayList<>();for (int i = 0; i < 30; i++) {Boy boy = new Boy();boy.setId(i);boy.setName("张三" + i);boy.setSalary((double) (i + 1));boy.setBirthday(LocalDateTime.now());boyList.add(boy);}//创建新的sheet页WriteSheet sheet = EasyExcel.writerSheet("Sheet" + j).build();writer.write(boyList,sheet );}} catch (IOException e) {throw new RuntimeException(e);//释放资源}finally {writer.finish();outputStream.flush();outputStream.close();}} catch (IOException e) {throw new RuntimeException(e);}}private void setExcelResponse(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {//设置内容类型response.setContentType("application/vnd.ms-excel");//设置编码格式response.setCharacterEncoding("utf-8");//设置导出文件名称String fileName = URLEncoder.encode(rawFileName.concat(".xlsx"), "UTF-8");//设置响应头response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);}
参考文献：https://blog.csdn.net/m0_51963973/article/details/131054664?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0-131054664-blog-142551367.235v43pc_blog_bottom_relevance_base2&spm=1001.2101.3001.4242.1&utm_relevant_index=3