添加头文件
#include<QAxObject>
在.pro文件中添加:
QT += axcontainer
然后就是对EXCEL的操作
/** 打开/新建一个Excel表格 并设置标题 **/
bool SaveDataToExcelThread::open(QString excelPath)
{
m_saveFilePath = excelPath;
QFileInfo fileInfo(excelPath);
m_excel = new QAxObject("Excel.Application");
m_excel->setProperty("Visible", false);
m_excel->setProperty("DisplayAlerts", false); // 在save和save as时,不弹窗提示
m_workBooks = m_excel->querySubObject("WorkBooks");//获取工作簿集合
if(!fileInfo.exists()){
//!!!新建工作簿
m_workBooks->querySubObject("Add");
} else {
//!!!打开已存在的工作簿
m_workBooks->querySubObject("Open (const QString&)", QDir::toNativeSeparators(excelPath));
}
m_workBook = m_excel->querySubObject("ActiveWorkBook");
if (m_workBook == nullptr)
return false;
//!!!获取表页对象
m_workSheets = m_workBook->querySubObject("WorkSheets");
m_workSheet = m_workSheets->querySubObject("Item(int)", 1);
QAxObject *cellA,*cellB,*cellC,*cellD,*cellE,*cellF,*cellG,*cellH,*cellI,*cellJ,*cellK;
//设置标题
QString A="A"+QString::number(1);//设置要操作的单元格,如A1
QString B="B"+QString::number(1);
QString C="C"+QString::number(1);
QString D="D"+QString::number(1);
QString E="E"+QString::number(1);
QString F="F"+QString::number(1);
QString G="G"+QString::number(1);
QString H="H"+QString::number(1);
QString I="I"+QString::number(1);
QString J="J"+QString::number(1);
QString K="K"+QString::number(1);
cellA = m_workSheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
cellB = m_workSheet->querySubObject("Range(QVariant, QVariant)",B);
cellC = m_workSheet->querySubObject("Range(QVariant, QVariant)",C);
cellD = m_workSheet->querySubObject("Range(QVariant, QVariant)",D);
cellE = m_workSheet->querySubObject("Range(QVariant, QVariant)",E);//获取单元格
cellF = m_workSheet->querySubObject("Range(QVariant, QVariant)",F);
cellG = m_workSheet->querySubObject("Range(QVariant, QVariant)",G);
cellH = m_workSheet->querySubObject("Range(QVariant, QVariant)",H);
cellI = m_workSheet->querySubObject("Range(QVariant, QVariant)",I);//获取单元格
cellJ = m_workSheet->querySubObject("Range(QVariant, QVariant)",J);
cellK = m_workSheet->querySubObject("Range(QVariant, QVariant)",K);
cellA->dynamicCall("SetValue(const QVariant&)",QVariant("电流环Id给定"));//设置单元格的值
cellB->dynamicCall("SetValue(const QVariant&)",QVariant("电流环Id反馈"));
cellC->dynamicCall("SetValue(const QVariant&)",QVariant("电流环Iq给定"));
cellD->dynamicCall("SetValue(const QVariant&)",QVariant("电流环Iq反馈"));
cellE->dynamicCall("SetValue(const QVariant&)",QVariant("速度环给定"));//设置单元格的值
cellF->dynamicCall("SetValue(const QVariant&)",QVariant("速度环反馈"));
cellG->dynamicCall("SetValue(const QVariant&)",QVariant("位置环给定"));
cellH->dynamicCall("SetValue(const QVariant&)",QVariant("位置环反馈"));
cellI->dynamicCall("SetValue(const QVariant&)",QVariant("方位取差量"));//设置单元格的值
cellJ->dynamicCall("SetValue(const QVariant&)",QVariant("俯仰取差量"));
cellK->dynamicCall("SetValue(const QVariant&)",QVariant("再生速度"));
QAxObject *range = m_workSheet->querySubObject("Range(const Qvariant&)", QVariant(QString("A1:K1")));
if(range == NULL)
return false;
// 设置自动适配宽度
range->setProperty("VerticalAlignment", -4108); // 水平居中
range->setProperty("HorizontalAlignment", -4108); // 垂直居中
range->setProperty("ColumnWidth", 15);
if(m_workSheets == NULL)
return false;
emit openSucced();
return true;
}
保存并退出Excel:
//保存并退出Excel
bool SaveDataToExcelThread::save()
{
if(m_excel == NULL || m_workBooks == NULL || m_workBook == NULL)
return false;
/** 保存文件 **/
QFileInfo fileInfo(m_saveFilePath);
if(!fileInfo.exists())
m_workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_saveFilePath));
else
m_workBook->dynamicCall("Save()");
/** 关闭并退出文件 **/
m_workBooks->dynamicCall("Close()");
m_excel->dynamicCall("Quit(void)");
delete m_workSheet;
m_workSheet = NULL;
delete m_workSheets;
m_workSheets = NULL;
delete m_workBook;
m_workBook = NULL;
delete m_workBooks;
m_workBooks = NULL;
delete m_excel;
m_excel = NULL;
return true;
}
写入数据:
//写入数据,写入到cellrow行
bool SaveDataToExcelThread::writeExcelData(int cellrow)
{
QAxObject *cellA,*cellB,*cellC,*cellD,*cellE,*cellF,*cellG,*cellH,*cellI,*cellJ,*cellK;
//设置标题
QString A="A"+QString::number(cellrow);//设置要操作的单元格,如A1
QString B="B"+QString::number(cellrow);
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 G="G"+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);
cellA = m_workSheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
cellB = m_workSheet->querySubObject("Range(QVariant, QVariant)",B);
cellC = m_workSheet->querySubObject("Range(QVariant, QVariant)",C);
cellD = m_workSheet->querySubObject("Range(QVariant, QVariant)",D);
cellE = m_workSheet->querySubObject("Range(QVariant, QVariant)",E);//获取单元格
cellF = m_workSheet->querySubObject("Range(QVariant, QVariant)",F);
cellG = m_workSheet->querySubObject("Range(QVariant, QVariant)",G);
cellH = m_workSheet->querySubObject("Range(QVariant, QVariant)",H);
cellI = m_workSheet->querySubObject("Range(QVariant, QVariant)",I);//获取单元格
cellJ = m_workSheet->querySubObject("Range(QVariant, QVariant)",J);
cellK = m_workSheet->querySubObject("Range(QVariant, QVariant)",K);
cellA->dynamicCall("SetValue(const QVariant&)",QVariant(m_currentLoopSetting_Id));//设置单元格的值
cellB->dynamicCall("SetValue(const QVariant&)",QVariant(m_currentLoopFeedback_Id));
cellC->dynamicCall("SetValue(const QVariant&)",QVariant(m_currentLoopSetting_Iq));
cellD->dynamicCall("SetValue(const QVariant&)",QVariant(m_currentLoopFeedback_Iq));
cellE->dynamicCall("SetValue(const QVariant&)",QVariant(m_speedLoopSetting));//设置单元格的值
cellF->dynamicCall("SetValue(const QVariant&)",QVariant(m_speedLoopFeedback));
cellG->dynamicCall("SetValue(const QVariant&)",QVariant(m_positionLoopSetting));
cellH->dynamicCall("SetValue(const QVariant&)",QVariant(m_positionLoopFeedback));
cellI->dynamicCall("SetValue(const QVariant&)",QVariant(m_azimuthDeviation));//设置单元格的值
cellJ->dynamicCall("SetValue(const QVariant&)",QVariant(m_pitchDeviation));
cellK->dynamicCall("SetValue(const QVariant&)",QVariant(m_regenerationSpeed));
return true;
}
执行:
void SaveDataToExcelThread::run()
{
open(m_saveFilePath); // 如果是重写QThread::run函数,初始化一定要放在run函数中,因为除了run函数其他代码都是运行在主线程中的,如果放其他地方,初始化会失败
while (m_running)
{
// 写入第m_numID行
writeExcelData(m_numID);
}
save();
}
变量:
QString m_saveFilePath;
QAxObject *m_excel;
QAxObject *m_workBook; // 当前工作簿
QAxObject *m_workBooks; // 工作簿集合
QAxObject *m_workSheet; //获取工作表集合
QAxObject *m_workSheets;//获取工作表集合的工作表
// 电流环Id给定
double m_currentLoopSetting_Id = 0.00;
// 电流环Id反馈
double m_currentLoopFeedback_Id = 0.00;
// 电流环Iq给定
double m_currentLoopSetting_Iq = 0.00;
// 电流环Iq反馈
double m_currentLoopFeedback_Iq = 0.00;
// 速度环给定
double m_speedLoopSetting = 0.00;
// 速度环反馈
double m_speedLoopFeedback = 0.00;
// 位置环给定
double m_positionLoopSetting = 0.00;
// 位置环反馈
double m_positionLoopFeedback = 0.00;
// 方位取差量
double m_azimuthDeviation = 0.00;
// 俯仰取差量
double m_pitchDeviation = 0.00;
// 再生速度
double m_regenerationSpeed = 0.00;
// 插入第几行
int m_numID = 2;