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

蔚落 2023-10-12 10:41 28阅读 0赞

### EasyPoi导出导入 ###

*  一.引入EasyPoi依赖
 *  二.导出示例(合并单元格)
 *   *  1.导出模板
     *  2.实体类加注释
     *  3.编写逻辑
 *  二.导入示例

# 一.引入EasyPoi依赖 #

<dependency>
             <groupId>cn.afterturn</groupId>
             <artifactId>easypoi-base</artifactId>
             <version>3.2.0</version>
         </dependency>
    
    	 
         <dependency>
             <groupId>cn.afterturn</groupId>
             <artifactId>easypoi-web</artifactId>
             <version>3.2.0</version>
         </dependency>
    
    	
         <dependency>
             <groupId>cn.afterturn</groupId>
             <artifactId>easypoi-annotation</artifactId>
             <version>3.2.0</version>
         </dependency>

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

## 1.导出模板 ##

![在这里插入图片描述][9af4ef9d759348739859267755c29c5b.jpeg]

## 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("导入成功");
        }

[9af4ef9d759348739859267755c29c5b.jpeg]: https://img-blog.csdnimg.cn/9af4ef9d759348739859267755c29c5b.jpeg