How to generate xlsx file from Django model

As you may have guessed, my reporting project is entirely focused on helpdesk events recording. At the end of the month, the system administrator must submit a report in the xlsx format for business head. To create such files, we need openpyxl.

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

######Some other VIEVS.PY CODE #########
@login_required
def saveinfo(request, username):
#function receives username as argument for filtering data in the model. 
#Don't forget add correct path in urls.py such as path
#('saveinfo/<str:username>', views.saveinfo, name='saveinfo'),
# and generate links for this in template.
    """
    Downloads all infos as Excel file with a single worksheet
    """
    try:
        if username == 'all':
            saveinfo = Addinfo.objects.order_by('date_added')
        else:
            saveinfo = Addinfo.objects.filter(work_user=username).order_by('date_added')
    except:
        username = request.user.id
        saveinfo = Addinfo.objects.filter(work_user=username).order_by('date_added')

    response = HttpResponse(
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    )
    response['Content-Disposition'] = 'attachment; filename=f"{date}-отчет.xlsx"'.format(
        date=datetime.now().strftime('%Y-%m-%d'),
    )
    workbook = Workbook()
    
    # Get active worksheet/tab
    worksheet = workbook.active
    worksheet.title = 'Report'

    # Define the titles for columns
    columns = [
        'Дата и время',
        'Офис',
        'Сотрудник',
        'Комментарий',
        'Время',
        'Расход',
        'Приход',
        'Доп. инфо',
    ]
    row_num = 1
    # Assign the titles for each cell of the header
    for col_num, column_title in enumerate(columns, 1):
        cell = worksheet.cell(row=row_num, column=col_num)
        cell.value = column_title

    # Iterate through all movies
    for info in saveinfo:
        row_num += 1
        
        # Define the data for each cell in the row 
        row = [
            info.work_day,
            info.work_where,
            info.work_who,
            info.work_what,
            info.work_minutes,
            info.work_trade,
            info.work_return,
            info.work_comment,
        ]
        
        # Assign the data for each cell of the row 
        for col_num, cell_value in enumerate(row, 1):
            cell = worksheet.cell(row=row_num, column=col_num)
            cell.font = Font(name='Times New Roman', size=12, vertAlign='baseline', underline='none')
            cell.value = cell_value

    workbook.save(response)

    return response

 

See also  Server control by Telegram Bot - Run Shell commands by Python
Author: admin

3 thoughts on “How to generate xlsx file from Django model

Leave a Reply

Your email address will not be published. Required fields are marked *