本文介绍了一个用于生成行车日志及多种监控报表的方法，包括实时违章处理、定位抽查、北斗卫星监控等，通过Excel表格形式展示各种数据，便于进行数据分析与管理。
public String allExcel(DailyReportParam queryParam) throws Exception {
// 创建文件路径
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
String dateString = simpleDateFormat.format(new Date());
String dir = "/upload/" + (StringUtils.isNotEmpty(webApiConfig.getSystemName()) == true
? webApiConfig.getSystemName() + "/" + dateString
: dateString);
File dirF = new File(multipartLocation + "/upload/"
+ (StringUtils.isNotEmpty(webApiConfig.getSystemName()) == true
? webApiConfig.getSystemName() + "/" + dateString
: dateString));
if (!dirF.exists()) {
if (!dirF.mkdirs()) {
throw new Exception("创建保存目录失败");
}
}
String fileName = UUID.randomUUID().toString() + ".xlsx";
String fullFileName = multipartLocation + dir + "/" + fileName;
// 获取企业名称、企业类型
SysOrgVo sysOrgVo = dailyDrivingLogMapper.selectOrgName(queryParam.getOrgId());
String orgType = "";
if (sysOrgVo.getCompantType() != null) {
if (sysOrgVo.getCompantType().equals(CompanyTrasportationTypeEnum.PASSENGER.getCode())) {
orgType = CompanyTrasportationTypeEnum.PASSENGER.getDesc();
}
if (sysOrgVo.getCompantType().equals(CompanyTrasportationTypeEnum.DANGEROUS.getCode())) {
orgType = CompanyTrasportationTypeEnum.DANGEROUS.getDesc();
}
}
// 获取日期
SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy年MM月dd日");
SimpleDateFormat theTimeFormat = new SimpleDateFormat("yyyy-MM-dd");
String theTime = queryParam.getDays();
Date date = theTimeFormat.parse(queryParam.getDays());
theTime = yearFormat.format(date);
DailyReportVo vo = findAll(queryParam);
FileOutputStream fileOut = null;
BufferedImage bufferImg = null;
HSSFWorkbook wb = null;
try {
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
// URLConnection urlConnection = null;
// URL url = null;
// InputStream is = null;
// 创建工作sheet
wb = new HSSFWorkbook();
HSSFSheet sheetDrivingLog = wb.createSheet("行车日志（前）");
// 设置标题单元格内容水平垂直居中
HSSFCellStyle styleDrivingLog = wb.createCellStyle();
styleDrivingLog.setVerticalAlignment(VerticalAlignment.CENTER);
styleDrivingLog.setAlignment(HorizontalAlignment.CENTER);
styleDrivingLog.setBorderBottom(BorderStyle.THIN); // 下边框
styleDrivingLog.setBorderLeft(BorderStyle.THIN);// 左边框
styleDrivingLog.setBorderTop(BorderStyle.THIN);// 上边框
styleDrivingLog.setBorderRight(BorderStyle.THIN);// 右边框
// 设置标题字体大小
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setBold(true);
font.setFontHeightInPoints((short) 14);
styleDrivingLog.setFont(font);
// 设置表头单元格内容水平垂直居中
HSSFCellStyle styleDrivingLogTitle = wb.createCellStyle();
styleDrivingLogTitle.setVerticalAlignment(VerticalAlignment.CENTER);
styleDrivingLogTitle.setAlignment(HorizontalAlignment.CENTER);
styleDrivingLogTitle.setBorderBottom(BorderStyle.THIN); // 下边框
styleDrivingLogTitle.setBorderLeft(BorderStyle.THIN);// 左边框
styleDrivingLogTitle.setBorderTop(BorderStyle.THIN);// 上边框
styleDrivingLogTitle.setBorderRight(BorderStyle.THIN);// 右边框
// 设置表头字体大小
HSSFFont fontTitle = wb.createFont();
fontTitle.setFontName("宋体");
fontTitle.setBold(true);
fontTitle.setFontHeightInPoints((short) 12);
styleDrivingLogTitle.setFont(fontTitle);
// 设置表格内容水平垂直居中
HSSFCellStyle styleTable = wb.createCellStyle();
styleTable.setVerticalAlignment(VerticalAlignment.CENTER);
styleTable.setAlignment(HorizontalAlignment.CENTER);
styleTable.setWrapText(true);
styleTable.setBorderBottom(BorderStyle.THIN); // 下边框
styleTable.setBorderLeft(BorderStyle.THIN);// 左边框
styleTable.setBorderTop(BorderStyle.THIN);// 上边框
styleTable.setBorderRight(BorderStyle.THIN);// 右边框
// 设置表格字体大小
HSSFFont fontTable = wb.createFont();
fontTable.setFontName("宋体");
fontTable.setFontHeightInPoints((short) 11);
styleTable.setFont(fontTable);
// 设置注字体格式
HSSFCellStyle styleNote = wb.createCellStyle();
styleNote.setBorderBottom(BorderStyle.THIN); // 下边框
styleNote.setBorderLeft(BorderStyle.THIN);// 左边框
styleNote.setBorderTop(BorderStyle.THIN);// 上边框
styleNote.setBorderRight(BorderStyle.THIN);// 右边框
// 设置表格字体大小
HSSFFont fontNote = wb.createFont();
fontNote.setFontName("宋体");
fontNote.setFontHeightInPoints((short) 11);
styleNote.setFont(fontNote);
// 设置表头下日期单元格内容水平垂直居右
HSSFCellStyle styleDrivingLogTitleDate = wb.createCellStyle();
styleDrivingLogTitleDate.setVerticalAlignment(VerticalAlignment.CENTER);
styleDrivingLogTitleDate.setAlignment(HorizontalAlignment.RIGHT);
styleDrivingLogTitleDate.setBorderBottom(BorderStyle.THIN); // 下边框
styleDrivingLogTitleDate.setBorderLeft(BorderStyle.THIN);// 左边框
styleDrivingLogTitleDate.setBorderTop(BorderStyle.THIN);// 上边框
styleDrivingLogTitleDate.setBorderRight(BorderStyle.THIN);// 右边框
// 设置表头字体大小
HSSFFont fontTitleDate = wb.createFont();
fontTitleDate.setFontName("宋体");
fontTitleDate.setFontHeightInPoints((short) 12);
styleDrivingLogTitleDate.setFont(fontTitleDate);
// 设置时间格式
HSSFCellStyle style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
style.setFont(fontTable);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN); // 下边框
style.setBorderLeft(BorderStyle.THIN);// 左边框
style.setBorderTop(BorderStyle.THIN);// 上边框
style.setBorderRight(BorderStyle.THIN);// 右边框
HSSFCellStyle styleTime = wb.createCellStyle();
styleTime.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd HH:mm"));
styleTime.setFont(fontTable);
styleTime.setVerticalAlignment(VerticalAlignment.CENTER);
styleTime.setAlignment(HorizontalAlignment.CENTER);
styleTime.setBorderBottom(BorderStyle.THIN); // 下边框
styleTime.setBorderLeft(BorderStyle.THIN);// 左边框
styleTime.setBorderTop(BorderStyle.THIN);// 上边框
styleTime.setBorderRight(BorderStyle.THIN);// 右边框
HSSFCellStyle styleDate = wb.createCellStyle();
styleDate.setDataFormat(wb.createDataFormat().getFormat("HH:mm"));
styleDate.setFont(fontTable);
styleDate.setVerticalAlignment(VerticalAlignment.CENTER);
styleDate.setAlignment(HorizontalAlignment.CENTER);
styleDate.setBorderBottom(BorderStyle.THIN); // 下边框
styleDate.setBorderLeft(BorderStyle.THIN);// 左边框
styleDate.setBorderTop(BorderStyle.THIN);// 上边框
styleDate.setBorderRight(BorderStyle.THIN);// 右边框
HSSFCellStyle styleDay = wb.createCellStyle();
styleDay.setDataFormat(wb.createDataFormat().getFormat("yyyy-MM-dd"));
styleDay.setFont(fontTable);
styleDay.setVerticalAlignment(VerticalAlignment.CENTER);
styleDay.setAlignment(HorizontalAlignment.CENTER);
styleDay.setBorderBottom(BorderStyle.THIN); // 下边框
styleDay.setBorderLeft(BorderStyle.THIN);// 左边框
styleDay.setBorderTop(BorderStyle.THIN);// 上边框
styleDay.setBorderRight(BorderStyle.THIN);// 右边框
HSSFCellStyle styleMoment = wb.createCellStyle();
styleMoment.setDataFormat(wb.createDataFormat().getFormat("HH:mm:ss"));
styleMoment.setFont(fontTable);
styleMoment.setVerticalAlignment(VerticalAlignment.CENTER);
styleMoment.setAlignment(HorizontalAlignment.CENTER);
styleMoment.setBorderBottom(BorderStyle.THIN); // 下边框
styleMoment.setBorderLeft(BorderStyle.THIN);// 左边框
styleMoment.setBorderTop(BorderStyle.THIN);// 上边框
styleMoment.setBorderRight(BorderStyle.THIN);// 右边框
HSSFRow row0DrivingLog = sheetDrivingLog.createRow(0);
row0DrivingLog.setHeightInPoints(40);
HSSFRow row1DrivingLog = sheetDrivingLog.createRow(1);
row1DrivingLog.setHeightInPoints(30);
HSSFRow row2DrivingLog = sheetDrivingLog.createRow(2);
row2DrivingLog.setHeightInPoints(25);
// 设置标题
String[] titlesDrivingLog = { "序号", "车牌号", "驾驶员姓名", "身份证号", "驾驶员状态", "行车时间", "出发地", "目的地", "备注" };
if (titlesDrivingLog.length == 0) {
return "error";
}
HSSFCell cellDrivingLog = null;
// 获取总行数
int rowsDrivingLog = vo.getDailyDrivingLogList().size();
// 合并单元格生成大标题
sheetDrivingLog.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
sheetDrivingLog.addMergedRegion(new CellRangeAddress(1, 1, 0, 8));
sheetDrivingLog.addMergedRegion(new CellRangeAddress(rowsDrivingLog + 3, rowsDrivingLog + 3, 0, 8));
cellDrivingLog = row0DrivingLog.createCell(0);
cellDrivingLog.setCellValue(sysOrgVo.getOrgName() + "车辆行车日志");
cellDrivingLog.setCellStyle(styleDrivingLog);
// 添加注
HSSFRow rowDrivingLogNote = sheetDrivingLog.createRow(rowsDrivingLog + 3);
cellDrivingLog = rowDrivingLogNote.createCell(0);
cellDrivingLog.setCellValue("每日与企业按约定时间对接行车日志信息");
cellDrivingLog.setCellStyle(styleNote);
// 第二行、标题行列
for (int i = 0; i < titlesDrivingLog.length; i++) {
cellDrivingLog = row2DrivingLog.createCell(i); // 第一个单元格
cellDrivingLog.setCellValue(titlesDrivingLog[i]); // 设定值
cellDrivingLog.setCellStyle(styleDrivingLogTitle);
sheetDrivingLog.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellDrivingLog = row0DrivingLog.createCell(i);
cellDrivingLog.setCellStyle(styleDrivingLog);
cellDrivingLog = row1DrivingLog.createCell(i);
cellDrivingLog.setCellStyle(styleNote);
cellDrivingLog = rowDrivingLogNote.createCell(i);
cellDrivingLog.setCellStyle(styleNote);
}
}
HSSFRow rowDrivingLog = null;
// HSSFCell cellRow = null;
if (rowsDrivingLog > 0) {
for (int i = 0; i < rowsDrivingLog; i++) {
// 创建行
rowDrivingLog = sheetDrivingLog.createRow(i + 3);
// 行数据处理
rowDrivingLog.createCell(0).setCellValue(vo.getDailyDrivingLogList().get(i).getRownum());
rowDrivingLog.createCell(1).setCellValue(vo.getDailyDrivingLogList().get(i).getVehicleNo());
rowDrivingLog.createCell(2).setCellValue(vo.getDailyDrivingLogList().get(i).getDriverName());
rowDrivingLog.createCell(3).setCellValue(vo.getDailyDrivingLogList().get(i).getDriverIdnumber());
rowDrivingLog.createCell(4).setCellValue(vo.getDailyDrivingLogList().get(i).getDirverState());
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String startTime = dateFormat.format(vo.getDailyDrivingLogList().get(i).getStartTime());
String endTime = dateFormat.format(vo.getDailyDrivingLogList().get(i).getEndTime());
rowDrivingLog.createCell(5).setCellValue(startTime + "至" + endTime);
rowDrivingLog.createCell(6).setCellValue(vo.getDailyDrivingLogList().get(i).getDeparture());
rowDrivingLog.createCell(7).setCellValue(vo.getDailyDrivingLogList().get(i).getDestination());
rowDrivingLog.createCell(8).setCellValue(vo.getDailyDrivingLogList().get(i).getMemo());
// 表格内容字体格式
rowDrivingLog.getCell(0).setCellStyle(styleTable);
rowDrivingLog.getCell(1).setCellStyle(styleTable);
rowDrivingLog.getCell(2).setCellStyle(styleTable);
rowDrivingLog.getCell(3).setCellStyle(styleTable);
rowDrivingLog.getCell(4).setCellStyle(styleTable);
rowDrivingLog.getCell(5).setCellStyle(styleTable);
rowDrivingLog.getCell(6).setCellStyle(styleTable);
rowDrivingLog.getCell(7).setCellStyle(styleTable);
rowDrivingLog.getCell(8).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetDrivingLog.setColumnWidth(0, 6 * 256);
sheetDrivingLog.setColumnWidth(1, 10 * 256);
sheetDrivingLog.setColumnWidth(2, 15 * 256);
sheetDrivingLog.setColumnWidth(3, 20 * 256);
sheetDrivingLog.setColumnWidth(4, 15 * 256);
sheetDrivingLog.setColumnWidth(5, 15 * 256);
sheetDrivingLog.setColumnWidth(6, 15 * 256);
sheetDrivingLog.setColumnWidth(7, 15 * 256);
sheetDrivingLog.setColumnWidth(8, 15 * 256);
cellDrivingLog = row1DrivingLog.createCell(0);
cellDrivingLog.setCellValue("日期： " + theTime);
cellDrivingLog.setCellStyle(styleDrivingLogTitleDate);
HSSFSheet sheetVehicleViolation = wb.createSheet("实时违章处理明细表（中）");
// 设置单元格内容水平垂直居中
HSSFCellStyle styleVehicleViolation = wb.createCellStyle();
styleVehicleViolation.setVerticalAlignment(VerticalAlignment.CENTER);
styleVehicleViolation.setAlignment(HorizontalAlignment.CENTER);
HSSFRow row0VehicleViolation = sheetVehicleViolation.createRow(0);
row0VehicleViolation.setHeightInPoints(40);
HSSFRow row1VehicleViolation = sheetVehicleViolation.createRow(1);
row1VehicleViolation.setHeightInPoints(25);
// 设置标题
String[] titlesVehicleViolation = { "序号", "行业", "公司名称", "车牌号", "违章时间", "违章类别", "违章地点", "行车速度", "违章行为结束时间",
"TTS语音下发时间", "企业相关负责人联系情况", "备注" };
if (titlesVehicleViolation.length == 0) {
return "error";
}
HSSFCell cellVehicleViolation = null;
// 获取总行数
int rowsVehicleViolation = vo.getDailyVehicleViolationList().size();
// 合并单元格生成大标题
sheetVehicleViolation.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
sheetVehicleViolation
.addMergedRegion(new CellRangeAddress(rowsVehicleViolation + 2, rowsVehicleViolation + 2, 0, 11));
// 创建单元格赋值
cellVehicleViolation = row0VehicleViolation.createCell(0);
cellVehicleViolation.setCellValue(sysOrgVo.getOrgName() + "车辆实时违章处理情况表（" + theTime + "）");
cellVehicleViolation.setCellStyle(styleDrivingLog);
// 添加注
HSSFRow rowVehicleViolationNote = sheetVehicleViolation.createRow(rowsVehicleViolation + 2);
cellVehicleViolation = rowVehicleViolationNote.createCell(0);
cellVehicleViolation.setCellValue("注：1、次日9点给运输企业及客户关系部主管发送前一天的违章情况处理情况表。\r\n"
+ " 2、TTS语音下发30秒内，车辆停止超速行为可以不必通知押运员，如TTS语音下发30秒之后，车辆仍持续超速行为，数控安全人员应及时通知1押运员2企业相关负责人，并做好相应记录。\r\n"
+ " 3、TTS语音下发5分钟内，车辆停止疲劳驾驶行为可以不必通知押运员，如TTS语音下发5分钟之后，车辆仍持续疲劳驾驶行为，数控安全人员应及时通知1押运员2企业相关负责人，并做好相应记录。");
// 强制自动换行
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
cellStyle.setFont(fontTable);
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
cellVehicleViolation.setCellStyle(cellStyle);
rowVehicleViolationNote.setHeightInPoints(40);
// 第二行、标题行列
for (int i = 0; i < titlesVehicleViolation.length; i++) {
cellVehicleViolation = row1VehicleViolation.createCell(i); // 第一个单元格
cellVehicleViolation.setCellValue(titlesVehicleViolation[i]); // 设定值
cellVehicleViolation.setCellStyle(styleDrivingLogTitle);
sheetVehicleViolation.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellVehicleViolation = row0VehicleViolation.createCell(i);
cellVehicleViolation.setCellStyle(styleDrivingLog);
cellVehicleViolation = rowVehicleViolationNote.createCell(i);
cellVehicleViolation.setCellStyle(styleNote);
}
}
HSSFRow rowVehicleViolation = null;
// HSSFCell cellRow = null;
if (rowsVehicleViolation > 0) {
for (int i = 0; i < rowsVehicleViolation; i++) {
// 创建行
rowVehicleViolation = sheetVehicleViolation.createRow(i + 2);
// 行数据处理
rowVehicleViolation.createCell(0)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getRownum());
rowVehicleViolation.createCell(1)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getIndustryName());
rowVehicleViolation.createCell(2)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getOrgName());
rowVehicleViolation.createCell(3)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getVehicleNo());
rowVehicleViolation.createCell(4).setCellStyle(style);
rowVehicleViolation.getCell(4)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getViolationTime());
rowVehicleViolation.createCell(5)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getViolationTypeName());
rowVehicleViolation.createCell(6)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getViolationPlace());
rowVehicleViolation.createCell(7)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getViolationSpeed());
rowVehicleViolation.createCell(8).setCellStyle(style);
if (vo.getDailyVehicleViolationList().get(i).getViolationEndTime() != null) {
rowVehicleViolation.getCell(8)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getViolationEndTime());
}
rowVehicleViolation.createCell(9).setCellStyle(style);
if (vo.getDailyVehicleViolationList().get(i).getTtsTime() != null) {
rowVehicleViolation.getCell(9)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getTtsTime());
}
rowVehicleViolation.createCell(10)
.setCellValue(vo.getDailyVehicleViolationList().get(i).getLeaderMemoName());
rowVehicleViolation.createCell(11).setCellValue(vo.getDailyVehicleViolationList().get(i).getMemo());
// 表格内容字体格式
rowVehicleViolation.getCell(0).setCellStyle(styleTable);
rowVehicleViolation.getCell(1).setCellStyle(styleTable);
rowVehicleViolation.getCell(2).setCellStyle(styleTable);
rowVehicleViolation.getCell(3).setCellStyle(styleTable);
rowVehicleViolation.getCell(5).setCellStyle(styleTable);
rowVehicleViolation.getCell(6).setCellStyle(styleTable);
rowVehicleViolation.getCell(7).setCellStyle(styleTable);
rowVehicleViolation.getCell(10).setCellStyle(styleTable);
rowVehicleViolation.getCell(11).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetVehicleViolation.setColumnWidth(0, 6 * 256);
sheetVehicleViolation.setColumnWidth(1, 10 * 256);
sheetVehicleViolation.setColumnWidth(2, 25 * 256);
sheetVehicleViolation.setColumnWidth(3, 20 * 256);
sheetVehicleViolation.setColumnWidth(4, 30 * 256);
sheetVehicleViolation.setColumnWidth(5, 20 * 256);
sheetVehicleViolation.setColumnWidth(6, 20 * 256);
sheetVehicleViolation.setColumnWidth(7, 30 * 256);
sheetVehicleViolation.setColumnWidth(8, 30 * 256);
sheetVehicleViolation.setColumnWidth(9, 30 * 256);
sheetVehicleViolation.setColumnWidth(10, 30 * 256);
sheetVehicleViolation.setColumnWidth(11, 30 * 256);
// 创建工作sheet
HSSFSheet sheetDevice = wb.createSheet("定位、视频抽查情况表（中）");
// 设置单元格内容水平垂直居中
HSSFCellStyle styleDevice = wb.createCellStyle();
styleDevice.setVerticalAlignment(VerticalAlignment.CENTER);
styleDevice.setAlignment(HorizontalAlignment.CENTER);
// 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
HSSFPatriarch patriarchDevice = sheetDevice.createDrawingPatriarch();
HSSFRow row0Device = sheetDevice.createRow(0);
row0Device.setHeightInPoints(40);
HSSFRow row1Device = sheetDevice.createRow(1);
row1Device.setHeightInPoints(25);
// 设置标题
String[] titlesDevice = { "序号", "车牌号", "抽查日期", "抽查时间", "行车地点", "行车速度", "在线情况", "摄像头", "使用状态", "TTS语音下发时间",
"企业相关负责人联系情况", "纠正情况", "备注", "抽查详情（图片）" };
if (titlesDevice.length == 0) {
return "error";
}
HSSFCell cellDevice = null;
// 获取总行数
int rowsDevice = vo.getDailyDeviceList().size();
// 合并单元格生成大标题
sheetDevice.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));
sheetDevice.addMergedRegion(new CellRangeAddress(rowsDevice + 2, rowsDevice + 2, 0, 13));
cellDevice = row0Device.createCell(0); // 第一个单元格
// cellDevice.setCellValue(sysOrgVo.getOrgName() + "定位视频抽查情况表（" +
// queryParam.getDays() + "）"); // 设定值
cellDevice.setCellValue(sysOrgVo.getOrgName() + "定位视频抽查情况表"); // 设定值
cellDevice.setCellStyle(styleDrivingLog);
cellDevice = row0Device.createCell(13); // 第14个单元格
cellDevice.setCellStyle(styleDrivingLog);
// 添加注
HSSFRow rowDeviceNote = sheetDevice.createRow(rowsDevice + 2);
cellDevice = rowDeviceNote.createCell(0);
cellDevice.setCellValue(
"注：1、摄像头正常，截实时图片至表内；摄像头异常，异常描述填制表内，次日9：30给运输企业及客户关系部主管发送前一天的抽查情况表\r\n" + " 2、抽查比例为企业车辆数的10%");
// 强制自动换行
cellDevice.setCellStyle(cellStyle);
rowDeviceNote.setHeightInPoints(40);
// 第二行、标题行列
for (int i = 0; i < titlesDevice.length; i++) {
cellDevice = row1Device.createCell(i); // 第一个单元格
cellDevice.setCellValue(titlesDevice[i]); // 设定值
cellDevice.setCellStyle(styleDrivingLogTitle);
sheetDevice.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellDevice = row0Device.createCell(i);
cellDevice.setCellStyle(styleDrivingLog);
cellDevice = rowDeviceNote.createCell(i);
cellDevice.setCellStyle(styleNote);
}
}
// HSSFCell cellRow = null;
HSSFClientAnchor anchorDevice = null;
HSSFRow rowDevice = null;
if (rowsDevice > 0) {
for (int i = 0; i < rowsDevice; i++) {
int cellColumn = i + 2;
// 创建行
rowDevice = sheetDevice.createRow(i + 2);
rowDevice.setHeightInPoints(80);
// 设置默认行高
// rowDevice.setHeightInPoints(150);
// 行数据处理
rowDevice.createCell(0).setCellValue(vo.getDailyDeviceList().get(i).getRownum());
rowDevice.createCell(1).setCellValue(vo.getDailyDeviceList().get(i).getVehicleNo());
rowDevice.createCell(2).setCellStyle(styleDay);
rowDevice.getCell(2).setCellValue(vo.getDailyDeviceList().get(i).getCheckTime());
rowDevice.createCell(3).setCellStyle(styleDate);
rowDevice.getCell(3).setCellValue(vo.getDailyDeviceList().get(i).getCheckTime());
rowDevice.createCell(4).setCellValue(vo.getDailyDeviceList().get(i).getPosition());
rowDevice.createCell(5).setCellValue(vo.getDailyDeviceList().get(i).getVehicleSpeed());
rowDevice.createCell(6).setCellValue(vo.getDailyDeviceList().get(i).getGpsState());
rowDevice.createCell(7).setCellValue(vo.getDailyDeviceList().get(i).getCarmeraMemoName());
rowDevice.createCell(8).setCellValue(vo.getDailyDeviceList().get(i).getUseStateName());
if (vo.getDailyDeviceList().get(i).getTtsTime() != null) {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String ttsTime = dateFormat.format(vo.getDailyDeviceList().get(i).getTtsTime());
rowDevice.createCell(9).setCellValue(ttsTime + "下发");
}
rowDevice.createCell(10).setCellValue(vo.getDailyDeviceList().get(i).getLeaderMemoName());
rowDevice.createCell(11).setCellValue(vo.getDailyDeviceList().get(i).getCorrectMemoName());
rowDevice.createCell(12).setCellValue(vo.getDailyDeviceList().get(i).getMemo());
// 表格内容字体格式
rowDevice.getCell(0).setCellStyle(styleTable);
rowDevice.getCell(1).setCellStyle(styleTable);
rowDevice.getCell(4).setCellStyle(styleTable);
rowDevice.getCell(5).setCellStyle(styleTable);
rowDevice.getCell(6).setCellStyle(styleTable);
rowDevice.getCell(7).setCellStyle(styleTable);
if (rowDevice.getCell(9) != null) {
rowDevice.getCell(9).setCellStyle(styleTable);
}
rowDevice.getCell(8).setCellStyle(styleTable);
rowDevice.getCell(10).setCellStyle(styleTable);
rowDevice.getCell(11).setCellStyle(styleTable);
rowDevice.getCell(12).setCellStyle(styleTable);
rowDevice.createCell(13).setCellStyle(styleTable);
int imageNo = 13;
// 循环附件列表添加图片
DailyDevice device = vo.getDailyDeviceList().get(i);
for (Attachment attachment : device.getAttachmentList()) {
byteArrayOut = new ByteArrayOutputStream();
// url = new URL(multipartLocation +attachment.getFilePath());
// urlConnection = url.openConnection();
// is = urlConnection.getInputStream();
// BufferedImage image = image.getInstance(multipartLocation +
// attachment.getFilePath());
//BufferedImage img = ImageIO.read(new File(multipartLocation + attachment.getFilePath()));
// BufferedImage img = ImageIO.read(new File("D:/a.jpg"));
if (attachment.getType() != null) {
// 1为外网地址
if (attachment.getType() == AttmentTypeEnum.UNUSED.getCode()) {
BufferedImage img = ImageIO.read(new File(attachment.getFilePath()));
// 获取文件名
String imName = attachment.getFileName() + "." + attachment.getFileSuffix();
// 获取文件后缀
String prefix = imName.substring(imName.lastIndexOf("."));
// 若需要防止生成的临时文件重复,可以在文件名后添加随机码
File file = File.createTempFile(fileName, prefix);
// 获取图片
bufferImg = ImageIO.read(file);
// 将图片写入
ImageIO.write(img, "jpg", byteArrayOut);
// 设置锚点
anchorDevice = new HSSFClientAnchor(0, 0, 1000, 255, (short) imageNo,
(short) cellColumn, (short) imageNo, cellColumn);
// anchor.setAnchorType(3);
// 创建图片
patriarchDevice.createPicture(anchorDevice,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
// 2为内网地址
if (attachment.getType() == AttmentTypeEnum.USED.getCode()) {
BufferedImage img = ImageIO
.read(new File(multipartLocation + attachment.getFilePath()));
// 获取文件名
String imName = attachment.getFileName() + "." + attachment.getFileSuffix();
// 获取文件后缀
String prefix = imName.substring(imName.lastIndexOf("."));
// 若需要防止生成的临时文件重复,可以在文件名后添加随机码
File file = File.createTempFile(fileName, prefix);
// 获取图片
bufferImg = ImageIO.read(file);
// 将图片写入
ImageIO.write(img, "jpg", byteArrayOut);
// 设置锚点
anchorDevice = new HSSFClientAnchor(0, 0, 1000, 255, (short) imageNo,
(short) cellColumn, (short) imageNo, cellColumn);
// anchor.setAnchorType(3);
// 创建图片
patriarchDevice.createPicture(anchorDevice,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
} else {
BufferedImage img = ImageIO.read(new File(multipartLocation + attachment.getFilePath()));
// 获取文件名
String imName = attachment.getFileName() + "." + attachment.getFileSuffix();
// 获取文件后缀
String prefix = imName.substring(imName.lastIndexOf("."));
// 若需要防止生成的临时文件重复,可以在文件名后添加随机码
File file = File.createTempFile(fileName, prefix);
// 获取图片
bufferImg = ImageIO.read(file);
// 将图片写入
ImageIO.write(img, "jpg", byteArrayOut);
// 设置锚点
anchorDevice = new HSSFClientAnchor(0, 0, 1000, 255, (short) imageNo, (short) cellColumn,
(short) imageNo, cellColumn);
// anchor.setAnchorType(3);
// 创建图片
patriarchDevice.createPicture(anchorDevice,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
imageNo++;
}
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetDevice.setColumnWidth(0, 6 * 256);
sheetDevice.setColumnWidth(1, 20 * 256);
sheetDevice.setColumnWidth(2, 15 * 256);
sheetDevice.setColumnWidth(3, 15 * 256);
sheetDevice.setColumnWidth(4, 25 * 256);
sheetDevice.setColumnWidth(5, 25 * 256);
sheetDevice.setColumnWidth(6, 25 * 256);
sheetDevice.setColumnWidth(7, 15 * 256);
sheetDevice.setColumnWidth(8, 25 * 256);
sheetDevice.setColumnWidth(9, 35 * 256);
sheetDevice.setColumnWidth(10, 30 * 256);
sheetDevice.setColumnWidth(11, 30 * 256);
// 创建工作sheet
HSSFSheet sheetDeviceTai = wb.createSheet(orgType + "车辆北斗卫星监控日志（中）");
// 设置单元格内容水平垂直居中
HSSFCellStyle styleDeviceTai = wb.createCellStyle();
styleDeviceTai.setVerticalAlignment(VerticalAlignment.CENTER);
styleDeviceTai.setAlignment(HorizontalAlignment.CENTER);
HSSFRow row0DeviceTai = sheetDeviceTai.createRow(0);
row0DeviceTai.setHeightInPoints(40);
HSSFRow row1DeviceTai = sheetDeviceTai.createRow(1);
row1DeviceTai.setHeightInPoints(30);
HSSFRow row2DeviceTai = sheetDeviceTai.createRow(2);
row2DeviceTai.setHeightInPoints(25);
// 设置标题
String[] titlesDeviceTai = { "序号", "检查时间", "车牌号", "地点", "北斗卫星不正常", "超速", "疲劳驾驶", "2点至5点违规运行", "采取处置措施" };
if (titlesDeviceTai.length == 0) {
return "error";
}
HSSFCell cellDeviceTai = null;
// 获取总行数
int rowsDeviceTai = vo.getDailyDeviceList().size();
// 合并单元格生成大标题
sheetDeviceTai.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
sheetDeviceTai.addMergedRegion(new CellRangeAddress(1, 1, 0, 8));
sheetDeviceTai.addMergedRegion(new CellRangeAddress(rowsDeviceTai + 3, rowsDeviceTai + 3, 0, 1));
sheetDeviceTai.addMergedRegion(new CellRangeAddress(rowsDeviceTai + 3, rowsDeviceTai + 3, 2, 8));
sheetDeviceTai.addMergedRegion(new CellRangeAddress(rowsDeviceTai + 4, rowsDeviceTai + 4, 1, 8));
cellDeviceTai = row0DeviceTai.createCell(0); // 第一个单元格
// cellDeviceTai.setCellValue(sysOrgVo.getOrgName() + orgType + "车辆北斗卫星监控日志（" +
// queryParam.getDays() + "）"); // 设定值
cellDeviceTai.setCellValue(sysOrgVo.getOrgName() + orgType + "车辆北斗卫星监控日志"); // 设定值
cellDeviceTai.setCellStyle(styleDrivingLog);
// 添加注
HSSFRow rowDeviceTaiUp = sheetDeviceTai.createRow(rowsDeviceTai + 3);
cellDeviceTai = rowDeviceTaiUp.createCell(0);
cellDeviceTai.setCellValue("上级巡检回复情况");
cellDeviceTai.setCellStyle(styleNote);
HSSFRow rowDeviceTaiNote = sheetDeviceTai.createRow(rowsDeviceTai + 4);
cellDeviceTai = rowDeviceTaiNote.createCell(0);
cellDeviceTai.setCellValue("备注");
cellDeviceTai.setCellStyle(styleNote);
// 第二行、标题行列
for (int i = 0; i < titlesDeviceTai.length; i++) {
cellDeviceTai = row2DeviceTai.createCell(i); // 第一个单元格
cellDeviceTai.setCellValue(titlesDeviceTai[i]); // 设定值
cellDeviceTai.setCellStyle(styleDrivingLogTitle);
sheetDeviceTai.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellDeviceTai = row0DeviceTai.createCell(i);
cellDeviceTai.setCellStyle(styleDrivingLog);
cellDeviceTai = row1DeviceTai.createCell(i);
cellDeviceTai.setCellStyle(styleDrivingLog);
cellDeviceTai = rowDeviceTaiNote.createCell(i);
cellDeviceTai.setCellStyle(styleNote);
cellDeviceTai = rowDeviceTaiUp.createCell(i);
cellDeviceTai.setCellStyle(styleNote);
}
}
HSSFRow rowDeviceTai = null;
// HSSFCell cellRow = null;
if (rowsDeviceTai > 0) {
for (int i = 0; i < rowsDeviceTai; i++) {
// 创建行
rowDeviceTai = sheetDeviceTai.createRow(i + 3);
// 设置默认行高
// rowDevice.setHeightInPoints(150);
// 行数据处理
rowDeviceTai.createCell(0).setCellValue(vo.getDailyDeviceList().get(i).getRownum());
rowDeviceTai.createCell(2).setCellValue(vo.getDailyDeviceList().get(i).getVehicleNo());
rowDeviceTai.createCell(1).setCellStyle(styleMoment);
rowDeviceTai.getCell(1).setCellValue(vo.getDailyDeviceList().get(i).getCheckTime());
rowDeviceTai.createCell(3).setCellValue(vo.getDailyDeviceList().get(i).getPosition());
rowDeviceTai.createCell(5).setCellValue(vo.getDailyDeviceList().get(i).getSpeeding());
rowDeviceTai.createCell(6).setCellValue(vo.getDailyDeviceList().get(i).getFatigueDriving());
rowDeviceTai.createCell(7).setCellValue(vo.getDailyDeviceList().get(i).getTwoToFiveViolation());
rowDeviceTai.createCell(4).setCellValue(vo.getDailyDeviceList().get(i).getGpsState());
rowDeviceTai.createCell(8).setCellValue(vo.getDailyDeviceList().get(i).getTakeManagementSteps());
// 表格内容字体格式
rowDeviceTai.getCell(0).setCellStyle(styleTable);
rowDeviceTai.getCell(2).setCellStyle(styleTable);
rowDeviceTai.getCell(3).setCellStyle(styleTable);
rowDeviceTai.getCell(4).setCellStyle(styleTable);
rowDeviceTai.getCell(5).setCellStyle(styleTable);
rowDeviceTai.getCell(6).setCellStyle(styleTable);
rowDeviceTai.getCell(7).setCellStyle(styleTable);
rowDeviceTai.getCell(8).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetDeviceTai.setColumnWidth(0, 6 * 256);
sheetDeviceTai.setColumnWidth(1, 15 * 256);
sheetDeviceTai.setColumnWidth(2, 15 * 256);
sheetDeviceTai.setColumnWidth(3, 15 * 256);
sheetDeviceTai.setColumnWidth(4, 25 * 256);
sheetDeviceTai.setColumnWidth(5, 25 * 256);
sheetDeviceTai.setColumnWidth(6, 25 * 256);
sheetDeviceTai.setColumnWidth(7, 25 * 256);
sheetDeviceTai.setColumnWidth(8, 25 * 256);
cellDeviceTai = row1DeviceTai.createCell(0); // 第二个单元格
cellDeviceTai.setCellValue("值班时间：" + theTime + "00时00分 至 " + theTime + "23时59分"); // 设定值
cellDeviceTai.setCellStyle(styleDrivingLogTitleDate);
HSSFSheet sheetOutline = wb.createSheet("车辆离线情况表");
sheetOutline.autoSizeColumn(1, true);
// 设置单元格内容水平垂直居中
HSSFCellStyle styleOutline = wb.createCellStyle();
styleOutline.setVerticalAlignment(VerticalAlignment.CENTER);
styleOutline.setAlignment(HorizontalAlignment.CENTER);
HSSFRow row0Outline = sheetOutline.createRow(0);
row0Outline.setHeightInPoints(40);
HSSFRow row1Outline = sheetOutline.createRow(1);
row1Outline.setHeightInPoints(25);
// 设置标题
String[] titlesOutline = { "序号", "公司名称", "车牌号", "离线时间", "末次定位地点", "离线速度", "数据分析", "整改情况", "备注" };
if (titlesOutline.length == 0) {
return "error";
}
HSSFCell cellOutline = null;
// 总行数
int rowsOutline = vo.getDailyOutlineList().size();
// 合并单元格生成大标题、注释
sheetOutline.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
sheetOutline.addMergedRegion(new CellRangeAddress(rowsOutline + 2, rowsOutline + 2, 0, 8));
// 添加标题
cellOutline = row0Outline.createCell(0);
cellOutline.setCellValue(sysOrgVo.getOrgName() + "车辆离线情况表（" + theTime + "）");
cellOutline.setCellStyle(styleDrivingLog);
// 添加注
HSSFRow rowOutlineNote = sheetOutline.createRow(rowsOutline + 2);
cellOutline = rowOutlineNote.createCell(0);
cellOutline.setCellValue("注：常规每周巡检一次离线情况，每周一9点发给运输企业及客户关系部主管");
cellOutline.setCellStyle(styleNote);
// 第二行、标题行列
for (int i = 0; i < titlesOutline.length; i++) {
cellOutline = row1Outline.createCell(i); // 第一个单元格
cellOutline.setCellValue(titlesOutline[i]); // 设定值
cellOutline.setCellStyle(styleDrivingLogTitle);
sheetOutline.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellOutline = row0Outline.createCell(i);
cellOutline.setCellStyle(styleDrivingLog);
cellOutline = rowOutlineNote.createCell(i);
cellOutline.setCellStyle(styleNote);
}
}
HSSFRow rowOutline = null;
// HSSFCell cellRow = null;
if (rowsOutline > 0) {
for (int i = 0; i < rowsOutline; i++) {
// 创建行
rowOutline = sheetOutline.createRow(i + 2);
// 行数据处理
rowOutline.createCell(0).setCellValue(vo.getDailyOutlineList().get(i).getRownum());
rowOutline.createCell(1).setCellValue(vo.getDailyOutlineList().get(i).getOrgName());
rowOutline.createCell(2).setCellValue(vo.getDailyOutlineList().get(i).getVehicleNo());
rowOutline.createCell(3).setCellStyle(styleTime);
if (vo.getDailyOutlineList().get(i).getCheckTime() != null) {
rowOutline.getCell(3).setCellValue(vo.getDailyOutlineList().get(i).getCheckTime());
}
rowOutline.createCell(4).setCellValue(vo.getDailyOutlineList().get(i).getLastLocation());
rowOutline.createCell(5).setCellStyle(styleTable);
if (vo.getDailyOutlineList().get(i).getVehicleSpeed() != null) {
rowOutline.getCell(5).setCellValue(vo.getDailyOutlineList().get(i).getVehicleSpeed());
}
rowOutline.createCell(6).setCellValue(vo.getDailyOutlineList().get(i).getAnalysisMemo());
rowOutline.createCell(7).setCellValue(vo.getDailyOutlineList().get(i).getRectificationMemo());
rowOutline.createCell(8).setCellValue(vo.getDailyOutlineList().get(i).getMemo());
// 表格内容字体格式
rowOutline.getCell(0).setCellStyle(styleTable);
rowOutline.getCell(1).setCellStyle(styleTable);
rowOutline.getCell(2).setCellStyle(styleTable);
rowOutline.getCell(4).setCellStyle(styleTable);
if (rowOutline.getCell(5) != null) {
rowOutline.getCell(5).setCellStyle(styleTable);
}
rowOutline.getCell(6).setCellStyle(styleTable);
rowOutline.getCell(7).setCellStyle(styleTable);
rowOutline.getCell(8).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetOutline.setColumnWidth(0, 6 * 256);
sheetOutline.setColumnWidth(1, 30 * 256);
sheetOutline.setColumnWidth(2, 15 * 256);
sheetOutline.setColumnWidth(3, 30 * 256);
sheetOutline.setColumnWidth(4, 25 * 256);
sheetOutline.setColumnWidth(5, 25 * 256);
sheetOutline.setColumnWidth(6, 25 * 256);
sheetOutline.setColumnWidth(7, 25 * 256);
sheetOutline.setColumnWidth(8, 25 * 256);
HSSFSheet sheetTrajectoryLose = wb.createSheet("车辆轨迹缺失情况表");
// 设置单元格内容水平垂直居中
HSSFCellStyle styleTrajectoryLose = wb.createCellStyle();
styleTrajectoryLose.setVerticalAlignment(VerticalAlignment.CENTER);
styleTrajectoryLose.setAlignment(HorizontalAlignment.CENTER);
HSSFRow row0TrajectoryLose = sheetTrajectoryLose.createRow(0);
row0TrajectoryLose.setHeightInPoints(40);
HSSFRow row1TrajectoryLose = sheetTrajectoryLose.createRow(1);
row1TrajectoryLose.setHeightInPoints(25);
// 设置标题
String[] titlesTrajectoryLose = { "序号", "车牌号", "日期", "丢失次数", "丢失里程（公里）", "总里程（公里）", "轨迹完整率", "数据分析" };
if (titlesTrajectoryLose.length == 0) {
return "error";
}
HSSFCell cellTrajectoryLose = null;
// 合并单元格生成大标题
sheetTrajectoryLose.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
cellTrajectoryLose = row0TrajectoryLose.createCell(0);
cellTrajectoryLose.setCellValue(sysOrgVo.getOrgName() + "车辆轨迹缺失情况表（" + theTime + "）");
cellTrajectoryLose.setCellStyle(styleDrivingLog);
int rowsTrajectoryLose = vo.getDailyTrajectoryLoseList().size();
// 第二行、标题行列
for (int i = 0; i < titlesTrajectoryLose.length; i++) {
cellTrajectoryLose = row1TrajectoryLose.createCell(i); // 第一个单元格
cellTrajectoryLose.setCellValue(titlesTrajectoryLose[i]); // 设定值
cellTrajectoryLose.setCellStyle(styleDrivingLogTitle);
sheetTrajectoryLose.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellTrajectoryLose = row0TrajectoryLose.createCell(i);
cellTrajectoryLose.setCellStyle(styleDrivingLog);
}
}
HSSFRow rowTrajectoryLose = null;
// HSSFCell cellRow = null;
if (rowsTrajectoryLose > 0) {
for (int i = 0; i < rowsTrajectoryLose; i++) {
// 创建行
rowTrajectoryLose = sheetTrajectoryLose.createRow(i + 2);
// 行数据处理
rowTrajectoryLose.createCell(0).setCellValue(vo.getDailyTrajectoryLoseList().get(i).getRownum());
rowTrajectoryLose.createCell(1).setCellValue(vo.getDailyTrajectoryLoseList().get(i).getVehicleNo());
rowTrajectoryLose.createCell(2).setCellStyle(style);
if (vo.getDailyTrajectoryLoseList().get(i).getCheckTime() != null) {
rowTrajectoryLose.getCell(2)
.setCellValue(vo.getDailyTrajectoryLoseList().get(i).getCheckTime());
}
if (vo.getDailyTrajectoryLoseList().get(i).getLoseTimes() != null) {
rowTrajectoryLose.createCell(3)
.setCellValue(vo.getDailyTrajectoryLoseList().get(i).getLoseTimes());
rowTrajectoryLose.getCell(3).setCellStyle(styleTable);
}
rowTrajectoryLose.createCell(4)
.setCellValue(vo.getDailyTrajectoryLoseList().get(i).getLastLocation());
rowTrajectoryLose.createCell(5)
.setCellValue(vo.getDailyTrajectoryLoseList().get(i).getTotalMileage());
rowTrajectoryLose.createCell(6)
.setCellValue(vo.getDailyTrajectoryLoseList().get(i).getTrajectoryRate());
rowTrajectoryLose.createCell(7)
.setCellValue(vo.getDailyTrajectoryLoseList().get(i).getAnalysisMemo());
// 表格内容字体格式
rowTrajectoryLose.getCell(0).setCellStyle(styleTable);
rowTrajectoryLose.getCell(1).setCellStyle(styleTable);
rowTrajectoryLose.getCell(4).setCellStyle(styleTable);
rowTrajectoryLose.getCell(5).setCellStyle(styleTable);
rowTrajectoryLose.getCell(6).setCellStyle(styleTable);
rowTrajectoryLose.getCell(7).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetTrajectoryLose.setColumnWidth(0, 6 * 256);
sheetTrajectoryLose.setColumnWidth(1, 20 * 256);
sheetTrajectoryLose.setColumnWidth(2, 30 * 256);
sheetTrajectoryLose.setColumnWidth(3, 20 * 256);
sheetTrajectoryLose.setColumnWidth(4, 20 * 256);
sheetTrajectoryLose.setColumnWidth(5, 20 * 256);
sheetTrajectoryLose.setColumnWidth(6, 20 * 256);
sheetTrajectoryLose.setColumnWidth(7, 20 * 256);
HSSFSheet sheetAccident = wb.createSheet("事故记录表");
// 设置单元格内容水平垂直居中
HSSFCellStyle styleAccident = wb.createCellStyle();
styleAccident.setVerticalAlignment(VerticalAlignment.CENTER);
styleAccident.setAlignment(HorizontalAlignment.CENTER);
HSSFRow row0Accident = sheetAccident.createRow(0);
row0Accident.setHeightInPoints(40);
HSSFRow row1Accident = sheetAccident.createRow(1);
row1Accident.setHeightInPoints(25);
// 设置标题
String[] titlesAccident = { "序号", "企业名称", "车牌号", "事故事项", "事故发生时间", "等级", "处理情况", "处理时间", "处理人", "交接人",
"跟进情况", "赔付信息" };
if (titlesAccident.length == 0) {
return "error";
}
HSSFCell cellAccident = null;
// 合并单元格生成大标题
sheetAccident.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
cellAccident = row0Accident.createCell(0);
cellAccident.setCellValue(sysOrgVo.getOrgName() + "事故记录表（" + theTime + "）");
cellAccident.setCellStyle(styleDrivingLog);
int rowsAccident = vo.getDailyAccidentList().size();
// 第二行、标题行列
for (int i = 0; i < titlesAccident.length; i++) {
cellAccident = row1Accident.createCell(i); // 第一个单元格
cellAccident.setCellValue(titlesAccident[i]); // 设定值
cellAccident.setCellStyle(styleDrivingLogTitle);
sheetAccident.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellAccident = row0Accident.createCell(i);
cellAccident.setCellStyle(styleDrivingLog);
}
}
HSSFRow rowAccident = null;
// HSSFCell cellRow = null;
if (rowsAccident > 0) {
for (int i = 0; i < rowsAccident; i++) {
// 创建行
rowAccident = sheetAccident.createRow(i + 2);
// 行数据处理
rowAccident.createCell(0).setCellValue(vo.getDailyAccidentList().get(i).getRownum());
rowAccident.createCell(1).setCellValue(vo.getDailyAccidentList().get(i).getOrgName());
rowAccident.createCell(2).setCellValue(vo.getDailyAccidentList().get(i).getVehicleNo());
rowAccident.createCell(3).setCellValue(vo.getDailyAccidentList().get(i).getAccidentMemo());
rowAccident.createCell(4).setCellStyle(style);
rowAccident.getCell(4).setCellValue(vo.getDailyAccidentList().get(i).getAccidentTime());
rowAccident.createCell(5).setCellValue(vo.getDailyAccidentList().get(i).getAccidentLevel());
rowAccident.createCell(6).setCellValue(vo.getDailyAccidentList().get(i).getHandlerMemo());
rowAccident.createCell(7).setCellStyle(style);
rowAccident.getCell(7).setCellValue(vo.getDailyAccidentList().get(i).getHandlerTime());
rowAccident.createCell(8).setCellValue(vo.getDailyAccidentList().get(i).getAnalysisMemo());
rowAccident.createCell(9).setCellValue(vo.getDailyAccidentList().get(i).getRectificationMemo());
rowAccident.createCell(10).setCellValue(vo.getDailyAccidentList().get(i).getTrackMemo());
rowAccident.createCell(11).setCellValue(vo.getDailyAccidentList().get(i).getPaidMemo());
// 表格内容字体格式
rowAccident.getCell(0).setCellStyle(styleTable);
rowAccident.getCell(1).setCellStyle(styleTable);
rowAccident.getCell(2).setCellStyle(styleTable);
rowAccident.getCell(3).setCellStyle(styleTable);
rowAccident.getCell(5).setCellStyle(styleTable);
rowAccident.getCell(6).setCellStyle(styleTable);
rowAccident.getCell(8).setCellStyle(styleTable);
rowAccident.getCell(9).setCellStyle(styleTable);
rowAccident.getCell(10).setCellStyle(styleTable);
rowAccident.getCell(11).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetAccident.setColumnWidth(0, 6 * 256);
sheetAccident.setColumnWidth(1, 30 * 256);
sheetAccident.setColumnWidth(2, 20 * 256);
sheetAccident.setColumnWidth(3, 20 * 256);
sheetAccident.setColumnWidth(4, 30 * 256);
sheetAccident.setColumnWidth(5, 20 * 256);
sheetAccident.setColumnWidth(6, 20 * 256);
sheetAccident.setColumnWidth(7, 30 * 256);
sheetAccident.setColumnWidth(8, 20 * 256);
sheetAccident.setColumnWidth(9, 20 * 256);
sheetAccident.setColumnWidth(10, 20 * 256);
sheetAccident.setColumnWidth(11, 20 * 256);
HSSFSheet sheetCheckPost = wb.createSheet("查岗回复");
// 设置单元格内容水平垂直居中
HSSFCellStyle styleCheckPost = wb.createCellStyle();
styleCheckPost.setVerticalAlignment(VerticalAlignment.CENTER);
styleCheckPost.setAlignment(HorizontalAlignment.CENTER);
HSSFRow row0CheckPost = sheetCheckPost.createRow(0);
row0CheckPost.setHeightInPoints(40);
HSSFRow row1CheckPost = sheetCheckPost.createRow(1);
row1CheckPost.setHeightInPoints(25);
// 设置标题
String[] titlesCheckPost = { "序号", "公司名称", "查岗时间", "查岗处理人" };
if (titlesCheckPost.length == 0) {
return "error";
}
HSSFCell cellCheckPost = null;
// 总行数
int rowsCheckPost = vo.getDailyCheckPostList().size();
// 合并单元格生成大标题
sheetCheckPost.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
sheetCheckPost.addMergedRegion(new CellRangeAddress(rowsCheckPost + 2, rowsCheckPost + 2, 0, 3));
cellCheckPost = row0CheckPost.createCell(0);
cellCheckPost.setCellValue(sysOrgVo.getOrgName() + "查岗明细表");
cellCheckPost.setCellStyle(styleDrivingLog);
// 添加注
HSSFRow rowCheckPostNote = sheetCheckPost.createRow(rowsCheckPost + 2);
cellCheckPost = rowCheckPostNote.createCell(0);
cellCheckPost.setCellValue("注：此表主要是考核数控人员工作情况");
cellCheckPost.setCellStyle(styleNote);
// 第二行、标题行列
for (int i = 0; i < titlesCheckPost.length; i++) {
cellCheckPost = row1CheckPost.createCell(i); // 第一个单元格
cellCheckPost.setCellValue(titlesCheckPost[i]); // 设定值
cellCheckPost.setCellStyle(styleDrivingLogTitle);
sheetCheckPost.setColumnWidth(i, 6000);
// 大标题加表格线
if (i != 0) {
cellCheckPost = row0CheckPost.createCell(i);
cellCheckPost.setCellStyle(styleDrivingLog);
cellCheckPost = rowCheckPostNote.createCell(i);
cellCheckPost.setCellStyle(styleNote);
}
}
HSSFRow rowCheckPost = null;
// HSSFCell cellRow = null;
if (rowsCheckPost > 0) {
for (int i = 0; i < rowsCheckPost; i++) {
// 创建行
rowCheckPost = sheetCheckPost.createRow(i + 2);
// 行数据处理
rowCheckPost.createCell(0).setCellValue(vo.getDailyCheckPostList().get(i).getRownum());
rowCheckPost.createCell(1).setCellValue(vo.getDailyCheckPostList().get(i).getOrgName());
rowCheckPost.createCell(2).setCellStyle(style);
rowCheckPost.getCell(2).setCellValue(vo.getDailyCheckPostList().get(i).getCheckTime());
rowCheckPost.createCell(3).setCellValue(vo.getDailyCheckPostList().get(i).getCheckHandler());
// 表格内容字体格式
rowCheckPost.getCell(0).setCellStyle(styleTable);
rowCheckPost.getCell(1).setCellStyle(styleTable);
rowCheckPost.getCell(3).setCellStyle(styleTable);
}
}
// 设置每列的列宽，单位为字符宽度的1/256
sheetCheckPost.setColumnWidth(0, 6 * 256);
sheetCheckPost.setColumnWidth(1, 30 * 256);
sheetCheckPost.setColumnWidth(2, 30 * 256);
sheetCheckPost.setColumnWidth(3, 20 * 256);
fileOut = new FileOutputStream(fullFileName);
// 写入excel文件
wb.write(fileOut);
} catch (Exception e) {
// System.out.println(e.fillInStackTrace());
e.printStackTrace();
return "error";
} finally {
if (fileOut != null) {
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
return "error";
}
}
}
return dir + "/" + fileName;
}
java 后台导出excel 包含样式格式 这是多个sheet 连续导出
最新推荐文章于 2023-03-23 11:34:27 发布
扫一扫
3047
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
