You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by noobguru <no...@gmail.com> on 2009/02/27 19:15:30 UTC
Setting Date object in Excel File
Hi,
So I'm trying to call the Cell cell.setCellValue(java.util.Date) method.
It puts the date in my excel file great, however it's showing the date as my
default timezone of my system. I don't want it to show that, I want it to
show a date according to any time zone I want. Is there a way to do this?
Thanks,
noobguru
--
View this message in context: http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22251385.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting Date object in Excel File
Posted by MSB <ma...@tiscali.co.uk>.
Sorry, forgot about the java.util.Calendar class because I was assuming you
wanted to offer the user of the spreadsheet the ability to alter the
timezone and see what the time was there. This class allows you to set the
timezone explicitly so that you should get a date/time value adjusted
accordingly.
If you are not familair with this class, have a look at the static
getInstance() methods. They are overloaded to allow you to set the Timezone
and even the Locale.
noobguru wrote:
>
> Hi,
>
> So I'm trying to call the Cell cell.setCellValue(java.util.Date)
> method. It puts the date in my excel file great, however it's showing the
> date as my default timezone of my system. I don't want it to show that, I
> want it to show a date according to any time zone I want. Is there a way
> to do this?
>
> Thanks,
> noobguru
>
--
View this message in context: http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22252291.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting Date object in Excel File
Posted by MSB <ma...@tiscali.co.uk>.
Assuming that you are talking about inserting a formatted String into a cell
that is typed to hold text then I can forsee one problem - what if the user
wishes to include the date value in some forumla; to calculate elapsed time
for example?
Dave Fisher-6 wrote:
>
>> My recommendation would have a method called setCellValue(Date d,
>> TimeZone
>> tz)
>> which creates a GregorianCalendar with the timezone given. Then the
>> date set
>> in the timezone would be exactly what I want it.
>>
>> The setCellValue(Date date) method as it is right now already
>> creates a
>> GregorianCalendar (but it creates it with the default time zone) --
>> why
>> can't we make this configurable to what the user wants?
>
> Probably the best way is have a routine that allowed a formatted
> string to be used.
>
> It would have to be something like setCellValueDate(String
> date,java.text.dateFormat formatter).
>
> There would also be a getCellValueDate(java.text.dateFormat formatter).
>
> It is a thought. It might solve some other issues.
>
> Dave
>
>
>>
>>
>> noobguru
>>
>>
>>
>>
>> MSB wrote:
>>>
>>> Yes, but you are going to need to use formulae to do so I think.
>>> Have a
>>> look at this link;
>>>
>>> http://www.exceltip.com/st/Converting_Time_Values_from_One_Time_Zone_to_Another/834.html
>>>
>>> it sort of gives you an idea of how to use a formula to do the
>>> work. Your
>>> best bet may be to have another sheet where there are all the
>>> possible
>>> timezones entered accompanied by the number of hours each is either
>>> in
>>> front of or behind your default. Then you can link into them using
>>> formulae I would guess. The timzone could be shown alongside each
>>> date/time value and - if the user changes it - the formula could be
>>> used
>>> to recalculate the value displayed in the cell. Not something I
>>> have done
>>> myself but it must be possible.
>>>
>>> I could very well be wrong but I do not think that it is possible
>>> to just
>>> use the formatting options available on the cell nor do I think
>>> there is
>>> an option to change your time zone at the global - application -
>>> level as
>>> there is with OpenOffice.
>>>
>>>
>>> noobguru wrote:
>>>>
>>>> Hi,
>>>>
>>>> So I'm trying to call the Cell cell.setCellValue(java.util.Date)
>>>> method. It puts the date in my excel file great, however it's
>>>> showing the
>>>> date as my default timezone of my system. I don't want it to show
>>>> that,
>>>> I want it to show a date according to any time zone I want. Is
>>>> there a
>>>> way to do this?
>>>>
>>>> Thanks,
>>>> noobguru
>>>>
>>>
>>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22254001.html
>> Sent from the POI - User mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22260351.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting Date object in Excel File
Posted by Dave Fisher <da...@jmlafferty.com>.
> My recommendation would have a method called setCellValue(Date d,
> TimeZone
> tz)
> which creates a GregorianCalendar with the timezone given. Then the
> date set
> in the timezone would be exactly what I want it.
>
> The setCellValue(Date date) method as it is right now already
> creates a
> GregorianCalendar (but it creates it with the default time zone) --
> why
> can't we make this configurable to what the user wants?
Probably the best way is have a routine that allowed a formatted
string to be used.
It would have to be something like setCellValueDate(String
date,java.text.dateFormat formatter).
There would also be a getCellValueDate(java.text.dateFormat formatter).
It is a thought. It might solve some other issues.
Dave
>
>
> noobguru
>
>
>
>
> MSB wrote:
>>
>> Yes, but you are going to need to use formulae to do so I think.
>> Have a
>> look at this link;
>>
>> http://www.exceltip.com/st/Converting_Time_Values_from_One_Time_Zone_to_Another/834.html
>>
>> it sort of gives you an idea of how to use a formula to do the
>> work. Your
>> best bet may be to have another sheet where there are all the
>> possible
>> timezones entered accompanied by the number of hours each is either
>> in
>> front of or behind your default. Then you can link into them using
>> formulae I would guess. The timzone could be shown alongside each
>> date/time value and - if the user changes it - the formula could be
>> used
>> to recalculate the value displayed in the cell. Not something I
>> have done
>> myself but it must be possible.
>>
>> I could very well be wrong but I do not think that it is possible
>> to just
>> use the formatting options available on the cell nor do I think
>> there is
>> an option to change your time zone at the global - application -
>> level as
>> there is with OpenOffice.
>>
>>
>> noobguru wrote:
>>>
>>> Hi,
>>>
>>> So I'm trying to call the Cell cell.setCellValue(java.util.Date)
>>> method. It puts the date in my excel file great, however it's
>>> showing the
>>> date as my default timezone of my system. I don't want it to show
>>> that,
>>> I want it to show a date according to any time zone I want. Is
>>> there a
>>> way to do this?
>>>
>>> Thanks,
>>> noobguru
>>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22254001.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting Date object in Excel File
Posted by noobguru <no...@gmail.com>.
Hey,
My recommendation would have a method called setCellValue(Date d, TimeZone
tz)
which creates a GregorianCalendar with the timezone given. Then the date set
in the timezone would be exactly what I want it.
The setCellValue(Date date) method as it is right now already creates a
GregorianCalendar (but it creates it with the default time zone) -- why
can't we make this configurable to what the user wants?
noobguru
MSB wrote:
>
> Yes, but you are going to need to use formulae to do so I think. Have a
> look at this link;
>
> http://www.exceltip.com/st/Converting_Time_Values_from_One_Time_Zone_to_Another/834.html
>
> it sort of gives you an idea of how to use a formula to do the work. Your
> best bet may be to have another sheet where there are all the possible
> timezones entered accompanied by the number of hours each is either in
> front of or behind your default. Then you can link into them using
> formulae I would guess. The timzone could be shown alongside each
> date/time value and - if the user changes it - the formula could be used
> to recalculate the value displayed in the cell. Not something I have done
> myself but it must be possible.
>
> I could very well be wrong but I do not think that it is possible to just
> use the formatting options available on the cell nor do I think there is
> an option to change your time zone at the global - application - level as
> there is with OpenOffice.
>
>
> noobguru wrote:
>>
>> Hi,
>>
>> So I'm trying to call the Cell cell.setCellValue(java.util.Date)
>> method. It puts the date in my excel file great, however it's showing the
>> date as my default timezone of my system. I don't want it to show that,
>> I want it to show a date according to any time zone I want. Is there a
>> way to do this?
>>
>> Thanks,
>> noobguru
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22254001.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting Date object in Excel File
Posted by MSB <ma...@tiscali.co.uk>.
Yes, but you are going to need to use formulae to do so I think. Have a look
at this link;
http://www.exceltip.com/st/Converting_Time_Values_from_One_Time_Zone_to_Another/834.html
it sort of gives you an idea of how to use a formula to do the work. Your
best bet may be to have another sheet where there are all the possible
timezones entered accompanied by the number of hours each is either in front
of or behind your default. Then you can link into them using formulae I
would guess. The timzone could be shown alongside each date/time value and -
if the user changes it - the formula could be used to recalculate the value
displayed in the cell. Not something I have done myself but it must be
possible.
I could very well be wrong but I do not think that it is possible to just
use the formatting options available on the cell nor do I think there is an
option to change your time zone at the global - application - level as there
is with OpenOffice.
noobguru wrote:
>
> Hi,
>
> So I'm trying to call the Cell cell.setCellValue(java.util.Date)
> method. It puts the date in my excel file great, however it's showing the
> date as my default timezone of my system. I don't want it to show that, I
> want it to show a date according to any time zone I want. Is there a way
> to do this?
>
> Thanks,
> noobguru
>
--
View this message in context: http://www.nabble.com/Setting-Date-object-in-Excel-File-tp22251385p22252079.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org