把单词列表转为单写默写卡

word_to_card.py

"""
单词默写表格生成小工具
读取包含了单词以及中文释义两列的 Excel 文件（也可以是 csv 文件）
然后按照 A4 纸大小排列可这翻折的单词默写表格形式
每页 48 个单词，每个单词都需要默写中文释义以及中文对应的单词
"""
import pandas as pd
import openpyxl
from openpyxl.styles import Alignment, Border, Side
from openpyxl.worksheet.page import PageMargins
from openpyxl.worksheet.pagebreak import Break, PageBreak

words_per_page = 48
lines_per_page = words_per_page + 1
page_breaks = PageBreak()
def set_row_height(ws, height=25):
    """设置所有行的行高"""
    for row in ws.rows:
        ws.row_dimensions[row[0].row].height = height

# 在保存文件前添加打印设置
def set_print_settings(ws):
    # 设置打印区域
    ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT  # 纵向打印
    ws.page_setup.paperSize = ws.PAPERSIZE_A4  # A4纸
    ws.page_setup.fitToPage = True  # 调整为一页
    ws.page_setup.fitToHeight = False  # 不自动调整高度
    ws.page_setup.fitToWidth = 1  # 宽度调整为1页

    # 设置页边距（单位：英寸）
    ws.page_margins = PageMargins(left=0.7, right=0.7, top=0.75, bottom=0.75)

    # 设置打印网格线
    ws.print_gridlines = True

    # 在每22行后添加分页符
    total_rows = ws.max_row
    page_breaks = PageBreak()
    idx = 0
    for row in range(lines_per_page, total_rows, lines_per_page):
        page_breaks.append(Break(id=row))
        idx += 1

    ws.row_breaks = page_breaks

def process_chinese(ch):
    """
    处理中文释义

    但中译英中，不需要那么多中文意思，取其中第一个就可以了，避免太长导致一行放不下
    我们按照顺序分别采取逗号，封号，空格来进行分割，如果第一次分割成功就返回
    """
    if ';' in ch:
        return ch.split(';')[0].strip()
    elif ',' in ch:
        return ch.split(',')[0].strip()
    elif ' ' in ch:
        return ch.split(' ')[0].strip()
    else:
        return ch.strip()

def create_word_test(input_file, output_file):
    # 读取Excel文件
    df = pd.read_excel(input_file)

    # 创建新的Excel工作簿
    wb = openpyxl.Workbook()
    ws = wb.active

    # 设置列宽
    ws.column_dimensions['A'].width = 70
    ws.column_dimensions['B'].width = 35
    ws.column_dimensions['C'].width = 35

    # 设置单元格样式
    border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    alignment_left = Alignment(horizontal='left', vertical='center', wrap_text=True)
    alignment_center = Alignment(horizontal='center', vertical='center', wrap_text=True)

    current_row = 1

    # 按每页words_per_page个单词进行处理
    for i in range(0, len(df), words_per_page):
        page_words = df.iloc[i:i+words_per_page]
        if len(page_words) < words_per_page:
            break  # 如果不够20个单词就跳过

        half = words_per_page // 2

        # 添加标题
        ws.cell(row=current_row, column=1, value="背诵")
        ws.cell(row=current_row, column=1).alignment = alignment_center
        ws.cell(row=current_row, column=1).border = border

        # 合并B和C列的单元格用于"自测"标题
        ws.merge_cells(start_row=current_row, start_column=2, end_row=current_row, end_column=3)
        ws.cell(row=current_row, column=2, value="自测")
        ws.cell(row=current_row, column=2).alignment = alignment_center
        ws.cell(row=current_row, column=2).border = border

        current_row += 1

        # 获取当前20个单词
        current_words = page_words.copy()

        # 第一部分：前10个单词
        # 第一列显示单词和中文
        for idx in range(half):
            row = current_words.iloc[idx]
            word_text = f"{idx+1}. {row['英文']} - {row['中文']}"
            ws.cell(row=current_row+idx, column=1, value=word_text).alignment = alignment_left

        # 随机打乱20个单词的顺序用于测试
        test_words = current_words.sample(frac=1).reset_index(drop=True)

        # 第二列和第三列显示打乱后的中文（用于默写英文）
        for idx in range(words_per_page):
            row = test_words.iloc[idx]
            if idx < half:
                cell_text = f"{idx+1}. {process_chinese(row['中文'])} _________"
                ws.cell(row=current_row+idx, column=2, value=cell_text).alignment = alignment_left

            else:
                cell_text = f"{idx+1}. {process_chinese(row['中文'])} _________"
                ws.cell(row=current_row+idx-half, column=3, value=cell_text).alignment = alignment_left

        # 添加空行
        current_row += half

        # 第二部分：后10个单词
        # 第一列显示单词和中文
        for idx in range(half):
            row = current_words.iloc[idx+half]
            word_text = f"{idx+1}. {row['英文']} - {row['中文']}"
            ws.cell(row=current_row+idx, column=1, value=word_text).alignment = alignment_left

        # 重新随机打乱20个单词的顺序
        test_words = current_words.sample(frac=1).reset_index(drop=True)

        # 第二列和第三列显示打乱后的英文（用于默写中文）
        for idx in range(words_per_page):
            row = test_words.iloc[idx]
            if idx < half:
                cell_text = f"{idx+1}. {row['英文']} _________"
                ws.cell(row=current_row+idx, column=2, value=cell_text).alignment = alignment_left
            else:
                cell_text = f"{idx+1}. {row['英文']} _________"
                ws.cell(row=current_row+idx-half, column=3, value=cell_text).alignment = alignment_left

        # 为所有单元格添加边框
        for row in range(current_row-half-1, current_row+half):
            for col in range(1, 4):
                cell = ws.cell(row=row, column=col)
                if not cell.border:  # 如果单元格还没有边框
                    cell.border = border

        # 移动到下一组的起始位置（不要空行）
        current_row += half
    set_row_height(ws)
    set_print_settings(ws)
    # 保存Excel文件
    wb.save(output_file)
# 使用示例
input_file = "高考英语译林词汇默写版.xlsx"  # 输入的Excel文件路径
output_file = "word_test_output.xlsx"  # 输出的Excel文件路径
create_word_test(input_file, output_file)