diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 911910b..799b137 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -382,7 +382,13 @@ w => String.Compare(w.Name, sName, true) == 0) && XLHelper.IsValidA1Address(cAddress) ) - return _worksheet.Workbook.Worksheet(sName).Cell(cAddress).Value; + { + var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); + if (referenceCell.IsEmpty(false)) + return 0; + else + return referenceCell.Value; + } var retVal = Worksheet.Evaluate(fA1); var retValEnumerable = retVal as IEnumerable; @@ -2194,7 +2200,7 @@ } Worksheet.EventTrackingEnabled = eventTracking; } - + return this; } diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 007b492..4f1180d 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -510,5 +510,41 @@ Assert.AreEqual("E8:J13", ws.Cell("J13").CurrentRegion.RangeAddress.ToString()); } } + + // https://github.com/ClosedXML/ClosedXML/issues/630 + [Test] + public void ConsiderEmptyValueAsNumericInSumFormula() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell("A1").SetValue("Empty"); + ws.Cell("A2").SetValue("Numeric"); + ws.Cell("A3").SetValue("Copy of numeric"); + + ws.Cell("B2").SetFormulaA1("=B1"); + ws.Cell("B3").SetFormulaA1("=B2"); + + ws.Cell("C2").SetFormulaA1("=SUM(C1)"); + ws.Cell("C3").SetFormulaA1("=C2"); + + object b1 = ws.Cell("B1").Value; + object b2 = ws.Cell("B2").Value; + object b3 = ws.Cell("B3").Value; + + Assert.AreEqual("", b1); + Assert.AreEqual(0, b2); + Assert.AreEqual(0, b3); + + object c1 = ws.Cell("C1").Value; + object c2 = ws.Cell("C2").Value; + object c3 = ws.Cell("C3").Value; + + Assert.AreEqual("", c1); + Assert.AreEqual(0, c2); + Assert.AreEqual(0, c3); + } + } } }