You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Dylan Kucera <dj...@sympatico.ca> on 2011/08/30 20:57:40 UTC

XSSF sheets and new 2010 ST_CellType - "d" for Date

I've run into a bit of a snag parsing an XSSF Excel sheet.  I'm not sure where 
the limitation lies, whether it's in the ooxml pieces or if it's something 
that the POI API could address.  Perhaps someone that understands the stack 
could respond with some thoughts or insights?

Excel 2010 introduced a new ST_CellType - "d" for Date.  This is described 
briefly on this page:

http://msdn.microsoft.com/en-
us/library/documentformat.openxml.spreadsheet.cellvalues(office.14).aspx

I'm receiving an XSSF sheet that I need to parse that contains some of these 
cells.  However, when I try to get the cell type or value from the cell, I get 
this exception:

org.apache.xmlbeans.impl.values.XmlValueOutOfRangeException: string 
value ''d'' is not a valid enumeration value for ST_CellType in namespace 
http://schemas.openxmlformats.org/spreadsheetml/2006/main
	at 
org.apache.xmlbeans.impl.values.JavaStringEnumerationHolderEx.set_text
(JavaStringEnumerationHolderEx.java)
	at 
org.apache.xmlbeans.impl.values.XmlObjectBase.update_from_wscanon_text
(XmlObjectBase.java)
	at org.apache.xmlbeans.impl.values.XmlObjectBase.check_dated
(XmlObjectBase.java)
	at 
org.apache.xmlbeans.impl.values.JavaStringEnumerationHolderEx.getEnumValue
(JavaStringEnumerationHolderEx.java)
	at 
org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.getT
(Unknown Source)
	at org.apache.poi.xssf.usermodel.XSSFCell.getBaseCellType
(XSSFCell.java)
	at org.apache.poi.xssf.usermodel.XSSFCell.getCellType(XSSFCell.java)

I've worked around the issue for now, through casting my Cell object to 
XSSFCell and using getRawValue whenever this XmlValueOutOfRangeException is 
raised from getCellType, but of course it feels clumsy and fragile.

It would be great to know if this could be enhanced/fixed more robustly and in 
what part of the API that would have to occur (the latter mostly just so I 
know where I should post the enhancement request!).

Thanks everyone, great API by the way, very very handy!
Dylan.



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: XSSF sheets and new 2010 ST_CellType - "d" for Date

Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 30 Aug 2011, Dylan Kucera wrote:
> I've run into a bit of a snag parsing an XSSF Excel sheet.  I'm not sure 
> where the limitation lies, whether it's in the ooxml pieces or if it's 
> something that the POI API could address.  Perhaps someone that 
> understands the stack could respond with some thoughts or insights?
>
> Excel 2010 introduced a new ST_CellType - "d" for Date.

Ah. That'll need the new xsd xml schema. That'll then need compiling to an 
updated ooxml-schemas jar, and finally we could update POI to read 
(though likely not write, for compatibility reasons)

I think the reason for not moving to the newer schema was worry about it 
breaking existing things. If someone had time, it'd be great to check 
exactly what did get changed in the new schema files, and if it was all 
additions of new fields (like this case) or if any existing ones were 
altered or removed?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org