How to fetch Date Field from excel sheet in Java

[Dinesh Remje]

I am writing a program where i am fetching the records from an excel sheet and after getting those records i am trying to update it in database.
Now i am able to fetch the records but i am not able to set the date field in database.
In excel sheet the date is in date field format. So how can i fetch the date field from excel sheets.

[Dinesh Remje]

i am able to get the contents from the excel sheets with the help of getContents() method. But for inserting into database i want to get the date field in date format itself so how can i do that.

[Soumyajit Hazra]

If you are getting the content from the excel sheet as String then you can use java SimpleDateFormat class to format it according to your db format.

[Dinesh Remje]

Ya i am able to do so. But the problem is i am getting time also with it. And i just want the date. So how can i ignore time and just take the date value.
This is what i have done

public Date startdate = new Date();
public String strtdate;
DateFormat formatter ;

System.out.println("StartDate : "+ rowData[j].getContents());
strtdate=rowData[j].getContents();
formatter = new SimpleDateFormat("yyyy-mm-dd");
System.out.println(strtdate);
 startdate = (Date)formatter.parse(strtdate);
  System.out.println("Startdate value..."+startdate);

And this is what i get output for printing strdate

06 05 2011 12:00:00

java.text.ParseException: Unparseable date: "06 05 2011 12:00:00"
        at java.text.DateFormat.parse(DateFormat.java:337)
        at summerinterndates.Main.contentReading(Main.java:143)
        at summerinterndates.Main.init(Main.java:51)
        at summerinterndates.Main.main(Main.java:290)

The error shows that i am not able to parse the date.
So what should i do in this case.

[amit punekar]

Hello,
If you are using Apache POI for accessing the excel sheet then you can use getDateValue for a Cell. This is more better approach instead of reading it as a string value and then converting it.

Regards,
Amit

[Soumyajit Hazra]

The date format what you are getting is "dd mm yyyy hh:MM:ss" not "yyyy-mm-dd" so you are getting the exception.

[Dinesh Remje]

I am usin jxl approach.
But in excel sheet i have set that field as "YYYY-MM-DD" format. So how i am getting the time here.
Shall i set that field to text format in excel sheet and see the changes???

[Soumyajit Hazra]

# java.text.ParseException: Unparseable date: "06 05 2011 12:00:00"
# at java.text.DateFormat.parse(DateFormat.java:337)
# at summerinterndates.Main.contentReading(Main.java:143)
# at summerinterndates.Main.init(Main.java:51)
# at summerinterndates.Main.main(Main.java:290)

From the exception we can see that "06 05 2011 12:00:00" is the string you are trying to format and which is not yyyy-mm-dd. You can use something like this

        String str="06 05 2011 12:00:00";	// fetching from excel in your case	
		SimpleDateFormat sdf1=new SimpleDateFormat("dd mm yyyy hh:MM:ss");
		Date d=sdf1.parse(str);
		SimpleDateFormat sdf2=new SimpleDateFormat("yyyy-mm-dd");
		System.out.println(sdf2.format(d));

and you will get "2010-05-06" as output

[Dinesh Remje]

Thanks Soumyajit Hazra,
What i have done is format the date field in excel sheet as a text field. I was successfully able to get the date in format i wanted.
But now there is an other problem which i am facing right now.

    if(con!=null)
    {
        String query = "update summerintern set startdate=? where firstname=? and lastname= ?;";
        PreparedStatement stmt = con.prepareStatement(query);
        [b]  stmt.setDate(1, startdate);[/b]
        stmt.setString(2,firstname);
        stmt.setString(3, lastname);
        stmt.executeUpdate();
        System.out.println("Updated............");
    }
    else
        System.out.println("Error in Updation");
}
catch(Exception e) {
    System.out.println("Error: "+e.getMessage());
}

the line which is marked bold, i am getting an error there. Where my netbeans specify me to do "stmt.setDate(1, (java.sql.Date) startdate);" but again in gives an exception while updating the table that "Error: java.util.Date cannot be cast to java.sql.Date" so what should be done in this case.

[Soumyajit Hazra]

What is the type of "startdate"? Remember SimpleDateFormat returns java.util.Date and if you want to use setDate() you require java.sql.Date. So convert the util date object to sql date object. Check the Date API for the related conversion help.

[Rob Spoor]

Dinesh Remje wrote:I am usin jxl approach.

JXL also has proper support for dates. Cell has a sub interface DateCell which has methods to get both the Date object and the DateFormat used. So just cast the current Cell to DateCell and use its getDate() method.

[Rob Spoor]

Also, Dinesh, I've modified your last post to decrease the indentation. That way the forum doesn't need horizontal scroll bars. Next time please keep your indentation a bit in check when posting code. We like proper indentation, but not if each line has an indentation of several tabs.