topic Re: Convert Excel Date in Java Date in Talend Studio

Convert Excel Date in Java Date

Anonymous — Sat, 16 Nov 2024 14:10:31 GMT

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

Re: Convert Excel Date in Java Date

Anonymous — Thu, 30 Oct 2008 14:16:26 GMT

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

José

Re: Convert Excel Date in Java Date

Anonymous — Thu, 30 Oct 2008 15:50:21 GMT

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

Re: Convert Excel Date in Java Date

Anonymous — Fri, 31 Oct 2008 18:48:09 GMT

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

Re: Convert Excel Date in Java Date

Anonymous — Mon, 29 Oct 2012 18:06:12 GMT

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;
    }