Green marker and SUM

Nov 21, 2012 at 8:02 AM
Edited Nov 21, 2012 at 8:04 AM

I have generated a sheet with "number" - 0 - values in it. When I open the Excel document I see green markers in top left corner of each cell.

If I (manually) create a SUM on that column I always get 0. The strange thing is that if I edit anything within the sell, replace comma or add a number the green marker disappears, the cell value is right justified and included in SUM.

I have confirmed by right clicking that original format is Number.

Not sure what I am doing wrong here?

Nov 21, 2012 at 8:35 AM

I figured out that setting format is not enough - I also have to convert the datatype (I was setting string everywhere because I am converting from a flat file).

I do not know if there is a better way to detect if it should be converted to a number before saving than this:

 

      

                      If col.Format.Contains("#") Or col.Format.Contains("0") Or col.Format.Contains("%") Or col.Format.Contains("?") Then

 

I only know format - not the data type.

Editor
Nov 21, 2012 at 12:06 PM

from http://epplus.codeplex.com/discussions/392635

jan wrote:

EPPlus will use the datatype of the object for the cell values, so make sure you add the datatable colums as numeric if they contains numeric data. In this case there is no need to use the datatable. Set the cell values directly instead.