当前位置：首页 > news >正文

SpringBoot集成阿里easyexcel（一）基础导入导出

news 来源：原创 2024/9/27 5:58:06

easyexcel主要用于excel文件的读写，可使用model实体类来定义文件读写的模板，对开发人员来说实现简单Excel文件的读写很便捷。可参考官方文档 https://github.com/alibaba/easyexcel

一、引入依赖

 <!-- 阿里开源EXCEL --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>${easyexcel.version}</version></dependency>

文档地址:https://www.yuque.com/easyexcel/doc/easyexcel

二、实体

普通导出实体，index的数字为导出的列，为0开始

@Data
public class ProjectExpView {@ExcelProperty(value = "项目编号"，index = 0)private String projectCode;@ExcelProperty(value = "项目名称",index = 1)private String projectName;
}

合并单元格导出实体

@Data
public class ProjectResourcesExpView {@ExcelProperty(value ="员工",index = 0)private String staffName;@ExcelProperty(value ="部门名称",index = 1)private String deptName;@ExcelProperty(value ={"月工作详情","月工作详情","月工作详情","月工作详情","1"},index = 2)private String d01;@ExcelProperty(value ={"月工作详情","月工作详情","月工作详情","月工作详情","2"},index = 3)private String d02;@ExcelProperty(value ={"月工作详情","月工作详情","月工作详情","月工作详情","3"},index = 4)private String d03;@ExcelProperty(value ={"月工作详情","月工作详情","月工作详情","月工作详情","4"},index = 5)private String d04;
}

导出样式
在这里插入图片描述
设置行高：@ContentRowHeight(150)作用在类上
设置列宽： @ColumnWidth(25)作用在字段上
忽略导出字段：@ExcelIgnore
设置时间字段导出格式： @DateTimeFormat(“yyyy-MM-dd”)

三、导出

根据查询出来的列表信息导出到页面

    @GetMapping("/export")@ApiOperation("导出XXX信息")public void exportProjectExpView(HttpServletResponse response, HttpServletRequest request) throws IOException {List<ProjectExpView> list = ProjectService.exportList();String name = "XXX信息";response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + new String(name.getBytes("gbk"), StandardCharsets.ISO_8859_1)  + ".xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();WriteSheet writeSheet1 = EasyExcel.writerSheet(0, name).head(SyfwEwmExport.class).build();excelWriter.write(list, writeSheet1);excelWriter.finish();}

四、导入

 @PostMapping("/import")@ApiOperation("导入XX信息")public ResponseResult<?> importProject(@RequestParam("file") MultipartFile file)  throws Exception{List<ProjectExpView> list = new ArrayList<>(1);List<ImportErrVo> errMsgList = new ArrayList<>(1);ExcelListener excelListener = new ExcelListener();Object Object1 = ExcelUtil.readExcel(file,ProjectExpView.class,0,excelListener);list = (List<ProjectExpView>) Object1;projectService.importProject(list);return ResponseResult.importSuccess();}