poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Avik Sengupta <a...@apache.org>
Subject Re: Cell Type Problem with Excel 2002
Date Tue, 11 Nov 2003 17:17:22 GMT
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


Mime
View raw message