JAVA EXCEL 导出合并单元格

发布时间:2023年12月21日
 public void writeExcelXLSX(HttpServletResponse response, BasicIndicatorDTO dto) {
        try {
//            ServletOutputStream os = response.getOutputStream();
            Integer type = dto.getType();
            String areaCode = dto.getAreaCode();
            LoginManagerUser user = LoginManagerUser.getLoginManagerUser();
//            if (user != null && !UserEnums.USER_TYPE_SYSTEM.getKey().equals(user.getUserType())) {
//                areaCode = user.getHospitalInformation().getTownshipCode();
//            }
            List<CdClinicBasicIndicator> indicators = this.lambdaQuery()
                    .eq(StringUtil.isNotEmpty(areaCode), CdClinicBasicIndicator::getAreaCode, areaCode)
                    .eq(CdClinicBasicIndicator::getBasicType, type)
                    .eq(CdClinicBasicIndicator::getBasicYear, LocalDate.now().getYear())
                    .list();

            List<String> title = new ArrayList<>();
            String zeroCell [] = {"签约情况","慢病管理情况","基层就诊情况"};
            String twoCell [] = {"65岁以上老人签约人数","慢病签约人数","高血压签约人数","2型糖尿病患者签约人数","高脂血症签约人数",
                    "慢病签约率","慢病签约增长率","慢病管理总人数","高血压管理人数","2型糖尿病患者管理人数","高脂血症管理人数","多类合并慢病人数",
                    "","","","血压控制率","血糖控制率","血脂控制率","慢病就诊人次","慢病基层就诊人次","上级医院就诊人次",
                    "慢病基层医生就诊率","慢病基层签约医生就诊率","下转人次","上转人次"};
            if (DefaultConstants.NO.equals(type)) {
                String arr[] = {"指标类别","指标名称","","1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"};
                title = Arrays.asList(arr);
            }else if (DefaultConstants.YES.equals(type)){
                String arr[] = {"指标类别","指标名称","","第一季度","第二季度","第三季度","第四季度"};
                title = Arrays.asList(arr);
            }else if (DefaultConstants.TWO.equals(type)) {
                String arr[] = {"指标类别","指标名称","",LocalDate.now().getYear()+"年"};
                title = Arrays.asList(arr);
            }
            // 第一步,创建一个webbook,对应一个Excel文件
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
            SXSSFSheet sheet = workbook.createSheet("基础指标");
            // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            SXSSFRow row = sheet.createRow(0);
            // 第四步,创建单元格,并设置值表头 设置表头居中
            CellStyle style = workbook.createCellStyle();
            // 创建一个居中格式
            style.setAlignment(HorizontalAlignment.CENTER);

            //合并单元格
            //签约情况
            CellRangeAddress addresses = new CellRangeAddress(1,7,0,0);
            //慢病管理情况
            CellRangeAddress addresses2 = new CellRangeAddress(8,18,0,0);
            //多类合并慢病人数
            CellRangeAddress addresses3 = new CellRangeAddress(12,15,1,1);
            //基层就诊情况
            CellRangeAddress addresses4 = new CellRangeAddress(19,25,0,0);
            sheet.addMergedRegion(addresses);
            sheet.addMergedRegion(addresses2);
            sheet.addMergedRegion(addresses3);
            sheet.addMergedRegion(addresses4);
            //指标名称合并占两列
            for (int i=0; i <= 11; i++) {
                CellRangeAddress addressesIndex = new CellRangeAddress(i,i,1,2);
                sheet.addMergedRegion(addressesIndex);
            }
            //指标名称合并占两列
            for (int i=16; i <= 25; i++) {
                CellRangeAddress addressesIndex = new CellRangeAddress(i,i,1,2);
                sheet.addMergedRegion(addressesIndex);
            }
            //设置0行表头
            for (int i = 0; i < title.size(); i++) {
                //创建单元格,row是前面创建的第0行,所以这个是0行0列
                SXSSFCell cell = row.createCell(i);
                cell.setCellValue(title.get(i));
                cell.setCellStyle(style);
            }
            //指标名称(第1列)名
            for (int i = 1; i <= twoCell.length; i++) {
                //每一行第一列
                SXSSFRow row1 = sheet.createRow(i);
                // 创建单元格,并设置值
                row1.createCell(1).setCellValue(twoCell[i-1]);
            }
            // 创建单元格,并设置值 (sheet.createRow 会覆盖上面的行数据)
            //第1行0列
            SXSSFRow row1 = sheet.createRow(1);
            row1.createCell(0).setCellValue(zeroCell[0]);
            row1.createCell(1).setCellValue("65岁以上老人签约人数");
            //8行 0列
            row1 = sheet.createRow(8);
            // 创建单元格,并设置值
            row1.createCell(0).setCellValue(zeroCell[1]);
            row1.createCell(1).setCellValue("慢病管理总人数");
            //19行0列
            row1 = sheet.createRow(19);
            // 创建单元格,并设置值
            row1.createCell(0).setCellValue(zeroCell[2]);
            row1.createCell(1).setCellValue("慢病就诊人次");
            //创建新行
            row1 = sheet.createRow(12);
            row1.createCell(1).setCellValue("多类合并慢病人数");
            row1.createCell(2).setCellValue("高并糖");
            //创建新行
            row1 = sheet.createRow(13);
            // 创建单元格,并设置值
            row1.createCell(2).setCellValue("高并脂");
            //创建新行
            row1 = sheet.createRow(14);
            // 创建单元格,并设置值
            row1.createCell(2).setCellValue("糖并脂");
            //创建新行
            row1 = sheet.createRow(15);
            // 创建单元格,并设置值
            row1.createCell(2).setCellValue("高并糖并脂人数");
            // 第五步,写入实体数据 实际应用中这些数据从数据库得到,
            Map<Integer, CdClinicBasicIndicator> baseMap = indicators.stream()
                    .collect(Collectors.toMap(CdClinicBasicIndicator::getBasicDate, Function.identity()));
            //横坐标 1-12月 循环次数
            Integer startIndex = 1;
            Integer endIndex = 12;
            if (DefaultConstants.YES.equals(type)) {
                //1-4季度
                endIndex = 4;
            }else if (DefaultConstants.TWO.equals(type)) {
                //今年
                endIndex = 1;
            }
           for (Integer i = startIndex; i<=endIndex; i++) {
               CdClinicBasicIndicator indicator = baseMap.get(i);
               //年
               if (DefaultConstants.TWO.equals(type)) {
                   indicator = baseMap.get(LocalDate.now().getYear());
               }
               if (indicator != null) {
                   sheet.getRow(1)
                           .createCell(i+2)
                           .setCellValue(indicator.getOldSignNum());
                   sheet.getRow(2)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicSignNum());
                   sheet.getRow(3)
                           .createCell(i+2)
                           .setCellValue(indicator.getHbpSignNum());
                   sheet.getRow(4)
                           .createCell(i+2)
                           .setCellValue(indicator.getDmSignNum());
                   sheet.getRow(5)
                           .createCell(i+2)
                           .setCellValue(indicator.getBfSignNum());
                   sheet.getRow(6)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicSignRate());
                   sheet.getRow(7)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicSignAddRate());
                   sheet.getRow(8)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicNum());
                   sheet.getRow(9)
                           .createCell(i+2)
                           .setCellValue(indicator.getHbpNum());
                   sheet.getRow(10)
                           .createCell(i+2)
                           .setCellValue(indicator.getDmNum());
                   sheet.getRow(11)
                           .createCell(i+2)
                           .setCellValue(indicator.getBfNum());
                   sheet.getRow(12)
                           .createCell(i+2)
                           .setCellValue(indicator.getHbpDmNum());
                   sheet.getRow(13)
                           .createCell(i+2)
                           .setCellValue(indicator.getHbpBfNum());
                   sheet.getRow(14)
                           .createCell(i+2)
                           .setCellValue(indicator.getDmBfNum());
                   sheet.getRow(15)
                           .createCell(i+2)
                           .setCellValue(indicator.getHbpDmBfNum());
                   sheet.getRow(16)
                           .createCell(i+2)
                           .setCellValue(indicator.getHbpControlRate());
                   sheet.getRow(17)
                           .createCell(i+2)
                           .setCellValue(indicator.getDmControlRate());
                   sheet.getRow(18)
                           .createCell(i+2)
                           .setCellValue(indicator.getBfControlRate());
                   sheet.getRow(19)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicVisitNum());
                   sheet.getRow(20)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicBasicVisitNum());
                   sheet.getRow(21)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicSuperiorVisitNum());
                   sheet.getRow(22)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicBasicVisitRate());
                   sheet.getRow(23)
                           .createCell(i+2)
                           .setCellValue(indicator.getChronicBasicSignVisitRate());
                   sheet.getRow(24)
                           .createCell(i+2)
                           .setCellValue(indicator.getDownTurnNum());
                   sheet.getRow(25)
                           .createCell(i+2)
                           .setCellValue(indicator.getUploadNum());
               }
           }
           SimpleDateFormat simpleDateFormat = new SimpleDateFormat("HH-mm-ss");
            // 第六步,将文件存到指定位置
            FileOutputStream fout = new FileOutputStream("d:/students"+simpleDateFormat.format(new Date())+".xlsx");
            workbook.write(fout);
            workbook.close();
            fout.close();
        }catch (Exception e) {
            log.error("内部错误", e);
        }

    }

最终样式

数据库格式

文章来源:https://blog.csdn.net/qq_17152035/article/details/135137650
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。