CODEWITHSUNDEEP
c#excel
SSK
SSK

Reputation: 803

Cell value is read as #### in excel

I am reading a cell value from excel using named range in my case sometime the column width is smaller then the value that is present in the cell and hence it is appearing as ##### when it is huge number.

when i read this from csharp it is reading as #####. Any fix for this

Sample code:

Excel.Application.get_Range(strRange, Type.Missing).Text.ToString() //If i make it 
Excel.Application.get_Range(strRange, Type.Missing).Value2.ToString() //and read any date string i get 40390

Upvotes: 3

Views: 1486

Answers (3)

Charles Williams
Charles Williams

Reputation: 23540

.Text gives you whatever you see as formatted in the Excel rendering layer (so #### if the column is too small) .Value2 gives you the underlying Excel value.

Date/times in Excel are stored internally as numbers representing the number of days since Year 1900 Jan 0 plus a decimal part representing a fraction of 24 hours for the time, so your date is coming through as an integer.

If you want to convert the number back into a date you can use Format: for example format(40390,"ddmmyyyy") or format(40390,"dd/mmm/yyyy")

Upvotes: 2

Lunatik
Lunatik

Reputation: 3948

Are you perhaps trying to import negative dates? Excel doesn't handles these well, processing dates as doubles usually works though.

Upvotes: 0

Ian
Ian

Reputation: 34519

Without having any code to read I would guess that you are reading a display string or similar, which Excel will update each time the row width varies.

You probably need to be reading a more raw value.

Upvotes: 3

Related Questions