用Python读取Excel题库随机组成Word版本试卷

老三的休闲书屋 2024-03-28 发布于湖北

展开全文

Excel题目类型有单选、多选、判断，其中多选题最多包含6个选项，判断题只有两个选项（A：正确，B：错误）。
此程序将随机抽取单选题20题，多选题10题，判断题20题，组成一张试卷。本Excel题库是按照单选、多选、判断的顺序排列的。本Python程序不适合题目类型乱序排列题库。
运行程序后，将得到1份试题和1份答案，均为Word版本。
Excel题库如图所示：

题库内容.PNG

Python代码如下

import xlrd
from docx import Document
from docx.shared import Pt
from docx.oxml.ns import qn
import random
import time

def joinlist(item):
    item[0] = str(item[0]).replace('\n','') + '\n'
    item[1] = 'A.' + str(item[1]).replace('\n','')
    item[2] = ' B.' + str(item[2]).replace('\n','')
    item[3] = (' C.' + str(item[3]).replace('\n','') if item[3] else '')
    item[4] = (' D.' + str(item[4]).replace('\n','') if item[4] else '')
    item[5] = (' E.' + str(item[5]).replace('\n','') if item[5] else '')
    item[6] = (' F.' + str(item[6]).replace('\n','') if item[6] else '')
    if len(item) == 8:
        item[7] = ' 答案：' + str(item[7]).replace('\n','') + '\n'
        return ''.join(item)
    else:
        return ''.join(item)+ '\n'

def generate(total,single,multi,a=9):
    sum = ['安规周周考\n一、单选题\n']
    for i in range(len(total)):
        sum.append('(' + str(i+1) + ')' + joinlist(table.row_values(total[i], start_colx=1, end_colx=a)))
        if i == (single - 1):
            sum.append('二、多选题\n')
            next
        elif i == (single+multi-1):
            sum.append('三、判断题\n')
            next
    return sum

def word(item,timenow,kaoshi):
    doc1 = Document()
    doc1.styles['Normal'].font.name = u'宋体'
    doc1.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    doc1.styles['Normal'].font.size = Pt(9)
    doc1.add_paragraph(''.join(item))
    doc1.save(kaoshi + timenow + '.docx')

def randnumber(single,multi,judge,item):
    total = [item.count('单选'),item.count('多选'),item.count('判断')]
    if single > total[0] or multi > total[1] or judge > total[2]:
        print('too many exam question!')
        return False
    else:
        s = random.sample(range(1,total[0]+1),single)
        m = random.sample(range(total[0]+1,total[0]+total[1]+1),multi)
        j = random.sample(range(total[0]+total[1]+1,total[0]+total[1]+total[2]+1),judge)
        s.extend(m)
        s.extend(j)
        return generate(s,single,multi),generate(s,single,multi,8)

try:
    data = xlrd.open_workbook('question.xlsx')
    table = data.sheets()[0]
    timenow = time.strftime('%Y%m%d%H%M%S', time.localtime())
    temp = randnumber(20,10,20,table.col_values(0, start_rowx=0, end_rowx=None))
    if temp:
        word(temp[0],timenow,'kaoshidaan')
        word(temp[1],timenow,'kaoshi')
    else:
        print('error!')
except:
    print('Exception occured!')