Django技术问题：如何从Excel表授权账号及用学生代码创建账号

Hey there! Let's work through these two Django tasks involving Excel data. I'll walk you through practical, reusable solutions with code snippets that fit right into a Django project.

First, we'll read the Excel data, match users in your Django database, and assign the specified permissions. The cleanest way to do this in Django is using a custom management command (since it automatically loads your project's environment).

Step 1: Install Required Libraries #

You'll need pandas to read Excel files and openpyxl to handle .xlsx files:

pip install pandas openpyxl

Step 2: Create a Custom Management Command #

In your Django app, create a management/commands directory (if it doesn't exist), then add a file named grant_user_permissions.py:

from django.core.management.base import BaseCommand
from django.contrib.auth.models import User, Permission
import pandas as pd

class Command(BaseCommand):
    help = 'Grant permissions to users from an Excel sheet'

    def add_arguments(self, parser):
        parser.add_argument('excel_file', type=str, help='Path to the Excel file')

    def handle(self, *args, **options):
        # Read Excel file - adjust the sheet name if needed
        df = pd.read_excel(options['excel_file'], sheet_name='Users')

        # Iterate over each row in the Excel sheet
        for index, row in df.iterrows():
            # Assume your Excel has columns: username, permission_codenames (comma-separated)
            username = row['username']
            permission_codes = row['permission_codenames'].split(',')

            try:
                user = User.objects.get(username=username)
            except User.DoesNotExist:
                self.stdout.write(self.style.ERROR(f"User {username} does not exist - skipping"))
                continue

            # Assign each permission to the user
            for code in permission_codes:
                code = code.strip()
                try:
                    permission = Permission.objects.get(codename=code)
                    user.user_permissions.add(permission)
                    self.stdout.write(self.style.SUCCESS(f"Added permission {code} to {username}"))
                except Permission.DoesNotExist:
                    self.stdout.write(self.style.ERROR(f"Permission {code} does not exist - skipping for {username}"))

Step 3: Run the Command #

Execute it from your project root:

python manage.py grant_user_permissions path/to/your/users.xlsx

Note: Make sure your Excel sheet has columns username (matching Django user usernames) and permission_codenames (comma-separated permission codenames like add_post, change_user).

For this task, we'll read student codes from Excel, create Django users with the code as both username and password (remember to hash the password!), and handle duplicates gracefully.

Step 1: Same Dependencies #

You can use the same libraries as before (pandas and openpyxl), so no extra installs needed if you completed step 1.

Step 2: Create Another Management Command #

Add a file named create_student_users.py in the same management/commands directory:

from django.core.management.base import BaseCommand
from django.contrib.auth.models import User
from django.contrib.auth.hashers import make_password
import pandas as pd

class Command(BaseCommand):
    help = 'Create student users from Excel using student code as username and password'

    def add_arguments(self, parser):
        parser.add_argument('excel_file', type=str, help='Path to the Excel file with student codes')

    def handle(self, *args, **options):
        df = pd.read_excel(options['excel_file'], sheet_name='Students')

        for index, row in df.iterrows():
            # Assume your Excel has a column named 'student_code'
            student_code = str(row['student_code']).strip()

            # Check if user already exists
            if User.objects.filter(username=student_code).exists():
                self.stdout.write(self.style.WARNING(f"User {student_code} already exists - skipping"))
                continue

            # Create the user with hashed password
            user = User.objects.create(
                username=student_code,
                password=make_password(student_code),
                is_active=True  # Set to False if you want to require email verification first
            )
            self.stdout.write(self.style.SUCCESS(f"Created user {student_code} successfully"))

Step 3: Run the Command #

Execute from your project root:

python manage.py create_student_users path/to/your/students.xlsx

Note: Your Excel sheet should have a student_code column with unique values. If you want to add extra fields like email or first name, just extend the create() method with those columns from your Excel.

内容的提问来源于stack exchange，提问作者Trần Huy