python提取excel为json题库

#提取xlsx中的题目以json格式组织 生成json配置文件
import os
import json
import openpyxl
import uuid
#读取判断题
def readJsutify(path,sheetname):
    workbook=openpyxl.load_workbook(path)
    sheet=workbook.get_sheet_by_name(sheetname)
    rownum=sheet.max_row
    timus=[]
    for iterr in range(1,rownum):
        #print(iterr+1) 注意是从第二行开始的
        id=str(uuid.uuid4())
        title=sheet.cell(row=iterr+1,column=2).value
        option=["正确","错误"]
        answer=sheet.cell(row=iterr+1,column=4).value
        if answer=="√":
            answer="A"
        elif answer=="×":
            answer="B"
        else:
            pass
        analysis=""

        dictiter={}
        dictiter["id"]=id
        dictiter["title"]=title
        dictiter["option"]=option
        dictiter["answer"]=answer
        dictiter["analysis"]=analysis
        timus.append(dictiter)
    return json.dumps(timus)


#读取单选题
def readSingleSel(path,sheetname):
    workbook=openpyxl.load_workbook(path)
    sheet=workbook.get_sheet_by_name(sheetname)
    #读取单选题
    rownum=sheet.max_row
    timus=[]
    for iterr in range(1,rownum):
        #print(iterr+1) 注意是从第二行开始的
        id=str(uuid.uuid4())
        title=sheet.cell(row=iterr+1,column=2).value
        ans1=sheet.cell(row=iterr+1,column=3).value
        ans2=sheet.cell(row=iterr+1,column=4).value
        ans3=sheet.cell(row=iterr+1,column=5).value
        ans4=sheet.cell(row=iterr+1,column=6).value
        option=[ans1,ans2,ans3,ans4]
        answer=sheet.cell(row=iterr+1,column=8).value
        analysis=""

        dictiter={}
        dictiter["id"]=id
        dictiter["title"]=title
        dictiter["option"]=option
        dictiter["answer"]=answer
        dictiter["analysis"]=analysis
        timus.append(dictiter)
    return json.dumps(timus)


#读取多选题
def readMultiSel(path,sheetname):
    workbook=openpyxl.load_workbook(path)
    sheet=workbook.get_sheet_by_name(sheetname)
    #读取单选题
    rownum=sheet.max_row
    colnum=sheet.max_column
    print("row:{row} column:{column}".format(row=rownum,column=colnum))
    timus=[]
    for iterr in range(1,rownum):
        #print(iterr+1) 注意是从第二行开始的
        id=str(uuid.uuid4())
        #2 是题目 colnum是答案  3~column-1 都是选项
        title=sheet.cell(row=iterr+1,column=2).value
        option=[]
        for iop in range(4,colnum):
            ans1=sheet.cell(row=iterr+1,column=iop).value
            if ans1==None:
                continue
            #if(len(str(ans1)))>0:
            option.append(ans1)
        answer=sheet.cell(row=iterr+1,column=colnum).value
        analysis=""

        dictiter={}
        dictiter["id"]=id
        dictiter["title"]=title
        dictiter["option"]=option
        dictiter["answer"]=answer
        dictiter["analysis"]=analysis
        timus.append(dictiter)
    return json.dumps(timus)

#主应用
if __name__ =="__main__":
    xlsxpath="E:/PyTorchLearn/day01/副本考核0910.xlsx"
    justSheetName="判断题"
    singleSheetName="单选题"
    multiSheetName="多选题"

    #判断题
    dict1=readJsutify(xlsxpath,justSheetName)
    with open("E:/PyTorchLearn/day01/202300.json","w+") as f:
        f.write(dict1)
    #单选题
    dict2=readSingleSel(xlsxpath,singleSheetName)
    with open("E:/PyTorchLearn/day01/202301.json","w+") as f:
        f.write(dict2)
    #多选题
    dict3=readMultiSel(xlsxpath,multiSheetName)
    with open("E:/PyTorchLearn/day01/202302.json","w+") as f:
        f.write(dict3)

    #pass