Convert Excel Date in Java Date

One Star

Convert Excel Date in Java Date

Hi,
I try to convert Excel date in Var of tMap.
The code of var is the follow:
java.util.GregorianCalendar(1900, java.util.Calendar.JANUARY,1).add(java.util.Calendar.DATE, java.lang.Integer.parseInt(row3.DeliveryAttributSetDate)-1).
When I execute the followinf error appeare:
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
java.util cannot be resolved to a type
at projectName.testconvertexceldate_0_1.testConvertExcelDate.tFileInputExcel_1Process(testConvertExcelDate.java:5445)
at projectName.testconvertexceldate_0_1.testConvertExcelDate.runJobInTOS(testConvertExcelDate.java:5959)
at projectName.testconvertexceldate_0_1.testConvertExcelDate.main(testConvertExcelDate.java:5848)
You are welcome if you have a good idea
Max
One Star

Re: Convert Excel Date in Java Date

Hello,
What format is your Excel date?
With the TalendDate.parseDate() function you should be able to convert your date.

José
One Star

Re: Convert Excel Date in Java Date

Thank you, it's resolve my problem, but I don't understand why
"java.util.GregorianCalenadr cannot be resolved"
Max
Employee

Re: Convert Excel Date in Java Date

Hello,
I think that your var was missing a "new" declaration.
One Star

Re: Convert Excel Date in Java Date

I am still working on a better way myself but in the mean time try this:
    /**
* ExcelDateParse: Parses a Java Date from an Excel Date Format
* {talendTypes} String
* {Category} User Defined
* {param} int(date) input: the excel date int to convert
* {example} ExcelDateParse(38838) # result: Tue May 02 00:00:00 PDT 2006
*/
public static Date ExcelDateParse(int ExcelDate){
Date result = null;
try{
GregorianCalendar gc = new GregorianCalendar(1900, Calendar.JANUARY, 1);
gc.add(Calendar.DATE, ExcelDate - 1);
result = gc.getTime();
} catch(RuntimeException e1) {}
return result;
}

/**
* ExcelTimeParse: Parses a Java Time String from an Excel Formated Time
* {talendTypes} String
* {Category} User Defined
* {param} String(date) input: the excel time String to convert
* {example} ExcelTimeParse("0.6549") # result: 15:43:03 PM
*/
public static String ExcelTimeParse(String ExcelDate){
String result = "";
try{
DateFormat format = new SimpleDateFormat("HH:mm:ss a");
format.setTimeZone(TimeZone.getTimeZone("UTC"));
double frac = Double.parseDouble(ExcelDate);
long day = (long) Math.floor(frac);
frac = frac - day;
Date time = new Date((long) ( 86400000l * frac));
result = format.format(time);
} catch(RuntimeException e1) {}
return result;
}
// this next one is a Work in progress and not functional:
/**
* ExcelTimeParse: Parses a Java Time String from an Excel Formated Time
* {talendTypes} String
* {Category} User Defined
* {param} String(date) input: the excel time String to convert
* {example} ExcelTimeParse("38838.6549") # result: Tue May 02 15:43:03 PM
*/
public static String ExcelDateTimeParse(String ExcelDateTime, String DateFormat){
String DefaultFormat = "MM/dd/yyyy HH:mm:ss a";
String result = "";

if(DateFormat.isEmpty()){
DateFormat = DefaultFormat;
}

try{
DateFormat format = new SimpleDateFormat(DateFormat);
format.setTimeZone(TimeZone.getTimeZone("UTC"));
double frac = Double.parseDouble(ExcelDateTime);
long day = (long) Math.floor(frac);
frac = frac - day;
Date time = new Date((long) ( 86400000l * frac));
result = format.format(time);
} catch(RuntimeException e1) {}
return result;
}