research proposal questionnaire development validation STROBE ethics informed consent guidance medical observational study

questionnaire development validation AND observational study

~/

~/attachments/eeb53423-ed57-48c4-a00b-923b750869a3

python3 -c "from docx import Document; from pathlib import Path; files=[Path('/tmp/workspace/attachments/eeb53423-ed57-48c4-a00b-923b750869a3/consent form (English).docx'),Path('/tmp/workspace/attachments/eeb53423-ed57-48c4-a00b-923b750869a3/Questionnaire English.docx'),Path('/tmp/workspace/attachments/eeb53423-ed57-48c4-a00b-923b750869a3/Updated Format for research proposal Submission in ISRC GMC, Datia[1].docx')];\nfor f in files:\n print('\\n'+'='*80+'\\nFILE:',f.name); d=Document(str(f));\n for i,p in enumerate(d.paragraphs):\n if p.text.strip(): print(f'P{i}: {p.text}');\n for ti,t in enumerate(d.tables):\n print(f'--TABLE {ti}--');\n for row in t.rows: print(' | '.join(c.text.replace('\\n',' / ') for c in row.cells))"

python3 -c "from docx import Document; from pathlib import Path; files=list(Path('/tmp/workspace/attachments/eeb53423-ed57-48c4-a00b-923b750869a3').glob('*.docx')); out=[]; [out.extend(['\\n'+'='*80, 'FILE: '+f.name] + ['P%d: %s'%(i,p.text) for i,p in enumerate((d:=Document(str(f))).paragraphs) if p.text.strip()] + sum([['--TABLE %d--'%ti]+[' | '.join(c.text.replace(chr(10),' / ') for c in row.cells) for row in t.rows] for ti,t in enumerate(d.tables)], [])) for f in files]; Path('/tmp/workspace/attachments/extracted_review.txt').write_text('\\n'.join(out), encoding='utf-8')" && python3 -c "from pathlib import Path; print(Path('/tmp/workspace/attachments/extracted_review.txt').stat().st_size)"

~/extract_docx.py

python3 "/tmp/workspace/extract_docx.py" && python3 -c "from pathlib import Path; print(Path('/tmp/workspace/attachments/extracted_review.txt').stat().st_size)"

glaucoma knowledge health personnel

World Glaucoma Association consensus glaucoma diagnosis management open angle glaucoma risk factors 2024

~/

mkdir -p "/tmp/workspace/glaucoma-study-package/src" "/tmp/workspace/glaucoma-study-package/output" && printf "created\n"

~/

glaucoma knowledge health personnel

2025 primary open angle glaucoma preferred practice pattern diagnosis risk factors American Academy Ophthalmology

cat > "/tmp/workspace/glaucoma-study-package/src/create_documents.py" <<'PY' from docx import Document from docx.shared import Inches, Pt, Cm from docx.enum.text import WD_ALIGN_PARAGRAPH from docx.enum.table import WD_TABLE_ALIGNMENT, WD_CELL_VERTICAL_ALIGNMENT from docx.oxml import OxmlElement from docx.oxml.ns import qn from docx.enum.section import WD_SECTION from pathlib import Path OUT=Path('/tmp/workspace/glaucoma-study-package/output') OUT.mkdir(parents=True, exist_ok=True) NAVY='17365D'; BLUE='1F4E78'; LIGHT='D9EAF7'; PALE='EEF5FB'; GRAY='666666' def shade(cell, fill): tcPr=cell._tc.get_or_add_tcPr(); shd=OxmlElement('w:shd'); shd.set(qn('w:fill'),fill); tcPr.append(shd) def border(cell, color='A6A6A6'): tcPr=cell._tc.get_or_add_tcPr(); borders=tcPr.first_child_found_in('w:tcBorders') if borders is None: borders=OxmlElement('w:tcBorders'); tcPr.append(borders) for edge in ('top','left','bottom','right'): el=OxmlElement(f'w:{edge}'); el.set(qn('w:val'),'single'); el.set(qn('w:sz'),'4'); el.set(qn('w:color'),color); borders.append(el) def set_repeat_table_header(row): trPr=row._tr.get_or_add_trPr(); el=OxmlElement('w:tblHeader'); el.set(qn('w:val'),'true'); trPr.append(el) def set_cell_text(cell, text, bold=False, size=10, align=None): cell.text=''; p=cell.paragraphs[0]; if align is not None: p.alignment=align r=p.add_run(text); r.bold=bold; r.font.name='Arial'; r.font.size=Pt(size) cell.vertical_alignment=WD_CELL_VERTICAL_ALIGNMENT.CENTER border(cell) def setup(doc): sec=doc.sections[0]; sec.page_width=Cm(21); sec.page_height=Cm(29.7) sec.top_margin=Cm(2.1); sec.bottom_margin=Cm(1.9); sec.left_margin=Cm(2.2); sec.right_margin=Cm(2.0) normal=doc.styles['Normal']; normal.font.name='Arial'; normal.font.size=Pt(10.5); normal.paragraph_format.space_after=Pt(5); normal.paragraph_format.line_spacing=1.15 for name,size,color in [('Title',18,NAVY),('Heading 1',14,NAVY),('Heading 2',11,BLUE)]: s=doc.styles[name]; s.font.name='Arial'; s.font.size=Pt(size); s.font.bold=True; s.font.color.rgb=__import__('docx').shared.RGBColor.from_string(color); s.paragraph_format.space_before=Pt(12); s.paragraph_format.space_after=Pt(5) footer=sec.footer.paragraphs[0]; footer.alignment=WD_ALIGN_PARAGRAPH.CENTER run=footer.add_run('Government Medical College, Datia | Research Protocol'); run.font.name='Arial'; run.font.size=Pt(8); run.font.color.rgb=__import__('docx').shared.RGBColor.from_string(GRAY) fld=OxmlElement('w:fldSimple'); fld.set(qn('w:instr'),'PAGE'); footer._p.append(fld) def title(doc, text, subtitle=None): p=doc.add_paragraph(); p.alignment=WD_ALIGN_PARAGRAPH.CENTER; p.paragraph_format.space_after=Pt(6) r=p.add_run(text); r.bold=True; r.font.name='Arial'; r.font.size=Pt(18); r.font.color.rgb=__import__('docx').shared.RGBColor.from_string(NAVY) if subtitle: p=doc.add_paragraph(); p.alignment=WD_ALIGN_PARAGRAPH.CENTER r=p.add_run(subtitle); r.italic=True; r.font.name='Arial'; r.font.size=Pt(11); r.font.color.rgb=__import__('docx').shared.RGBColor.from_string(GRAY) def add_para(doc, text, bold_lead=None): p=doc.add_paragraph(); p.paragraph_format.first_line_indent=Cm(.5) if bold_lead and text.startswith(bold_lead): r=p.add_run(bold_lead);r.bold=True;p.add_run(text[len(bold_lead):]) else:p.add_run(text) return p def bullets(doc, items): for x in items: doc.add_paragraph(x, style='List Bullet') def heading(doc,t,level=1): doc.add_heading(t,level=level) def metadata_table(doc): rows=[('Study title','Knowledge of Glaucoma Among Healthcare Providers at Government Medical College, Datia: A Cross-Sectional Study'),('Principal investigator','Dr. Abhishek U. Rathod, PG Resident (M.S.), Department of Ophthalmology'),('Co-investigator','Dr. Aarushi Bhatnagar, PG Resident (M.S.), Department of Ophthalmology'),('Institution','Government Medical College, Datia, Madhya Pradesh'),('Study design','Hospital-based analytical cross-sectional questionnaire study'),('Funding','Non-funded'),('Proposed duration','Six months, after Institutional Ethics Committee approval')] t=doc.add_table(rows=0, cols=2);t.alignment=WD_TABLE_ALIGNMENT.CENTER;t.style='Table Grid' for i,(a,b) in enumerate(rows): c=t.add_row().cells;set_cell_text(c[0],a,True,9);set_cell_text(c[1],b,False,9) shade(c[0],LIGHT if i%2==0 else PALE) doc.add_paragraph() def make_synopsis(): d=Document();setup(d);title(d,'RESEARCH SYNOPSIS','For submission to the Institutional Scientific and Research Committee / Institutional Ethics Committee') metadata_table(d) heading(d,'1. Background and rationale') add_para(d,'Glaucoma is a chronic progressive optic neuropathy characterized by retinal ganglion-cell loss, structural damage to the optic nerve head and retinal nerve fibre layer, and corresponding visual-field loss. It is a leading cause of irreversible blindness worldwide. Primary open-angle glaucoma often remains asymptomatic until substantial visual loss has occurred; therefore, early recognition of at-risk patients and appropriate referral are important.') add_para(d,'Healthcare providers outside ophthalmology may encounter patients with glaucoma risk factors or symptoms suggestive of acute angle closure. Their knowledge can influence patient education, identification of urgent presentations, and referral for ophthalmic assessment. Mapping knowledge gaps across professional cadres can guide context-specific teaching and continuing professional development.') add_para(d,'This study will measure glaucoma-related knowledge among eligible healthcare providers at Government Medical College, Datia, using a newly developed structured questionnaire that will undergo expert content review and pilot testing before the main survey.') heading(d,'2. Aim') add_para(d,'To assess glaucoma-related knowledge among non-ophthalmology healthcare providers at Government Medical College, Datia.') heading(d,'3. Objectives') heading(d,'Primary objective',2); add_para(d,'To estimate the mean percentage glaucoma-knowledge score among eligible healthcare providers.') heading(d,'Secondary objectives',2); bullets(d,[ 'To compare total knowledge scores across faculty/medical officers, postgraduate or junior residents, interns, and staff nurses.', 'To describe knowledge in four domains: disease concepts, risk factors, diagnosis/referral, and treatment/follow-up.', 'To examine associations between knowledge score and cadre, age group, clinical experience, prior glaucoma teaching, and prior clinical exposure to patients with glaucoma.', 'To identify topics requiring targeted educational interventions.' ]) heading(d,'4. Research question') add_para(d,'What is the level of glaucoma-related knowledge among non-ophthalmology healthcare providers at Government Medical College, Datia, and how does it vary by professional cadre and prior training?') heading(d,'5. Methods') heading(d,'Study design and setting',2); add_para(d,'This will be a hospital-based analytical cross-sectional study conducted at Government Medical College and associated tertiary care teaching hospital, Datia, Madhya Pradesh. Data collection will begin only after approval by the Institutional Ethics Committee (IEC).') heading(d,'Study duration',2); add_para(d,'Six months after IEC approval: approximately one month for expert review and pilot testing, three months for participant recruitment and data collection, and two months for data cleaning, analysis, and manuscript preparation.') heading(d,'Study population',2); add_para(d,'Eligible participants will be non-ophthalmology faculty members/medical officers, postgraduate residents or junior residents, MBBS interns, and staff nurses working at the institution.') heading(d,'Eligibility criteria',2) p=d.add_paragraph();p.add_run('Inclusion criteria').bold=True; bullets(d,['Age 18 years or older.','Belonging to one of the eligible professional cadres.','Providing written informed consent.']) p=d.add_paragraph();p.add_run('Exclusion criteria').bold=True; bullets(d,['Ophthalmology faculty and ophthalmology residents, because specialist training is expected to create a ceiling effect.','Participants included in the pilot test.','Individuals who decline consent.','Questionnaires with more than 20% unanswered knowledge items.']) heading(d,'Sample size',2) add_para(d,'For estimating a single proportion, assuming 50% adequate knowledge (a conservative assumption in the absence of a reliable local estimate), 95% confidence level, and 5% absolute precision: n = Z²pq/d² = (1.96² × 0.50 × 0.50)/(0.05²) = 384. Allowing 10% non-response, the target sample size will be 423 participants. If the total eligible workforce is smaller, the finite-population correction will be applied after the eligible workforce list is obtained, and the final target will be documented before recruitment.') heading(d,'Sampling and recruitment',2) add_para(d,'A proportionate stratified sampling approach will be used. Eligible participants will be stratified by cadre, and the target sample from each cadre will be allocated in proportion to its number in the eligible workforce. Potential participants will be approached through departmental lists, duty rosters, and academic sessions. Recruitment will avoid direct pressure from supervisors. The number approached, consenting, and returning usable questionnaires will be recorded by cadre to calculate the response rate. If operational constraints prevent probability sampling, this will be documented and reported as a limitation.') heading(d,'Study instrument',2) add_para(d,'The self-administered English questionnaire contains seven participant-characteristic questions and 25 glaucoma-knowledge statements. Each statement will have True, False, and Don’t know options. The knowledge statements are grouped into disease concepts, risk factors, diagnosis/referral, and treatment/follow-up. No name, employee number, phone number, or other direct identifier will be collected on the questionnaire.') heading(d,'Instrument development and pretesting',2) bullets(d,['Items will be drafted from standard glaucoma concepts and reviewed by an expert panel of 5 to 7 persons with expertise in ophthalmology and research methodology.', 'Experts will rate relevance and clarity; item-level and scale-level content validity indices will be calculated where feasible. Items judged unclear or insufficiently relevant will be revised or removed.', 'The revised tool will be pilot-tested among 20 to 30 eligible healthcare providers who will not be recruited into the main study. Comprehension, completion time, and item ambiguity will be assessed.', 'Internal consistency of the dichotomous scored knowledge items will be assessed using Kuder-Richardson Formula 20 (KR-20). The questionnaire will not be described as validated until this process is completed and results are reported.']) heading(d,'Data-collection procedure',2) add_para(d,'After written informed consent, participants will independently complete the paper or electronic questionnaire in approximately 10 to 15 minutes. Completed paper questionnaires will be placed in a sealed collection box or returned without a name. Signed consent forms will be stored separately from completed questionnaires. The participant may skip any question or withdraw before submitting the questionnaire without penalty.') heading(d,'Variables and scoring',2) add_para(d,'Each correct knowledge response will score 1 point. Incorrect and Don’t know responses will score 0. The total raw score will range from 0 to 25 and will be expressed as a percentage: (total correct/25) × 100. The continuous percentage score will be the primary outcome. For descriptive presentation only, an operational category of adequate knowledge (≥70%) and inadequate knowledge (<70%) may be used; these categories will not replace analysis of the continuous score.') heading(d,'Data management and confidentiality',2) add_para(d,'Data will be coded using a study ID and entered into a password-protected dataset accessible only to the research team. Paper forms and consent forms will be stored in separate locked locations. The linkage between study ID and any recruitment log, if required for response-rate monitoring, will be kept separately. Aggregate results only will be reported. Data will be retained for the period required by institutional policy and then securely destroyed.') heading(d,'Statistical analysis plan',2) bullets(d,['Descriptive statistics: categorical variables will be summarized as frequency and percentage. Knowledge score will be summarized as mean with standard deviation if approximately normally distributed, otherwise median with interquartile range.', 'Questionnaires with more than 20% missing knowledge responses will be excluded from the main analysis. For questionnaires with 20% or fewer missing knowledge items, unanswered items will be scored as 0, with a complete-case sensitivity analysis reported.', 'Knowledge scores across cadres will be compared using one-way ANOVA with suitable post-hoc tests if assumptions are met, or Kruskal-Wallis test otherwise. Two-group comparisons will use independent-samples t-test or Mann-Whitney U test as appropriate.', 'Multivariable linear regression will evaluate independent associations with total percentage knowledge score. Candidate predictors will include cadre, age group, experience, prior glaucoma teaching, and prior clinical exposure. Regression assumptions will be checked and 95% confidence intervals reported.', 'All tests will be two-sided; p<0.05 will be considered statistically significant. Analyses will be conducted using Jamovi, R, SPSS, or an equivalent statistical package.']) heading(d,'Ethical considerations',2) bullets(d,['IEC approval will be obtained before recruitment and data collection.', 'Written informed consent will be obtained. Participation will be voluntary and refusal or withdrawal will not affect employment, training, appraisal, or patient care.', 'The study entails no clinical intervention. The expected risk is minimal, limited to inconvenience or fatigue while answering a questionnaire.', 'No payment or direct clinical benefit is promised. Findings may contribute to future educational planning.', 'Any protocol amendment, including a change in sampling, tool, or outcome definition, will be submitted to the IEC/ISRC as required before implementation.']) heading(d,'6. Expected outcomes') add_para(d,'The study is expected to quantify overall and domain-specific glaucoma knowledge, identify differences across cadres, and identify modifiable gaps in risk-factor recognition, diagnostic/referral principles, and long-term management. The findings may inform educational sessions, induction training, and future interventional studies.') heading(d,'7. Dissemination plan') add_para(d,'Findings will be submitted as a dissertation/research report and may be presented at scientific meetings or submitted to a peer-reviewed journal. Reporting will follow the STROBE guidance for cross-sectional studies. No participant-level identifiable information will be published.') heading(d,'8. References') refs=[ 'Tham YC, Li X, Wong TY, Quigley HA, Aung T, Cheng CY. Global prevalence of glaucoma and projections of glaucoma burden through 2040: a systematic review and meta-analysis. Ophthalmology. 2014;121(11):2081-2090. doi:10.1016/j.ophtha.2014.05.013.', 'American Academy of Ophthalmology. Primary Open-Angle Glaucoma Preferred Practice Pattern. 2025. Available from: https://www.aao.org/education/preferred-practice-pattern/primary-open-angle-glaucoma-ppp. Accessed 12 October 2026.', 'American Academy of Ophthalmology. Primary Angle-Closure Disease Preferred Practice Pattern. 2025. Available from: https://www.aao.org/education/preferred-practice-pattern/primary-angle-closure-disease-ppp. Accessed 12 October 2026.', 'Yimam W, Anbesaw T, Seid M. Knowledge about glaucoma among adults in Africa: a systematic review. BMC Ophthalmol. 2024. PMID: 38355482.', 'Indian Council of Medical Research. National Ethical Guidelines for Biomedical and Health Research Involving Human Participants. New Delhi: ICMR; 2017. Available from: https://www.icmr.gov.in/icmrobject/custom_data/pdf/resource-guidelines/ethical_guidelines.pdf.', 'von Elm E, Altman DG, Egger M, et al. The Strengthening the Reporting of Observational Studies in Epidemiology (STROBE) Statement: guidelines for reporting observational studies. PLoS Med. 2007;4(10):e296. doi:10.1371/journal.pmed.0040296.' ] for r in refs: d.add_paragraph(r,style='List Number') heading(d,'9. Investigator declaration') add_para(d,'The investigators declare that this protocol contains no fabricated data and will be conducted only after required institutional approvals. Any substantive methodological modification will be submitted to the appropriate institutional committee before implementation.') d.add_paragraph('\n\nPrincipal Investigator: ___________________________ Date: ______________') d.add_paragraph('Guide / Supervisor: ______________________________ Date: ______________') d.save(OUT/'Glaucoma_Study_Synopsis.docx') def make_questionnaire(): d=Document();setup(d);title(d,'ANNEXURE: QUESTIONNAIRE','Knowledge of Glaucoma Among Healthcare Providers at Government Medical College, Datia') p=d.add_paragraph();p.alignment=WD_ALIGN_PARAGRAPH.CENTER; r=p.add_run('Study ID: ____________________ Date: ____________________');r.bold=True heading(d,'Participant instructions',1) add_para(d,'This questionnaire is anonymous. Do not write your name, phone number, employee number, or any other direct identifier. Please answer based on your present knowledge. For each statement, mark only one response: True, False, or Don’t know. The questionnaire takes approximately 10-15 minutes. Your responses will be used only for research and reported in aggregate.') heading(d,'Section A: Participant characteristics',1) t=d.add_table(rows=0,cols=2);t.alignment=WD_TABLE_ALIGNMENT.CENTER;t.style='Table Grid' qs=[('A1. Age group','□ 18-24 years □ 25-34 years □ 35-44 years □ 45 years or above'),('A2. Gender','□ Female □ Male □ Other □ Prefer not to say'),('A3. Professional cadre','□ Faculty / medical officer □ PG resident / JR □ MBBS intern □ Staff nurse'),('A4. Department / unit','____________________________________________'),('A5. Years of clinical experience','□ <1 year □ 1-5 years □ 6-10 years □ >10 years'),('A6. Formal teaching/training on glaucoma in the last 2 years','□ Yes □ No □ Not sure'),('A7. Ever referred, managed, or assisted in care of a patient with glaucoma','□ Yes □ No □ Not sure')] for i,(q,a) in enumerate(qs): c=t.add_row().cells;set_cell_text(c[0],q,True,9);set_cell_text(c[1],a,False,9);shade(c[0],LIGHT if i%2==0 else PALE) heading(d,'Section B: Glaucoma knowledge',1) p=d.add_paragraph(); p.add_run('For each statement, tick one box only. ').bold=True; p.add_run('Do not leave an item blank. If you are unsure, choose “Don’t know”.') items=[ ('Disease concepts','Glaucoma is a group of diseases that can damage the optic nerve.'),('Disease concepts','Glaucoma can cause irreversible loss of vision.'),('Disease concepts','Most people with early primary open-angle glaucoma have obvious symptoms.'),('Disease concepts','Glaucoma occurs only in people older than 60 years.'),('Disease concepts','A person may have glaucoma without being aware of it until vision is significantly affected.'),('Disease concepts','Vision already lost due to glaucoma can usually be restored by eye drops.'), ('Risk factors','A family history of glaucoma increases a person’s risk.'),('Risk factors','Increasing age is a risk factor for glaucoma.'),('Risk factors','Long-term corticosteroid use can raise intraocular pressure and may cause glaucoma.'),('Risk factors','Diabetes mellitus is associated with increased risk of primary open-angle glaucoma.'),('Risk factors','Myopia is a risk factor for primary open-angle glaucoma.'),('Risk factors','Every person with raised intraocular pressure has glaucoma.'), ('Diagnosis and referral','A person can have glaucoma even when intraocular pressure is within the statistically normal range.'),('Diagnosis and referral','Measurement of intraocular pressure alone is sufficient to diagnose glaucoma.'),('Diagnosis and referral','Examination of the optic nerve is important in evaluating suspected glaucoma.'),('Diagnosis and referral','Visual-field testing can detect functional loss caused by glaucoma.'),('Diagnosis and referral','Gonioscopy helps evaluate the anterior chamber angle.'),('Diagnosis and referral','Patients with suspicious optic-nerve findings should be referred for ophthalmic assessment even if they have no symptoms.'),('Diagnosis and referral','Acute angle-closure glaucoma may present with severe eye pain, redness, blurred vision, headache, nausea, or vomiting.'),('Diagnosis and referral','Acute angle-closure glaucoma requires urgent ophthalmic referral.'), ('Treatment and follow-up','Eye drops are commonly used to lower intraocular pressure in glaucoma.'),('Treatment and follow-up','Laser treatment may be used in selected forms of glaucoma.'),('Treatment and follow-up','Surgery may be needed when glaucoma is not adequately controlled with medication or laser treatment.'),('Treatment and follow-up','Regular use of prescribed treatment and follow-up are important to prevent further visual loss.'),('Treatment and follow-up','Lifestyle modification alone can replace prescribed glaucoma treatment.')] t=d.add_table(rows=1,cols=5);t.alignment=WD_TABLE_ALIGNMENT.CENTER;t.style='Table Grid';h=t.rows[0].cells for cell,txt,w in zip(h,['No.','Statement','True','False','Don’t know'],[.5,4.8,.55,.55,.8]): set_cell_text(cell,txt,True,8,WD_ALIGN_PARAGRAPH.CENTER);shade(cell,NAVY) for cell in h: for r in cell.paragraphs[0].runs:r.font.color.rgb=__import__('docx').shared.RGBColor.from_string('FFFFFF') set_repeat_table_header(t.rows[0]) for n,(domain,statement) in enumerate(items,1): c=t.add_row().cells;set_cell_text(c[0],str(n),False,9,WD_ALIGN_PARAGRAPH.CENTER);set_cell_text(c[1],statement,False,9);set_cell_text(c[2],'□',False,12,WD_ALIGN_PARAGRAPH.CENTER);set_cell_text(c[3],'□',False,12,WD_ALIGN_PARAGRAPH.CENTER);set_cell_text(c[4],'□',False,12,WD_ALIGN_PARAGRAPH.CENTER) if n%2: [shade(x,'F7FBFF') for x in c] heading(d,'For research team use only - scoring key',1) add_para(d,'Do not provide this page to participants. Score 1 for a correct response and 0 for an incorrect or Don’t know response. Total score: ____ / 25. Percentage score: ____ %. The primary analysis uses the continuous percentage score.') key=['T','T','F','F','T','F','T','T','T','T','T','F','T','F','T','T','T','T','T','T','T','T','T','T','F'] tk=d.add_table(rows=1,cols=5);tk.style='Table Grid'; tk.alignment=WD_TABLE_ALIGNMENT.CENTER for c,txt in zip(tk.rows[0].cells,['Domain','Items','Maximum score','Correct responses','Notes']):set_cell_text(c,txt,True,8,WD_ALIGN_PARAGRAPH.CENTER);shade(c,NAVY);[setattr(r.font.color,'rgb',__import__('docx').shared.RGBColor.from_string('FFFFFF')) for r in c.paragraphs[0].runs] domains=[('Disease concepts','1-6',6,','.join(key[0:6])),('Risk factors','7-12',6,','.join(key[6:12])),('Diagnosis and referral','13-20',8,','.join(key[12:20])),('Treatment and follow-up','21-25',5,','.join(key[20:25]))] for a,b,c1,e in domains: row=tk.add_row().cells for cell,txt in zip(row,[a,b,str(c1),e,'T=True; F=False']):set_cell_text(cell,txt,False,8) d.add_paragraph('Operational descriptive category (optional): adequate knowledge ≥70% (≥18/25); inadequate knowledge <70%. This category does not replace analysis of the continuous score.') d.save(OUT/'Glaucoma_Knowledge_Questionnaire.docx') make_synopsis();make_questionnaire() print('Created:', *(str(p) for p in OUT.glob('*.docx')), sep='\n') PY python3 "/tmp/workspace/glaucoma-study-package/src/create_documents.py" python /tmp/skills/docx/scripts/office/validate.py "/tmp/workspace/glaucoma-study-package/output/Glaucoma_Study_Synopsis.docx" python /tmp/skills/docx/scripts/office/validate.py "/tmp/workspace/glaucoma-study-package/output/Glaucoma_Knowledge_Questionnaire.docx"

~/glaucoma-study-package/output

~/glaucoma-study-package/src/create_documents.py

python3 "/tmp/workspace/glaucoma-study-package/src/create_documents.py" && python3 - <<'PY' from pathlib import Path import zipfile, tempfile, shutil out=Path('/tmp/workspace/glaucoma-study-package/output') for path in out.glob('*.docx'): tmp=path.with_suffix('.fixed.docx') with zipfile.ZipFile(path,'r') as zin, zipfile.ZipFile(tmp,'w',zipfile.ZIP_DEFLATED) as zout: for info in zin.infolist(): data=zin.read(info.filename) if info.filename=='word/settings.xml': data=data.replace(b'<w:zoom/>',b'<w:zoom w:percent="100"/>') data=data.replace(b'<w:zoom />',b'<w:zoom w:percent="100"/>') zout.writestr(info,data) tmp.replace(path) PY python /tmp/skills/docx/scripts/office/validate.py "/tmp/workspace/glaucoma-study-package/output/Glaucoma_Study_Synopsis.docx"; python /tmp/skills/docx/scripts/office/validate.py "/tmp/workspace/glaucoma-study-package/output/Glaucoma_Knowledge_Questionnaire.docx"

~/glaucoma-study-package/src/create_documents.py

python3 "/tmp/workspace/glaucoma-study-package/src/create_documents.py" && python3 - <<'PY' from pathlib import Path import zipfile for p in Path('/tmp/workspace/glaucoma-study-package/output').glob('*.docx'): with zipfile.ZipFile(p) as z: text=z.read('word/settings.xml').decode('utf-8') start=text.find('<w:zoom') print(p.name, text[start:start+80]) PY

python3 - <<'PY' from pathlib import Path import zipfile out=Path('/tmp/workspace/glaucoma-study-package/output') for path in out.glob('*.docx'): tmp=path.with_suffix('.tmp.docx') with zipfile.ZipFile(path,'r') as zin, zipfile.ZipFile(tmp,'w',zipfile.ZIP_DEFLATED) as zout: for info in zin.infolist(): data=zin.read(info.filename) if info.filename=='word/settings.xml': data=data.replace(b'<w:zoom w:val="bestFit"/>', b'<w:zoom w:val="bestFit" w:percent="100"/>') zout.writestr(info, data) tmp.replace(path) PY python /tmp/skills/docx/scripts/office/validate.py "/tmp/workspace/glaucoma-study-package/output/Glaucoma_Study_Synopsis.docx"; python /tmp/skills/docx/scripts/office/validate.py "/tmp/workspace/glaucoma-study-package/output/Glaucoma_Knowledge_Questionnaire.docx"

Excel data validation formulas documentation list validation official Microsoft

~/

~/glaucoma-study-package

cat > "/tmp/workspace/glaucoma-study-package/src/create_excel.py" <<'PY' from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Border, Side, Alignment from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.formatting.rule import FormulaRule from openpyxl.worksheet.table import Table, TableStyleInfo from openpyxl.utils import get_column_letter from openpyxl.comments import Comment from openpyxl.workbook.properties import CalcProperties from pathlib import Path OUT=Path('/tmp/workspace/glaucoma-study-package/output/Glaucoma_Data_Collection_Automatic_Scoring.xlsx') MAXROW=502 # rows 3 to 502 = 500 participants navy='17365D'; blue='1F4E78'; light='D9EAF7'; pale='EEF5FB'; yellow='FFF2CC'; green='E2F0D9'; red='FCE4D6'; gray='666666'; white='FFFFFF' thin=Side(style='thin',color='B7C9D6') wb=Workbook(); ws=wb.active; ws.title='Data Entry' wb.calculation=CalcProperties(calcMode='auto', fullCalcOnLoad=True, forceFullCalc=True) for sheet in ['Read Me','Dashboard','Codebook']: wb.create_sheet(sheet) readme=wb['Read Me']; dash=wb['Dashboard']; code=wb['Codebook'] def style_title(ws, title, sub, end_col=10): ws.merge_cells(start_row=1,start_column=1,end_row=1,end_column=end_col) c=ws.cell(1,1,title);c.font=Font(name='Arial',size=16,bold=True,color=white);c.fill=PatternFill('solid',fgColor=navy);c.alignment=Alignment(horizontal='center',vertical='center') ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=end_col) c=ws.cell(2,1,sub);c.font=Font(name='Arial',size=10,italic=True,color='404040');c.alignment=Alignment(wrap_text=True) ws.row_dimensions[1].height=28; ws.row_dimensions[2].height=32 def header(c): c.font=Font(name='Arial',size=9,bold=True,color=white);c.fill=PatternFill('solid',fgColor=blue);c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True);c.border=Border(left=thin,right=thin,top=thin,bottom=thin) def data_cell(c): c.font=Font(name='Arial',size=9);c.alignment=Alignment(vertical='center',wrap_text=True);c.border=Border(left=thin,right=thin,top=thin,bottom=thin) # Read Me style_title(readme,'Glaucoma Study Data-Collection Workbook','Knowledge of Glaucoma Among Healthcare Providers at Government Medical College, Datia',8) readme.column_dimensions['A'].width=24; readme.column_dimensions['B'].width=105 instructions=[ ('Purpose','Enter one anonymised participant per row in the Data Entry sheet. The workbook automatically calculates item scores, domain scores, total score, percentage score, completeness, and an optional operational category.'), ('Do not enter identifiers','Do NOT enter participant name, phone number, employee number, or a signed-consent reference. Use only the anonymous Study ID assigned to the questionnaire.'), ('Data entry order','Enter participant details in columns A-I. Enter questionnaire responses exactly as True, False, or Don’t know in columns J-AH. Drop-down lists are provided.'), ('Automatic scoring','Correct answers score 1. Incorrect and Don’t know responses score 0. The sheet calculates total score out of 25 and percentage score automatically when opened in Excel.'), ('Missing responses','Blank knowledge responses remain blank in individual score cells. The total score treats a blank as 0. “Complete?” is Yes only if all 25 knowledge items have a response. “Eligible for main analysis?” is Yes if missing knowledge responses are 5 or fewer (20% or less), according to the protocol.'), ('Quality checks','Check the red warning cells before analysis. Do not overwrite formula columns AI onwards. The dashboard is a live monitoring summary, not a substitute for formal statistical analysis.'), ('Recommended process','Keep signed consent forms separate from this workbook. Maintain any recruitment log separately and securely. Protect this file with institutional access controls and store it in an approved secure location.'), ('Before analysis','Freeze data entry, audit ranges and outliers, document exclusions, and export a de-identified analysis dataset. Analyse the continuous percentage score as the primary outcome.')] for r,(a,b) in enumerate(instructions,4): readme.cell(r,1,a);readme.cell(r,2,b) for c in (readme.cell(r,1),readme.cell(r,2)): c.font=Font(name='Arial',size=10,bold=(c.column==1));c.alignment=Alignment(wrap_text=True,vertical='top');c.border=Border(left=thin,right=thin,top=thin,bottom=thin) readme.cell(r,1).fill=PatternFill('solid',fgColor=light if r%2==0 else pale);readme.row_dimensions[r].height=42 readme.freeze_panes='A4' # Codebook style_title(code,'Codebook and Scoring Key','Use this sheet to understand variables and correct answers. Do not change scoring key after data collection begins.',7) code_headers=['Field','Variable / response coding','Type','Allowed entries','Scoring / notes','Correct response','Domain'] for i,h in enumerate(code_headers,1): header(code.cell(4,i,h)) code.column_dimensions['A'].width=20;code.column_dimensions['B'].width=40;code.column_dimensions['C'].width=14;code.column_dimensions['D'].width=36;code.column_dimensions['E'].width=40;code.column_dimensions['F'].width=18;code.column_dimensions['G'].width=24 rows=[ ('Study_ID','Anonymous participant ID','Text','e.g., GLAU-001','Unique, no names','',''),('Date_Collected','Date questionnaire completed','Date','Excel date','Required for record tracking','',''),('Age_Group','Age group','Categorical','18-24 years; 25-34 years; 35-44 years; 45 years or above','Participant characteristic','',''),('Gender','Gender','Categorical','Female; Male; Other; Prefer not to say','Participant characteristic','',''),('Cadre','Professional cadre','Categorical','Faculty / medical officer; PG resident / JR; MBBS intern; Staff nurse','Primary comparison group','',''),('Department_Unit','Department / unit','Text','Free text','Avoid identifiers','',''),('Experience','Years of clinical experience','Categorical','<1 year; 1-5 years; 6-10 years; >10 years','Participant characteristic','',''),('Training_2y','Formal glaucoma teaching in past 2 years','Categorical','Yes; No; Not sure','Predictor','',''),('Clinical_Exposure','Ever referred/managed/assisted with glaucoma care','Categorical','Yes; No; Not sure','Predictor','','')] questions=[ ('Q1','Glaucoma is a group of diseases that can damage the optic nerve.','True','Disease concepts'),('Q2','Glaucoma can cause irreversible loss of vision.','True','Disease concepts'),('Q3','Most people with early primary open-angle glaucoma have obvious symptoms.','False','Disease concepts'),('Q4','Glaucoma occurs only in people older than 60 years.','False','Disease concepts'),('Q5','A person may have glaucoma without being aware of it until vision is significantly affected.','True','Disease concepts'),('Q6','Vision already lost due to glaucoma can usually be restored by eye drops.','False','Disease concepts'),('Q7','A family history of glaucoma increases a person’s risk.','True','Risk factors'),('Q8','Increasing age is a risk factor for glaucoma.','True','Risk factors'),('Q9','Long-term corticosteroid use can raise intraocular pressure and may cause glaucoma.','True','Risk factors'),('Q10','Diabetes mellitus is associated with increased risk of primary open-angle glaucoma.','True','Risk factors'),('Q11','Myopia is a risk factor for primary open-angle glaucoma.','True','Risk factors'),('Q12','Every person with raised intraocular pressure has glaucoma.','False','Risk factors'),('Q13','A person can have glaucoma even when intraocular pressure is within the statistically normal range.','True','Diagnosis and referral'),('Q14','Measurement of intraocular pressure alone is sufficient to diagnose glaucoma.','False','Diagnosis and referral'),('Q15','Examination of the optic nerve is important in evaluating suspected glaucoma.','True','Diagnosis and referral'),('Q16','Visual-field testing can detect functional loss caused by glaucoma.','True','Diagnosis and referral'),('Q17','Gonioscopy helps evaluate the anterior chamber angle.','True','Diagnosis and referral'),('Q18','Patients with suspicious optic-nerve findings should be referred for ophthalmic assessment even if they have no symptoms.','True','Diagnosis and referral'),('Q19','Acute angle-closure glaucoma may present with severe eye pain, redness, blurred vision, headache, nausea, or vomiting.','True','Diagnosis and referral'),('Q20','Acute angle-closure glaucoma requires urgent ophthalmic referral.','True','Diagnosis and referral'),('Q21','Eye drops are commonly used to lower intraocular pressure in glaucoma.','True','Treatment and follow-up'),('Q22','Laser treatment may be used in selected forms of glaucoma.','True','Treatment and follow-up'),('Q23','Surgery may be needed when glaucoma is not adequately controlled with medication or laser treatment.','True','Treatment and follow-up'),('Q24','Regular use of prescribed treatment and follow-up are important to prevent further visual loss.','True','Treatment and follow-up'),('Q25','Lifestyle modification alone can replace prescribed glaucoma treatment.','False','Treatment and follow-up')] for field,desc,typ,allowed,note,correct,domain in rows: code.append([field,desc,typ,allowed,note,correct,domain]) for q,text,correct,domain in questions: code.append([q,text,'Categorical','True; False; Don’t know','Correct=1; Incorrect/Don’t know=0',correct,domain]) for row in code.iter_rows(min_row=5,max_row=code.max_row,min_col=1,max_col=7): for c in row: data_cell(c) if row[0].row%2: for c in row:c.fill=PatternFill('solid',fgColor='F7FBFF') code.freeze_panes='A5';code.auto_filter.ref=f'A4:G{code.max_row}' # Data Entry headers=['Study_ID','Date_Collected','Age_Group','Gender','Cadre','Department_Unit','Experience','Training_2y','Clinical_Exposure']+[f'Q{i}' for i in range(1,26)]+[f'Q{i}_Score' for i in range(1,26)]+['Disease_Concepts_Score','Risk_Factors_Score','Diagnosis_Referral_Score','Treatment_Followup_Score','Total_Score_25','Percentage_Score','Missing_Knowledge_Items','Complete?','Eligible_for_Main_Analysis?','Operational_Category'] style_title(ws,'DATA ENTRY - GLAUCOMA KNOWLEDGE STUDY','Enter only de-identified data. Yellow columns are for entry. Formula columns begin at AI and should not be overwritten.',len(headers)) for i,h in enumerate(headers,1): header(ws.cell(3,i,h)) ws.row_dimensions[3].height=44 for r in range(4,MAXROW+1): for col in range(1,len(headers)+1): data_cell(ws.cell(r,col)) # data-entry fields for col in range(1,35): ws.cell(r,col).fill=PatternFill('solid',fgColor=yellow) # formulas: answers in J:AH, scores AI:BG for q in range(25): ans_col=get_column_letter(10+q); score_col=get_column_letter(35+q) ws[f'{score_col}{r}']=f'=IF({ans_col}{r}="","",IF({ans_col}{r}=Codebook!$F${14+q},1,0))' # Domain scores BH:BK, total BL, percentage BM, missing BN, complete BO eligible BP category BQ ws[f'BH{r}']=f'=IF($A{r}="","",SUM(AI{r}:AN{r}))' ws[f'BI{r}']=f'=IF($A{r}="","",SUM(AO{r}:AT{r}))' ws[f'BJ{r}']=f'=IF($A{r}="","",SUM(AU{r}:BB{r}))' ws[f'BK{r}']=f'=IF($A{r}="","",SUM(BC{r}:BG{r}))' ws[f'BL{r}']=f'=IF($A{r}="","",SUM(AI{r}:BG{r}))' ws[f'BM{r}']=f'=IF($A{r}="","",IFERROR(BL{r}/25,""))' ws[f'BN{r}']=f'=IF($A{r}="","",COUNTBLANK(J{r}:AH{r}))' ws[f'BO{r}']=f'=IF($A{r}="","",IF(BN{r}=0,"Yes","No"))' ws[f'BP{r}']=f'=IF($A{r}="","",IF(BN{r}<=5,"Yes","No"))' ws[f'BQ{r}']=f'=IF($A{r}="","",IF(BM{r}>=0.7,"Adequate (≥70%)","Inadequate (<70%)"))' for col in range(35,len(headers)+1): ws.cell(r,col).fill=PatternFill('solid',fgColor=green) # widths widths={1:15,2:14,3:17,4:18,5:23,6:23,7:20,8:17,9:19} for c in range(10,35): widths[c]=10 for c in range(35,60): widths[c]=10 for c in range(60,70): widths[c]=18 for i,w in widths.items():ws.column_dimensions[get_column_letter(i)].width=w ws.freeze_panes='J4';ws.auto_filter.ref=f'A3:BQ{MAXROW}' ws.sheet_view.showGridLines=False # table tab=Table(displayName='GlaucomaData',ref=f'A3:BQ{MAXROW}');tab.tableStyleInfo=TableStyleInfo(name='TableStyleMedium2',showRowStripes=True,showColumnStripes=False);ws.add_table(tab) # validations lists={'age':'"18-24 years,25-34 years,35-44 years,45 years or above"','gender':'"Female,Male,Other,Prefer not to say"','cadre':'"Faculty / medical officer,PG resident / JR,MBBS intern,Staff nurse"','exp':'"<1 year,1-5 years,6-10 years,>10 years"','yesno':'"Yes,No,Not sure"','answer':'"True,False,Don’t know"'} def dv(formula, rng): x=DataValidation(type='list',formula1=formula,allow_blank=True);x.error='Select a value from the drop-down list.';x.errorTitle='Invalid entry';x.prompt='Choose from the listed options.';x.promptTitle='Data entry'; ws.add_data_validation(x);x.add(rng) dv(lists['age'],f'C4:C{MAXROW}');dv(lists['gender'],f'D4:D{MAXROW}');dv(lists['cadre'],f'E4:E{MAXROW}');dv(lists['exp'],f'G4:G{MAXROW}');dv(lists['yesno'],f'H4:I{MAXROW}');dv(lists['answer'],f'J4:AH{MAXROW}') date_dv=DataValidation(type='date',operator='between',formula1='DATE(2025,1,1)',formula2='DATE(2035,12,31)',allow_blank=True);date_dv.error='Enter a valid study date between 2025 and 2035.';ws.add_data_validation(date_dv);date_dv.add(f'B4:B{MAXROW}') # formats, quality warnings for r in range(4,MAXROW+1): ws[f'B{r}'].number_format='dd-mmm-yyyy';ws[f'BM{r}'].number_format='0.0%' ws.conditional_formatting.add(f'A4:A{MAXROW}',FormulaRule(formula=['AND(A4<>"",COUNTIF($A$4:$A$502,A4)>1)'],fill=PatternFill('solid',fgColor=red))) ws.conditional_formatting.add(f'BN4:BN{MAXROW}',FormulaRule(formula=['BN4>5'],fill=PatternFill('solid',fgColor=red))) ws.conditional_formatting.add(f'BO4:BO{MAXROW}',FormulaRule(formula=['BO4="No"'],fill=PatternFill('solid',fgColor=red))) ws.conditional_formatting.add(f'BP4:BP{MAXROW}',FormulaRule(formula=['BP4="No"'],fill=PatternFill('solid',fgColor=red))) # comments ws['A3'].comment=Comment('Use unique anonymous IDs, e.g. GLAU-001. Duplicate IDs are highlighted red.','Orris') ws['AI3'].comment=Comment('Automatic formula column. Do not type over this cell.','Orris') ws['BQ3'].comment=Comment('Operational descriptive category only. Primary analysis should use the continuous Percentage_Score.','Orris') # Dashboard style_title(dash,'LIVE DASHBOARD','Automatically updates when the workbook is opened in Excel. Formula results will calculate on opening.',8) dash.column_dimensions['A'].width=34;dash.column_dimensions['B'].width=18;dash.column_dimensions['D'].width=32;dash.column_dimensions['E'].width=18;dash.column_dimensions['G'].width=34;dash.column_dimensions['H'].width=18 for cell,label,formula in [('A4','Records with Study ID','=COUNTA(\'Data Entry\'!A4:A502)'),('A5','Complete questionnaires','=COUNTIF(\'Data Entry\'!BO4:BO502,"Yes")'),('A6','Eligible for main analysis','=COUNTIF(\'Data Entry\'!BP4:BP502,"Yes")'),('A7','Mean percentage score','=IFERROR(AVERAGEIF(\'Data Entry\'!BP4:BP502,"Yes",\'Data Entry\'!BM4:BM502),"")'),('A8','Adequate knowledge (≥70%)','=COUNTIFS(\'Data Entry\'!BP4:BP502,"Yes",\'Data Entry\'!BQ4:BQ502,"Adequate (≥70%)")'),('A9','Incomplete / >5 missing','=COUNTIF(\'Data Entry\'!BP4:BP502,"No")')]: dash[cell]=label;dash[cell].font=Font(name='Arial',size=10,bold=True);dash[cell].fill=PatternFill('solid',fgColor=light);dash[cell].border=Border(left=thin,right=thin,top=thin,bottom=thin) v=dash.cell(dash[cell].row,2,formula);v.font=Font(name='Arial',size=11,bold=True);v.fill=PatternFill('solid',fgColor=green);v.border=Border(left=thin,right=thin,top=thin,bottom=thin) dash['B7'].number_format='0.0%' # cadre summary for i,h in enumerate(['Cadre','N eligible','Mean % score','Adequate n','Adequate %'],4): header(dash.cell(12,i,h)) cadres=['Faculty / medical officer','PG resident / JR','MBBS intern','Staff nurse'] for r,cadre in enumerate(cadres,13): dash.cell(r,4,cadre) dash.cell(r,5,f'=COUNTIFS(\'Data Entry\'!$E$4:$E$502,D{r},\'Data Entry\'!$BP$4:$BP$502,"Yes")') dash.cell(r,6,f'=IFERROR(AVERAGEIFS(\'Data Entry\'!$BM$4:$BM$502,\'Data Entry\'!$E$4:$E$502,D{r},\'Data Entry\'!$BP$4:$BP$502,"Yes"),"")') dash.cell(r,7,f'=COUNTIFS(\'Data Entry\'!$E$4:$E$502,D{r},\'Data Entry\'!$BP$4:$BP$502,"Yes",\'Data Entry\'!$BQ$4:$BQ$502,"Adequate (≥70%)")') dash.cell(r,8,f'=IFERROR(G{r}/E{r},"")') for c in range(4,9): data_cell(dash.cell(r,c)); dash.cell(r,6).number_format='0.0%';dash.cell(r,8).number_format='0.0%' # domains for i,h in enumerate(['Knowledge domain','Maximum','Mean raw score','Mean %'],1): header(dash.cell(12,i,h)) domains=[('Disease concepts',6,'BH'),('Risk factors',6,'BI'),('Diagnosis and referral',8,'BJ'),('Treatment and follow-up',5,'BK')] for r,(name,maxv,col) in enumerate(domains,13): dash.cell(r,1,name);dash.cell(r,2,maxv);dash.cell(r,3,f'=IFERROR(AVERAGEIF(\'Data Entry\'!$BP$4:$BP$502,"Yes",\'Data Entry\'!${col}$4:${col}$502),"")');dash.cell(r,4,f'=IFERROR(C{r}/B{r},"")') for c in range(1,5):data_cell(dash.cell(r,c)) dash.cell(r,4).number_format='0.0%' dash['A19']='Dashboard notes';dash['A19'].font=Font(name='Arial',bold=True,color=white);dash['A19'].fill=PatternFill('solid',fgColor=blue) dash.merge_cells('A20:H22');dash['A20']='The dashboard excludes questionnaires with more than 5 missing knowledge responses from eligible analyses. Use the Data Entry sheet for audit checks and export a de-identified data set for formal statistical analysis.';dash['A20'].alignment=Alignment(wrap_text=True,vertical='top');dash['A20'].font=Font(name='Arial',size=10) dash.freeze_panes='A4';dash.sheet_view.showGridLines=False # tables and print setup for s in [ws,code,readme,dash]: s.sheet_properties.pageSetUpPr.fitToPage=True s.page_setup.fitToWidth=1;s.page_setup.fitToHeight=0 s.page_margins.left=0.25;s.page_margins.right=0.25;s.page_margins.top=0.5;s.page_margins.bottom=0.5 # protection optional not enabled, so investigator can edit if necessary wb.save(OUT) # verify formula cells expected and formula types check=load_workbook(OUT,data_only=False) assert check['Data Entry']['AI4'].value.startswith('=IF(') assert check['Data Entry']['BL4'].value.startswith('=IF(') assert check['Dashboard']['B7'].value.startswith('=IFERROR(') assert len(check['Data Entry'].data_validations.dataValidation)==7 print(OUT) PY python3 "/tmp/workspace/glaucoma-study-package/src/create_excel.py"

~/glaucoma-study-package/src/create_excel.py

python3 - <<'PY' p='/tmp/workspace/glaucoma-study-package/src/create_excel.py' s=open(p,encoding='utf-8').read().replace('502','503') open(p,'w',encoding='utf-8').write(s) PY python3 "/tmp/workspace/glaucoma-study-package/src/create_excel.py" && python3 - <<'PY' from openpyxl import load_workbook p='/tmp/workspace/glaucoma-study-package/output/Glaucoma_Data_Collection_Automatic_Scoring.xlsx' wb=load_workbook(p,data_only=False) ws=wb['Data Entry'] print('sheets:',wb.sheetnames) print('rows:',ws.max_row,'cols:',ws.max_column) print('data table:',ws.tables['GlaucomaData'].ref) print('formula:',ws['BL4'].value) print('last formula:',ws['BQ503'].value) print('validations:',len(ws.data_validations.dataValidation)) print('dashboard:',wb['Dashboard']['B7'].value) assert ws.max_row==503 and ws.tables['GlaucomaData'].ref=='A3:BQ503' assert ws['BL4'].value == '=IF($A4="","",SUM(AI4:BG4))' assert '503' in wb['Dashboard']['B7'].value print('Workbook structural verification passed.') PY