Jeecg 导出复杂表格

要求：
根据驾驶员信息，导出相应的排班信息
如下图所示：

分析：
jeecg支持的普通导出满足不了我的表头需求，表头后面的站点和发车时间是两个维度，而且数量不确定，所以，使用jeecg的通用下载就不行了。只能自己手动生成excel了。

废话不多说，代码如下：

Controller层：

@Slf4j
@Api(tags="驾驶员")
@RestController
@RequestMapping("/driver/management")
public class BusDriverManagementController extends BaseController<BusDriverManagement, IBusDriverManagementService> {
	@Autowired
	private IBusDriverManagementService busDriverManagementService;
	 /**
	  * 导出驾驶员班次excel
	  *
	  * @param busDriverManagement
	  */
	 @RequestMapping(value = "/export-driver-shift-xlsx")
	 public void exportDriverXls( BusDriverManagement busDriverManagement,HttpServletRequest request, HttpServletResponse response)  {
	 	//获取驾驶员班次信息表；
		List<BusDriverLineExportVO> driverList = busDriverManagementService.getDriverShiftList(busDriverManagement);
		//获取最大站点
		 Long siteNum = busDriverManagementService.getSiteNum(busDriverManagement);
		 try {
			 busDriverManagementService.exportDriverXls(request,response,driverList,siteNum);
		 } catch (IOException e) {
		 	throw new HyException("导出失败");
		 }

	 }
	 }

Service层：

@Service
public class
BusDriverManagementServiceImpl extends ServiceImpl<BusDriverManagementMapper, BusDriverManagement> implements IBusDriverManagementService {
   @Override
    public void exportDriverXls(HttpServletRequest request, HttpServletResponse response, List<BusDriverLineExportVO> driverList, Long siteNum) throws IOException {

        // 创建XSSFWorkbook对象(excel的文档对象)
        XSSFWorkbook wb = new XSSFWorkbook();
        String sheetName = "驾驶员班次";
        // 建立新的sheet对象（excel的表单） 并设置sheet名字
        XSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultRowHeightInPoints(CommonConstant.EXCEL_DEFAULT_ROW_HEIGHT);// 设置缺省列高
        sheet.setDefaultColumnWidth(CommonConstant.EXCEL_DEFAULT_COLUMN_WIDTH);//设置缺省列宽
        //----------------单元格样式----------------------------------
        //表格样式
        XSSFCellStyle cellStyle = getXssfCellStyle(wb);
        //----------------------------------------------------------
        //表头
        createExcelHeader(siteNum, sheet, cellStyle);
        //填充内容
        createExcelContent(driverList, sheet, cellStyle);
        //通过输出流进行文件下载
        ServletOutputStream out = response.getOutputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=report.xlsx");
        wb.write(out);

        out.flush();
        out.close();
        wb.close();

    }

    private void createExcelContent(List<BusDriverLineExportVO> driverList, XSSFSheet sheet, XSSFCellStyle cellStyle) {
        Set<String> driverSet = new HashSet<>();
        Set<String> lineSet = new HashSet<>();
        XSSFCell cell;
        XSSFRow row;
        for (int i = 0; i < driverList.size(); i++) {
            BusDriverLineExportVO driver = driverList.get(i);
            row = sheet.createRow( 2+i*2);
            if (!driverSet.contains(driver.getWorkNo())) {
                driverSet.add(driver.getWorkNo());
                Long count = driverList.stream().filter(dr -> dr.getWorkNo().equals(driver.getWorkNo())).count();
                sheet.addMergedRegion(new CellRangeAddress(2 + i*2, (i + count.intValue()) * 2+1, 0, 0));
                cell = row.createCell(0);
                cell.setCellValue(driver.getDriverName());
                cell.setCellStyle(cellStyle);
            }
            if (!lineSet.contains(driver.getLineId())) {
                lineSet.add(driver.getLineId());
                Long count = driverList.stream().filter(dr -> dr.getLineId().equals(driver.getLineId())).count();
                sheet.addMergedRegion(new CellRangeAddress(2 + i*2, (i + count.intValue()) * 2+1, 1, 1));
                cell = row.createCell(1);
                cell.setCellValue(driver.getLineName());
                cell.setCellStyle(cellStyle);
            }
            sheet.addMergedRegion(new CellRangeAddress(2 + i*2, i * 2+3, 2, 2));
            cell = row.createCell(2);
            cell.setCellValue(driver.getShiftName());
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(2 + i*2, i * 2+3, 3, 3));
            cell = row.createCell(3);
            cell.setCellValue(driver.getCarNo());
            cell.setCellStyle(cellStyle);
            for (int j = 0; j < driver.getSiteList().size(); j++) {
                cell = row.createCell(4+j);
                cell.setCellValue(driver.getSiteList().get(j));
                cell.setCellStyle(cellStyle);
            }
            row = sheet.createRow( i*2+3);
            for (int k = 0; k < driver.getDepartureTimeList().size(); k++) {
                cell = row.createCell(4+k);
                cell.setCellValue(driver.getDepartureTimeList().get(k));
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private void createExcelHeader(Long siteNum, XSSFSheet sheet, XSSFCellStyle cellStyle) {
        XSSFCell cell;
        XSSFRow row;
        // ----------------------创建第一行---------------
        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        row = sheet.createRow(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        cell = row.createCell(0);
        // 设置单元格内容
        cell.setCellValue("驾驶员");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        cell = row.createCell(1);
        cell.setCellValue("线路名称");
        cell.setCellStyle(cellStyle);

        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
        cell = row.createCell(2);
        cell.setCellValue("班次");
        cell.setCellStyle(cellStyle);

        sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
        cell = row.createCell(3);
        cell.setCellValue("车号");
        cell.setCellStyle(cellStyle);
        if(siteNum !=null) {
            for (int i = 1; i <= siteNum.intValue(); i++) {
                cell = row.createCell(3 + i);
                cell.setCellValue("站点" + i);
                cell.setCellStyle(cellStyle);
            }
        }
        // ----------------------------------------------
        // ------------------创建第二行---------------------
        row = sheet.createRow(1);
        if(siteNum !=null) {
            for (int i = 1; i <= siteNum.intValue(); i++) {
                cell = row.createCell(3 + i);
                cell.setCellValue("发车时间");
                cell.setCellStyle(cellStyle);
            }
        }
        //-------------------------表头end---------------------
    }

    private XSSFCellStyle getXssfCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font cellFont = wb.createFont();
        cellFont.setItalic(false);                     // 设置字体为斜体字
        cellFont.setColor(Font.COLOR_NORMAL);            // 将字体设置为“红色”
        cellFont.setFontHeightInPoints((short) 10);    // 将字体大小设置为18px
        cellFont.setFontName("宋体");             // 字体应用到当前单元格上
//        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyle.setFont(cellFont);
        cellStyle.setWrapText(true);//设置自动换行
        return cellStyle;
    }

}

核心代码：service层中的exportDriverXls方法；