Question / feature request: Excel - format as date

[khink]

I can feed a tablib cell a datetime, and formatting that cell as date in Excel works - the number shown initially gets converted to a valid date.

Can i tell tablib to format the cell as datetime right away, so the Excel file shows date formatting right after being opened?

(Looks like XlsxWriter and xlwt can do that, but i'm reluctant to switch.)

[kennethreitz]

Can you share the code of how to do that? We use xlwt under the covers.

[khink]

def to_excel(dt_obj):
    """Convert a datetime object to a float for Excel's date formatting.

    Excel stores dates and times internally as floating point numbers
    where the integer part is the number of days since 31st December 1899
    and the fractional part is the time of day.
    When loaded into a tablib Dataset these values remain as floating point.
    """
    REF_DATE = datetime(1899, 12, 31)
    SECS_IN_DAY = 24 * 60 * 60.0
    delta = dt_obj - REF_DATE
    excel_date = delta.days + delta.seconds / SECS_IN_DAY
    return excel_date

[djw]

The transformation above is already handled by xlwt:

https://github.com/python-excel/xlwt/blob/5a222d0315b6d3ce52a3cedd7c3e41309587c107/xlwt/Row.py#L86-L106

In order to get them to display correctly you need to specify a formatter, such as:

if isinstance(col, date):
    date_fmt = ws.write(i, j, col, xlwt.easyxf(num_format_str="M/D/YY"))
elif isinstance(col, datetime):
    date_fmt = ws.write(i, j, col, xlwt.easyxf(num_format_str="M/D/YY h:mm"))

The format string should be taken from the list of Excel's built-in formats.

Despite selecting American M/D/YY format, this is correctly localised to D/M/YY on my British computer.

[matthijskooijman]

A related request would be to format as currency (and also set the cell format to currency, so excel/libreoffice displays a currency symbol on the field).