CODEWITHSUNDEEP
djangoexceldatetimeopenpyxl
velis
velis

Reputation: 10035

Date exported is displayed as UTC

I'm trying to export some data from Django to Excel using openpyxl. The exported datetimes are correctly interpreted as such in Excel, but their printout is in UTC, not in local timezone (CET in this case) as I'd expect.

I tried to use to_excel, but that only outputs datetimes converted to excel's internal float format. Which is in addition also interpreted as float, not datetime. When formatted as datetime, it's still in UTC

I also tried to use Django's django.utils.timezone.localtime, but again the dates are rendered in UTC.

I could just subtract the timezone offset from my dates, but I feel it's more likely I'm missing something here.

How can I export datetime data such that Excel would display it in my local timezone?

Upvotes: 1

Views: 1373

Answers (3)

Sergey K.
Sergey K.

Reputation: 79

I had the similar problem and solved it in the following way. May be it helps.

from dateutil.tz import tzutc, tzlocal
TZ_UTC = tzutc()  # UTC timezone
TZ_LOCAL = tzlocal()  # Local timezone

datevalue = value #value I get from API I am using, which is datetime object. 
   #  For some reason I don't get tzinfo filled).
datevalue = datevalue.replace(tzinfo=TZ_UTC)  # Adding time zone info for UTC
datevalue = datevalue.astimezone(TZ_LOCAL)  # Converting to local timezone
datevalue = datevalue.replace(tzinfo=None)  # Removing tzinfo to correctly record local time to Excel
cell.value = datevalue

Upvotes: 3

velis
velis

Reputation: 10035

I ended up using a combination of javascript & server-side processing:

At the client HTML I create an input for user's local timezone:

<input type="hidden" value="" name="tz" id="xls-tz"/>

and populate its value (using jQuery):

$("#xls-tz").val(new Date().getTimezoneOffset());

At the server, I parse the timezone offset & write to openpyxl accordingly:

tz_offs = int(request.GET.get("tz", "0"))
ws.cell(row=row, column=2, value=item.time - timedelta(minutes=tz_offs))

That comes IMO pretty close to what I needed.

Thanks Charlie for the hint about Excel not being TZ aware.

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19507

Excel itself has no concept of timezones and will always dates and times naively. In this context the only sane thing to do is to convert to UTC which is what openpyxl does. openpyxl.utils.datetime is the module to look at if you want to change this

Upvotes: 1

Related Questions