Excel shared formula expansion

Question

To read Excel files, I'm using the OpenXML libraries from C#.

The ability to display the precise formula for each cell that contains one is one of my criteria. "Shared formulas" are used in the OpenXML encoded file to save file size.

Like this:

D3 : <x:f t="shared" ref="D3:D6" si="1" >D2+C3</x:f><x:v >130</x:v>
D4 : <x:f t="shared" si="1"  /><x:v >136</x:v>
D5 : <x:f t="shared" si="1"  /><x:v >141</x:v>
D6 : <x:f t="shared" si="1"  /><x:v >147</x:v>

In the aforementioned example, the root formula (D2+C3) is rather straightforward, however, they can clearly be arbitrarily complex.

If there is a library or example code that can accept any lower cell (for example, D4,D5,D6) and return the "unshared" formula, that is what I want to know.

Answer 1

You can use Linq

I would start by looking for any cells with the properties v:f.Value > "" and v:f.@t = "shared." I would then perform a. TakeWhile of the.ElementsAfterSelf with values "" and "shared" for v:f.Value and v:f.@t, respectively.

Once I get that IEnumerable(Of XElement), I would use a parser to construct a new formula for the first XElement before doing a For Each to increment the cell's relative value for each XElement (s).