How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

kakadi
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?
Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

Markus Kirsten
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 <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

kakadi
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;
  }
Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

Nick Burch-2
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

kakadi
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
Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

Nick Burch-2
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

kakadi
thanks a lot...
I got it working now
Reply | Threaded
Open this post in threaded view
|

Re: How to identify date cell type using XMLStreamReader (poi's XSSFReader api)

pengfeiji
This post was updated on .
In reply to this post by Markus Kirsten
I have a problem  as follows
public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz) {
        return getJavaDate(date, use1904windowing, tz, false);
 }
When id used this method, How to confirm use1904windowing true or false.


Thanks