Django export to xlsx with lock columns

Question

I have this code for exporting data to CSV.

How can I lock some columns in this file?

For example: lock 'id' and 'Subsidiary' that the the user won't be able to edit

views.py

def combinationExport(request):
    combination = Combination.objects.all()
    response = HttpResponse(content_type="text/csv")
    response["Content-Disposition"] = 'attachment; filename="Combination.csv"'
    writer = csv.writer(response)
    writer.writerow(
        [
            "id",
            "Subsidiary",
            "Department",
            "Account",
            "Sub Budget",
            "Budget Owner",
            "Sub Budget Owner",
        ]
    )
    combo = combination.values_list(
        "id",
        "subsidiary__name",
        "department__name",
        "account__name",
        "sub_budget__name",
        "budget_owner__budget_owner__name",
        "budget_owner__sub_budget_owner__name",
    )
    for q in combo:
        writer.writerow(q)
        # print(q)
    return response

TNX

Itamar Ben Oren · Answer

I found a solution.

class YearScenarioExportView(
    Budgetlevel3Mixin, BudgetMixin, LoginRequiredMixin, DetailView
):
    model = YearScenario

    def get(self, request, pk: int):
        self.object: YearScenario = self.get_object()
        qs = self.object.rows.fast()

        df = pd.DataFrame.from_records(
            qs.values_list(
                "code",
                "scenario_year__scenario__scenario",
                "scenario_year__year__year",
                "scenario_year__version__version",
                "combination__subsidiary__name",
                "combination__department__name",
                "combination__account__name",
                "combination__sub_budget__name",
                "combination__budget_owner__budget_owner__name",
                "combination__budget_owner__sub_budget_owner__name",
                "is_active",
                "budget_q1",
                "budget_q2",
                "budget_q3",
                "budget_q4",
                "q1_comment",
                "q2_comment",
                "q3_comment",
                "q4_comment",
            ),
            columns=[
                "code",
                "scenario",
                "year",
                "version",
                "subsidiary",
                "department",
                "account",
                "sub_budget",
                "budget_owner",
                "sub_budget_owner",
                "is_active",
                "budget_q1",
                "budget_q2",
                "budget_q3",
                "budget_q4",
                "q1_comment",
                "q2_comment",
                "q3_comment",
                "q4_comment",
            ],
        )
        # print(df)
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {"in_memory": True})
        worksheet = workbook.add_worksheet("Tablib Dataset")
        unlocked = workbook.add_format({"locked": False})
        worksheet.set_column("A:A", 40)
        worksheet.protect()
        bold = workbook.add_format({"bold": True})

        # Write the title for every column in bold
        worksheet.write("A1", "code", bold)
        worksheet.write("B1", "scenario", bold)
        worksheet.write("C1", "year", bold)
        worksheet.write("D1", "version", bold)
        worksheet.write("E1", "subsidiary", bold)
        worksheet.write("F1", "department", bold)
        worksheet.write("G1", "account", bold)
        worksheet.write("H1", "sub_budget", bold)
        worksheet.write("I1", "budget_owner", bold)
        worksheet.write("J1", "sub_budget_owner", bold)
        worksheet.write("K1", "is_active", bold)
        worksheet.write("L1", "q1", bold)
        worksheet.write("M1", "q2", bold)
        worksheet.write("N1", "q3", bold)
        worksheet.write("O1", "q4", bold)
        worksheet.write("P1", "q1_comment", bold)
        worksheet.write("Q1", "q2_comment", bold)
        worksheet.write("R1", "q3_comment", bold)
        worksheet.write("S1", "q4_comment", bold)

        # Iterate over the data and write it out row by row.
        for linea in df.index:
            worksheet.write(f"A{linea+2}", df["code"][linea])
            worksheet.write(f"B{linea+2}", df["scenario"][linea])
            worksheet.write(f"C{linea+2}", df["year"][linea])
            worksheet.write(f"D{linea+2}", df["version"][linea])
            worksheet.write(f"E{linea+2}", df["subsidiary"][linea])
            worksheet.write(f"F{linea+2}", df["department"][linea])
            worksheet.write(f"G{linea+2}", df["account"][linea])
            worksheet.write(f"H{linea+2}", df["sub_budget"][linea])
            worksheet.write(f"I{linea+2}", df["budget_owner"][linea])
            worksheet.write(f"J{linea+2}", df["sub_budget_owner"][linea])
            worksheet.write(f"K{linea+2}", df["is_active"][linea], unlocked)
            worksheet.write(f"L{linea+2}", df["budget_q1"][linea], unlocked)
            worksheet.write(f"M{linea+2}", df["budget_q2"][linea], unlocked)
            worksheet.write(f"N{linea+2}", df["budget_q3"][linea], unlocked)
            worksheet.write(f"O{linea+2}", df["budget_q4"][linea], unlocked)
            worksheet.write(f"P{linea+2}", df["q1_comment"][linea], unlocked)
            worksheet.write(f"Q{linea+2}", df["q2_comment"][linea], unlocked)
            worksheet.write(f"R{linea+2}", df["q3_comment"][linea], unlocked)
            worksheet.write(f"S{linea+2}", df["q4_comment"][linea], unlocked)

        workbook.close()

        output.seek(0)
        response = HttpResponse(
            output.read(),
            content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        )
        file_name = f"{self.object.get_export_file_name_base()}"
        response["Content-Disposition"] = f"attachment; filename={file_name}.xlsx"
        return response

I converted my Query to Dataframe and export it via response

Django export to xlsx with lock columns

1 Answers