Django Export Excel -- Category based on count-CodePudding

Edited based on comment

I am using Django and want to export it in excel. How can you do count based on occurences of the category. How can you put unique values of Accident Causation?

Accident Causation -- Severity Severity has three options: Severity 1, Severity 2, Severity 3

The excel should look like this

Accident Causation | Severity 1| Severity 2 | Severity 3
Accident Causation1| 20        | 10         | 0
Accident Causation2| 0         | 5          | 0

Model

user = models.ForeignKey(User, on_delete=models.CASCADE, editable=False, null=True, blank=True)
user_report = models.OneToOneField(UserReport, on_delete=models.CASCADE)
accident_factor = models.ForeignKey(AccidentCausation, on_delete=models.SET_NULL, blank=True, null=True)
accident_subcategory = models.ForeignKey(AccidentCausationSub, on_delete=models.SET_NULL, blank=True, null=True)
collision_type = models.ForeignKey(CollisionType, on_delete=models.SET_NULL, blank=True, null=True)
collision_subcategory = models.ForeignKey(CollisionTypeSub, on_delete=models.SET_NULL, blank=True, null=True)
crash_type = models.ForeignKey(CrashType, on_delete=models.SET_NULL, blank=True, null=True)
weather = models.PositiveSmallIntegerField(choices=WEATHER, blank=True, null=True)
light = models.PositiveSmallIntegerField(choices=LIGHT, blank=True, null=True)
severity = models.PositiveSmallIntegerField(choices=SEVERITY, blank=True, null=True)
movement_code = models.CharField(max_length=250, blank=True)
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)

Views

def export_accidentcausation_xls(request):
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename="accident_causation.xls"'


wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Accident Causation')

alignment = xlwt.Alignment()

alignment.horz = xlwt.Alignment.HORZ_LEFT
alignment.vert = xlwt.Alignment.VERT_TOP
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style

# Sheet header, first row
row_num = 0

font_style = xlwt.XFStyle()
font_style.font.bold = True

header_font = xlwt.Font()

# Header font preferences
header_font.name = 'Times New Roman'
header_font.height = 20 * 15
header_font.bold = True

# Header Cells style definition
header_style = xlwt.XFStyle()
header_style.font = header_font 


body_font = xlwt.Font()

# Body font preferences
body_font.name = 'Arial'
body_font.italic = True


borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
header_style.borders = borders

# body cell name style definition
body_style = xlwt.XFStyle()
body_style.font = body_font 



columns = ['Username', 'First name', 'Middle name', 'Last name', 'Email address', 'Mobile Number', 'Birthday', 'Role', 'Status', 'Last Login', 'Created At', 'Updated At']

for col_num in range(len(columns)):
    ws.write(row_num, col_num, columns[col_num], header_style)
    ws.col(col_num).width = 7000

    

# Sheet body, remaining rows
# font_style = xlwt.XFStyle()
# rows1 = AccidentCausation.objects.all()
rows = IncidentGeneral.objects.all().values_list('accident_factor__category', 'accident_subcategory')
rows = rows.annotate(
     severity1=Count('severity', only=Q(1)),
    severity2=Count('severity', only=Q(2)),
    severity3=Count('severity', only=Q(3))
)
# 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], body_style)


wb.save(response)
return response

URL

urlpatterns = [  
    path('generate_report', views.generate_report, name='generate_report'),
    path('generate_report/export_users_xls', views.export_users_xls, name='export_users_xls'),
    path('generate_report/export_accidentcausation_xls', views.export_accidentcausation_xls, name='export_accidentcausation_xls'),
]

CodePudding user response：

OK, you can make a queryset like this i think:

queryset = IncidentGeneral.objects.all().values('accident_factor')
queryset = queryset.annotate(
    severity1=Count('severity', filter=Q(severity=1)),
    severity2=Count('severity', filter=Q(severity=2)),
    severity3=Count('severity', filter=Q(severity=3))
)

for item in queryset:
    print(item.accident_factor, item.severity1, item.severity2, item.severity3)