One more "Worksheet position out of range" when calculating a formula

hubaksis commented 2 days ago

EPPlus usage

Soon to be commercial

Environment

Windows

Epplus version

7.5.0 7.5.0.1122-20241120-develop7

Spreadsheet application

Excel

Description

One more System.IndexOutOfRangeException: Worksheet position out of range.

Here is the test:

[Test]
public void i1708()
{
    using (var package = new ExcelPackage())
    {
        var sheet1 = package.Workbook.Worksheets.Add("Sheet1");
        package.Compatibility.IsWorksheets1Based = true;

        sheet1.Cells["C3"].Formula = @"IFERROR(IF(OR(H3="""",I3="""",E3=0),""N/A"",IF(J3<>"""",INDEX($G$1:$J$1,MATCH(TRUE,INDEX(ABS(G3:J3-E3)=MIN(INDEX(ABS(G3:J3-E3),,)),,),0)),INDEX($G$1:$I$1,MATCH(TRUE,INDEX(ABS(G3:I3-E3)=MIN(INDEX(ABS(G3:I3-E3),,)),,),0)))),"""")";

        sheet1.Cells["E3"].Value = 25;

        sheet1.Cells["G1"].Value = "one";
        sheet1.Cells["H1"].Value = "two";
        sheet1.Cells["I1"].Value = "three";
        sheet1.Cells["J1"].Value = "four";

        sheet1.Cells["G3"].Value = 10;
        sheet1.Cells["H3"].Value = 20;
        sheet1.Cells["I3"].Value = 30;
        sheet1.Cells["J3"].Value = 40;

        Assert.DoesNotThrow(() => package.Workbook.Calculate());
        Assert.AreEqual("two", sheet1.Cells["C3"].Value);
    }
}

JanKallman commented 1 day ago

I'll have a look at it.

hubaksis commented 1 day ago

Thank you for looking into this.

If you take an Excel file, and fill the data manually, then it also fails with the long formula when loaded with EPPlus and calculated. I managed to reproduce the issue with the in-memory Excel, that is why I did not provide an Excel file.

If it helps, I have narrowed down the formula to the part ABS(G3:J3-E3). Not sure is there is anything else causing the issue, but the following test fails on Calculate method:

 [Test]
 public void i1708()
 {
     using (var package = new ExcelPackage())
     {
         var sheet1 = package.Workbook.Worksheets.Add("Sheet1");
         package.Compatibility.IsWorksheets1Based = true;

         //sheet1.Cells["C3"].Formula = @"IFERROR(IF(OR(H3="""",I3="""",E3=0),""N/A"",IF(J3<>"""",INDEX($G$1:$J$1,MATCH(TRUE,INDEX(ABS(G3:J3-E3)=MIN(INDEX(ABS(G3:J3-E3),,)),,),0)),INDEX($G$1:$I$1,MATCH(TRUE,INDEX(ABS(G3:I3-E3)=MIN(INDEX(ABS(G3:I3-E3),,)),,),0)))),"""")";
         sheet1.Cells["C4"].Formula = @"ABS(G3:J3-E3)";

         sheet1.Cells["E3"].Value = 25;

         sheet1.Cells["G1"].Value = "one";
         sheet1.Cells["H2"].Value = "two";
         sheet1.Cells["I3"].Value = "three";
         sheet1.Cells["J3"].Value = "four";

         sheet1.Cells["G3"].Value = 10;
         sheet1.Cells["H3"].Value = 20;
         sheet1.Cells["I3"].Value = 30;
         sheet1.Cells["J3"].Value = 40;

         Assert.DoesNotThrow(() => package.Workbook.Calculate());
         //Assert.AreEqual("two", sheet1.Cells["C3"].Value);
         Assert.AreEqual("15", sheet1.Cells["C4"].Value);
         Assert.AreEqual("5", sheet1.Cells["D4"].Value);
         Assert.AreEqual("5", sheet1.Cells["E4"].Value);
         Assert.AreEqual("15", sheet1.Cells["F4"].Value);
     }
 }

EPPlusSoftware / EPPlus