XSSFWorkbook自由合并单元格并设置单元格样式

我的需求是这样子的：导出下面格式的报表。

（这个格式应该是之前打卡APP的或者是手作的。5月份接入了qiyeweixin打卡，qiyeweixin没有提供导出这样报表的功能。只提供了API给我们开发这样的功能，所以我的任务是自动获取qiyeweixin打卡记录，按照这个格式的报表统计）

这个是主方法体，从new XSSFWorkbook()开始。这个表格创建了....（好像不用说..）。

public void createXlsx(HttpServletResponse response,AttendanceQueryCriteria criteria){
        if(criteria.getMonth()==null || "".equals(criteria.getMonth())){//不传入时间默认取上个月的考勤
            criteria.setMonth(getUpperMonth());//上个月时间yyyy-mm
        }
        String date=criteria.getMonth();//格式2022-04
        String[] dates=date.split("-");
        String staDate=date+"-01";//2022-04-01
        String endDate=date+"-"+getLastDayOfMonth(Integer.parseInt(dates[0]),Integer.parseInt(dates[1]));
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();//创建工作簿
        String deptName = criteria.getDeptName()==null?"%":criteria.getDeptName();
        List<String> list = attUserRepository.groupByDeptName(deptName);//获取所有部门创建sheet
        XSSFSheet sheet=null;
        for(int i=0;i<list.size();i++){
            sheet = xssfWorkbook.createSheet(list.get(i));//创建工作表
            createHead(sheet,staDate,endDate);     //创建表头
            createTitle(sheet,date,list.get(i));        //创建标题
            criteria.setDeptName(list.get(i));
            List<Map<String,List<String>>> rows=this.getRowsDate(criteria);
            createRow(sheet,rows);//创建行
            createFooter(sheet);//创建底部
        }
        OutputStream osOut=null;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=baobiao.xlsx");
            response.setContentType("application/octet-stream;charset=UTF-8");
            osOut = response.getOutputStream();
            xssfWorkbook.write(osOut);
            osOut.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                osOut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

再到表头，标题是根据表头的列数合并的，因为表头列数会随着月份的天数有变。

private void createHead(XSSFSheet sheet,String  staDate,String endDate){//创建表头
        String[] heads = {"序号","姓名","部门","应出勤天数","实际出勤天数（包含法定节假日）"
                ,"全勤奖","请假t","请假类型","平时加班h","周日加班h"
                ,"调休h","欠休h","迟到早退m","迟到早退乐捐","缺卡","缺卡乐捐","旷工h"};
        XSSFRow head1 = sheet.createRow(1);//表头
        XSSFRow head2 = sheet.createRow(2);//表头
        head2.setHeight((short)(30*20));
        for(int i = 0;i < heads.length;i++){
            if(i==0){
                sheet.setColumnWidth(i,4*256);
            }else if(i==1){
                sheet.setColumnWidth(i,7*256);
            }else if(i==2){
                sheet.setColumnWidth(i,9*256);
            }else{
                sheet.setColumnWidth(i,5*256);
            }
            XSSFCell cell1 = head1.createCell(i);
            XSSFCell cell2 = head2.createCell(i);
            cell1.setCellValue(heads[i]);
            sheet.addMergedRegion(new CellRangeAddress(1,2,i,i));
            cell1.setCellStyle(setFontBoldStyle(sheet,setBorderStyle(sheet.getWorkbook())));
            cell2.setCellStyle(setFontBoldStyle(sheet,setBorderStyle(sheet.getWorkbook())));
        }
        String[] staDates=staDate.split("-");
        String[] endDates=endDate.split("-");
        LocalDate sta = LocalDate.of(Integer.parseInt(staDates[0]), Integer.parseInt(staDates[1]), Integer.parseInt(staDates[2]));
        LocalDate end = LocalDate.of(Integer.parseInt(endDates[0]), Integer.parseInt(endDates[1]), Integer.parseInt(endDates[2]));
        end = end.plusDays(1);//2022-04-30   2022-05-01
        Map<String,String> map=new HashMap<String,String>();
        map.put("SUNDAY","日");
        map.put("MONDAY","一");
        map.put("TUESDAY","二");
        map.put("WEDNESDAY","三");
        map.put("THURSDAY","四");
        map.put("FRIDAY","五");
        map.put("SATURDAY","六");
        int j=heads.length;
        while(sta.isBefore(end)){
            sheet.setColumnWidth(j,4*280);
            XSSFCell cell1=head1.createCell(j);
            cell1.setCellValue(sta.getDayOfMonth());
            cell1.setCellStyle(setBorderStyle(sheet.getWorkbook()));
            XSSFCell cell2=head2.createCell(j);
            cell2.setCellValue(map.get(sta.getDayOfWeek().name()));
            cell2.setCellStyle(setBorderStyle(sheet.getWorkbook()));
            j++;
            sta = sta.plusDays(1);
        }
        XSSFCell cell = head1.createCell(j);
        XSSFCell cell2=head2.createCell(j);
        cell.setCellValue("本人签字");
        cell.setCellStyle(setBorderStyle(sheet.getWorkbook()));
        cell2.setCellStyle(setBorderStyle(sheet.getWorkbook()));
        sheet.addMergedRegion(new CellRangeAddress(1,2,j,j));
    }

private void createTitle(XSSFSheet sheet,String date,String name){//创建标题
        XSSFRow head = sheet.createRow(0);//标题
        XSSFCell cell=head.createCell(0);
        cell.setCellValue(name+date+"考勤表");
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,sheet.getRow(1).getLastCellNum()-1));
        head.setHeight((short)(40*20));
        XSSFFont f = sheet.getWorkbook().createFont();
        f.setFontHeightInPoints((short) 18);//字号
        f.setUnderline(Font.U_SINGLE);//设置下划线
        f.setBold(true);
        XSSFCellStyle style=setBorderStyle(sheet.getWorkbook());
        style.setFont(f);
        cell.setCellStyle(style);
    }

//设计样式
    private XSSFCellStyle setBorderStyle(XSSFWorkbook wb){
        XSSFCellStyle cellStyle=wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        return cellStyle;
    }

代码有点多，不全部贴上来了。主要是知道能做成这样，根据自己的需求，去写业务逻辑就ok..

下图是使用XSSFWorkbook做出来的效果