Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
From: "Konstantin Paradizov" <java_dev_kp@hotmail.com>
To: poi-user@jakarta.apache.org
Bcc: 
Subject: Re: Cell Type Problem with Excel 2002
Date: Tue, 11 Nov 2003 11:57:28 -0500
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <Law14-F48T2JveR6IHu000260ba@hotmail.com>

Avik,

I can determine the cell format (HSSFDataFormat) as 0x31.
That tells me that my cell should be read as text.

When I know that it's 0x31, isn't there a way to convert the cell value to a 
string?
Excel says that if you format fields as Text they will be shown exactly as 
typed.
Inside Excel it is true.

I found that within the same spreadsheet, if I format a column as Text, some 
fields show the green corner at top left and some don't. The ones that don't 
are the ones that look like "12345" and get interpreted as "12345.0". If I 
click on each one of them then they automatically get a green corner, which 
Excel 2002 uses to show numbers stored as text.

Unfortunately users are not all very computer-friendly. It's hard to educate 
everybody to prefix the numbers with an apostrophe. They would still forget 
to do that and then come to me and say that my application doesn't work.

Should I write some kind of VBA Conversion Macro and keep it in the Excel 
template that the users use to upload data? Is POI going to keep the Macro 
intact?


>From: Avik Sengupta <avik@apache.org>
>Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
>To: POI Users List <poi-user@jakarta.apache.org>
>Subject: Re: Cell Type Problem with Excel 2002
>Date: 11 Nov 2003 21:19:06 +0530
>
>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
>

_________________________________________________________________
Send a QuickGreet with MSN Messenger 
http://www.msnmessenger-download.com/tracking/cdp_games


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