EPPlus returns #VALUE! instead of Value from the cell content randomly [epplus]

Question

As per Current requirement I need to read value from the cell using EPPlus. This cell contains the formula and showing value correctly in XL Sheet. but when i am reading that value using EPPlus some cells are giving correct value but some cells are giving error "#VALUE!". I have used ".Calculate()" before read the value but still facing the same problem. Please find the code below in c#.

totalRecycleWorksheet.Cells[row, colval + 5].Style.Numberformat.Format = "#";

totalRecycleWorksheet.Cells[row, colval + 5].Calculate();

var value = totalRecycleWorksheet.Cells[row, colval + 5].Value;

if (!totalRecycleWorksheet.Cells[row, colval + 5].Value.ToString().Equals("#VALUE!")) {}

and here is the formula in every cells:

=IF(('Failure Item'!E348+ROUNDUP(('Output'!E348)*$B$1,0)-'Purchased Items'!F348)>0,('Failure Item'!E348+ROUNDUP(('Output'!E348)*$B$1,0)-'Purchased Items'!F348),0)

and values are as per the screenshot:

Also you can check the Output I have stored in datatable to check the value:

Answer 1

Just keep updated on this post that I have found another library IronXL. When I use it then this problem is solved but one issue with this IronXL that that it will work locally but if you deploy your code in production then code will stop working & need to purchase the license for use in production

Answer 2

The only examples I see call Calculate at the workbook level such as

excelPackage.Workbook.Calculate();

Answer 3

I had a similar problem. In my case the Excel workbook was a macro-enabled (.xlsm) file. It was macro enabled because I had made use of VBA functions.

When reading Excel.Range.Value2 property from cells the numerical result was consistently -2146826273. I searched this error code as the Hex (800A 07DF) with no luck, but eventually used a bit of debugging to find it resulted from Excel outputting #VALUE! in the cell I was trying to read.

This was because the macros weren't enabled when I'd loaded it via C#, so calls to the VBA functions were failing.

I followed the advice in: Programmatically enable Excel macro in C# to enable macros on the workbook and all my #VALUE! problems disappeared.