Cannot open excel file generated by django

Question

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.

RBowen · Accepted Answer · 2020-05-09 10:39:54Z

1

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

answered May 9, 2020 at 10:39

RBowen

3054 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RBowen Over a year ago

In that case, you might want to try df = df.reset_index() prior to df.to_excel(stream_file) as it could be related to the dataframe being grouped. If you add the print out of df.to_dict('records') to your question it'll be easier to reproduce

Steven Levitt · Accepted Answer · 2024-01-15 21:13:00Z

0

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

answered Jan 15, 2024 at 21:13

Steven Levitt

112 bronze badges

2 Comments

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Ro.oT Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Collectives™ on Stack Overflow

Cannot open excel file generated by django

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related