Can't set a cell to an error value

[craigbrown]

I found this potential bug (?) while writing a unit test.

In Excel, if I type the string #VALUE! into a cell, it is stored in the XML as an error value like this:

<c r="A1" t="e">
    <v>#VALUE!</v>
</c>

When I do the same in EPPlus, it's stored as a regular string like this:

<c r="A1" s="0" t="s">
    <v>0</v>
</c>

This means that using the ISERROR \ IFERROR functions on this cell won't work as expected.

I've tried setting the error value in a few different ways:

sheet1.Cells["A1"].Value = "#VALUE!";
sheet1.Cells["A1"].Value = ExcelErrorValue.Values.Value;
sheet1.Cells["A1"].Value = eErrorType.Value;

but none seem to work.

Is this a bug or is there a different way you're supposed to set error values on a cell?

Unit Test

[TestMethod]
public void IsError_CellReference_StringLiteral()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["B2"].Value = "#VALUE!";
        sheet1.Cells["C3"].Formula = "ISERROR(B2)";

        sheet1.Calculate();

        Assert.IsTrue(sheet1.Cells["B2"].Value is ExcelErrorValue);
        Assert.IsTrue(sheet1.Cells["C3"].Value is bool);
        Assert.IsTrue((bool)sheet1.Cells["C3"].Value);
    }
}

[JanKallman]

I'll have a look at this

[JanKallman]

The value of the must be set to the ExcelErrorValue class, but the constructor and the method's that create this class is all internal. I will switch them all to public and I will also add a new method to the ExcelRangeBase class to set an error value. I'll get back to you when the implementation is done.

[JanKallman]

If it's really really urgent you can call the static Parse or Create methods on the ExcelErrorValue class using reflection to get an instance.
https://github.com/EPPlusSoftware/EPPlus/blob/927d53e60a373f9efe8b20f8448d36d696470cd3/src/EPPlus/FormulaParsing/ExcelValues.cs#L146

[JanKallman]

Fixed in 6.0.7