1

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:

enter image description here

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

enter image description here

nittin aggarwal
  • 31
  • 8
  • 1
    Providing text instead of images helps to get much faster recommendations from the community – RF1991 Apr 13 '22 at 06:58

2 Answers2

0

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

excelPackage.Workbook.Calculate();
jabda001
  • 31
  • 2
0

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.

rbren
  • 73
  • 7
  • Thank you for your update. I will check this one and will update accordingly. Just want to update you that i already shared these details with EPPlus developer and he was agree that there is problem in reading because there are some issue in different worksheet. so may be in next version this issue will fix But this is very good point i need to try this as well before reach to any conclusion. Thank you for your update. – nittin aggarwal Aug 02 '22 at 16:14