poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Laubach Shawn Contr OC-ALC/PSB <Shawn.Laub...@tinker.af.mil>
Subject RE: General questions about HSSFDataFormat
Date Tue, 17 Feb 2004 22:06:20 GMT
Sorry, I missed the attachments.

The array format exception is because you passed dataformat a new workbook
that did not have the format index you was looking for.  As mentioned in my
first email, you need to call createDataFormat on the workbook so change the

			dataFormat = new HSSFDataFormat(new Workbook());


			dataFormat = wb.createDataFormat();

and all your problems (at least with data format) will go away.  I've also
attached the corrected program that ran just fine.


-----Original Message-----
From: Paul Lee [mailto:paul.lee@skinnypigeon.com] 
Sent: Tuesday, February 17, 2004 3:40 PM
To: POI Users List
Subject: RE: General questions about HSSFDataFormat

Thanks for the reply Shawn.  However, I have already looked into the api you
mentioned.  My concern is that HSSFDataFormat does not cover all the format
mask available in Excel.

Perhaps an example would explain better.

In the attached formatTest.xls, I have a single cell, formatted using
UI: Format->Cell->Number->Accounting, 2 Decimal places, Symbol EUR

If you save the xls file as xml, you can see that the formatmask is 

    ss:Format="_([$EUR]\ * #,##0.00_);_([$EUR]\ * \(#,##0.00\);_([$EUR]\ *

Now I wrote a little program (attached) to loop through the workbook for all
the dataformats.  The format mask I mentioned above is detected as format
index "171", which is not in the HSSFDataFormat class, and hence results in 

java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 171

Ideally POI can just return the format string as above.  If not, at least we
need to substantially increase the format array of HSSFDataFormat.  I am
actually willing to do that (it's just tedious dumb work :), but I want to
make sure there are no better existing solutions.

Am I making sense?  I just think that if what I understand is all there is
data format mask, some improvement can be made to make POI more useful.


On Tue, 17 Feb 2004 14:58:06 -0600, Laubach Shawn Contr OC-ALC/PSB wrote
> The predefined ones are the ones that was pre defined in the 
> original excel spec (to avoid having to include the info for each 
> one in each file which saves a few k in space).
> Look in the java api for HSSFWorkbook and look for createDataFormat which
> returns a HSSFDataFormat which will have a method (getFormat) that returns
> the mask, given an index.
> Shawn
> -----Original Message-----
> From: Paul Lee [mailto:paul.lee@skinnypigeon.com] 
> Sent: Tuesday, February 17, 2004 2:48 PM
> To: poi-user@jakarta.apache.org
> Subject: General questions about HSSFDataFormat
> Hi all, I am still new at POI, so please forgive me if my questions sound
> obvious.
> I am writing a class to extract all style information from a spreadsheet.
> My
> steps are as follows:
> (1) use HSSFWorkbook.getNumCellStyles() to get total # of styles
> (2) cycle through each style.  For each style, I get the alignment,
> etc.
> My problems came when I am extracting the DataFormat using
> HSSFCellStyle.getDataFormat().  My specific questions are:
> - there are only 40 or so predefined style formats in HSSFDataFormat
> aFormat.html).
>  With the endless combination of Excel format mask (e.g. I want 
> Negative Red Parenteses, 4 decimal with Euro sign), it is impossible 
> to predefine style format.  Is there a way to just get the format 
> mask string back, or am I missing something?
> - if I just create a blank spreadsheet using Excel 2002, my little loop
> program already picked up four undefined HSSFDataFormat indexes (43, 
> 41, 44, 42).  Just curious as to what those are.
> I (think) have made an honest effort to dig up more info but came to 
> a dead end.  Any pointers would be greatly appreciated.
> Paul
> ---------------------------------------------------------------------
> 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

View raw message