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

try package.Workbook.Worksheets[1].Cells["B2"].Value.ToString(); — Yahya Hussein, Jan 31 '17 at 05:25
I tried with and without `.ToString()` same result, see Output. I even tried `.Text` — Ash, Jan 31 '17 at 05:46
I see, why do not use dictionary in C# instead of reading the value(Country Code) just read the key (Country Name) and get the value from dictionary — Yahya Hussein, Jan 31 '17 at 05:50
This is just an example scenario to reproduce the issue I am having with reading from Excel using EPPlus — Ash, Jan 31 '17 at 05:52

kuujinbo · Accepted Answer · 2017-02-01T22:44:38.613

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.

When I initially started it didn't work, so I tried the .Calculate() and still didn't work, then now it is working again without .Calculate(), I am baffled. — Ash, Feb 01 '17 at 22:55
@Ash - Agree. From reading the documentation and [source code examples on codeplex](https://epplus.codeplex.com/wikipage?title=About%20Formula%20calculation), I would _expect_ that `Calculate()` is needed.... — kuujinbo, Feb 01 '17 at 23:08

score 1 · Answer 2 · answered Aug 23 '18 at 16:37

Perhaps this documentation or functionality when the question was originally posted did not exist, but I ran into this same problem and tried all the suggestions on the interweb. If you go to the EPPlus Wiki under the section "Some Common Mistakes" you will find:

Don't add the leading = sign in your formula. "=SUM(A1:A2)" is wrong, "SUM(A1:A2)" is correct.

Originally, my code looked like:

sheet.Cells["A1"].Formula = "=SUM(A2:A5)";

when removing the leading = sign:

sheet.Cells["A1"].Formula = "SUM(A2:A5)";

The sheet loaded perfectly (even in safe mode) in Excel with all formulas calculating prior to having to "Enable Editing"

Crud. I posted this on the wrong question....Now the trolls are going to come out. — Steve, Aug 23 '18 at 17:12

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

2 Answers2