You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by kakadi <k....@gmail.com> on 2017/03/22 06:44:55 UTC
How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
I have a simple excel sheet(.xlsx) with a number of rows. For one of cell the
data type is "Date" and I can see the date in correct format in Excel. But
when I read excel using XMLStreamReader(poi's XSSFReader api) I get some
double value for this cell.
I need to be able to:
1. determine data type of cell
2. if it is date data type then I want to convert the double value to date,
as i am not sure which cell is of type date
How can I do this?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-identify-date-cell-type-using-XMLStreamReader-poi-s-XSSFReader-api-tp5727025.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by pengfeiji <15...@qq.com>.
I have a problem as follows
public static Date getJavaDate(double date, boolean use1904windowing,
TimeZone tz) {
return getJavaDate(date, use1904windowing, tz, false);
}
How to confirm use1904windowing true or false.
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-identify-date-cell-type-using-XMLStreamReader-poi-s-XSSFReader-api-tp5727025p5727205.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by kakadi <k....@gmail.com>.
thanks a lot...
I got it working now
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-identify-date-cell-type-using-XMLStreamReader-poi-s-XSSFReader-api-tp5727025p5727103.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by Nick Burch <ap...@gagravarr.org>.
On Wed, 29 Mar 2017, kakadi wrote:
> Can you please give me an example on how to get formatIndex and
> formatString for my above example as isADateFormat(int formatIndex,
> java.lang.String formatString) expects both the parameters
Take a look at XSSFSheetXMLHandler - that shows how to get the format
index and string, then use it when a numeric cell closes
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by kakadi <k....@gmail.com>.
Can you please give me an example on how to get formatIndex and formatString
for my above example
as isADateFormat(int formatIndex, java.lang.String formatString) expects
both the parameters
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-identify-date-cell-type-using-XMLStreamReader-poi-s-XSSFReader-api-tp5727025p5727099.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by Nick Burch <ap...@gagravarr.org>.
On Wed, 22 Mar 2017, kakadi wrote:
> Thanks for reply
> DateUtil.isCellDateFormatted(cell) method needs Cell instance as parameter,
> how do I create Cell instance, as I am using low level XMLStreamReader's
> event based api to parse sheet XML
Try isADateFormat instead:
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html#isADateFormat(int,%20java.lang.String)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by kakadi <k....@gmail.com>.
Thanks for reply
DateUtil.isCellDateFormatted(cell) method needs Cell instance as parameter,
how do I create Cell instance, as I am using low level XMLStreamReader's
event based api to parse sheet XML
I am using XMLStreamReader as I need to process very large Excel and I am
getting Out of memory errors with other APIs/approches
My example code snippet
......
this.xmlReader.next();
if (this.xmlReader.isStartElement())
{
if (this.xmlReader.getLocalName().equals("c"))
{
String cellID = this.xmlReader.getAttributeValue(null, "r");
CellReference cellReference = new CellReference(cellID);
String colName =
CellReference.convertNumToColString(cellReference.getCol());
String cellType = this.xmlReader.getAttributeValue(null, "t");
String cellValue = getCellValue(cellType);
rowValues.add(cellValue);
}
}
}
private String getCellValue(String cellType) throws XMLStreamException
{
String value = EMPTY; // by default
while (this.xmlReader.hasNext())
{
this.xmlReader.next();
if (this.xmlReader.isStartElement())
{
if (this.xmlReader.getLocalName().equals("v"))
{
if ((cellType != null) && cellType.equals("s"))
{
int idx = Integer.parseInt(this.xmlReader.getElementText());
return new
XSSFRichTextString(this.stringsTable.getEntryAt(idx)).toString();
}
else
{
return this.xmlReader.getElementText();
}
}
}
else if (this.xmlReader.isEndElement() &&
this.xmlReader.getLocalName().equals("c"))
{
break;
}
}
return value;
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-identify-date-cell-type-using-XMLStreamReader-poi-s-XSSFReader-api-tp5727025p5727027.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to identify date cell type using XMLStreamReader (poi's
XSSFReader api)
Posted by Markus Kirsten <mk...@gmail.com>.
Hi,
You can use
1) DateUtil.isCellDateFormatted(cell) to determine whether a cell is formatted as a date, and
2) DateUtil.getJavaDate(date) where date is a double (that you can get from the cell)
Best regards,
Markus
> On 22 Mar 2017, at 07:44, kakadi <k....@gmail.com> wrote:
>
> I have a simple excel sheet(.xlsx) with a number of rows. For one of cell the
> data type is "Date" and I can see the date in correct format in Excel. But
> when I read excel using XMLStreamReader(poi's XSSFReader api) I get some
> double value for this cell.
>
> I need to be able to:
>
> 1. determine data type of cell
>
> 2. if it is date data type then I want to convert the double value to date,
> as i am not sure which cell is of type date
>
> How can I do this?
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-identify-date-cell-type-using-XMLStreamReader-poi-s-XSSFReader-api-tp5727025.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org