Achievement Qztc

课程目标达成情况分析表生成工具。根据Excel学生数据替换Word模板中的课程目标达成情况，生成新的分析表。

Security Scan

The skill's purpose (generate Word analysis from Excel) matches its instructions, but there are multiple implementation inconsistencies and missing dependency/usage details that can break or cause unintended local file changes — review before using.

Purpose & Capability

Name/description match the instructions: the SKILL.md shows Python code that reads an Excel file and fills a Word template to produce an analysis table (including randomly generated scores). However the skill does not declare that it requires Python, pandas, python-docx, or a writable filesystem, which are necessary for the documented operations.

Instruction Scope

Instructions reference absolute, user-specific local paths and will read/write files on the user's Desktop (copying templates and writing output). This is expected for the stated purpose but is sensitive — the instructions will modify files locally and can overwrite outputs without explicit confirmation. Also there are coding inconsistencies in the provided snippets (e.g., the function defined is replace_text_in_table_cell but code calls replace_text_in_cell), and the SKILL.md contains mismatched paths (/Volumes/... vs /Users/...), which will likely cause runtime errors or accidental use of the wrong files.

Install Mechanism

No install spec (instruction-only), which reduces supply-chain risk. But the instructions import pandas and python-docx; the skill fails to document required runtime packages or Python version. Users must ensure appropriate Python environment and dependencies are installed before running the code.

Credentials

The skill requests no environment variables, credentials, or external network access in the instructions. All operations are local file reads/writes. That is proportional to the stated function.

Persistence & Privilege

always is false and the skill is instruction-only with no install step; it does not request persistent/system-wide privileges. The only effect is local file modification as part of its normal operation.

What to consider before installing

This skill appears to do what it says (generate Word analysis from an Excel file) but has several red flags you should address before running it: - Hardcoded, user-specific file paths: the SKILL.md points to absolute paths on /Volumes and /Users. Edit these paths to point to safe sample files or copies, or the script will fail or overwrite files on your Desktop. - Missing dependency documentation: the code uses Python, pandas, and python-docx but the skill doesn't declare them. Create a controlled Python environment and install required packages (pip install pandas python-docx) before running. - Code bugs / name mismatches: there is at least one function-name mismatch (replace_text_in_table_cell vs replace_text_in_cell) and truncated/partial code; expect runtime errors. Review and test the script line-by-line on sample data. - Overwrite risk: the script copies and writes Word documents in-place. Back up your templates and important files first. - Behavior check: the tool intentionally generates random scores (it documents '随机生成成绩'). If you expect to use real scores, modify the code accordingly. If you want to proceed, run this in an isolated environment (test directory or VM) after fixing paths and dependencies, and verify outputs on sample data. If you need, ask for a cleaned, corrected version of the script with dependency and path configuration options added.

课程目标达成情况分析表生成工具（QZTC版）

根据Excel学生数据替换Word模板中的课程目标达成情况，生成新的分析表。

适用场景

• 根据Excel学生名单生成课程目标达成情况分析表
• 自动计算各课程目标的达成值（随机生成成绩，百分比表示）

---

文件路径

• 模板文件: /Volumes/qztcm09/Desktop/temp/课程目标达成情况分析表-数据可视化-模版.docx
• Excel数据: /Volumes/qztcm09/Desktop/temp/数据可视化技术23级计算机.xls

---

使用步骤

步骤1：读取Excel数据

import pandas as pd
import shutil
import random
from docx import Document
from datetime import datetime

# 读取Excel
df = pd.read_excel('/Users/qztcm09/Desktop/temp/数据可视化技术23级计算机.xls')

# 排除旷考学生
df = df[df['备注'] != '旷考'].reset_index(drop=True)

# 自动判断当前学年和学期
now = datetime.now()
year = now.year
month = now.month
day= now.day

if 1 <= month <= 6:
    # 1-6月：第一学期（上学年）
    academic_year = f"{year-1} - {year}"
    semester = "一"
else:
    # 7-12月：第二学期（上学年）
    academic_year = f"{year-1} - {year}"
    semester = "二"

print(f"当前学年: {academic_year}, 学期: {semester}学期")

# 从"班级"字段提取年级、班级、专业信息
# 专业名称提取支持两种情况：
#   情况1: "23级计算机" → 年级=23, 专业=计算机
#   情况2: "23级软工2班" → 年级=23, 专业=软工
import re
class_name = df['班级'].iloc[0] if '班级' in df.columns else ''
match = re.search(r'(\d+)级', str(class_name))
grade = match.group(1) if match else ''  # 如 "23"
# 提取专业：匹配 "XX级" 后面到 "XX班" 或结尾的部分
match = re.search(r'\d+级(.+?)(?:\d+班)?$', str(class_name))
major = match.group(1).strip() if match else ''  # 如 "计算机" 或 "软工"


# 获取学生信息
students = df[['学号', '姓名']].copy()
print(f"学生人数: {len(students)}")

步骤2：复制模板并打开

template_path = '/Users/qztcm09/Desktop/temp/课程目标达成情况分析表-数据可视化-模版.docx'
output_path = '/Users/qztcm09/Desktop/temp/课程目标达成情况分析表-数据可视化-23级计算机.docx'

shutil.copy(template_path, output_path)
doc = Document(output_path)

步骤3：通用文本替换

⚠️ 重要：Word占位符可能被拆分成多个run，且表格单元格中可能有换行，需要特殊处理，保留原格式

def replace_text_preserve_format(para, replacements):
    """替换段落文本但保留格式"""
    # 先收集所有run的文本
    full_text = ''.join(run.text for run in para.runs if run.text)
    
    # 检查是否有占位符
    has_placeholder = any(old in full_text for old, _ in replacements)
    if not has_placeholder:
        return
    
    # 执行替换
    for old, new in replacements:
        full_text = full_text.replace(old, new)
    
    # 获取第一个run的格式作为基准
    if para.runs:
        first_run = para.runs[0]
        font_name = first_run.font.name
        font_size = first_run.font.size
        font_bold = first_run.font.bold
        font_italic = first_run.font.italic
    else:
        font_name, font_size, font_bold, font_italic = None, None, None, None
    
    # 清空并用新文本创建单一run，保留格式
    para.clear()
    run = para.add_run(full_text)
    if font_name:
        run.font.name = font_name
    if font_size:
        run.font.size = font_size
    if font_bold is not None:
        run.font.bold = font_bold
    if font_italic is not None:
        run.font.italic = font_italic
    
    return para

def replace_text_in_table_cell(cell, replacements):
    """替换表格单元格中的文本（处理单元格内换行的情况）"""
    # 遍历单元格中的所有段落
    for para in cell.paragraphs:
        full_text = ''.join(run.text for run in para.runs if run.text)
        has_placeholder = any(old in full_text for old, _ in replacements)
        
        if has_placeholder:
            # 获取格式
            if para.runs:
                first_run = para.runs[0]
                font_name = first_run.font.name
                font_size = first_run.font.size
                font_bold = first_run.font.bold
                font_italic = first_run.font.italic
            else:
                font_name, font_size, font_bold, font_italic = None, None, None, None
            
            # 执行替换
            for old, new in replacements:
                full_text = full_text.replace(old, new)
            
            # 清空并重新设置
            para.clear()
            run = para.add_run(full_text)
            if font_name:
                run.font.name = font_name
            if font_size:
                run.font.size = font_size
            if font_bold is not None:
                run.font.bold = font_bold
            if font_italic is not None:
                run.font.italic = font_italic
    
    return cell

# 替换配置
replacements = [
    ('$acyear$', academic_year),
    ('$semester$', semester),
    ('$g$', grade),
    ('$major$', major),
    ('$total$', f'{len(students)}人'),
    ('$year$', str(year)),
    ('$month$', str(month)),
    ('$day$', str(day)),
]

# 替换表格中的文本
for table in doc.tables:
    for row in table.rows:
        for cell in row.cells:
            replace_text_in_cell(cell, replacements)

# 替换段落中的文本（处理被拆分的情况，保留格式）
for para in doc.paragraphs:
    replace_text_preserve_format(para, replacements)

步骤4：更新表8 - 课程目标个体达成情况明细

重要：

1. 先清理除表头（2行）和最后一行（平均值）外的所有学生数据
2. 根据Excel学生数动态调整表格行数
3. 在最后一行（平均值行）之前插入新行
4. 所有达成值用百分比表示（如 66%）

表格索引: 8

表头结构（前2行）:

• 行0: 序号 | 学号 | 姓名 | 课程目标1(25分) | 课程目标1 | 课程目标2(30分) | 课程目标2 | 课程目标3(26.5分) | 课程目标3 | 课程目标4(18.5分) | 课程目标4
• 行1: 序号 | 学号 | 姓名 | 得分 | 达成值 | 得分 | 达成值 | 得分 | 达成值 | 得分 | 达成值

数据列对应关系:

列索引	内容
0	序号
1	学号
2	姓名
3	随机值 a (10-24)，课程目标1得分
4	a/25*100%，课程目标1达成值（百分比）
5	随机值 b (20-28)，课程目标2得分
6	b/30*100%，课程目标2达成值（百分比）
7	随机值 c (20-25)，课程目标3得分
8	c/26.5*100%，课程目标3达成值（百分比）
9	随机值 d (15-18)，课程目标4得分
10	d/18.5*100%，课程目标4达成值（百分比）

# 步骤4.1: 动态调整表格行数（在平均值行之前插入）
table = doc.tables[8]
current_rows = len(table.rows)
# 当前模板可以容纳的学生数（去掉2行表头和1行平均值）
template_capacity = current_rows - 3

# 调整行数：在倒数第2行（平均值行之前）插入
if len(students) > template_capacity:
    # 需要插入行，在平均值行之前插入
    for i in range(len(students) - template_capacity):
        # 在倒数第2行之前插入（即平均值行之前）
        new_row = table.add_row()
        # 将新行移动到平均值行之前
        # 由于add_row()是添加到末尾，需要交换位置
        # 获取平均值行索引
        avg_idx = len(table.rows) - 1
        # 将新插入的行移到平均值行之前
        table._element.remove(new_row._element)
        table._element.insert(len(table.rows) - 2, new_row._element)
elif len(students) < template_capacity:
    # 需要删除多余行（从数据区域末尾开始删，保留平均值行）
    for i in range(template_capacity - len(students)):
        # 删除倒数第2行（最后一个数据行）
        delete_idx = 2 + len(students)
        if delete_idx < len(table.rows) - 1:
            table._element.remove(table.rows[delete_idx]._element)

# 步骤4.2: 清理数据行（保留前2行表头和最后1行平均值）
for row_idx in range(2, len(table.rows) - 1):
    for cell in table.rows[row_idx].cells:
        cell.text = ''

# 步骤4.3: 填入学生数据（达成值用百分比）
all_a, all_b, all_c, all_d = [], [], [], []
student_data = []

for i, (_, student) in enumerate(students.iterrows()):
    row_idx = i + 2
    if row_idx >= len(table.rows) - 1:
        break
    
    row = table.rows[row_idx]
    a = random.randint(10, 24)
    b = random.randint(20, 28)
    c = random.randint(20, 25)
    d = random.randint(15, 18)
    
    all_a.append(a)
    all_b.append(b)
    all_c.append(c)
    all_d.append(d)
    
    # 转为百分比
    v1, v2, v3, v4 = a/25*100, b/30*100, c/26.5*100, d/18.5*100
    student_data.append({'v1': v1, 'v2': v2, 'v3': v3, 'v4': v4})
    
    row.cells[0].text = str(i + 1)
    row.cells[1].text = str(student['学号'])
    row.cells[2].text = student['姓名']
    row.cells[3].text = f"{a:.2f}"
    row.cells[4].text = f"{v1:.0f}%"  # 百分比
    row.cells[5].text = f"{b:.2f}"
    row.cells[6].text = f"{v2:.0f}%"  # 百分比
    row.cells[7].text = f"{c:.2f}"
    row.cells[8].text = f"{v3:.0f}%"  # 百分比
    row.cells[9].text = f"{d:.2f}"
    row.cells[10].text = f"{v4:.0f}%"  # 百分比

n = len(student_data)

# 步骤4.4: 最后一行填写平均值（百分比）
avg_row = table.rows[-1]
avg_a = sum(all_a) / n
avg_b = sum(all_b) / n
avg_c = sum(all_c) / n
avg_d = sum(all_d) / n
avg_v1 = sum(s['v1'] for s in student_data) / n
avg_v2 = sum(s['v2'] for s in student_data) / n
avg_v3 = sum(s['v3'] for s in student_data) / n
avg_v4 = sum(s['v4'] for s in student_data) / n

avg_row.cells[0].text = "平均值"
avg_row.cells[3].text = f"{avg_a:.2f}"
avg_row.cells[4].text = f"{avg_v1:.0f}%"
avg_row.cells[5].text = f"{avg_b:.2f}"
avg_row.cells[6].text = f"{avg_v2:.0f}%"
avg_row.cells[7].text = f"{avg_c:.2f}"
avg_row.cells[8].text = f"{avg_v3:.0f}%"
avg_row.cells[9].text = f"{avg_d:.2f}"
avg_row.cells[10].text = f"{avg_v4:.0f}%"

步骤5：更新表7汇总数据（百分比）

table7 = doc.tables[7]
table7.rows[5].cells[7].text = f"{avg_v1:.0f}%"
table7.rows[8].cells[7].text = f"{avg_v2:.0f}%"
table7.rows[11].cells[7].text = f"{avg_v3:.0f}%"
table7.rows[14].cells[7].text = f"{avg_v4:.0f}%"

步骤6：更新课程目标分析段落（百分比）

# 计算统计数据
max_v1 = max(s['v1'] for s in student_data)
min_v1 = min(s['v1'] for s in student_data)
# ... 其他目标类似

# 计算满足百分比阈值的学生比例
count_v1_80 = sum(1 for s in student_data if s['v1'] >= 80)
count_v1_60 = sum(1 for s in student_data if s['v1'] >= 60)
# ... 其他类似

pct_v1_80 = count_v1_80 / n * 100
pct_v1_60 = count_v1_60 / n * 100
# ... 其他类似

# 更新段落
new_texts = {
    1: f"课程目标1的平均达成值为{avg_v1:.0f}%，最高达成值为{max_v1:.0f}%，最低达成值为{min_v1:.0f}%。其中{pct_v1_80:.1f}%的学生达成值在80%以上，{pct_v1_60:.1f}%的学生达成值在60%以上，说明...",
    # ... 其他目标
}

# 找到并更新段落
for i, para in enumerate(doc.paragraphs):
    if '课程目标1的平均达成值为' in para.text:
        # 更新段落文本
        break

步骤7：保存

doc.save(output_path)
print(f"文件已保存至: {output_path}")

---

完整代码示例

import pandas as pd
import shutil
import random
from docx import Document
from datetime import datetime

random.seed(42)  # 可选：固定随机种子

def replace_text_preserve_format(para, replacements):
    """替换段落文本但保留格式"""
    # 先收集所有run的文本
    full_text = ''.join(run.text for run in para.runs if run.text)
    
    # 检查是否有占位符
    has_placeholder = any(old in full_text for old, _ in replacements)
    if not has_placeholder:
        return
    
    # 执行替换
    for old, new in replacements:
        full_text = full_text.replace(old, new)
    
    # 获取第一个run的格式作为基准
    if para.runs:
        first_run = para.runs[0]
        font_name = first_run.font.name
        font_size = first_run.font.size
        font_bold = first_run.font.bold
        font_italic = first_run.font.italic
    else:
        font_name, font_size, font_bold, font_italic = None, None, None, None
    
    # 清空并用新文本创建单一run，保留格式
    para.clear()
    run = para.add_run(full_text)
    if font_name:
        run.font.name = font_name
    if font_size:
        run.font.size = font_size
    if font_bold is not None:
        run.font.bold = font_bold
    if font_italic is not None:
        run.font.italic = font_italic
    
    return para

def replace_text_in_table_cell(cell, replacements):
    """替换表格单元格中的文本（处理单元格内换行的情况）"""
    for para in cell.paragraphs:
        full_text = ''.join(run.text for run in para.runs if run.text)
        has_placeholder = any(old in full_text for old, _ in replacements)
        
        if has_placeholder:
            if para.runs:
                first_run = para.runs[0]
                font_name = first_run.font.name
                font_size = first_run.font.size
                font_bold = first_run.font.bold
                font_italic = first_run.font.italic
            else:
                font_name, font_size, font_bold, font_italic = None, None, None, None
            
            for old, new in replacements:
                full_text = full_text.replace(old, new)
            
            para.clear()
            run = para.add_run(full_text)
            if font_name:
                run.font.name = font_name
            if font_size:
                run.font.size = font_size
            if font_bold is not None:
                run.font.bold = font_bold
            if font_italic is not None:
                run.font.italic = font_italic
    
    return cell

def generate_achievement_analysis():
    # 1. 读取Excel
    df = pd.read_excel('/Users/qztcm09/Desktop/temp/数据可视化技术23级计算机.xls')
    df = df[df['备注'] != '旷考'].reset_index(drop=True)
    students = df[['学号', '姓名']].copy()
    print(f"学生人数: {len(students)}")
    
    # 2. 复制模板
    template_path = '/Users/qztcm09/Desktop/temp/课程目标达成情况分析表-数据可视化-模版.docx'
    output_path = '/Users/qztcm09/Desktop/temp/课程目标达成情况分析表-数据可视化-23级计算机.docx'
    shutil.copy(template_path, output_path)
    doc = Document(output_path)
    
    # 3. 通用文本替换
    now = datetime.now()
    year, month, day = now.year, now.month, now.day
    
    # 根据班级提取专业信息（支持两种情况）
    # 情况1: "23级计算机" → 专业="计算机"
    # 情况2: "23级软工2班" → 专业="软工"
    import re
    class_name = df['班级'].iloc[0] if '班级' in df.columns else ''
    match = re.search(r'(\d+)级', str(class_name))
    grade = match.group(1) if match else ''
    match = re.search(r'\d+级(.+?)(?:\d+班)?$', str(class_name))
    major = match.group(1).strip() if match else ''
    
    if 1 <= month <= 6:
        academic_year = f"{year-1} - {year}"
        semester = "一"
    else:
        academic_year = f"{year-1} - {year}"
        semester = "二"
    
    replacements = [
        ('$acyear$', academic_year),
        ('$semester$', semester),
        ('$g$', grade),
        ('$major$', major),
        ('$total$', f'{len(students)}人'),
        ('$year$', str(year)),
        ('$month$', str(month)),
        ('$day$', str(day)),
    ]
    
    # 替换表格中的文本（使用新的函数处理换行）
    for table in doc.tables:
        for row in table.rows:
            for cell in row.cells:
                replace_text_in_table_cell(cell, replacements)
    
    # 替换段落中的文本（保留格式）
    for para in doc.paragraphs:
        replace_text_preserve_format(para, replacements)
    
    # 4. 更新表8
    table = doc.tables[8]
    current_rows = len(table.rows)
    template_capacity = current_rows - 3
    
    # 调整行数
    if len(students) > template_capacity:
        # 需要插入行，在平均值行之前插入
        rows_to_add = len(students) - template_capacity
        for i in range(rows_to_add):
            # 在倒数第2行（平均值行之前）插入新行
            new_row = table.add_row()
            # 将新行从末尾移到平均值行之前
            avg_idx = len(table.rows) - 1
            # 获取新行的tr元素
            new_tr = new_row._element
            # 移除
            table._element.remove(new_tr)
            # 在平均值行之前插入
            table._element.insert(avg_idx, new_tr)
        print(f"插入{rows_to_add}行")
    elif len(students) < template_capacity:
        # 需要删除多余行
        rows_to_remove = template_capacity - len(students)
        for i in range(rows_to_remove):
            delete_idx = 2 + len(students)
            if delete_idx < len(table.rows) - 1:
                table._element.remove(table.rows[delete_idx]._element)
        print(f"删除{rows_to_remove}行")
    
    # 清理数据行
    for row_idx in range(2, len(table.rows) - 1):
        for cell in table.rows[row_idx].cells:
            cell.text = ''
    
    # 填入数据
    all_a, all_b, all_c, all_d = [], [], [], []
    student_data = []
    
    for i, (_, student) in enumerate(students.iterrows()):
        row_idx = i + 2
        if row_idx >= len(table.rows) - 1:
            break
        
        row = table.rows[row_idx]
        a = random.randint(10, 24)
        b = random.randint(20, 28)
        c = random.randint(20, 25)
        d = random.randint(15, 18)
        
        all_a.append(a)
        all_b.append(b)
        all_c.append(c)
        all_d.append(d)
        
        v1, v2, v3, v4 = a/25*100, b/30*100, c/26.5*100, d/18.5*100
        student_data.append({'v1': v1, 'v2': v2, 'v3': v3, 'v4': v4})
        
        row.cells[0].text = str(i + 1)
        row.cells[1].text = str(student['学号'])
        row.cells[2].text = student['姓名']
        row.cells[3].text = f"{a:.2f}"
        row.cells[4].text = f"{v1:.0f}%"
        row.cells[5].text = f"{b:.2f}"
        row.cells[6].text = f"{v2:.0f}%"
        row.cells[7].text = f"{c:.2f}"
        row.cells[8].text = f"{v3:.0f}%"
        row.cells[9].text = f"{d:.2f}"
        row.cells[10].text = f"{v4:.0f}%"
    
    n = len(student_data)
    
    # 平均值（最后一行）
    avg_row = table.rows[-1]
    avg_a = sum(all_a) / n
    avg_b = sum(all_b) / n
    avg_c = sum(all_c) / n
    avg_d = sum(all_d) / n
    avg_v1 = sum(s['v1'] for s in student_data) / n
    avg_v2 = sum(s['v2'] for s in student_data) / n
    avg_v3 = sum(s['v3'] for s in student_data) / n
    avg_v4 = sum(s['v4'] for s in student_data) / n
    
    avg_row.cells[0].text = "平均值"
    avg_row.cells[1].text = ""
    avg_row.cells[2].text = ""
    avg_row.cells[3].text = f"{avg_a:.2f}"
    avg_row.cells[4].text = f"{avg_v1:.0f}%"
    avg_row.cells[5].text = f"{avg_b:.2f}"
    avg_row.cells[6].text = f"{avg_v2:.0f}%"
    avg_row.cells[7].text = f"{avg_c:.2f}"
    avg_row.cells[8].text = f"{avg_v3:.0f}%"
    avg_row.cells[9].text = f"{avg_d:.2f}"
    avg_row.cells[10].text = f"{avg_v4:.0f}%"
    
    # 5. 更新表7
    table7 = doc.tables[7]
    table7.rows[5].cells[7].text = f"{avg_v1:.0f}%"
    table7.rows[8].cells[7].text = f"{avg_v2:.0f}%"
    table7.rows[11].cells[7].text = f"{avg_v3:.0f}%"
    table7.rows[14].cells[7].text = f"{avg_v4:.0f}%"
    
    # 6. 统计并更新段落（见步骤6）
    # ...
    
    # 7. 保存
    doc.save(output_path)
    print(f"完成！文件已保存至: {output_path}")

if __name__ == '__main__':
    generate_achievement_analysis()

---

⚠️ 注意事项

1. 达成值格式

• 所有达成值使用百分比表示（如 66%、85%）
• 得分保持原始数值（如 16.50）

2. 表格行数调整

• 模板容量 = 表格总行数 - 2（表头）- 1（平均值）
• 学生数多：在平均值行之前插入新行
• 学生数少：删除多余的数据行

3. 随机成绩范围

课程目标	得分范围	满分	达成值计算
目标1	10-24	25	a/25*100%
目标2	20-29	30	b/30*100%
目标3	20-25	26.5	c/26.5*100%
目标4	15-18	18.5	d/18.5*100%

4. 模板预处理

使用前确保模板已修正：

• 去除黄色高亮
• 修正占位符（如 major$ → $major$）
• 扩展表8容量（如需要）