feature request: circular formulas - how I did it in openpyxl 3.0.9
Hi.
I added support for circular formulas in Openpyxl 3.0.9 for my own use, and the solution was much easier than this discussion suggested. It required a minor change to the etree_write_cell function in cell/_writer.py, which seems similar to the handling of datatable formulas in recent versions. The modification has no impact on other formula types in v3.0.9, and I think that's feasible but not automatic now. The output is read correctly by Excel in Windows and LibreOffice in Linux.
3.0.9 | modified |
---|---|
|
|
Adding a circular formula:
range_label = f"..."
formula = f"=MAX(0,100-SUMPRODUCT({range_label} * NOT(_xlfn.ISFORMULA({range_label}))))/MAX(1,SUMPRODUCT(1 * _xlfn.ISFORMULA({range_label})))"
ws[cell_id] = formula
ws.formula_attributes[cell_id] = {"value": str(arbitrary_number)}
Thanks.