#VALUE! Error when manually tryinng to add a cell with numeric value and a empty cell

Aug 28, 2012 at 5:53 AM

HI All,

I generated a excel file using EPPlus library. Now I am trying to manually add some of the cells by using follwing method.

=H23 + H24 (where h23 value is 233 and h24 is blank)

I get #VALUE! error. If I evaluate the expression it says 233 + "". The value of H24 cell is considered as "" instead of treating it as null.

What should i do to resolve this issue.




Aug 28, 2012 at 1:42 PM

I have tried to reproduce the issue but with no luck. Did I do anything wrong ?



            FileInfo  arq = new FileInfo(@"c:\file.xlsx");

            using (ExcelPackage p = new ExcelPackage(arq))

                var w = p.Workbook.Worksheets.Add("sheet");

                w.Cells[1, 1].Value= 2;                
                w.Cells[1, 3].Formula = "SUM(A1+B1)";



Aug 29, 2012 at 3:05 AM

HI Mrxrsd,

Thank you for quick reply. I guess I was not able to explain the problem properly. This is what I am trying.

I have generated an Excel file using EPPlus library. Some of the cells in the file are empty and some have numeric values. I open the file by double clicking on it (file is saved and I am opening it manually without the help of code).

Now if I try to add two or more cells (where atleast one cell is empty) I get #VALUE! error.

(=H1 + H2 + H3) where H2 is a blank cell. If I eveluate the expression it says 233 + "" + 20. If I apply sum() to add the cells I get the result back. But users of the application are use to using numeric operators. So I am hoping when I write a null or empty value in a cell, instead of storing it as "" it should be store as blank.


Aug 29, 2012 at 12:03 PM

How do you load your cells? Are you using any helper method, like loadfromdatable, loadfromcollection,etc ? If you are using it, maybe will be necessary to add some check for empty values.

Aug 30, 2012 at 2:48 AM

HI mrxrsd,

Yes, I am using LoadFromDataTable helper method. Please let me know what sort of chech's I have to add.

Thank you



Aug 30, 2012 at 3:16 AM

I am only guessing here, but try this in ExcelRangeBase.cs line 1547.


			foreach (DataRow dr in Table.Rows)
				foreach (object value in dr.ItemArray)
					if ( value!=null && value.ToString() != string.Empty  )
					_worksheet.Cell(row, col++).Value = value;
				col = _fromCol;

Aug 30, 2012 at 4:24 AM

HI mrxrsd,

Thank you quick reply. With this check, I am able to add empty cells with the ones which contains numeric values.