How to Read Excel Cell Having Date with Apache Poi

How to read Excel cell having Date with Apache POI?

NOTE: HSSFDateUtil is deprecated

If you know which cell i.e. column position say 0 in each row is going to be a date, you can go for
row.getCell(0).getDateCellValue() directly.

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#getDateCellValue()

UPDATE: Here is an example - you can apply this in your switch case code above. I am checking and printing the Numeric as well as Date value. In this case the first column in my sheet has dates, hence I use row.getCell(0).

You can use the if (HSSFDateUtil.isCellDateFormatted .. code block directly in your switch case.

if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
    System.out.println ("Row No.: " + row.getRowNum ()+ " " +
        row.getCell(0).getNumericCellValue());

    if (HSSFDateUtil.isCellDateFormatted(row.getCell(0))) {
        System.out.println ("Row No.: " + row.getRowNum ()+ " " + 
            row.getCell(0).getDateCellValue());
    }
}

The output is

Row No.: 0 39281.0
Row No.: 0 Wed Jul 18 00:00:00 IST 2007
Row No.: 1 39491.0
Row No.: 1 Wed Feb 13 00:00:00 IST 2008
Row No.: 2 39311.0
Row No.: 2 Fri Aug 17 00:00:00 IST 2007

Java POI - read date from Excel file

Dates cannot be stored in CELL_TYPE_STRING cell. You should store it in CELL_TYPE_NUMERIC cell. See here for details.

You also missed break keyword after first case. So if cell is Cell.CELL_TYPE_STRING then also

System.out.print(cell.getNumericCellValue() + "\t\t");

is called.

So it should be:

switch(cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        System.out.print(cell.getStringCellValue() + "\t\t");
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
            System.out.print(dateFormat.format(cell.getDateCellValue()) + "\t\t");
        } else {
            System.out.print(cell.getNumericCellValue() + "\t\t");
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.print(cell.getBooleanCellValue() + "\t\t");
        break;
}

Guide how date cell is read by Apache poi

The right way is using getDataFormatString as it is done in this answer Validating the excel date for MM/dd/yyyy format
But the builtin formats are limited.

If the limitation of the built-in formats bothers you, casting the value as a string is done through Data formatter

DataFormatter formatter = new DataFormatter();
String formattedValue = formatter.formatCellValue(cell);

The validation of the date field is already performed from the Excel file, and the date is read as a string if it is not in the right format.

Read excel 'Formula Date cell' value in a specific format using Apache POI (excel has STRING FORMULA and INT FORMULA types)

The issue was to check if cell is in dateformat or not, then set the date format, after making below changes to condition, able to get the value in expected format.
Thanks for all the support.

  case NUMERIC:
                       //  HSSFCellStyle style = cell.getCellStyle();
                       CellStyle style = cell.getCellStyle();
                         if(style == null) {
                             cell.getNumericCellValue();
                            //text.append( (char) cell.getNumericCellValue() );
                         } else if(DateUtil.isCellDateFormatted(cell)) {
                             cell.setCellStyle(style);
                             SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd");
                             //String formattedCell = datetemp.format(cell.getDateCellValue());

                             Date date = cell.getDateCellValue();
                                String formattedCell = datetemp.format(date);
                                return formattedCell;
                         }

How to read the exact date format from Excel cell having formula field with Apache POI?

What have you tried? Please try using DataFormatter as shown in first part of Getting the cell contents. If that not fulfills your needs then please show used code in your question together with what the code produces and what you wants to get.
@axel ritcher

This worked

I'm reading excel file using Apache POI. Unable to read Date. In excel,date format 2017-03-15 6:00(cell format=custom) & using poi,reading 42809.25

If you know which cell i.e. column position say 2 in each row is going to be a date, you can go for row.getCell(2).getDateCellValue() directly.

Check out the method in POI's DateUtil class for dealing with dates in Excelsheets, DateUtil.isCellDateFormatted()

or you can get date as shown below

if (DateUtil.isCellDateFormatted(cell))
{
try {
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  cellValue = sdf.format(cell.getDateCellValue());

} catch (ParseException e) {
        e.printStackTrace();
}
}

Read data from excel sheet in java with Apache POI

tl;dr

row                              // Starting with a Poi `Row` object.
.getCell(6)                      // Extract a Poi `Cell` object.
.getDateCellValue()              // Extract value from spreadsheet as a `java.util.Date` object. This terrible class is now legacy. Immediately convert to java.time.Instant as seen on next line.
.toInstant()                     // Convert from legacy `java.util.Date` class to modern `java.time.Instant`. Both represent a moment in UTC.
.atZone(                         // Adjusting from UTC to a time zone.
    ZoneId.of( "Asia/Tokyo" )    // Specify a time zone to adjust from UTC (an offset of zero hours-minutes-seconds). 
)                                // Returns a `ZonedDateTime` object. Same moment, different wall-clock time.
.toLocalDate()                   // Extract the date-only portion. Or call `toLocalTime` for the time-of-day portion.

Details

The other Answers correctly show how to get a java.util.Date object. But that class is (a) terrible, and (b) now legacy, supplanted years ago by the modern java.time classes. You need to go further to get a proper solution.

After getting your java.util.Date object:

java.util.Date javaUtilDate = row.getCell(6).getDateCellValue() ;

…you have a moment in time in UTC. Unfortunately, following the advice to call Date::toString:

row.getCell(6).getDateCellValue().toString()

…creates a confusing the result. That method applies the JVM’s current default time zone, adjusting from UTC to some zone. That creates the illusion that this time zone was part of the java.util.Date, but that is not true. And this behavior means your results will vary at runtime depending on whatever the current default time zone might be. Be aware that not only the user can change the default time zone, but so can any code in any thread of any app running within that JVM.

For predictable results, you should specify the desired/expected time zone.

To do so, immediately convert your java.util.Date to its replacement, the Instant class. Both represent a moment in UTC. Notice the new conversion methods added to the old legacy classes.

Instant instant = javaUtilDate.toInstant() ;

Specify a proper time zone name in the format of Continent/Region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 2-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId zoneId = ZoneId.of( "Africa/Tunis" ) ;

If you want to use the JVM’s current default time zone, ask for it explicitly, and pass as an argument. If omitted, the code becomes ambiguous to read in that we do not know for certain if you intended to use the default or if you, like so many programmers, were unaware of the issue.

ZoneId zoneId = ZoneId.systemDefault() ;  // Get JVM’s current default time zone.

Apply that time zone to your moment in UTC.

ZonedDateTime zdt = instant.atZone( zoneId ) ;

The resulting ZonedDateTime object represents the same moment, the same simultaneous point on the timeline as the Instant. But its wall-clock time has been adjusted to that used by the people of a particular region (a time zone).

You asked:

How can I read the time?

How can I read the date correctly?

You can use a DateTimeFormatter to print either or both in any format you desire, or even automatically localize to the human language and cultural norms defined in a Locale object.

Or you can extract the date and time values as separate objects.

LocalDate localDate = zdt.toLocalDate() ;
LocalTime localTime = zdt.toLocalTime() ;

Table of date-time types in Java, both modern and legacy

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

How to Read Excel Cell Having Date with Apache Poi