It would be great to have a way to populate the "value cache" (<v> tag in the XML) for cells with formulae. Given that openpyxl won't have the math engine to do so automatically (cf issue
#11), 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 @Eric Gazoni's original proposal from issue
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.
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 <v> tag with it.
Optionally, there could also be a
workbook.clear_cache_globally method to set all
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 <v> 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).