潘钊 / bsth_control · Files

package com.bsth.service.schedule.datatools;

import com.bsth.entity.StationRoute;
import com.bsth.service.StationRouteService;
import com.bsth.service.schedule.exception.ScheduleException;
import com.bsth.service.schedule.timetable.ExcelFormatType;
import com.bsth.service.schedule.utils.*;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.joda.time.LocalTime;
import org.joda.time.format.DateTimeFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.stereotype.Service;

import java.awt.*;
import java.io.File;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.*;
import java.util.List;

/**
 * Created by xu on 17/5/16.
 */
@EnableConfigurationProperties(DataToolsProperties.class)
@Service(value = "ttInfoDetail_dataTool")
public class TTInfoDetailDataToolsImpl implements DataToolsService, TTInfoDetailForEdit, TTinfoDetailDynamicData {
    /** 日志记录器 */
    private final static Logger LOGGER = LoggerFactory.getLogger(TTInfoDetailDataToolsImpl.class);

    @Autowired
    private StationRouteService stationRouteService;

    @Autowired
    @Qualifier(value = "dataToolsServiceImpl")
    private DataToolsService dataToolsService;

    @Autowired
    private DataToolsProperties dataToolsProperties;

    @Override
    public DataToolsFile uploadFile(String filename, byte[] filedata) throws ScheduleException {
        try {
            // 对上传的excel文件做处理，将第一个sheet名字设定为工作表1
            DataToolsFile file = dataToolsService.uploadFile(filename, filedata);
//            Workbook workbook = Workbook.getWorkbook(file);
//            Sheet sheet = workbook.getSheet(0);
//
//            File fileCal = new File(file.getAbsolutePath() + "_sheetChange.xls");
//            WritableWorkbook writableWorkbook = Workbook.createWorkbook(fileCal);
//            WritableSheet writableSheet = writableWorkbook.createSheet("工作表1", 0);
//            for (int i = 0; i < sheet.getRows(); i++) {
//                Cell[] cells = sheet.getRow(i);
//                for (int j = 0; j < cells.length; j++) {
//                    writableSheet.addCell(new Label(j, i, cells[j].getContents()));
//                }
//            }
//            writableWorkbook.write();
//            writableWorkbook.close();
//
//            return fileCal;

            return file;

        } catch (Exception exp) {
            throw new ScheduleException(exp);
        }
    }

    @Override
    public void importData(File file, Map<String, Object> params) throws ScheduleException {
        try {
            LOGGER.info("//---------------- 导入时刻表明细 start... ----------------//");

            if (params.get("lineversion") == null) {
                throw new Exception("线路版本未知");
            }

            String filename = file.getAbsolutePath(); // xls xlsx 文件名
            String sheetname = String.valueOf(params.get("sheetname")); // sheet名字
            Long ttid = Long.valueOf(String.valueOf(params.get("ttid"))); // 时刻表id
            Long xlid = Long.valueOf(String.valueOf(params.get("xlid"))); // 线路id
            Integer lineid = Integer.valueOf(String.valueOf(params.get("lineinfo"))); // 线路标准id
            String xlname = String.valueOf(params.get("xlname")); // 线路名字
            String ttname = String.valueOf(params.get("ttname")); // 时刻表名字
            Integer lineversion = Integer.valueOf(params.get("lineversion").toString()); // 线路路由版本
            ExcelFormatType excelFormatType = ExcelFormatType.getEnum(String.valueOf(params.get("excelFormatType"))); // 格式

            LOGGER.info("参数1, xls文件名={},sheet名字={}", filename, sheetname);
            LOGGER.info("参数2, 线路id={},线路名字={}", xlid, xlname);
            LOGGER.info("参数3, 时刻表id={},时刻表名字={}", ttid, ttname);

            LOGGER.info("转换xls文件格式成文本格式...");
            // 1、修改已经上传的excel文件，在每个起点站后标示数字，表示第几个班次
            // 2、由于格式问题，需要把内容都转换成字符串
            List<String> colList = new ArrayList<>();

            // poi api，并读取第一行数据，组合成站点列表，逗号分隔
            org.apache.poi.ss.usermodel.Workbook poi_workbook;
            org.apache.poi.ss.usermodel.Sheet poi_sheet;
            if (DataToolsFileType.XLS.isThisType(file)) {
                poi_workbook = DataToolsFileType.XLS.getWorkBook(file);
            } else if (DataToolsFileType.XLSX.isThisType(file)) {
                poi_workbook = DataToolsFileType.XLSX.getWorkBook(file);
            } else {
                throw new Exception("不是xls xlsx文件!");
            }
            poi_sheet = poi_workbook.getSheet(sheetname);
            int rownums = poi_sheet.getLastRowNum() + 1;
            int colnums = poi_sheet.getRow(0).getLastCellNum();
            Row firstrow = poi_sheet.getRow(0);
            for (int i = 0; i < colnums; i++) {
                org.apache.poi.ss.usermodel.Cell cell = firstrow.getCell(i);
                if (i == 0) {
                    colList.add(PoiUtils.getStringValueFromCell(cell).trim());
                } else {
                    // 站点名字后加->数字形式标示成不同的字段
                    colList.add(PoiUtils.getStringValueFromCell(cell).trim().replaceAll("(->\\d+)", "") + "->" + i);
                }
            }

            // jxl api
            File fileCal = new File(filename + "_stringType.xls");
            WritableWorkbook writableWorkbook = Workbook.createWorkbook(fileCal);
            WritableSheet sheet1 = writableWorkbook.createSheet(sheetname, 0);
            for (int i = 0; i < colnums; i++) { // 第一行数据
                sheet1.addCell(new Label(i, 0, colList.get(i)));
            }
            for (int i = 1; i < rownums; i++) { // 第二行开始
                for (int j = 0; j < colnums; j++) {
                    // poi读
                    String cellContent = PoiUtils.getStringValueFromCell(
                            poi_sheet.getRow(i).getCell(j)
                    ).replaceAll("\\s*", "");
                    // jxl写
                    sheet1.addCell(new Label(j, i, cellContent));
                }
            }
            writableWorkbook.write();
            writableWorkbook.close();

            // 2、删除原有数据
            // 操作在ktr内部执行

            // 3、导入时刻表

            // 计算表头参数
            Workbook book = Workbook.getWorkbook(fileCal);
            Sheet sheet_exp = book.getSheet(sheetname);
            List<String> columnames = new ArrayList<>();
            for (int i = 0; i < sheet_exp.getColumns(); i++) { // 获取第一行，数据，作为列名
                columnames.add(sheet_exp.getCell(i, 0).getContents());
            }
            LOGGER.info("表头={}", StringUtils.join(columnames.toArray(), ","));

            // 创建ktr转换所需参数
            Map<String, Object> ktrParms = new HashMap<>();
            // 元数据ktr
            File mktrFile = new File(this.getClass().getResource(
                    dataToolsProperties.getTtinfodetailMetadatainputktr()).toURI());
            // 实际数据ktr
            File ktrFile2_version = new File(this.getClass().getResource(
                    dataToolsProperties.getTtinfodetailDatainputktr2version()).toURI());
            // 实际数据ktr2
            File ktrFile2_version2 = new File(this.getClass().getResource(
                    dataToolsProperties.getTtinfodetailDatainputktr2version2()).toURI());


            // 通用参数，转换文件路径，excel输入文件路径，错误输出文件路径
            ktrParms.put("transpath", mktrFile.getAbsolutePath());
            ktrParms.put("filepath", fileCal.getAbsolutePath());
            ktrParms.put("erroroutputdir", dataToolsProperties.getTransErrordir());

            // 附加参数
            if (excelFormatType == ExcelFormatType.Normal) {
                ktrParms.put("injectktrfile", ktrFile2_version.getAbsolutePath()); // 注入元数据的ktr文件
            } else if (excelFormatType == ExcelFormatType.Dynamic) {
                ktrParms.put("injectktrfile", ktrFile2_version2.getAbsolutePath()); // 注入元数据的ktr文件
            } else {
                throw new ScheduleException("未知ExcelFormatType");
            }

            ktrParms.put("sheetname", sheetname); // sheet工作区的名字
            ktrParms.put("lineinfoid", lineid); // 线路标准id
            ktrParms.put("xlname", xlname); // 线路名称
            ktrParms.put("xlid", xlid); // 线路id
            ktrParms.put("ttinfoname", ttname); // 时刻表名称
            ktrParms.put("ttid", ttid.intValue()); // 时刻表id
            ktrParms.put("lineversion", lineversion); // 站点路由版本
            ktrParms.put("excelfieldnames", StringUtils.join(columnames.toArray(), ",")); // 时刻表excel输入字段名，以逗号连接
            columnames.remove(0);
            ktrParms.put("normalizefieldnames", StringUtils.join(columnames.toArray(), ",")); // 数据范式化字段名，以逗号连接

            dataToolsService.importData(fileCal, ktrParms);

            LOGGER.info("//---------------- 导入时刻表明细 success... ----------------//");
        } catch (Exception exp) {
            LOGGER.info("//---------------- 导入时刻表明细 failed... ----------------//");

            StringWriter sw = new StringWriter();
            exp.printStackTrace(new PrintWriter(sw));
            LOGGER.info(sw.toString());

            throw new ScheduleException(exp.getMessage());
        }
    }

    @Override
    public DataToolsFile exportData(Map<String, Object> params) throws ScheduleException {
        try {
            LOGGER.info("//---------------- 导出时刻表明细 start... ----------------//");

            // 创建ktr转换所需参数
            Map<String, Object> ktrParms = new HashMap<>();
            File ktrFile = new File(this.getClass().getResource(
                    dataToolsProperties.getTtinfodetailMetaoutput()).toURI());
            File ktrFile2 = new File(this.getClass().getResource(
                    dataToolsProperties.getTtinfodetailOutput()).toURI());

            // 通用参数，转换文件路径，excel输出文件名
            ktrParms.put("transpath", ktrFile.getAbsolutePath());
            ktrParms.put("filename", String.format("时刻表_(id=%s)_download-", String.valueOf(params.get("ttinfoid"))));

            // 附加参数
            ktrParms.put("injectktrfile", ktrFile2.getAbsolutePath()); // 注入元数据的ktr文件
            ktrParms.put("ttinfoid", String.valueOf(params.get("ttinfoid")));

            DataToolsFile file = dataToolsService.exportData(ktrParms);

            // 将导出的数据表头重新处理一遍，祛除->数字

            // poi api，并读取第一行数据，组合成站点列表，逗号分隔
            org.apache.poi.ss.usermodel.Workbook poi_workbook;
            org.apache.poi.ss.usermodel.Sheet poi_sheet;
            if (DataToolsFileType.XLS.isThisType(file.getFile())) {
                poi_workbook = DataToolsFileType.XLS.getWorkBook(file.getFile());
            } else if (DataToolsFileType.XLSX.isThisType(file.getFile())) {
                poi_workbook = DataToolsFileType.XLSX.getWorkBook(file.getFile());
            } else {
                throw new Exception("不是xls xlsx文件!");
            }
            poi_sheet = poi_workbook.getSheet("Sheet1");
            List<String> colList = new ArrayList<>();
            int rownums = poi_sheet.getLastRowNum() + 1;
            int colnums = poi_sheet.getRow(0).getLastCellNum();
            Row firstrow = poi_sheet.getRow(0);
            for (int i = 0; i < colnums; i++) {
                org.apache.poi.ss.usermodel.Cell cell = firstrow.getCell(i);
                colList.add(PoiUtils.getStringValueFromCell(cell).trim().replaceAll("(->\\d+)", ""));
            }

            // jxl api
            File fileCal = new File(file.getFile().getAbsolutePath() + "_rowHeadReplace.xls");
            WritableWorkbook writableWorkbook = Workbook.createWorkbook(fileCal);
            WritableSheet sheet1 = writableWorkbook.createSheet("Sheet1", 0);
            for (int i = 0; i < colnums; i++) { // 第一行数据
                sheet1.addCell(new Label(i, 0, colList.get(i)));
            }
            for (int i = 1; i < rownums; i++) { // 第二行开始
                for (int j = 0; j < colnums; j++) {
                    // poi读
                    String cellContent = PoiUtils.getStringValueFromCell(
                            poi_sheet.getRow(i).getCell(j)
                    ).replaceAll("\\s*", "");
                    // jxl写
                    sheet1.addCell(new Label(j, i, cellContent));
                }
            }
            writableWorkbook.write();
            writableWorkbook.close();

            file.setFile(fileCal);

            LOGGER.info("//---------------- 导出时刻表明细 success... ----------------//");

            return file;
        } catch (Exception exp) {
            LOGGER.info("//---------------- 导出时刻表明细 failed... ----------------//");

            StringWriter sw = new StringWriter();
            exp.printStackTrace(new PrintWriter(sw));
            LOGGER.info(sw.toString());

            throw new ScheduleException(exp.getMessage());
        }
    }

    @Override
    public EditInfo getEditInfo(Integer xlid, Long ttid, Long maxfcno) throws ScheduleException {
        try {
            LOGGER.info("//---------------- 时刻表编辑用数据输出 start... ----------------//");

            // 创建ktr转换所需参数
            Map<String, Object> ktrParms = new HashMap<>();
            File ktrFile = new File(this.getClass().getResource(
                    dataToolsProperties.getTtinfodetailForeditktr()).toURI());

            // 通用参数，转换文件路径，excel输出文件名，错误输出文件路径
            ktrParms.put("transpath", ktrFile.getAbsolutePath());
            ktrParms.put("filename", "todo");

            // 附加参数
            String outputFilePath = String.format("ttinfodetail_(id=%s)_foredit-%s",
                    String.valueOf(ttid), new DateTime().toString("yyyyMMddHHmmss"));

            ktrParms.put("tempfilepath", dataToolsProperties.getTransTempdir() + File.separator + outputFilePath); // 数据输出文件路径
            ktrParms.put("xlid", String.valueOf(xlid));
            ktrParms.put("ttid", String.valueOf(ttid));

            dataToolsService.exportData(ktrParms);

            EditInfo editInfo = new EditInfo(); // 输出数据


            // 1.6、获取最大的发车数，用于输出数据的数量
            LOGGER.info("最大发车顺序号={}", maxfcno);
            if (maxfcno != null) {
                // 2、读取ktr生成的excel数据，组织编辑用数据返回
                // 2-1、读取Excel文件
                Workbook book = Workbook.getWorkbook(new File(dataToolsProperties.getTransTempdir() +
                        File.separator + outputFilePath + ".xls"));
                Sheet sheet = book.getSheet(0);

                // 2-2、处理数据
                int all_bc_ks = 0; // 总空驶班次
                int all_bc_yy = 0; // 总营运班次
                double all_lc_ks = 0; // 总空驶里程
                double all_lc_yy = 0; // 总营运里程

                String[] headarrays = new String[maxfcno.intValue() + 3];
                headarrays[0] = "路牌";
                headarrays[maxfcno.intValue() + 1] = "空驶班次/空驶里程";
                headarrays[maxfcno.intValue() + 2] = "运营班次/运营里程";

                for (int r = 1; r < sheet.getRows(); r++) {
                    List<FcInfo> fcInfos = new ArrayList<>();
                    // 每行第一列都是路牌
                    fcInfos.add(new FcInfo(null, null, sheet.getCell(0, r).getContents(), null, null, null, null, null, null)); // 用fcsj放置路牌显示

                    int bc_ks = 0; // 空驶班次
                    int bc_yy = 0; // 营运班次
                    double lc_ks = 0; // 空驶里程
                    double lc_yy = 0; // 营运里程

                    for (int c = 1; c <= maxfcno; c++) {
                        String content_str = sheet.getCell(c, r).getContents();
                        try {
                            String[] content = StringUtils.isEmpty(content_str) ? null : content_str.split(","); // 总的内容

                            String ttdid_str = content == null ? "" : content[0]; // 时刻表明细id
                            String fcsj = content == null ? "" : content[1]; // 发车时间
                            String jhlc = content == null ? "" : content[2]; // 计划里程
                            String fzdname = content == null ? "" : content[3]; // 发车站点名称
                            String bctype = content == null ? "" : content[4]; // 班次类型
                            String xldir = content == null ? "" : content[5]; // 线路上下行
                            String isfb = content == null ? "" : content[6]; // 是否分班

                            String qdzCode = content == null ? "" : content[7]; // 起点站编码
                            String zdzCode = content == null ? "" : content[8]; // 终点站编码

                            String ists = content == null ? "" : content[9]; // 是否停驶

                            String bcsj = content == null ? "" : content[10]; // 班次时间

                            FcInfo fcInfo = new FcInfo(ttdid_str, bctype, fcsj, xldir, isfb, qdzCode, zdzCode, ists, bcsj);

                            if (StringUtils.isNotEmpty(fzdname))
                                headarrays[c] = fzdname;
                            fcInfos.add(fcInfo);


                            // 计算班次里程
                            if (StringUtils.isNotEmpty(jhlc)) {
                                if ("in".equals(bctype) || "out".equals(bctype) || "ldks".equals(bctype)) {
                                    bc_ks += 1;
                                    lc_ks += Double.valueOf(jhlc);

                                    all_bc_ks += 1;
                                    all_lc_ks += Double.valueOf(jhlc);

                                } else {
                                    bc_yy += 1;
                                    lc_yy += Double.valueOf(jhlc);

                                    all_bc_yy += 1;
                                    all_lc_yy += Double.valueOf(jhlc);
                                }
                            }

                        } catch (Exception exp) {
                            exp.printStackTrace();
                            LOGGER.info("第{}行，第{}列数据有问题，数据={}，异常message={}", r, c, content_str, exp.getCause());
                            break;
                        }

                    }

                    // 添加一列 空驶班次/空驶里程，fcsj放置数据
                    fcInfos.add(new FcInfo(null, null, String.format("%d/%.3f", bc_ks, lc_ks), null, null, null, null, null, null));

                    // 添加一列 营运班次/营运里程，fcsj放置数据
                    fcInfos.add(new FcInfo(null, null, String.format("%d/%.3f", bc_yy, lc_yy), null, null, null, null, null, null));

                    editInfo.getContents().add(fcInfos);
                }
                editInfo.getHeader().addAll(Arrays.asList(headarrays));

                editInfo.setYy_desc(String.format("空驶班次=%d,营运班次=%d,空驶里程=%.3f,营运里程=%.3f,总里程=%.3f", all_bc_ks, all_bc_yy, all_lc_ks, all_lc_yy, all_lc_ks + all_lc_yy));
            }

            LOGGER.info("//---------------- 时刻表编辑用数据输出 success... ----------------//");

            return editInfo;
        } catch (Exception exp) {
            LOGGER.info("//---------------- 时刻表编辑用数据输出 failed... ----------------//");

            StringWriter sw = new StringWriter();
            exp.printStackTrace(new PrintWriter(sw));
            LOGGER.info(sw.toString());

            throw new ScheduleException(exp.getMessage());
        }
    }

    private void createDynamicTTinfoStatSheet(org.apache.poi.ss.usermodel.Workbook wb, DTInfos dtInfos, ExcelPoiOperator excelPoiOperator) {
        org.apache.poi.ss.usermodel.Sheet sheet = excelPoiOperator.createWorkBookSheet(wb, "时刻表信息_统计");

        //-------------------------------- 1、路牌班次数据 -------------------------------//
        List<LpObj> lpObjList = dtInfos.getLpObjList();

        // 构建第一行数据
        Row lpHeadRow = excelPoiOperator.createSheetRow(sheet, 0);
        if (lpObjList.size() == 0) {
            throw new RuntimeException("没有班次数据！");
        }
        int groupCount = lpObjList.get(0).getGroupCount(); // 获取总圈数
        if (groupCount == 0) {
            throw new RuntimeException("总圈数为0，有问题！");
        }

        // 构造表头
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) 0, "路牌", new Color(0x96b9d7));

        StationRoute station1 = stationRouteService.findById(lpObjList.get(0).getStationRouteId1());
        StationRoute station2 = stationRouteService.findById(lpObjList.get(1).getStationRouteId2());

        // TODO
        Map<String, Object> param = new HashMap<>();
        param.put("station.id_eq", null);
        if (station1 == null) {
            param.put("station.id_eq", lpObjList.get(0).getStationRouteId1());
            station1 = ((List<StationRoute>) stationRouteService.list(param)).get(0);
        }
        if (station2 == null) {
            param.put("station.id_eq", lpObjList.get(0).getStationRouteId2());
            station2 = ((List<StationRoute>) stationRouteService.list(param)).get(0);
        }

        for (int i = 0; i < groupCount; i++) {
            excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (i * 2 + 1), station1.getStationName(), new Color(0x96b9d7));
            excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (i * 2 + 2), station2.getStationName(), new Color(0x96b9d7));
        }
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 1), "路牌工时", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 2), "营运班次数", new Color(0x96b9d7));

        // 构建每个路牌的班次数据
        for (int i = 0; i < lpObjList.size(); i++) {
            LpObj lpObj = lpObjList.get(i);
            Row lpRow = excelPoiOperator.createSheetRow(sheet, i + 1);
            excelPoiOperator.createStringCell(wb, lpRow, (short) 0, lpObj.getLpname());
            for (int j = 0; j < groupCount; j++) {
                excelPoiOperator.createBlankCell(wb, lpRow, (short) (j * 2 + 1));
                excelPoiOperator.createBlankCell(wb, lpRow, (short) (j * 2 + 2));
            }
            for (BcObj bcObj : lpObj.getBcObjList()) {
                if (bcObj.getBcType() == BcType.NORMAL) {
                    lpRow.getCell((short) (bcObj.getGroupNo() * 2 + bcObj.getGroupBcNo() + 1)).setCellValue(bcObj.getFcsjDesc());
                }
            }
            // 路牌工时/班次数
            excelPoiOperator.createDoubleCell(wb, lpRow, (short) (groupCount * 2 + 1),
                    lpObj.getZgs() / 60);
            // 营运班次数
            excelPoiOperator.createIntegerCell(wb, lpRow, (short) (groupCount * 2 + 2),
                    lpObj.getZbc());
        }

        // 自适应单元格长宽
        sheet.autoSizeColumn(0);
        for (int i = 0; i < groupCount; i++) {
            sheet.autoSizeColumn(i * 2 + 1);
            sheet.autoSizeColumn(i * 2 + 2);
        }
        sheet.autoSizeColumn(groupCount * 2 + 1);
        sheet.autoSizeColumn(groupCount * 2 + 2);

        // 锁定行首，列首
        sheet.createFreezePane(1, 1);

        //-------------------------------- 2、统计数据 -------------------------------//
        List<StatInfo> statInfos = dtInfos.getStatInfoList();

        // 创建总的统计数据格式
        // 第一行 统计数据
        // 第二行 序号，统计项目（8个单元格合并），统计数值
        // 第三行开始数据，一共20行

        int startrow = lpObjList.size() + 3;
        for (int i = startrow; i <= startrow + 22; i++) {
            Row row = excelPoiOperator.createSheetRow(sheet, i);
            for (int j = 0; j < 10; j++) {
                excelPoiOperator.createStringCell(wb, row, (short) j, "");

            }
        }
        // 合并第一行
        sheet.addMergedRegion(new CellRangeAddress(startrow, startrow, 0, 9));
        sheet.getRow(startrow).getCell(0).setCellValue("统计数据");
        // 合并第二行
        sheet.getRow(startrow + 1).getCell(0).setCellValue("序号");
        sheet.getRow(startrow + 1).getCell(1).setCellValue("统计项目");
        sheet.getRow(startrow + 1).getCell(9).setCellValue("统计数值");
        sheet.addMergedRegion(new CellRangeAddress(startrow + 1, startrow + 1, 1, 8));
        // 处理后面具体统计行
        for (int row = startrow + 2; row <= startrow + 2 + statInfos.size(); row++) {
            sheet.addMergedRegion(new CellRangeAddress(row, row, 1, 8));
        }

        for (int i = 0; i < statInfos.size(); i++) {
            StatInfo statInfo = statInfos.get(i);

            // 1、统计序号
            excelPoiOperator.setCellDataFormat(wb, sheet.getRow(startrow + 2 + i).getCell(0), "0");
            sheet.getRow(startrow + 2 + i).getCell(0).setCellValue(i);

            // 2、统计项目
            sheet.getRow(startrow + 2 + i).getCell(1).setCellValue(statInfo.getStatItem());

            // 3、统计数值
            excelPoiOperator.setCellDataFormat(wb, sheet.getRow(startrow + 2 + i).getCell(9), "0.00");
            sheet.getRow(startrow + 2 + i).getCell(9).setCellValue(statInfo.getStatValue());
        }

    }

    private void createDynamicTTinfoSheet(org.apache.poi.ss.usermodel.Workbook wb, DTInfos dtInfos, ExcelPoiOperator excelPoiOperator) {
        org.apache.poi.ss.usermodel.Sheet sheet = excelPoiOperator.createWorkBookSheet(wb, "时刻表信息");

        //-------------------------------- 1、路牌班次数据 -------------------------------//
        List<LpObj> lpObjList = dtInfos.getLpObjList();

        // 构建第一行数据
        Row lpHeadRow = excelPoiOperator.createSheetRow(sheet, 0);
        if (lpObjList.size() == 0) {
            throw new RuntimeException("没有班次数据！");
        }
        int groupCount = lpObjList.get(0).getGroupCount(); // 获取总圈数
        if (groupCount == 0) {
            throw new RuntimeException("总圈数为0，有问题！");
        }

        // 构造表头，添加一个进场，添加一个出场

        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) 0, "路牌", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) 1, "报到", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) 2, "出场", new Color(0x96b9d7));


        StationRoute station1 = stationRouteService.findById(lpObjList.get(0).getStationRouteId1());
        StationRoute station2 = stationRouteService.findById(lpObjList.get(1).getStationRouteId2());

        // TODO
        Map<String, Object> param = new HashMap<>();
        param.put("station.id_eq", null);
        if (station1 == null) {
            param.put("station.id_eq", lpObjList.get(0).getStationRouteId1());
            station1 = ((List<StationRoute>) stationRouteService.list(param)).get(0);
        }
        if (station2 == null) {
            param.put("station.id_eq", lpObjList.get(0).getStationRouteId2());
            station2 = ((List<StationRoute>) stationRouteService.list(param)).get(0);
        }

        for (int i = 1; i <= groupCount; i++) {
            excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (i * 2 + 1), station1.getStationName(), new Color(0x96b9d7));
            excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (i * 2 + 2), station2.getStationName(), new Color(0x96b9d7));
        }

        // 最后一个进场需要变成三个班次
        // 1个分班进场班次插入到前面的班次
        // 后面添加两个班次，1个到场班次，1个离场班次
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 3), "到场", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 4), "离场", new Color(0x96b9d7));

        // 路牌统计字段
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 5), "总公里", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 6), "营业公里", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 7), "空驶公里", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 8), "总工时", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 9), "营业工时", new Color(0x96b9d7));
        excelPoiOperator.createStringCell(wb, lpHeadRow, (short) (groupCount * 2 + 10), "营运班次", new Color(0x96b9d7));

        // 构建每个路牌的班次数据
        Cell cell = null;
        for (int i = 0; i < lpObjList.size(); i++) {
            LpObj lpObj = lpObjList.get(i);
            Row lpRow = excelPoiOperator.createSheetRow(sheet, i + 1);
            excelPoiOperator.createStringCell(wb, lpRow, (short) 0, lpObj.getLpname());

            // 创建空白单元格，并设置字符串格式
            cell = excelPoiOperator.createBlankCell(wb, lpRow, (short) 1);
            excelPoiOperator.setCellDataFormat(wb, cell, "@");

            cell = excelPoiOperator.createBlankCell(wb, lpRow, (short) 2);
            excelPoiOperator.setCellDataFormat(wb, cell, "@");

            for (int j = 1; j <= groupCount; j++) {
                cell = excelPoiOperator.createBlankCell(wb, lpRow, (short) (j * 2 + 1));
                excelPoiOperator.setCellDataFormat(wb, cell, "@");
                cell = excelPoiOperator.createBlankCell(wb, lpRow, (short) (j * 2 + 2));
                excelPoiOperator.setCellDataFormat(wb, cell, "@");
            }

            cell = excelPoiOperator.createBlankCell(wb, lpRow, (short) (groupCount * 2 + 3));
            excelPoiOperator.setCellDataFormat(wb, cell, "@");
            cell = excelPoiOperator.createBlankCell(wb, lpRow, (short) (groupCount * 2 + 4));
            excelPoiOperator.setCellDataFormat(wb, cell, "@");

            for (int j = 0; j < lpObj.getBcObjList().size(); j++) {
                BcObj bcObj = lpObj.getBcObjList().get(j); // 当前班次

                switch (bcObj.getBcType()) {
                    case BD: // 报到班次
                        if (bcObj.getIsFb()) { // 分班-两个车次链
                            cell = lpRow.getCell((short) (bcObj.getGroupNo() * 2 + bcObj.getGroupBcNo() + 3));
                            cell.setCellValue("报" + bcObj.getFcsj());
                        } else { // 一个车次链
                            cell = lpRow.getCell((short) 1);
                            cell.setCellValue(bcObj.getFcsj());
                        }
                        break;
                    case OUT: // 出场班次
                        if (bcObj.getIsFb()) { // 分班-两个车次链
                            cell = lpRow.getCell((short) (bcObj.getGroupNo() * 2 + bcObj.getGroupBcNo() + 3));
                            cell.setCellValue("出" + bcObj.getFcsj());
                        } else { // 一个车次链
                            cell = lpRow.getCell((short) (2));
                            cell.setCellValue(bcObj.getFcsj());
                        }
                        break;
                    case NORMAL: // 正常班次
                        cell = lpRow.getCell((short) (bcObj.getGroupNo() * 2 + bcObj.getGroupBcNo() + 3));
                        cell.setCellValue(bcObj.getFcsj());
                        break;
                    case IN: // 进场班次
                        if (bcObj.getIsFb()) { // 分班-两个车次链
                            cell = lpRow.getCell((short) (bcObj.getGroupNo() * 2 + bcObj.getGroupBcNo() + 3));
                            // TODO：停驶用 ※
                            cell.setCellValue(bcObj.getFcsj() + "X");
                        } else { // 一个车次链
                            cell = lpRow.getCell((short) (bcObj.getGroupNo() * 2 + bcObj.getGroupBcNo() + 3));
                            if (cell != null) {
                                cell.setCellValue(bcObj.getFcsj() + "X");
                            }
                        }
                        break;
                    case LC:
                        // 这个是到场，准备离场班次
                        cell = lpRow.getCell((short) (groupCount * 2 + 3));
                        cell.setCellValue(bcObj.getFcsj());
                        // 加10分钟表示真正离场时间
                        cell = lpRow.getCell((short) (groupCount * 2 + 4));
                        LocalTime localTime = LocalTime.parse(bcObj.getFcsj(), DateTimeFormat.forPattern("HH:mm"));
                        cell.setCellValue(localTime.plusMinutes(10).toString(DateTimeFormat.forPattern("HH:mm")));
                        break;
                }
            }

            // 路牌统计字段
            excelPoiOperator.createDoubleCell(wb, lpRow, (short) (groupCount * 2 + 5), lpObj.getZlc());
            excelPoiOperator.createDoubleCell(wb, lpRow, (short) (groupCount * 2 + 6), lpObj.getYylc());
            excelPoiOperator.createDoubleCell(wb, lpRow, (short) (groupCount * 2 + 7), lpObj.getKslc());
            cell = excelPoiOperator.createStringCell(wb, lpRow, (short) (groupCount * 2 + 8),
                    String.format("%d%s%d", (int) (lpObj.getZgs() / 60), ":", (int) (lpObj.getZgs() % 60)));
            excelPoiOperator.setCellDataFormat(wb, cell, "[h]:mm");
            cell = excelPoiOperator.createStringCell(wb, lpRow, (short) (groupCount * 2 + 9),
                    String.format("%d%s%d", (int) (lpObj.getYygs() / 60), ":", (int) (lpObj.getYygs() % 60)));
            excelPoiOperator.setCellDataFormat(wb, cell, "[h]:mm");
            excelPoiOperator.createIntegerCell(wb, lpRow, (short) (groupCount * 2 + 10), lpObj.getYybc());

        }

        // 自适应单元格长宽
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        for (int i = 1; i <= groupCount; i++) {
            sheet.autoSizeColumn(i * 2 + 1);
            sheet.autoSizeColumn(i * 2 + 2);
        }
        sheet.autoSizeColumn(groupCount * 2 + 3);
        sheet.autoSizeColumn(groupCount * 2 + 4);
        sheet.autoSizeColumn(groupCount * 2 + 5);
        sheet.autoSizeColumn(groupCount * 2 + 6);
        sheet.autoSizeColumn(groupCount * 2 + 7);
        sheet.autoSizeColumn(groupCount * 2 + 8);
        sheet.autoSizeColumn(groupCount * 2 + 9);
        sheet.autoSizeColumn(groupCount * 2 + 10);
    }

    private void createParameterInfoSheet(
            org.apache.poi.ss.usermodel.Workbook wb,
            DTInfos dtInfos, ExcelPoiOperator excelPoiOperator) {
        org.apache.poi.ss.usermodel.Sheet sheet = excelPoiOperator.createWorkBookSheet(wb, "时刻表信息_参数");

        //---------------------- 参数对象信息 -----------------------//
        List<ParameterInfo> parameterInfoList = dtInfos.getParameterInfoList();

        int _s = parameterInfoList.size() / 2;
        int _y = parameterInfoList.size() % 2;
        // 1行2个参数对象值，1个参数对象对应2列，1列描述，1列值
        int rowCount = _s + (_y == 0 ? 0 : 1);

        for (int i = 0; i < rowCount; i++) {
            Row row = excelPoiOperator.createSheetRow(sheet, i);
            for (int j = 0; j < 4; j++) {
                excelPoiOperator.createStringCell(wb, row, (short) j, "");
            }
        }

        for (int i = 0; i < parameterInfoList.size(); i++) {
            int row = i / 2;
            int col1 = (i % 2) == 0 ? 0 : 2;
            int col2 = (i % 2) == 0 ? 1 : 3;

            ParameterInfo parameterInfo = parameterInfoList.get(i);

            sheet.getRow(row).getCell(col1).setCellValue(parameterInfo.getParamItem());
            sheet.getRow(row).getCell(col2).setCellValue(parameterInfo.getParamValue());
        }

        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);

    }

    @Override
    public DataToolsFile exportDynamicTTinfo(DTInfos dtInfos, DataToolsFileType type) throws ScheduleException {
        try {
            ExcelPoiOperator excelPoiOperator;
            if (type == DataToolsFileType.XLS) {
                excelPoiOperator = new Excel2003PoiOperator();
            } else if (type == DataToolsFileType.XLSX) {
                excelPoiOperator = new Excel2007PoiOperator();
            } else {
                throw new RuntimeException("未知的导出格式==>" + type);
            }

            // 使用POI，创建xlsx文件
            org.apache.poi.ss.usermodel.Workbook wb = excelPoiOperator.createWorkBook();
            this.createDynamicTTinfoSheet(wb, dtInfos, excelPoiOperator);
            this.createDynamicTTinfoStatSheet(wb, dtInfos, excelPoiOperator);
            this.createParameterInfoSheet(wb, dtInfos, excelPoiOperator);

            // wb内存写入文件
            String filepath = dataToolsProperties.getFileoutputDir() +
                    File.separator +
                    "动态时刻表-" +
                    new DateTime().toString("yyyyMMddHHmmss") + ".xlsx";
            File file = new File(filepath);
            excelPoiOperator.writeExcel(file, wb);

            DataToolsFile dataToolsFile = new DataToolsFile();
            dataToolsFile.setFileType(DataToolsFileType.XLSX);
            dataToolsFile.setFile(file);

            return dataToolsFile;

        } catch (Exception exp) {
            LOGGER.info("//---------------- 动态时刻表输出 failed... ----------------//");

            StringWriter sw = new StringWriter();
            exp.printStackTrace(new PrintWriter(sw));
            LOGGER.info(sw.toString());

            throw new ScheduleException(exp);
        }

    }
}