基于 ifix 的 Excel 报表自动生成与制作 - 1 - http://www.paper.edu.cn 孟昭晋 1 2, 张红星 1 南京河海大学电气工程学院 ( 210098) 2 扬州惠通化工技术有限公司 (225009) E-mail:mengzhaojin@163.com 摘要 : 本文基于某化工生产项目, 利用 if1x 软件自身集成的 VBA 标准脚本语言以及 ADO 技术, 控制 Excel 根据要求生成多种报表以及预览打印报表 关健词 :ifix VBA ADO Excel 报表 1. 引言 本文基于国家 863 计划 的某化工项目, 根据厂方的要求, 需要对操作员的操作记录以及采集到的工艺参数进行筛选并能直接生成并制作出符合报表要求, 可以直接打印的报表, 我们对此要求通过内嵌在 ifix 中的标准脚本语言 VBA(Visual Basic for Applications) 获取 Excel 的控制句柄, 编写直接控制 Excel 操作的程序, 制作我们熟悉的并且符合中文习惯的 Excel 报表, 避免使用操作起来麻烦, 中文处理能力也不理想的 Crystal Reports 程序输出报表 下面通过实例来介绍 Excel 报表的制作过程 2.Execl 报表生成及制作 VBA 是一种易学易用, 编程简洁的开发工具语言, 通过它, 我们可以轻松的扩展 ifix 的功能, 使用 VBA 控制 Excel 生成并制作报表 2.1 本文用到的 Excel 对象 Excel 对象模型包括了 128 个不同的对象, 这里我们用到其中的四个对象 (1) Application 对象 Application 对象处于 Excel 对象层次结构的顶层, 表示 Excel 自身的运行环境 (2) Workbook 对象 Workbook 对象直接地处于 Application 对象的下层, 表示一个 Excel 工作薄文件 (3) Worksheet 对象 Worksheet 对象包含于 Workbook 对象, 表示一个 Excel 工作表 (4) Range 对象 Range 对象包含于 Worksheet 对象, 表示 Excel 工作表中的一个或多个单元格 2.2 生成并制作 Excel 报表 生成报表的界面如图 1, 其中还包括了对 Access 数据库在系统级加密基础上的再加密功能按钮 ( 它文介绍 ) (l) 首先在 VBE 编译环境中引用 Microsoft Excel 类型库 : 从 工程 菜单中选择 引用 栏, 选择 Microsoft Excel 9.0 Object Library(Office 2000 对应 9.0 版本 ) 和 Microsoft ActiveX Data Objects 2.5 Library (2) 自动生成自命名 Excel 文件 这里通过生成报表时调用过程 CreatXls 实现
CreatXls 关键代码 : ' 首先定义我们所要使用的 Excel 对象 Public xlapp As Excel.Application ' 应用 Public xlbook As Excel.Workbook ' 工作薄 Public xlsheet As Excel.Worksheet ' 工作表 Set xlapp = CreateObject("Excel.Application") ' 定义 Xls 对象 Set xlbook=xlapp.workbooks.add' 定义工作簿对象 Set xlsheet =xlbook.worksheets(3)' 定义工作表对象 xlsheet.cells.numberformatlocal = "@"' 设置为文本格式 ' 生成文件保存路径 Dim Path As String Dim Name As String Name = Textbox2.Value Path = "E:" + "\" + text http://www.paper.edu.cn 图 1. 报表生成及制作界面 xlbook.saveas (path)' 当前程序目录 "E:\Name.xls" xlbook.close (True) ' 按内容变化关闭 xlapp.quit' 关闭创建的文件 Set xlbook = Nothing Set xlapp = Nothing Set xlsheet = Nothing - 2 -
- 3 - http://www.paper.edu.cn ' 确认文件是否生成 Dim ReturnValue As Variant Dim IsOK As Boolean IsOK = True GoTo Exit_Sub' 创建 XLS 文件后退出 Exit_Sub: xlbook.close (False) ' 不按内容变化关闭 If IsOK = True Then MsgBox " 生成 Excel 成功!", vbokonly + vbinformation, " 消息 :" ReturnValue = Shell("Explorer," & App.path, vbnormalnofocus) ' 查看当前路径 (3) 报表制作 同样要首先定义我们所要使用的 Excel 对象, 这里不再赘述 关键代码如下 [1] : ' 利用 ODBC 和 ADO 技术访问要读取数据生成报表的数据库 如操作记录数据库 Record, 报警数据库 FIXALARMS 等 Dim cn As New ADODB.Connection Dim rst As New ADODB.Recordset Set cn = New ADODB.Connection Set rst = New ADODB.Recordset cn.cursorlocation = aduseclient cn.open "Provider=MSDASQL.1;Persist Security_ Info=true;Data Source= 数据库名 ",, " 密码 " rst.cursortype = adopenkeyset rst.open "select*from hist where 标签名 ='"_ & (textbox5.text) & "'", cn, adopenkeyset,_ adlockoptimistic adlockreadonly' 按标签名筛选数据 rst.open "select * from hist where 工号 ='" _ &(textbox4.text)&"'",cn,adopenkeyset,adlock_ Optimistic adlockreadonly' 按工号筛选数据 rst.open "select * from hist where 时间 ='" _ &(textbox3.text)&"'",cn,adopenkeyset,adlock_ Optimistic adlockreadonly ' 按时间筛选数据 如果数据库为 Access 则 &(textbox3.text)& 应为 #(textbox3.text)# ' 判断文件是否存在 Dim fs As New filesystemobject Path = "E:" + "\" + Name If fs.fileexists(path) Then MsgBox " 文件存在!" Exit Sub Else Call creatxls GoTo Continue
http://www.paper.edu.cn End If Continue 关键代码 : xlapp.visible = True 'Excel 可见 xlsheet.range("g1") = " 报表生成时间 : " & Now()' 在 G1 单元格生成报表时间 xlsheet.range("a1:f1").mergecells = True' 合并第一列 A1 到 F1 的单元格 xlsheet.cells(1, 1).Interior.ColorIndex = 2 ' 设置第 2 行底色为蓝色 xlsheet.cells(1, 1).Font.Name = " 黑体 " xlsheet.cells(1, 1).Value = textbox2.value ' 在 textbox2 中输入需要的表头, 表头是一份报表的关键部分 xlsheet.cells(1, 1).Font.Bold = True ' 标题字体加粗 xlsheet.cells(1, 1).HorizontalAlignment = xlcenter ' 表头在单元格中居中以下以读取 Access 数据库生成 Excel 报表为例 : Dim Line As Long ' 记录 Excel 行数 Dim Column As Long ' 记录 Excel 列数 Dim rstcount As Long ' 记录 Access 行数 Dim rstfield As Long ' 记录 Access 列数 Dim scellvalue As String ' 将筛选过的 Access 数据从读取到 Excel 中 rstcount = rst.recordcount rstfield = rst.fields.count Line = 2 ' 写列头 For Column = 0 To rstfield - 1 scellvalue = rst.fields(column).name xlsheet.cells(line, Column + 1) = scellvalue Next Column DoEvents ' 给下一个执行让 " 道 " For Line = 3 To rstcount + 1 For Column = 0 To rstfield - 1 scellvalue = rst.fields(column) xlsheet.cells(line, Column + 1) = scellvalue Next Column rst.movenext ' 下一行数据 Next Line For Column = 1 To rstfield xlsheet.columns(column).autofit' 自动调整列 xlsheet.range(cells(1,1),cells(rstcount+1,rstfield))_.borders.linestyle = xlcontinuous ' 设表格边框样式 Next ' 输出该表 xlbook.save ' 保存 - 4 -
http://www.paper.edu.cn 生成报表如图 2, 图 3 和图 4: 2.3 报表打印生成了工作表后, 就可以对 Excel 发出打印指令了 注意在执行打印操作之前应该对 Excel 临时文件执行一次保存操作 图 2. 按工号报表 图 3. 按时间报表 图 4 按标签名报表 如下语句 : xlbook.save ' 保存文件 xlsheet.printout ' 执行打印 x1app.quit ' 退出 EXCEL 语句 xlsheet.preview 可以实现打印预览 3. 结束语 到此为止我们己经利用 ifix 的脚本语言 VBA 通过 ADO 技术引用 Excel 对象实现了报表自动生成和制作 我们这里是直接通过 ADO 从 Access 取得数据写入 Excel 单元格, 当然也可以从 ifix 的过程数据库中读取标签值做成 Excel 报表 - 5 -
http://www.paper.edu.cn 总之, 取得的任何数据都可以通过以上的方法做成 Excel 报表 参考文献 [1] Visual Basic 开发指南一 ADO 篇,Mike Gunderloy 著, 电子工业出版社. Automatic Make of Report Forms With Excel Based On ifix Meng Zhaojin 1,Zhang Hongxing 2 College of Electric Engineering of Hohai University, Nanjing, (210098) HuiTong Chemical Industry Technique CO.,LTD,Yangzhou,(225009) Abstracts The paper which based on a chemical manufacture introduces the method of controlling Excel to automatically make and print report forms with VBA and the technology of ADO in ifix. Keywords: ifix VBA ADO Report Forms of Excel 作者简介 : 孟昭晋 (1982 年出生 ), 山东即墨人 现为河海大学电气工程学院在读研究生, 研究方向为工业控制 - 6 -