Set the decision and its input
The synthetic workbook has A in A1, comment text Discussed and a list-validation definition A/B applying to A1. The programme creates XLSX in memory and reloads it with OpenPyxl3.1.5, retaining value, comment text, rule type, formula and range. Value-only CSV retains A but neither feature.
| Case | Input JSON |
|---|---|
| Base test | {"format":"xlsx"} |
Run the supplied comparison
This is actual generated/reloaded artefact evidence. It concerns ordinary comment text and stored validation metadata, not threaded comments, formatting preservation or enforcement by a spreadsheet application. OpenPyxl does not evaluate the validator in this test.
| Path | Output JSON |
|---|---|
| Faulty path | {"value":"A","commentText":null,"validation":null} |
| Required path | {"value":"A","commentText":"Discussed","validation":{"type":"list","formula":"\"A,B\"","range":"A1"}} |
import assert from 'node:assert/strict';
import {spawnSync} from 'node:child_process';
const run=function run(i,corrected=true){
if(!['xlsx','csv'].includes(i.format))return {held:true};
const python=`import io,json,csv,sys
from openpyxl import Workbook,load_workbook
from openpyxl.comments import Comment
from openpyxl.worksheet.datavalidation import DataValidation
i=json.load(sys.stdin)
w=Workbook();s=w.active;s["A1"]="A";s["A1"].comment=Comment("Discussed","Reviewer")
v=DataValidation(type="list",formula1='"A,B"');s.add_data_validation(v);v.add(s["A1"])
if i["format"]=="xlsx":
b=io.BytesIO();w.save(b);b.seek(0);r=load_workbook(b).active;rule=r.data_validations.dataValidation[0]
out={"value":r["A1"].value,"commentText":r["A1"].comment.text,"validation":{"type":rule.type,"formula":rule.formula1,"range":str(rule.sqref)}}
else:
b=io.StringIO();csv.writer(b).writerow([s["A1"].value]);b.seek(0)
out={"value":next(csv.reader(b))[0],"commentText":None,"validation":None}
print(json.dumps(out))
`;
const result=spawnSync(process.env.PYTHON||'python',['-c',python],{input:JSON.stringify({format:corrected?i.format:'csv'}),encoding:'utf8',windowsHide:true});if(result.status!==0)throw new Error(result.stderr);return JSON.parse(result.stdout);
};
const cases=[{"input":{"format":"xlsx"},"corrected":false,"expected":{"value":"A","commentText":null,"validation":null}},{"input":{"format":"xlsx"},"corrected":true,"expected":{"value":"A","commentText":"Discussed","validation":{"type":"list","formula":"\"A,B\"","range":"A1"}}},{"input":{"format":"csv"},"corrected":true,"expected":{"value":"A","commentText":null,"validation":null}},{"input":{"format":null},"corrected":true,"expected":{"held":true}}];
for(const test of cases){const actual=await run(test.input,test.corrected);assert.deepEqual(actual,test.expected);console.log(actual);}
Choose from the boundary evidence
Explicit CSV selection makes its value-only loss visible rather than claiming all features transferred. Missing format choice is held.
- Name required workbook features
- Generate and reopen the selected format
- Inspect exact retained metadata
Do not infer feature preservation from matching cell values.
| Condition | Input JSON | Expected output JSON |
|---|---|---|
| value-only CSV chosen | {"format":"csv"} | {"value":"A","commentText":null,"validation":null} |
| format not declared | {"format":null} | {"held":true} |
What does the repaired base XLSX round trip retain?
Show the worked answer
A, Discussed and the list rule with formula "A,B" and range A1. That metadata still needs its own receiving-app behaviour test if enforcement is required.