CODEWITHSUNDEEP
javaexcelapache-poi
Mike
Mike

Reputation: 63

Read data from excel sheet in java with Apache POI

I have a data in an excel sheet that I need to read in java, I am able to read normal Strings but when I try to read a cell that contains a date and time (28/5/2018 10:00) I get only the date as 28-May-2018.

This is the excel cell

enter image description here

This code read: 28-May-2018

enter image description here

row.getCell(6).toString()

This code throws an exception:

row.getCell(6).getStringCellValue()

How can I read the time?

How can I read the date correctly?

Upvotes: 5

Views: 1760

Answers (4)

Axel Richter
Axel Richter

Reputation: 61852

To get the cell values as formatted strings as shown in the Excel cells, do using DataFormatter.

Example:

...
DataFormatter formatter = new DataFormatter(); 
...
Row row = ...;
...
Cell cell = row.getCell(6);
String value = formatter.formatCellValue(cell);
System.out.println(value);
...

And because the value might be result of a formula, do using using a DataFormatter together with a FormulaEvaluator: 

...
DataFormatter formatter = new DataFormatter(); 
...
Workbook workbook = ...;
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
...
Row row = ...;
...
Cell cell = row.getCell(6);
String value = formatter.formatCellValue(cell, evaluator);
System.out.println(value);
...

Upvotes: 0

Basil Bourque
Basil Bourque

Reputation: 338136

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?

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.

Upvotes: 3

Meow
Meow

Reputation: 135

I hope this would help

try (FileInputStream fis = new FileInputStream(filename)) {
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet sheet = workbook.getSheetAt(0);

        // Read a cell the first row and sixth cell.
        HSSFCell cell = sheet.getRow(0).getCell(6);


        // Using HSSFDateUtil to check if a cell contains a date.
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            System.out.println("The cell contains a date value: "
                + cell.getDateCellValue());
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

Upvotes: 0

dinonugs
dinonugs

Reputation: 69

Try 

row.getCell(6).getDateCellValue();

And if you still need it to be a string, try

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

Upvotes: 3

Related Questions