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, 2022 in Others by Kithuzzz
• 27,740 points
78 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, 2022 by narikkadan
• 51,240 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, 2022 in Others by Edureka
• 13,640 points
281 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

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

Excel COUNTIF formula

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

answered Sep 29, 2022 in Others by narikkadan
• 51,240 points
107 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, 2022 in Others by narikkadan
• 51,240 points
93 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, 2022 in Others by narikkadan
• 51,240 points
492 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, 2022 in Others by gaurav
• 22,940 points
112 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, 2022 in Database by gaurav
• 22,940 points
193 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, 2022 in Database by gaurav
• 22,940 points
415 views
0 votes
1 answer

Excel - IF Formula with a FIND

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

answered Sep 27, 2022 in Others by narikkadan
• 51,240 points
109 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, 2022 in Others by narikkadan
• 51,240 points
165 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