我曾经在开发学生档案管理系统,在生成学生学籍卡时,找了好几种报表都很难实现学生照片显示在学籍卡上,最后试着用Active Report报表实现了学生照片的打印,现将这个方法介绍给大家。
(1)、首先,把学生照片(jpg文件)存储的问题。
由于学生档案管理系统是用Access做库,照片的存储采用OLE对象存储到数据库的相关表中。具体存储到函数为:
Public Sub SaveImage(ByVal ImageFile As String, filedname As String, Number As String) '把图片写入到数据库,参数ImageFile为图形路径及文件名,filedname为数据库表中的字段名,Number为存储到哪个记录。
Dim blocksize As Long
blocksize = 1024
If ImageFile = "" Then
Exit Sub
End If
Dim Res As New ADODB.Recordset
Set Res = New ADODB.Recordset
Sql = "select * from StudentInfo where StudentNumber='" & Number & "'" 'StudentInfo为数据库名称
Res.Open Sql, Conn, adOpenStatic, adLockOptimistic, adCmdText
On Error GoTo OpenData
If Res.RecordCount > 0 Then
'提供一个尚未使用的文件号
SourceFile = FreeFile
'打开文件
Open ImageFile For Binary Access Read As SourceFile
'得到文件长度
FileLength = LOF(SourceFile)
'判断文件是否存在
If FileLength = 0 Then
Close SourceFile
MsgBox DiskFile & "无内容或不存在!"
Else
NumBlocks = FileLength / blocksize '得到数据块的个数
LeftOver = FileLength Mod blocksize '得到剩余字节数
On Error GoTo WritePhoto
Res.Fields(filedname).Value = Null
ReDim byteData(blocksize) '重新定义数据块的大小
For i = 1 To NumBlocks
Get SourceFile, , byteData() '读到内存块中
Res.Fields(filedname).AppendChunk byteData() '写入FLD
Next i
ReDim byteData(LeftOver) '重新定义数据块的大小
Get SourceFile, , byteData() '读到内存块中
Res.Fields(filedname).AppendChunk byteData() '写入FLD
Close SourceFile '关闭源文件
Res.Update
End If
Res.Close
Set Res = Nothing
End If
Exit Sub
OpenData:
MsgBox "打死数据库出错", vbCritical + vbCritical, "错误"
WritePhoto:
MsgBox "数据库出错,不能保存你的照片请与开发商联系", vbCritical + vbOKOnly, "错误"
End Sub
用这个函数实现图形文件存储到数据库。
(2)、如何将数据库中的图形文件顺序的显示报表中
在做学生学籍卡时,要顺序的将数据库表中记录读取,然后写到报表中,代码如下:
Private Sub BtnPrint_Click() '将数据导入到Excel中
Dim Res As New ADODB.Recordset
Set Res = New ADODB.Recordset
Dim Sql As String
If SFAllSchool = True Then '学校
Sql = "Select * from School,StudentInfo where StudentStudy=True And School.SchoolID=StudentInfo.SchoolID"
Else
Sql = "Select * from School,StudentInfo where StudentStudy=True And School.SchoolID=StudentInfo.SchoolID And StudentInfo.SchoolID=" & CurrenSchool
End If
If SFAllGread = False Then '年级
Sql = Sql & " And StudentGread=" & CurrenGread
End If
If SFAllClass = False Then '班级
Sql = Sql & " And StudentClass=" & CurrenClass
End If
Sql = Sql & " ORDER BY StudentNumber ASC"
Res.Open Sql, Conn, adOpenStatic, adLockOptimistic, adCmdText
On Error GoTo OpenData
If Res.RecordCount > 0 Then
XJK.StudentNumber.DataField = "StudentNumber"
XJK.StudentName.DataField = "StudentName"
XJK.StudentSex.DataField = "StudentSex"
XJK.StudentBirthDay.DataField = "StudentBirthDay"
XJK.StudentClass.DataField = "StudentClass"
XJK.StudentJS.DataField = "StudentJS"
XJK.Image1.DataField = "StudentPhoto"
XJK.DataControl1.Recordset = Res
XJK.WindowState = 2
XJK.Printer.PaperSize = 9 '设置页面为A4
XJK.PageSettings.LeftMargin = 0.5 / 2.54 * 1440
XJK.PageSettings.RightMargin = 0.5 / 2.54 * 1440
XJK.PageSettings.TopMargin = 0.5 / 2.54 * 1440
XJK.PageSettings.BottomMargin = 0.5 / 2.54 * 1440
XJK.Show
Else
MsgBox "没有数据生成学籍卡", vbInformation + vbOKOnly, "提示"
End If
Exit Sub
OpenData:
MsgBox "打开数据库出错", vbCritical + vbOKOnly, "错误"
End Sub
期中
XJK.StudentNumber.DataField = "StudentNumber"
XJK.StudentName.DataField = "StudentName"
XJK.StudentSex.DataField = "StudentSex"
XJK.StudentBirthDay.DataField = "StudentBirthDay"
XJK.StudentClass.DataField = "StudentClass"
XJK.StudentJS.DataField = "StudentJS"
XJK.Image1.DataField = "StudentPhoto" ‘显示学生照片
XJK.DataControl1.Recordset = Res
XJK.WindowState = 2
XJK.Printer.PaperSize = 9 '设置页面为A4
XJK.PageSettings.LeftMargin = 0.5 / 2.54 * 1440
XJK.PageSettings.RightMargin = 0.5 / 2.54 * 1440
XJK.PageSettings.TopMargin = 0.5 / 2.54 * 1440
XJK.PageSettings.BottomMargin = 0.5 / 2.54 * 1440
XJK.Show
xjk是事先设计好的报表文件,XJK.Image1.DataField="StudentPhoto"很容易的就把学生照片显示出来。
效果如下: