文章描述了一种使用iFIX组态软件生成report.MDB文件并利用用户脚本定时存储数据的过程。接着，介绍了如何在本地设置ODBC数据源以连接Access数据库。此外，还详细说明了如何使用Qt程序通过ODBC连接数据库，检索特定时间的数据，并将数据写入Excel模板文件生成报表。
一、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表格前面，需要调整隐藏文件的索引顺序即可。
扫一扫
3301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
