php考试座位编排系统,考试座位编排vba

程序开发的准备

在开发小程序前&＃xff0c;首先要了解“缅茄杯”对座位编排的具体要求。“缅茄杯”的考生来自高州市61所初级中学(每所中学的考生人数由3到400不等&＃xff0c;总人数1848)。每个试室人数限额是30人&＃xff0c;按5组6行编排。考生的座位是随机的并且来自同一个学校的考生的座位前后左右都不能相邻。其次选用一个好用的开发软件&＃xff0c;本人就用自己比较常用的“VB”。

程序的开发

程序的开发&＃xff0c;是最重要的&＃xff0c;而且又是最艰难的一环工作&＃xff0c;主要工作是代码的编写&＃xff0c;它要求“算法”精简优化&＃xff0c;操作简单&＃xff0c;通用好用&＃xff0c;这里我按以下几部分进行设计。

一、数据的导入

“缅茄杯”的考生在报名时&＃xff0c;是由学校统一上送的。上送的名单是用“EXCELL”格式的文件上送的&＃xff0c;考生的详细信息如下图所示&＃xff1a;

为此程序的第一部工作是完成考生基本数据的导入。其代码编写为&＃xff1a;

Dim ap As Excel.Application

Dim bk As Excel.Workbook

Dim st1 As Excel.Worksheet

Set ap &＃61; CreateObject("Excel.Application")

cd.DialogTitle &＃61; "打开EXCEL文件"

cd.Filter &＃61; "*.xls|*.xls"

cd.ShowOpen

If cd.FileName &＃61; "" Then MsgBox ("文件不能为空"): Exit Sub

Set bk &＃61; ap.Workbooks.Open(cd.FileName)

Set st1 &＃61; bk.Worksheets(1)

cols &＃61; 1

rows &＃61; 1

&＃39;cells(行&＃xff0c;列)

With st1

Do Until .Cells(rows, 1).Value &＃61; ""

&＃39;表格行数

rows &＃61; rows &＃43; 1

Loop

End With

renshu &＃61; rows - 2

For i &＃61; 1 To renshu

b(i) &＃61; Trim(st1.Cells((i &＃43; 1), 1).Value)

c(i) &＃61; Trim(st1.Cells((i &＃43; 1), 3).Value)

xb(i) &＃61; Trim(st1.Cells((i &＃43; 1), 4).Value)

Next i

bk.Close

ap.Quit

Set ap &＃61; Nothing

二、座位的编排

这是程序开发的中心环节&＃xff0c;我设计的思路简单描述是这样的&＃xff1a;先为每个学生添加一个各不相同的随机数字(一至考生总人数&＃xff0c;这里就是1&＃xff5e;1848)&＃xff0c;再把考生按这些随机数按升序的顺序排列&＃xff0c;依次编排到第一个试室至最后一个试室的1&＃xff5e;30的座位中&＃xff0c;然后按试室和座位的顺序对每一个考生进行检查&＃xff0c;检查他的前后左右是否有同学校的考生&＃xff0c;如果有则对他进行调整。其代码编写如下&＃xff1a;

&＃39;随机号生成

i &＃61; 1

Randomize Time

Do While i

flag2 &＃61; 0

n &＃61; (Rnd * (renshu - 1)) \ 1 &＃43; 1

For j &＃61; 1 To i

If a(j) &＃61; n Then

flag2 &＃61; 1

Exit For

End If

Next j

If flag2 &＃61; 0 Then

a(i) &＃61; n

i &＃61; i &＃43; 1

End If

Loop

&＃39;试室,座号生成

For i &＃61; 1 To renshu \ 30 &＃43; 1

For j &＃61; 1 To 30

z &＃61; (i - 1) * 30 &＃43; j

If z > renshu Then

Exit For

Exit For

End If

d(z) &＃61; i

e(z) &＃61; j

Next j

Next i

For i &＃61; 1 To renshu

For j &＃61; 1 To renshu

If a(j) &＃61; i Then

f(i) &＃61; a(j)

g(i) &＃61; b(j)

h(i) &＃61; c(j)

xbxb(i) &＃61; xb(j)

id2(i) &＃61; id1(j)

Exit For

End If

Next j

Next i

&＃39;判断1

flag &＃61; 1

Do While flag &＃61; 1

flag &＃61; 0

For i &＃61; 1 To (renshu \ 30 &＃43; 1)

For j &＃61; 1 To 29

x &＃61; 30 * (i - 1) &＃43; j

If x <(renshu - 1) Then

If g(x) &＃61; g(x &＃43; 1) Then

Randomize

y &＃61; (Rnd * (renshu - 1)) \ 1 &＃43; 1

temp1 &＃61; g(y)

g(y) &＃61; g(x)

g(x) &＃61; temp1

temp2 &＃61; h(y)

h(y) &＃61; h(x)

h(x) &＃61; temp2

temp3 &＃61; xbxb(y)

xbxb(y) &＃61; xbxb(x)

xbxb(x) &＃61; temp3

temp4 &＃61; id2(y)

id2(y) &＃61; id2(x)

id2(x) &＃61; temp4

End If

End If

Next j

Next i

&＃39;判断2

For i &＃61; 1 To (renshu \ 30 &＃43; 1)

For j &＃61; 1 To 24

x &＃61; 30 * (i - 1) &＃43; j

If x <(renshu - 5) Then

If g(x) &＃61; g(x &＃43; 6) Then

Randomize

y &＃61; (Rnd * (renshu - 1)) \ 1 &＃43; 1

temp1 &＃61; g(y)

g(y) &＃61; g(x)

g(x) &＃61; temp1

temp2 &＃61; h(y)

h(y) &＃61; h(x)

h(x) &＃61; temp2

temp3 &＃61; xbxb(y)

xbxb(y) &＃61; xbxb(x)

xbxb(x) &＃61; temp3

temp4 &＃61; id2(y)

id2(y) &＃61; id2(x)

id2(x) &＃61; temp4

End If

End If

Next j

Next i

&＃39;检验1

For i &＃61; 1 To (renshu \ 30 &＃43; 1)

For j &＃61; 1 To 29

x &＃61; 30 * (i - 1) &＃43; j

If x <(renshu - 1) Then

If g(x) &＃61; g(x &＃43; 1) Then

flag &＃61; 1

Exit For

End If

End If

Next j

Next i

&＃39;检验2

For i &＃61; 1 To (renshu \ 30 &＃43; 1)

For j &＃61; 1 To 24

x &＃61; 30 * (i - 1) &＃43; j

If x <(renshu - 5) Then

If g(x) &＃61; g(x &＃43; 6) Then

flag &＃61; 1

Exit For

End If

End If

Next j

Next i

Loop

三、座位表的输出

完成座位编排后&＃xff0c;还要把结果输出。按实际需要首先要输出座位表(输出格式为“EXCELL”格式文件)。座位表内容如下图所示&＃xff1a;

代码编写如下&＃xff1a;

Dim zsbexcel As Excel.Application

Dim zsbworkbook As Excel.Workbook

Set zsbexcel &＃61; New Excel.Application

zsbexcel.SheetsInNewWorkbook &＃61; 1

Set zsbworkbook &＃61; zsbexcel.Workbooks.Add

With zsbexcel.ActiveSheet

For i &＃61; 1 To (renshu \ 30 &＃43; 1) &＃39;试室

z &＃61; 9 * (i - 1) &＃43; 7

.Cells(z, 3).Value &＃61; "讲台"

.Cells((z &＃43; 1), 3).Value &＃61; "试室" & i

For j &＃61; 1 To 5&＃39;组

For k &＃61; 1 To 6 &＃39;排

x &＃61; 30 * (i - 1) &＃43; 6 * (j - 1) &＃43; k&＃39;考试号

If x <(renshu &＃43; 1) Then

y &＃61; 6 * (j - 1) &＃43; k&＃39;座号

m &＃61; 9 * (i - 1) &＃43; (7 - k) &＃39;纵坐标

n &＃61; j&＃39;横坐标

.Cells(m, n).Value &＃61; "(座号:" & y & ")" & h(x)

End If

Next k

Next j

Next i

四、准考证的输出

除了输出座位表&＃xff0c;还要输出准考证(包括存根)&＃xff0c;输出格式文件为“EXCELL”文件。其内容如下图所示&＃xff1a;

代码编写如下&＃xff1a;

For i &＃61; 1 To renshu

For j &＃61; 1 To renshu

If id2(j) &＃61; i Then

b(i) &＃61; g(j)

c(i) &＃61; h(j)

dd(i) &＃61; d(j)

ee(i) &＃61; e(j)

xb(i) &＃61; xbxb(j)

Exit For

End If

Next j

Next i

Dim ap As Excel.Application

Dim bk As Excel.Workbook

Dim st1 As Excel.Worksheet

Set ap &＃61; CreateObject("Excel.Application")

cd.DialogTitle &＃61; "打开EXCEL文件"

cd.Filter &＃61; "*.xls|*.xls"

cd.ShowOpen

If cd.FileName &＃61; "" Then MsgBox ("文件不能为空"): Exit Sub

Set bk &＃61; ap.Workbooks.Open(cd.FileName)

Set st1 &＃61; bk.Worksheets(1)

With st1

For j &＃61; 1 To 1

i &＃61; (j - 1) * 2 &＃43; 1

.Cells((j - 1) * 28 &＃43; 6, 4).Value &＃61; c(i)

.Cells((j - 1) * 28 &＃43; 6, 6).Value &＃61; xb(i)

.Cells((j - 1) * 28 &＃43; 7, 4).Value &＃61; dd(i)

.Cells((j - 1) * 28 &＃43; 7, 6).Value &＃61; ee(i)

.Cells((j - 1) * 28 &＃43; 8, 2).Value &＃61; b(i)

.Cells((j - 1) * 28 &＃43; 20, 4).Value &＃61; c(i)

.Cells((j - 1) * 28 &＃43; 20, 6).Value &＃61; xb(i)

.Cells((j - 1) * 28 &＃43; 21, 4).Value &＃61; dd(i)

.Cells((j - 1) * 28 &＃43; 21, 6).Value &＃61; ee(i)

.Cells((j - 1) * 28 &＃43; 22, 2).Value &＃61; b(i)

.Cells((j - 1) * 28 &＃43; 6, 12).Value &＃61; c(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 6, 14).Value &＃61; xb(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 7, 12).Value &＃61; dd(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 7, 14).Value &＃61; ee(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 8, 10).Value &＃61; b(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 20, 12).Value &＃61; c(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 20, 14).Value &＃61; xb(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 21, 12).Value &＃61; dd(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 21, 14).Value &＃61; ee(i &＃43; 1)

.Cells((j - 1) * 28 &＃43; 22, 10).Value &＃61; b(i &＃43; 1)

Next j

End With

五、总名册的输出

最后的输出是对考生的名册进行输出了&＃xff0c;输出格式同样为“EXCELL”格式文件。其内容如下图所示&＃xff1a;

代码编写如下&＃xff1a;

Dim ap As Excel.Application

Dim bk As Excel.Workbook

Dim st1 As Excel.Worksheet

Set ap &＃61; CreateObject("Excel.Application")

cd.DialogTitle &＃61; "打开EXCEL文件"

cd.Filter &＃61; "*.xls|*.xls"

cd.ShowOpen

If cd.FileName &＃61; "" Then MsgBox ("文件不能为空"): Exit Sub

Set bk &＃61; ap.Workbooks.Open(cd.FileName)

Set st1 &＃61; bk.Worksheets(1)

With st1

For i &＃61; 1 To renshu

For j &＃61; 1 To renshu

If id2(j) &＃61; i Then

dd(i) &＃61; d(j)

ee(i) &＃61; e(j)

Exit For

End If

Next j

Next i

For i &＃61; 1 To renshu

.Cells((i &＃43; 1), 7).Value &＃61; dd(i)

.Cells((i &＃43; 1), 8).Value &＃61; ee(i)

Next i

End With

六、其它

最后设计完整这个程序&＃xff0c;同时为方便程序的调试和运行&＃xff0c;还可以设计一些其它功能&＃xff0c;例如程序运行的进度条&＃xff0c;设计程序的时间和作者信息等&＃xff0c;最后完成的主界面如下图所示&＃xff1a;

在上面6步思路中&＃xff0c;我经过认真的编写和反复的调试修改&＃xff0c;最后终于完成。

程序的应用与评价

程序编写完后&＃xff0c;成功运行&＃xff0c;效果不错&＃xff0c;受到本学校领导和老师的高度肯定和赞扬。同时由于时间有限和本人的水平不高&＃xff0c;本小程序也存在不足的地方&＃xff0c;例如在处理大量数据时&＃xff0c;直接用VB与EXCELL进行对话&＃xff0c;没有应用到“数据库”&＃xff0c;使得运行时间偏长。这里我希望读者能给我意见和指正。