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

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

Aug 28, 2012 at 6: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.

 

 

 

Editor
Aug 28, 2012 at 2: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)";
                
                p.Save();
            }

 

 

Aug 29, 2012 at 4: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.

 

Editor
Aug 29, 2012 at 1: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 3: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

 

 

Editor
Aug 30, 2012 at 4: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;
				}
				row++;
				col = _fromCol;
			}

Aug 30, 2012 at 5: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.