reading a formula from a spreadsheet with openxml

Question

I'm reading a spreadsheet in C# using the Openxml library, and I'm parsing the values of the Cell object using InnerText propierty, that returns a String or null.

When in the Cell is present a formula, I get the literal text present in the spreadsheet, like that:

=Sheet1!A1Content

that is composed by: former formula ( Sheet1!A1 ) + string of the result ( Content ) and this happens also with basics like: =2+24

how do i get only the result of the formula?

have you tried a Google Search C# Reading a Formula from a spreadsheet with OpenXml.? openxmldeveloper.org/blog/b/openxmldeveloper/archive/2012/11/04/… — MethodMan
I did search but I didn't found that link. I'll start from there. — Ozeta
@MethodMan I read the page but I didn't found anything useful. I understood the structure of the formula but there isn't nothing about the reading of it, as I understood it — Ozeta

Alexander Derck Alexander Derck · Accepted Answer · 2016-01-26T20:03:58

From http://officeopenxml.com/ documentation:

For a formula, the formula itself is stored within an f element as a child element of c. Following the formula is the actual calculated value within a element.

There is also an example, this open xml for a single cell:

<c r="B9" s="3" t="str">
  <f>SUM(B2:B8)</f>
  <v>2105</v>
</c>

So in a Cell element, there is a child for the Formula and a child element for the CellValue. The InnerText property of the cell is the concatenated value of those two in your case. If you already have the cell you need you can do the following:

static string GetValue(Cell cell)
{
   //The ? operator to make sure the 'CellValue' element exists, otherwise return null
   return cell.GetFirstChild<CellValue>()?.Text;
}

reading a formula from a spreadsheet with openxml

1 Answers