EPPlus returns #VALUE! instead of the cell content when used with Formula OFFSET, INDIRECT & MATCH

Question

I have an Excel file with a table tblPhoneCode and with two columns Country and Code, and I have a cell B1 with List Data Validation pointing to the Country column

and a cell B2 basically it displays the Code for the selected Country.

Cell B2 uses the following formula

OFFSET(INDIRECT("tblPhoneCode[#Headers]"),MATCH(B1,INDIRECT("tblPhoneCode[Country]"),0),1,1,1)

Everything in Excel works as it should, but the issue is when I am reading the value of B2 using EPPlus in C# I am getting #VALUE! instead of the actual Phone Code. I've tried .Calculate() from workbook, worksheet, to cell and tried to access the value is still the same. I've attached the logger and it turns up empty and there is no error logged in it.

C# Code

static void Main(string[] args)
{
    var excelFile = new FileInfo(@"C:\Users\Ash\Desktop\Epplus.xlsx");
    using (var package = new ExcelPackage(excelFile))
    {
        // Output from the logger will be written to the following file
        var logfile = new FileInfo(@"C:\Users\Ash\Desktop\EpplusLogFile.txt");
        // Attach the logger before the calculation is performed.
        package.Workbook.FormulaParserManager.AttachLogger(logfile);
        // Calculate - can also be executed on sheet- or range level.
        package.Workbook.Calculate();

        Debug.Print(String.Format("Country: \t{0}", package.Workbook.Worksheets[1].Cells["B1"].Value));
        Debug.Print(String.Format("Phone Code:\t{0}", package.Workbook.Worksheets[1].Cells["B2"].Value));

        // The following method removes any logger attached to the workbook.
        package.Workbook.FormulaParserManager.DetachLogger();
    }
}

Output:

Country:    US
Phone Code: #VALUE!

Any help or insight is much appreciated, I am using MS Excel 2010, .NET 4.0, EPPlus 4.1.0, and Windows 10 64bit

Answer 1

Created a quick replica of your spreadsheet:

and confirmed the same results you're getting - cell B2 has a value of #VALUE!. From reading the documentation / EPPlus source code examples, the result is surprising, and looks like a bug or 'feature'. Went through some of the other ExcelWorksheet and ExcelWorkbook members, and found setting the ExcelWorkbook.FullCalcOnLoad property solves the issue:

using (var package = new ExcelPackage(fileInfo))
{
    // UPDATED answer - doesn't look like this is needed either
    // package.Workbook.FullCalcOnLoad = true;
    // same result as question code if Calculate() is called instead: '#VALUE!' 
    // package.Workbook.Calculate();
    var ws = package.Workbook.Worksheets[1];
    Console.WriteLine("Country: \t{0}", ws.Cells["B1"].Value);
    Console.WriteLine("Phone Code:\t{0}", ws.Cells["B2"].Value.ToString());
}

Output:

Country:        Germany
Phone Code:     49

UPDATE: After posting, checked the default value of FullCalcOnLoad, which appears to be true. And indeed, removing both the Calculate() call and leaving FullCalcOnLoad at it's default (not setting the property) seems to work as well, and gives the desired output.