Create a Excel report of 2 Django Models related by a ForeignKey Field

Question

I have two models like these:

class NominaEnc(models.Model):
    G1 = "PERSONAL"
    G2 = "SUPERVISOR"
    G3 ="AUXILIAR"

    GRUPO_CHOICES = (
        (G1, 'PERSONAL'),
        (G2, 'SUPERVISOR'),
        (G3, 'AUXILIAR'),
    )

    fecha_nomina= models.DateField()
    planta =models.ForeignKey(PlantaNomina,to_field='descripcion_planta',on_delete=models.CASCADE)
    area=models.ForeignKey(AreaNomina, on_delete=models.CASCADE, to_field='descripcion_area')
    linea =models.ForeignKey(lineaNomina, on_delete=models.CASCADE, to_field='descripcion_linea')
    grupo=models.CharField(choices=GRUPO_CHOICES, max_length=30, blank=True, null =True)
    supervisor=models.ForeignKey(SupervisorNomina, on_delete=models.CASCADE, 
        to_field='nombre_supervisor')
    semana = models.IntegerField(default=1 )
    plantilla = models.IntegerField(default=0)

    def __str__(self):
        return'{} {} {} {} {}'.format(self.semana, self.area, self.linea, self.grupo, 
        self.supervisor)

    class Meta:
        verbose_name_plural ="Encabezados Nomina"
        verbose_name = "Encabezado Nomina"

class NominaDet(models.Model):

    nomina = models.ForeignKey(NominaEnc, related_name='detalles' ,on_delete=models.CASCADE)
    concepto=models.ForeignKey(ConceptoNomina, on_delete=models.CASCADE, to_field='concepto')
    cantidad =models.FloatField(default=0.0)


    def __str__(self):
        return "{} {}".format(self.nomina,self.concepto)


    class Meta:
        verbose_name_plural ="Detalles Nomina"
        verbose_name = "Detalle Nomina"

the Views I have are:

class NominaList( generic.ListView):
    model=NominaEnc
    template_name='nomina/nomina_list.html'
    context_object_name='nomina'

class NominaCompletaList(generic.ListView):
    template_name='nomina/nomina_completa.html'
     context_object_name='nomina'
    queryset = NominaEnc.objects.all()

    def get_context_data(self, **kwargs):
        context = super(NominaCompletaList, self).get_context_data(**kwargs)
        context['detalles'] = NominaDet.objects.all()
        context['encabezado'] = self.queryset
        return context

and the url are:

urlpatterns = [
path('nomina/', NominaList.as_view(), name="nomina_list"),
path('nomina_completa/', NominaCompletaList.as_view(), name="nomina_completa")
]

I want to create a XLSX file (Excel) that shows the data of both Models. I have done for the NominaEnc model (Parent Model) but I can´t do it joining the data of NominaEnc and NominaDet Models.

to create the report I did it this way:

from django.shortcuts import render

from django.contrib.auth.mixins import LoginRequiredMixin
from django.views .generic.base import TemplateView
from django.http.response import HttpResponse
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border,Font,PatternFill,Side

from django.views import generic
from django.urls import reverse_lazy

from .models import NominaEnc, NominaDet

class ReporteNomina(TemplateView):
def get (self, request, *args, **kwargs):

    query = NominaEnc.objects.all()
    wb = Workbook()

    ws = wb.active
    ws.tittle='Nomina'


    #Establer el nombre del archivo
    nombre_archivo = "Reporte Nomina.xlsx"
    ws['B1'].alignment= Alignment(horizontal='left', vertical='center')
    ws['B1'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                        top=Side(border_style='thin'), bottom=Side(border_style='thin'))

    ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
    ws['B1'].font = Font(name='calibri', size=12, bold=True)
    ws['B1']='Company'

    ws.merge_cells('B1:F1')

    ws['B2'].alignment= Alignment(horizontal='left', vertical='center')
    ws['B2'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                        top=Side(border_style='thin'), bottom=Side(border_style='thin'))

    ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
    ws['B2'].font = Font(name='calibri', size=12, bold=True)
    ws['B2']='Department'

    ws.merge_cells('B2:F2')
    ws['B3'].alignment= Alignment(horizontal='left', vertical='center')
    ws['B3'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                        top=Side(border_style='thin'), bottom=Side(border_style='thin'))

    ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
    ws['B3'].font = Font(name='calibri', size=12, bold=True)
    ws['B3']='Reporte de Nomina'

    ws.merge_cells('B3:F3')

    ws.row_dimensions[1].height=20
    ws.row_dimensions[2].height=20
    ws.row_dimensions[3].height=20

    ws.column_dimensions['B'].width=20
    ws.column_dimensions['C'].width=20
    ws.column_dimensions['D'].width=20
    ws.column_dimensions['E'].width=20


    ws['B6'].alignment= Alignment(horizontal='center', vertical='center')
    ws['B6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                        top=Side(border_style='thin'), bottom=Side(border_style='thin'))
    ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
    ws['B6'].font = Font(name='calibri', size=11, bold=True)
    ws['B6']='Semana'


    ws['C6'].alignment= Alignment(horizontal='center', vertical='center')
    ws['C6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                        top=Side(border_style='thin'), bottom=Side(border_style='thin'))
    ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
    ws['C6'].font = Font(name='calibri', size=11, bold=True)
    ws['C6']='Area'

    controlador = 7
    for q in query:
        ws.cell(row=controlador,column=2).alignment= Alignment(horizontal='center', vertical='center')
        ws.cell(row=controlador,column=2).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws.cell(row=controlador,column=2).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws.cell(row=controlador,column=2).font = Font(name='calibri', size=11, bold=True)
        ws.cell(row=controlador,column=2).value=q.semana

        ws.cell(row=controlador,column=3).alignment= Alignment(horizontal='center', vertical='center')
        ws.cell(row=controlador,column=3).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
                            top=Side(border_style='thin'), bottom=Side(border_style='thin'))
        ws.cell(row=controlador,column=3).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
        ws.cell(row=controlador,column=3).font = Font(name='calibri', size=11, bold=True)
        ws.cell(row=controlador,column=3).value=q.area

        #contador+=1
        controlador +=1

    response = HttpResponse(content_type='application/ms-excel')
    contenido = "attachment; filename = {0}".format(nombre_archivo)
    response["Content-Disposition"] = contenido
    wb.save(response)
    return response

The question is how to define the query that joins the data of both models in the field nomina of NominaDet model?

Answer 1

You should use a NominaEnc object example

nom = NominaEnc.objects.first()

nom.detalles.all()

or something like .first(), .filter() etc...