CODEWITHSUNDEEP
javaexcel
Clay Banks
Clay Banks

Reputation: 4591

Formatting a Date from an Excel Sheet

I am reading a date column from an Excel Sheet in my Java program via Apache POI that returns the string Fri Jan 16 00:00:00 EST 2015. I need to format this string to 01/16/2016.

I try the following using the SimpleDateFormat to parse the String to a Date Object, then back to the formatted String I need

String inputDate = excelData.get(20).toString(); // Returns Fri Jan 16 00:00:00 EST 201
Date date = new SimpleDateFormat("MM/dd/yyyy").parse(inputDate);
String outputDate = new SimpleDateFormat("MM/dd/yyyy").format(date);

However the following is returned when trying to parse inputDate

Unparseable date: "Fri Jan 16 00:00:00 EST 2015"

Is my inputDate unreadable or am I missing something here? I've also thought of formatting the cell itself to the proper date format instead - thoughts?

Upvotes: 0

Views: 4963

Answers (3)

SatyaTNV
SatyaTNV

Reputation: 4135

Change 

Date date = new SimpleDateFormat("MM/dd/yyyy").parse(inputDate);

to

Date date = new SimpleDateFormat("EEE MMM DD HH:mm:ss zzz yyyy").parse(inputDate);

zzz   Time zone              EST
EEE   Day name in week       Tuesday
MMM   Month in year          July

Reference: SimpleDateFormat doc

Upvotes: 1

JP Moresmau
JP Moresmau

Reputation: 7403

Your code tells SimpleDateFormat to use MM/dd/yyyy format to PARSE the date, so of course it can't work, since this is not the format of the string you get.

The POI FAQ at https://poi.apache.org/faq.html#faq-N1008D gives a snippet of code to read a date:

case HSSFCell.CELL_TYPE_NUMERIC:
     double d = cell.getNumericCellValue();
     // test if a date!
     if (HSSFDateUtil.isCellDateFormatted(cell)) {
       // format in form of M/D/YY
       cal.setTime(HSSFDateUtil.getJavaDate(d));
       cellText =
         (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
       cellText = cal.get(Calendar.MONTH)+1 + "/" +
                  cal.get(Calendar.DAY_OF_MONTH) + "/" +
                  cellText;
     }

Have you tried that?

Upvotes: 1

MetroidFan2002
MetroidFan2002

Reputation: 29908

Your problem is on this line:

Date date = new SimpleDateFormat(**"MM/dd/yyyy"**).parse(inputDate);

The date format you've described looks more like: "DDD MMM DD HH:mm:ss tz yyyy" or something like that.

Use https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html to find the right formatter in order to read the date.

The output looks fine, given you can read the input string format as the right format into a Date.

Upvotes: 1

Related Questions