This project has moved. For the latest updates, please go here.
1

Resolved

EPPlus Formula value resulting in #VALUE!

description

I am trying to retrieve the cell content from Excel using EPPlus and it returns #VALUE!

To reproduce the issue I have an Excel file with a named range Countries and PhoneCodes and another table tblPhoneCodes and with two columns Country and Code, both the named range and table have the same data, 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(G2:G5,MATCH(B1,F2:F5,0)-1,0,1,1)
and a cell B3 basically it displays the Code for the selected Country. Cell B3 uses the following formula
=OFFSET(PhoneCodes,MATCH(B1,Countries,0)-1,0,1,1)
and a cell B4 basically it displays the Code for the selected Country. Cell B4 uses the following formula
=OFFSET(INDIRECT("tblPhoneCodes[#Headers]"),MATCH(B1,INDIRECT("tblPhoneCodes[Country]"),0),1,1,1)
Everything in Excel works as it should, but the issue is when I am reading the value of B2, B3 and B4 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();

            Console.WriteLine(String.Format("Country: \t\t\t{0}", package.Workbook.Worksheets[1].Cells["B1"].Value));
            Console.WriteLine(String.Format("Phone Code - Direct Reference:\t{0}", package.Workbook.Worksheets[1].Cells["B2"].Value.ToString()));
            Console.WriteLine(String.Format("Phone Code - Name Ranges:\t{0}", package.Workbook.Worksheets[1].Cells["B3"].Value.ToString()));
            Console.WriteLine(String.Format("Phone Code - Table reference:\t{0}", package.Workbook.Worksheets[1].Cells["B4"].Value.ToString()));

            // The following method removes any logger attached to the workbook.
            package.Workbook.FormulaParserManager.DetachLogger();
        }
    }
Output:
Country:                        China
Phone Code - Direct Reference:  #VALUE!
Phone Code - Name Ranges:       #VALUE!
Phone Code - Table reference:   #VALUE!

I am using MS Excel 2010, .NET 4.0, EPPlus 4.1.0, and Windows 10 64bit

UPDATE:

Weirdly, it works if you don't have the package.Workbook.Calculate(); in it

file attachments

comments

ashleyabraham wrote Feb 1 at 5:12 AM

Please see the discussion

JanKallman wrote Feb 17 at 12:25 PM

Fixed in changeset 6cf0e60fbc63