Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
  b=t1I7w/yRnffZcmnEWhDhGIXyg6gW+mg+jaQ92115TvA1I7lmhKQbVfDpgXAwrHxWPW7AT46hB9LiioeTIr6xwqZRta4+IHsJ53/Lj9460TZ5eGxhHcxSKvOvqsg26Mddp+8mwaPQm4p9v9+NjRIUlqAm53W/z4utZ9PBRMUdei4=
  ;
Message-ID: <20060620070103.89637.qmail@web36615.mail.mud.yahoo.com>
Date: Tue, 20 Jun 2006 00:01:03 -0700 (PDT)
From: Anthony Andrews <pythonaddict@yahoo.com>
Subject: RE: Reading leading zero formatted numbers
To: POI Users List <poi-user@jakarta.apache.org>
In-Reply-To: 
 <9184F901B9329343B15CA661D1009B0502E6FA73@NHQ1ACCOEX02VS1.corporate.amfam.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-606000588-1150786863=:85186"
Content-Transfer-Encoding: 8bit

--0-606000588-1150786863=:85186
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Your other option is just to carry out the required correction to the value using Java.

If you know for certain that the format is incorrect, are aware of which column the cells are within and know what they should look like, then simply wirte a method that will take the value you are recovering and convert it into a String of the correct length with the numbers packed - if necessary - using leading zeros.

"Chaudhary, Harsh" <HCHAUDHA@amfam.com> wrote: This is probably too simplistic but try to get the value of the Excel
cells as String by getStringCellValue(). But this will probably throw a
runtime exception about the format of the cell data. I also encountered
similar problems with my workbooks. After working with work arounds for
a while, I got tired of it.
So, I wrote a Visual Basic sub (a glorified macro essentially) which
would take the original workbook and paste the relevant data into a new
workbook. Basic data pre-processing. That's where I got rid of the
annoying stuff like $ and ',' symbols; percentage conversions from e.g.
60% to .6 etc. A favorable side effect was reduction in the file size of
the workbooks I had to read as the new workbooks were only about 20KB as
opposed to the 830KB filesize of the original workbooks. Huge
performance gains there.

This is something you might want to look into if nothing else works.
Harsh.

-----Original Message-----
From: Martin Bradley [mailto:martsbradley@btinternet.com] 
Sent: Monday, June 19, 2006 2:22 PM
To: poi-user@jakarta.apache.org
Subject: Reading leading zero formatted numbers


Folks,

I've been using POI to read values from excel spreadsheets, but I have a
problem.

My spreadsheets can contain a column with the following codes shown in
the table.  The formatting of the cells is set up as Custom 0000000

0003322
0047345
7088449
7145456
2380398
0234400

I do know that the column should be set to Text, since that is what it
contains, these are not numbers but codes.  However I have no control
over this since I have not created the data.

Whenever I read the values out of the cells I get the following doubles.

3322.0
47345.0
7088449.0
7145456.0
2380398.0
234400.0

POI is reading the correct value from the sheet, the values read are the
same as the ones shown in the Formula bar whenever the cell is selected.
The Excel application is taking the Formatting preference and displaying
the leading zeros to the user.

How can I get POI to read the values that are shown in the table after
the formatting has been applied.  I want the String 
0003322 for the first cell for example.

I'm sure that this is not the first time this problem came up, I've
searched the mailing list archive but only seen people trying to
preserve the leading zero when writing Excel sheets.

thanks for your time,
Martin.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--0-606000588-1150786863=:85186--