OpenXml excel and formulas

In an old post, I deal with a simple way to create excel report using openXml format. The trick is a simple manipulation of the document with Linq to Xml.

Now I need to add another feature, I need to open an excel document with formulas, fill some cells, leaving formulas intact. My first version does not work as expected, I simply created an excel with simple formulas, then fire my function and when I open the resulting excel I see all zero on formula column, but the formula is there, and if I change some cell referenced by the formula I’ll obtain the right value.

This problem arise because formula are stored in original sheet with such a xml

1
2
3
4

<c r="C2">
  <f t="shared" ref="C2:C10" si="0">A2+B2</f>
  <v>0</v>
</c>

This means that the cell C2 contains the formula A2+B2, but the node <v>0</v> tells Excel that actual value is Zero. So when you open the resulting excel file, excel found that content of the cell is 0 and shows this value until some related cell changes content. To solve this problem I simply added a bit of code that removes the <v> element.

1
2

originalElement.Descendants(ExcelFiller.ns_s + "v").Remove();
row.Add(originalElement);

Now when excel opens elaborated document, it found no <v> (value) element, so it recalculate it based on formula.

alk.

Tags: OpenXml