我的需求是这样子的：导出下面格式的报表。
（这个格式应该是之前打卡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();//格式-04String[] dates=date.split("-");String staDate=date+"-01";//-04-01String 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);//获取所有部门创建sheetXSSFSheet 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);//-04-30 -05-01Map<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做出来的效果
