EasyPoi导出复杂合并单元格

前言:

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

导出模板:

在这里插入图片描述

坐标:

版本号,自己来定,可以去官网查看: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还有其他是我的业务类, 到时候可以替换掉。

评论 11
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

技术武器库

一句真诚的谢谢,胜过千言万语

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值