要求：
根据驾驶员信息，导出相应的排班信息
如下图所示：
分析：
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方法；