This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes Other Open Source Projects and the fly likes POI reads date format in excel as datetime Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "POI reads date format in excel as datetime " Watch "POI reads date format in excel as datetime " New topic

POI reads date format in excel as datetime

Ahsan Bagwan
Ranch Hand

Joined: Oct 05, 2010
Posts: 219

I am using POI 3.9 to read Excel file in my application. Though I had the issue of reading dates as doubles that was hushed with the help of DataFormatter class method.

However when using formatCellValue() the dates stored in excel are returned as date with time information regardless if it was a Date or DateTime value (when exported from MySQL).

So how would I read both formats separately? Here is the relevant part of the code

Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42958
What is the connection between "data in an Excel spreadsheet" and "data exported from MySQL"?
Ahsan Bagwan
Ranch Hand

Joined: Oct 05, 2010
Posts: 219

Sorry if the details are blurry. I am required to insert date values into the my table and along with it is also a column timestamp.

In my excel both date and timestamp exists and are shown correctly on checking the cell. But when I read it into my Java app, I want the date cell from excel as 'yyyy-MM-dd' and timestamp cell as 'yyyy-MM-dd HH:mm:ss' . Now it is parsed in the Date form.

How I would differentiate between these two formats while using POI?

My use of the word 'datetime' in the above post might be the cause of confusion.
Paul Clapham

Joined: Oct 14, 2005
Posts: 19719

I think you are trying to make a distinction which does not exist. Consider this statement from the POI documentation for the Cell interface which I happened to stumble across while researching your post:

Note - There is actually no 'DATE' cell type in Excel. In many cases (when entering date values), Excel automatically adjusts the cell style to some date format, creating the illusion that the cell data type is now something besides CELL_TYPE_NUMERIC. POI does not attempt to replicate this behaviour.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: POI reads date format in excel as datetime
It's not a secret anymore!