Excel shared formula expansion

0 votes

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.

Nov 7 in Others by Kithuzzz
• 20,660 points
42 views

1 answer to this question.

0 votes
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).
answered Nov 7 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
210 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
84 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
62 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer

How to create and download excel document using asp.net

First, download the Open XML Format SDK 2.0. It comes ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
172 views
0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
80 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
147 views
0 votes
1 answer

Deleting duplicate rows in Excel using Epplus

You need to re-think this… the while ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
302 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
60 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
68 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP