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

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

article 2024/11/19 18:31:31

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();
    }