flowmarg / core /excel_export.py
akash
Deploy to Hugging Face (source code only, no binary files)
2379054
Raw History Blame Contribute Delete
19.2 kB
"""
Excel Export Functions for Track Design Copilot
Network Rail Standards - Professional Edition
Your Exact Logic Preserved
"""
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from pathlib import Path
from typing import Optional, List, Dict, Any
# ============================================
# STYLING HELPERS (YOUR EXACT LOGIC)
# ============================================
def fmt_cell(cell, header: str = ""):
"""
Format cell based on header.
YOUR EXACT LOGIC.
"""
val = cell.value
# If value is None, skip formatting
if val is None:
return
# Allow formatting for numeric values and also NaN numeric placeholders
if not (isinstance(val, (int, float)) or pd.isna(val)):
return
h = str(header).lower()
if "chainage" in h or h in ["ch", "station"] or " ch" in h:
cell.number_format = "0.###"
elif any(k in h for k in ["mm", "slue", "slew", "cant", "sixfoot", "above", "below", "lift", "lower", "diff", "por"]):
cell.number_format = "0"
elif any(k in h for k in ["level", "lvl", "radius", "versine", "creep", "length", "northing", "easting", "distance", "m)", "gradient", "grade"]):
cell.number_format = "0.000"
else:
cell.number_format = "0.000"
def add_professional_disclaimer(ws):
"""
Add professional disclaimer to worksheet.
YOUR EXACT LOGIC.
"""
disclaimer = "DISCLAIMER: This report is generated by a calculation aid tool. All results must be independently verified by a professionally qualified Track Design Engineer before use."
row = ws.max_row + 2
ws.cell(row, 1, disclaimer)
ws.cell(row, 1).font = Font(italic=True, size=8, color="666666")
ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=5)
def add_version_footer(ws, version: int, timestamp: str):
"""
Add version footer to worksheet.
YOUR EXACT LOGIC.
"""
row = ws.max_row + 2
ws.cell(row, 1, f"Generated from EngineeringData v{version} at {timestamp}")
ws.cell(row, 1).font = Font(italic=True, size=8, color="666666")
ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=5)
def style_df_sheet(wb, name: str, df: pd.DataFrame, theme: str = "blue", version: int = 1, timestamp: str = ""):
"""
Style a DataFrame sheet.
YOUR EXACT LOGIC.
"""
fill = PatternFill("solid", fgColor="D9EAF7" if theme == "blue" else "FCE4D6")
font = Font(bold=True, size=9)
thin = Side(style="thin", color="999999")
ws = wb.create_sheet(name)
ws.sheet_view.showGridLines = False
if df is None or df.empty:
ws.cell(1, 1, "No data")
return
# Headers
for j, h in enumerate(df.columns, 1):
c = ws.cell(1, j, h)
c.fill = fill
c.font = font
c.alignment = Alignment(wrap_text=True, horizontal="center", vertical="center")
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
# Data
for i, row in enumerate(df.itertuples(index=False), 2):
for j, v in enumerate(row, 1):
c = ws.cell(i, j, "" if pd.isna(v) else v)
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
fmt_cell(c, df.columns[j - 1])
# Freeze panes
ws.freeze_panes = "A2"
# Column widths
for c in range(1, ws.max_column + 1):
ws.column_dimensions[get_column_letter(c)].width = min(34, max(12, len(str(ws.cell(1, c).value)) + 2))
add_professional_disclaimer(ws)
add_version_footer(ws, version, timestamp)
def apply_final_excel_format(ws):
"""
Apply final formatting to worksheet.
YOUR EXACT LOGIC.
"""
for row in ws.iter_rows():
for cell in row:
cell.font = Font(name="Arial", size=10, bold=(cell.font.bold if cell.font else False))
cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
for col in range(1, ws.max_column + 1):
letter = get_column_letter(col)
max_len = 10
for cell in ws[letter]:
if cell.value is not None:
max_len = max(max_len, min(28, len(str(cell.value).replace("\n", " ")) + 2))
ws.column_dimensions[letter].width = min(22, max(11, max_len))
add_professional_disclaimer(ws)
# ============================================
# MAIN EXPORT FUNCTIONS (YOUR EXACT LOGIC)
# ============================================
def write_project_info(wb, project: Dict, mode: str):
"""
Write project info sheet.
YOUR EXACT LOGIC.
"""
ws = wb.create_sheet("Project Info", 0)
ws.sheet_view.showGridLines = False
fill = PatternFill("solid", fgColor="1F4E78")
white = Font(bold=True, color="FFFFFF", size=11)
bold = Font(bold=True)
ws.cell(1, 1, "Alignment Report Builder")
ws.merge_cells("A1:C1")
ws.cell(1, 1).fill = fill
ws.cell(1, 1).font = white
ws.cell(1, 1).alignment = Alignment(horizontal="center")
items = [
("Project Name", project.get("project_name")),
("Design Stage", project.get("stage")),
("Calculation Reference Mode", mode),
("Designed By", project.get("designed_by")),
("Checked By", project.get("checked_by")),
("Approved By", project.get("approved_by")),
("Date", project.get("date")),
("Revision", project.get("revision"))
]
for r, (k, v) in enumerate(items, 3):
ws.cell(r, 1, k).font = bold
ws.cell(r, 2, v)
ws.column_dimensions["A"].width = 30
ws.column_dimensions["B"].width = 44
add_professional_disclaimer(ws)
def write_master_sheet(
wb,
sheet_name: str,
chainage_source: List[float],
track_names: Dict[int, str],
data: Dict,
mode_label: str,
selected_cols: Optional[List[str]] = None,
actual_tracks: Optional[int] = None
):
"""
Write master sheet with track data.
YOUR EXACT LOGIC.
"""
ws = wb.create_sheet(sheet_name)
ws.sheet_view.showGridLines = False
blue = PatternFill("solid", fgColor="D9EAF7")
red = PatternFill("solid", fgColor="FCE4D6")
grey = PatternFill("solid", fgColor="D9D9D9")
green = PatternFill("solid", fgColor="E2F0D9")
font = Font(bold=True, size=8)
thin = Side(style="thin", color="999999")
all_cols = [
"Own Ch", "Creep", "Existing {n} LH\nRail Level", "Existing {n} RH\nRail Level",
"Existing Rail used\nfor Lift calculation", "Existing {n}\nLow Rail Level",
"Proposed {n}\nLow Rail Level", "Proposed Low Rail\nLift / Lower", "Proposed Slue",
"Existing\nCant", "Proposed\nCant", "Existing\nRadius", "Existing\nVersine",
"Proposed\nRadius", "Proposed\nVersine", "Existing Reported\nSixfoot", "Diff",
"Existing Calculated\nSixfoot", "Proposed\nSixfoot (mm)",
"POR Existing Track {nx}\nAbove+/-Below Track {c}",
"POR Proposed Track {nx}\nAbove+/-Below Track {c}"
]
col_map = {
"Own Ch": "Own Ch",
"Creep": "Creep",
"Ex LH Level": "Existing {n} LH\nRail Level",
"Ex RH Level": "Existing {n} RH\nRail Level",
"Ex Low Level": "Existing {n}\nLow Rail Level",
"Pr Low Level": "Proposed {n}\nLow Rail Level",
"Lift": "Proposed Low Rail\nLift / Lower",
"Slue": "Proposed Slue",
"Ex Cant": "Existing\nCant",
"Pr Cant": "Proposed\nCant",
"Ex Radius": "Existing\nRadius",
"Pr Radius": "Proposed\nRadius",
"Ex Sixfoot": "Existing Reported\nSixfoot",
"Pr Sixfoot": "Proposed\nSixfoot (mm)",
"POR Ex": "POR Existing Track {nx}\nAbove+/-Below Track {c}",
"POR Pr": "POR Proposed Track {nx}\nAbove+/-Below Track {c}",
}
if selected_cols:
cols = []
for full in all_cols:
for short, full_name in col_map.items():
if full_name == full and short in selected_cols:
cols.append(full)
break
else:
cols = all_cols
ws.cell(1, 1, "Reference\nChainage (m)" if mode_label == "single" else "Own\nChainage (m)")
ws.cell(1, 1).fill = grey
ws.cell(1, 1).font = font
ws.cell(1, 1).alignment = Alignment(wrap_text=True, horizontal="center")
col = 2
if actual_tracks is None:
actual_tracks = len(track_names)
for t in range(1, actual_tracks + 1):
name = track_names.get(t, "")
start = col
ws.cell(1, start, f"Track {t} - {name}")
ws.merge_cells(start_row=1, start_column=start, end_row=1, end_column=start + len(cols) - 1)
ws.cell(1, start).fill = blue if t % 2 else red
ws.cell(1, start).font = Font(bold=True, color="0070C0" if t % 2 else "C00000")
ws.cell(1, start).alignment = Alignment(horizontal="center")
for i, h in enumerate(cols):
c = ws.cell(2, start + i, h.format(n=name, c=t, nx=t + 1))
c.fill = grey
c.font = font
c.alignment = Alignment(wrap_text=True, horizontal="center", vertical="center")
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
col = start + len(cols)
ws.row_dimensions[2].height = 85
col_key_map = {
"Own Ch": "own_ch",
"Creep": "creep",
"Existing {n} LH\nRail Level": "ex_lh",
"Existing {n} RH\nRail Level": "ex_rh",
"Existing {n}\nLow Rail Level": "ex_low",
"Proposed {n}\nLow Rail Level": "pr_low",
"Proposed Low Rail\nLift / Lower": "lift",
"Proposed Slue": "slue",
"Existing\nCant": "ex_cant",
"Proposed\nCant": "pr_cant",
"Existing\nRadius": "ex_radius",
"Proposed\nRadius": "pr_radius",
"Existing Reported\nSixfoot": "ex_reported_sixfoot",
"Proposed\nSixfoot (mm)": "pr_sixfoot",
"POR Existing Track {nx}\nAbove+/-Below Track {c}": "por_ex",
"POR Proposed Track {nx}\nAbove+/-Below Track {c}": "por_pr",
}
for r, ch in enumerate(chainage_source, 3):
ws.cell(r, 1, ch)
fmt_cell(ws.cell(r, 1), "Chainage")
for t in range(1, actual_tracks + 1):
d = data.get(t, {})
base = 2 + (t - 1) * len(cols)
for i, h in enumerate(cols):
key = col_key_map.get(h)
if key:
v = d.get(key, {}).get(ch)
if v is not None:
c = ws.cell(r, base + i)
c.value = float(v) if isinstance(v, (int, float)) and pd.notna(v) else v
fmt_cell(c, h)
c.fill = green
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
ws.freeze_panes = "B3"
for c in range(1, ws.max_column + 1):
ws.column_dimensions[get_column_letter(c)].width = 13
add_professional_disclaimer(ws)
def write_workbook(
path: Path,
project: Dict,
mode: str,
track_names: Dict[int, str],
data: Dict,
station_df: pd.DataFrame,
H: pd.DataFrame,
V: pd.DataFrame,
E: pd.DataFrame,
align_summary: pd.DataFrame,
qa_report: pd.DataFrame,
qa_summary: Dict,
chainages: Optional[List[float]] = None,
own_chs: Optional[Dict] = None,
version: int = 1,
timestamp: str = "",
export_qa_only: bool = False,
selected_cols: Optional[List[str]] = None,
actual_tracks: Optional[int] = None
):
"""
Write complete workbook.
YOUR EXACT LOGIC.
"""
wb = Workbook()
wb.remove(wb.active)
write_project_info(wb, project, mode)
if export_qa_only:
write_qa_only_sheet(wb, qa_report, qa_summary, project, version, timestamp)
else:
if mode == "Single Datum Alignment":
write_master_sheet(wb, "Master Sheet", chainages, track_names, data, "single", selected_cols, actual_tracks)
style_df_sheet(wb, "Lift & Slue Table", station_df, "blue", version, timestamp)
else:
write_master_sheet_own(wb, "Master Sheet - Own Ch", own_chs, track_names, data, selected_cols, actual_tracks)
style_df_sheet(wb, "Lift & Slue - Own Ch", station_df, "blue", version, timestamp)
style_df_sheet(wb, "Horizontal Alignment", H, "blue", version, timestamp)
style_df_sheet(wb, "Vertical Alignment", V, "blue", version, timestamp)
style_df_sheet(wb, "Event Points", E, "blue", version, timestamp)
style_df_sheet(wb, "Detected Alignment Data", align_summary, "blue", version, timestamp)
if qa_report is not None and not qa_report.empty:
write_qa_summary_sheet(wb, qa_summary, qa_report, version, timestamp)
for ws in wb.worksheets:
add_professional_disclaimer(ws)
add_version_footer(ws, version, timestamp)
apply_final_excel_format(ws)
wb.save(path)
def write_qa_only_sheet(wb, qa_report: pd.DataFrame, qa_summary: Dict, project: Dict, version: int, timestamp: str):
"""Write QA-only sheet."""
ws = wb.create_sheet("QA Report")
ws.cell(1, 1, "QA Summary")
ws.cell(1, 1).font = Font(bold=True, size=12)
ws.cell(2, 1, "Total Checks")
ws.cell(2, 2, qa_summary.get('total_checks', 0))
ws.cell(3, 1, "Passed")
ws.cell(3, 2, qa_summary.get('passed', 0))
ws.cell(4, 1, "Failed")
ws.cell(4, 2, qa_summary.get('failed', 0))
ws.cell(5, 1, "Informational")
ws.cell(5, 2, qa_summary.get('informational', 0))
ws.cell(6, 1, "Pass Rate")
ws.cell(6, 2, f"{qa_summary.get('pass_rate', 0):.1f}%")
if qa_report is not None and not qa_report.empty:
row = 8
for j, col in enumerate(qa_report.columns, 1):
ws.cell(row, j, col)
ws.cell(row, j).font = Font(bold=True)
row += 1
for _, r in qa_report.iterrows():
for j, val in enumerate(r, 1):
ws.cell(row, j, val)
row += 1
add_professional_disclaimer(ws)
add_version_footer(ws, version, timestamp)
def write_qa_summary_sheet(wb, qa_summary: Dict, qa_report: pd.DataFrame, version: int, timestamp: str):
"""Write QA summary sheet."""
ws = wb.create_sheet("QA Summary")
ws.cell(1, 1, "QA Summary Statistics")
ws.cell(1, 1).font = Font(bold=True, size=12)
ws.cell(2, 1, "Total Checks")
ws.cell(2, 2, qa_summary.get('total_checks', 0))
ws.cell(3, 1, "Passed")
ws.cell(3, 2, qa_summary.get('passed', 0))
ws.cell(4, 1, "Failed")
ws.cell(4, 2, qa_summary.get('failed', 0))
ws.cell(5, 1, "Informational")
ws.cell(5, 2, qa_summary.get('informational', 0))
ws.cell(6, 1, "Pass Rate")
ws.cell(6, 2, f"{qa_summary.get('pass_rate', 0):.1f}%")
add_professional_disclaimer(ws)
add_version_footer(ws, version, timestamp)
if qa_report is not None and not qa_report.empty:
style_df_sheet(wb, "Alignment QA Report", qa_report, "red", version, timestamp)
def write_master_sheet_own(
wb,
sheet_name: str,
own_chs: Dict,
track_names: Dict[int, str],
data: Dict,
selected_cols: Optional[List[str]] = None,
actual_tracks: Optional[int] = None
):
"""Write master sheet for own chainage mode."""
ws = wb.create_sheet(sheet_name)
ws.sheet_view.showGridLines = False
blue = PatternFill("solid", fgColor="D9EAF7")
red = PatternFill("solid", fgColor="FCE4D6")
grey = PatternFill("solid", fgColor="D9D9D9")
green = PatternFill("solid", fgColor="E2F0D9")
font = Font(bold=True, size=8)
thin = Side(style="thin", color="999999")
all_cols = ["Own Ch", "Creep", "Ex LH Level", "Ex RH Level", "Ex Low Level", "Pr Low Level", "Lift", "Slue", "Ex Cant", "Pr Cant", "Ex Radius", "Pr Radius", "Ex Sixfoot", "Pr Sixfoot", "POR Ex", "POR Pr"]
if selected_cols:
cols = [c for c in all_cols if c in selected_cols]
else:
cols = all_cols
col_key_map = {
"Own Ch": "own_ch",
"Creep": "creep",
"Ex LH Level": "ex_lh",
"Ex RH Level": "ex_rh",
"Ex Low Level": "ex_low",
"Pr Low Level": "pr_low",
"Lift": "lift",
"Slue": "slue",
"Ex Cant": "ex_cant",
"Pr Cant": "pr_cant",
"Ex Radius": "ex_radius",
"Pr Radius": "pr_radius",
"Ex Sixfoot": "ex_reported_sixfoot",
"Pr Sixfoot": "pr_sixfoot",
"POR Ex": "por_ex",
"POR Pr": "por_pr",
}
row = 1
actual_tracks = actual_tracks or len(track_names)
for t in range(1, actual_tracks + 1):
name = track_names.get(t, f"Track {t}")
chs = own_chs.get(t, []) if isinstance(own_chs, dict) else own_chs
if not chs:
continue
ws.cell(row, 1, f"Track {t} - {name}")
ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=len(cols) + 1)
ws.cell(row, 1).fill = blue if t % 2 else red
ws.cell(row, 1).font = Font(bold=True, color="0070C0" if t % 2 else "C00000")
ws.cell(row, 1).alignment = Alignment(horizontal="center")
row += 1
ws.cell(row, 1, "Own\nChainage (m)")
ws.cell(row, 1).fill = grey
ws.cell(row, 1).font = font
ws.cell(row, 1).alignment = Alignment(wrap_text=True, horizontal="center", vertical="center")
ws.cell(row, 1).border = Border(left=thin, right=thin, top=thin, bottom=thin)
for i, h in enumerate(cols, 2):
c = ws.cell(row, i, h)
c.fill = grey
c.font = font
c.alignment = Alignment(wrap_text=True, horizontal="center", vertical="center")
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
ws.row_dimensions[row].height = 85
row += 1
d = data.get(t, {})
for ch in chs:
ws.cell(row, 1, ch)
fmt_cell(ws.cell(row, 1), "Chainage")
ws.cell(row, 1).border = Border(left=thin, right=thin, top=thin, bottom=thin)
for i, h in enumerate(cols, 2):
key = col_key_map.get(h)
if key:
v = d.get(key, {}).get(ch)
if v is not None:
c = ws.cell(row, i)
c.value = float(v) if isinstance(v, (int, float)) and pd.notna(v) else v
fmt_cell(c, h)
c.fill = green
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
else:
c = ws.cell(row, i)
c.value = "N/A"
c.border = Border(left=thin, right=thin, top=thin, bottom=thin)
row += 1
row += 2
ws.freeze_panes = "A2"
for c in range(1, ws.max_column + 1):
ws.column_dimensions[get_column_letter(c)].width = 13
add_professional_disclaimer(ws)