## pip install streamlit==1.47.1
## pip install pandas==1.5.1
## pip install plotly==5.24.1
## pip install chinese-calendar==1.9.0
## pip install openpyxl==3.1.2
## 并把 file_path 改成你的 Excel 绝对路径
import streamlit as st
import pandas as pd
import plotly.graph_objects as go
from datetime import datetime, timedelta
import chinese_calendar
from openpyxl import load_workbook
st.set_page_config(page_title="GE90 Gate1 TAT Tracker", layout="wide")
st.title("GE90 Gate1 TAT Tracker Dashboard")
file_path = r"C:\Users\28976\PycharmProjects\GE Gate1 Dashboard\G1 Status Review Report-to ZhiBo.xlsm"
sheet_names = ["WIP G1", "Closed G1"]
# ========== 只保留 Excel 大纲第一层级并保留原始行序 ==========
def filter_df_by_outline_and_attach_row(ws, df, header_row=1, keep_level_max=1, exclude_hidden=True):
"""
根据 openpyxl 读取到的 outlineLevel/hidden 过滤 DataFrame，仅保留 <= keep_level_max 的可见行，
并在 DataFrame 中附加 Excel 行号 'ExcelRow'，用于后续严格按 Excel 顺序展示。
假设 df 第一条数据对应 Excel 第 header_row+1 行。
"""
start_excel_row = header_row + 1
levels, visibles, excel_rows = [], [], []
for i in range(len(df)):
excel_row = start_excel_row + i
dim = ws.row_dimensions.get(excel_row)
level = getattr(dim, "outlineLevel", 0) if dim else 0
hidden = getattr(dim, "hidden", False) if dim else False
excel_rows.append(excel_row)
levels.append(level)
visibles.append(not hidden)
out = df.copy()
out["ExcelRow"] = excel_rows
out["_outline_level"] = levels
out["_visible"] = visibles
mask = (out["_outline_level"] <= keep_level_max)
if exclude_hidden:
mask &= out["_visible"]
out = out[mask].drop(columns=["_outline_level", "_visible"])
return out
@st.cache_data
def load_data():
all_data = {}
# 打开一次工作簿，便于逐 sheet 读取大纲层级/隐藏状态
try:
wb = load_workbook(file_path, data_only=True, read_only=False)
except Exception:
wb = None
# 如果表头不是第 1 行，请修改 HEADER_ROW
HEADER_ROW = 1
KEEP_LEVEL_MAX = 1
EXCLUDE_HIDDEN = True
for sheet in sheet_names:
# 1) pandas 读取
try:
df0 = pd.read_excel(file_path, sheet_name=sheet)
except Exception:
all_data[sheet] = pd.DataFrame()
continue
# 2) 保留 Projected 列（WIP 会用到）
# 不要把 Projected 重命名成 Actual；保留两者
# 若你的 WIP 表叫 "Projected G1 Close Date" 就在重命名映射中映射为 Projected_G1_Close_Date
base_cols = [
'ESN', 'Operator', 'SV Type', 'Induction Date',
'Target G1 Close Date', 'Actual G1 Close Date', 'Projected G1 Close Date',
'G1 Actual TAT', 'G1 Target TAT',
'Workstop Item', 'Workstop Module',
'Workstop Start Date', 'Workstop Item Close Date',
'Categories/Delay reasons', 'Details/Comments', 'G1 Stopped Days'
]
optional_cols = []
if 'Workstop Description' in df0.columns:
optional_cols.append('Workstop Description')
# 只取存在的列
cols_to_use = [c for c in base_cols if c in df0.columns] + optional_cols
df0 = df0[cols_to_use].copy()
# 3) 按 Excel 大纲第一层级过滤 + 附加 ExcelRow（若能打开工作簿）
if wb is not None and sheet in wb.sheetnames:
ws = wb[sheet]
try:
df0 = filter_df_by_outline_and_attach_row(
ws, df0, header_row=HEADER_ROW,
keep_level_max=KEEP_LEVEL_MAX, exclude_hidden=EXCLUDE_HIDDEN
)
except Exception:
# 失败就退化为未过滤，但仍继续执行
df0["ExcelRow"] = range(HEADER_ROW+1, HEADER_ROW+1+len(df0))
else:
# 无 wb 时至少保留一个顺序标记
df0["ExcelRow"] = range(HEADER_ROW+1, HEADER_ROW+1+len(df0))
# 4) 统一列名
rename_map = {
'SV Type': 'SV_Type',
'Induction Date': 'Induction_Date',
'Target G1 Close Date': 'Target_G1_Close_Date',
'Actual G1 Close Date': 'Actual_G1_Close_Date',
'Projected G1 Close Date': 'Projected_G1_Close_Date',
'G1 Actual TAT': 'G1_Actual_TAT',
'G1 Target TAT': 'G1_Target_TAT',
'Workstop Item': 'Workstop_Item',
'Workstop Module': 'Workstop_Module',
'Workstop Start Date': 'Workstop_Start_Date',
'Workstop Item Close Date': 'Workstop_Item_Close_Date',
'Categories/Delay reasons': 'Delay_Reason',
'Details/Comments': 'Details',
'G1 Stopped Days': 'G1_Stopped_Days',
'Workstop Description': 'Workstop_Description'
}
df = df0.rename(columns=rename_map)
# 5) 缺列统一补空，保证后续访问安全
for must in [
'ESN','Operator','SV_Type','Induction_Date',
'Target_G1_Close_Date','Actual_G1_Close_Date','Projected_G1_Close_Date',
'G1_Actual_TAT','G1_Target_TAT',
'Workstop_Item','Workstop_Module',
'Workstop_Start_Date','Workstop_Item_Close_Date',
'Delay_Reason','Details','G1_Stopped_Days','Workstop_Description',
'ExcelRow'
]:
if must not in df.columns:
df[must] = pd.NA
# 6) 日期字段容错转换
for col in ['Induction_Date','Target_G1_Close_Date','Actual_G1_Close_Date','Projected_G1_Close_Date',
'Workstop_Start_Date','Workstop_Item_Close_Date']:
try:
df[col] = pd.to_datetime(df[col], errors='coerce')
except Exception:
df[col] = pd.NaT
df['Sheet'] = sheet
try:
df['ESN'] = df['ESN'].astype(str)
except Exception:
pass
# 7) 最终按 ExcelRow 排序，确保和 Excel 第一层级显示顺序一致
df = df.sort_values('ExcelRow', kind="stable", na_position="last").reset_index(drop=True)
all_data[sheet] = df
return all_data
all_data = load_data()
all_df = pd.concat(all_data.values(), ignore_index=True)
# ===== Row 1: From / To =====
c_from, c_to = st.columns(2)
with c_from:
today = datetime.today().date()
default_from = datetime(today.year, 1, 1).date()
from_date = st.date_input("📅 From:", value=default_from)
with c_to:
to_date = st.date_input("📅 To:", value=today)
# ===== Row 2: Time Axis Unit + Select Sheet =====
c_unit, c_sheet = st.columns(2)
with c_unit:
view_mode = st.selectbox("⏱️ Time Axis Unit", ['Day', '3-Day', 'Week', 'Month', 'Quarter'])
with c_sheet:
selected_sheet = st.selectbox("📑 Select Sheet", sheet_names)
# Tick Mapping
one_day_ms = 24 * 60 * 60 * 1000
dtick_map = {
'Day': "D1",
'3-Day': 3 * one_day_ms,
'Week': 7 * one_day_ms,
'Month': "M1",
'Quarter': "M3",
}
dtick = dtick_map.get(view_mode, "D1")
# === Category Color Mapping ===
DELAY_COLOR_MAP = {
"W/S Escalation pre-approval list": "#A9D08E",
"W/S Escalation exception list": "#A9D08E",
"W/S level creep": "#FFFF00",
"W/S in level creep": "#FFFF00",
"Prod. stopped by DR/SR": "#F4B084",
"DR/SR Miss-recommitment/Overdue": "#F4B084",
"W/S management": "#C00000",
"Engine Disassemble Process Challenge": "#FFC000",
"GE Commercial Approval Delay": "#5B9BD5",
"Operator Approval Delay": "#5B9BD5",
"Safran W/S approval": "#5B9BD5",
"Module W/S Related lssues": "#FFCCFF",
"Public Holiday": "#808080",
"Manpower Limition": "#33CCCC",
"Others": "#33CCCC"
}
DEFAULT_DELAY_COLOR = "#CD5C5C"
UNCATEGORIZED_LABEL = "Uncategorized"
UNCATEGORIZED_COLOR = "#808080"
def category_and_color(raw_reason):
label = (str(raw_reason).strip() if pd.notnull(raw_reason) else "")
if not label:
return UNCATEGORIZED_LABEL, UNCATEGORIZED_COLOR
return label, DELAY_COLOR_MAP.get(label, DEFAULT_DELAY_COLOR)
# --- Session State ---
if "selected_esn" not in st.session_state:
st.session_state.selected_esn = ""
if "filter_esns" not in st.session_state:
st.session_state.filter_esns = set()
if st.button("🔙 Back To Overview"):
st.session_state.selected_esn = ""
st.session_state.filter_esns = set()
st.rerun()
# ===== Search Options =====
st.markdown("### 🔍 Search Options")
with st.form("search_form"):
input_esn = st.text_input("🔎 Enter ESN", value="").strip()
operator_choices = [
"", "CCA", "CAL", "CPA", "CSN", "UAE", "ETH", "ETD", "MSR", "CES", "ANA", "SIA",
"AFL", "SVA", "THY", "Aercap", "AIC", "CKK", "DAL", "EVA", "FDX", "JAL", "JAI",
"QTR", "THA", "Safran", "Others"
]
operator = st.selectbox("🧑✈️ Operator", operator_choices, index=0)
sv_type_choices = ["", "PR-H", "PR-M", "QT-H", "QT-M", "QT-L", "Minor"]
sv_type = st.selectbox("🔧 SV Type", sv_type_choices, index=0)
submitted = st.form_submit_button("🔍 Search")
# --- Search / Mode Logic ---
if submitted:
match_df = all_df.copy()
if input_esn:
match_df = match_df[match_df['ESN'] == input_esn]
if not match_df.empty:
st.session_state.selected_esn = input_esn
selected_sheet = match_df.iloc[0]['Sheet']
st.session_state.filter_esns = set()
st.rerun()
else:
st.warning("No Matching Record Found.")
else:
if operator:
match_df = match_df[match_df['Operator'] == operator]
if sv_type:
match_df = match_df[match_df['SV_Type'] == sv_type]
# 只取当前 sheet 的 ESN，并保持 Excel 原始顺序
sheet_order = all_data[selected_sheet]["ESN"].tolist()
filtered_esns = [e for e in sheet_order if e in set(match_df[match_df['Sheet'] == selected_sheet]['ESN'].astype(str).tolist())]
if filtered_esns:
st.session_state.selected_esn = ""
st.session_state.filter_esns = set(filtered_esns)
st.rerun()
else:
st.warning("No Matching Record Found.")
# === Filter Current Sheet Data By From/To ===
df = all_data[selected_sheet].copy()
selected_esn = st.session_state.selected_esn
filter_esns = st.session_state.filter_esns
if from_date > to_date:
st.error("Invalid Date Range: 'From' Must Be Earlier Than Or Equal To 'To'.")
st.stop()
date_col = 'Induction_Date' if selected_sheet == "WIP G1" else 'Actual_G1_Close_Date'
mask = (df[date_col].dt.date >= from_date) & (df[date_col].dt.date <= to_date)
df = df[mask].copy()
# 只保留当前 sheet，并按 ExcelRow 保序
df = df.sort_values('ExcelRow', kind="stable", na_position="last").reset_index(drop=True)
# 过滤 ESN：严格保持 Excel 原始顺序
sheet_esn_order = df['ESN'].tolist() # 包含重复；用于顺序参考
esn_order_unique = []
seen = set()
for e in sheet_esn_order:
if pd.isna(e):
continue
s = str(e)
if s not in seen:
esn_order_unique.append(s)
seen.add(s)
if selected_esn:
esn_scope = [selected_esn] if selected_esn in esn_order_unique else []
elif filter_esns:
esn_scope = [e for e in esn_order_unique if e in filter_esns]
else:
esn_scope = esn_order_unique
if not esn_scope:
st.warning("No ESN Matched After Filters.")
st.stop()
def build_parent_fields(esn, subdf, sheet_name):
start = subdf['Induction_Date'].min()
if sheet_name == "WIP G1":
# WIP：优先用 Projected 作为母条结束时间
if 'Projected_G1_Close_Date' in subdf.columns and subdf['Projected_G1_Close_Date'].notna().any():
end = subdf['Projected_G1_Close_Date'].max()
else:
end = subdf['Actual_G1_Close_Date'].max()
else:
# Closed：用 Actual 作为母条结束时间
end = subdf['Actual_G1_Close_Date'].max()
try:
op = subdf['Operator'].dropna().astype(str).iloc[0]
except Exception:
op = ""
try:
svt = subdf['SV_Type'].dropna().astype(str).iloc[0]
except Exception:
svt = ""
return start, end, op, svt
def parent_label(esn, operator, sv_type):
return f"ESN: {esn}<br>Operator: {operator}<br>SV Type: {sv_type}"
tasks = []
any_children = False
esn_mode = bool(selected_esn)
is_wip = (selected_sheet == "WIP G1")
for esn in esn_scope:
esn_df = df[df['ESN'] == esn].copy()
if esn_df.empty:
continue
p_start, p_end, p_op, p_svt = build_parent_fields(esn, esn_df, selected_sheet)
if pd.isna(p_start) or pd.isna(p_end):
continue
parent_days = (p_end - p_start).days + 1
if parent_days <= 0:
continue
tasks.append({
'Task': parent_label(esn, p_op, p_svt),
'Start': p_start,
'End': p_end,
'Type': 'Engine',
'Color': 'purple',
'Hover': f"Start: {p_start.date()}<br>End: {p_end.date()}<br>G1 Projected TAT: {parent_days}",
'Days': parent_days,
'Delay_Reason': None
})
# Closed 在 Overview 也显示子条；WIP 始终显示
is_overview = (not esn_mode) and (len(filter_esns) == 0)
show_children_closed = (selected_sheet == "Closed G1") and (esn_mode or is_overview)
children_allowed = is_wip or show_children_closed
if children_allowed:
has_child = False
# 保持与 Excel 顺序一致：子条也按 ExcelRow 排序
esn_df = esn_df.sort_values('ExcelRow', kind="stable", na_position="last")
for _, row in esn_df.iterrows():
ws_start = row.get('Workstop_Start_Date', pd.NaT)
ws_end = row.get('Workstop_Item_Close_Date', pd.NaT)
if pd.isna(ws_start) or pd.isna(ws_end):
continue
child_days = (ws_end - ws_start).days + 1
if child_days <= 0:
continue
delay_reason = row.get('Delay_Reason', None)
cat_label, cat_color = category_and_color(delay_reason)
desc_text = row.get('Workstop_Description', None)
if pd.isna(desc_text) or (str(desc_text).strip() == ""):
desc_text = row.get('Details', "")
ws_module = row.get('Workstop_Module', "")
if pd.isna(ws_module): ws_module = ""
tasks.append({
'Task': ws_module,
'Start': ws_start,
'End': ws_end,
'Type': 'Module',
'Color': cat_color,
'Hover': f"Start: {ws_start.date()}<br>End: {ws_end.date()}<br>G1 Stopped Days: {row.get('G1_Stopped_Days','')}<br>Workstop Description: {desc_text}",
'Days': child_days,
'Delay_Reason': cat_label,
'PStart': p_start,
'PDays': parent_days
})
has_child = True
any_children = any_children or has_child
# ===== Plot =====
if not tasks:
st.warning("No Data To Display.")
else:
task_df = pd.DataFrame(tasks).dropna(subset=['Start', 'End'])
if task_df.empty:
st.warning("No Data To Display.")
else:
# 这里的顺序就是 append 的顺序（已经是 Excel 顺序的 ESN + 该 ESN 下按 ExcelRow 的子条）
task_df['y_pos'] = range(len(task_df))
fig = go.Figure()
for _, row in task_df.iterrows():
start = row['Start']
end = row['End'] + timedelta(hours=4) if start == row['End'] else row['End']
fig.add_trace(go.Scatter(
x=[start, end],
y=[row['y_pos'], row['y_pos']],
mode='lines',
line=dict(color=row['Color'], width=30 if row['Type'] == 'Engine' else 20),
hovertext=row['Hover'],
hoverinfo='text',
name=row['Task'],
showlegend=False
))
# 标注
if row['Type'] == 'Engine':
for i in range(int(row['Days'])):
x_pos = row['Start'] + timedelta(days=i)
fig.add_annotation(x=x_pos, y=row['y_pos'] - 0.40, text=str(i+1),
showarrow=False, font=dict(size=11, color="black"))
else:
pstart = row.get('PStart', None)
pdays = row.get('PDays', None)
child_days = int(row['Days'])
if (pstart is not None) and (pdays is not None) and (not pd.isna(pstart)):
start_idx = (row['Start'] - pstart).days + 1
end_idx = (row['End'] - pstart).days + 1
within_parent = (1 <= start_idx <= pdays) and (1 <= end_idx <= pdays)
if within_parent:
s_label, e_label = str(start_idx), str(end_idx)
else:
s_label, e_label = "1", str(child_days)
else:
s_label, e_label = "1", str(child_days)
fig.add_annotation(x=row['Start'], y=row['y_pos'] - 0.40, text=s_label,
showarrow=False, font=dict(size=11, color="black"))
fig.add_annotation(x=row['End'], y=row['y_pos'] - 0.40, text=e_label,
showarrow=False, font=dict(size=11, color="black"))
# 仅“输入 ESN + Day/3-Day”标法定节假日
if st.session_state.selected_esn and view_mode in ['Day', '3-Day']:
for d in pd.date_range(start=start, end=end):
try:
is_hol, hol_name = chinese_calendar.get_holiday_detail(d)
if is_hol and hol_name:
fig.add_vline(x=d, line=dict(color='red', width=1, dash='dot'))
except Exception:
pass
# Legend
fig.add_trace(go.Scatter(
x=[None], y=[None], mode='markers',
marker=dict(size=12, color='purple'),
name='Engine', showlegend=True
))
module_df = task_df[task_df['Type'] == 'Module']
if not module_df.empty:
tmp = module_df[['Delay_Reason', 'Color']].copy()
tmp['Delay_Reason'] = tmp['Delay_Reason'].fillna('Uncategorized')
for _, r in tmp.drop_duplicates().iterrows():
fig.add_trace(go.Scatter(
x=[None], y=[None], mode='markers',
marker=dict(size=12, color=r['Color']),
name=str(r['Delay_Reason']), showlegend=True
))
# X 轴
xaxis_args = dict(title='Date', type='date', showgrid=True, rangeslider_visible=True)
if view_mode == 'Week':
one_week_ms = 7 * one_day_ms
xaxis_args.update(tickmode='linear', dtick=one_week_ms,
tickformatstops=[dict(dtickrange=[one_week_ms, one_week_ms], value="Week %W")])
elif view_mode == 'Quarter':
span_start = task_df['Start'].min() - pd.DateOffset(years=1)
span_end = task_df['End'].max() + pd.DateOffset(years=1)
quarter_starts = pd.date_range(span_start, span_end, freq='QS')
xaxis_args.update(tickmode='array', tickvals=quarter_starts,
ticktext=[f"{d.year}Q{((d.month-1)//3)+1}" for d in quarter_starts])
else:
xaxis_args.update(dtick=dtick)
if view_mode in ['Day', '3-Day']:
xaxis_args.update(tickformat="%b-%d")
elif view_mode == 'Month':
xaxis_args.update(tickformat="%b %Y")
# Y 轴与尺寸
y_min = task_df['y_pos'].min() - 0.9
y_max = task_df['y_pos'].max() + 0.6
fig.update_layout(
title="📊 Gantt Chart",
height=150 + 70 * len(task_df),
width=6000,
xaxis=xaxis_args,
yaxis=dict(
tickvals=task_df['y_pos'],
ticktext=task_df['Task'], # 任务标签顺序 = 我们 append 的顺序 = Excel 顺序
title="",
automargin=True,
range=[y_max, y_min], # 维持现有“上新下旧”的视觉；如需从上到下递增，把这一行换成 autorange=True
autorange=False
),
hovermode="x unified",
margin=dict(l=320, r=40, t=70, b=80)
)
st.plotly_chart(fig, use_container_width=True)
else:
st.warning("No Data To Display.")
扫一扫
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
