Hi I need a column to enter height weight of children with automatic bmi calculation.please include that option I already entered some data don't change those data.
cd /home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/ && python3 -c " import openpyxl wb = openpyxl.load_workbook('DOC-20260710-WA0000.xlsx') print('Sheets:', wb.sheetnames) for sheet in wb.sheetnames: ws = wb[sheet] print(f'\nSheet: {sheet}, dims: {ws.dimensions}, max_row: {ws.max_row}, max_col: {ws.max_column}') for row in ws.iter_rows(min_row=1, max_row=min(5, ws.max_row), values_only=True): print(row) "
python3 -c " import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx') ws = wb['Data Entry'] print('Max col:', ws.max_column, ' Max row:', ws.max_row) # Print header row (row 3) columns for cell in ws[3]: if cell.value: print(f' Col {cell.column} ({cell.column_letter}): {repr(cell.value)[:60]}') # Check how many rows have data data_rows = 0 for row in ws.iter_rows(min_row=4, max_row=ws.max_row, min_col=1, max_col=1, values_only=True): if row[0] is not None: data_rows += 1 print(f'Data rows: {data_rows}') "
python3 << 'EOF' import openpyxl from openpyxl import load_workbook from openpyxl.styles import (PatternFill, Font, Alignment, Border, Side, GradientFill) from openpyxl.utils import get_column_letter import copy src = '/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx' dst = '/home/daytona/workspace/DOC-20260710-WA0000_updated.xlsx' wb = load_workbook(src) ws = wb['Data Entry'] # New columns: AM=39 Height, AN=40 Weight, AO=41 BMI HEIGHT_COL = 39 # AM WEIGHT_COL = 40 # AN BMI_COL = 41 # AO # ── Grab the style of an existing header cell (row 3, col 1) as reference ── ref_hdr = ws.cell(row=3, column=1) def hdr_style(cell, fill_rgb, font_color="FFFFFF"): cell.fill = PatternFill("solid", fgColor=fill_rgb) cell.font = Font(name="Arial", bold=True, size=10, color=font_color, wrap_text=True) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(style="thin", color="000000") cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) # ── Row 1: merge header title into new cols (copy the merged title row) ── # The title "PRECOCIOUS PUBERTY STUDY..." spans all columns — extend the merge # Find and update the merged cell range for row 1 for merged in list(ws.merged_cells.ranges): # expand any merge that covers row 1 and currently ends at col 38 mr = str(merged) if merged.min_row == 1 and merged.max_row <= 2 and merged.max_col == 38: ws.unmerge_cells(str(merged)) ws.merge_cells(start_row=merged.min_row, start_col=merged.min_col, end_row=merged.max_row, end_col=BMI_COL) # ── Row 2: same banner – extend merge ── for merged in list(ws.merged_cells.ranges): if merged.min_row == 2 and merged.max_row == 2 and merged.max_col == 38: ws.unmerge_cells(str(merged)) ws.merge_cells(start_row=2, start_col=merged.min_col, end_row=2, end_col=BMI_COL) # ── Row 3: column headers ── h_cell = ws.cell(row=3, column=HEIGHT_COL, value="Height\n(cm)") w_cell = ws.cell(row=3, column=WEIGHT_COL, value="Weight\n(kg)") bmi_cell = ws.cell(row=3, column=BMI_COL, value="BMI\n(Auto)") # Green-ish for height/weight (input), blue for BMI (calculated) hdr_style(h_cell, "4CAF50") # green hdr_style(w_cell, "4CAF50") # green hdr_style(bmi_cell, "1565C0") # blue # Set row 3 height to match existing (approx 45) ws.row_dimensions[3].height = 45 # Set column widths ws.column_dimensions[get_column_letter(HEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(WEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(BMI_COL)].width = 12 # ── Data rows 4 → 503: add BMI formula ── thin = Side(style="thin", color="CCCCCC") light_border = Border(left=thin, right=thin, top=thin, bottom=thin) for r in range(4, 504): h_col_ltr = get_column_letter(HEIGHT_COL) w_col_ltr = get_column_letter(WEIGHT_COL) b_col_ltr = get_column_letter(BMI_COL) # Height input cell hc = ws.cell(row=r, column=HEIGHT_COL) hc.number_format = "0.0" hc.border = light_border hc.alignment = Alignment(horizontal="center", vertical="center") # Weight input cell wc = ws.cell(row=r, column=WEIGHT_COL) wc.number_format = "0.0" wc.border = light_border wc.alignment = Alignment(horizontal="center", vertical="center") # BMI formula: =IF(OR(AM4="",AN4=""), "", ROUND(AN4/(AM4/100)^2, 1)) bmi_formula = (f'=IF(OR({h_col_ltr}{r}="",' f'{w_col_ltr}{r}=""),"",ROUND({w_col_ltr}{r}' f'/({h_col_ltr}{r}/100)^2,1))') bc = ws.cell(row=r, column=BMI_COL, value=bmi_formula) bc.number_format = "0.0" bc.border = light_border bc.alignment = Alignment(horizontal="center", vertical="center") bc.font = Font(name="Arial", size=10, color="1565C0") # blue = formula # ── Add a legend note below the existing data area (row 505) ── note = ws.cell(row=505, column=HEIGHT_COL, value="↑ Enter Height (cm) & Weight (kg) — BMI auto-fills") note.font = Font(name="Arial", italic=True, size=9, color="555555") wb.save(dst) print("Saved:", dst) EOF
python3 << 'EOF' import openpyxl from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils import get_column_letter, column_index_from_string src = '/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx' dst = '/home/daytona/workspace/DOC-20260710-WA0000_updated.xlsx' wb = load_workbook(src) ws = wb['Data Entry'] HEIGHT_COL = 39 # AM WEIGHT_COL = 40 # AN BMI_COL = 41 # AO def hdr_style(cell, fill_rgb, font_color="FFFFFF"): cell.fill = PatternFill("solid", fgColor=fill_rgb) cell.font = Font(name="Arial", bold=True, size=10, color=font_color, wrap_text=True) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(style="thin", color="000000") cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) # ── Extend merged cells in rows 1-2 that currently end at col 38 ── merges_to_fix = [] for merged in ws.merged_cells.ranges: if merged.max_col == 38 and merged.min_row in (1, 2): merges_to_fix.append((merged.min_row, merged.max_row, merged.min_col, merged.max_col)) for (min_r, max_r, min_c, max_c) in merges_to_fix: rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(max_c)}{max_r}" ws.unmerge_cells(rng) new_rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(BMI_COL)}{max_r}" ws.merge_cells(new_rng) # ── Row 3: column headers ── h_cell = ws.cell(row=3, column=HEIGHT_COL, value="Height\n(cm)") w_cell = ws.cell(row=3, column=WEIGHT_COL, value="Weight\n(kg)") bmi_cell = ws.cell(row=3, column=BMI_COL, value="BMI\n(Auto)") hdr_style(h_cell, "4CAF50") # green = input hdr_style(w_cell, "4CAF50") # green = input hdr_style(bmi_cell, "1565C0") # blue = formula ws.row_dimensions[3].height = 45 ws.column_dimensions[get_column_letter(HEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(WEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(BMI_COL)].width = 12 # ── Data rows 4-503: BMI formula ── thin_s = Side(style="thin", color="CCCCCC") light_border = Border(left=thin_s, right=thin_s, top=thin_s, bottom=thin_s) H = get_column_letter(HEIGHT_COL) W = get_column_letter(WEIGHT_COL) B = get_column_letter(BMI_COL) for r in range(4, 504): hc = ws.cell(row=r, column=HEIGHT_COL) hc.number_format = "0.0" hc.border = light_border hc.alignment = Alignment(horizontal="center", vertical="center") wc = ws.cell(row=r, column=WEIGHT_COL) wc.number_format = "0.0" wc.border = light_border wc.alignment = Alignment(horizontal="center", vertical="center") formula = f'=IF(OR({H}{r}="",{W}{r}=""),"",ROUND({W}{r}/({H}{r}/100)^2,1))' bc = ws.cell(row=r, column=BMI_COL, value=formula) bc.number_format = "0.0" bc.border = light_border bc.alignment = Alignment(horizontal="center", vertical="center") bc.font = Font(name="Arial", size=10, color="1565C0") # ── Tip note below data ── note = ws.cell(row=505, column=HEIGHT_COL, value="← Enter Height (cm) & Weight (kg) — BMI fills automatically") note.font = Font(name="Arial", italic=True, size=9, color="555555") wb.save(dst) print("Done! Saved to:", dst) EOF
python3 << 'EOF' import openpyxl from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils import get_column_letter src = '/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx' dst = '/home/daytona/workspace/DOC-20260710-WA0000_updated.xlsx' wb = load_workbook(src) ws = wb['Data Entry'] HEIGHT_COL = 39 # AM WEIGHT_COL = 40 # AN BMI_COL = 41 # AO def hdr_style(cell, fill_rgb, font_color="FFFFFF"): cell.fill = PatternFill("solid", fgColor=fill_rgb) cell.font = Font(name="Arial", bold=True, size=10, color=font_color) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(style="thin", color="000000") cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) # ── Extend merged cells in rows 1-2 that currently end at col 38 ── merges_to_fix = [] for merged in ws.merged_cells.ranges: if merged.max_col == 38 and merged.min_row in (1, 2): merges_to_fix.append((merged.min_row, merged.max_row, merged.min_col, merged.max_col)) for (min_r, max_r, min_c, max_c) in merges_to_fix: rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(max_c)}{max_r}" ws.unmerge_cells(rng) new_rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(BMI_COL)}{max_r}" ws.merge_cells(new_rng) # ── Row 3: column headers ── h_cell = ws.cell(row=3, column=HEIGHT_COL, value="Height\n(cm)") w_cell = ws.cell(row=3, column=WEIGHT_COL, value="Weight\n(kg)") bmi_cell = ws.cell(row=3, column=BMI_COL, value="BMI\n(Auto)") hdr_style(h_cell, "4CAF50") # green = input hdr_style(w_cell, "4CAF50") # green = input hdr_style(bmi_cell, "1565C0") # blue = formula ws.row_dimensions[3].height = 45 ws.column_dimensions[get_column_letter(HEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(WEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(BMI_COL)].width = 12 # ── Data rows 4-503: BMI formula ── thin_s = Side(style="thin", color="CCCCCC") light_border = Border(left=thin_s, right=thin_s, top=thin_s, bottom=thin_s) H = get_column_letter(HEIGHT_COL) W = get_column_letter(WEIGHT_COL) B = get_column_letter(BMI_COL) for r in range(4, 504): hc = ws.cell(row=r, column=HEIGHT_COL) hc.number_format = "0.0" hc.border = light_border hc.alignment = Alignment(horizontal="center", vertical="center") wc = ws.cell(row=r, column=WEIGHT_COL) wc.number_format = "0.0" wc.border = light_border wc.alignment = Alignment(horizontal="center", vertical="center") formula = f'=IF(OR({H}{r}="",{W}{r}=""),"",ROUND({W}{r}/({H}{r}/100)^2,1))' bc = ws.cell(row=r, column=BMI_COL, value=formula) bc.number_format = "0.0" bc.border = light_border bc.alignment = Alignment(horizontal="center", vertical="center") bc.font = Font(name="Arial", size=10, color="1565C0") # ── Tip note below data ── note = ws.cell(row=505, column=HEIGHT_COL, value="<- Enter Height (cm) & Weight (kg) — BMI fills automatically") note.font = Font(name="Arial", italic=True, size=9, color="555555") wb.save(dst) print("Done! Saved to:", dst) EOF
mkdir -p /home/daytona/workspace/bmi-update && ls /home/daytona/workspace/bmi-update/
python3 << 'EOF' import shutil, openpyxl from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils import get_column_letter src = '/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx' dst = '/home/daytona/workspace/bmi-update/DOC-20260710-WA0000_updated.xlsx' # Copy first so we work on a fresh copy shutil.copy2(src, dst) wb = load_workbook(dst) ws = wb['Data Entry'] HEIGHT_COL = 39 # AM WEIGHT_COL = 40 # AN BMI_COL = 41 # AO def hdr_style(cell, fill_rgb, font_color="FFFFFF"): cell.fill = PatternFill("solid", fgColor=fill_rgb) cell.font = Font(name="Arial", bold=True, size=10, color=font_color) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(style="thin", color="000000") cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) # Extend merged cells in rows 1-2 that end at col 38 merges_to_fix = [] for merged in ws.merged_cells.ranges: if merged.max_col == 38 and merged.min_row in (1, 2): merges_to_fix.append((merged.min_row, merged.max_row, merged.min_col, merged.max_col)) for (min_r, max_r, min_c, max_c) in merges_to_fix: rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(max_c)}{max_r}" ws.unmerge_cells(rng) new_rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(BMI_COL)}{max_r}" ws.merge_cells(new_rng) # Row 3: headers h_cell = ws.cell(row=3, column=HEIGHT_COL, value="Height\n(cm)") w_cell = ws.cell(row=3, column=WEIGHT_COL, value="Weight\n(kg)") bmi_cell = ws.cell(row=3, column=BMI_COL, value="BMI\n(Auto)") hdr_style(h_cell, "4CAF50") # green = input hdr_style(w_cell, "4CAF50") # green = input hdr_style(bmi_cell, "1565C0") # blue = calculated ws.row_dimensions[3].height = 45 ws.column_dimensions[get_column_letter(HEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(WEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(BMI_COL)].width = 12 # Data rows 4-503 thin_s = Side(style="thin", color="CCCCCC") light_border = Border(left=thin_s, right=thin_s, top=thin_s, bottom=thin_s) H = get_column_letter(HEIGHT_COL) W = get_column_letter(WEIGHT_COL) for r in range(4, 504): hc = ws.cell(row=r, column=HEIGHT_COL) hc.number_format = "0.0" hc.border = light_border hc.alignment = Alignment(horizontal="center", vertical="center") wc = ws.cell(row=r, column=WEIGHT_COL) wc.number_format = "0.0" wc.border = light_border wc.alignment = Alignment(horizontal="center", vertical="center") formula = f'=IF(OR({H}{r}="",{W}{r}=""),"",ROUND({W}{r}/({H}{r}/100)^2,1))' bc = ws.cell(row=r, column=BMI_COL, value=formula) bc.number_format = "0.0" bc.border = light_border bc.alignment = Alignment(horizontal="center", vertical="center") bc.font = Font(name="Arial", size=10, color="1565C0") # Tip note note = ws.cell(row=505, column=HEIGHT_COL, value="Enter Height (cm) & Weight (kg) above — BMI fills automatically") note.font = Font(name="Arial", italic=True, size=9, color="555555") wb.save(dst) print("SUCCESS - saved to:", dst) # Verify wb2 = load_workbook(dst, data_only=False) ws2 = wb2['Data Entry'] print("Col AM header:", ws2.cell(3, HEIGHT_COL).value) print("Col AN header:", ws2.cell(3, WEIGHT_COL).value) print("Col AO header:", ws2.cell(3, BMI_COL).value) print("BMI formula r4:", ws2.cell(4, BMI_COL).value) EOF
cp '/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx' '/home/daytona/workspace/bmi-update/DOC-20260710-WA0000_updated.xlsx' && ls -la /home/daytona/workspace/bmi-update/
python3 << 'EOF' from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils import get_column_letter dst = '/home/daytona/workspace/bmi-update/DOC-20260710-WA0000_updated.xlsx' wb = load_workbook(dst) ws = wb['Data Entry'] HEIGHT_COL = 39 # AM WEIGHT_COL = 40 # AN BMI_COL = 41 # AO def hdr_style(cell, fill_rgb, font_color="FFFFFF"): cell.fill = PatternFill("solid", fgColor=fill_rgb) cell.font = Font(name="Arial", bold=True, size=10, color=font_color) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(style="thin", color="000000") cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) # Extend merged cells in rows 1-2 that end at col 38 merges_to_fix = [] for merged in ws.merged_cells.ranges: if merged.max_col == 38 and merged.min_row in (1, 2): merges_to_fix.append((merged.min_row, merged.max_row, merged.min_col, merged.max_col)) for (min_r, max_r, min_c, max_c) in merges_to_fix: rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(max_c)}{max_r}" ws.unmerge_cells(rng) new_rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(BMI_COL)}{max_r}" ws.merge_cells(new_rng) # Row 3: headers h_cell = ws.cell(row=3, column=HEIGHT_COL, value="Height\n(cm)") w_cell = ws.cell(row=3, column=WEIGHT_COL, value="Weight\n(kg)") bmi_cell = ws.cell(row=3, column=BMI_COL, value="BMI\n(Auto)") hdr_style(h_cell, "4CAF50") # green = input hdr_style(w_cell, "4CAF50") # green = input hdr_style(bmi_cell, "1565C0") # blue = calculated ws.row_dimensions[3].height = 45 ws.column_dimensions[get_column_letter(HEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(WEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(BMI_COL)].width = 12 # Data rows 4-503 thin_s = Side(style="thin", color="CCCCCC") light_border = Border(left=thin_s, right=thin_s, top=thin_s, bottom=thin_s) H = get_column_letter(HEIGHT_COL) W = get_column_letter(WEIGHT_COL) for r in range(4, 504): hc = ws.cell(row=r, column=HEIGHT_COL) hc.number_format = "0.0" hc.border = light_border hc.alignment = Alignment(horizontal="center", vertical="center") wc = ws.cell(row=r, column=WEIGHT_COL) wc.number_format = "0.0" wc.border = light_border wc.alignment = Alignment(horizontal="center", vertical="center") formula = f'=IF(OR({H}{r}="",{W}{r}=""),"",ROUND({W}{r}/({H}{r}/100)^2,1))' bc = ws.cell(row=r, column=BMI_COL, value=formula) bc.number_format = "0.0" bc.border = light_border bc.alignment = Alignment(horizontal="center", vertical="center") bc.font = Font(name="Arial", size=10, color="1565C0") # Tip note note = ws.cell(row=505, column=HEIGHT_COL, value="Enter Height (cm) & Weight (kg) above - BMI fills automatically") note.font = Font(name="Arial", italic=True, size=9, color="555555") wb.save(dst) print("SUCCESS") # Verify wb2 = load_workbook(dst, data_only=False) ws2 = wb2['Data Entry'] print("Col AM header:", ws2.cell(3, HEIGHT_COL).value) print("Col AN header:", ws2.cell(3, WEIGHT_COL).value) print("Col AO header:", ws2.cell(3, BMI_COL).value) print("BMI formula r4:", ws2.cell(4, BMI_COL).value) # Check existing data still intact print("Row 4 ID still:", ws2.cell(4, 1).value) print("Row 4 Age still:", ws2.cell(4, 3).value) EOF
python3 << 'EOF' import os, io from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils import get_column_letter src = '/home/daytona/workspace/attachments/a4f1e331-1eb1-474c-90a3-e6ef6652d14d/DOC-20260710-WA0000.xlsx' dst = '/home/daytona/workspace/bmi-update/DOC-20260710-WA0000_updated.xlsx' wb = load_workbook(src) ws = wb['Data Entry'] HEIGHT_COL = 39 # AM WEIGHT_COL = 40 # AN BMI_COL = 41 # AO def hdr_style(cell, fill_rgb, font_color="FFFFFF"): cell.fill = PatternFill("solid", fgColor=fill_rgb) cell.font = Font(name="Arial", bold=True, size=10, color=font_color) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(style="thin", color="000000") cell.border = Border(left=thin, right=thin, top=thin, bottom=thin) # Extend merged cells in rows 1-2 that end at col 38 merges_to_fix = [] for merged in ws.merged_cells.ranges: if merged.max_col == 38 and merged.min_row in (1, 2): merges_to_fix.append((merged.min_row, merged.max_row, merged.min_col, merged.max_col)) for (min_r, max_r, min_c, max_c) in merges_to_fix: rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(max_c)}{max_r}" ws.unmerge_cells(rng) new_rng = f"{get_column_letter(min_c)}{min_r}:{get_column_letter(BMI_COL)}{max_r}" ws.merge_cells(new_rng) # Row 3: headers h_cell = ws.cell(row=3, column=HEIGHT_COL, value="Height\n(cm)") w_cell = ws.cell(row=3, column=WEIGHT_COL, value="Weight\n(kg)") bmi_cell = ws.cell(row=3, column=BMI_COL, value="BMI\n(Auto)") hdr_style(h_cell, "4CAF50") hdr_style(w_cell, "4CAF50") hdr_style(bmi_cell, "1565C0") ws.row_dimensions[3].height = 45 ws.column_dimensions[get_column_letter(HEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(WEIGHT_COL)].width = 10 ws.column_dimensions[get_column_letter(BMI_COL)].width = 12 thin_s = Side(style="thin", color="CCCCCC") light_border = Border(left=thin_s, right=thin_s, top=thin_s, bottom=thin_s) H = get_column_letter(HEIGHT_COL) W = get_column_letter(WEIGHT_COL) for r in range(4, 504): hc = ws.cell(row=r, column=HEIGHT_COL) hc.number_format = "0.0" hc.border = light_border hc.alignment = Alignment(horizontal="center", vertical="center") wc = ws.cell(row=r, column=WEIGHT_COL) wc.number_format = "0.0" wc.border = light_border wc.alignment = Alignment(horizontal="center", vertical="center") formula = f'=IF(OR({H}{r}="",{W}{r}=""),"",ROUND({W}{r}/({H}{r}/100)^2,1))' bc = ws.cell(row=r, column=BMI_COL, value=formula) bc.number_format = "0.0" bc.border = light_border bc.alignment = Alignment(horizontal="center", vertical="center") bc.font = Font(name="Arial", size=10, color="1565C0") note = ws.cell(row=505, column=HEIGHT_COL, value="Enter Height (cm) & Weight (kg) above - BMI fills automatically") note.font = Font(name="Arial", italic=True, size=9, color="555555") # Save to BytesIO then write bytes to file buf = io.BytesIO() wb.save(buf) buf.seek(0) data = buf.read() with open(dst, 'wb') as f: f.write(data) print("SUCCESS - size:", len(data)) EOF
python3 << 'EOF' from openpyxl import load_workbook dst = '/home/daytona/workspace/bmi-update/DOC-20260710-WA0000_updated.xlsx' wb = load_workbook(dst, data_only=False) ws = wb['Data Entry'] print("=== HEADER ROW 3 (new cols) ===") print("Col AM (39):", repr(ws.cell(3, 39).value)) print("Col AN (40):", repr(ws.cell(3, 40).value)) print("Col AO (41):", repr(ws.cell(3, 41).value)) print("\n=== BMI FORMULAS (rows 4-6) ===") for r in [4, 5, 6]: print(f"Row {r} BMI formula:", ws.cell(r, 41).value) print("\n=== EXISTING DATA INTACT (row 4) ===") print("ID:", ws.cell(4,1).value) print("Age:", ws.cell(4,3).value) print("Grade:", ws.cell(4,4).value) print("School:", ws.cell(4,5).value) print("\n=== EXISTING DATA INTACT (row 5) ===") print("ID:", ws.cell(5,1).value) print("Age:", ws.cell(5,3).value) # Count data rows data_rows = sum(1 for r in ws.iter_rows(min_row=4, max_row=503, min_col=1, max_col=1, values_only=True) if r[0] is not None) print(f"\nData rows preserved: {data_rows}") print("Max col:", ws.max_column) EOF
DOC 20260710 WA0000 updated
Spreadsheet · XLSX
| Column | Label | Colour | Purpose |
|---|---|---|---|
| AM | Height (cm) | Green header | You type the child's height in centimetres |
| AN | Weight (kg) | Green header | You type the child's weight in kilograms |
| AO | BMI (Auto) | Blue header | Calculates automatically - no typing needed |
=IF(OR(AM4="",AN4=""),"",ROUND(AN4/(AM4/100)^2,1))
- If either Height or Weight is blank, the BMI cell stays blank (no error).
- Once both are entered, BMI = Weight (kg) / Height (m)² - rounded to 1 decimal place.
- The formula runs for all 500 participant rows (rows 4-503).