CODEWITHSUNDEEP
pythondjangoexcelpandas
jia Jimmy
jia Jimmy

Reputation: 1848

Cannot open excel file generated by django

Say there's a dataframe from pandas like :

                          mediabuy      cpa        mediabuy        cpc          
cost        2020-02         0.00       371929.95   15956581.16    16328511.11
            2020-04        1311.92     224747.07   26710431.81    26936490.80
total                      1311.92     596677.02   42667012.97     43265001.91

I want to create an excel file in django, and I've tried with codes as below:


# return excel view


            df = pd.DataFrame(data, index=index, columns=column)

            # saved as excel
            excel_writer = pd.ExcelWriter(path='temp.xlsx', engine='openpyxl')
            df.to_excel(excel_writer)
            wb = excel_writer.book

            response = HttpResponse(save_virtual_workbook(wb))
            response["Content-Type"] = 'application/vnd.ms-excel'
            response['Content-Disposition'] = 'attachment; filename={}.xlsx'.format("data"))
            return response

I'm working with python3.6.8, django2.2.4, pandas1.0.3, openpyxl3.0.3

But I always get an error saying "excel file cannot opened because the file format or file extension is not valid".

Why am I getting this error?

Thanks.

Upvotes: 0

Views: 644

Answers (2)

Steven Levitt
Steven Levitt

Reputation: 11

Chunking the Data always helps. Or you can create a background task with celery

Upvotes: 0

RBowen
RBowen

Reputation: 305

Unless there is a problem with the structure of the data in the dataframe you should be able to achieve this using:

from io import BytesIO

df = pd.DataFrame(data, index=index, columns=column)

stream_file = BytesIO()
df.to_excel(stream_file)
stream_file.seek(0)

response = HttpResponse(stream_file)
response["Content-Type"] = 'application/vnd.ms-excel'
response['Content-Disposition'] = 'attachment; filename={}.xlsx'.format("data")
return response

Upvotes: 1

Related Questions