How to save an in memory Excel file to a Django models.FileField

Question

I am trying to save an Excel file that is built in memory to a Django models.FileField but get the error message: "'XlsxWriter' object has no attribute 'read'".

Here are the steps I follow: Data are first extracted from Django models. The data is saved temporarily in a pandas DataFrame. When completed, the Dataframe is transferred to an Excel file. The final step would be to save the Excel file to a models.FileField. This is the last step I have a problem with.

Here are the problematic lines of code with the error message I get:

file = File(excel_file)
doc.document.save(file_name, file, save=True)

'XlsxWriter' object has no attribute 'read'

I suspect the solution might lie with the usage of io.BytesIO but I cannot figure out how.

Here are more details on my code:

models.py

class DocuPlan(models.Model):
    plan = models.ForeignKey(Plan, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.DO_NOTHING, null=True)
    doc_type = models.IntegerField(
        _('Document type'),
        choices=DocuPlanType.choices,
        default=DocuPlanType.OTHER
    )
    date_order = models.DateField(null=True, blank=True)
    date_published = models.DateField(null=True, blank=True)
    is_public = models.BooleanField(_('Public document'), default=False)
    document = models.FileField(
        upload_to=docu_plan_directory_path, max_length=100, null=True, blank=True)
    date_filed = models.DateTimeField(auto_now_add=True)
    file_name = models.CharField(max_length=150, null=True, blank=True)

views.py

def dataExtraction(request, pk):
    template = 'basic/form_data_extraction.html'
    plan = Plan.objects.get(pk=pk)
    context = {}

    form = DataExtractionForm(request.POST or None, request=request, plan=plan)

    if request.method == 'POST':
        if form.is_valid():
            cleaned_data = form.cleaned_data
            year = int(cleaned_data.get('year'))
            data_type = cleaned_data.get('data_type')
            data_from_list = cleaned_data.get('data_from_list')
            excel_file = extract_data(
                request,
                context,
                plan,
                year,
                data_type,
                data_from_list
            )
            today = datetime.date.today()
            file_name = "data_" + str(year) + ".xlsx"
            doc = DocuPlan(
                user=request.user,
                plan=plan,
                doc_type=DocuPlanType.DATA,
                date_order=plan.year_end(year),
                date_published=today,
                is_public=False,
                date_filed=today,
                file_name=file_name,
            )
            file = File(excel_file)
            doc.document.save(file_name, file, save=True)


    context['form'] = form
    context['plan'] = plan
    return render(request, template, context=context)

utils.py

def df_to_excel(writer, df, sheet_name):
    if isinstance(df, pd.DataFrame):
        if len(df.index) > 0:
            df.to_excel(writer, sheet_name=str(sheet_name), index=False)


def save_xlsx(xlsx_path=None, df_active=None, df_earnings=None):

    writer = pd.ExcelWriter(xlsx_path)
    df_to_excel(writer, df_active, sheet_name=_('active'))
    df_to_excel(writer, df_earnings, sheet_name=_('active_history'))

    writer.save()

    return writer

def extract_data(request, context, plan, year, data_type, data_from_list=False):
    date_statement = datetime.date(year, 12, 31)
    filename = _('data_valuation')

    # A queryset of the plan members is created
    members = Member.objects.filter(plan=plan)

    # Active and disable members ...
    active = members.filter(
        Q(date_of_termination__gt=date_statement) |
        Q(date_of_termination__isnull=True)).filter(
        date_joined_plan__lte=date_statement)
    df_active, df_earnings = extract_data_active(
        request,
        plan,
        year,
        active,
        date_statement,
        date_previous,
        data_type,
    )
    path = os.path.join(settings.STATIC_ROOT, 'data_extract', filename)

    excel_file = save_xlsx(
        xlsx_path=path,
        df_active=df_active,
        df_earnings=df_earnings,
    )

    return excel_file

Answer 1

You need to convert the excel_file to a byte stream using io.BytesIO and pass that to the File constructor so the view should be:

def dataExtraction(request, pk):
    template = 'basic/form_data_extraction.html'
    plan = Plan.objects.get(pk=pk)
    context = {}

    form = DataExtractionForm(request.POST or None, request=request, plan=plan)

    if request.method == 'POST':
        if form.is_valid():
            cleaned_data = form.cleaned_data
            year = int(cleaned_data.get('year'))
            data_type = cleaned_data.get('data_type')
            data_from_list = cleaned_data.get('data_from_list')
            excel_file = extract_data(
                request,
                context,
                plan,
                year,
                data_type,
                data_from_list
            )
            today = datetime.date.today()
            file_name = "data_" + str(year) + ".xlsx"
            doc = DocuPlan(
                user=request.user,
                plan=plan,
                doc_type=DocuPlanType.DATA,
                date_order=plan.year_end(year),
                date_published=today,
                is_public=False,
                date_filed=today,
                file_name=file_name,
            )

            file_stream = io.BytesIO()
            excel_file.save(file_stream)
            file_stream.seek(0)
            file = File(file_stream, name=file_name)
            doc.document.save(file_name, file, save=True)


    context['form'] = form
    context['plan'] = plan
    return render(request, template, context=context)