潘钊 / bsth_control · Files

package com.bsth.service.schedule.utils;

import com.bsth.entity.Line;
import com.bsth.entity.LsStationRoute;
import com.bsth.entity.schedule.GuideboardInfo;
import com.bsth.service.LineService;
import com.bsth.service.StationRouteService;
import com.bsth.service.schedule.GuideboardInfoService;
import com.bsth.service.schedule.exception.ScheduleException;
import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonValue;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 时刻表Excel数据类。
 */
public class TimetableExcelData {
    /** 日志记录器 */
    private final static Logger LOG = LoggerFactory.getLogger(TimetableExcelData.class);

    //-------------------- 公用属性，方法（如下：）-----------------//
    /** 待导入excel workbook对象 */
    private Workbook excelWorkBook;
    /** 待导入excel workbook 工作区对象 */
    private Sheet excelWorkBookSheet;
    /** excel导入格式类型 */
    private ImportFormatType importFormatType;

    // 内部标识excel单元格类
    private static class TimetableExcelInternalCell {
        /** 是否可用（默认不可用） */
        private Boolean isEnable = false;
        /** 单元格内容 */
        private String cellContent;

        //------------- 班次类型（发车时间单元格 isFcsjCell = true）-----------//
        /** 是否是班次 */
        private Boolean isBc = false;
        /** 是否是出场班次 */
        private Boolean isOutBc = false;
        /** 是否是进场班次 */
        private Boolean isInBc = false;

        //------------- 单元格类型 -------------//
        /** 是否表头Cell */
        private Boolean isHeadCell = false;
        /** 是否路牌名字Cell */
        private Boolean isLpNameCell = false;
        /** 是否发车时间Cell */
        private Boolean isFcsjCell = false;
        /** 是否行驶时间Cell */
        private Boolean isXssjCell = false;
    }
    /** 内部Excel单元格二维数组 */
    private TimetableExcelInternalCell[][] internalExcelCells;

    // 初始化内部标识excel单元格
    private void initInternalExcelCells() {
        int rowCount = this.excelWorkBookSheet.getLastRowNum() + 1; // 基于0 base的，长度加1
        int colCount = this.excelWorkBookSheet.getRow(0).getLastCellNum(); // 不需要加1，就是长度

        this.internalExcelCells = new TimetableExcelInternalCell[rowCount][colCount];
        for (int rowNum = 0; rowNum < rowCount; rowNum ++) {
            for (int colNum = 0; colNum < colCount; colNum ++) {
                TimetableExcelInternalCell cell = new TimetableExcelInternalCell();
                String cell_con = StringUtils.trimToEmpty(PoiUtils.getStringValueFromCell(
                        this.excelWorkBookSheet.getRow(rowNum).getCell(colNum))
                        .replaceAll("\\s*", "")); // trimToEmpty

                // 如果站名中有类似->{数字}，使用正则表达式过滤掉
                cell_con = cell_con.replaceAll("(->\\d+)", "");

                cell.cellContent = cell_con;
                cell.isEnable = true;
                this.internalExcelCells[rowNum][colNum] = cell;
            }
        }

    }
    // 使用第一行单元格的属性设其他行单元格的属性
    private void setInternalExcelCellPropertyWithFirstRowCell() {
        for (int rowNum = 1; rowNum < this.internalExcelCells.length; rowNum ++) {
            for (int colNum = 0; colNum < this.internalExcelCells[rowNum].length; colNum ++) {
                TimetableExcelInternalCell cell = this.internalExcelCells[rowNum][colNum];
                TimetableExcelInternalCell firstRowCell = this.internalExcelCells[0][colNum];
                if (firstRowCell.isEnable) {
                    cell.isBc = firstRowCell.isBc;
                    cell.isInBc = firstRowCell.isInBc;
                    cell.isOutBc = firstRowCell.isOutBc;

                    cell.isLpNameCell = firstRowCell.isLpNameCell;
                    cell.isFcsjCell = firstRowCell.isFcsjCell;
                    cell.isXssjCell = firstRowCell.isXssjCell;
                }
            }
        }
    }

    //-------------------- 公用属性，方法（如上：）-----------------//

    private TimetableExcelData() {}


    //-------------------- 验证相关属性，方法（如下：）------------------//
    /** 线路信息 */
    private Line line;
    /** 线路站点路由列表（包含上下行的起终点站） */
    private List<LsStationRoute> lsStationRouteList;
    /** 路牌列表 */
    private List<GuideboardInfo> guideboardInfoList;

    private void validateRouteName(int colNum, String routeName) throws ScheduleException {
        if (StringUtils.isEmpty(routeName)) {
            throw new ScheduleException(String.format("第1行，第%d列数据不能为空", colNum + 1));
        }

        List<LsStationRoute> lsStationRoutes = new ArrayList<>();
        for (LsStationRoute lsStationRoute : this.lsStationRouteList) {
            if ("B".equals(lsStationRoute.getStationMark()) && routeName.equals(lsStationRoute.getStationName())) {
                lsStationRoutes.add(lsStationRoute);
            }
        }

        if (CollectionUtils.isEmpty(lsStationRoutes)) {
            throw new ScheduleException(String.format("第1行，第%d列数据%s在%s站点路由中不是起点站",
                    colNum + 1, routeName, this.line.getName()));
        } else if (lsStationRoutes.size() > 1) {
            throw new ScheduleException(String.format("第1行，第%d列数据%s在%s站点路由中上下行都是起点站",
                    colNum + 1, routeName, this.line.getName()));
        } else if (StringUtils.isEmpty(lsStationRoutes.get(0).getStationCode())) {
            throw new ScheduleException(String.format("第1行，第%d列数据%s在%s站点路由中没有站点编码",
                    colNum + 1, routeName, this.line.getName()));
        }

    }

    private void validateGuideboardName(int rowNum, String guideboardName) throws ScheduleException {
        if (StringUtils.isEmpty(guideboardName)) {
            throw new ScheduleException(String.format("第%d行，第1列路牌无数据", rowNum + 1));
        }

        List<GuideboardInfo> guideboardInfos = new ArrayList<>();
        for (GuideboardInfo guideboardInfo : this.guideboardInfoList) {
            if (guideboardName.equals(guideboardInfo.getLpName())) {
                guideboardInfos.add(guideboardInfo);
            }
        }

        if (CollectionUtils.isEmpty(guideboardInfos)) {
            throw new ScheduleException(String.format("第%d行，第1列的路牌在%s中不存在",
                    rowNum + 1, this.line.getName()));
        } else if (guideboardInfos.size() > 1) {
            throw new ScheduleException(String.format("第%d行，第1列的路牌在%s中重复",
                    rowNum + 1, this.line.getName()));
        }
    }
    private void validateFcsjContent(int rowNum, int colNum, String fcsjContent) throws ScheduleException {
        if (!this.importFormatType.validateFcsjContent(fcsjContent)) {
            if (this.importFormatType == ImportFormatType.Dynamic) {
                throw new ScheduleException(String.format(
                        "第%d行，第%d列的发车时间格式不正确，格式应为(报|出)hh:mm或hh:mm(X|※)",
                        rowNum + 1, colNum + 1));
            } else {
                throw new ScheduleException(String.format("第%d行，第%d列的发车时间格式不正确，格式应为hh:mm或hh,mm或hhmm",
                        rowNum + 1, colNum + 1));
            }
        }
    }
    private void validateXssjContent(int rowNum, int colNum, String xssjContent) throws ScheduleException {
        if (!this.importFormatType.validateXssjContent(xssjContent)) {
            throw new ScheduleException(String.format("第%d行，第%d列的行驶时间格式不正确，格式应为正整数",
                    rowNum + 1, colNum + 1));
        }
    }

    /**
     * 验证Excel。
     */
    public void doValidate() throws Exception {
        int rowCount = this.excelWorkBookSheet.getLastRowNum() + 1; // 基于0 base的，长度加1
        int colCount = this.excelWorkBookSheet.getRow(0).getLastCellNum(); // 不需要加1，就是长度
        if (rowCount == 0 || colCount == 0) { // 工作区是否为空
            throw new ScheduleException(String.format("%s 工作区没有数据！", this.excelWorkBookSheet.getSheetName()));
        }
        if (rowCount <= 1 || colCount <= 1) {
            throw new ScheduleException("工作区至少包含2行2列的数据");
        }

        // 1、创建内部Excel单元格数组
        this.initInternalExcelCells();

        // 2、验证单元格信息
        // 2-1、验证第1行数据（表头行数据）
        for (int colNum = 0; colNum < colCount; colNum ++) {
            TimetableExcelInternalCell cell = this.internalExcelCells[0][colNum];
            cell.isHeadCell = true;

            // 2-1-1、第一行，第一列必须路牌2个字
            if (colNum == 0) {
                if (!"路牌".equals(cell.cellContent)) {
                    throw new ScheduleException("第1行，第1列数据必须是路牌2个字");
                }
                cell.isLpNameCell = true;
                continue;
            }

            // 2-1-2、如果导入格式是Dynamic，忽略指定列
            if (this.importFormatType == ImportFormatType.Dynamic) {
                if ("报到".equals(cell.cellContent) ||
                        "到场".equals(cell.cellContent) ||
                        "离场".equals(cell.cellContent) ||
                        "总公里".equals(cell.cellContent) ||
                        "营业公里".equals(cell.cellContent) ||
                        "空驶公里".equals(cell.cellContent) ||
                        "总工时".equals(cell.cellContent) ||
                        "营业工时".equals(cell.cellContent) ||
                        "营运班次".equals(cell.cellContent)) {
                    // 这些列都忽略，不参与计算
                    cell.isEnable = false;
                    continue;
                }
            }

            // 2-1-3、出场，进场班次，不验证路由
            if ("出场".equals(cell.cellContent)) {
                cell.isBc = true;
                cell.isOutBc = true;
                continue;
            }
            if ("进场".equals(cell.cellContent)) {
                cell.isBc = true;
                cell.isInBc = true;
                continue;
            }

            // 2-1-4、如果导入类型是NormalWithXssj，空的列表表示行驶时间列，不做站点路由验证
            if (this.importFormatType == ImportFormatType.NormalWithXssj) {
                if ("".equals(cell.cellContent)) { // 空的cell默认是行驶时间列头
                    cell.isXssjCell = true;
                    continue;
                }
            }

            // 2-1-5、其他情况作为站点路由验证
            cell.isBc = true;
            cell.isFcsjCell = true;
            validateRouteName(colNum, cell.cellContent); // 验证路由名字

        }

        // 2-2、根据第1行的单元格验证后的设置的属性，设置其余行单元格的属性
        this.setInternalExcelCellPropertyWithFirstRowCell();

        // 2-3、验证具体路牌单元格（第1列）
        Map<String, Integer> gbindexmap = new HashMap<>(); // 记录每个路牌在第几行
        for (int rowNum = 1; rowNum < rowCount; rowNum ++) {
            TimetableExcelInternalCell cell = this.internalExcelCells[rowNum][0];
            if (StringUtils.isEmpty(cell.cellContent)) {
                throw new ScheduleException(String.format("第%d行，第1列路牌无数据", rowNum + 1));
            } else if (gbindexmap.get(cell.cellContent) != null) {
                throw new ScheduleException(String.format("第%d行，第1列的路牌数据与第%d行，第1列数据重复",
                        rowNum + 1,
                        gbindexmap.get(cell.cellContent)));
            } else {
                validateGuideboardName(rowNum, cell.cellContent); // 验证路牌名字
                gbindexmap.put(cell.cellContent, rowNum + 1);
            }
        }

        // 2-4、验证其余单元格（第2行，第2列开始）
        for (int rowNum = 1; rowNum < rowCount; rowNum ++) {
            for (int colNum = 1; colNum < colCount; colNum ++) {
                TimetableExcelInternalCell cell = this.internalExcelCells[rowNum][colNum];
                if (!cell.isEnable) {
                    continue;
                }

                if (cell.isBc) {
                    // 对应路牌对应单元格没有发车班次
                    if (StringUtils.isNotEmpty(cell.cellContent)) {
                        validateFcsjContent(rowNum, colNum, cell.cellContent);
                    }
                } else if (cell.isXssjCell) {
                    TimetableExcelInternalCell preCell = this.internalExcelCells[rowNum][colNum - 1];
                    if (StringUtils.isEmpty(cell.cellContent)) {
                        if (StringUtils.isEmpty(preCell.cellContent)) {
                            // 当前行驶时间单元格为空，前一个单元格也为空，跳过
                            continue;
                        } else {
                            throw new ScheduleException(String.format("第%d行，第%d列的行驶时间为空（前一个单元格不为空）！", rowNum + 1, colNum + 1));
                        }
                    } else {
                        if (StringUtils.isEmpty(preCell.cellContent)) {
                            throw new ScheduleException(String.format("第%d行，第%d列的行驶时间（前一个单元格为空）！", rowNum + 1, colNum + 1));
                        } else {
                            if (!preCell.isBc) {
                                throw new ScheduleException(String.format("第%d行，第%d列的行驶时间同行前一个单元格必须是班次发车时间！", rowNum + 1, colNum + 1));
                            } else if (preCell.isInBc) {
                                throw new ScheduleException(String.format("第%d行，第%d列的行驶时间同行前一个单元格不能是进场班次！", rowNum + 1, colNum + 1));
                            } else if (preCell.isOutBc) {
                                throw new ScheduleException(String.format("第%d行，第%d列的行驶时间同行前一个单元格不能是出场班次！", rowNum + 1, colNum + 1));
                            } else if (StringUtils.isEmpty(preCell.cellContent)) {
                                throw new ScheduleException(String.format("第%d行，第%d列的行驶时间同行前一个单元格的班次发车时间不能为空！", rowNum + 1, colNum + 1));
                            } else {
                                validateXssjContent(rowNum, colNum, cell.cellContent);
                            }
                        }
                    }
                } else {
                    throw new ScheduleException(String.format("第%d行，第%d列的单元格内容既不是发车时间，也不是行驶时间！", rowNum + 1, colNum + 1));
                }
            }
        }

    }
    public static ValidateBuilder validateBuilder() {
        return new ValidateBuilder();
    }
    public TimetableExcelData(ValidateBuilder validateBuilder) {
        this.excelWorkBook = validateBuilder.excelWorkBook;
        this.excelWorkBookSheet = validateBuilder.excelWorkBookSheet;
        this.line = validateBuilder.line;
        this.lsStationRouteList = validateBuilder.lsStationRouteList;
        this.guideboardInfoList = validateBuilder.guideboardInfoList;
        this.importFormatType = validateBuilder.importFormatType;
    }
    //-------------------- 验证相关属性，方法（如上：）------------------//

    //-------------------- 导入相关属性，方法（如下：）------------------//
    /** 导入的Excel文件路径 */
    private String excelFilePath;
    /** 数据工具服务 */
    private DataToolsService dataToolsService;
    /** 配置数据导入导出用到的配置信息 */
    private DataToolsProperties dataToolsProperties;
    /** ktr转换所需参数对象 */
    private Map<String, Object> ktrParams = new HashMap<>();
    /**
     * 导入Excel。
     */
    public void doImport() throws Exception {
        // 注意：这里不做验证，业务上导入前必须验证的（调用验证用的builder）

        // 1、创建内部单元格数据
        // 1-1、创建内部Excel单元格数组
        this.initInternalExcelCells();
        // 1-2、计算第一行，并设置单元格属性
        for (int colNum = 0; colNum < this.internalExcelCells[0].length; colNum ++) {
            TimetableExcelInternalCell cell = this.internalExcelCells[0][colNum];
            cell.isHeadCell = true;
            if (colNum == 0) {
                cell.isLpNameCell = true;
                continue;
            }
            if (this.importFormatType == ImportFormatType.Dynamic) {
                if ("报到".equals(cell.cellContent) ||
                        "到场".equals(cell.cellContent) ||
                        "离场".equals(cell.cellContent) ||
                        "总公里".equals(cell.cellContent) ||
                        "营业公里".equals(cell.cellContent) ||
                        "空驶公里".equals(cell.cellContent) ||
                        "总工时".equals(cell.cellContent) ||
                        "营业工时".equals(cell.cellContent) ||
                        "营运班次".equals(cell.cellContent)) {
                    // 这些列都忽略，不参与计算
                    cell.isEnable = false;
                    continue;
                }
            }
            // 2-1-3、出场，进场班次，不验证路由
            if ("出场".equals(cell.cellContent)) {
                cell.isBc = true;
                cell.isOutBc = true;
                continue;
            }
            if ("进场".equals(cell.cellContent)) {
                cell.isBc = true;
                cell.isInBc = true;
                continue;
            }

            // 2-1-4、如果导入类型是NormalWithXssj，空的列表表示行驶时间列，不做站点路由验证
            if (this.importFormatType == ImportFormatType.NormalWithXssj) {
                if ("".equals(cell.cellContent)) { // 空的cell默认是行驶时间列头
                    cell.isXssjCell = true;
                    continue;
                }
            }
            // 2-1-5、其他情况作为站点路由验证
            cell.isBc = true;
            cell.isFcsjCell = true;

        }
        // 1-3、根据第1行的单元格验证后的设置的属性，设置其余行单元格的属性
        this.setInternalExcelCellPropertyWithFirstRowCell();

        // 2、重新构造一个新的单元格数组，去除enable=false的，合并发车时间和行驶时间
        List<List<TimetableExcelInternalCell>> newEnabledExcelCells = new ArrayList<>();
        for (int rowNum = 0; rowNum < this.internalExcelCells.length; rowNum ++) {
            List<TimetableExcelInternalCell> newEnabledExcelRowCells = new ArrayList<>();
            newEnabledExcelCells.add(newEnabledExcelRowCells);

            for (int colNum = 0; colNum < this.internalExcelCells[rowNum].length; colNum ++) {
                TimetableExcelInternalCell cell = this.internalExcelCells[rowNum][colNum];
                if (!cell.isEnable) {
                    continue;
                }
                if (cell.isXssjCell) { // 如果匹配到行驶时间单元格，需要合并到前一个单元格中
                    if (StringUtils.isNotEmpty(cell.cellContent)) {
                        // 前一个单元格就是当前已经保存的行单元格列表的最后一个
                        TimetableExcelInternalCell preNewEnabledCell =
                                newEnabledExcelRowCells.get(newEnabledExcelRowCells.size() - 1);
                        // 合并成 12:01->30 这种格式
                        preNewEnabledCell.cellContent = preNewEnabledCell.cellContent + "->" + cell.cellContent;
                    }
                } else {
                    newEnabledExcelRowCells.add(cell);
                }
            }
        }

        // 3、计算其他ktr参数
        // 3-1、计算字段名相关参数
        List<String> columnNames = new ArrayList<>();
        for (int colNum = 0; colNum < newEnabledExcelCells.get(0).size(); colNum ++) {
            TimetableExcelInternalCell cell = newEnabledExcelCells.get(0).get(colNum);
            if (colNum == 0) {
                columnNames.add(cell.cellContent);
            } else {
                columnNames.add(cell.cellContent + "->" + colNum);
            }
        }
        LOG.info("表头参数={}", StringUtils.join(columnNames.toArray(), ","));
        this.ktrParams.put("excelfieldnames", StringUtils.join(columnNames.toArray(), ",")); // 时刻表excel输入字段名，以逗号连接
        columnNames.remove(0);
        this.ktrParams.put("normalizefieldnames", StringUtils.join(columnNames.toArray(), ",")); // 数据范式化字段名，以逗号连接

        // 3-2、计算excel内容文件参数
        File fileCal = new File(this.excelFilePath + "_stringType.xls");
        WritableWorkbook writableWorkbook = jxl.Workbook.createWorkbook(fileCal);
        WritableSheet sheet1 = writableWorkbook.createSheet(this.excelWorkBookSheet.getSheetName(), 0);
        for (int rowNum = 0; rowNum < newEnabledExcelCells.size(); rowNum ++) {
            for (int colNum = 0; colNum < newEnabledExcelCells.get(rowNum).size(); colNum ++) {
                TimetableExcelInternalCell cell = newEnabledExcelCells.get(rowNum).get(colNum);
                sheet1.addCell(new Label(colNum, rowNum, cell.cellContent));
            }
        }
        writableWorkbook.write();
        writableWorkbook.close();
        this.ktrParams.put("filepath", fileCal.getAbsolutePath());

        // 3-3、计算ktr路径相关参数
        // 元数据ktr
        File mktrFile = new File(this.getClass().getResource(
                dataToolsProperties.getTtinfodetailMetadatainputktr()).toURI());
        this.ktrParams.put("transpath", mktrFile.getAbsolutePath());
        // 实际数据ktr
        if (this.importFormatType == ImportFormatType.NormalWithXssj) {
            File normal_ktrFile = new File(this.getClass().getResource(
                    this.dataToolsProperties.getTtinfodetailDatainputktr2version()).toURI());
            this.ktrParams.put("injectktrfile", normal_ktrFile.getAbsolutePath());
        }
        if (this.importFormatType == ImportFormatType.Dynamic) {
            File dynamic_ktrFile = new File(this.getClass().getResource(
                    this.dataToolsProperties.getTtinfodetailDatainputktr2version2()).toURI());
            this.ktrParams.put("injectktrfile", dynamic_ktrFile.getAbsolutePath());
        }

        // 3-4、执行ktr
        this.dataToolsService.importData(fileCal, this.ktrParams);

    }
    public static ImportBuilder importBuilder() {
        return new ImportBuilder();
    }
    public TimetableExcelData(ImportBuilder importBuilder) {
        this.excelWorkBook = importBuilder.excelWorkBook;
        this.excelWorkBookSheet = importBuilder.excelWorkBookSheet;
        this.importFormatType = importBuilder.importFormatType;

        this.excelFilePath = importBuilder.excelFilePath;
        this.dataToolsService = importBuilder.dataToolsService;
        this.dataToolsProperties = importBuilder.dataToolsProperties;
        this.ktrParams = importBuilder.ktrParams;

        LOG.info("参数组1, xls文件名={},sheet名字={}",
                this.excelFilePath, this.ktrParams.get("sheetname"));
        LOG.info("参数组2, 线路id={},线路名字={},线路版本={}",
                this.ktrParams.get("xlid"), this.ktrParams.get("xlname"), this.ktrParams.get("lineversion"));
        LOG.info("参数组3, 时刻表id={},时刻表名字={}",
                this.ktrParams.get("ttid"), this.ktrParams.get("ttinfoname"));

    }
    //-------------------- 导入相关属性，方法（如上：）------------------//

    /**
     * excel导入格式。
     */
    public enum ImportFormatType {
        NormalWithXssj("normal") { // 老系统格式（可以在发车时间后带一个行驶时间）
            @Override
            boolean validateFcsjContent(String fcsjContent) {
                Matcher m1 = p1.matcher(fcsjContent.trim());
                Matcher m2 = p2.matcher(fcsjContent.trim());
                Matcher m3 = p3.matcher(fcsjContent.trim());
                return m1.matches() || m2.matches() || m3.matches();
            }
            @Override
            boolean validateXssjContent(String xssjContent) {
                Matcher m1 = xssj_p.matcher(xssjContent.trim());
                return m1.matches();
            }
        },
        Dynamic("dynamic") { // 自动生成时刻表的导出格式
            @Override
            boolean validateFcsjContent(String fcsjContent) {
                Matcher m1 = p1_1.matcher(fcsjContent.trim());
                Matcher m2 = p2_1.matcher(fcsjContent.trim());
                return m1.matches() || m2.matches();
            }
            @Override
            boolean validateXssjContent(String xssjContent) {
                throw new IllegalStateException("Dynamic导入格式下没有行驶时间单元格！");
            }
        };

        // NormalWithXssj格式下的发车时间格式
        private static Pattern p1 = Pattern.compile("^([01]?[0-9]|2[0-3]):[0-5][0-9]$"); // hh:mm格式
        private static Pattern p2 = Pattern.compile("^([01]?[0-9]|2[0-3]),[0-5][0-9]$"); // hh,mm格式
        private static Pattern p3 = Pattern.compile("^([01]?[0-9]|2[0-3])[0-5][0-9]$"); // hhmm格式

        // Dynamic格式下的发车时间格式
        private static Pattern p1_1 = Pattern.compile("^(\u62a5|\u51fa)?([01]?[0-9]|2[0-3]):[0-5][0-9]$"); // (报|出)hh:mm格式
        private static Pattern p2_1 = Pattern.compile("^([01]?[0-9]|2[0-3]):[0-5][0-9](X|\u203b)?$"); // hh:mm(X|※)格式

        // NormalWithXssj格式下的行驶时时间格式
        private static Pattern xssj_p = Pattern.compile("^[1-9]\\d*$"); // 正整数

        private static Map<String, ImportFormatType> descMapEnum = new HashMap<>();
        static {
            descMapEnum.put("normal", NormalWithXssj);
            descMapEnum.put("dynamic", Dynamic);
        }

        private String desc;

        @JsonValue
        public String getDesc() {
            return desc;
        }
        public void setDesc(String desc) {
            this.desc = desc;
        }

        @JsonCreator
        ImportFormatType(String desc) {
            this.desc = desc;
        }

        public static ImportFormatType fromDesc(String desc) {
            Assert.hasText(desc, "excel导入格式为空！");
            Assert.notNull(descMapEnum.get(desc), "未知的excel导入格式[" + desc + "]！");
            return descMapEnum.get(desc);
        }

        /**
         * 不同的excel导入格式验证发车时间内容。
         * @param fcsjContent 发车时间
         * @return true/通过，false/格式有问题
         */
        abstract boolean validateFcsjContent(String fcsjContent);

        /**
         * 不同的excel导入格式验证行驶时间内容。
         * @param xssjContent 行驶时间
         * @return true/通过，false/格式有问题
         */
        abstract boolean validateXssjContent(String xssjContent);
    }
    // 验证数据用builder
    public static class ValidateBuilder {
        private ValidateBuilder() {}

        /** 导入的Excel文件路径 */
        private String excelFilePath;
        /** 导入的Excel sheet名字 */
        private String excelSheetName;
        /** 线路Id */
        private Integer lineId;
        /** 线路路由版本 */
        private Integer lineRouteVersion;
        /** 线路信息service */
        private LineService lineService;
        /** 站点路由信息service */
        private StationRouteService stationRouteService;
        /** 路牌信息service */
        private GuideboardInfoService guideboardInfoService;
        /** excel导入格式类型 */
        private ImportFormatType importFormatType;

        public ValidateBuilder setExcelFilePath(String excelFilePath) {
            this.excelFilePath = excelFilePath;
            return this;
        }

        public ValidateBuilder setExcelSheetName(String excelSheetName) {
            this.excelSheetName = excelSheetName;
            return this;
        }

        public ValidateBuilder setLineId(Integer lineId) {
            this.lineId = lineId;
            return this;
        }

        public ValidateBuilder setLineRouteVersion(Integer lineRouteVersion) {
            this.lineRouteVersion = lineRouteVersion;
            return this;
        }
        public ValidateBuilder setLineService(LineService lineService) {
            this.lineService = lineService;
            return this;
        }
        public ValidateBuilder setGuideboardInfoService(GuideboardInfoService guideboardInfoService) {
            this.guideboardInfoService = guideboardInfoService;
            return this;
        }
        public ValidateBuilder setStationRouteService(StationRouteService stationRouteService) {
            this.stationRouteService = stationRouteService;
            return this;
        }
        public ValidateBuilder setImportFormatType(ImportFormatType importFormatType) {
            this.importFormatType = importFormatType;
            return this;
        }

        /** 待导入excel workbook对象 */
        private Workbook excelWorkBook;
        /** 待导入excel workbook工作区对象 */
        private Sheet excelWorkBookSheet;
        /** 线路信息 */
        private Line line;
        /** 线路站点路由列表（包含上下行的起终点站） */
        private List<LsStationRoute> lsStationRouteList;
        /** 路牌列表 */
        private List<GuideboardInfo> guideboardInfoList;

        public TimetableExcelData build() throws ScheduleException {
            // 0、检测excel导入业务格式
            if (this.importFormatType == null) {
                throw new ScheduleException("导入的Excel类型为空！");
            }

            // 1、检测excel文件，创建excel workbook对象
            // 1-1、检测文件是否存在
            Assert.hasText(this.excelFilePath, "导入的Excel文件路径为空！");
            File file = new File(this.excelFilePath);
            if (!file.exists()) {
                throw new ScheduleException("导入的Excel文件[" + this.excelFilePath + "]不存在！");
            }
            // 1-2、检测文件类型
            DataToolsFile dataToolsFile = new DataToolsFile();
            dataToolsFile.setFile(file);
            if (DataToolsFileType.XLS.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLS);
            } else if (DataToolsFileType.XLSX.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLSX);
            } else {
                throw new ScheduleException("导入的Excel文件[" + this.excelFilePath + "]内部不是xls,xlsx文件！");
            }
            // 1-3、创建workbook
            this.excelWorkBook = dataToolsFile.getFileType().getWorkBook(dataToolsFile.getFile());
            // 1-4、检测sheet名字
            Assert.hasText(this.excelSheetName, "导入的Excel sheet名字为空！");
            this.excelWorkBookSheet = this.excelWorkBook.getSheet(this.excelSheetName);
            if (this.excelWorkBookSheet == null) {
                throw new ScheduleException("导入的Excel文件sheet[" + this.excelSheetName + "]不存在！");
            }

            // 2、获取线路信息，站点路由信息
            // 2-1、线路信息
            Assert.notNull(this.lineService, "线路信息service为空！");
            Assert.notNull(this.lineId, "线路Id为空！");
            this.line = this.lineService.findById(this.lineId);
            if (this.line == null) {
                throw new ScheduleException("线路[id=" + this.lineId + "]不存在！");
            }
            // 2-2、站点路由信息
            Assert.notNull(this.stationRouteService, "站点路由信息service为空！");
            Assert.notNull(this.lineRouteVersion, "线路站点路由版本为空！");
            Map<String, Object> p1 = new HashMap<>();
            p1.put("line.id_eq", this.lineId);
            p1.put("stationMark_in", "B,E"); // 起点站
            p1.put("destroy_eq", 0); // 未撤销
            p1.put("versions_eq", this.lineRouteVersion); // 带线路版本
            this.lsStationRouteList = (List<LsStationRoute>) this.stationRouteService.list_ls(p1);
            if (CollectionUtils.isEmpty(this.lsStationRouteList)) {
                throw new ScheduleException("线路[" + this.line.getName() + "]，站点路由[版本=" + this.lineRouteVersion + "]信息为空");
            }

            // 3、获取路牌信息
            Assert.notNull(this.guideboardInfoService, "路牌信息service为空！");
            p1.clear();
            p1.put("xl.id_eq", this.lineId);
            p1.put("isCancel_eq", false);
            this.guideboardInfoList = guideboardInfoService.list(p1);
            if (CollectionUtils.isEmpty(this.guideboardInfoList)) {
                throw new ScheduleException("线路[" + this.line.getName() + "]路牌信息为空！");
            }

            return new TimetableExcelData(this);
        }
    }
    // 导入数据用
    public static class ImportBuilder {
        private ImportBuilder() {}

        /** 导入的Excel文件路径 */
        private String excelFilePath;
        /** 导入的Excel sheet名字 */
        private String excelSheetName;
        /** 时刻表Id */
        private Long ttInfoId;
        /** 线路Id */
        private Integer lineId;
        /** 线路标准信息Id */
        private Integer lineInfoId;
        /** 线路名字 */
        private String lineName;
        /** 时刻表名字 */
        private String ttInfoName;
        /** 线路路由版本 */
        private Integer lineRouteVersion;
        /** excel导入格式类型 */
        private ImportFormatType importFormatType;
        /** 数据工具服务 */
        private DataToolsService dataToolsService;
        /** 配置数据导入导出用到的配置信息 */
        private DataToolsProperties dataToolsProperties;

        public ImportBuilder setExcelFilePath(String excelFilePath) {
            this.excelFilePath = excelFilePath;
            return this;
        }

        public ImportBuilder setExcelSheetName(String excelSheetName) {
            this.excelSheetName = excelSheetName;
            return this;
        }

        public ImportBuilder setTtInfoId(Long ttInfoId) {
            this.ttInfoId = ttInfoId;
            return this;
        }

        public ImportBuilder setLineId(Integer lineId) {
            this.lineId = lineId;
            return this;
        }

        public ImportBuilder setLineInfoId(Integer lineInfoId) {
            this.lineInfoId = lineInfoId;
            return this;
        }

        public ImportBuilder setLineName(String lineName) {
            this.lineName = lineName;
            return this;
        }

        public ImportBuilder setTtInfoName(String ttInfoName) {
            this.ttInfoName = ttInfoName;
            return this;
        }

        public ImportBuilder setLineRouteVersion(Integer lineRouteVersion) {
            this.lineRouteVersion = lineRouteVersion;
            return this;
        }

        public ImportBuilder setImportFormatType(ImportFormatType importFormatType) {
            this.importFormatType = importFormatType;
            return this;
        }

        public ImportBuilder setDataToolsService(DataToolsService dataToolsService) {
            this.dataToolsService = dataToolsService;
            return this;
        }

        public ImportBuilder setDataToolsProperties(DataToolsProperties dataToolsProperties) {
            this.dataToolsProperties = dataToolsProperties;
            return this;
        }

        /** 待导入excel workbook对象 */
        private Workbook excelWorkBook;
        /** 待导入excel workbook工作区对象 */
        private Sheet excelWorkBookSheet;
        /** ktr转换所需参数对象 */
        private Map<String, Object> ktrParams = new HashMap<>();

        public TimetableExcelData build() {
            // 注意：这里不做验证，业务上导入前必须验证的（调用验证用的builder）
            File file = new File(this.excelFilePath);
            DataToolsFile dataToolsFile = new DataToolsFile();
            dataToolsFile.setFile(file);
            if (DataToolsFileType.XLS.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLS);
            } else if (DataToolsFileType.XLSX.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLSX);
            }
            this.excelWorkBook = dataToolsFile.getFileType().getWorkBook(dataToolsFile.getFile());
            this.excelWorkBookSheet = this.excelWorkBook.getSheet(this.excelSheetName);

            // 设置ktr转换参数
            this.ktrParams.put("erroroutputdir", this.dataToolsProperties.getTransErrordir()); // 错误文件输出路径
            this.ktrParams.put("sheetname", this.excelSheetName); // sheet工作区的名字
            this.ktrParams.put("lineinfoid", this.lineInfoId); // 线路标准id
            this.ktrParams.put("xlname", this.lineName); // 线路名称
            this.ktrParams.put("xlid", this.lineId); // 线路id
            this.ktrParams.put("ttinfoname", this.ttInfoName); // 时刻表名称
            this.ktrParams.put("ttid", this.ttInfoId.intValue()); // 时刻表id
            this.ktrParams.put("lineversion", this.lineRouteVersion); // 站点路由版本

            return new TimetableExcelData(this);
        }
    }
}