Ah, works like a charm now. Thanks! On Tue, 17 Feb 2004 16:06:20 -0600, Laubach Shawn Contr OC-ALC/PSB wrote > 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 line > > dataFormat = new HSSFDataFormat(new Workbook()); > > to > > 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. > > Shawn > > -----Original Message----- > From: Paul Lee [mailto:[EMAIL PROTECTED] > 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 > 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]\ * "-"??_);_(@_)"/> > > 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:[EMAIL PROTECTED] > > Sent: Tuesday, February 17, 2004 2:48 PM > > To: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > --------------------------------------------------------------------- > > 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]
