Exception in ClosedXML GetString with Excel formula

3
votes

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?

1
c#closedxml

1 Answers

4
votes

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.