poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kais Dukes" ...@kaisdukes.com>
Subject RE: General questions about HSSFDataFormat
Date Tue, 17 Feb 2004 21:54:16 GMT
Dear Paul

In the XLS binary format, any format indices starting at 164 upwards are
custom formats.

That doesn't answer your question, but its some information.

-- Kais

-----Original Message-----
From: Paul Lee [mailto:paul.lee@skinnypigeon.com]
Sent: 17 February 2004 21:40
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
Excel's
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

<NumberFormat
    ss:Format="_([$EUR]\ * #,##0.00_);_([$EUR]\ * \(#,##0.00\);_([$EUR]\ *
&quot;-&quot;??_);_(@_)"/>

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
in
data format mask, some improvement can be made to make POI more useful.

Paul

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,
borders
> 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
>
>
(http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFDat
> 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






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