How to update the formula cell after copying rows

Question

Hi All,

I am using OpenXML SDK (2.0) to copying rows from one part of Excel to another.

I am able to copy all the cells (Merged cells, normal cells etc) except the Formula cells.

In other words, I have valid rows from "A" through "E". Also i have a formula in "A5" whose text (or formula) is "SUM(B2+B3)".

Now i copy the entire rows ("A" through "E") from row "G" onwards (using OPENXML SDK).

Now my rows "G" through "K" contains the data present in rows "A" to "E".

Also the formula cell present in "A5" is copied to "G5". However this text in the formula cell (G5) states "SUM(B2+B3)". I want this to be updated i.e. the formula cell in G5 should have text "SUM(H2+H3)"

Hope you understood my requirement. I just mentioned the above example using a simple "SUM" method but i want the same to be applied for all the available formulas.

Can you suggest me solution for achieving the above mentioned functionality.

Thanks in Advance

Thanks and Regards,

YKK Reddy

Answer 1

Hi Reddy

You have to change the formulas, as well as the other row and column information, such as the row number in the row (r attribute) and cell (r attribute in the c element).

When you copy things in Excel, the application interface takes care of adjusting this information. This is not available when working with the Open XML file format. There, you're responsible for doing all the things the application interface does for user.

You may want to see this discussion: http://openxmldeveloper.org/discussions/development_tools/f/35/p/5957/160110.aspx

Then look at this article that has links on how to write the type of parser Eric suggests in the discussion

http://ericwhite.com/blog/map/recursive-descent-parser/

---

Cindy Meister, VSTO/Word MVP, my blog

Answer 2

Hi Cindy,

Thanks for your reply. But the Eric's blog contain dealing with simple formulas. If we include the row/column number in the formula (say A1 + A5), it is throwing an exception.

Can you suggest a way to overcome this problem .

Thanks and Regards,

Kishor Reddy

Answer 3

Hi Kishor

I'm sorry, not really. In essence, you have to create code to change the column references. If you're moving from A to D then you basically need to assign A the value 1, D the value 4, get the difference (3), then do the same for all the references in the formulas, 1 + 3 = 4 and 4 = D.

You might ask in the Excel Developer forum that supports the interop if anyone there has code that does this - not for working with Open XML, but for working in the interop. I imagine the people who work with APIs (whether VBA or any other programming language) need this on occasion. So there might be something "laying around" that you could use as a starting point.

---

Cindy Meister, VSTO/Word MVP, my blog