SpringBoot集成的Excel的导入导出，真的比POI好用

文章目录

前言
一、引入
二、导入导出实现
- 1.基本导入导出
- 2.复杂的导入导出
总结

前言

之前写过一篇文档 poi实现Excel文件解析导入_flowery_的博客-CSDN博客用poi实现excel文件的导入导出，代码很多，最近在公众号看到了一篇文章，（公众号：macrozheng，感谢这位老哥）就是这篇：

参考这篇文章的讲解，在这里做一下简单的记录。

一、引入easypoi

代码中同时用了mybatis-plus

 
        
            cn.afterturn
            easypoi-spring-boot-starter
            4.4.0

二、导入导出实现

做一些准备工作，建表实现entity vo等

CREATE TABLE IF NOT EXISTS `work` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `title` varchar(20) DEFAULT NULL COMMENT '工作标题字',
  `content` varchar(200) DEFAULT NULL COMMENT '工作内容',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT='工作信息表';

@Data
@TableName("work")
public class Work {

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private String title;
    
    private String content;
    
    private Date createTime;
    
    private Date updateTime;

}

public interface WorkMapper extends baseMapper {
}

@Service
public class WorkService extends ServiceImpl implements IService {

}

1.简单导入导出的实现

这部分代码实现的是简单的excel导入导出，不做excel格式的改变：

@Data
public class WorkVO {

    @Excel(name = "编号")
    private Long id;
    
    @Excel(name = "标题")
    private String title;
    
    @Excel(name = "内容")
    private String content;
    
    @Excel(name = "创建时间", format = "yyyy-MM-dd")
    private Date createTime;
    
    @Excel(name = "更新时间", format = "yyyy-MM-dd")
    private Date updateTime;
}

@Service
public class WorkService extends ServiceImpl implements IService {

    public List entity2Vo(List workList) {
        if (CollectionUtil.isEmpty(workList)) {
            return Collections.emptyList();
        }
        List result = new ArrayList<>();
        workList.forEach(item -> {
            WorkVO excel = new WorkVO();
            BeanUtils.copyProperties(item, excel);
            result.add(excel);
        });
        return result;
    }
}

导出：

  @GetMapping("simple/export")
        public void simpleExport(HttpServletRequest request, HttpServletResponse response) {
        List workList = workService.list();

        List list = workService.entity2Vo(workList);
        ExportParams params = new ExportParams();
        params.setTitle("工作列表");
        params.setSheetName("工作列表sheet");
        params.setType(ExcelType.XSSF);

        ModelMap map = new ModelMap();
        map.put(NormalExcelConstants.DATA_LIST,list);
        map.put(NormalExcelConstants.CLASS, WorkVO.class);
        map.put(NormalExcelConstants.PARAMS,params);
        map.put(NormalExcelConstants.FILE_NAME,"工作列表");
        PoibaseView.render(map,request,response,NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }

导入：

@PostMapping("simple/import")
    public Object simpleimport(@RequestParam MultipartFile file) throws IOException {
        importParams params = new importParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        InputStream inputStream = null;
        try{
             inputStream = file.getInputStream();
            return ExcelimportUtil.importExcel(inputStream, WorkVO.class,params);
        }catch (Exception e) {
            throw new IllegalStateException("文件导入异常");
        } finally {
            if (Objects.nonNull(inputStream)) {
                inputStream.close();
            }
        }

    }

2.复杂导入导出的实现

复杂的导入导出需要对基本的实体类做一些改变：

这里我新加了一个工作子项的类并在WorkVO中新增子项列表字段，改动如下：

CREATE TABLE IF NOT EXISTS `work_item` (
   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
   `title` varchar(20) DEFAULT NULL COMMENT '工作子项标题',
    `content` varchar(200) DEFAULT NULL COMMENT '工作子项内容',
    `work_id` bigint NOT NULL DEFAULT '0' COMMENT '所属工作ID',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT='工作子项表';

@Data
@TableName("work_item")
public class WorkItem {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private Long workId;
    
    private String title;
    
    private String content;
    
    private Date createTime;
    
    private Date updateTime;
}

@Data
public class WorkItemVO {
    @Excel(name = "子项编号", width = 10)
    private Long id;
    
    @Excel(name = "工作子项标题",width = 20)
    private String title;
    
    @Excel(name = "工作子项内容",width = 20)
    private String content;
    
    @Excel(name = "子项创建时间",width = 20,format = "yyyy-MM-dd")
    private Date createTime;
    
    @Excel(name = "子项更新时间",width = 20,format = "yyyy-MM-dd")
    private Date updateTime;
}

//WorkVO变化如下
@Data
public class WorkVO {

    @Excel(name = "编号")
    private Long id;
    
    @Excel(name = "标题")
    private String title;
    
    @Excel(name = "内容")
    private String content;
    
    @Excel(name = "创建时间", format = "yyyy-MM-dd")
    private Date createTime;
    
    @Excel(name = "更新时间", format = "yyyy-MM-dd")
    private Date updateTime;

    @ExcelCollection(name = "工作子项")
    private List workItemVOList;
}

public class WorkService extends ServiceImpl implements IService {

    @Resource
    private WorkItemService workItemService;
//这个方法改动了一下，为了封装itemList
    public List entity2Vo(List workList,Boolean flag) {
        if (CollectionUtil.isEmpty(workList)) {
            return Collections.emptyList();
        }
        List result = new ArrayList<>();
        Map> map = new HashMap<>();
        if (Boolean.TRUE.equals(flag)) {
            List ids = workList.stream().map(Work::getId).collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(ids)) {
                LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
                wrapper.in(WorkItem::getWorkId,ids);
               map = workItemService.list(wrapper).stream().collect(Collectors.groupingBy(WorkItem::getWorkId));
            }
        }
        Map> finalMap = map;

        workList.forEach(item -> {
            WorkVO excel = new WorkVO();
            BeanUtils.copyProperties(item, excel);

            List list = finalMap.get(item.getId());
            //暂时先直接转化成VO
            List itemList = new ArrayList<>();
            if (CollectionUtil.isNotEmpty(list)) {
                list.forEach(a -> {
                    WorkItemVO itemVo = new WorkItemVO();
                    BeanUtils.copyProperties(a,itemVo);
                    itemList.add(itemVo);
                });
            }

            excel.setWorkItemVOList(itemList);
            result.add(excel);
        });

        return result;
    }
}

最终导入导出的代码为：

 @GetMapping("diff/export")
    public void diffExport(HttpServletRequest request, HttpServletResponse response) {
        List workList = workService.list();

        List list = workService.entity2Vo(workList,Boolean.TRUE);
        ExportParams params = new ExportParams();
        params.setTitle("复杂工作列表");
        params.setSheetName("复杂工作列表sheet");
        params.setType(ExcelType.XSSF);

        ModelMap map = new ModelMap();
        map.put(NormalExcelConstants.DATA_LIST,list);
        map.put(NormalExcelConstants.CLASS, WorkVO.class);
        map.put(NormalExcelConstants.PARAMS,params);
        map.put(NormalExcelConstants.FILE_NAME,"复杂导出-工作列表");
        PoibaseView.render(map,request,response,NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }

@PostMapping("diff/import")
    public Object diffimport(@RequestParam MultipartFile file) throws IOException {
        importParams params = new importParams();
        //复杂的导入算好头部的行数和标题占用的行数，导出的文件就可以很方便的进行导入了
        params.setTitleRows(1);
        params.setHeadRows(2);
        InputStream inputStream = null;
        try{
            inputStream = file.getInputStream();
            return ExcelimportUtil.importExcel(inputStream, WorkVO.class,params);
        }catch (Exception e) {
            throw new IllegalStateException("文件导入异常");
        } finally {
            if (Objects.nonNull(inputStream)) {
                inputStream.close();
            }
        }
    }

总结

写的比较匆忙，后面会在完善一下，仅供参考。

SpringBoot集成的Excel的导入导出，真的比POI好用

Java相关栏目本月热门文章