近来项目报表处理较多,期中有个按周按月按年自动排班的导出的功能,这个表头让我愁了一阵子,记录一下月排班的:
因为每个月的天数和每月一号的星期数都是动态的,easypoi的注解导出在这里并不适用,可以根据Map创建对应的Excel
@Test
public void exportScheduleTest() throws Exception{
String[][] strArray = {{"MONDAY", "一"}, {"TUESDAY", "二"}, {"WEDNESDAY", "三"}, {"THURSDAY", "四"}, {"FRIDAY", "五"}, {"SATURDAY", "六"}, {"SUNDAY", "日"}};
// 排班状态
String[] constantStr = {"休", "班"};
int length = constantStr.length;
LocalDate today = LocalDate.now();
//本月的第一天
LocalDate firstday = LocalDate.of(today.getYear(),today.getMonth(),1);
//本月的最后一天
LocalDate lastDay =today.with(TemporalAdjusters.lastDayOfMonth());
log.info("本月的第一天" + firstday);
log.info("本月的最后一天" + lastDay);
List<LocalDate> dateList = new ArrayList<>();
long distance = ChronoUnit.DAYS.between(firstday, lastDay);
Stream.iterate(firstday, d -> {
return d.plusDays(1);
}).limit(distance + 1).forEach(dateList::add);
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
//第一列
ExcelExportEntity colEntity1 = new ExcelExportEntity("序号", "code");
colEntity1.setNeedMerge(true);
colList.add(colEntity1);
//第二列
ExcelExportEntity colEntity2 = new ExcelExportEntity("工号", "jobNo");
colEntity2.setNeedMerge(true);
colList.add(colEntity2);
//第三列
ExcelExportEntity colEntity3 = new ExcelExportEntity("姓名\\日期", "name");
colEntity3.setNeedMerge(true);
colList.add(colEntity3);
for(LocalDate localDate:dateList){
ExcelExportEntity dateColGroup = new ExcelExportEntity(""+localDate.getDayOfMonth(), "dayOfMouth"+localDate.getDayOfMonth());
List<ExcelExportEntity> dateColList = new ArrayList<ExcelExportEntity>();
String dayOfWeek = String.valueOf(localDate.getDayOfWeek());
for (String[] strings : strArray) {
if (dayOfWeek.equals(strings[0])) {
dayOfWeek = strings[1];
break;
}
}
ExcelExportEntity tempExcelExportEntity = new ExcelExportEntity(dayOfWeek, "dayOfWeek"+localDate.getDayOfMonth());
dateColList.add(tempExcelExportEntity);
dateColGroup.setList(dateColList);
colList.add(dateColGroup);
}
// 根据部门id查询人员
List<DepartmentUserResultDTO> userResultDTOS = upmsService.listUserByDepartmentId(departmentId);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for(int i=0;i < userResultDTOS.size();i++){
Map<String, Object> valMap = new HashMap<>();
//添加序号
valMap.put("code", i+1);
//添加工号
valMap.put("jobNo",userResultDTOS.get(i).getJobNo());
//添加姓名
valMap.put("name",userResultDTOS.get(i).getRealName());
for(LocalDate localDate:dateList){
List<Map<String, Object>> dayOfWeekList = new ArrayList<Map<String, Object>>();
Map<String, Object> dayOfWeekMap = new HashMap<String, Object>();
dayOfWeekMap.put("dayOfWeek"+ localDate.getDayOfMonth(), constantStr[(int)(Math.random()*length)]);
dayOfWeekList.add(dayOfWeekMap);
valMap.put("dayOfMouth"+ localDate.getDayOfMonth(), dayOfWeekList);
}
list.add(valMap);
}
ExportParams params = new ExportParams("XXX部门"+today.getYear()+"年"+today.getMonthValue()+"月排班表", today.getYear()+"年"+today.getMonthValue()+"月(安排)");
// 设置标题样式(ExcelExportStyleBigHeaderImpl重写了easypoi的AbstractExcelExportStyler方法)
params.setStyle(ExcelExportStyleBigHeaderImpl.class);
Workbook workbook = ExcelExportUtil.exportExcel(params, colList,
list);
FileOutputStream fos = new FileOutputStream("D:/XXX部门"+today.getYear()+"年"+today.getMonthValue()+"月排班表.xlsx");
workbook.write(fos);
fos.close();
}