search

PydPiper / pylightxl

A light weight, zero dependency, minimal functionality excel read/writer python library
https://pylightxl.readthedocs.io
MIT License
300 stars 47 forks source link

Fix #84: Reference handling of formulas #85

Open onatyap opened 1 year ago

onatyap commented 1 year ago

Hi @PydPiper, I couldn't see a contribution guide, so I apologize if I made some changes in a non-conventional way. I can change it accordingly if there is any guidance.

This fixed my issue for #84 in the MWE I gave, I ran the tests, and it passed without any errors. Including a test for referenced range formulas is a good idea, and will do it if I find time to do so.

PydPiper commented 1 year ago

@onatyap thanks a lot for giving me a hand on this. I just closed out a few other issues on pylightxl tonight and i've been going at it for the past few hours so ill take a look at this something this week and get back to you!

onatyap commented 1 year ago

No worries @PydPiper; however, this turns out to be a more significant and complex issue than I thought. Excel takes the relative positions of cells referenced in the formula and carries the formula according to the relative position. Therefore, the solution will require the cell with a formula reference in its XML ref attribute (ref="C2:G2") to be parsed, all the cells referenced in the formula to be extracted, and the cell addresses to be changed according to these relative positions.

Therefore, my modification was an easy fix for simple formulas and could be a starting point, but the solution, as I see it, may introduce an overhead that will slow the load process. A workaround could be an additional argument that flags the loader to process and load these formulas, but then there should be a warning about how these types of formulas are not included in (.address(address=address, output='f')).