| | |
|---|
| | | import org.apache.poi.hssf.usermodel.*; |
|---|
| | | import org.apache.poi.ss.usermodel.CellType; |
|---|
| | | import org.apache.poi.ss.usermodel.HorizontalAlignment; |
|---|
| | | import org.apache.poi.ss.util.CellRangeAddress; |
|---|
| | | import org.springframework.beans.factory.annotation.Autowired; |
|---|
| | | import org.springframework.stereotype.Service; |
|---|
| | | |
|---|
| | |
|---|
| | | public void exportExcel(String month, HttpServletResponse response) { |
|---|
| | | PageInfo<SchedulingDetailDto> pageInfo = new PageInfo<>(0, 10000); |
|---|
| | | List<SchedulingDetailDto> schedulingDetailDtos = this.baseMapper.pageList(pageInfo, month); |
|---|
| | | List<ExportSchedulingDetailDto> exportSchedulingDetailDtos = new ArrayList<>(); |
|---|
| | | HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); |
|---|
| | | // for (int i = 0; i < titles.size(); i++) { |
|---|
| | | // HSSFSheet hssfSheet = hssfWorkbook.createSheet(); |
|---|
| | | // hssfSheet.setColumnWidth(0, 6 * 256); |
|---|
| | | // hssfSheet.setDefaultRowHeightInPoints(20f); |
|---|
| | | // |
|---|
| | | // HSSFRow hssfRow = hssfSheet.createRow(0);//设置第一行数据（标题） |
|---|
| | | // HSSFCellStyle style = hssfWorkbook.createCellStyle(); |
|---|
| | | // HSSFFont font = hssfWorkbook.createFont(); |
|---|
| | | // font.setBold(true); |
|---|
| | | // style.setFont(font); |
|---|
| | | // style.setAlignment(HorizontalAlignment.CENTER); |
|---|
| | | // for (int l = 0; l < titles.get(i).size(); l++) { |
|---|
| | | // HSSFCell hssfCell = hssfRow.createCell(l); |
|---|
| | | // hssfCell.setCellType(CellType.STRING);//设置表格类型 |
|---|
| | | // hssfCell.setCellValue(titles.get(i).get(l)); |
|---|
| | | // hssfCell.setCellStyle(style); |
|---|
| | | // hssfSheet.setColumnWidth(l, 20 * 256); |
|---|
| | | // } |
|---|
| | | // |
|---|
| | | // //将数据添加到表格中 |
|---|
| | | // List<String> data = null; |
|---|
| | | // for (int l = 0; l < datas.get(i).size(); l++) { |
|---|
| | | // hssfRow = hssfSheet.createRow(l + 1); |
|---|
| | | // data = datas.get(i).get(l); |
|---|
| | | // for (int j = 0; j < data.size(); j++) { |
|---|
| | | // HSSFCell hssfCell = hssfRow.createCell(j); |
|---|
| | | // hssfCell.setCellType(CellType.STRING);//设置表格类型 |
|---|
| | | // hssfCell.setCellValue(data.get(j)); |
|---|
| | | // } |
|---|
| | | // } |
|---|
| | | // } |
|---|
| | | |
|---|
| | | |
|---|
| | | //获取月份的最大天数 |
|---|
| | | int m = Integer.parseInt(month.split("-")[1]); |
|---|
| | | LocalDate parse1 = LocalDate.parse(month + "-01", DateTimeFormatter.ofPattern("yyyy-MM-dd")); |
|---|
| | | int dayOfMonth = parse1.with(TemporalAdjusters.lastDayOfMonth()).getDayOfMonth(); |
|---|
| | | //构建表头第二行数据 |
|---|
| | | ExportSchedulingDetailDto exportSchedulingDetailDto = new ExportSchedulingDetailDto(); |
|---|
| | | exportSchedulingDetailDto.setFleetName(""); |
|---|
| | | for (int i = 1; i <= dayOfMonth; i++) { |
|---|
| | | LocalDate parse = LocalDate.parse(month + "-" + String.format("%02d", i), DateTimeFormatter.ofPattern("yyyy-MM-dd")); |
|---|
| | | String week = ""; |
|---|
| | | switch (parse.getDayOfWeek()) { |
|---|
| | | case MONDAY: |
|---|
| | | week = "一"; |
|---|
| | | break; |
|---|
| | | case TUESDAY: |
|---|
| | | week = "二"; |
|---|
| | | break; |
|---|
| | | case WEDNESDAY: |
|---|
| | | week = "三"; |
|---|
| | | break; |
|---|
| | | case THURSDAY: |
|---|
| | | week = "四"; |
|---|
| | | break; |
|---|
| | | case FRIDAY: |
|---|
| | | week = "五"; |
|---|
| | | break; |
|---|
| | | case SATURDAY: |
|---|
| | | week = "六"; |
|---|
| | | break; |
|---|
| | | case SUNDAY: |
|---|
| | | week = "日"; |
|---|
| | | break; |
|---|
| | | } |
|---|
| | | setEnglishDay(i, exportSchedulingDetailDto, week); |
|---|
| | | } |
|---|
| | | exportSchedulingDetailDto.setAllDay("全天"); |
|---|
| | | exportSchedulingDetailDto.setMorning("早"); |
|---|
| | | exportSchedulingDetailDto.setMidDay("中"); |
|---|
| | | exportSchedulingDetailDto.setEvening("晚"); |
|---|
| | | exportSchedulingDetailDtos.add(exportSchedulingDetailDto); |
|---|
| | | HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); |
|---|
| | | HSSFSheet hssfSheet = hssfWorkbook.createSheet(); |
|---|
| | | hssfSheet.setColumnWidth(0, 6 * 256); |
|---|
| | | hssfSheet.setDefaultRowHeightInPoints(20f); |
|---|
| | | |
|---|
| | | //添加第一行标题 |
|---|
| | | HSSFRow hssfRow = hssfSheet.createRow(0); |
|---|
| | | List<XpShift> xpShifts = xpShiftService.list(new QueryWrapper<XpShift>().eq("is_deleted", 0)); |
|---|
| | | //设置单元格数量 |
|---|
| | | int totalCell = dayOfMonth + 1 + xpShifts.size(); |
|---|
| | | |
|---|
| | | //设置表头样式，合并单元格 |
|---|
| | | CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0); |
|---|
| | | hssfSheet.addMergedRegion(region); |
|---|
| | | region = new CellRangeAddress(0, 1, dayOfMonth + 1, totalCell); |
|---|
| | | hssfSheet.addMergedRegion(region); |
|---|
| | | |
|---|
| | | |
|---|
| | | //设置单元格数据 |
|---|
| | | for (int i = 0; i < totalCell; i++) { |
|---|
| | | HSSFCell cell = hssfRow.createCell(i); |
|---|
| | | if(0 == i){ |
|---|
| | | cell.setCellValue("车队"); |
|---|
| | | }else if(i <= dayOfMonth){ |
|---|
| | | cell.setCellValue(m + "/" + i); |
|---|
| | | }else{ |
|---|
| | | cell.setCellValue("排班小计"); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | //添加第二行标题 |
|---|
| | | HSSFRow hssfRow1 = hssfSheet.createRow(1); |
|---|
| | | //设置单元格数量 |
|---|
| | | //设置单元格数据 |
|---|
| | | for (int i = 0; i < totalCell; i++) { |
|---|
| | | HSSFCell cell = hssfRow1.createCell(i); |
|---|
| | | if(i > 0 && i <= dayOfMonth){ |
|---|
| | | LocalDate parse = LocalDate.parse(month + "-" + String.format("%02d", i), DateTimeFormatter.ofPattern("yyyy-MM-dd")); |
|---|
| | | String week = ""; |
|---|
| | | switch (parse.getDayOfWeek()) { |
|---|
| | | case MONDAY: |
|---|
| | | week = "一"; |
|---|
| | | break; |
|---|
| | | case TUESDAY: |
|---|
| | | week = "二"; |
|---|
| | | break; |
|---|
| | | case WEDNESDAY: |
|---|
| | | week = "三"; |
|---|
| | | break; |
|---|
| | | case THURSDAY: |
|---|
| | | week = "四"; |
|---|
| | | break; |
|---|
| | | case FRIDAY: |
|---|
| | | week = "五"; |
|---|
| | | break; |
|---|
| | | case SATURDAY: |
|---|
| | | week = "六"; |
|---|
| | | break; |
|---|
| | | case SUNDAY: |
|---|
| | | week = "日"; |
|---|
| | | break; |
|---|
| | | } |
|---|
| | | cell.setCellValue(week); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | //构建数据部分的内容 |
|---|
| | | int rowNum = 2; |
|---|
| | | for (SchedulingDetailDto schedulingDetailDto : schedulingDetailDtos) { |
|---|
| | | exportSchedulingDetailDto = new ExportSchedulingDetailDto(); |
|---|
| | | exportSchedulingDetailDto.setFleetName(schedulingDetailDto.getFleetName()); |
|---|
| | | HSSFRow row = hssfSheet.createRow(rowNum); |
|---|
| | | row.createCell(0).setCellValue(schedulingDetailDto.getFleetName()); |
|---|
| | | |
|---|
| | | String days = schedulingDetailDto.getDays(); |
|---|
| | | if (StringUtils.isNotBlank(days)) { |
|---|
| | | String[] split = days.split(","); |
|---|
| | |
|---|
| | | break; |
|---|
| | | } |
|---|
| | | } |
|---|
| | | setEnglishDay(i, exportSchedulingDetailDto, shiftName); |
|---|
| | | } |
|---|
| | | long allDay = list.stream().filter(s -> s.contains("全天")).count(); |
|---|
| | | long morning = list.stream().filter(s -> s.contains("早")).count(); |
|---|
| | | long midDay = list.stream().filter(s -> s.contains("中")).count(); |
|---|
| | | long evening = list.stream().filter(s -> s.contains("晚")).count(); |
|---|
| | | exportSchedulingDetailDto.setAllDay(allDay + ""); |
|---|
| | | exportSchedulingDetailDto.setMorning(morning + ""); |
|---|
| | | exportSchedulingDetailDto.setMidDay(midDay + ""); |
|---|
| | | exportSchedulingDetailDto.setEvening(evening + ""); |
|---|
| | | } else { |
|---|
| | | for (int i = 1; i <= 31; i++) { |
|---|
| | | String shiftName = "休"; |
|---|
| | | setEnglishDay(i, exportSchedulingDetailDto, shiftName); |
|---|
| | | } |
|---|
| | | exportSchedulingDetailDto.setAllDay("0"); |
|---|
| | | exportSchedulingDetailDto.setMorning("0"); |
|---|
| | | exportSchedulingDetailDto.setMidDay("0"); |
|---|
| | | exportSchedulingDetailDto.setEvening("0"); |
|---|
| | | } |
|---|
| | | exportSchedulingDetailDtos.add(exportSchedulingDetailDto); |
|---|
| | | } |
|---|
| | | |
|---|
| | | String[] split = month.split("-"); |
|---|
| | | ExcelUtil<ExportSchedulingDetailDto> excelUtil = new ExcelUtil<>(ExportSchedulingDetailDto.class); |
|---|
| | | excelUtil.exportExcel(response, exportSchedulingDetailDtos, "排班计划[日期：" + split[0] + "年" + split[1] + "月]"); |
|---|
| | | row.createCell(i).setCellValue(shiftName); |
|---|
| | | } |
|---|
| | | |
|---|
| | | for (int i = 0; i < xpShifts.size(); i++) { |
|---|
| | | String shiftName = xpShifts.get(i).getShiftName(); |
|---|
| | | long allDay = list.stream().filter(s -> s.contains(shiftName)).count(); |
|---|
| | | row.createCell(dayOfMonth + 1 + i).setCellValue(allDay + allDay); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | | } |
|---|
| | | |
|---|
