4

I am opening an Excel spreadsheet and processing it. When I get to a cell that contains a formula (LOOKUP in this case), there is an exception:

Cannot convert Submission!G6's value to System.String

This is happening in this line of code:

        var values = row.Cells(1, lastColumnNumber)
            .Select(x => x.GetString())
            .ToArray();

There is also a Github issue for this https://github.com/ClosedXML/ClosedXML/issues/1217

How do I get the calculated "value" from the cell?

Jess
  • 23,901
  • 21
  • 124
  • 145

1 Answers1

4

As noted, this is actually an issue with ClosedXML.
But! There is a workaround. You can call CachedValue instead of Value. Here is the code, fixed:

        var values = row.Cells(1, lastColumnNumber)
            .Select(x => x.CachedValue?.ToString())
            .ToArray();

From the documentation, CachedValue holds the result of the calculation/formula.

Jess
  • 23,901
  • 21
  • 124
  • 145
  • What if the property `NeedsRecalculation` is set to `true`? How you can force the recalculation? I have noticed that `GetFormattedString()` seems always to return a text even if `Value` or `GetString()` are throwing exception – Kill KRT Dec 26 '21 at 15:47
  • Hi @KillKRT, I'm sorry - I don't know. – Jess Dec 27 '21 at 13:02
  • 1
    I've found that you can do at worksheet level using `RecalculateAllFormulas` – Kill KRT Dec 27 '21 at 18:08