RE: Percent Formatting - getBuiltinFormat ArrayIndexOutOfBoundsEx ception

[Laubach Shawn Contr OC-ALC/PSB <Sh...@tinker.af.mil>]

That's because the index of 167 is not a built in format.  All the built in
formats are listed in HSSFDataFormat documentation.  You'll have to extract
the format from the workbook.

Shawn

-----Original Message-----
From: Bigwood, David [mailto:dbigwood@metatomix.com] 
Sent: Tuesday, April 13, 2004 3:38 PM
To: POI Users List
Subject: Percent Formatting - getBuiltinFormat
ArrayIndexOutOfBoundsException


Having an issue related to reading a spreadsheet containing percentage
formatting and outputting the values read into XML in the same percentage
format.

Wondered if someone has seen the issues before and may know to assist or
even
better be able to provide a code snippet that does what I want.

We have a generic Excel spreadsheet reader that generates XML on the back
end. We throw most things at it OK and it gives us what we want. However we
have an issue with extracting the built in format string for perentage
formatted cells. We can read the format for cells with percentage formats
with no decimal places "0%" with 2 decimal places "0.00%" but NOT 4 decimal
places - we get an ArrayIndexOutOfBoundsException at the point below where
the value returned for call:

Short s = cell.getCellStyle().getDataFormat();

returns a value (167) which is causing the call below:

HSSFDataFormat.getBuiltinFormat(s);

To barf with the exception noted.

I added the exception catch to work around this problem as several values
for
s are causing it. Anyone got any ideas.

A snippet of bad code follows ;)


private void processCell(HSSFCell cell) {
	int cellType = cell.getCellType();
	switch (cellType) {
		case HSSFCell.CELL_TYPE_NUMERIC :
			double value = cell.getNumericCellValue();
			// test if a date!
			if (OfficeUtils.isCellDateFormatted(cell)) {
				String cellText = OfficeUtils.getDate(cell);
				OfficeUtils.addCell(
					buffy,
					(int) cell.getCellNum(),
					cellText);
			}
			else if
(OfficeUtils.isCellPercentageFormatted(cell))
{
				NumberFormat percent =
NumberFormat.getPercentInstance();
				try {
					short s =
cell.getCellStyle().getDataFormat();
	error here -->		String formatString =
	
HSSFDataFormat.getBuiltinFormat(s);
					if (formatString.indexOf(".") > 0) {
						// count number of 0's
between . and %
						int i1 =
formatString.indexOf(".");
						int i2 =
formatString.indexOf("%");
						if (i2 != -1 && (i2 - i1) >
1) {
	
percent.setMinimumFractionDigits(i2 - i1 - 1);
						}
					}
				}
				catch (ArrayIndexOutOfBoundsException e) {
				}
				String cellText =
	
percent.format(cell.getNumericCellValue());
				OfficeUtils.addCell(
					buffy,
					(int) cell.getCellNum(),
					cellText);
			}
		...

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