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:51:10 GMT
This is a sample function that I decided to use:

  public String convertNumberToString(double num) {
    int myIntNum = (int)num;
    if ((num-myIntNum)==0) {
      return myIntNum+"";
    } else return num+"";
  }

It is called if HSSFDataFormat = 0x31 and getCellType = 
HSSFCell.CELL_TYPE_NUMERIC.

Example:

convertNumberToString(12345.0) => "12345"
convertNumberToString(12345.1) => "12345.1"

Avik,
Does this look like a valid solution considering the observations stated in 
my previous e-mail?





>From: "Konstantin Paradizov" <java_dev_kp@hotmail.com>
>Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
>To: poi-user@jakarta.apache.org
>Subject: Re: Cell Type Problem with Excel 2002
>Date: Tue, 11 Nov 2003 15:45:21 -0500
>
>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
>

_________________________________________________________________
>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