views.py

def download_excel(request):
  if request.user.admin == True and request.user.admin_approval == True:
    # content-type of response
    response = HttpResponse(content_type='application/ms-excel')

    #decide file name
    response['Content-Disposition'] = 'attachment; filename=Products'   \
        str(datetime.datetime.now())   '.xls'

    #creating workbook
    wb = xlwt.Workbook(encoding='utf-8')

    #adding sheet
    ws = wb.add_sheet("Products")

    # Sheet header, first row
    row_num = 0

    font_style = xlwt.XFStyle()
    date_style = xlwt.XFStyle()
    date_style.num_format_str = 'DD-MM-YY'

    # headers are bold
    font_style.font.bold = True

    #column header names, you can use your own headers here
    columns = ['Added Date', 'Modified Date', 'Vendor ID','Product ID','Item No','Color','Stock','Approval Status','Approved Date','Approved By', ]

    #write column headers in sheet
    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 = ProductVariants.objects.all().values_list('created','edited','vendoruser','product_id',
                                                'item_num','variant_value','initial_stock','approval_status',
                                                'approved_date','approved_by',)


    


    for my_row in rows:
        row_num = row_num   1
        ws.write(row_num, 0, my_row.created, font_style)
        ws.write(row_num, 1, my_row.edited, font_style)
        ws.write(row_num, 2, my_row.vendoruser, font_style)
        ws.write(row_num, 3, my_row.product_id, font_style)
        ws.write(row_num, 4, my_row.item_num, font_style)
        ws.write(row_num, 5, my_row.variant_value, font_style)
        ws.write(row_num, 6, my_row.initial_stock, font_style)
        ws.write(row_num, 7, my_row.approval_status, font_style)
        ws.write(row_num, 8, my_row.approved_date, font_style)
        ws.write(row_num, 9, my_row.approved_by, font_style)
        
        wb.save(response)
        return response

I used this and it works, but I don't get the foreign key values, instead, I get the id of the foreign key field. So, I used the above code, but that gives error 'tuple' object has no attribute 'created'. I want to get the values for the corresponding columns. Please, I want a solution to this.

   for row in rows:
       row_num  =  1                
      
      for col_num in range(len(row)):
              ws.write(row_num, col_num,str(row[col_num]), font_style)

CodePudding user response：

values_list gives you list if tuples with values of fields listed as arguments in each tuple. You can't call this values like this my_row.created because each value in tuple have only index, so you can call them only by index my_row[0], my_row[1]...

You can use values instead of values_list, then you'll get list of dicts in wich you can call each value by key.

rows = ProductVariants.objects.all().values(
    'created','edited','vendoruser','product_id',
    'item_num','variant_value','initial_stock',
    'approval_status','approved_date','approved_by',
)
for my_row in rows:
    row_num = row_num   1
    ws.write(row_num, 0, my_row['created'], font_style)
    ...

CodePudding user response：

Alongside you can define in the method .values_list() foreign_key fields too! For example if approved_by is a user and has a field name:

ProductVariants.objects.all().values_list('created','edited', 'approved_by__name')

Notify the double underscore between the foreign_key and the model field.

But the error says something else:

for my_row in rows:
    row_num = row_num   1
        ...

my_row is a tuple object. So you have need to unpack that first:

for my_row in rows:
    print(my_row)
    created, edited, ...add_more_values_here = myrow
    row_num = row_num   1
    ws.write(row_num, 0, created, font_style)
    ...