DateTime issues in django xlsxwriter
I am trying to create an export to excel in my django view like this:
def export_myreport(request, sd, ed):
from xlsxwriter.workbook import Workbook
import cStringIO as StringIO
from django.utils.encoding import smart_str
# create a workbook in memory
output = StringIO.StringIO()
wb = Workbook(output)
bg = wb.add_format({'bg_color': '#9CB640', 'font_color': 'black'})
bg2 = wb.add_format({'bg_color': '#FFFFFF', 'font_color': 'black'})
ws = wb.add_worksheet('My Report')
row_num = 0
summary = MyModel.objects.filter(time__range = (sd, ed)).select_related()
row_num += 2
row = [
smart_str(u"Time"),
smart_str(u"Item"),
smart_str(u"User")
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], bg)
for s in summary:
row_num += 1
row2 = [
s.time,
s.model_name,
s.user.first_name
]
for col_num in xrange(len(row2)):
ws.write(row_num, col_num, row2[col_num], bg2)
wb.close()
output.seek(0)
response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=myreport.xlsx"
return response
But I am getting some problems with DateTime formatting! Perhaps there is something I am missing here?
Here is the error I am getting:
TypeError at /myapp/export_myreport/2015-05-01/2015-05-19
can't subtract offset-naive and offset-aware datetimes
EDIT:
This is how I call url in my html:
<a href="export_myreport/{{begindate}}/{{enddate}}" class="btn btn-default pull-right" role="button">Export to XLSX</a>
Here {{begindate}}
and {{enddate}}
are variable angular.
source to share
Excel, and therefore XlsxWriter, does not support time zones in dates / times.
Therefore, you will need to remove or adjust the timezone from the date and time before passing it to the XlsxWriter.
Something like this from the pytz docs:
dt = datetime(2005, 3, 1, 14, 13, 21, tzinfo=utc)
naive = dt.replace(tzinfo=None)
This would probably be better handled in Django, but instead of adjusting all datetime data before passing it to the XlsxWriter. Maybe someone else can add a sentence.
source to share