How to go from pandas.ExcelWriter to models.FileField

Question

How to save an Excel file into a file_stream (IOBytes).

Here is the code:

views.py:

import io
from django.core.files.base import File
import datetime
import pandas as pd

def planSaveFile(request, pk):
    plan = get_object_or_404(Plan, pk=pk)
    file_name = 'foo.xlsx'

    # creation of an Excel file saved in memory
    writer = pd.ExcelWriter(file_name)
    df = pd.DataFrame({'A': [1, 2, -3], 'B': [1, 2, 6]})
    df.to_excel(writer, sheet_name='sheet1')

    # converting BytesIO() to File (django)
    file_stream = io.BytesIO()
    writer.save(file_stream) # ERROR HERE (takes 1 positional argument but 2 were given)
    file_stream.seek(0)
    file = File(writer)

    # creating an instance of DocuPlan
    doc = DocuPlan(
        plan=plan,
        user=request.user,
        doc_type=DocuPlanType.DATA,
        date_order=datetime.date.today(),
        date_published=datetime.date.today(),
        is_public=False,
        # document=file,
        file_name=file_name,
    )

    # Saving File (django) to a models.FileField
    doc.document.save(file_name, file, save=True)

    return redirect('docu_plan_list', pk=pk)

I am getting the error: "takes 1 positional argument but 2 were given" when I try to save the Excel file in IOBytes.

Answer 1

You do not need pandas.ExcelWriter to write an inmemory Excel file. You write it directly to io.BytesIO(). This four-minutes video pointed me to the solution: Python Tutorial: File Objects - Reading and Writing to Files - Corey Schafer. Do not forget to close the files after you're done.

Here is the modified code:

def planSaveFile(request, pk):
    plan = get_object_or_404(Plan, pk=pk)
    file_name = "foo.xlsx"

    # creation of an Excel file saved in memory
    df = pd.DataFrame({"A": [1, 2, -3], "B": [1, 2, 6]})
    file_stream = io.BytesIO()
    df.to_excel(file_stream, sheet_name="sheet1")
    file_stream.seek(0)
    file = File(file_stream)

    # creating an instance of DocuPlan
    doc = DocuPlan(
        plan=plan,
        user=request.user,
        doc_type=DocuPlanType.DATA,
        date_order=datetime.date.today(),
        date_published=datetime.date.today(),
        is_public=False,
        # document=file,
        file_name=file_name,
    )

    # Saving File (django) to a models.FileField
    doc.document.save(file_name, file, save=True)
    file_stream.close()
    file.close()

    return redirect("docu_plan_list", pk=pk)