IFIX与EXCEL的连接 - 实在点好工控博客工控论坛

胡先生

学历：大学本科

职称：工程师

年龄：40岁

新闻信息(1/0)

工作图片(0)

技术论文(0/0)

交流论坛(64/54)

留言信箱(4)

浏览人次：12711

发表人：ifixonline

发表时间：2009/8/18 17:27:00

发表新论题

本栏论题：

IFIX与EXCEL的连接 [10733]

    第一步，创建ODBC数据源，具体方法：启动控制面板中的数据源ODBC，单击“系统DNS”对话框，选择所需数据源的驱动，如：“Microsoft excel driver (*.exl)”,单击“完成”按钮，弹出“ODBC Microsoft excel 安装”对话框；在数据源名中定义一个数据源名称，数据库中“选择”中选择区县数据所在的数据库，此数据库的表至少有三个字段：时间字段、数据字段、毫秒字段。单击确定按钮，新创建数据源就添加到“系统DNS”列表中。

    第二步，在IFIX中创建代码

    ADO—在安装了MCROWIN BASIC 6.0 等软件就有了；

    Private Sub CommandButton1_Click()
    '注释: 1。该程序需要安装ADO 2.0目标库并在本机注册
    ' 2。Microsoft ActiveX Data Objects 2.1 Library 必须被引用 (Office 2000)
    ' 3。Microsoft Excel 9.0 object libraries 必须被引用 (Office 2000)
    ' 4。划===处可根据具体报表修改

    Dim strQueryAvg As String

    Dim c As Integer
    Dim r As Integer
    Dim Intyexcel As Excel.Application

    Dim MyDate, MyMonth, MyDay, MyHour, MyMinute, MySecond
    Dim StartTime, EndTime, Duration, DisplayDay, DisplayMonth As String

    '++===================================================================
    '报表中的 TAG
    Dim Tag1, Tag2, Tag3, Tag4, Tag5, Tag6, Tag7, Tag8 As String
    Dim Items As Integer

    Tag1 = "TEST"
    Tag2 = "TEST1"
    Tag3 = " "
    Tag4 = " "
    Tag5 = " "
    Tag6 = " "
    Tag7 = " "
    Tag8 = " "

    '从历史库中取得域项， 2 - DATATIME, VALUE, TAG 共三项
    Items = 2
    '--====================================================================

    MyDate = Now()
    MyMonth = Month(MyDate)
    MyDay = Day(MyDate)
    MyHour = Hour(MyDate)
    MyMinute = Minute(MyDate)
    MySecond = Second(MyDate)

    StartTime = "2000" & "-" & MyMonth & "-" & MyDay - 1 & " " & "00:00:00"
    EndTime = "2000" & "-" & MyMonth & "-" & MyDay - 1 & " " & "23:00:00"

    '++==========================================================================
    '查询，根据报表修改
    strQueryAvg = "Select DATETIME, VALUE, TAG FROM FIX " & _
    "WHERE MODE = 'AVERAGE' and (TAG='" & Tag1 & "' or TAG='" & Tag2 & "'" & _
    " or TAG='" & Tag3 & "' or TAG='" & Tag4 & "' or TAG='" & Tag5 & "'" & _
    " or TAG='" & Tag6 & "' or TAG='" & Tag7 & "' or TAG='" & Tag8 & "')" & _
    "and INTERVAL = '01:00:00' and " & _
    "(DATETIME >= and " & _
    "DATETIME <= )"
    '--===========================================================================


    Dim cnADO As New ADODB.Connection
    Dim rsADO As Recordset

    Set cnADO = New ADODB.Connection
    cnADO.ConnectionString = "DSN = FIX Dynamics Historical Data; UID = sa; PWD = ;"
    cnADO.Open "FIX Dynamics Historical Data", "sa", ""

    Set rsADO = New ADODB.Recordset

    rsADO.Open strQueryAvg, cnADO, adOpenForwardOnly, adLockBatchOptimistic
    '''如果执行上面的语句出错的话，则最大的可能性就是SQL语句有错误！
    r = 1
    Set Intyexcel = New Excel.Application
    Intyexcel.Visible = False


    '++============================================================================
    '打开的报表文件名
    Dim OutReportFile As String
    Dim InReportFile As String

    InReportFile = "C:\Dynamics\App\HIST1"

    Intyexcel.Workbooks.Open InReportFile & ".XLS"

    Intyexcel.Sheets("Sheet2").Select
    Intyexcel.Columns("A:Z").Select
    Intyexcel.Selection.ClearContents
    Intyexcel.Range("A1").Select

    While rsADO.EOF <> True
    With Intyexcel.Worksheets(2)
    For c = 0 To Items
    If rsADO(c) <> "" Then .Cells(r, c + 1).Value = rsADO(c)
    Next c
    r = r + 1
    rsADO.MoveNext
    End With
    Wend

    Intyexcel.Sheets("Sheet1").Select

    ' Intyexcel.ActiveSheet.PageSetup.Orientation = xlPortrait 'xlLandscape
    ' Intyexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4
    Intyexcel.ActiveSheet.PrintOut
    Intyexcel.DisplayAlerts = False
    Intyexcel.Activ