make a excel sheet for the below data collection Column What to enter Patient ID A number you assign (1, 2, 3...) — never use name or NHS number Age Patient's age at diagnosis Tumour Stage I / II / III / IV Date of Biopsy DD/MM/YYYY Date of MDT / First Oncology Clinic DD/MM/YYYY ER Documented? Yes / No ER Result Positive / Negative / Not recorded PR Documented? Yes / No PR Result Positive / Negative / Not recorded HER2 IHC Documented? Yes / No HER2 IHC Result 0 / 1+ / 2+ / 3+ / Not recorded HER2 FISH Done (if IHC 2+)? Yes / No / N/A Ki-67 Documented? Yes / No Ki-67 Value % or "Not recorded" Date of Full Biomarker Report DD/MM/YYYY TAT (days) Calculate: Date of Full Report minus Date of Biopsy CA 15-3 Ordered? Yes / No CA 15-3 Indication Metastatic monitoring / Early disease / Unclear / N/A CEA Ordered? Yes / No CEA Indication Metastatic monitoring / Early disease / Unclear / N/A Treatment Decision Documented at MDT? Yes / No Notes Any comments or unusual circumstances
ls -la /home/daytona/workspace/ 2>/dev/null || echo "workspace does not exist"
mkdir -p /home/daytona/workspace/breast-audit-data
~/breast-audit-data/build_sheet.py
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import (
Font, PatternFill, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles.numbers import FORMAT_DATE_DDMMYY
wb = Workbook()
# ── Sheet 1: Data Collection ────────────────────────────────────────────────
ws = wb.active
ws.title = "Data Collection"
# ── Colour palette ──────────────────────────────────────────────────────────
COL_HEADER_BG = "1F4E79" # dark navy
COL_HEADER_FG = "FFFFFF"
COL_GROUP_BG = "D6E4F0" # light blue group divider
COL_GROUP_FG = "1F4E79"
COL_ALT1 = "EBF5FB" # alternating row A
COL_ALT2 = "FFFFFF" # alternating row B
COL_NOTE_BG = "FFF9C4" # soft yellow for Notes column
THIN = Side(style="thin", color="B0C4DE")
THICK = Side(style="medium", color="1F4E79")
thin_border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
thick_border = Border(left=THICK, right=THICK, top=THICK, bottom=THICK)
# ── Column definitions ──────────────────────────────────────────────────────
# (header_text, width, validation_type, validation_formula_or_list, num_format)
COLS = [
("Patient ID", 12, "whole", "1,9999", "General"),
("Age", 8, "whole", "0,120", "General"),
("Tumour Stage", 14, "list", '"I,II,III,IV"', "General"),
("Date of Biopsy", 17, None, None, "DD/MM/YYYY"),
("Date of MDT / First Oncology Clinic", 28, None, None, "DD/MM/YYYY"),
("ER Documented?", 16, "list", '"Yes,No"', "General"),
("ER Result", 20, "list", '"Positive,Negative,Not recorded"', "General"),
("PR Documented?", 16, "list", '"Yes,No"', "General"),
("PR Result", 20, "list", '"Positive,Negative,Not recorded"', "General"),
("HER2 IHC Documented?", 20, "list", '"Yes,No"', "General"),
("HER2 IHC Result", 20, "list", '"0,1+,2+,3+,Not recorded"', "General"),
("HER2 FISH Done (if IHC 2+)?", 24, "list", '"Yes,No,N/A"', "General"),
("Ki-67 Documented?", 18, "list", '"Yes,No"', "General"),
("Ki-67 Value (% or Not recorded)", 26, None, None, "General"),
("Date of Full Biomarker Report", 26, None, None, "DD/MM/YYYY"),
("TAT (days)", 14, None, None, "General"), # formula
("CA 15-3 Ordered?", 18, "list", '"Yes,No"', "General"),
("CA 15-3 Indication", 30, "list", '"Metastatic monitoring,Early disease,Unclear,N/A"', "General"),
("CEA Ordered?", 14, "list", '"Yes,No"', "General"),
("CEA Indication", 30, "list", '"Metastatic monitoring,Early disease,Unclear,N/A"', "General"),
("Treatment Decision Documented at MDT?", 32, "list", '"Yes,No"', "General"),
("Notes", 40, None, None, "General"),
]
DATA_START_ROW = 3 # row 1 = title banner, row 2 = headers, rows 3+ = data
DATA_ROWS = 50 # pre-fill 50 patient rows
# ── Title banner ────────────────────────────────────────────────────────────
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(COLS))
title_cell = ws.cell(row=1, column=1)
title_cell.value = "Breast Cancer Biomarker Audit — Data Collection Sheet"
title_cell.font = Font(name="Arial", bold=True, size=14, color=COL_HEADER_FG)
title_cell.fill = PatternFill("solid", fgColor=COL_HEADER_BG)
title_cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.row_dimensions[1].height = 32
# ── Header row ──────────────────────────────────────────────────────────────
for col_idx, (hdr, width, *_rest) in enumerate(COLS, start=1):
cell = ws.cell(row=2, column=col_idx, value=hdr)
cell.font = Font(name="Arial", bold=True, size=10, color=COL_HEADER_FG)
cell.fill = PatternFill("solid", fgColor=COL_HEADER_BG)
cell.alignment = Alignment(horizontal="center", vertical="center",
wrap_text=True)
cell.border = thin_border
ws.column_dimensions[get_column_letter(col_idx)].width = width
ws.row_dimensions[2].height = 40
# ── Column index helpers ─────────────────────────────────────────────────────
BIOPSY_COL = 4 # D
FULL_RPT_COL = 15 # O
TAT_COL = 16 # P
NOTES_COL = 22 # V
# ── Data rows ────────────────────────────────────────────────────────────────
for row in range(DATA_START_ROW, DATA_START_ROW + DATA_ROWS):
is_even = (row % 2 == 0)
row_bg = COL_ALT1 if is_even else COL_ALT2
for col_idx, (hdr, width, val_type, val_formula, num_fmt) in enumerate(COLS, start=1):
cell = ws.cell(row=row, column=col_idx)
# TAT formula
if col_idx == TAT_COL:
biopsy_addr = f"{get_column_letter(BIOPSY_COL)}{row}"
full_rpt_addr = f"{get_column_letter(FULL_RPT_COL)}{row}"
cell.value = (
f'=IF(AND(ISNUMBER({full_rpt_addr}),ISNUMBER({biopsy_addr})),'
f'{full_rpt_addr}-{biopsy_addr},"")'
)
cell.number_format = "General"
else:
# Date columns
if num_fmt == "DD/MM/YYYY":
cell.number_format = "DD/MM/YYYY"
else:
cell.number_format = num_fmt
# Notes highlight
bg = COL_NOTE_BG if col_idx == NOTES_COL else row_bg
cell.fill = PatternFill("solid", fgColor=bg)
cell.font = Font(name="Arial", size=10)
cell.alignment = Alignment(horizontal="left", vertical="center",
wrap_text=(col_idx == NOTES_COL))
cell.border = thin_border
# ── Data Validation ──────────────────────────────────────────────────────────
for col_idx, (hdr, width, val_type, val_formula, num_fmt) in enumerate(COLS, start=1):
col_letter = get_column_letter(col_idx)
dv_range = f"{col_letter}{DATA_START_ROW}:{col_letter}{DATA_START_ROW + DATA_ROWS - 1}"
if val_type == "list":
dv = DataValidation(type="list", formula1=val_formula,
allow_blank=True, showDropDown=False)
dv.error = "Please select a valid option from the list."
dv.errorTitle = "Invalid Entry"
dv.prompt = f"Select an option for: {hdr}"
dv.promptTitle= hdr
dv.sqref = dv_range
ws.add_data_validation(dv)
elif val_type == "whole":
lo, hi = val_formula.split(",")
dv = DataValidation(type="whole", operator="between",
formula1=lo.strip(), formula2=hi.strip(),
allow_blank=True)
dv.error = f"Please enter a whole number between {lo.strip()} and {hi.strip()}."
dv.errorTitle = "Invalid Entry"
dv.sqref = dv_range
ws.add_data_validation(dv)
# ── Date validation for date columns ─────────────────────────────────────────
DATE_COLS = [BIOPSY_COL, 5, FULL_RPT_COL]
for dc in DATE_COLS:
col_letter = get_column_letter(dc)
dv_range = f"{col_letter}{DATA_START_ROW}:{col_letter}{DATA_START_ROW + DATA_ROWS - 1}"
dv = DataValidation(type="date", operator="between",
formula1="DATE(2000,1,1)", formula2="DATE(2099,12,31)",
allow_blank=True)
dv.error = "Please enter a valid date."
dv.errorTitle = "Invalid Date"
dv.sqref = dv_range
ws.add_data_validation(dv)
# ── Freeze panes ─────────────────────────────────────────────────────────────
ws.freeze_panes = "A3"
# ── Auto-filter ──────────────────────────────────────────────────────────────
ws.auto_filter.ref = (
f"A2:{get_column_letter(len(COLS))}{DATA_START_ROW + DATA_ROWS - 1}"
)
# ── Sheet 2: Legend / Guidance ───────────────────────────────────────────────
lg = wb.create_sheet("Guidance")
GUIDE = [
("Column", "Accepted Values / Instructions"),
("Patient ID", "Enter a sequential number (1, 2, 3 …). Never use patient name or NHS number."),
("Age", "Age at diagnosis in whole years (0–120)."),
("Tumour Stage", "Select from dropdown: I / II / III / IV."),
("Date of Biopsy", "Enter in DD/MM/YYYY format."),
("Date of MDT / First Oncology Clinic", "Enter in DD/MM/YYYY format."),
("ER Documented?", "Yes — result was present in notes. No — not documented."),
("ER Result", "Positive / Negative / Not recorded."),
("PR Documented?", "Yes — result was present in notes. No — not documented."),
("PR Result", "Positive / Negative / Not recorded."),
("HER2 IHC Documented?", "Yes / No."),
("HER2 IHC Result", "0 / 1+ / 2+ / 3+ / Not recorded."),
("HER2 FISH Done (if IHC 2+)?", "Yes / No / N/A (N/A if IHC was not 2+)."),
("Ki-67 Documented?", "Yes / No."),
("Ki-67 Value (% or Not recorded)", "Enter a percentage (e.g. 25) or type 'Not recorded'."),
("Date of Full Biomarker Report", "Enter in DD/MM/YYYY format. This is the date the complete pathology/biomarker report was available."),
("TAT (days)", "AUTO-CALCULATED: Full Biomarker Report date minus Biopsy date. Do not edit."),
("CA 15-3 Ordered?", "Yes / No."),
("CA 15-3 Indication", "Metastatic monitoring / Early disease / Unclear / N/A."),
("CEA Ordered?", "Yes / No."),
("CEA Indication", "Metastatic monitoring / Early disease / Unclear / N/A."),
("Treatment Decision Documented at MDT?", "Yes / No — was a treatment decision recorded in the MDT minutes or clinic letter?"),
("Notes", "Free-text: any unusual circumstances, missing records, or relevant observations."),
]
# Header
for gi, (col_name, guide_text) in enumerate(GUIDE, start=1):
c1 = lg.cell(row=gi, column=1, value=col_name)
c2 = lg.cell(row=gi, column=2, value=guide_text)
if gi == 1:
for c in [c1, c2]:
c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF")
c.fill = PatternFill("solid", fgColor="1F4E79")
c.alignment = Alignment(horizontal="center", vertical="center")
c.border = thin_border
else:
row_bg_g = "EBF5FB" if gi % 2 == 0 else "FFFFFF"
for c in [c1, c2]:
c.font = Font(name="Arial", size=10)
c.fill = PatternFill("solid", fgColor=row_bg_g)
c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
c.border = thin_border
lg.row_dimensions[gi].height = 28 if gi == 1 else 36
lg.column_dimensions["A"].width = 35
lg.column_dimensions["B"].width = 70
lg.freeze_panes = "A2"
# ── Save ──────────────────────────────────────────────────────────────────────
out = "/home/daytona/workspace/breast-audit-data/Breast_Cancer_Audit_Data_Collection.xlsx"
wb.save(out)
print(f"Saved: {out}")
python /home/daytona/workspace/breast-audit-data/build_sheet.py
~/breast-audit-data/build_sheet.py
Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/breast-audit-data/build_sheet.py'
python /home/daytona/workspace/breast-audit-data/build_sheet.py
python /home/daytona/skills/xlsx/scripts/recalc.py /home/daytona/workspace/breast-audit-data/Breast_Cancer_Audit_Data_Collection.xlsx
Breast Cancer Audit Data Collection
Spreadsheet · XLSX
- All 22 columns from your specification, across 50 pre-formatted patient rows
- Dropdown menus on every categorical column (Tumour Stage, ER/PR/HER2/Ki-67 results, CA 15-3 & CEA indications, Yes/No fields) - no free-text errors
- Date validation on the three date columns (DD/MM/YYYY format enforced)
- TAT (days) auto-calculates from "Date of Full Biomarker Report" minus "Date of Biopsy" - just enter the dates and it fills itself
- Notes column highlighted in soft yellow to stand out
- Frozen header row and auto-filter enabled for easy sorting/filtering
- Alternating row shading for readability
- A clean reference table explaining every column's accepted values and instructions - useful for anyone filling in the sheet for the first time
the file is not working
python3 -c " import zipfile, sys path = '/home/daytona/workspace/breast-audit-data/Breast_Cancer_Audit_Data_Collection.xlsx' try: zf = zipfile.ZipFile(path) names = zf.namelist() print('ZIP OK, entries:', len(names)) zf.close() except Exception as e: print('ZIP ERROR:', e) "
python3 << 'PYEOF' import openpyxl, io, os from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation wb = Workbook() # ── colours / borders ──────────────────────────────────────────────────────── HDR_BG = "1F4E79" HDR_FG = "FFFFFF" ALT1 = "EBF5FB" ALT2 = "FFFFFF" NOTE_BG = "FFF9C4" THIN = Side(style="thin", color="B0C4DE") border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN) # ── sheet 1 ────────────────────────────────────────────────────────────────── ws = wb.active ws.title = "Data Collection" COLS = [ ("Patient ID", 12), ("Age", 8), ("Tumour Stage", 14), ("Date of Biopsy", 17), ("Date of MDT / First Oncology Clinic", 28), ("ER Documented?", 16), ("ER Result", 22), ("PR Documented?", 16), ("PR Result", 22), ("HER2 IHC Documented?", 20), ("HER2 IHC Result", 20), ("HER2 FISH Done (if IHC 2+)?", 24), ("Ki-67 Documented?", 18), ("Ki-67 Value", 20), ("Date of Full Biomarker Report", 26), ("TAT (days)", 14), ("CA 15-3 Ordered?", 18), ("CA 15-3 Indication", 30), ("CEA Ordered?", 14), ("CEA Indication", 30), ("Treatment Decision Documented at MDT?", 32), ("Notes", 40), ] NCOLS = len(COLS) DATA_START = 3 NROWS = 50 # title banner ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=NCOLS) tc = ws.cell(row=1, column=1, value="Breast Cancer Biomarker Audit — Data Collection Sheet") tc.font = Font(name="Arial", bold=True, size=14, color=HDR_FG) tc.fill = PatternFill("solid", fgColor=HDR_BG) tc.alignment = Alignment(horizontal="center", vertical="center") ws.row_dimensions[1].height = 32 # header row for ci, (hdr, w) in enumerate(COLS, 1): c = ws.cell(row=2, column=ci, value=hdr) c.font = Font(name="Arial", bold=True, size=10, color=HDR_FG) c.fill = PatternFill("solid", fgColor=HDR_BG) c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) c.border = border ws.column_dimensions[get_column_letter(ci)].width = w ws.row_dimensions[2].height = 42 # data rows BIOPSY_COL = 4 # D RPT_COL = 15 # O TAT_COL = 16 # P NOTES_COL = 22 # V for r in range(DATA_START, DATA_START + NROWS): bg = ALT1 if r % 2 == 0 else ALT2 for ci in range(1, NCOLS + 1): c = ws.cell(row=r, column=ci) if ci == TAT_COL: bc = f"{get_column_letter(BIOPSY_COL)}{r}" rc = f"{get_column_letter(RPT_COL)}{r}" c.value = f'=IF(AND(ISNUMBER({rc}),ISNUMBER({bc})),{rc}-{bc},"")' fill_bg = NOTE_BG if ci == NOTES_COL else bg c.fill = PatternFill("solid", fgColor=fill_bg) c.font = Font(name="Arial", size=10) c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=(ci == NOTES_COL)) c.border = border if ci in (BIOPSY_COL, 5, RPT_COL): c.number_format = "DD/MM/YYYY" # ── dropdowns ──────────────────────────────────────────────────────────────── def add_list_dv(ws, col, options_str): cl = get_column_letter(col) dv = DataValidation(type="list", formula1=f'"{options_str}"', allow_blank=True, showDropDown=False) dv.sqref = f"{cl}{DATA_START}:{cl}{DATA_START+NROWS-1}" ws.add_data_validation(dv) add_list_dv(ws, 3, "I,II,III,IV") add_list_dv(ws, 6, "Yes,No") add_list_dv(ws, 7, "Positive,Negative,Not recorded") add_list_dv(ws, 8, "Yes,No") add_list_dv(ws, 9, "Positive,Negative,Not recorded") add_list_dv(ws, 10, "Yes,No") add_list_dv(ws, 11, "0,1+,2+,3+,Not recorded") add_list_dv(ws, 12, "Yes,No,N/A") add_list_dv(ws, 13, "Yes,No") add_list_dv(ws, 17, "Yes,No") add_list_dv(ws, 18, "Metastatic monitoring,Early disease,Unclear,N/A") add_list_dv(ws, 19, "Yes,No") add_list_dv(ws, 20, "Metastatic monitoring,Early disease,Unclear,N/A") add_list_dv(ws, 21, "Yes,No") ws.freeze_panes = "A3" ws.auto_filter.ref = f"A2:{get_column_letter(NCOLS)}{DATA_START+NROWS-1}" # ── sheet 2: guidance ──────────────────────────────────────────────────────── lg = wb.create_sheet("Guidance") GUIDE = [ ("Column", "Accepted Values / Instructions"), ("Patient ID", "Sequential number (1, 2, 3 …). Never use patient name or NHS number."), ("Age", "Age at diagnosis in whole years."), ("Tumour Stage", "Dropdown: I / II / III / IV"), ("Date of Biopsy", "DD/MM/YYYY format."), ("Date of MDT / First Oncology Clinic", "DD/MM/YYYY format."), ("ER Documented?", "Yes = result present in notes. No = not documented."), ("ER Result", "Positive / Negative / Not recorded"), ("PR Documented?", "Yes = result present in notes. No = not documented."), ("PR Result", "Positive / Negative / Not recorded"), ("HER2 IHC Documented?", "Yes / No"), ("HER2 IHC Result", "0 / 1+ / 2+ / 3+ / Not recorded"), ("HER2 FISH Done (if IHC 2+)?", "Yes / No / N/A (N/A if IHC was not 2+)"), ("Ki-67 Documented?", "Yes / No"), ("Ki-67 Value", "Enter a number (e.g. 25 for 25%) or type: Not recorded"), ("Date of Full Biomarker Report", "DD/MM/YYYY — date the complete pathology/biomarker report was available."), ("TAT (days)", "AUTO-CALCULATED: Full Biomarker Report date minus Biopsy date. Do not edit manually."), ("CA 15-3 Ordered?", "Yes / No"), ("CA 15-3 Indication", "Metastatic monitoring / Early disease / Unclear / N/A"), ("CEA Ordered?", "Yes / No"), ("CEA Indication", "Metastatic monitoring / Early disease / Unclear / N/A"), ("Treatment Decision Documented at MDT?", "Yes / No — was a decision recorded in MDT minutes or clinic letter?"), ("Notes", "Free-text: unusual circumstances, missing records, or relevant observations."), ] for gi, (col_name, guide_text) in enumerate(GUIDE, 1): c1 = lg.cell(row=gi, column=1, value=col_name) c2 = lg.cell(row=gi, column=2, value=guide_text) if gi == 1: for c in (c1, c2): c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = PatternFill("solid", fgColor=HDR_BG) c.alignment = Alignment(horizontal="center", vertical="center") c.border = border else: row_bg = ALT1 if gi % 2 == 0 else ALT2 for c in (c1, c2): c.font = Font(name="Arial", size=10) c.fill = PatternFill("solid", fgColor=row_bg) c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True) c.border = border lg.row_dimensions[gi].height = 30 lg.column_dimensions["A"].width = 36 lg.column_dimensions["B"].width = 72 lg.freeze_panes = "A2" # ── write to disk via BytesIO ──────────────────────────────────────────────── out = "/home/daytona/workspace/breast-audit-data/Breast_Cancer_Biomarker_Audit.xlsx" buf = io.BytesIO() wb.save(buf) raw = buf.getvalue() with open(out, "wb") as fh: fh.write(raw) # verify import zipfile zf = zipfile.ZipFile(out) entries = zf.namelist() zf.close() print(f"Saved: {out}") print(f"Size : {os.path.getsize(out):,} bytes") print(f"ZIP entries: {len(entries)}") print("All entries:", entries) PYEOF
python /home/daytona/skills/xlsx/scripts/recalc.py /home/daytona/workspace/breast-audit-data/Breast_Cancer_Biomarker_Audit.xlsx
Breast Cancer Biomarker Audit
Spreadsheet · XLSX
is it possible to make a google sheet link to the same?
- Download the
.xlsxfile using the link above - Go to sheets.google.com
- Click File > Import (or drag the file straight onto the Google Sheets homepage)
- Choose "Replace spreadsheet" and click Import data
.xlsx file is opening correctly for you, you can upload it to your Google Drive and it will be viewable/editable as a Google Sheet natively - no conversion step needed.Generate a CSV version for importing to Google Sheets
python3 << 'PYEOF' import csv, io, os headers = [ "Patient ID", "Age", "Tumour Stage", "Date of Biopsy", "Date of MDT / First Oncology Clinic", "ER Documented?", "ER Result", "PR Documented?", "PR Result", "HER2 IHC Documented?", "HER2 IHC Result", "HER2 FISH Done (if IHC 2+)?", "Ki-67 Documented?", "Ki-67 Value (% or Not recorded)", "Date of Full Biomarker Report", "TAT (days)", "CA 15-3 Ordered?", "CA 15-3 Indication", "CEA Ordered?", "CEA Indication", "Treatment Decision Documented at MDT?", "Notes", ] out = "/home/daytona/workspace/breast-audit-data/Breast_Cancer_Biomarker_Audit.csv" with open(out, "w", newline="", encoding="utf-8") as f: writer = csv.writer(f) writer.writerow(headers) # 50 blank data rows for _ in range(50): writer.writerow([""] * len(headers)) print(f"Saved: {out}") print(f"Size : {os.path.getsize(out):,} bytes") PYEOF
Breast Cancer Biomarker Audit
CSV File · CSV
- Go to sheets.google.com and open a new blank sheet
- Click File > Import
- Choose the CSV file > set Separator type to "Comma"
- Click Import data