|
程序開(kāi)發(fā)的準(zhǔn)備 在開(kāi)發(fā)小程序前，首先要了解“緬茄杯”對(duì)座位編排的具體要求?！熬捛驯钡目忌鷣?lái)自高州市61所初級(jí)中學(xué)（每所中學(xué)的考生人數(shù)由3到400不等，總?cè)藬?shù)1848）。每個(gè)試室人數(shù)限額是30人，按5組6行編排。考生的座位是隨機(jī)的并且來(lái)自同一個(gè)學(xué)校的考生的座位前后左右都不能相鄰。其次選用一個(gè)好用的開(kāi)發(fā)軟件，本人就用自己比較常用的“VB”。 程序的開(kāi)發(fā) 程序的開(kāi)發(fā)，是最重要的，而且又是最艱難的一環(huán)工作，主要工作是代碼的編寫(xiě)，它要求“算法”精簡(jiǎn)優(yōu)化，操作簡(jiǎn)單，通用好用，這里我按以下幾部分進(jìn)行設(shè)計(jì)。 一、數(shù)據(jù)的導(dǎo)入 “緬茄杯”的考生在報(bào)名時(shí)，是由學(xué)校統(tǒng)一上送的。上送的名單是用“EXCELL”格式的文件上送的，考生的詳細(xì)信息如下圖所示：
為此程序的第一部工作是完成考生基本數(shù)據(jù)的導(dǎo)入。其代碼編寫(xiě)為： Dim ap As Excel.Application Dim bk As Excel.Workbook Dim st1 As Excel.Worksheet Set ap = CreateObject("Excel.Application") cd.DialogTitle = "打開(kāi)EXCEL文件" cd.Filter = "*.xls|*.xls" cd.ShowOpen If cd.FileName = "" Then MsgBox ("文件不能為空"): Exit Sub Set bk = ap.Workbooks.Open(cd.FileName) Set st1 = bk.Worksheets(1) cols = 1 rows = 1 'cells(行，列) With st1 Do Until .Cells(rows, 1).Value = "" '表格行數(shù) rows = rows + 1 Loop End With renshu = rows - 2 For i = 1 To renshu b(i) = Trim(st1.Cells((i + 1), 1).Value) c(i) = Trim(st1.Cells((i + 1), 3).Value) xb(i) = Trim(st1.Cells((i + 1), 4).Value) Next i bk.Close ap.Quit Set ap = Nothing 二、座位的編排 這是程序開(kāi)發(fā)的中心環(huán)節(jié)，我設(shè)計(jì)的思路簡(jiǎn)單描述是這樣的：先為每個(gè)學(xué)生添加一個(gè)各不相同的隨機(jī)數(shù)字（一至考生總?cè)藬?shù)，這里就是1～1848），再把考生按這些隨機(jī)數(shù)按升序的順序排列，依次編排到第一個(gè)試室至最后一個(gè)試室的1～30的座位中，然后按試室和座位的順序?qū)γ恳粋€(gè)考生進(jìn)行檢查，檢查他的前后左右是否有同學(xué)校的考生，如果有則對(duì)他進(jìn)行調(diào)整。其代碼編寫(xiě)如下： '隨機(jī)號(hào)生成 i = 1 Randomize Time Do While i < renshu + 1 flag2 = 0 n = (Rnd * (renshu - 1)) \ 1 + 1 For j = 1 To i If a(j) = n Then flag2 = 1 Exit For End If Next j If flag2 = 0 Then a(i) = n i = i + 1 End If Loop '試室,座號(hào)生成 For i = 1 To renshu \ 30 + 1 For j = 1 To 30 z = (i - 1) * 30 + j If z > renshu Then Exit For Exit For End If d(z) = i e(z) = j Next j Next i For i = 1 To renshu For j = 1 To renshu If a(j) = i Then f(i) = a(j) g(i) = b(j) h(i) = c(j) xbxb(i) = xb(j) id2(i) = id1(j) Exit For End If Next j Next i '判斷1 flag = 1 Do While flag = 1 flag = 0 For i = 1 To (renshu \ 30 + 1) For j = 1 To 29 x = 30 * (i - 1) + j If x < (renshu - 1) Then If g(x) = g(x + 1) Then Randomize y = (Rnd * (renshu - 1)) \ 1 + 1 temp1 = g(y) g(y) = g(x) g(x) = temp1 temp2 = h(y) h(y) = h(x) h(x) = temp2 temp3 = xbxb(y) xbxb(y) = xbxb(x) xbxb(x) = temp3 temp4 = id2(y) id2(y) = id2(x) id2(x) = temp4 End If End If Next j Next i '判斷2 For i = 1 To (renshu \ 30 + 1) For j = 1 To 24 x = 30 * (i - 1) + j If x < (renshu - 5) Then If g(x) = g(x + 6) Then Randomize y = (Rnd * (renshu - 1)) \ 1 + 1 temp1 = g(y) g(y) = g(x) g(x) = temp1 temp2 = h(y) h(y) = h(x) h(x) = temp2 temp3 = xbxb(y) xbxb(y) = xbxb(x) xbxb(x) = temp3 temp4 = id2(y) id2(y) = id2(x) id2(x) = temp4 End If End If Next j Next i '檢驗(yàn)1 For i = 1 To (renshu \ 30 + 1) For j = 1 To 29 x = 30 * (i - 1) + j If x < (renshu - 1) Then If g(x) = g(x + 1) Then flag = 1 Exit For End If End If Next j Next i '檢驗(yàn)2 For i = 1 To (renshu \ 30 + 1) For j = 1 To 24 x = 30 * (i - 1) + j If x < (renshu - 5) Then If g(x) = g(x + 6) Then flag = 1 Exit For End If End If Next j Next i Loop 三、座位表的輸出 完成座位編排后，還要把結(jié)果輸出。按實(shí)際需要首先要輸出座位表（輸出格式為“EXCELL”格式文件）。座位表內(nèi)容如下圖所示：
代碼編寫(xiě)如下： Dim zsbexcel As Excel.Application Dim zsbworkbook As Excel.Workbook Set zsbexcel = New Excel.Application zsbexcel.SheetsInNewWorkbook = 1 Set zsbworkbook = zsbexcel.Workbooks.Add With zsbexcel.ActiveSheet For i = 1 To (renshu \ 30 + 1) '試室 z = 9 * (i - 1) + 7 .Cells(z, 3).Value = "講臺(tái)" .Cells((z + 1), 3).Value = "試室" & i For j = 1 To 5 '組 For k = 1 To 6 '排 x = 30 * (i - 1) + 6 * (j - 1) + k '考試號(hào) If x < (renshu + 1) Then y = 6 * (j - 1) + k '座號(hào) m = 9 * (i - 1) + (7 - k) '縱坐標(biāo) n = j '橫坐標(biāo) .Cells(m, n).Value = "(座號(hào):" & y & ")" & h(x) End If Next k Next j Next i 四、準(zhǔn)考證的輸出 除了輸出座位表，還要輸出準(zhǔn)考證（包括存根），輸出格式文件為“EXCELL”文件。其內(nèi)容如下圖所示：
代碼編寫(xiě)如下： For i = 1 To renshu For j = 1 To renshu If id2(j) = i Then b(i) = g(j) c(i) = h(j) dd(i) = d(j) ee(i) = e(j) xb(i) = 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 = CreateObject("Excel.Application") cd.DialogTitle = "打開(kāi)EXCEL文件" cd.Filter = "*.xls|*.xls" cd.ShowOpen If cd.FileName = "" Then MsgBox ("文件不能為空"): Exit Sub Set bk = ap.Workbooks.Open(cd.FileName) Set st1 = bk.Worksheets(1) With st1 For j = 1 To 1 i = (j - 1) * 2 + 1 .Cells((j - 1) * 28 + 6, 4).Value = c(i) .Cells((j - 1) * 28 + 6, 6).Value = xb(i) .Cells((j - 1) * 28 + 7, 4).Value = dd(i) .Cells((j - 1) * 28 + 7, 6).Value = ee(i) .Cells((j - 1) * 28 + 8, 2).Value = b(i) .Cells((j - 1) * 28 + 20, 4).Value = c(i) .Cells((j - 1) * 28 + 20, 6).Value = xb(i) .Cells((j - 1) * 28 + 21, 4).Value = dd(i) .Cells((j - 1) * 28 + 21, 6).Value = ee(i) .Cells((j - 1) * 28 + 22, 2).Value = b(i) .Cells((j - 1) * 28 + 6, 12).Value = c(i + 1) .Cells((j - 1) * 28 + 6, 14).Value = xb(i + 1) .Cells((j - 1) * 28 + 7, 12).Value = dd(i + 1) .Cells((j - 1) * 28 + 7, 14).Value = ee(i + 1) .Cells((j - 1) * 28 + 8, 10).Value = b(i + 1) .Cells((j - 1) * 28 + 20, 12).Value = c(i + 1) .Cells((j - 1) * 28 + 20, 14).Value = xb(i + 1) .Cells((j - 1) * 28 + 21, 12).Value = dd(i + 1) .Cells((j - 1) * 28 + 21, 14).Value = ee(i + 1) .Cells((j - 1) * 28 + 22, 10).Value = b(i + 1) Next j End With 五、總名冊(cè)的輸出 最后的輸出是對(duì)考生的名冊(cè)進(jìn)行輸出了，輸出格式同樣為“EXCELL”格式文件。其內(nèi)容如下圖所示：
代碼編寫(xiě)如下： Dim ap As Excel.Application Dim bk As Excel.Workbook Dim st1 As Excel.Worksheet Set ap = CreateObject("Excel.Application") cd.DialogTitle = "打開(kāi)EXCEL文件" cd.Filter = "*.xls|*.xls" cd.ShowOpen If cd.FileName = "" Then MsgBox ("文件不能為空"): Exit Sub Set bk = ap.Workbooks.Open(cd.FileName) Set st1 = bk.Worksheets(1) With st1 For i = 1 To renshu For j = 1 To renshu If id2(j) = i Then dd(i) = d(j) ee(i) = e(j) Exit For End If Next j Next i For i = 1 To renshu .Cells((i + 1), 7).Value = dd(i) .Cells((i + 1), 8).Value = ee(i) Next i End With 六、其它 最后設(shè)計(jì)完整這個(gè)程序，同時(shí)為方便程序的調(diào)試和運(yùn)行，還可以設(shè)計(jì)一些其它功能，例如程序運(yùn)行的進(jìn)度條，設(shè)計(jì)程序的時(shí)間和作者信息等，最后完成的主界面如下圖所示：
在上面6步思路中，我經(jīng)過(guò)認(rèn)真的編寫(xiě)和反復(fù)的調(diào)試修改，最后終于完成。 程序的應(yīng)用與評(píng)價(jià) 程序編寫(xiě)完后，成功運(yùn)行，效果不錯(cuò)，受到本學(xué)校領(lǐng)導(dǎo)和老師的高度肯定和贊揚(yáng)。同時(shí)由于時(shí)間有限和本人的水平不高，本小程序也存在不足的地方，例如在處理大量數(shù)據(jù)時(shí)，直接用VB與EXCELL進(jìn)行對(duì)話，沒(méi)有應(yīng)用到“數(shù)據(jù)庫(kù)”，使得運(yùn)行時(shí)間偏長(zhǎng)。這里我希望讀者能給我意見(jiàn)和指正。 | 
