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" <[EMAIL PROTECTED]> Reply-To: "POI Users List" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> Reply-To: "POI Users List" <[EMAIL PROTECTED]> To: POI Users List <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> >Reply-To: "POI Users List" <[EMAIL PROTECTED]>
> >To: POI Users List <[EMAIL PROTECTED]>
> >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: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> > >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
> >
>
> _________________________________________________________________
> Send a QuickGreet with MSN Messenger
> http://www.msnmessenger-download.com/tracking/cdp_games
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
From Beethoven to the Rolling Stones, your favorite music is always playingon MSN Radio Plus. No ads, no talk. Trial month FREE! http://join.msn.com/?page=offers/premiumradio
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
