File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Servlets and the fly likes Displaying timestamp field in excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Displaying timestamp field in excel" Watch "Displaying timestamp field in excel" New topic
Author

Displaying timestamp field in excel

aliya sharma
Ranch Hand

Joined: Aug 11, 2004
Posts: 48
Hello,
I have a program where i get the result set from SQl Server and display it on MS-excel. I'm having trouble displaying the timestamp field. ITs showing some mess in excel, but if i click on it it displays the correct value on the top display bar, but not on the spreadsheet. THis is my related code:
JavaBean:

Servlet(that forwards the result set to Excel)

I'm sending it as a string so it would display the exact format in the SQL server that is yyyy-mm-dd hh:mm:ss. Any suggestions??
aliya sharma
Ranch Hand

Joined: Aug 11, 2004
Posts: 48
One more thing: the date in sql server is shown as:
2003-11-18 15:43:49.910
Does anyone know what ".910" mean???. Its of type datetime in SqlServer. THanks
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by aliya sharma:
Does anyone know what ".910" mean???.


.910 is milisecods .

Actually you are getting
18 Novemeber 2003 15 hours 43 minute 49 secods and 910 miliseconds.

since you need to display date in excel why don't you set/get date as string and before setting the date format your date into desired 'date format' .

I haven't worked with sql server !!! but I think there is a convert function to change date formats.
[ October 18, 2004: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
aliya sharma
Ranch Hand

Joined: Aug 11, 2004
Posts: 48
since you need to display date in excel why don't you set/get date as string and before setting the date format your dare it into desired 'date format' .
THis is how im setting and getting the date:

COuld please give me an example of you way of doing it.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

if you were using oracle then I had recommended to use to_char() method.

but still you can do it in either way. first format your date in desired way. you can do this by

CONVERT(CHAR(8),your date column ,110) for MM-DD-YYYY

Then while retrieving date from resultset retrieve it as rs.getString() instead of rs.getTimestamp()

Change your method
to

public String getCreateTstamp() {
return createTstamp;
}

public void setCreateTstamp(String create) {
createTstamp = create;
}

hope this will work !!!

one more thing I dont remeber exactly but if your date will be like 18/11/2004 excel automatically try to devide (I am not very much sure )

to avoid this append a space before your date.
[ October 18, 2004: Message edited by: Shailesh Chandra ]
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

I would recommend using java.text.DateFormat and java.text.SimpleDateFormat for formatting of the returned date.


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

java.text.DateFormat and java.text.SimpleDateFormat are not bad option

but in my applications I keep queries in XML so changing the format of date is bit easier.
[ October 19, 2004: Message edited by: Shailesh Chandra ]
aliya sharma
Ranch Hand

Joined: Aug 11, 2004
Posts: 48
Hey,
i could dispaly the date part of datetime in excel with no problem..and thats what my requirement was..So, just ignored the time!!!..THanks for your help Shailesh and Sonny..Really appreciated!
THIS IS RAMESH KUMAR
Greenhorn

Joined: Oct 19, 2004
Posts: 2
Originally posted by aliya sharma:
Hello,
I have a program where i get the result set from SQl Server and display it on MS-excel. I'm having trouble displaying the timestamp field. ITs showing some mess in excel, but if i click on it it displays the correct value on the top display bar, but not on the spreadsheet. THis is my related code:
JavaBean:

Servlet(that forwards the result set to Excel)

I'm sending it as a string so it would display the exact format in the SQL server that is yyyy-mm-dd hh:mm:ss. Any suggestions??
THIS IS RAMESH KUMAR
Greenhorn

Joined: Oct 19, 2004
Posts: 2
If it is displaying correctly in the above toolbar that means the date what u r getting is correct. The problem may be with column width in excel. Try to increase the column width and try.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

"THIS IS RAMESH KUMAR"-
Ramesh please adjust your displayed name to meet the

JavaRanch Naming Policy.

You can change it

here.

Thanks!

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Displaying timestamp field in excel