Download core/excel_export.py from Surajnitp/flowmarg: direct link, hf CLI and curl.
- Browser
- Download file 19.2 kB
-
https://huggingface.co/spaces/Surajnitp/flowmarg/resolve/main/core/excel_export.py
- Command line
-
hf download hf://spaces/Surajnitp/flowmarg/core/excel_export.py
-
curl -L -o excel_export.py https://huggingface.co/spaces/Surajnitp/flowmarg/resolve/main/core/excel_export.py
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) | |