We have an enhancement request that would help us more efficiently solve a problem that we’ve been working around for a long time. We use a Custom Calculation Engine to refresh workbooks containing proprietary formulas and we must recalculate the workbook several times so that we can handle batch processing of formula data retrieval. We need to cache formula results so that multiple calls to CalculateFormula are efficient and can be used to “collect” formulas and then “distribute” formula responses on the next recalc. Also due to the purpose of our application it is possible that we will manipulate the workbook in between recalcs (e.g. we may insert or delete rows/columns).
The challenge we have is building a cache key so that we can reliably identify that we’ve seen a specific formula on a previous recalc and have a result in our cache. A common solution is to build a cache key based on the formula’s function name and a serialized copy of the formula arguments. Due to the complexity of our application this only works if we can also tie that key to the cell location. However, the trouble is the cell can move due to workbook manipulation between recalcs and this will invalidate our cache key.
Our solution at the moment is to create a named range for every cell that contains a proprietary formula. Then when processing a recalc we can build a key using the cells namedrange, function name and arguments to lookup in the cache. Prior to saving the workbook we remove all of these temporary names ranges.
This solution works, but it’s a of a misuse of named ranges. During a recalc we can have over 500k cells containing formulas and it can take up to 2 minutes just to remove all of the named ranges. Certainly we would never save a file with these named ranges, they’re only to help the calc process.
Our enhancement request is to add a temporary “Tag” property or object type metadata property to the Cell object. This would allow developers to hide metadata in this property which will carry state with the Cell regardless of changes in the cells location. I recognize that there may not be an OpenXML spec to persist this metadata in the saved Excel file. We’re not asking for this to be persisted or added to the saved file, only in memory to assist with the manipulation and calculation of the workbook where state information needs to be managed and tracked. Ideally the metadata can be looked up directly on the cell (e.g. data.Cell.Metadata ) and also a mechanism to find all cells which contain metadata so that we don’t have to search cell by cell (e.g. workbook.Worksheets.GetCellsContainingMetadat() ).
Thank you for considering this request.