试卷指的是抽取的题目类似试卷，但是不是格式是试卷那种格式。
应对考试搞得一个，题库里面的题非常之多，每次都看完不太可能，就想着自动抽取汇总成题目文件。
1 ''' 2 抽取100道题目：单选30，多选10，填空10，判断改错10，名词解释20，问答20 3 ''' 4 5 import xlwings 6 import time 7 import random 8 9 def ret_timeStr(fileSsuffix = "xlsx"): 10 ''' 11 返回当前时间的字符串 12 ''' 13 currentTime = time.localtime() 14 return str(currentTime.tm_year) + \ 15 "年" + str(currentTime.tm_mon) + \ 16 "月" + str(currentTime.tm_mday) + \ 17 "日 " + str(currentTime.tm_hour) + \ 18 "时" + str(currentTime.tm_min) + \ 19 "分" + str(currentTime.tm_sec) + \ 20 "秒." + fileSsuffix 21 ''' 22 给定sheet，检查有多少行内容 23 ''' 24 def get_line_nums(sheet): 25 index=0 26 while True: 27 if sheet.range('A{}'.format(index+1)).value != None: 28 index=index+1 29 else: 30 break 31 return index 32 ''' 33 去除right_list、mistake_list中的重复值，然后再去除right_list中含有mistake_list的值;返回处理后的right_list； 34 ''' 35 def remove_num(right_list:list,mistake_list:list): 36 right_list = list(set(right_list)); 37 mistake_list = list(set(mistake_list)); 38 for i in mistake_list: 39 if(i in right_list): 40 right_list.remove(i); 41 return right_list; 42 ''' 43 抽取单选题目和写入对应的答案 44 ''' 45 def 单选(source_sheet,result_sheet,file): 46 result_sheet.name="单选" #重命名sheet 47 result_sheet.range('A1').value='题干'; 48 result_sheet.range('B1').value='A'; 49 result_sheet.range('C1').value='B'; 50 result_sheet.range('D1').value='C'; 51 result_sheet.range('E1').value='D'; 52 result_sheet.range('F1').value='我的答案' 53 result_sheet.range("A1").column_width = 65 #列宽65 54 result_sheet.range("B:E").column_width = 25 #列宽25 55 56 print("一、单选题",file=file) 57 #获取单选题目总数 58 all_line_nums = get_line_nums(source_sheet) 59 #用来排除存在于list_tiku中的题号的的题目。 60 跳过的题号_list=[10,85,191,79,178,137,242,228,69,152,106,9,14,255,133,96,150,139,117,185,241,184,165,24,12,118,20,189,197,78,3,83,42,140,105,7,31,43,27,97,233,206,116,219,129,190,210,60,2,212,172,5,59,142,235,35,88,193,229,98,181,200,220,52,199,167,103,89,67,134,64,262,211,65,17,237,147,109,245,87,84,71,180,254,183,92,100,213,209,107,57,136,16,250,11,230,121,192,23,146,39,49,76,62,246,119,110,163,204,123,4,72,231,236,6,66,102,153,161,196,253,239,48,207,40,113,252,55,205,120,177,234,170,128,208,166,111,13,46,33,29,186,187,1381,222,256,176,164,143,224,174,95,160,156,74,38,171,90,18,56,217,101,58,155,53,15,141,112,173,127,86,159,32,251,244,175,36,249,248,55,99,144,259,252,263,240,70,47,238,93,243,169,260,138,225,188,198,30,227,201,202,257,214,114,63,34,91,94,44,55,148,37,215,68,131,81,145,168,223,19,135,122,41,216,28,82,203,26,162,194,179,154,130,75,21,218,232,80,157,258,61,132,252,226,104,51,151,261,126,115,195,124,54,45,221,25,55,108,149,156,22,73,50,252,8,182,247,68,125,158,] 61 #做错的题目的题号 62 mistake_list=[] 63 跳过的题号_list = remove_num(跳过的题号_list,mistake_list); 64 list_tiku =[] 65 i = 2 66 #记录随机次数，防止无限循环 67 循环次数=0 68 while True: 69 if len(list_tiku) >= 50: 70 break 71 num = random.randint(2,all_line_nums) 72 循环次数=循环次数+1 73 if(循环次数>(all_line_nums*10)): 74 break; 75 if num in list_tiku: 76 continue 77 elif num in 跳过的题号_list: 78 continue 79 else: 80 list_tiku.append(num) 81 #连续写（赋值）一段单元格 82 result_sheet.range("A{}:E{}".format(i,i)).value = source_sheet.range('A{}:E{}'.format(num,num)).value 83 result_sheet.range("G{}".format(i)).value = source_sheet.range('F{}'.format(num)).value 84 result_sheet.range("h{}".format(i)).value = '=IF(F{}=G{},"正确","错误")'.format(i,i) 85 result_sheet.range("I{}".format(i)).value = num 86 #前面的序号是源题号，用于后续筛选已完全掌握的题目，不让其再出现在试卷中。 87 print("{}、{}".format(num,source_sheet.range('F{}'.format(num)).value),file=file) #将标准答案写入到文件中 88 i=i+1 89 result_sheet.range('A:E').api.WrapText = True #A列至E列自动换行 90 result_sheet.api.Columns("G:H").EntireColumn.Hidden = True #隐藏G-H列 91 92 93 ''' 94 抽取多选题目和写入对应的答案 95 ''' 96 def 多选(source_sheet,result_sheet,file): 97 result_sheet.name="多选" #重命名sheet 98 result_sheet.range('A1').value='题干'; 99 result_sheet.range('B1').value='A'; 100 result_sheet.range('C1').value='B'; 101 result_sheet.range('D1').value='C'; 102 result_sheet.range('E1').value='D'; 103 result_sheet.range('F1').value='E'; 104 result_sheet.range('G1').value='我的答案' 105 result_sheet.range("A1").column_width = 65 #列宽65 106 result_sheet.range("B:F").column_width = 25 #列宽25 107 108 109 print("二、多选题",file=file) 110 #获取多选题目总数 111 all_line_nums = get_line_nums(source_sheet) 112 #用来排除存在于list_tiku中的题号的的题目。 113 跳过的题号_list =[83,91,43,78,92,26,23,62,55,18,57,2,71,9,70,85,34,30,32,46,88,24,16,60,41,98,96,99,51,36,53,13,81,31,20,29,93,44,90,35,6,17,87,12,59,56,89,28,27,3,61,4,14,77,58,63,65,72,52,22,47,63,19,89,54,33,74,27,48,11,67,15,97,8,20,80,10,94,25,49,37,42,66,38,76,45,73,40,] 114 #做错的题目的题号 115 mistake_list=[84,21,7,86,50,79,39,95,64,69,68,82,5,75,22,20,86,5,75,50,89,82,39,63,45,74,27,42,52,33,80,67,49,38,] 116 跳过的题号_list = remove_num(跳过的题号_list,mistake_list); 117 list_tiku=[] 118 i = 2 119 #记录随机次数，防止无限循环 120 循环次数=0 121 while True: 122 if len(list_tiku) >= 50: 123 break 124 num = random.randint(2,all_line_nums) 125 循环次数=循环次数+1 126 if(循环次数>(all_line_nums*10)): 127 break; 128 if num in list_tiku: 129 continue 130 elif num in 跳过的题号_list: 131 continue 132 else: 133 list_tiku.append(num) 134 result_sheet.range("A{}:F{}".format(i,i)).value = source_sheet.range('A{}:F{}'.format(num,num)).value 135 136 result_sheet.range("H{}".format(i)).value = source_sheet.range('G{}'.format(num)).value 137 result_sheet.range("I{}".format(i)).value = '=IF(G{}=H{},"正确","错误")'.format(i,i) 138 result_sheet.range("J{}".format(i)).value = num 139 #前面的序号是源题号，用于后续筛选已完全掌握的题目，不让其再出现在试卷中。 140 print("{}、{}".format(num,source_sheet.range('G{}'.format(num)).value),file=file) #将标准答案写入到文件中 141 i=i+1 142 result_sheet.range('A:F').api.WrapText = True #A列至F列自动换行 143 result_sheet.api.Columns("H:I").EntireColumn.Hidden = True #隐藏D-F列 144 145 ''' 146 抽取填空题目和写入对应的答案 147 ''' 148 def 填空(source_sheet,result_sheet,file): 149 result_sheet.name="填空" #重命名sheet 150 result_sheet.range('A1').value='题干'; 151 result_sheet.range('B1').value='答案一'; 152 result_sheet.range('C1').value='答案二'; 153 result_sheet.range("A1").column_width = 65 #列宽65 154 155 print("三、填空题",file=file) 156 #获取单选题目总数 157 all_line_nums = get_line_nums(source_sheet) 158 #用来排除存在于list_tiku中的题号的的题目。 159 跳过的题号_list =[11,8,4,25,24,15,28,14,9,26,22,12,7,18,2,27,20,5,10,17,21,16,6,13,29,19,3,23] 160 #做错的题目的题号 161 mistake_list=[] 162 跳过的题号_list = remove_num(跳过的题号_list,mistake_list); 163 list_tiku=[] 164 i = 2 165 #记录随机次数，防止无限循环 166 循环次数=0 167 while True: 168 if len(list_tiku) >= 1000: 169 break 170 num = random.randint(2,all_line_nums) 171 循环次数=循环次数+1 172 if(循环次数>(all_line_nums*10)): 173 break; 174 if num in list_tiku: 175 continue 176 elif num in 跳过的题号_list: 177 continue 178 else: 179 list_tiku.append(num) 180 result_sheet.range("A{}".format(i)).value = source_sheet.range('A{}'.format(num)).value 181 result_sheet.range("D{}".format(i)).value = source_sheet.range('B{}'.format(num)).value 182 result_sheet.range("E{}".format(i)).value = source_sheet.range('C{}'.format(num)).value 183 result_sheet.range("F{}".format(i)).value = '=IF(B{}=D{},"正确","错误")'.format(i,i) 184 result_sheet.range("G{}".format(i)).value = '=IF(C{}=E{},"正确","错误")'.format(i,i) 185 result_sheet.range("H{}".format(i)).value = num 186 #前面的序号是源题号，用于后续筛选已完全掌握的题目，不让其再出现在试卷中。 187 print("{}、{}\t{}".format(num,source_sheet.range('B{}'.format(num)).value,source_sheet.range('C{}'.format(num)).value),file=file) #将标准答案写入到文件中 188 i=i+1 189 result_sheet.range('A:A').api.WrapText = True #A列自动换行 190 result_sheet.api.Columns("D:G").EntireColumn.Hidden = True #隐藏D-F列 191 192 ''' 193 抽取判断题目和写入对应的答案 194 ''' 195 def 判断(source_sheet,result_sheet,file): 196 result_sheet.name="判断" #重命名sheet 197 result_sheet.range('A1').value='题干'; 198 result_sheet.range('B1').value='答案'; 199 result_sheet.range('C1').value='改正'; 200 result_sheet.range("A1").column_width = 65 #列宽65 201 print("四、判断题",file=file) 202 #获取单选题目总数 203 all_line_nums = get_line_nums(source_sheet) 204 #用来排除存在于list_tiku中的题号的的题目。 205 跳过的题号_list =[65,64,44,24,11,32,13,8,39,9,21,20,50,59,49,7,60,25,46,36,52,4,38,63,48,45,5,51,29,58,66,31,55,17,43,61,56,15,12,14,3,47,40,27,2,35,33,23,53,30,34,26,19,62,22,41,42,37,18,57,16,6,54,10,41,28,] 206 #做错的题目的题号 207 mistake_list=[] 208 跳过的题号_list = remove_num(跳过的题号_list,mistake_list); 209 list_tiku=[] 210 i = 2 211 #记录随机次数，防止无限循环 212 循环次数=0 213 while True: 214 if len(list_tiku) >= 10: 215 break 216 num = random.randint(2,all_line_nums) 217 循环次数=循环次数+1 218 if(循环次数>(all_line_nums*10)): 219 break; 220 if num in list_tiku: 221 continue 222 elif num in 跳过的题号_list: 223 continue 224 else: 225 list_tiku.append(num) 226 result_sheet.range("A{}".format(i)).value = source_sheet.range('A{}'.format(num)).value 227 result_sheet.range("D{}".format(i)).value = source_sheet.range('C{}'.format(num)).value 228 result_sheet.range("E{}".format(i)).value = source_sheet.range('D{}'.format(num)).value 229 result_sheet.range("F{}".format(i)).value = '=IF(B{}=D{},"正确","错误")'.format(i,i) 230 result_sheet.range("G{}".format(i)).value = num 231 #这里可能有坑，none和空值的转换 232 #前面的序号是源题号，用于后续筛选已完全掌握的题目，不让其再出现在试卷中。 233 print("{}、{}\t{}".format(num,source_sheet.range('C{}'.format(num)).value,source_sheet.range('D{}'.format(num)).value),file=file) #将标准答案写入到文件中 234 i=i+1 235 result_sheet.range('A:A').api.WrapText = True #A列自动换行 236 result_sheet.api.Columns("D:F").EntireColumn.Hidden = True #隐藏D-F列 237 238 ''' 239 抽取名词解释题目和写入对应的答案 240 ''' 241 def 名词解释(source_sheet,result_sheet,file): 242 result_sheet.name="名词解释" #重命名sheet 243 result_sheet.range('A1').value='题干'; 244 result_sheet.range('B1').value='答案'; 245 result_sheet.range("A1").column_width = 25 #列宽25 246 result_sheet.range("B:C").column_width = 65 #列宽65 247 248 print("四、名词解释",file=file) 249 #获取单选题目总数 250 all_line_nums = get_line_nums(source_sheet) 251 #用来排除存在于list_tiku中的题号的的题目。 252 跳过的题号_list =[53,42,5,3,26,22,45,7] 253 #做错的题目的题号 254 mistake_list=[43,] 255 跳过的题号_list = remove_num(跳过的题号_list,mistake_list); 256 list_tiku=[] 257 i = 2 258 #记录随机次数，防止无限循环 259 循环次数=0 260 while True: 261 if len(list_tiku) >= 20: 262 break 263 num = random.randint(2,all_line_nums) 264 循环次数=循环次数+1 265 if(循环次数>(all_line_nums*10)): 266 break; 267 if num in list_tiku: 268 continue 269 elif num in 跳过的题号_list: 270 continue 271 else: 272 list_tiku.append(num) 273 result_sheet.range("A{}".format(i)).value = source_sheet.range('A{}'.format(num)).value 274 result_sheet.range("C{}".format(i)).value = source_sheet.range('B{}'.format(num)).value 275 result_sheet.range("D{}".format(i)).value = num 276 print("{}、{}".format(num,source_sheet.range('B{}'.format(num)).value),file=file) #将标准答案写入到文件中 277 i=i+1 278 result_sheet.range('A:A').api.WrapText = True #A列自动换行 279 result_sheet.range("C:C").api.WrapText = True #C列自动换行 280 result_sheet.api.Columns("C:C").EntireColumn.Hidden = True #隐藏C列 281 ''' 282 抽取问答题目和写入对应的答案 283 ''' 284 def 问答(source_sheet,result_sheet,file): 285 result_sheet.name="问答" #重命名sheet 286 result_sheet.range('A1').value='题干'; 287 result_sheet.range('B1').value='答案'; 288 result_sheet.range("A:C").column_width = 65 289 290 print("四、问答",file=file) 291 #获取单选题目总数 292 all_line_nums = get_line_nums(source_sheet) 293 #用来排除存在于list_tiku中的题号的的题目。 294 跳过的题号_list =[33,16,] 295 #做错的题目的题号 296 mistake_list=[] 297 跳过的题号_list = remove_num(跳过的题号_list,mistake_list); 298 list_tiku=[] 299 i = 2 300 #记录随机次数，防止无限循环 301 循环次数=0 302 while True: 303 if len(list_tiku) >= 20: 304 break 305 num = random.randint(2,all_line_nums) 306 循环次数=循环次数+1 307 if(循环次数>(all_line_nums*10)): 308 break; 309 if num in list_tiku: 310 continue 311 elif num in 跳过的题号_list: 312 continue 313 else: 314 list_tiku.append(num) 315 result_sheet.range("A{}".format(i)).value = source_sheet.range('A{}'.format(num)).value 316 result_sheet.range("C{}".format(i)).value = source_sheet.range('B{}'.format(num)).value 317 result_sheet.range("D{}".format(i)).value = num 318 print('"{}"'.format(source_sheet.range('B{}'.format(num)).value),file=file) #将标准答案写入到文件中 319 i=i+1 320 result_sheet.range('A:A').api.WrapText = True #A列自动换行 321 result_sheet.range("C:C").api.WrapText = True #C列自动换行 322 result_sheet.api.Columns("C:C").EntireColumn.Hidden = True #隐藏C列 323 324 325 def main(name=""): 326 #标准答案 327 standard_answer_file = open(r"D:\工作\temp\暂存\信息政策与法规\试卷\试卷答案-{}".format(ret_timeStr("txt")),mode='w',encoding='utf-8') 328 #试卷 329 app_shijuan = xlwings.App(visible=False,add_book=False) 330 workbook_shijuan = app_shijuan.books.add() 331 #题库 332 app_tiku = xlwings.App(visible=False,add_book=False) 333 workbook_tiku = app_tiku.books.open(r"D:\工作\temp\暂存\信息政策与法规\信息政策与法规模拟试题集.xlsm") 334 #单选选题 335 #workSheet_shijuan = workbook_shijuan.sheets.add() 336 workSheet_shijuan = workbook_shijuan.sheets['sheet1'] #新建的时候默认就会有一个sheet页 337 workbook_tiku_sheet = workbook_tiku.sheets['单选'] 338 单选(workbook_tiku_sheet,workSheet_shijuan,standard_answer_file) 339 print("已完成单选题目抽取...") 340 #多选 341 workSheet_shijuan = workbook_shijuan.sheets.add() 342 workbook_tiku_sheet = workbook_tiku.sheets['多选'] 343 多选(workbook_tiku_sheet,workSheet_shijuan,standard_answer_file) 344 print("已完成多选题目抽取...") 345 #填空 346 workSheet_shijuan = workbook_shijuan.sheets.add() 347 workbook_tiku_sheet = workbook_tiku.sheets['填空'] 348 填空(workbook_tiku_sheet,workSheet_shijuan,standard_answer_file) 349 print("已完成填空题目抽取...") 350 #判断改错 351 workSheet_shijuan = workbook_shijuan.sheets.add() 352 workbook_tiku_sheet = workbook_tiku.sheets['判断改错'] 353 判断(workbook_tiku_sheet,workSheet_shijuan,standard_answer_file) 354 print("已完成判断题目抽取...") 355 #名词解释 356 workSheet_shijuan = workbook_shijuan.sheets.add() 357 workbook_tiku_sheet = workbook_tiku.sheets['名词解释'] 358 名词解释(workbook_tiku_sheet,workSheet_shijuan,standard_answer_file) 359 print("已完成名词解释题目抽取...") 360 #问答 361 workSheet_shijuan = workbook_shijuan.sheets.add() 362 workbook_tiku_sheet = workbook_tiku.sheets['问答'] 363 问答(workbook_tiku_sheet,workSheet_shijuan,standard_answer_file) 364 print("已完成问答题目抽取...") 365 # #还应该设置单元格宽和高 366 # workSheet_shijuan.range('A:A').api.WrapText = True #A列自动换行 367 # workSheet_shijuan.range('B:B').api.WrapText = True #B列自动换行 368 # workSheet_shijuan.range("B:B").autofit() #自动调整B列的高，但是之前不能设置B列的高为固定数值 369 # workSheet_shijuan.api.Columns("C:C").EntireColumn.Hidden = True #隐藏C列 370 # workSheet_shijuan.api.Columns("E:E").EntireColumn.Hidden = True #隐藏E列 371 372 373 workbook_shijuan.save(r"D:\工作\temp\暂存\信息政策与法规\试卷\试卷-{}".format(ret_timeStr())) 374 workbook_shijuan.close() 375 app_shijuan.quit() 376 workbook_tiku.close() 377 app_tiku.quit() 378 standard_answer_file.close(); 379 380 if __name__ == "__main__": 381 main()
原题库样式：
生成的试卷样式：
