mysql - Exporting issues to excel using openpyxl(django) (cant seems to work with fetchall())

Question

def export_as_xls(self, request, queryset):
    opts = self.model._meta
    file_name = unidecode(opts.verbose_name)
    sql_query = '''SELECT  
                    COUNT(id) AS No_Of_Report, 
                    vendor, 
                    country_code, 
                    SUM(new_code)*100/SUM(sent) AS 'failure_rate', 
                    SUM(case when new_code =0 then 1 ELSE 0 end)*100/sum(sent) AS 'success_rate' 
                    FROM sms_statistics 
                    WHERE FROM_UNIXTIME(date) >= curdate() - interval 30 day
                    GROUP BY vendor, country_code 
                    ORDER BY vendor DESC;'''

This is mysql query i used to call for the data in mysql schema

    field_names = ('No of report', 'Vendor', 'Country Code', 'Failure Rate', 'Success Rate')
    wb = Workbook()
    ws = wb.active
    ws.append(ExportExcelAction.generate_header(self, self.model, field_names))
    with connection.cursor() as cursor:
        cursor.execute(sql_query)
        objects = list(cursor.fetchall())
        for row in cursor.fetchall():
            objects = list(row)
            ws.append(objects)
            print(ws.append(row))
    ws = style_output_file(ws)

I think the issue is right here for not being able to export to excel. Im not be using the right method to export the file from action.py

response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = f'attachment; filename={file_name}.xlsx' wb.save(response) return response export_as_xls.short_description = "Reports of Past 30 days" export_as_xls.acts_on_all = True

Blockquote

I believe this part is fine as i tried exporting an empty file and its working as expexted

question from:https://stackoverflow.com/questions/66058855/exporting-issues-to-excel-using-openpyxldjango-cant-seems-to-work-with-fetcha

Answer 1

def export_thirty_days(self, request, queryset):
    opts = self.model._meta
    file_name = "Sms Statistic Report"
    sql_query = '''SELECT  
                    COUNT(id) AS No_Of_Report, 
                    vendor, 
                    country_code, 
                    SUM(new_code)*100/SUM(sent) AS 'failure_rate', 
                    SUM(case when new_code =0 then 1 ELSE 0 end)*100/sum(sent) AS 'success_rate' 
                    FROM sms_statistics 
                    WHERE FROM_UNIXTIME(date) >= curdate() - interval 30 day
                    GROUP BY vendor, country_code 
                    ORDER BY COUNT(id) DESC;'''
    field_names = ('No of report', 'Vendor', 'Country Code', 'Failure Rate', 'Success Rate')
    wb = Workbook()
    ws = wb.active
    ws.append(ExportExcelAction.generate_header(self, self.model, field_names))
    with connection.cursor() as cursor:
        cursor.execute(sql_query)
        for row in cursor.fetchall():
            l = list(row)
            ws.append(l)
    ws = style_output_file(ws)
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={file_name}.xlsx'
    wb.save(response)
    return response
export_thirty_days.short_description = "Reports of Past 30 days"
export_thirty_days.acts_on_all = True

This is the solutions i've found to make it work