Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Determine TCellType for empty cell

 Post Reply Post Reply
Langley Adam View Drop Down
New Member
New Member

Joined: 22 Jul 2016
Posts: 3
Post Options Post Options   Quote Langley Adam Quote  Post ReplyReply Direct Link To This Post Topic: Determine TCellType for empty cell
    Posted: 13 Aug 2016 at 2:42am
I am trying to determine if the user has intended a cell (which is currently empty) to contain only a numerics.

Is there a helper function anywhere that will let me ask if a cell is expecting a numeric value?

I will utilise this to determine whether I should show the user a numeric keyboard, or a full qwerty.

If the cell is non-empty, I know that I can call 
if (TExcelTypes.ObjectToCellType(cellObj) == TCellType.Number) {}

However, my cell is empty, but formatted in Excel as either "Number", or any other kind of digit-only field.

How can I simply ask if the cell can legally contains non-digits or not?

Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support

Joined: 18 May 2010
Posts: 1236
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 16 Aug 2016 at 3:10pm
The main issue here is that cell formats are mostly all numeric. (in Excel docs they are actually referred as "numeric formatting). So if the cell has a format, it is likely a number or a date. To be more precise, you can have formats for:

2)Numbers (or currency, etc)
3)Text (this is the format  "@")

You can use TFlxNumberFormat.HasDate and TFlxNumberFormat.HasTime to know if the cell is a date/time/datetime. This would detect option 1), but you probably want to show the numeric keyboard for dates too.
Now, "general" format might be either the string "general" or an empty string. This is the most common case you will find for strings in a cell. (it makes no sense to format cells with strings, the numeric format won't apply to them).

The last case is a cell formatted with "@". "@" is an special format, in that it doesn't format anything (as said, strings are not changed by the numeric format of the cell), but instead of being a format, what it does is to tell Excel that even if you enter a number in this cell, it must be entered as string. Normally Excel will convert the numbers or dates you enter to numbers, so if you type "1" it will enter number 1, not the string "1". When a cell is formatted with "@" and you type 1, it will enter the string 1 into the cell. So @ doesn't affect the display of the cell, but the way Excel converts the input when you enter data. Most people doesn't even know about @ so you are most likely to find that in a normal file cells with strings are not formatted at all.

So well, basically, you should:
1)Get the format string for the cell:
string Format = xls.GetCellVisibleFormatDef(row, col);

2)See if the string is null, empty or "General" in those cases, assume a string.

3)Search for an |@" in the format string. Note that the format might not be just "@" but something like "@@" (@@ will repeat whatever you enter in the cell twice). So you need to search for the string "@" in the format string, if there are any then this is a text cell.

4)If you want to differentiate between dates and numbers, call TFlxNumberFormat.HasDate(Format) and TFlxNumberFormat.HasTime(Format)

And that should be it. I am not sure it is very useful, but If a cell has numeric formatting, it likely has a number or a date.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down