File size: 19,165 Bytes
2379054
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
"""
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)