excel shared formula expansion
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"
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 one1 and then do a For Each
to create a new formula for each XElement, incrementing the cell's relative value(s).
1The recent series by Eric White on Writing a Recursive Descent Parser using C# and LINQ is probably the best I've seen.
精彩评论