poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shawn Laubach" <shawnlaub...@cox.net>
Subject RE: Percent Formatting - getBuiltinFormat ArrayIndexOutOfBoundsException
Date Wed, 14 Apr 2004 02:47:19 GMT
Here's something even better:
http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFWork
book.html#createDataFormat().  Just call createDataFormat on a HSSFWorkbook
and it returns a HSSFDataFormat object that you can call get format with
strings or indexes as pleased.

Shawn

-----Original Message-----
From: Davinder Kohli [mailto:kdavinder@yahoo.com] 
Sent: Tuesday, April 13, 2004 8:01 PM
To: POI Users List
Subject: RE: Percent Formatting - getBuiltinFormat
ArrayIndexOutOfBoundsException

That's exactly the issue I had with getting the
formats for all decimal numbers, getting the format
for 3,5 or 20 decimal places. I also got the string
format for these from
org.apache.poi.hssf.model.Workbook.

The problem I have is that there is no API to get
Workbook (at least I couldn't find one) so I had to
modify the source code make the getWorkbook() in
HSSFWorkbook as public.

David, is there a better was to get this?
Thanks,
Dave

--- "Bigwood, David" <dbigwood@metatomix.com> wrote:
> Thanks for the input - that did it.
> -DAB
> 
> 
> > -----Original Message-----
> > From: Laubach Shawn Contr OC-ALC/PSB 
> > [mailto:Shawn.Laubach@tinker.af.mil] 
> > Sent: Tuesday, April 13, 2004 4:47 PM
> > To: POI Users List
> > Subject: RE: Percent Formatting - getBuiltinFormat
> 
> > ArrayIndexOutOfBoundsException
> > 
> > 
> > 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
> > 
> > 
> 
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail:
> poi-user-help@jakarta.apache.org
> 



	
		
__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html

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