Add a value cache to `cell.Cell`
Created originally on Bitbucket by felipeochoa (Felipe)
Proposed enhancement
It would be great to have a way to populate the "value cache" ( tag in the XML) for cells with formulae. Given that openpyxl won't have the math engine to do so automatically (cf issue #11 (closed)), it would be nice to have a way to interface with the openpyxl OOXML writer from an outside engine.
Such an interface would be helpful even in the absence of a math engine for many simple cases that the programmer could compute directly (E.g., a row of dates implemented as '=RC[-1] + 1', a simple total sum, etc.). The interface could also enable keeping the value cache from a workbook that is read (like in @ericgazoni's original proposal from issue #11 (closed)).
Filling in the value cache for cells with formulae would be helpful if the workbook is going to be opened with applications that don't support computing the result. (Many mobile applications, openpyxl itself pre 1.6, etc.)
Cache-invalidation, propagation, etc. would fall on the programmer, except for perhaps a convenience function to clear the cache globally.
Proposed implementation
The key changes would have to come in lines 303-312 of writer.worksheet
, which handle writing cells with formulae.
My idea is to add a new attribute to cell.Cell
(maybe called value_cache
). Then, when writing the cell contents to the file, if the cell is of type TYPE_FORMULA
the writer checks this value_cache
, and if not None
writes a tag with it.
Optionally, there could also be a workbook.clear_cache_globally
method to set all cell.Cell
s' value_cache
to None
.
Questions
I'm happy to create a pull request for this change but wanted to get some input on it before jumping in. Specifically, does the OOXML schema/spec restrict the values that the tag can assume? If the answer is yes, the writer would have to also support checking the value (The writer could then silently ignoring that cache value in case of failure).