在Django站点中创建可编辑类电子表格表格的技术实现问询

1. 数据库模型设计 #

先定义存储单元格状态的核心模型，确保每个日期+学科的组合唯一：

# models.py
from django.db import models
from django.contrib.auth.models import User

class Subject(models.Model):
    name = models.CharField(max_length=50, unique=True)

    def __str__(self):
        return self.name

class DailyPlan(models.Model):
    date = models.DateField()
    subject = models.ForeignKey(Subject, on_delete=models.CASCADE)
    content = models.TextField(blank=True)
    is_highlighted = models.BooleanField(default=False)
    has_homework = models.BooleanField(default=False)
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        unique_together = ('date', 'subject')  # 避免重复数据

2. Django后端视图与URL配置 #

2.1 表格渲染视图 #

生成一年的日期范围，预加载已有计划数据，减少数据库查询次数：

# views.py
from django.shortcuts import render
from django.utils import timezone
from datetime import timedelta
from .models import Subject, DailyPlan

def plan_table(request):
    subjects = Subject.objects.all()
    # 生成当前日期往后一年的日期列表
    start_date = timezone.localdate()
    end_date = start_date + timedelta(days=365)
    date_range = [start_date + timedelta(days=i) for i in range((end_date - start_date).days)]
    
    # 批量加载计划数据，转成字典方便模板查找
    plan_data = DailyPlan.objects.filter(date__range=(start_date, end_date))
    plan_dict = {(p.date, p.subject_id): p for p in plan_data}

    return render(request, 'plan_table.html', {
        'subjects': subjects,
        'date_range': date_range,
        'plan_dict': plan_dict,
    })

2.2 单元格更新API视图 #

处理前端AJAX请求，实现数据的新增/更新：

# views.py
from django.http import JsonResponse
from django.views.decorators.http import require_POST
from django.views.decorators.csrf import csrf_exempt
from .models import DailyPlan, Subject
from datetime import datetime

@csrf_exempt
@require_POST
def update_cell(request):
    try:
        date_str = request.POST.get('date')
        subject_id = request.POST.get('subject_id')
        content = request.POST.get('content', '')
        is_highlighted = request.POST.get('is_highlighted') == 'true'
        has_homework = request.POST.get('has_homework') == 'true'

        date = datetime.strptime(date_str, '%Y-%m-%d').date()
        subject = Subject.objects.get(id=subject_id)

        # 存在则更新，不存在则创建
        plan, created = DailyPlan.objects.get_or_create(
            date=date,
            subject=subject,
            defaults={
                'content': content,
                'is_highlighted': is_highlighted,
                'has_homework': has_homework,
                'updated_by': request.user if request.user.is_authenticated else None
            }
        )
        if not created:
            plan.content = content
            plan.is_highlighted = is_highlighted
            plan.has_homework = has_homework
            if request.user.is_authenticated:
                plan.updated_by = request.user
            plan.save()

        return JsonResponse({'status': 'success', 'plan_id': plan.id})
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

2.3 URL配置 #

# urls.py
from django.urls import path
from . import views

urlpatterns = [
    path('plan/', views.plan_table, name='plan_table'),
    path('update-cell/', views.update_cell, name='update_cell'),
]

3. 前端模板实现 #

创建plan_table.html，实现可编辑单元格、样式控制和AJAX实时提交：

<!-- templates/plan_table.html -->
<!DOCTYPE html>
<html>
<head>
    <title>年度学科规划表格</title>
    {% load plan_tags %} <!-- 加载自定义模板过滤器 -->
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
            min-width: 120px;
        }
        th {
            background-color: #f2f2f2;
            position: sticky;
            top: 0;
        }
        .date-column {
            position: sticky;
            left: 0;
            background-color: #f2f2f2;
            font-weight: bold;
        }
        .highlighted {
            background-color: #fff3cd;
        }
        .has-homework {
            border-left: 4px solid #dc3545;
        }
        .cell-controls {
            display: none;
            margin-top: 4px;
            gap: 4px;
            flex-wrap: wrap;
        }
        td:hover .cell-controls {
            display: flex;
        }
        button {
            font-size: 12px;
            padding: 2px 6px;
            cursor: pointer;
        }
    </style>
</head>
<body>
    <h1>年度学科规划表格</h1>
    <table>
        <thead>
            <tr>
                <th class="date-column">日期</th>
                {% for subject in subjects %}
                <th>{{ subject.name }}</th>
                {% endfor %}
            </tr>
        </thead>
        <tbody>
            {% for date in date_range %}
            <tr>
                <td class="date-column">{{ date|date:"Y-m-d" }}</td>
                {% for subject in subjects %}
                {% with plan=plan_dict|get_item:date|get_item:subject.id %}
                <td 
                    class="{% if plan.is_highlighted %}highlighted{% endif %} {% if plan.has_homework %}has-homework{% endif %}"
                    data-date="{{ date|date:"Y-m-d" }}"
                    data-subject-id="{{ subject.id }}"
                    contenteditable="true"
                >
                    {{ plan.content|default:"" }}
                    <div class="cell-controls">
                        <button class="toggle-highlight" type="button">
                            {% if plan.is_highlighted %}取消高亮{% else %}高亮{% endif %}
                        </button>
                        <button class="toggle-homework" type="button">
                            {% if plan.has_homework %}取消作业{% else %}标记作业{% endif %}
                        </button>
                    </div>
                </td>
                {% endwith %}
                {% endfor %}
            </tr>
            {% endfor %}
        </tbody>
    </table>

    <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script>
    <script>
        // 单元格内容编辑后保存
        $('td[contenteditable="true"]').on('blur', function() {
            const $cell = $(this);
            submitCellUpdate($cell);
        });

        // 高亮按钮切换
        $('.toggle-highlight').on('click', function(e) {
            e.stopPropagation();
            const $cell = $(this).closest('td');
            $cell.toggleClass('highlighted');
            $(this).text($cell.hasClass('highlighted') ? '取消高亮' : '高亮');
            submitCellUpdate($cell);
        });

        // 作业标记切换
        $('.toggle-homework').on('click', function(e) {
            e.stopPropagation();
            const $cell = $(this).closest('td');
            $cell.toggleClass('has-homework');
            $(this).text($cell.hasClass('has-homework') ? '取消作业' : '标记作业');
            submitCellUpdate($cell);
        });

        // 统一提交更新的函数
        function submitCellUpdate($cell) {
            const date = $cell.data('date');
            const subjectId = $cell.data('subject-id');
            const content = $cell.text().trim();
            const isHighlighted = $cell.hasClass('highlighted');
            const hasHomework = $cell.hasClass('has-homework');

            $.post('{% url "update_cell" %}', {
                date: date,
                subject_id: subjectId,
                content: content,
                is_highlighted: isHighlighted,
                has_homework: hasHomework,
                csrfmiddlewaretoken: '{{ csrf_token }}'
            }, function(response) {
                if (response.status !== 'success') {
                    alert('保存失败：' + response.message);
                }
            }).fail(function() {
                alert('网络错误，保存失败');
            });
        }
    </script>
</body>
</html>

4. 自定义模板过滤器 #

在templatetags/plan_tags.py中创建过滤器，用于模板中访问字典值：

# templatetags/plan_tags.py
from django import template

register = template.Library()

@register.filter
def get_item(dictionary, key):
    return dictionary.get(key)

5. 实时同步优化（可选） #

如果需要多用户实时同步更新，可以用Django Channels实现WebSocket：

1. 配置Channels环境，创建消费者监听DailyPlan模型的保存事件
2. 前端建立WebSocket连接，收到后端推送的更新消息后，自动刷新对应单元格的内容和样式

6. 注意事项 #

• 性能优化：一年365行+多学科会生成大量单元格，建议实现滚动懒加载或按月分页
• 权限控制：添加用户验证，限制不同用户的编辑权限
• 冲突处理：添加version字段实现乐观锁，解决多用户并发编辑冲突
• 数据验证：后端对日期、学科ID等参数做严格校验，防止非法数据写入

内容的提问来源于stack exchange，提问作者HTTP ERROR 500