基于Excel VBA的学生宿舍管理系统开发研究

傅思萍

(泉州经贸职业技术学院信息系　福建泉州　362000)



基于Excel VBA的学生宿舍管理系统开发研究

傅思萍

(泉州经贸职业技术学院信息系福建泉州362000)

摘要：学生宿舍管理是学校管理工作的重要组成部分，宿舍管理工作繁杂，利用手工纸质管理容易忙中出错，效率低下。为了提高学院宿舍管理工作的效率和质量，笔者采用Excel VBA编程开发学生宿舍管理系统，系统包含四个功能模块：学生信息管理、住宿信息管理、用户管理和统计分析模块，重点研究统计分析功能，实现在窗体中自动识别统计汇总结果生成图表。

关键词：Excel VBA，学生宿舍管理系统，窗体图表，自动识别，分类汇总

目前很多高校仍采用传统的手工方式来管理学生宿舍信息，管理工作繁杂、琐碎。为了提高宿舍管理工作效率，为学生提供便捷的服务，迫切需要开发学生宿舍管理系统，提高学校现代化管理水平，笔者采用Excel VBA开发一款小型简单实用的学生宿舍管理系统。

Excel VBA提供了完整的程序设计语言，使用和VB相同的语言结构，利用VBA已有的控件能设计出漂亮的窗体。在Excel中使用VBA，能高效率地实现数据处理自动化，将工作人员从简单而重复的数据处理工作中解脱出来[1]。不少学者利用Excel VBA设计系统辅助管理学校工作，孙淑娟等提出利用Excel VBA开发统计学校教师工作量[2]，韩昌选提出利用Excel VBA开发考场教室安排系统[3]，俞山峰提出应用Excel VBA开发考试系统[4]。本系统实现在窗体中引入了图表统计分析功能。

1系统分析

学生处在每个学期最后3周接受老生调换宿舍申请；每年新生入校，为新生分配宿舍；毕业班离校，要批量删除离校学生的住宿信息。每到这些时候，学生处、各系分管宿舍的老师总是非常忙碌。系统开发的目的是为了方便分管宿舍的老师对学生住宿情况进行管理，能快速查看宿舍安排情况、安排新生入住、调整住宿信息、办理退宿等操作。系统包含两种角色：系统管理员、管理员。系统管理员可以在后台对系统进行管理；管理员是指学生处、各系分管宿舍的老师，他们可以管理学生、住宿信息。

2系统开发研究

系统包含学生信息管理、住宿信息管理、统计分析和用户管理四个功能模块，其功能结构如图1所示。

图1　学生宿舍管理系统功能结构图

2.1工作表设计

利用Excel创建“学生宿舍管理系统.xls”工作簿，根据我院宿舍管理实际情况分析，建立3个工作表：学生信息表、宿舍楼层情况表和宿舍安排表。其中，学生信息表包含学号、姓名、性别、院系、班级、出生日期、籍贯、家庭地址等学生基本信息，每年秋学期新生入校，各系已经利用Excel创建各班花名册，只需将花名册学生信息导入学生信息表。宿舍楼层情况表保存每栋楼每层每间宿舍信息，如A栋每层有7间宿舍，每间最多有8个床位，工作表信息如图2所示。宿舍安排表保存宿舍各床位分配详细信息，包含宿舍楼、宿舍、床位号、院系、班级、学号、姓名、性别，如图3所示。

图2　宿舍楼层情况表

图3　宿舍安排表

2.2 系统功能

2.2.1 基本管理模块基本管理模块是指学生信息管理、住宿信息管理和用户管理3个模块。其中，学生信息管理模块完成将学生花名册信息的导入，新增个别学生信息，修改、删除、查询学生信息的操作；住宿信息管理模块实现安排新生或老生入住，调整住宿信息，删除和查询住宿信息；用户管理模块可以对系统的用户进行管理，包括新增、删除用户和修改用户权限。

2.2.2统计分析模块该模块是为了方便管理员查看每栋楼、每层、每间、各系和各班宿舍安排情况，采用图表直观形象显示统计分析结果。管理员经常需要查看、统计每栋楼每个房间剩余床位情况，方便安排学生入住。以统计A栋1楼各间宿舍空床位数量为例。窗体设计界面如图4所示。

图4　窗体界面

Excel VBA没有支持图表的控件，利用Excel VBA编程实现图表统计分析功能的思路是：选择符合条件记录；对记录自动进行分类汇总；制作图表；在窗体中显示图表。

(1)选择符合条件记录。从“宿舍安排表”把符合条件的住宿记录选择出来存入sheet3工作表中，主要代码如下：

Dim i, j

If ComboBox1.Value = "" Then '是否有选择宿舍楼

MsgBox "请选择宿舍楼！"

ComboBox1.SetFocus

Exit Sub

End If

Sheet3.Range("A2:IV65536").RemoveSubtotal '撤销分类汇总

Sheet3.Range("A2:IV65536") = ""

j = 2

For i = 2 To Sheets("宿舍安排表").Range("a65535").End(xlUp).Row '从第2行到最后一行

If (Sheets("宿舍安排表").Cells(i, 1) = ComboBox1.Text And Left(Sheets("宿舍安排表").Cells(i, 2), 1) = ComboBox2.Text) Or (Sheets("宿舍安排表").Cells(i, 1) = ComboBox1.Text And ComboBox2.Text = "") Then

当前单元格的宿舍楼和楼层等于选定的值

With Sheets("宿舍安排表")

Sheet3.Cells(j, 1) = .Cells(i, 1)

Sheet3.Cells(j, 2) = .Cells(i, 2)

Sheet3.Cells(j, 3) = .Cells(i, 3)

Sheet3.Cells(j, 4) = .Cells(i, 4)

Sheet3.Cells(j, 5) = .Cells(i, 5)

Sheet3.Cells(j, 6) = .Cells(i, 6)

Sheet3.Cells(j, 7) = .Cells(i, 7)

Sheet3.Cells(j, 8) = .Cells(i, 8)

End With

j = j + 1 'j-1为满足条件的记录数

End If

Next

(2)分类汇总。对sheet3工作表中符合条件记录进行自动分类汇总，按“宿舍号”升序排序，以“宿舍号”为分类字段，对床位号进行计数统计。主要代码如下：

Application.DisplayAlerts = False

Application.ScreenUpdating = False

Worksheets("sheet3").Activate

With ActiveSheet.Sort

.SortFields.Clear

.SortFields.Add Key:=Range(Range("B2"), Cells(Rows.Count, 2).End(xlUp)), _

SortOn:=xlSortOnValues, Order:=xlAscending

.SetRange Range("B1").CurrentRegio

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("B1").Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(3), _

Replace:=True, SummaryBelowData:=True

ActiveWindow.DisplayOutline = False

Application.DisplayAlerts = True

Application.ScreenUpdating = True

执行代码，Sheet3工作表自动分类汇总结果如图5所示。

图5自动分类汇总结果

(3)制作图表。根据sheet3分类汇总结果制作折线图表，但每次要查看宿舍楼和楼层可能不同，因而分类汇总结果是动态的，汇总结果所在行不固定，解决制作图表的关键问题是如何动态识别作图数据源。

通过分析汇总结果的规律，利用循环逐行判断宿舍列对应单元格，满足条件时选中对应数据单元格；逐行判断累加选择符合条件的数据区域作为作图数据源。但符合条件的数据区域不连续，不连续数据区域如何累加选中？考虑把每次得到的符合条件的单元格区域转换为字符串，通过字符串相连完成数据源识别操作。对制作好的折线图选项进行设置，设置数据标志显示值，在折线点显示对应数值，这样显示结果更清晰；根据复合框设置的值修改图表标题，如选中A栋1楼，则标题显示为“A栋1楼床位已使用情况统计”。主要代码如下：

Dim str As String

str = "B1:C1,"

动态识别做图数据源

For i = 2 To Sheet3.Range("a65535").End(xlUp).Row '从第2行到最后一行

If Sheet3.Cells(i, 2) <> Sheet3.Cells(i + 1, 2) And Left(Sheet3.Cells(i + 1, 2), 3) = Left(Sheet3.Cells(i, 2), 3) Then 'Left(Sheet3.Cells(i, 2), 3)清除空格

Range(Sheet3.Cells(i + 1, 2), Sheet3.Cells(i + 1, 3)).Select

str = str & Selection.Address(False, False) & "," '数据源区域转为字符串再相连

End If

Next

str = Left(str, Len(str) - 1) '去除最后一个,

Sheet3.Cells(i, 2).Select '定位到最后一行第一格

str1 = Selection.Address(False, False)

'生成图表

Range(str).Select

Range(str1).Activate '最后一行的第一格

ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=Range(str)

ActiveChart.ChartType = xlLine

ActiveChart.ChartArea.Select '图表数据标志显示值

ActiveChart.ApplyLayout (9)

str1 = ComboBox1.Text + ComboBox2.Text + "楼"

ActiveChart.ChartTitle.Text = str1 + "床位已使用情况统计" '修改图表标题

(4)在窗体中显示图表。第(3)步代码设计制作的图表显示在工作表中，如何将图表移到窗体中？在窗体中添加Image控件，将图表保存为图片，作为Image控件的数据源。主要代码如下：

Dim pic As Chart, frame As String

Set pic = ThisWorkbook.Worksheets("sheet3").ChartObjects("1").Chart

frame = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"

pic.Export Filename:=frame, filtername:="gif"

Image1.Picture = LoadPicture(frame)

Kill ThisWorkbook.Path & " emp.gif"

ActiveSheet.ChartObjects("1").Activate '删除工作表中图表

ActiveChart.Parent.Delete

运行窗体，单击“图表分析”按钮，窗体显示结果如下图6所示。通过图表，管理员可以清楚地看到A栋1楼各间宿舍床位已使用情况。

图6窗体运行结果

利用上述设计思路同样可以实现各层、各栋楼、各系和各班的住宿信息图表统计功能，也可以根据需要绘制其他类型图表，提升管理质量。

3结语

利用Excel VBA设计的学生宿舍管理系统，能方便管理员高效率、高质量地管理学生住宿信息，系统操作方便，易于使用。系统开发研究实现统计分析功能，使得管理员能直观地在窗体中查看图表统计汇总结果，不用一直面对枯燥的宿舍文字处理操作。

参考文献：

[1]郭刚.Excel VBA入门与应用典型实例[M].北京：科学出版社、北京希望电子出版社.2009.66.

[2]孙淑娟，刘静宜，崔立超.Excel VBA在高校办公自动化中的应用[J].宜春学院学报，2013，35(6)：48.

[3]韩昌选.基于Excel VBA考场教室安排系统的设计与实现[J].南京广播电视大学学报，2012，20(4)：82.

[4]俞山峰.应用Excel VBA开发考试系统的研究[J].杨凌职业技术学院学报，2011，10(1)：22.

(责任编辑胡安娜)

中图分类号：TP 311

文献标识码：A

文章编号：1674-9545(2015)04-0070-(04)

通讯作者：傅思萍，sipingfu@qq.com。

收稿日期：2015-9-14