试卷指的是抽取的题目类似试卷，但是不是格式是试卷那种格式。
应对考试搞得一个，题库里面的题非常之多，每次都看完不太可能，就想着自动抽取汇总成题目文件。
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()
View Code
原题库样式：
生成的试卷样式：
标签：sheet,表格,试卷,list,value,range,num,result,题库 From： https://www.cnblogs.com/love-DanDan/p/17336631.html