poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Konstantin Paradizov" <java_dev...@hotmail.com>
Subject Re: Cell Type Problem with Excel 2002
Date Tue, 11 Nov 2003 20:45:21 GMT
I have tried to enter 12345.000 into the cells and here is what I found:

12345.000 typed in a Text cell stays 12345.000 when saved and re-opened

12345.000 typed in a Number cell becomes 12345.00 only because by default 
the Number cell is formatted to have 2 decimal places

12345.000 typed in a General cell automatically becomes 12345 (!!!)

If you take a General type cell with "12345" in it and format it as text, 
the green corner WILL NOT appear right away. This is the cell that causes 
problems with POI.

So, I guess what my users are doing is they are copying data from other 
spreadsheets where cells are formatted as General and pasting them into my 
template. Then they select the column and format it as Text. But the values 
copied from General type cells do not get the green corner right away.
They only get it if you click on them and then click on something else.

Given that Excel does not preserve "12345.000" entered in a General type 
cell and makes it "12345", is it safe to assume that if my HSSFDataFormat = 
0x31 (Text) and getCellType = NUMERIC then I can use
the getNumericCellValue() (type double) converted into a string (Java 
appends ".0" to a whole number automatically) and cut off ".0" at the end?







>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 22:47:22 +0530
>
>Try to enter 12345.000 into cells, and format them as text. then see if
>they are maintained on closing and re-opening the file, for cells with
>and without the green corner. That should give us some info on what
>formats are stored where.
>
>About macro's, yes, newer version of POI does keep the macro's intact on
>rewrites.
>
>Regards
>-
>Avik
>
>
>On Tue, 2003-11-11 at 22:27, Konstantin Paradizov wrote:
> > 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
> >
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: poi-user-help@jakarta.apache.org
>

_________________________________________________________________
>From Beethoven to the Rolling Stones, your favorite music is always playing 
on MSN Radio Plus. No ads, no talk. Trial month FREE!  
http://join.msn.com/?page=offers/premiumradio


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


Mime
View raw message