python中使用xlrd、xlwt操作excel

钩子：

python中使用xlrd、xlwt操作excel表格详解

# -*- coding: utf-8 -*-
import xlrd
import xlwt
import random
"""
需求：
    由于平台测试，需要生成测试报告，故需要学生对试卷的随机答题情况，
    故出此脚本
"""

def do(name):
    # 打开文件，return xlrd.book.Book
    data = xlrd.open_workbook(u'{}.xls'.format(name))
    # # @return A list of all sheets in the book.
    table = data.sheets()[0]
    # 取sheet1的行数
    rows = table.nrows

    full_data = {}
    for i in range(rows):
        # 循环遍历整行的值
        row_val = table.row_values(i)
        if row_val[0] == 'line':
            full_data[row_val[0]] = int(row_val[1])
        else:
            full_data[row_val[0]] = row_val[1:]

    print 'row_val', full_data
    print full_data['answer']
    print full_data['score']
    if len(full_data['answer']) != len(full_data['score']):
        raise ValueError(u'答案和分数数量不匹配')
    score_lists = []
    answer_lists = []
    for loop_number in range(int(full_data['line'])):
        score_line_lists = []
        answer_line_lists = []
        for k, v in enumerate(full_data['answer']):
            max_score = int(full_data['score'][k])
            if v == '':
                # 主观题答案根据random随机一个数值
                score = random.randint(0, max_score)
                answer_line_lists.append('')
            else:
                score = random.choice([0, max_score, max_score, max_score])
                if score == max_score:
                    answer_line_lists.append(v)
                else:
                    answer_choice = ['A', 'B', 'C', 'D']
                    answer_choice.remove(v)
                    answer_line_lists.append(random.choice(answer_choice))

            score_line_lists.append(score)
        score_lists.append(score_line_lists)
        answer_lists.append(answer_line_lists)

    print(score_lists)
    print(answer_lists)

    # 创建工作薄
    wb = xlwt.Workbook()
    # 创建第一个sheet
    ws = wb.add_sheet('Score')
    for k, v in enumerate(score_lists):
        for q, n in enumerate(v):
            # 一行行生成数据
            ws.write(k, q, n)

    # 创建第二个sheet
    asw = wb.add_sheet('Answer')
    for k, v in enumerate(answer_lists):
        for q, n in enumerate(v):
            asw.write(k, q, n)

    wb.save(u'{0}-result.xls'.format(name))


# def main():
#     for name in [u'化学', u'数学', u'物理', u'英语', u'语文']:
#         do(name)
#
# main()
do('tpl')

要求：

结果：