-
-
Notifications
You must be signed in to change notification settings - Fork 592
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
Question / feature request: Excel - format as date #210
Comments
Can you share the code of how to do that? We use xlwt under the covers. |
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 |
The transformation above is already handled by xlwt: 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. |
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). |
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.)
The text was updated successfully, but these errors were encountered: