New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for dates without time #3

Closed
eazb opened this Issue Mar 21, 2013 · 11 comments

Comments

3 participants
@eazb

eazb commented Mar 21, 2013

In workbook.py, there is currently only support for the full datetime type. It would be beneficial to include support for datetime.time and datetime.date as well.

A simple implementation would be:

if isinstance(date, datetime.date):  # separate handling for partial date
    # date in excel is number of days since Jan1 1900
    d = datetime.datetime.fromordinal( date.toordinal() ) - datetime.datetime( 1900, 1, 1 )
    number = d.days
elif isinstance(date, datetime.time):  # separate handling for partial time
    # time in excel is float between 0 and 1. Use time in seconds from 00:00, divided by seconds in a day
    number = (date.hour_60_60.0 + date.minute_60.0 + date.second + 1.0_date.microsecond/1e6)/(24_60_60.0)
else:  # existing version for full datetime
    number = self._convert_date_time(date)


Thanks for your efforts!

Eduardo
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 21, 2013

Owner

Thanks for the input.

I'll keep it in mind and see if there are any other requests for this feature.

Owner

jmcnamara commented Mar 21, 2013

Thanks for the input.

I'll keep it in mind and see if there are any other requests for this feature.

@ghost ghost assigned jmcnamara Mar 21, 2013

@JoshEnglish

This comment has been minimized.

Show comment
Hide comment
@JoshEnglish

JoshEnglish Mar 21, 2013

I've run into this as well. I just wrote a quick conversion function:

def date_to_datetime(date):
return datetime.datetime.combine(date, datetime.time(0))

This works fine with the sheet.write_datetime method.

JoshEnglish commented Mar 21, 2013

I've run into this as well. I just wrote a quick conversion function:

def date_to_datetime(date):
return datetime.datetime.combine(date, datetime.time(0))

This works fine with the sheet.write_datetime method.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 21, 2013

Owner

Thanks Josh. I was going to suggest something like that as a workaround.

My general philosophy in the past has been to not modify the API for issues that can be solved in the user's space since that requires more testing and documentation on my side. This in turn takes time from adding features that can't be implemented by the user.

However, in cases where I've seen repeated workarounds from users I have modified the API. For example I resisted adding a write_row() function since it can easily be implemented with a for loop but in the end it was clear that it was something that users wanted and kept implementing themselves.

So I'll leave the API as it is for now. If this becomes an issue I will respond to it.

John

Owner

jmcnamara commented Mar 21, 2013

Thanks Josh. I was going to suggest something like that as a workaround.

My general philosophy in the past has been to not modify the API for issues that can be solved in the user's space since that requires more testing and documentation on my side. This in turn takes time from adding features that can't be implemented by the user.

However, in cases where I've seen repeated workarounds from users I have modified the API. For example I resisted adding a write_row() function since it can easily be implemented with a for loop but in the end it was clear that it was something that users wanted and kept implementing themselves.

So I'll leave the API as it is for now. If this becomes an issue I will respond to it.

John

@eazb

This comment has been minimized.

Show comment
Hide comment
@eazb

eazb Mar 23, 2013

Hi,

And thanks for the prompt reply.
It i,s of course, ultimately up to you :)
But I simply wanted to point out that from user's perspective it is easier not to worry about the type of the value being written to a cell. For example, the alternative module "xlsxcessive" handles this automatically for the aforementioned types.

Thanks again!

Eduardo

eazb commented Mar 23, 2013

Hi,

And thanks for the prompt reply.
It i,s of course, ultimately up to you :)
But I simply wanted to point out that from user's perspective it is easier not to worry about the type of the value being written to a cell. For example, the alternative module "xlsxcessive" handles this automatically for the aforementioned types.

Thanks again!

Eduardo

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 23, 2013

Owner

Hi Eduardo,

Thanks again for your input. It is good to get real world feedback.

Since yourself and Josh both perceive this as a limitation that is enough for me. :-)

I'll add this in the next 1 or 2 releases.

John

Owner

jmcnamara commented Mar 23, 2013

Hi Eduardo,

Thanks again for your input. It is good to get real world feedback.

Since yourself and Josh both perceive this as a limitation that is enough for me. :-)

I'll add this in the next 1 or 2 releases.

John

jmcnamara added a commit that referenced this issue Mar 25, 2013

Added support for datetime .date and .time objects.
Added additional support for datetime .date and .time objects
to write_datetime(). For issue #3.
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 25, 2013

Owner

@eazb, @JoshEnglish:

Could you try the above enhancement to support date and time objects in write_datetime() and let me know if you encounter any issues.

I'll do some more testing and if everything looks okay I'll push it to PYPI.

John

Owner

jmcnamara commented Mar 25, 2013

@eazb, @JoshEnglish:

Could you try the above enhancement to support date and time objects in write_datetime() and let me know if you encounter any issues.

I'll do some more testing and if everything looks okay I'll push it to PYPI.

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 25, 2013

Owner

Now available on PyPI in version 0.2.1: https://pypi.python.org/pypi/XlsxWriter

Thanks for the input.

Owner

jmcnamara commented Mar 25, 2013

Now available on PyPI in version 0.2.1: https://pypi.python.org/pypi/XlsxWriter

Thanks for the input.

@jmcnamara jmcnamara closed this Mar 25, 2013

@JoshEnglish

This comment has been minimized.

Show comment
Hide comment
@JoshEnglish

JoshEnglish Mar 25, 2013

Too late, I know. I finally got back to coding at work (you know, the fun part) and fed dates into the program and everything went tickety-boo.

JoshEnglish commented Mar 25, 2013

Too late, I know. I finally got back to coding at work (you know, the fun part) and fed dates into the program and everything went tickety-boo.

@eazb

This comment has been minimized.

Show comment
Hide comment
@eazb

eazb Mar 27, 2013

Hi, Josh

I am also late with this, but I just checked the new build.
Works fine with both python 2.7.2 and 3.3.0

Thanks a lot!

The only thing I would still wish for here would be if there would be a default format for time-only and date-only, so that one can pass values without making type-dependent handling.

Thanks again!

Eduardo

eazb commented Mar 27, 2013

Hi, Josh

I am also late with this, but I just checked the new build.
Works fine with both python 2.7.2 and 3.3.0

Thanks a lot!

The only thing I would still wish for here would be if there would be a default format for time-only and date-only, so that one can pass values without making type-dependent handling.

Thanks again!

Eduardo

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 27, 2013

Owner

The only thing I would still wish for here would be if there would be
a default format for time-only and date-only, so that one can pass
values without making type-dependent handling.

Hi Eduardo,

Do you mean that it would be nice to have a default cell format, so you don't have to add things like this:

date_format = workbook.add_format({'num_format': 'd-m-yyyy'})
worksheet.write('A4', number, date_format) 

I'm going to assume that is what you mean and answer that question. :-)

There are a couple of reasons that isn't there:

  1. Excel doesn't have any default formatting for dates or times. At least not in the file format. It does apply some default formatting in the runtime but that isn't available in the file. Which means that XlsxWriter would have to apply a default format if none is supplied.
  2. In which case what would be a reasonable date format. It would probably have to be an an ISO 8601 date. Not everyone would be happy with that but it is reasonably standard in Python.

I have considered this in the past and also providing some set_default_date_format() style methods to allow the defaults to be overridden.

In the end it hasn't been a big issue with most users but I am open to adding a change like this. So, I'll keep it in mind.

If you want you can open an new issue and I'll mark it as a feature request so that it isn't forgotten.

Owner

jmcnamara commented Mar 27, 2013

The only thing I would still wish for here would be if there would be
a default format for time-only and date-only, so that one can pass
values without making type-dependent handling.

Hi Eduardo,

Do you mean that it would be nice to have a default cell format, so you don't have to add things like this:

date_format = workbook.add_format({'num_format': 'd-m-yyyy'})
worksheet.write('A4', number, date_format) 

I'm going to assume that is what you mean and answer that question. :-)

There are a couple of reasons that isn't there:

  1. Excel doesn't have any default formatting for dates or times. At least not in the file format. It does apply some default formatting in the runtime but that isn't available in the file. Which means that XlsxWriter would have to apply a default format if none is supplied.
  2. In which case what would be a reasonable date format. It would probably have to be an an ISO 8601 date. Not everyone would be happy with that but it is reasonably standard in Python.

I have considered this in the past and also providing some set_default_date_format() style methods to allow the defaults to be overridden.

In the end it hasn't been a big issue with most users but I am open to adding a change like this. So, I'll keep it in mind.

If you want you can open an new issue and I'll mark it as a feature request so that it isn't forgotten.

@eazb

This comment has been minimized.

Show comment
Hide comment
@eazb

eazb Mar 27, 2013

Hi John,

That's exactly what I had in mind :-)
Personally, I would find it easier to override the method when creating the Workbook object, rather than making the exceptions later. I will open a feature request, so that it may be added one day.

Thanks again!

eazb commented Mar 27, 2013

Hi John,

That's exactly what I had in mind :-)
Personally, I would find it easier to override the method when creating the Workbook object, rather than making the exceptions later. I will open a feature request, so that it may be added one day.

Thanks again!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment