Qt编写iFIx组态软件日报表插件的实现

文章描述了一种使用iFIX组态软件生成report.MDB文件并利用用户脚本定时存储数据的过程。接着,介绍了如何在本地设置ODBC数据源以连接Access数据库。此外,还详细说明了如何使用Qt程序通过ODBC连接数据库,检索特定时间的数据,并将数据写入Excel模板文件生成报表。
摘要由CSDN通过智能技术生成

一、iFIx中生成report.MDB文件

在Ifxi组态软件的【调度】中新建调度任务【report】添加【定时器调度项】FixTimer5,间隔1h触发一次。通过此任务及脚本程序,将相关变量定时存入自动生成的report.MDB文件中。

 用户脚本程序如下:

'---- User ConnectionString ----
Const ExcelConnectionString = "PROVIDER=MSDASQL;DRIVER=Microsoft Excel Driver (*.xls);User ID=Admin;FIL=excel 8.0;DBQ="

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

'---- ConnectModeEnum Values ----
Const adModeUnknown = 0
Const adModeRead = 1
Const adModeWrite = 2
Const adModeReadWrite = 3
Const adModeShareDenyRead = 4
Const adModeShareDenyWrite = 8
Const adModeShareExclusive = &HC
Const adModeShareDenyNone = &H10
Const adModeRecursive = &H400000
Dim LastAlarmCount As Long, LastAlarmDate As String, creat_rpt As Boolean
Dim last_re As Boolean

Private Sub rpt_record()

Dim strQuery As String
Dim db As Object, rst As Object
Dim rtn As Integer, v1 As Double, v2 As Double, v3 As Double, v4 As Double, v5 As Double, v6 As Double, v7 As Double, v8 As Double, v9 As Double, v10 As Double, uPath As String     
Dim T1, T2, T3, M1, M2, M3
On Error Resume Next
 rtn = 1
v1 = READVALUE("GFJ_AI_0.F_CV", 1) '进水浊度
v2 = READVALUE("GFJ_AI_1.F_CV", 1) '进水PH
v3 = READVALUE("GFJ_AI_8.F_CV", 1) '空气总管压力
v4 = READVALUE("GFJ_AI_9.F_CV", 1) '提升泵房液位
v5 = READVALUE("OUT_AI_0.F_CV", 1) '出水COD
v6 = READVALUE("OUT_AI_1.F_CV", 1) '出水氨氮
v7 = READVALUE("OUT_AI_2.F_CV", 1) '出水总磷
v8 = READVALUE("OUT_AI_3.F_CV", 1) '出水总氮
v9 = READVALUE("OUT_AI_4.F_CV", 1) '出水PH

T1 = Date
If last_re Then
T2 = 24
Else
T2 = Hour(Now)
End If
T3 = Minute(Now)
M1 = Year(Now)
M2 = Month(Now)
M3 = M1 & M2
Set db = CreateObject("ADODB.Connection")
'db.ConnectionString = "DSN = report; UID =; PWD =;"
db.Open "report", "", ""
db.Execute "select * into " & M3 & " from rptdata"
'---------
db.Execute "insert into " & M3 & "([date],[hr],[mi])values('" & T1 & "','" & T2 & "','" & T3 & "')"
db.Execute "update " & M3 & " set [v1]='" & v1 & "',[v2]='" & v2 & "',[v3]='" & v3 & "',[v4]='" & v4 & "',[v5]='" & v5 & "',[v6]='" & v6 & _
        "',[v7]='" & v7 & "',[v8]='" & v8 & "',[v9]='" & v9 & "',[v10]='" & v10 & "' where date = '" & T1 & "' and hr = '" & T2 & "'"
db.Close
End Sub

Private Sub FixTimer5_OnTimeOut(ByVal lTimerId As Long)
last_re = 0
rpt_record
End Sub

二、本地电脑上设置数据库ODBC

在【Windows管理工具】下打开【ODBC Data Sources(32-bit)】,添加【Driver do Microsoft Access(*.mdb)】,数据源名称设为【12】,数据库选择路径为ifIx组态软件生成的路径下的report.mdb数据库文件,点击【确定】即可。

 三、Qt程序编写

1、数据库连接,注意"SetDatabaseName"内写“12”和上述数据源名称一致

      // 通过ODBC连接ACESSS数据库
        QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
        db.setHostName("127.0.0.1");
        db.setDatabaseName("12");
        db.setUserName("");
        db.setPassword("");
        bool ok = db.open();
        // 判断数据库是否连接成功
        if (ok)
        {
             QMessageBox::information(this, "infor", "success");
        }
        else
        {
             QMessageBox::information(this, "infor", "open failed");
             qDebug()<<"error open database because"<<db.lastError().text();
        }

2、按照表名、日期和时刻(0-23)搜索数据,存入容器内

      注意:QString 数据类型的变量表达“...%1...”.arg(str)

  QString str=QString("SELECT * FROM `%1` where date = '%2' and hr = '%3'").arg(year_month).arg(year_month1).arg(QString::number(hr));
        QSqlQuery query(str,db);
 QSqlRecord rec = query.record();
       while(query.next())
 //调用一次QSqlQuery::next()内部指针就移动到第一条记录上,然后重复调用next()方法就可以移动到其他记录上,
        //直到该函数返回false为止
       {
            rec = query.record();
            int  v1_data=rec.indexOf("v1");
            v1.push_back( query.value(v1_data).toDouble());
            int  v2_data=rec.indexOf("v2");
            v2.push_back( query.value(v2_data).toDouble());
            int  v3_data=rec.indexOf("v3");
            v3.push_back( query.value(v3_data).toDouble());
            int  v4_data=rec.indexOf("v4");
            v4.push_back( query.value(v4_data).toDouble());
            int  v5_data=rec.indexOf("v5");
            v5.push_back( query.value(v5_data).toDouble());
            int  v6_data=rec.indexOf("v6");
            if(query.value(v6_data).toDouble()>1.0)
            v6.push_back( query.value(v6_data).toDouble()/100.0);
            else v6.push_back(query.value(v6_data).toDouble());
            int  v7_data=rec.indexOf("v7");
            if(query.value(v7_data).toDouble()>1.0)
            v7.push_back( query.value(v7_data).toDouble());
            else v7.push_back(query.value(v7_data).toDouble());
            int  v8_data=rec.indexOf("v8");
            v8.push_back( query.value(v8_data).toDouble());
            int  v9_data=rec.indexOf("v9");
            v9.push_back( query.value(v9_data).toDouble());
            int  v10_data=rec.indexOf("v10");
            v10.push_back( query.value(v10_data).toDouble());
          }
           QueryData(db);
        }

3、数据生成.xls文件

采用.xls模板文件的方式,在模板文件Cell上填入读取到的数据,sheet1中总共有27行数据

       int cellrow;
       int rows=27;

        //获取工作表集合的工作表1
        work_sheet = work_sheets->querySubObject("Item(int)",1);
        
       //Sheet1:    D/E/F/H/I/J/K/L/M/N/O/P
        QAxObject *cellB,*cellC,*cellD,*cellE,*cellF,*cellH,*cellI,*cellJ,*cellK,*cellL,*cellM,*cellN,*cellO,*cellP;

        //填写报表日期
        cellrow=4;
        QString D0="D"+QString::number(2);
        cellD = work_sheet->querySubObject("Range(QVariant, QVariant)",D0);
        cellD->dynamicCall("SetValue(const QVariant&)",QVariant(ReportDate));
 
        //获取列表数据,写入excel
        //设置表格数据

        for(int i=0;i<rows;i++)
        {
            //设置要操作的单元格,如A1,A2,A3,A4
            QString C="C"+QString::number(cellrow);
            QString D="D"+QString::number(cellrow);
            QString E="E"+QString::number(cellrow);
            QString F="F"+QString::number(cellrow);
            QString H="H"+QString::number(cellrow);
            QString I="I"+QString::number(cellrow);
            QString J="J"+QString::number(cellrow);
            QString K="K"+QString::number(cellrow);
            QString L="L"+QString::number(cellrow);
            QString M="M"+QString::number(cellrow);
            QString N="N"+QString::number(cellrow);
            QString O="O"+QString::number(cellrow);
            QString P="P"+QString::number(cellrow);
            //获取单元格
            cellC = work_sheet->querySubObject("Range(QVariant, QVariant)",C);
            cellD = work_sheet->querySubObject("Range(QVariant, QVariant)",D);
            cellE = work_sheet->querySubObject("Range(QVariant, QVariant)",E);
            cellF = work_sheet->querySubObject("Range(QVariant, QVariant)",F);
            cellH = work_sheet->querySubObject("Range(QVariant, QVariant)",H);
            cellI = work_sheet->querySubObject("Range(QVariant, QVariant)",I);
            cellJ = work_sheet->querySubObject("Range(QVariant, QVariant)",J);
            cellK = work_sheet->querySubObject("Range(QVariant, QVariant)",K);
            cellL = work_sheet->querySubObject("Range(QVariant, QVariant)",L);
            cellM = work_sheet->querySubObject("Range(QVariant, QVariant)",M);
            cellN = work_sheet->querySubObject("Range(QVariant, QVariant)",N);
            cellO = work_sheet->querySubObject("Range(QVariant, QVariant)",O);
            cellP = work_sheet->querySubObject("Range(QVariant, QVariant)",P);
       

            //设置单元格的值
            if(i<v1.size())  //*****判断下标在容器索引内
             cellD->dynamicCall("SetValue(const QVariant&)",QVariant(v1.at(i)));
            if(i<v2.size())
            cellE->dynamicCall("SetValue(const QVariant&)",QVariant(v2.at(i)));
            if(i<v3.size())
            cellF->dynamicCall("SetValue(const QVariant&)",QVariant(v3.at(i)));
            if(i<v4.size())
            cellJ->dynamicCall("SetValue(const QVariant&)",QVariant(v4.at(i)));
            if(i<v5.size())
            cellK->dynamicCall("SetValue(const QVariant&)",QVariant(v5.at(i)));
            if(i<v6.size())
            cellL->dynamicCall("SetValue(const QVariant&)",QVariant(v6.at(i)));
            if(i<v7.size())
            cellM->dynamicCall("SetValue(const QVariant&)",QVariant(v7.at(i)));
            if(i<v8.size())
            cellN->dynamicCall("SetValue(const QVariant&)",QVariant(v8.at(i)));
            if(i<v9.size())
            cellO->dynamicCall("SetValue(const QVariant&)",QVariant(v9.at(i)));
            if(i<v10.size())
            cellP->dynamicCall("SetValue(const QVariant&)",QVariant(v10.at(i)));


      }
       cellrow++;

四、程序运行

1、程序操作界面,选择报表生成的日期,进行先生成,后查看

2、打开iFIx组态软件生成的数据文件report.mdb文件,可看到具体对应的数据

 3、点击程序操作界面报表查看后,生成的数据报表.xls文件,可以看到已将上述2中的数据写入EXCEL表格中。

 4、注意

      在调试过程中,遇到根据模板无法写入到指定Sheet表格中的情况,原因是文件中有隐藏的Sheet,并且该隐藏的Sheet索引顺序在需要写入的Sheet表格前面,需要调整隐藏文件的索引顺序即可。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值