Cell Type Problem with Excel 2002

[Konstantin Paradizov <ja...@hotmail.com>]

Hello,

I have been using POI / HSSF for a while now. With the deployment of Office 
XP at many of hour sites I have noticed that I started having problems with 
my web application that uses POI.

The spreadsheet that users upload to the server contains a text column, 
which is loaded to Oracle as VARCHAR2. This column has a designated item 
code, which can contain standard alphabet letters and digits. The problem is 
that when it's all digits Excel treats it somehow differently. It comes up 
with an error message saying "Number stored as text".
In POI I would expect getCellType to return STRING rather than NUMERIC 
because that's how the cell is formatted. Instead I get a numeric cell value 
and all of a sudden ".0" is added at the end when I convert it to a string. 
Here is the example:

12345 formatted as text gets interpreted as 12345.0 in POI. I understand 
that according to the description of the HSSFCell model in JavaDocs the 
client app is supposed to do the conversion by itself. But how do I know if 
what I am looking at was "12345" or "12345.0" in the original spreadsheet?
I can't risk making those assumptions myself.

Does anybody know a workaround? What do I do if no matter what, I always 
have to read string value of a cell in a certain column?

Any answers will be greatly appreciated.

Konstantin Paradizov

_________________________________________________________________
Frustrated with dial-up? Get high-speed for as low as $26.95.  
https://broadband.msn.com (Prices may vary by service area.)


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org

Re: Cell Type Problem with Excel 2002

[Avik Sengupta <av...@apache.org>]

Unfortunately, there isn't a generic solution to this. As we keep
saying, we give you what excel has stored.. so the best you can do is a
workaround. 

So what is happening is that Excel is being (over)smart and imagining
that if its all numbers, it should not be text (and hence the error
message you mention). If then in the file  excel stores that cell as a
number object, there's nothing POI can do...

The solution lies in various workarounds. Get your users to enter the
values as '12345 (with a single-quote, forcing it to be a string).
Alternatively, try to get the number format for the cell, and try to
decipher what it was displayed as. 

>What do I do if no matter what, I always 
> have to read string value of a cell in a certain column?

As i said above, the problem is that if excel has the cell as a number
(technically, a NUMBER record or an RK record) rather than a string (SST
record) then POI will have to give you a number. It cant do the
conversion itself, can it? In other words, its not POI thats converting
a string to a number .. excel is. 

Hope that helps. I realise this not necessarily the answer you were
looking for, but this is the underlying technical problem.  Maybe
someone else can provide other workarounds. 

Regards
-
Avik



On Tue, 2003-11-11 at 21:19, Konstantin Paradizov wrote:
> Hello,
> 
> I have been using POI / HSSF for a while now. With the deployment of Office 
> XP at many of hour sites I have noticed that I started having problems with 
> my web application that uses POI.
> 
> The spreadsheet that users upload to the server contains a text column, 
> which is loaded to Oracle as VARCHAR2. This column has a designated item 
> code, which can contain standard alphabet letters and digits. The problem is 
> that when it's all digits Excel treats it somehow differently. It comes up 
> with an error message saying "Number stored as text".
> In POI I would expect getCellType to return STRING rather than NUMERIC 
> because that's how the cell is formatted. Instead I get a numeric cell value 
> and all of a sudden ".0" is added at the end when I convert it to a string. 
> Here is the example:
> 
> 12345 formatted as text gets interpreted as 12345.0 in POI. I understand 
> that according to the description of the HSSFCell model in JavaDocs the 
> client app is supposed to do the conversion by itself. But how do I know if 
> what I am looking at was "12345" or "12345.0" in the original spreadsheet?
> I can't risk making those assumptions myself.
> 
> Does anybody know a workaround? What do I do if no matter what, I always 
> have to read string value of a cell in a certain column?
> 
> Any answers will be greatly appreciated.
> 
> Konstantin Paradizov
> 
> _________________________________________________________________
> Frustrated with dial-up? Get high-speed for as low as $26.95.  
> https://broadband.msn.com (Prices may vary by service area.)
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org