Django改造CSV导出为XLSX并锁定指定列（如id、Subsidiary）

要实现导出XLSX文件并锁定指定列，你需要用到openpyxl库（它支持操作Excel的单元格保护和格式设置），步骤如下：

1. 安装依赖库 #

先安装openpyxl：

pip install openpyxl

2. 修改views.py中的导出函数 #

替换原来的CSV导出代码，改为生成带列锁定的XLSX文件：

from django.http import HttpResponse
from openpyxl import Workbook
from openpyxl.styles import Protection
from openpyxl.utils import get_column_letter
from .models import Combination

def combinationExport(request):
    combination = Combination.objects.all()
    
    # 创建Excel工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "Combination"
    
    # 定义表头
    headers = [
        "id",
        "Subsidiary",
        "Department",
        "Account",
        "Sub Budget",
        "Budget Owner",
        "Sub Budget Owner",
    ]
    ws.append(headers)
    
    # 获取数据
    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 row in combo:
        ws.append(row)
    
    # 先解除所有单元格的锁定状态（Excel默认单元格是锁定的）
    for row in ws.iter_rows():
        for cell in row:
            cell.protection = Protection(locked=False)
    
    # 锁定指定列：id对应A列、Subsidiary对应B列（索引0和1）
    locked_columns = [0, 1]
    for col_idx in locked_columns:
        col_letter = get_column_letter(col_idx + 1)  # openpyxl列序号从1开始
        for cell in ws[col_letter]:
            cell.protection = Protection(locked=True)
    
    # 启用工作表保护（无密码，如需密码可添加ws.protection.password = "your_pwd"）
    ws.protection.sheet = True
    
    # 配置响应参数
    response = HttpResponse(content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response["Content-Disposition"] = 'attachment; filename="Combination.xlsx"'
    
    # 保存工作簿到响应流
    wb.save(response)
    return response

关键说明 #

• 先解除所有单元格锁定，再单独设置需要保护的列，确保其他列默认可编辑
• 必须启用工作表保护，锁定列的设置才会生效
• 若需要密码验证才能修改工作表，可取消注释密码设置行

内容的提问来源于stack exchange，提问作者user13719970