Xlwt Excel Export Foreign Key By Actual Values / Django

I export products in excel format using xlwt.But foreign key fields are exported as id.

How can I export foreign key fields with their actual values?

I want to export brand_id and author fields with their actual values.

Here is my product model :

class Product(models.Model):
    id = models.AutoField(primary_key=True)
    author = models.ForeignKey(User,on_delete= models.CASCADE, verbose_name='Product Author', null=True)
    brand_id = models.ForeignKey(Brand,on_delete=models.CASCADE, verbose_name="Brand Names")
    name = models.CharField(max_length=255, verbose_name="Product Name")
    barcode = models.CharField(max_length=255, verbose_name="Barcode")
    unit = models.CharField(max_length=255,verbose_name="Product Unit") 

    def __str__(self):
        return self.name

Here is my export view:

def export_excel(request):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = "attachment; filename=Products-" + str(datetime.datetime.now().date())+".xls" 
    wb = xlwt.Workbook(encoding="utf-8")
    ws = wb.add_sheet('Products')
    row_num = 0
    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    columns = ["Product Id","Product Author","Product Brand","Product Name","Product Barcode","Product Unit"]

    for col_num in range(len(columns)):
        ws.write(row_num,col_num,columns[col_num],font_style)

    font_style = xlwt.XFStyle()

    rows = Product.objects.filter(author = request.user).values_list("id","author","brand_id","name","barcode","unit")

    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)

    wb.save(response)

Thanks for your help. Kind regards

Solution

You could use django-import-export to export the data from a model to an excel file. This library also supports other data types in case you need them in the future.

As described in the documentation of django-import-export you can create a resource, which can then be used to both import and export data into a model. Start by creating a resource:

from import_export import resources
from import_export.fields import Field

from .models import Product


class ProductResource(resources.ModelResource):
    author = Field() # for field with foreignkeys you need to add them here
    brand_id = Field() # for field with foreignkeys you need to add them here

    fields = ["id", "author", "brand_id", "name", "barcode", "unit"]

    export_order = ["id", "author", "brand_id", "name", "barcode", "unit"]

    def dehydrate_author(self, product: Product) -> str:
        return f"{product.author.name}" # probably need to adapt the name of the field

    def dehydrate_brand_id(self, product: Product) -> str:
        return f"{product.brand_id.brand}" # probably need to adapt the name of the field

This is also documented here: django-import-export advanced manipulation

Now you can use this ModelResource to export your data to any supported format, in your case an Excel file. Import your resource you've created earlier all you need to do to return this in your view is the following:

from django.http import HttpResponse
from .resource import ProductRes

#... other code in your view
project_resource = ProjectResource()
dataset = project_resource.export()
response = HttpResponse(dataset.xlsx, ontent_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response["Content-Disposition"] = 'attachment; filename="projects_export.xlsx"'