HSSFWorkbook多级表头代码实现

如我般骄傲
已于 2023-12-18 16:19:04 修改
阅读量224
点赞数
文章标签： java excel
于 2023-12-18 16:18:18 首次发布
本文链接：https://blog.csdn.net/qq_38173650/article/details/135065620
版权
该代码片段展示了如何使用Java通过HSSFWorkbook创建一个医院分娩报告的Excel表格，包括合并单元格、数据填充和流式输出到InputStream。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
public InputStream getInputStream() {


        try {
            PagerModel pagerModel = hospitalDeliveryReportService.list(dto);
            List<HospitalDeliveryReport> hospitalDeliveryReportList = (List<HospitalDeliveryReport>) pagerModel.getPageData();

            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Data Sheet");

            Row row1 = sheet.createRow(1);
            Cell cell11 = row1.createCell(0);

            cell11.setCellValue("填报单位");
            sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));

            Cell cell12 = row1.createCell(1);
            cell12.setCellValue("住院分娩活产数");
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 9));

            //创建第二行
            Row row2 = sheet.createRow(2);

            Cell cell21 = row2.createCell(1);
            cell21.setCellValue("分性别活产数");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));
            Cell cell22 = row2.createCell(5);
            cell22.setCellValue("分孕周活产数");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 8));
            Cell cell23 = row2.createCell(9);
            cell23.setCellValue("<28周存货儿数");
            sheet.addMergedRegion(new CellRangeAddress(1, 2, 9, 9));

            //创建第二行
            Row row3 = sheet.createRow(3);
            Cell cell31 = row3.createCell(1);
            cell31.setCellValue("男");
            Cell cell32 = row3.createCell(2);
            cell32.setCellValue("女");
            Cell cell33 = row3.createCell(3);
            cell33.setCellValue("性别不明");
            Cell cell34 = row3.createCell(4);
            cell34.setCellValue("合计");
            Cell cell35 = row3.createCell(5);
            cell35.setCellValue("≥37周");
            Cell cell36 = row3.createCell(6);
            cell36.setCellValue("28~36周");
            Cell cell37 = row3.createCell(7);
            cell37.setCellValue("合计");
            Cell cell38 = row3.createCell(8);
            cell38.setCellValue("早产率%");

            CellStyle style = wb.createCellStyle();

            style.setBorderBottom(BORDER_THIN);
            style.setBorderTop(BORDER_THIN);
            style.setBorderRight(BORDER_THIN);
            style.setBorderLeft(BORDER_THIN);
            style.setAlignment(ALIGN_CENTER);
            style.setVerticalAlignment(VERTICAL_CENTER);


            int rowCount = hospitalDeliveryReportList.size() + 4;

            for (int i = 4; i < rowCount; i++) {
                int j = i - 4;
                int col = 0;
                Row row = sheet.createRow(i);
                //填报单位
                row.createCell(col++).setCellValue(DhccUtil.getString(hospitalDeliveryReportList.get(j).getOrgName()));
                //分性别活产数-男
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getAliveNumberBoy()));
                //分性别活产数-女
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getAliveNumberGirl()));
                //分性别活产数-不明
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getAliveNumberUnknow()));
                //分性别活产数-合计
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getAliveNumberSum()));
                //分孕周活产数-≥37周
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getBirthFullAlive()));
                //分孕周活产数-28~36周
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getBirthPrematureAlive()));
                //分孕周活产数-合计
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getBirthAliveSum()));
                //分孕周活产数-早产率%
                row.createCell(col++).setCellValue(DhccUtil.getString(hospitalDeliveryReportList.get(j).getBirthPrematureRate()));
                //<28周存货儿数
                row.createCell(col++).setCellValue(DhccUtil.getInt(hospitalDeliveryReportList.get(j).getBirthVeryPremature()));
            }

            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell == null || cell.getCellType() == CELL_TYPE_BLANK) { // 判断cell是否为空
                        continue;
                    }
                    cell.setCellStyle(style);
                }
            }
            int numMergedRegions = sheet.getNumMergedRegions();
            for (int i = 0; i < numMergedRegions; i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                for (int rowNum = mergedRegion.getFirstRow(); rowNum <= mergedRegion.getLastRow(); rowNum++) {
                    for (int colNum = mergedRegion.getFirstColumn(); colNum <= mergedRegion.getLastColumn(); colNum++) {
                        Row row = sheet.getRow(rowNum);
                        if (row == null) {
                            row = sheet.createRow(rowNum);
                        }
                        Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK);
                        cell.setCellStyle(style);
                    }
                }
            }
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            wb.write(output);
            byte[] ba = output.toByteArray();
            inputStream = new ByteArrayInputStream(ba);
            output.flush();
            output.close();
            return inputStream;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }