Exporting Django models to Excel (different sheets) using Django-tables2

Question

I've checked django-tables2 documentation, however I haven't find an easy way to export models to different sheets in Excel. To make it simple, let's suppose I have two different models: Sales and Products. I would like to export an excel document with two different sheets: Sales and Products. I can export the first model with the code shown below, but I'm not sure if there's a way to export the Products model to another sheet. Any help would be appreciated.


export_format = request.GET.get('_export', None)

if TableExport.is_valid_format(export_format):
        table = [[Sales Table Object]]
        exporter = TableExport(export_format, table)
        return exporter.response('File_Name.{}'.format(export_format)) ```

Not helpful for the package you are using, but it is super simple to export excel files in python and build out the sheets you need. — ViaTech, Aug 29 '22 at 01:09

score 2 · Answer 1 · answered Aug 29 '22 at 02:47

So, I came with a simple solution, probably not the most efficient, by using pandas. Convert the queryset to pandas dataframe and use pandas ExcelWriter to generate the excel and it can be done for different sheets. Probably for deployment with large data, might be better to use libraries like xlsxwriter but it works for what I wanted to do.

    iden= request.GET["id"]
    output = BytesIO()
    queryset = Efectivo.objects.filter(Q(id1=iden)).values()
    df = pd.DataFrame(queryset)
    writer = pd.ExcelWriter(output,engine='xlsxwriter')
    df.to_excel(writer,sheet_name='Efectivo')
    writer.save()
    output_name = 'example'
    output.seek(0)
    response = HttpResponse(output, 
    content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={output_name}.xlsx'
        
    
    return response```

score 0 · Answer 2 · edited Oct 20 '22 at 14:30

Try using xlsxwriter:

response = HttpResponse(content_type='application/vnd.ms-excel')
# tell the browser what the file is named
response['Content-Disposition'] = 'attachment;filename="file_name.xlsx"'
writer = pd.ExcelWriter(response, engine = 'xlsxwriter')
#d1 and d2 are pandas dataframe 
d1.to_excel(writer, sheet_name='Sheet1',index=False)
d2.to_excel(writer, sheet_name='Sheet2',index=False)
writer.save()

Exporting Django models to Excel (different sheets) using Django-tables2

2 Answers2