EasyPoi导出复杂合并单元格

清疚 2023-02-16 04:30 13阅读 0赞

## 前言： ##

> 上星期做了一个Excel的单元格合并，用的是EasyPoi，我之前合并单元格都是原生的，第一次使用EasyPoi合并也不太熟悉，看着网上自己套用，使用后发现比原生的方便些，贡献一下，也给其他用到合并而且用的是EasyPoi的小伙伴节省下时间。

## 导出模板： ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70]

## 坐标： ##

版本号，自己来定，可以去官网查看：[EasyPoi官网][EasyPoi]

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

## 实现代码： ##

//表头设置
                    List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
    
                    ExcelExportEntity colEntity = new ExcelExportEntity("经销商", "distributorName");
                    colEntity.setNeedMerge(true);
                    colEntity.setWidth(20);
                    colList.add(colEntity);
    
                    colEntity = new ExcelExportEntity("科室", "dept");
                    colEntity.setNeedMerge(true);
                    colList.add(colEntity);
    
                    colEntity = new ExcelExportEntity("部门", "region");
                    colEntity.setNeedMerge(true);
                    colList.add(colEntity);
    
                    colEntity = new ExcelExportEntity("省份", "province");
                    colEntity.setNeedMerge(true);
                    colList.add(colEntity);
    
                    colEntity = new ExcelExportEntity("门店数量", "storeNum");
                    colEntity.setNeedMerge(true);
                    colEntity.setStatistics(true);
                    colList.add(colEntity);
                    Map<String, Integer> map = DateUtils.getLastDayOfMonthByStr(request.getMonthStr());
                    Integer dayNum = map.get("dayNum");
    
                    for (int i = 1; i <= dayNum; i++) { 
                        ExcelExportEntity group_1 = new ExcelExportEntity(i + "日", "day");
                        List<ExcelExportEntity> exportEntities = new ArrayList<>();
                        ExcelExportEntity appalyExcel = new ExcelExportEntity("申请数量", "applyNum" + i);
                        appalyExcel.setStatistics(true);
                        exportEntities.add(appalyExcel);
                        ExcelExportEntity adoptExcel = new ExcelExportEntity("通过数量", "adoptNum" + i);
                        adoptExcel.setStatistics(true);
                        exportEntities.add(adoptExcel);
                        group_1.setList(exportEntities);
                        colList.add(group_1);
                    }
                    //文件数据
                    List<Map<String, Object>> list = new ArrayList<>();
                    List<StoreNewAddReportVO.DistributorStoreNewAddReportVO> disList = register.getStoreNewAddReportVO().getDistributorStoreNewAddReportVOList();
                    int size = disList.size();
                    for (int i = 0; i < size; i++) { 
                        StoreNewAddReportVO.DistributorStoreNewAddReportVO dis = disList.get(i);
                        Map<String, Object> valMap = new HashMap<>();
                        valMap.put("distributorName", dis.getDistributorName());
                        valMap.put("dept", dis.getDept());
                        valMap.put("region", dis.getRegion());
                        valMap.put("province", dis.getProvince());
                        valMap.put("storeNum", dis.getStoreNum());
                        List<StoreNewAddReportVO.dayData> dayDataList = dis.getDayDataList();
                        Map<String, List<StoreNewAddReportVO.dayData>> collectMap = Maps.newHashMap();
                        if (CollectionUtils.isNotEmpty(dayDataList)) { 
                            collectMap = dayDataList.stream().collect(Collectors.groupingBy(StoreNewAddReportVO.dayData::getDayStr));
                        }
                        List<Map<String, Object>> list_1 = new ArrayList<>();
                        Map<String, Object> valMap_1 = new HashMap<>();
                        for (int j = 1; j <= dayNum; j++) { 
                            List<StoreNewAddReportVO.dayData> dayData = collectMap.get(String.valueOf(j));
                            int applyflag = 0;
                            int adoptflag = 0;
                            if (CollectionUtils.isNotEmpty(dayData)) { 
                                applyflag = dayData.get(0).getApplyNum();
                                adoptflag = dayData.get(0).getAdoptNum();
                            }
                            valMap_1.put("applyNum" + j, applyflag);
                            valMap_1.put("adoptNum" + j, adoptflag);
                        }
                        list_1.add(valMap_1);
                        valMap.put("day", list_1);
                        list.add(valMap);
                    }
                    //导出
                    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("【" + request.getMonthStr() + "】门店注册日明细数据", "数据"), colList, list);
                    Sheet sheet = workbook.getSheet("数据");
                    Row row = sheet.getRow(sheet.getLastRowNum());
                    Cell cell = row.getCell(0);
                    cell.setCellValue("总计");
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                    Font font = workbook.createFont();
                    font.setFontHeightInPoints((short) 15);
                    font.setFontName("Trebuchet MS");
                    cellStyle.setFont(font);
                    cell.setCellStyle(cellStyle);
                    CellRangeAddress range_0 = new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 3);
                    sheet.addMergedRegion(range_0);
                    File file = new File("D:\\".concat(UUID.randomUUID().toString().concat(".xls")));
    		        FileOutputStream fileOutputStream = null;
    		        try { 
    		            fileOutputStream = new FileOutputStream(file);
    		            workbook.write(fileOutputStream);
    		        } catch (Exception e) { 
    		            log.error("门店注册日workbook写入到文件中失败，错误信息：{}", ExceptionUtils.getStackTrace(e));
    		        } finally { 
    		            if (null != fileOutputStream) { 
    		                try { 
    		                    fileOutputStream.close();
    		                } catch (IOException e) { 
    		                    //skip
    		                }
    		            }
    		        }

具体的API细节就不介绍了可以去官网，关键在于ExcelExportEntity 这个类，它是以map形式展现的，创建的时候设置key，设置value的根据key进行设置，上面一些StoreNewAddReportVO还有其他是我的业务类， 到时候可以替换掉。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200608102448684.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg=,size_16,color_FFFFFF,t_70
[EasyPoi]: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u6ksp2r091