Incorrect Calculations

[adawi9]

Hi,

I am EPPlus 6.0.6.0 to do some excel calculations

In one cell I have the below equation in sheet Y =SUM(A!B151:A!B159)

which is calculate the sum of cells B151 - B 159 in sheet A, but I always get 0 value

Below the code I used in asp.net webforms C# (.net framework 4.8)

Note: values calculated correctly when open the excel file, but not retrieved correctly by EPPLUS library

`

    protected void Button1_Click(object sender, EventArgs e)
    {

        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        using (var package = new ExcelPackage(new FileInfo(Server.MapPath("~") + @"\B.xlsx")))
        {
            var calculateOptions = new ExcelCalculationOption { AllowCircularReferences = true, };
            var workbook = package.Workbook;
            workbook.Calculate(calculateOptions);
            var worksheet = workbook.Worksheets["Y"];
            worksheet.Calculate(calculateOptions);
            worksheet.Workbook.CalcMode = ExcelCalcMode.Automatic;

            var x = GetData(worksheet, 117);
        }
    }
    private static List<int> GetData(OfficeOpenXml.ExcelWorksheet worksheet, int rowIndex)
    {
        List<int> result = new List<int>();
        rowIndex++;
        for (int index = 1; index <= 5; index++)
        {
            string value = "";
            double total = 0;
            var cell = worksheet.Cells[rowIndex, index + 1];
            if (cell.Value != null)
            {
                var calculateOptions = new ExcelCalculationOption { AllowCircularReferences = true, };
                cell.Calculate(calculateOptions);
                if (cell.Value.GetType().Equals(typeof(Double)) || cell.Value.GetType().Equals(typeof(Int32)) || 
                          cell.Value.GetType().Equals(typeof(Int16)) || cell.Value.GetType().Equals(typeof(Int64)))
                {
                    result.Add(Convert.ToInt32(cell.Value));
                }
                else if (cell.Value.GetType().Equals(typeof(String)))
                {
                    value = cell.Value.ToString();
                }
                else
                {
                    value = cell.Value.ToString();
                }
            }
            else
            {
            }
        }

        return result;
    }

`

[JanKallman]

I tried this test which seems to work... [TestMethod]

        public void SumWithDoubleWorksheetRefs()
        {
            using(var p=new ExcelPackage())
            {
                var wsA = p.Workbook.Worksheets.Add("a");
                var wsB = p.Workbook.Worksheets.Add("b");
                wsA.Cells["A1"].Value = 1;
                wsA.Cells["A2"].Value = 2;
                wsA.Cells["A3"].Value = 3;
                wsB.Cells["A4"].Formula = "sum(a!a1:a!A3)";

                wsB.Calculate();
                Assert.AreEqual(6D, wsB.GetValue(4, 1));
            }
        }

Can you provide a Test method that I can run to reproduce your issue?

[adawi9]

EPPLUS_Test.zip

Attached a sample project with the issue, I am using excel file already exist not creating the sheets by code

[JanKallman]

This seems to happened when you have an address referencing a worksheet after the colon when the sheet name is wrapped in '. like this: 'a'!a1:'a'!A3. I'll try to provide a fix.

[adawi9]

Great... Waiting your fix

[swmal]

Released in 6.0.7