Intended Cell Type

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Intended Cell Type

Blake Watson
​​
Hi, guys--

I'm struggling with figuring out how to determine what type a cell SHOULD
be. If I use CellType, what I get is the contents of the cell as they
currently are. For example, an empty cell which is formatted as "#,##0"
will have a type of "blank" but I'd like to be able to get that it's meant
to be a number.

This came up because my users were creating Excel spreadsheets with a
10-digit ID number like and defining it as "a number between 1000000000 and
9999999999". The problem being this would end up as a double, and (at
export time) come out as something like "1.23456789E9".

So I sez to 'em, I sez, "Hey, it's not a number, it's a numeral. Format it
as text in your Excel, and I'll know to treat it as a string." That seems
sensible to me: If it's a numeral, don't pretend it's a number. It's just a
string that happens to look like a number.

But then I realized that blanks all come out as, well, blank (or
CellType/BLANK rather). Do I have to suss the type from the format manually
to get the type they mean? Is there any way for them to specify in Excel
that something is a particular type that POI can tell me what they mean?
--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Javen O'Neal-2
Are you after the Cell's CellStyle's DataFormat?

On Jun 26, 2017 4:34 PM, "Blake Watson" <[hidden email]> wrote:

> ​​
> Hi, guys--
>
> I'm struggling with figuring out how to determine what type a cell SHOULD
> be. If I use CellType, what I get is the contents of the cell as they
> currently are. For example, an empty cell which is formatted as "#,##0"
> will have a type of "blank" but I'd like to be able to get that it's meant
> to be a number.
>
> This came up because my users were creating Excel spreadsheets with a
> 10-digit ID number like and defining it as "a number between 1000000000 and
> 9999999999". The problem being this would end up as a double, and (at
> export time) come out as something like "1.23456789E9".
>
> So I sez to 'em, I sez, "Hey, it's not a number, it's a numeral. Format it
> as text in your Excel, and I'll know to treat it as a string." That seems
> sensible to me: If it's a numeral, don't pretend it's a number. It's just a
> string that happens to look like a number.
>
> But then I realized that blanks all come out as, well, blank (or
> CellType/BLANK rather). Do I have to suss the type from the format manually
> to get the type they mean? Is there any way for them to specify in Excel
> that something is a particular type that POI can tell me what they mean?
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Blake Watson
>>Are you after the Cell's CellStyle's DataFormat?

No, that would be the "#,##0.00", which I have. I want to get from the
DataFormat to the type (numeric, string, etc.). I think I'll have to do it
myself.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Javen O'Neal-2
Right. If the cell is blank, the data format could hint at the value it is
designed to hold, but doesn't mean that's what will be written there.

You could try entering a date and format it as "#,##0.00". The value would
probably be meaningless, but would still be valid as far as POI or Excel
are concerned.


On Jun 26, 2017 21:09, "Blake Watson" <[hidden email]> wrote:

>>Are you after the Cell's CellStyle's DataFormat?

No, that would be the "#,##0.00", which I have. I want to get from the
DataFormat to the type (numeric, string, etc.). I think I'll have to do it
myself.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Blake Watson
>> but doesn't mean that's what will be written there.

Not in Excel-land. This presents some challenges when you're trying to
actually restrict what might go there.

>>You could try entering a date and format it as "#,##0.00".

Yeah. And I could format the values according to whats in there, which
would remove the scientific notation aspect, but which would also put
commas in exported numbers.

Excel's a hell of a thing.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Intended Cell Type

Murphy, Mark
I don't think you can restrict what goes in a cell in Excel without using VBA. If you need to do that you are better off with a database anyway. Besides, VBA cannot be parsed by POI, so even if you do restrict the contents of a cell that way, you can't detect it with POI.

-----Original Message-----
From: Blake Watson [mailto:[hidden email]]
Sent: Tuesday, June 27, 2017 12:26 PM
To: POI Users List <[hidden email]>
Subject: Re: Intended Cell Type

>> but doesn't mean that's what will be written there.

Not in Excel-land. This presents some challenges when you're trying to actually restrict what might go there.

>>You could try entering a date and format it as "#,##0.00".

Yeah. And I could format the values according to whats in there, which would remove the scientific notation aspect, but which would also put commas in exported numbers.

Excel's a hell of a thing.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Greg Woolsey
Even if you are attempting to just write a validator for some input that is
then used to update/add to a workbook, you may not get what you want
relying on cell formatting.  Excel lets you format an entire column as
numbers, but enter text in whatever cells you want, for things like column
headers.

You may be better served having users define data validation constraints
for specific cells or ranges of cells, and using the code added recently to
POI to evaluate those constraints dynamically as data changes.

On Tue, Jun 27, 2017 at 10:45 AM Murphy, Mark <[hidden email]>
wrote:

> I don't think you can restrict what goes in a cell in Excel without using
> VBA. If you need to do that you are better off with a database anyway.
> Besides, VBA cannot be parsed by POI, so even if you do restrict the
> contents of a cell that way, you can't detect it with POI.
>
> -----Original Message-----
> From: Blake Watson [mailto:[hidden email]]
> Sent: Tuesday, June 27, 2017 12:26 PM
> To: POI Users List <[hidden email]>
> Subject: Re: Intended Cell Type
>
> >> but doesn't mean that's what will be written there.
>
> Not in Excel-land. This presents some challenges when you're trying to
> actually restrict what might go there.
>
> >>You could try entering a date and format it as "#,##0.00".
>
> Yeah. And I could format the values according to whats in there, which
> would remove the scientific notation aspect, but which would also put
> commas in exported numbers.
>
> Excel's a hell of a thing.
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Blake Watson
In reply to this post by Murphy, Mark
>> I don't think you can restrict what goes in a cell in Excel without
using VBA.

You can if you're only accessing the cell through POI, which I am. =)

>>If you need to do that you are better off with a database anyway.

I guess I'm using POI differently than most. For me, it's a way for people
who only know Excel to describe their calculations.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Intended Cell Type

Blake Watson
In reply to this post by Greg Woolsey
>>You may be better served having users define data validation constraints

I do use that. It helps with keeping the data correct, but has no effect on
how the data acts when exported.

In Excel, you can type '1 to mean "the string 1" but then you can turn
around and put a plain 1 in the same cell, and all will be fine. So I think
I can only do this with a meta-Excel option, where the user specifies a
genuine data type. Fortunately, it'll only be necessary for oddball
situations like this (large numeric IDs, and maybe a few others I haven't
encountered yet).

Thanks, all!
Loading...