import os
import pickle
import pandas as pd
import dash
from dash import dcc, html, dash_table, callback_context
from dash.dependencies import Input, Output, State
import dash_bootstrap_components as dbc
from datetime import datetime, timedelta
import base64
import io
# -------------------------------
# 🧩 1. 配置 Config
# -------------------------------
CATEGORY_OPTIONS = [
{"label": "安装", "value": "安装"},
{"label": "勘测", "value": "勘测"},
{"label": "可选", "value": "可选"},
{"label": "网优", "value": "网优"},
{"label": "运输-正向", "value": "运输-正向"},
{"label": "运输-逆向", "value": "运输-逆向"}
]
TABLE_STYLE = {
'maxHeight': '600px',
'overflowY': 'auto',
'overflowX': 'auto',
'whiteSpace': 'nowrap'
}
CELL_TOOLTIP_STYLE = {
'textAlign': 'left',
'overflow': 'hidden',
'textOverflow': 'ellipsis',
'maxWidth': 150,
'minWidth': 100
}
CACHE_TTL = timedelta(minutes=10)
# 全局缓存
DATA_CACHE = {}
CLASSIFICATION_CACHE = {}
RESULT_CACHE = None
RESULT_CACHE_TIME = None
# -------------------------------
# 🛠️ 2. 工具函数 Utils
# -------------------------------
def get_time_str():
return datetime.now().strftime('%Y-%m-%d %H:%M:%S')
def time_print(*args):
print(f"{get_time_str()}", *args)
def save_to_pickle(df_dict, path):
with open(path, 'wb') as f:
pickle.dump(df_dict, f)
def load_from_pickle(path):
with open(path, 'rb') as f:
return pickle.load(f)
def read_excel_with_sheets(file_path):
try:
xl = pd.ExcelFile(file_path)
return {sheet: xl.parse(sheet) for sheet in xl.sheet_names}, None
except Exception as e:
return None, str(e)
def get_file_size_mb(file_path):
return round(os.path.getsize(file_path) / (1024 * 1024), 2)
def validate_columns(df, required_cols):
missing = [col for col in required_cols if col not in df.columns]
return len(missing) == 0, missing
def site_model(data_df, classification_df):
"""核心分析逻辑"""
if data_df is None or classification_df is None:
return pd.DataFrame({"错误": ["缺少数据"]})
key = "条目描述"
if key not in data_df.columns or key not in classification_df.columns:
return pd.DataFrame({"错误": [f"缺少 '{key}' 字段"]})
result = data_df.merge(
classification_df[[key, "条目分类", "项目编码"]].drop_duplicates(),
on=key, how="left"
).fillna({"条目分类": "未分类", "项目编码": "ALL"})
return result
# -------------------------------
# 🎨 3. 布局 Layout
# -------------------------------
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.LUMEN], suppress_callback_exceptions=True)
server = app.server
app.layout = html.Div([
# html.H1("站点模型分类工具", style={'textAlign': 'center', 'margin': '30px'}),
# 文件上传区
html.Div([
html.H4("选择Excel文件"),
dcc.Upload(
id='upload-data',
children=html.Div(['拖拽文件到这里，或 ', html.A('点击选择文件')]),
style={
'width': '100%', 'height': '60px', 'lineHeight': '60px',
'borderWidth': '1px', 'borderStyle': 'dashed',
'borderRadius': '5px', 'textAlign': 'center', 'margin': '10px'
},
multiple=False
),
html.Div(id='file-info', style={'margin': '10px'}),
html.Div(id='cache-status', style={'margin': '10px'}),
], style={'padding': '20px'}),
# 主 Tabs
dcc.Tabs(id="main-tabs", value='tab-import', children=[
# 导入预览
dcc.Tab(label='导入数据及预览', value='tab-import', children=[
html.H5("选择Sheet"),
dcc.Dropdown(id='sheet-dropdown'),
html.Div(id='validation-alert', style={'margin': '10px'}),
html.Div(id='data-preview-table', style=TABLE_STYLE),
]),
# 条目分类
dcc.Tab(id='tab-classify', label='条目分类', value='tab-classify', disabled=True, children=[
html.Div([
html.H4("条目分类管理"),
html.Div([
html.Button("批量修改分类", id="btn-batch-category", n_clicks=0, style={'margin': '5px'}),
html.Button("批量修改项目编码", id="btn-batch-code", n_clicks=0, style={'margin': '5px'})
], style={'margin': '10px'}),
html.Div(id='selection-count', style={'margin': '10px', 'color': '#666'}),
# Modals
dbc.Modal([
dbc.ModalHeader("批量修改 - 条目分类"),
dbc.ModalBody([html.Label("选择新分类："), dcc.Dropdown(id="modal-category-dropdown", options=CATEGORY_OPTIONS)]),
dbc.ModalFooter([
html.Button("取消", id="close-category-modal", n_clicks=0),
html.Button("确认修改", id="confirm-category-modal", n_clicks=0)
])
], id="category-modal", is_open=False),
dbc.Modal([
dbc.ModalHeader("批量修改 - 项目编码"),
dbc.ModalBody([html.Label("输入新的项目编码（留空表示 ALL）："),
dcc.Input(id="modal-code-input", type="text", placeholder="例如：PROJ001", style={'width': '100%'})]),
dbc.ModalFooter([
html.Button("取消", id="close-code-modal", n_clicks=0),
html.Button("确认修改", id="confirm-code-modal", n_clicks=0)
])
], id="code-modal", is_open=False),
# 分类表格
dash_table.DataTable(
id='classification-table',
editable=True,
# row_deletable=True,
row_selectable="multi",
sort_action="native",
filter_action="native",
page_size=15,
fixed_rows={'headers': True},
style_table=TABLE_STYLE,
style_cell=CELL_TOOLTIP_STYLE,
tooltip_duration=None
)
], style={'padding': '20px'})
]),
# 数据分析
dcc.Tab(id='tab-analyze', label='数据分析', value='tab-analyze', disabled=True, children=[
html.Div([
html.H4("分析结果"),
html.Button("刷新结果", id="btn-refresh-result", n_clicks=0, style={'margin': '10px'}),
html.Div(id='result-timestamp', style={'margin': '10px', 'color': '#555'}),
dash_table.DataTable(
id='analysis-result-table',
page_size=20,
style_table=TABLE_STYLE,
style_cell=CELL_TOOLTIP_STYLE,
tooltip_duration=None
),
html.Div([
html.Button("导出全部数据", id="btn-export", n_clicks=0, style={'margin': '20px'}),
dcc.Download(id="download-data")
])
], style={'padding': '20px'})
])
]),
# 存储组件
dcc.Store(id='stored-data-path'),
dcc.Store(id='stored-sheet-name'),
])
# -------------------------------
# 🔁 回调 Callbacks
# -------------------------------
from dash.exceptions import PreventUpdate
@app.callback(
[Output('file-info', 'children'),
Output('cache-status', 'children'),
Output('stored-data-path', 'data'),
Output('sheet-dropdown', 'options'),
Output('sheet-dropdown', 'value')],
Input('upload-data', 'contents'),
[State('upload-data', 'filename')]
)
def handle_upload(contents, filename):
if contents is None:
raise PreventUpdate
content_type, content_string = contents.split(',', 1)
decoded = base64.b64decode(content_string)
temp_path = f"./temp_{filename}"
with open(temp_path, 'wb') as f:
f.write(decoded)
file_size = get_file_size_mb(temp_path)
info_msg = f"📁 {filename} | 💾 {file_size} MB"
pickle_path = temp_path + ".pickle"
if os.path.exists(pickle_path):
sheets_dict = load_from_pickle(pickle_path)
cache_msg = "✅ 从缓存加载"
else:
sheets_dict, err = read_excel_with_sheets(temp_path)
if err:
return f"❌ 失败: {err}", "", None, [], None
save_to_pickle(sheets_dict, pickle_path)
cache_msg = "🆕 已缓存"
sheet_options = [{'label': s, 'value': s} for s in sheets_dict.keys()]
return info_msg, cache_msg, temp_path, sheet_options, sheet_options[0]['value']
@app.callback(
[Output('data-preview-table', 'children'),
Output('validation-alert', 'children'),
Output('tab-classify', 'disabled'),
Output('tab-analyze', 'disabled')],
[Input('sheet-dropdown', 'value'),
State('stored-data-path', 'data')]
)
def update_preview(sheet, file_path):
time_print("update preview...")
if not file_path or not sheet:
raise PreventUpdate
try:
df = load_from_pickle(file_path + ".pickle")[sheet]
required = ["条目描述", "项目编码", "项目名称"]
valid, missing = validate_columns(df, required)
if valid:
alert = ""
can_proceed = True
table = dash_table.DataTable(
data=df.to_dict('records'),
columns=[{"name": c, "id": c} for c in df.columns],
page_size=20,
style_table=TABLE_STYLE,
style_cell=CELL_TOOLTIP_STYLE,
tooltip_data=[
{c: {'value': str(v), 'type': 'markdown'} for c, v in row.items()}
for row in df.to_dict('records')
],
tooltip_duration=None
)
else:
alert = dbc.Alert(f"❌ 缺少字段: {', '.join(missing)}", color="danger")
table = None
can_proceed = False
return table, alert, not can_proceed, not can_proceed
except Exception as e:
alert = dbc.Alert(f"❌ 预览失败: {e}", color="danger")
return None, alert, True, True
@app.callback(
[Output('classification-table', 'data'),
Output('classification-table', 'columns'),
Output('classification-table', 'tooltip_data'),
Output('classification-table', 'dropdown')],
Input('main-tabs', 'value'),
[State('stored-data-path', 'data'),
State('sheet-dropdown', 'value')],
prevent_initial_call=True
)
def load_classification_data(tab, file_path, selected_sheet, ):
time_print("load classification data...", tab,file_path, selected_sheet)
if tab != 'tab-classify' or not file_path:
# if not file_path or not selected_sheet:
time_print("load_classification_data raise...")
raise PreventUpdate
try:
class_file = os.path.join(os.path.dirname(file_path), "classification.xlsx")
df_class = None
# 尝试加载已有分类文件
if os.path.exists(class_file):
df_class = pd.read_excel(class_file)
required = ["条目描述", "条目分类", "项目编码"]
valid, _ = validate_columns(df_class, required)
if not valid:
df_class = None
# 若无有效分类文件，则从主表生成
if df_class is None:
pickle_path = file_path + ".pickle"
sheets_dict = load_from_pickle(pickle_path)
main_df = sheets_dict[selected_sheet]
if "条目描述" in main_df.columns:
unique_desc = main_df["条目描述"].drop_duplicates().reset_index(drop=True)
df_class = pd.DataFrame({
"条目描述": unique_desc,
"条目分类": "",
"项目编码": ""
})
else:
df_class = pd.DataFrame([{
"条目描述": "字段缺失",
"条目分类": "",
"项目编码": ""
}])
# 确保 CATEGORY_OPTIONS 存在
options = CATEGORY_OPTIONS.copy() if CATEGORY_OPTIONS else []
# 构建列
columns = [
{"name": "条目描述", "id": "条目描述", "editable": False},
{"name": "条目分类", "id": "条目分类", "presentation": "dropdown", "editable": True},
{"name": "项目编码", "id": "项目编码", "editable": True, }
]
# 构建数据
data = df_class.to_dict('records')
if not data:
data = [{"条目描述": "无数据", "条目分类": "", "项目编码": ""}]
# 构建 tooltip
tooltip_data = [
{k: {'value': str(v), 'type': 'markdown'} for k, v in row.items()}
for row in data
]
# ✅ 关键：确保 dropdown 结构正确
dropdown = {
"条目分类": {
"options": options or []
}
}
global CLASSIFICATION_CACHE
CLASSIFICATION_CACHE = data.copy()
if len(data) == 0:
data = [{"条目描述": "", "条目分类": "", "项目编码": ""}]
# time_print(dropdown) # columns, tooltip_data,
# time_print(data.__len__())
# time_print(columns.__len__())
# time_print(tooltip_data.__len__())
# time_print(dropdown.__len__())
# time_print(data)
# time_print(columns)
# time_print(tooltip_data)
# time_print(dropdown)
time_print("return ...")
return data, columns, tooltip_data, dropdown
except Exception as e:
time_print(f"[Error] 加载分类数据失败: {e}")
# ✅ 返回兜底值，防止前端崩溃
fallback_cols = [
{"name": "条目描述", "id": "条目描述"},
{"name": "条目分类", "id": "条目分类", "presentation": "dropdown"},
{"name": "项目编码", "id": "项目编码"}
]
empty_data = [{"条目描述": "加载失败", "条目分类": "", "项目编码": ""}]
return (
empty_data,
fallback_cols,
[{}],
{"条目分类": {"options": CATEGORY_OPTIONS or []}}
)
@app.callback(
[Output('category-modal', 'is_open'),
Output('code-modal', 'is_open')],
[Input('btn-batch-category', 'n_clicks'),
Input('btn-batch-code', 'n_clicks'),
Input('close-category-modal', 'n_clicks'),
Input('close-code-modal', 'n_clicks'),
Input('confirm-category-modal', 'n_clicks'),
Input('confirm-code-modal', 'n_clicks')],
[State('category-modal', 'is_open'),
State('code-modal', 'is_open')],
prevent_initial_call=True
)
def toggle_modals(*args):
time_print("toggle modals...")
ctx = callback_context
if not ctx.triggered:
return False, False
btn = ctx.triggered[0]['prop_id'].split('.')[0]
if btn == 'btn-batch-category':
return True, False
elif btn == 'btn-batch-code':
return False, True
elif btn in ['close-category-modal', 'confirm-category-modal']:
return False, args[-2]
elif btn in ['close-code-modal', 'confirm-code-modal']:
return args[-3], False
return False, False
# =============================================
# ✅ 修复 Bug：支持单行 inline 编辑
# =============================================
# @app.callback(
# Output('classification-table', 'data', allow_duplicate=True),
# Input('classification-table', 'data_timestamp'),
# State('classification-table', 'data'),
# prevent_initial_call=True
# )
def capture_inline_edit(data_timestamp, current_data):
time_print("capture inline edit...")
if not current_data:
raise PreventUpdate
return current_data
@app.callback(
Output('classification-table', 'data', allow_duplicate=True),
[Input('confirm-category-modal', 'n_clicks'),
Input('confirm-code-modal', 'n_clicks')],
[State('classification-table', 'data'),
State('classification-table', 'selected_rows'),
State('modal-category-dropdown', 'value'),
State('modal-code-input', 'value')],
prevent_initial_call=True
)
def apply_batch_changes(nc1, nc2, data, selected_rows, category, code):
time_print("apply_batch_changes...")
if not data:
raise PreventUpdate
indices = selected_rows if selected_rows else range(len(data)) # 全选时应用所有行
if 'confirm-category-modal' in callback_context.triggered[0]['prop_id'] and category: # 分类修改
for i in indices:
data[i]['条目分类'] = category
# elif 'confirm-code-modal' in callback_context.triggered[0]['prop_id']:
# final_code = code or "ALL"
# for i in indices:
# data[i]['项目编码'] = final_code
return data
@app.callback(
Output('selection-count', 'children'),
Input('classification-table', 'derived_viewport_selected_row_ids')
)
def show_selection_count(selected):
return f"✅ 已选中 {len(selected or [])} 行" if selected else "未选中任何行"
@app.callback(
[Output('analysis-result-table', 'data'),
Output('analysis-result-table', 'columns'),
Output('analysis-result-table', 'tooltip_data'),
Output('result-timestamp', 'children')],
# [Input('btn-refresh-result', 'n_clicks'),
Input('main-tabs', 'value'),
[State('stored-data-path', 'data'),
State('sheet-dropdown', 'value'),
State('classification-table', 'data')],
prevent_initial_call=True
)
def run_analysis(tab, file_path, sheet, class_data):
# time_print("run analysis...")
time_print("run analysis data...", tab,file_path, sheet)
global RESULT_CACHE, RESULT_CACHE_TIME
if tab != 'tab-analyze' or not file_path:
time_print("run analysis raise...")
raise PreventUpdate
now = datetime.now()
do_refresh = callback_context.triggered[0]['prop_id'].startswith('btn-refresh')
expired = RESULT_CACHE_TIME is None or (now - RESULT_CACHE_TIME) > CACHE_TTL
if do_refresh or expired:
try:
df = load_from_pickle(file_path + ".pickle")[sheet]
class_df = pd.DataFrame(class_data)
RESULT_CACHE = site_model(df, class_df)
RESULT_CACHE_TIME = now
except Exception as e:
RESULT_CACHE = pd.DataFrame({"错误": [str(e)]})
data = RESULT_CACHE.to_dict('records')
cols = [{"name": c, "id": c} for c in RESULT_CACHE.columns]
tooltips = [{k: {'value': str(v), 'type': 'markdown'} for k, v in row.items()} for row in data]
ts = RESULT_CACHE_TIME.strftime("%Y-%m-%d %H:%M:%S") if RESULT_CACHE_TIME else "无"
return data, cols, tooltips, f"⏱️ 上次刷新: {ts}"
@app.callback(
Output("download-data", "data"),
Input("btn-export", "n_clicks"),
[State('stored-data-path', 'data'),
State('sheet-dropdown', 'value'),
State('classification-table', 'data'),
State('analysis-result-table', 'data'),
State('analysis-result-table', 'columns')],
prevent_initial_call=True
)
def export_data(n, file_path, sheet, class_data, result_data, result_cols):
if not file_path:
return None
buffer = io.BytesIO()
with pd.ExcelWriter(buffer, engine='openpyxl') as writer:
original = load_from_pickle(file_path + ".pickle")[sheet]
original.to_excel(writer, index=False, sheet_name="原始数据")
pd.DataFrame(class_data).to_excel(writer, index=False, sheet_name="条目分类")
pd.DataFrame(result_data).to_excel(writer, index=False, sheet_name="分析结果")
buffer.seek(0)
filename = f"站点模型分类结果_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
return dcc.send_bytes(buffer.getvalue(), filename)
# @app.callback(
# Input('tab-classify', 'nclicks'),
# )
# def test_tag_click(nclick):
# time_print(f"click {nclick} times....")
if __name__ == '__main__':
app.run(debug=True, port=8050)
为什么不能跳转到tab-calssify，对比analysy分析
探讨了在HTML中使用a标签时，错误地添加data-toggle=modal属性导致跳转页面出现遮罩层的问题。解析了这一现象的原因，并提供了理解和解决该问题的思路。
扫一扫
2246
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
