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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
