1 Public Sub Check_Click()
2 Sheets(readmeSheetName).Visible = False
3 '首先输入学校验证码，如果不输入直接退出
4 xxbsm = InputBox("请输入学校标识码")
5 If StrPtr(xxbsm) Then
6 If IsNumeric(xxbssm) Then
7 If Len(xxbsm) <> 10 Then
8 xxbsm = InputBox("学校标识码必须为10位数字，请重新输入!")
9 End If
10 Else
11 xxbsm = InputBox("学校标识码必须为10位数字，请重新输入!")
12 End If
13 Else
14 Exit Sub
15 End If
16
17 totalCount = 0
18
19 checkJxbs = Sheets(valueSheetName).Cells(2, 1)
20 If checkJxbs = "" Then
21 MsgBox ("没有数据，无需校验！")
22 Exit Sub
23 End If
24
25 checkName = Sheets(valueSheetName).Cells(2, 3)
26 If checkName = "" Then
27 MsgBox ("没有数据，无需校验！")
28 Exit Sub
29 End If
30 '初始化列
31 cc = getValueColumCount(valueSheetName)
32
33 Dim columColection() As TypeValueColum
34 ReDim columColection(1 To cc)
35 Dim cindex As Integer
36 cindex = 1
37 Title = Sheets(valueSheetName).Cells(1, cindex)
38 While Title <> ""
39 Dim vlaueColum As TypeValueColum
40
41 vlaueColum.columnIndex = cindex
42 vlaueColum.columnName = Sheets(valueSheetName).Cells(1, cindex)
43 vlaueColum.dicColumnIndex = 0
44 vlaueColum.dicColumnName = ""
45
46 '去字典里找是否有同名列有则设置为字典
47 dicColumnIndex = findDic(vlaueColum.columnName)
48 If dicColumnIndex <> 0 Then
49 vlaueColum.dicColumnIndex = dicColumnIndex
50 vlaueColum.dicColumnName = Sheets(dicSheetName).Cells(1, dicColumnIndex)
51 End If
52
53 If vlaueColum.dicColumnIndex = 0 Then
54
55 End If
56
57 '手动对应字典
58 Select Case Title
59 Case "是否进城务工人员随迁子女"
60 vlaueColum.dicColumnName = "是否"
61 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
62 Case "是否农村留守儿童"
63 vlaueColum.dicColumnName = "是否"
64 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
65 Case "是否留守儿童"
66 vlaueColum.dicColumnName = "是否"
67 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
68 Case "是否随迁子女"
69 vlaueColum.dicColumnName = "是否"
70 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
71 Case "是否残疾人"
72 vlaueColum.dicColumnName = "是否"
73 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
74 Case "成员2民族"
75 vlaueColum.dicColumnName = "民族"
76 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
77 Case "成员2身份证件类型"
78 vlaueColum.dicColumnName = "家长证件类型"
79 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
80 Case "成员2是否监护人"
81 vlaueColum.dicColumnName = "是否"
82 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
83 Case "成员2关系"
84 vlaueColum.dicColumnName = "关系"
85 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
86 Case "成员1民族"
87 vlaueColum.dicColumnName = "民族"
88 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
89 Case "成员1身份证件类型"
90 vlaueColum.dicColumnName = "家长证件类型"
91 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
92 Case "成员1是否监护人"
93 vlaueColum.dicColumnName = "是否"
94 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
95 Case "成员1关系"
96 vlaueColum.dicColumnName = "关系"
97 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
98 Case "是否由政府购买学位"
99 vlaueColum.dicColumnName = "是否"
100 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
101 Case "是否需要乘坐校车"
102 vlaueColum.dicColumnName = "是否"
103 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
104 Case "是否烈士或优抚子女"
105 vlaueColum.dicColumnName = "是否"
106 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
107 Case "是否孤儿"
108 vlaueColum.dicColumnName = "是否"
109 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
110 Case "是否享受一补"
111 vlaueColum.dicColumnName = "是否"
112 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
113 Case "是否需要申请资助"
114 vlaueColum.dicColumnName = "是否"
115 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
116 Case "是否受过学前教育"
117 vlaueColum.dicColumnName = "是否"
118 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
119 Case "是否独生子女"
120 vlaueColum.dicColumnName = "是否"
121 vlaueColum.dicColumnIndex = findDic(vlaueColum.dicColumnName)
122 End Select
123 '将列放入集合
124 columColection(cindex) = vlaueColum
125 cindex = cindex + 1
126
127 Title = Sheets(valueSheetName).Cells(1, cindex)
128 Wend
129
130 curRowIndex = dataRowStart
131 stuName = Sheets(valueSheetName).Cells(curRowIndex, 2)
132 '初始化日志
133 curMsgRow = 1
134 A = clearLog()
135
136 valueColumCoount = cc
137 While stuName <> ""
138 totalCount = totalCount + 1
139 For col = 1 To valueColumCoount
140 Dim valueCol As TypeValueColum
141 valueCol = columColection(col)
142 valueColName = valueCol.columnName
143 '此次加校验
144 Select Case valueColName
145 'Case "学籍接续标识"
146 'checkXjjxbs valueCol, curRowIndex
147
148 Case "学校标识码"
149 checkXXsbh valueCol, curRowIndex
150
151 Case "姓名"
152 checkXm valueCol, curRowIndex
153
154 Case "性别"
155 checkXb valueCol, curRowIndex
156
157 Case "出生日期"
158 checkCsrq valueCol, curRowIndex
159
160 Case "出生地行政区划代码"
161 checkCsrXzqh valueCol, curRowIndex
162 Case "籍贯"
163 checkJg valueCol, curRowIndex
164 Case "民族"
165 checkMz valueCol, curRowIndex
166
167 Case "国籍/地区"
168 checkGjdq valueCol, curRowIndex
169
170 Case "身份证件类型"
171 checkSfzjlx valueCol, curRowIndex
172
173 Case "港澳台侨外"
174 checkGat valueCol, curRowIndex
175
176 Case "健康状况"
177 checkJkzk valueCol, curRowIndex
178
179 Case "政治面貌"
180 checkZzmm valueCol, curRowIndex
181
182 Case "身份证件号"
183 checkSfzjhm valueCol, curRowIndex
184
185 Case "户口性质"
186 checkHkxz valueCol, curRowIndex
187
188 Case "户口所在地行政区划"
189 checkHkszdXzqh valueCol, curRowIndex
190
191 Case "班号"
192 checkBh valueCol, curRowIndex
193
194 Case "入学年月"
195 checkRxny valueCol, curRowIndex
196
197 Case "入学方式"
198 checkRxfs valueCol, curRowIndex
199
200 Case "就读方式"
201 checkJdfs valueCol, curRowIndex
202
203 Case "现住址"
204 checkXzz valueCol, curRowIndex
205
206 Case "通信地址"
207 checkTxfs valueCol, curRowIndex
208
209 Case "家庭地址"
210 checkJtzz valueCol, curRowIndex
211
212 Case "联系电话"
213 checkLxdh valueCol, curRowIndex
214
215 Case "邮政编码"
216 checkYzbm valueCol, curRowIndex
217
218 Case "是否独生子女"
219 checkSfdszn valueCol, curRowIndex
220
221 Case "是否受过学前教育"
222 checkSfsgxqjy valueCol, curRowIndex
223
224 Case "是否留守儿童"
225 checkSflset valueCol, curRowIndex
226
227 Case "是否需要申请资助"
228 checkSfsqzz valueCol, curRowIndex
229
230 Case "是否享受一补"
231 checkSfxsyb valueCol, curRowIndex
232
233 Case "是否孤儿"
234 checkSfge valueCol, curRowIndex
235
236 Case "是否烈士或优抚子女"
237 checkSflshyfzn valueCol, curRowIndex
238
239 Case "上下学距离"
240 checkSxjl valueCol, curRowIndex
241
242 Case "上下学方式"
243 checkSxxfs valueCol, curRowIndex
244
245 Case "是否需要乘坐校车"
246 checkSfxyczxc valueCol, curRowIndex
247
248 Case "曾用名"
249 checkCym valueCol, curRowIndex
250
251 Case "身份证件有效期"
252 checkSfzyxq valueCol, curRowIndex
253
254 Case "血型"
255 checkXx valueCol, curRowIndex
256
257 Case "特长"
258 checkTc valueCol, curRowIndex
259
260 Case "学籍辅号"
261 checkXjfh valueCol, curRowIndex
262
263 Case "班内学号"
264 checkBnxh valueCol, curRowIndex
265
266 Case "学生来源"
267 checkXsly valueCol, curRowIndex
268
269 Case "电子信箱"
270 checkDzxx valueCol, curRowIndex
271
272 Case "主页地址"
273 checkZydz valueCol, curRowIndex
274
275 Case "残疾类型"
276 checkCjlx valueCol, curRowIndex
277
278 Case "是否由政府购买学位"
279 checkSfyzfgmxw valueCol, curRowIndex
280
281 Case "是否随迁子女"
282 checkSfsqzn valueCol, curRowIndex
283
284 Case "隐藏"
285
286 Case "成员1姓名"
287 checkCyyxm valueCol, curRowIndex
288
289 Case "成员1关系"
290 checkCyygx valueCol, curRowIndex
291
292 Case "成员1关系说明"
293 checkCyygxsm valueCol, curRowIndex
294
295 Case "成员1现住址"
296 checkCyyxzz valueCol, curRowIndex
297
298 Case "成员1户口所在地行政区划"
299 checkCyyhkszdxzqh valueCol, curRowIndex
300
301 Case "成员1联系电话"
302 checkCyylxdh valueCol, curRowIndex
303
304 Case "成员1是否监护人"
305 checkCyysfjhr valueCol, curRowIndex
306
307 Case "成员1身份证件类型"
308 checkCyysfzjlx valueCol, curRowIndex
309
310 Case "成员1身份证件号"
311 checkCyysfzjh valueCol, curRowIndex
312
313 Case "成员1民族"
314 checkCyymz valueCol, curRowIndex
315
316 Case "成员1工作单位"
317 checkCyygzdw valueCol, curRowIndex
318
319 Case "成员1职务"
320 checkCyyzw valueCol, curRowIndex
321
322 Case "成员2姓名"
323 checkCyexm valueCol, curRowIndex
324
325 Case "成员2关系"
326 checkCyegx valueCol, curRowIndex
327
328 Case "成员2关系说明"
329 checkCyegxsm valueCol, curRowIndex
330
331 Case "成员2现住址"
332 checkCyexzz valueCol, curRowIndex
333
334 Case "成员2户口所在地行政区划"
335 checkCyehkszdxzqh valueCol, curRowIndex
336
337 Case "成员2联系电话"
338 checkCyelxdh valueCol, curRowIndex
339
340 Case "成员2是否监护人"
341 checkCyesfjhr valueCol, curRowIndex
342
343 Case "成员2身份证件类型"
344 checkCyesfzjlx valueCol, curRowIndex
345
346 Case "成员2身份证件号"
347 checkCyesfzjh valueCol, curRowIndex
348
349 Case "成员2民族"
350 checkCyemz valueCol, curRowIndex
351
352 Case "成员2工作单位"
353 checkCyegzdw valueCol, curRowIndex
354
355 Case "成员2职务"
356 checkCyezw valueCol, curRowIndex
357
358 Case "是否进城务工人员随迁子女"
359 checkSfjcwgrysqzn valueCol, curRowIndex
360
361 Case "是否农村留守儿童"
362 checkSfnclset valueCol, curRowIndex
363
364 Case "是否残疾人"
365 checkSfcjr valueCol, curRowIndex
366
367 Case "校区号"
368 checkXqh valueCol, curRowIndex
369
370
371 End Select
372 Next col
373 curRowIndex = curRowIndex + 1
374 stuName = Sheets(valueSheetName).Cells(curRowIndex, 2)
375 Wend
376
377 '保存excel
378 ActiveWorkbook.Save
379 If curMsgRow > 1 Then
380 MsgBox ("部分数据不符合要求，单击确定查看")
381
382 '激活日志
383 Sheets(msgSheetName).Visible = True
384 Sheets(msgSheetName).Select
385 Else
386 MsgBox ("数据校验通过，可以上传")
387 errorMsg = "数据校验成功,校验记录数为" & totalCount & "条！"
388 writeLog (errorMsg)
389 Sheets(msgSheetName).Visible = False
390 End If
391 End Sub
知识兔