EXCEL指定单元格合并

需求：一对多数据Excel导出，部分数据合并
缺陷：数据是固定的，无法做动态数据展示
实现方式：

// 合并(4个参数，分别为起始行，结束行，起始列，结束列)
CellRangeAddress region = new CellRangeAddress(n, n + m - 1, 0, 0);
sheet.addMergedRegion(region);

效果图：

代码（方法比较笨，自己手动处理的）：

  public void outLogExceloperation(HttpServletResponse response, HttpServletRequest request, BusinessTrip businessTrip) throws IOException, ParseException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立sheet对象
        HSSFSheet sheet = wb.createSheet("出差申请表");

        // 设置列宽
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 25 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.setColumnWidth(4, 25 * 256);
        sheet.setColumnWidth(5, 25 * 256);
        sheet.setColumnWidth(6, 25 * 256);
        sheet.setColumnWidth(7, 25 * 256);
        sheet.setColumnWidth(8, 25 * 256);
        sheet.setColumnWidth(9, 25 * 256);
        sheet.setColumnWidth(10, 25 * 256);
        // 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可：cell.setCellStyle(cellStyle);
        // 合并单元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
//        cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
//        cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//增加水平居中样式-old
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//增加垂直居中样式-old
        // 设置字体
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(0);
        //创建单元格并设置单元格内容
        ArrayList<String> tittles = new ArrayList<>();
        tittles.add("提交人");
        tittles.add("提交时间");
        tittles.add("出差事由");
        tittles.add("出差天数");
        tittles.add("出差备注");
        tittles.add("交通工具");
        tittles.add("出发地点");
        tittles.add("目的地点");
        tittles.add("开始时间");
        tittles.add("结束时间");
        tittles.add("时长（小时）");

        for (int i = 0; i < tittles.size(); i++) {
            String s = tittles.get(i);
            HSSFCell cell = row2.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(s);
        }
        //获取出差审批详情
        TableDataInfo businessTripList = businessTripService.getBusinessTripList(businessTrip, request);
        List<BusinessTrip> businessTrips = businessTripList.getRows();
        if (CollectionUtils.isEmpty(businessTrips)) {
            return;
        } else {
            int n = 1;
            int m = 0;
            for (int i = 0; i < businessTrips.size(); i++) {
                BusinessTrip businessTrip1 = businessTrips.get(i);
                HSSFRow row3 = sheet.createRow(n);
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                HSSFCell cell1 = row3.createCell(0);
                cell1.setCellStyle(cellStyle);
                cell1.setCellValue(businessTrip1.getCreateName());

                HSSFCell cell2 = row3.createCell(1);
                cell2.setCellStyle(cellStyle);
                cell2.setCellValue(format.format(businessTrip1.getCreateTime()));

                HSSFCell cell3 = row3.createCell(2);
                cell3.setCellStyle(cellStyle);
                cell3.setCellValue(businessTrip1.getIntroduce());

                HSSFCell cell4 = row3.createCell(3);
                cell4.setCellStyle(cellStyle);
                cell4.setCellValue(businessTrip1.getDays());

                HSSFCell cell5 = row3.createCell(4);
                cell5.setCellStyle(cellStyle);
                cell5.setCellValue(businessTrip1.getRemark());

                if (CollectionUtils.isNotEmpty(businessTrip1.getTripList())) {
                    List<Trip> tripList = businessTrip1.getTripList();
                    m = tripList.size();
                    for (int i1 = 0; i1 < tripList.size(); i1++) {
                        Trip trip = tripList.get(i1);
                        HSSFRow row4;
                        if (i1 + n == n) {
                            row4 = row3;
                        } else {
                            row4 = sheet.createRow(i1 + n);
                        }
                        HSSFCell cell6 = row4.createCell(5);
                        cell6.setCellStyle(cellStyle);
                        cell6.setCellValue(trip.getTransportationName());

                        HSSFCell cell7 = row4.createCell(6);
                        cell7.setCellStyle(cellStyle);
                        cell7.setCellValue(trip.getOrigin());

                        HSSFCell cell8 = row4.createCell(7);
                        cell8.setCellStyle(cellStyle);
                        cell8.setCellValue(trip.getDestination());

                        HSSFCell cell9 = row4.createCell(8);
                        cell9.setCellStyle(cellStyle);
                        cell9.setCellValue(format.format(trip.getStartTime()));

                        HSSFCell cell10 = row4.createCell(9);
                        cell10.setCellStyle(cellStyle);
                        cell10.setCellValue(format.format(trip.getEndTime()));

                        HSSFCell cell11 = row4.createCell(10);
                        cell11.setCellStyle(cellStyle);
                        cell11.setCellValue(trip.getDuration());


                    }
                    if (tripList.size() != 1) {
                        // 合并(4个参数，分别为起始行，结束行，起始列，结束列)
                        CellRangeAddress region = new CellRangeAddress(n, n + m - 1, 0, 0);
                        sheet.addMergedRegion(region);
                        CellRangeAddress region1 = new CellRangeAddress(n, n + m - 1, 1, 1);
                        sheet.addMergedRegion(region1);
                        CellRangeAddress region2 = new CellRangeAddress(n, n + m - 1, 2, 2);
                        sheet.addMergedRegion(region2);
                        CellRangeAddress region3 = new CellRangeAddress(n, n + m - 1, 3, 3);
                        sheet.addMergedRegion(region3);
                        CellRangeAddress region4 = new CellRangeAddress(n, n + m - 1, 4, 4);
                        sheet.addMergedRegion(region4);
                        n = n + tripList.size();
                    } else {
                        n = n + 1;
                    }

                } else {
                    n = n + 1;
                }

            }

        }
        String path = "C:\\poi";

        File file = new File(path);
        if (!file.exists()) {
            file.mkdirs();
        }
        FileOutputStream fileOutputStream = new FileOutputStream("C:\\poi\\出差申请" + new Date().getTime() + ".xlsx");
        wb.write(fileOutputStream);
        fileOutputStream.close();

        String fileName = "出差申请";
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        //文件中文名转码，防止乱码
        String excfileName = "";
        try {
            String agent = (request.getHeader("USER-AGENT")).toLowerCase();
            if (StringUtils.contains(agent, "mise")) {
                //IE浏览器
                excfileName = fileName;
                //excfileName = URLEncoder.encode(fileName,"UTF8");
            } else if (StringUtils.contains(agent, "chrome") || StringUtils.contains(agent, "firefox")) {
                //google,火狐浏览器
                excfileName = new String(fileName.getBytes("GB2312"), "ISO-8859-1");
            } else if (StringUtils.contains(agent, "safari")) {
                excfileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            } else {
                excfileName = URLEncoder.encode(fileName, "UTF8");
            }
        } catch (UnsupportedEncodingException e) {

        }
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd ");//设置日期格式
        String testDate = df.format(new Date());//格式化当前日期
        response.addHeader("Content-Disposition", "attachment;filename=" + excfileName + "-" + testDate + ".xls");
        response.flushBuffer();
        wb.write(response.getOutputStream());

    }