excel shared formula expansion

Question

Hi. I am using the OpenXML libraries from C# to read Excel files.

One of my requirements is to be able to show the exact formula for each cell that has one. The OpenXML encoded file uses "shared formulas" to reduce 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>

Its a fairly simple root formula in the example above (D2+C3) and obviously these can be arbitrarily complex.

What I want to know is if there is a library or example code available that takes any lower cell (e.g. D4,D5,D6) and return the "unshared" formula?

e.g. for D6 this would return "D5+C6"

Answer 1

Not easily, but it can be done. What I would do is use Linq.

First I would find any cells where <v:f>.Value <> "" and <v:f>.@t = "shared". Then I would do a .TakeWhile of the .ElementsAfterSelf where <v:f>.Value = "" and <v:f>.@t = "shared".

Once I have that IEnumerable(Of XElement), I would then use a parser for the formula on the first one¹ and then do a For Each to create a new formula for each XElement, incrementing the cell's relative value(s).

¹_{The recent series by Eric White on Writing a Recursive Descent Parser using C# and LINQ is probably the best I've seen.}