# poi-user mailing list archives

##### Site index · List index
Message view
Top
From avik.sengu...@itellix.com
Subject Re: Cell Type Problem with Excel 2002
Date Tue, 21 Oct 2003 18:57:11 GMT
```You previous mail contains some mind-numbing details that that will certainly
make a good addition to our mailing list archives. I had to read it a couple of
times to understand how it behaves :)..  Thanks for the investigation.

Yeah, your solution sure looks logical and sensible. Whether it is correct or
not i think should be your judgement based on your user requirement.

Regards
-
Avik

> 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?
>
>
>
>
>
> >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
> >> > > > 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
> >> >
> >> >
> >> > ---------------------------------------------------------------------
> >> > 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!
> >
> >
> >---------------------------------------------------------------------
> >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!
>
>
> ---------------------------------------------------------------------
> 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