【java】EasyPoi导出导入(合并单元格)

一.引入EasyPoi依赖

	<!-- 导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能 -->
 	<dependency>
         <groupId>cn.afterturn</groupId>
         <artifactId>easypoi-base</artifactId>
         <version>3.2.0</version>
     </dependency>

	 <!--耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能 -->
     <dependency>
         <groupId>cn.afterturn</groupId>
         <artifactId>easypoi-web</artifactId>
         <version>3.2.0</version>
     </dependency>

	<!-- 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理 -->
     <dependency>
         <groupId>cn.afterturn</groupId>
         <artifactId>easypoi-annotation</artifactId>
         <version>3.2.0</version>
     </dependency>

二.导出示例(合并单元格)

1.导出模板

2.实体类加注释

	@Excel(name = "真爱粉", width = 15)
	private String heizi;
	
	@Excel(name = "唱", width = 15)
	private String chang;
	
	@Excel(name = "跳", width = 15)
	private String tiao;
	
	@Excel(name = "rap", width = 15)
	private String rap;
	
	@Excel(name = "篮球", width = 15)
	private String qiu;

3.编写逻辑

  public void exportXls(KunKun ikun, HttpServletResponse response) {
        //查询导出的信息列表
        List<KunKun> ikuns = kunKunMapper.allList(ikun);
        
        //存放excel的表头 ExcelExportEntity是EasyPoi提供的excel属性实体类
        List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
        
        //表头
        ExcelExportEntity title = new ExcelExportEntity("序号","num");
        entityList.add(title);
        
		//真爱粉对应key，heizi对应的是表头也是你实体类的字段
        title = new ExcelExportEntity("真爱粉","heizi");
        entityList.add(title);

        //合并坤坤单元格
        ExcelExportEntity groupOne = new ExcelExportEntity("坤坤", "kunkun");
        List<ExcelExportEntity> exportEntitieOne = new ArrayList<>();
        exportEntitieOne.add(new ExcelExportEntity("唱","chang"));
        exportEntitieOne.add(new ExcelExportEntity("跳","tiao"));
        exportEntitieOne.add(new ExcelExportEntity("rap","rap"));
        exportEntitieOne.add(new ExcelExportEntity("篮球","qiu"));
        groupOne.setList(exportEntitieOne);
        entityList.add(groupOne);

        //存放全部的数据
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        //定义序号
        int num = 1;
        for (KunKun ikun: ikuns) {
            //存放坤坤表头下的数据
       	    List<Map<String, Object>> listOne = new ArrayList<Map<String, Object>>();
            Map<String, Object> map = new HashMap<>();
            Map<String, Object> mapOne = new HashMap<>();
            map.put("num",num++);
            map.put("heizi",ikun.getHeizi());
            mapOne.put("chang",ikun.getChang());
            mapOne.put("tiao",ikun.getTiao());
            mapOne.put("rap",ikun.getRap());
            mapOne.put("qiu",ikun.getQiu());
            listOne.add(mapOne);
            map.put("kunkun",listOne);
            list.add(map);
        }
        //获取当前时间
        String date = LocalDate.now().toString();
        String fileName = date + "坤坤记录导出";
        //ExcelExportUtil是EasyPoi提供的导出工具类。
        //参数对应：导出文件名字、表头、对应的数据
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(fileName, "sheet1"), entityList, list);
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

二.导入示例

public Result<?> importExcel(MultipartFile file) {
        String extNane = FileUtil.getSuffix(file.getOriginalFilename());
        if (!"xls,xlsx".contains(extNane)){
            return Result.error("请选择Excel文件！");
        }
        ExcelReader reader = null;
        try {
            reader = ExcelUtil.getReader(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
        //从第2行开始读取数据(0开始计数)
        List<List<Object>> readAll = reader.read(2);
        if (CollectionUtils.isEmpty(readAll)){
            return Result.error("数据为空，请正确填写数据后再上传！");
        }

        //存放导入的数据
        List<KunKun> insertList = new ArrayList<>();

        try{
            readAll.forEach(e ->{
                KunKun ikun = new KunKun();
                //从1开始取，因为0列是序号列
                //真爱粉
                fuelSupplyStore.setHeizi(ObjectUtil.isNull(e.get(1)) ? null : ObjectUtil.toString(e.get(1)).trim());
                //唱
                fuelSupplyStore.setChang(ObjectUtil.isNull(e.get(2)) ? null : ObjectUtil.toString(e.get(2)).trim());
                //跳
                fuelSupplyStore.setTiao(ObjectUtil.isNull(e.get(3)) ? null : ObjectUtil.toString(e.get(3)).trim());
                //rap
                fuelSupplyStore.setRap(ObjectUtil.isNull(e.get(4)) ? null : ObjectUtil.toString(e.get(4)).trim());
                //篮球
                fuelSupplyStore.setQiu(ObjectUtil.isNull(e.get(5)) ? null : ObjectUtil.toString(e.get(5)).trim());
        }catch (Exception ex){
            return Result.error("导入失败");
        }
        //批量新增
        kunKunMapper.saveBatch(insertList);
        return Result.OK("导入成功");
    }