can't subtract offset-naive and offset-aware datetimes when I try to export to excel

Question

Before marking as duplicated read the full question.

This error showed up when I tried to export to excel file using Django

using the library pip install xlwt

Here is the view I used

def export_warehouse_movement_report_csv(request):
    today_date = date.today()
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="warehouse_movement_' + str(today_date) + '.xls"'
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('BuyAndSells')
    # Sheet header, first row
    row_num = 0
    font_style = xlwt.XFStyle()
    font_style.font.bold = True
    columns = ['المستخدم', 'الكمية', 'الكتاب', 'الصنف', 'الفئة', 'نوع الحركة', 'التاريخ']
    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)
    # Sheet body, remaining rows
    font_style = xlwt.XFStyle()
    rows = models.WarehouseMovement.objects.all().values_list('user', 'item_quantity', 'book_name', 'item_name',
                                                              'move_class', 'move_type', 'date')
    for row in rows:
        row_num += 1
        for col_num in range(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)
    wb.save(response)
    return response

the error disappears when I remove the date field from the file

here is the modelthat is being added to the file:

class WarehouseMovement(models.Model):
    move_in = 'دخول'
    move_out = 'خروج'
    move_type_choices = (
        (move_in, 'دخول'),
        (move_out, 'خروج')
    )
    move_class_buy = 'شراء'
    move_class_sell = 'مبيعات'
    move_class_print = 'طباعة'
    move_class_ordering = 'تسوية'
    move_class_choices = (
        (move_class_buy, 'شراء'),
        (move_class_sell, 'مبيعات'),
        (move_class_print, 'طباعة'),
        (move_class_ordering, 'تسوية'),
    )
    date = models.DateTimeField(auto_now=True)
    move_type = models.CharField(max_length=150, choices=move_type_choices)
    user = models.ForeignKey(User, on_delete=PROTECT)
    move_class = models.CharField(max_length=150, choices=move_class_choices)
    item_name = models.ForeignKey(Item, on_delete=PROTECT, null=True, blank=True)
    book_name = models.ForeignKey(Book, on_delete=PROTECT, null=True, blank=True)
    item_quantity = models.IntegerField()

Full trace-back of the error

Environment:


Request Method: GET
Request URL: http://127.0.0.1:8000/export/warehousemoves/excel/

Django Version: 2.1.3
Python Version: 3.7.1
Installed Applications:
['erp',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django_filters',
 'mathfilters']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware']



Traceback:

File "E:\Django projects\erp_printing\venv\lib\site-packages\django\core\handlers\exception.py" in inner
  34.             response = get_response(request)

File "E:\Django projects\erp_printing\venv\lib\site-packages\django\core\handlers\base.py" in _get_response
  126.                 response = self.process_exception_by_middleware(e, request)

File "E:\Django projects\erp_printing\venv\lib\site-packages\django\core\handlers\base.py" in _get_response
  124.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "E:\Django projects\erp_printing\erp\views.py" in export_warehouse_movement_report_csv
  2062.             ws.write(row_num, col_num, row[col_num], font_style)

File "E:\Django projects\erp_printing\venv\lib\site-packages\xlwt\Worksheet.py" in write
  1088.         self.row(r).write(c, label, style)

File "E:\Django projects\erp_printing\venv\lib\site-packages\xlwt\Row.py" in write
  244.             date_number = self.__excel_date_dt(label)

File "E:\Django projects\erp_printing\venv\lib\site-packages\xlwt\Row.py" in __excel_date_dt
  99.         delta = date - epoch

Exception Type: TypeError at /export/warehousemoves/excel/
Exception Value: can't subtract offset-naive and offset-aware datetimes

Answer 1

The right way to do this is is to add values_list = [[x.strftime("%Y-%m-%d %H:%M") if isinstance(x, datetime.datetime) else x for x in row] for row in values_list] In the view to be like :

def export_warehouse_movement_report_csv(request):
    today_date = date.today()
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="warehouse_movement_' + str(today_date) + '.xls"'
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('BuyAndSells')
    # Sheet header, first row
    row_num = 0
    font_style = xlwt.XFStyle()
    font_style.font.bold = True
    columns = ['المستخدم', 'الكمية', 'الكتاب', 'الصنف', 'الفئة', 'نوع الحركة', 'التاريخ']
    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)
    # Sheet body, remaining rows
    font_style = xlwt.XFStyle()
    rows = models.WarehouseMovement.objects.all().values_list('user', 'item_quantity', 'book_name', 'item_name',
                                                              'move_class', 'move_type', 'date')
rows = [[x.strftime("%Y-%m-%d %H:%M") if isinstance(x, datetime.datetime) else x for x in row] for row in rows ]
    for row in rows:
        row_num += 1
        for col_num in range(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)
    wb.save(response)
    return response