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]
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/*
* Created on Feb 17, 2004
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
/**
* @author Paul Lee
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class DataFormatTest {
static HSSFDataFormat dataFormat;
public DataFormatTest(String excelFile) {
// create a POIFSFileSystem object to read the data
try {
POIFSFileSystem poiFs = new POIFSFileSystem( new FileInputStream(excelFile));
HSSFWorkbook wb = new HSSFWorkbook(poiFs);
dataFormat = new HSSFDataFormat(new Workbook());
int numStyles = wb.getNumCellStyles();
for (int i=0; i<numStyles; i++) {
parseStyle(wb.getCellStyleAt((short)i));
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private void parseStyle(HSSFCellStyle cellStyle) {
short formatIndex = cellStyle.getDataFormat();
System.out.println("formatIndex = "+formatIndex+", "+dataFormat.getFormat(formatIndex));
}
public static void main(String args[]) {
DataFormatTest dft = new DataFormatTest(args[0]);
}
}
formatTest.xls
Description: MS-Excel spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
