CODEWITHSUNDEEP
c#excelopenxmlopenxml-sdk
Ozeta
Ozeta

Reputation: 329

reading a formula from a spreadsheet with openxml

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?

Upvotes: 0

Views: 1460

Answers (1)

Alexander Derck
Alexander Derck

Reputation: 14488

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;
}

Upvotes: 1

Related Questions