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
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.
Now when excel opens elaborated document, it found no <v> (value) element, so it recalculate it based on formula.