Home : Converting Excel Automation Date Type to java.util.Date
Q89771 - HOWTO: Converting Excel Automation Date Type to java.util.Date

Converting Excel Automation Date Type to java.util.Date

 

This article applies to Java programs accessing an Excel application. Reading date types in Excel from your Java program have idiosyncratic behaviors depending on how you access the cell(s) containing the date. To illustrate, let's take an Excel worksheet that looks as follows:

 

Assume that you need to read cell A2 which contains the date 05-Mar-2004 and the time 10:17:40 AM and have this converted to java.util.Date. If you try to do the following in your Java program:

      // Read a single cell and print its content
      Range cell = sheet.getRange("A2", null);
      System.out.println("Date = " + cell.getValue2());

You will get the following output:

      Date = 38051.42893935185

The result (38051.42893935185) is what Microsoft calls a serial value that represents the date 05-Mar-2004 10:17:40 AM. Here's more information about date and time taken from the Microsoft Excel Help:

Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Now, if you need to convert this serial value date into java.util.Date, you can certainly create a nice algorithm to do that. But before you start brainstorming on the logic of the algorithm (we know you're extremely excited!), rein in your horses because theres an easier way to do this (sorry to douse your excitement like that). If you access the cell contents as a two-dimensional Object array, you will get a different output - a human-readable date type. Huh?! Believe us, it sounds more complicated than it actually is. To access a range of cells as two-dimensional Object array, you do as follows:

      // Read an array of cells and print its content
      Range cells = sheet.getRange("A2:A3", null);
      Object[][] date = (Object[][])cells.getValue(null);
      System.out.println("Date = " + date[0][0]);
      System.out.println("Date = " + date[1][0]);

If you do it this way, you will get the following output:

      Date = Fri Mar 05 10:17:40 PST 2004
      Date = Wed Jan 01 00:00:00 PST 2003

The above format is certainly a lot cleaner to convert into java.util.Date. All you need to do is parse this String type, retrieve individual parts of the date (day, month, year), and pass these to the java.util.Date constructor.

The Java code that demonstrates what we have discussed here can be downloaded ExcelDate.zip (it leaves the actual parsing to you).

Related Articles
No Related Articles Available.

Article Attachments
ExcelDate.zip

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 6/23/2006.
Last Modified on 7/12/2006.
Last Modified by No Author Name Available!.
Article has been viewed 13698 times.
Rated 6 out of 10 based on 16 votes.
Print Article
Email Article