为便于实时采集并保存数据到excel,需要调用QAxObject
首先定义变量excel workbooks workbook worksheets worksheet range 等;
#ifndef EXCEL_H
#define EXCEL_H
//添加头文件
#include <QVariant>
#include <ActiveQt/QAxObject>//Excel
#include <QDebug>//debug输出
#include <QDir>//保存路径
class Excel
{
public:
Excel();
void SetTitle1(const QVariant &var,const QVariant &value);
QVariant getValue(const QVariant &var);
bool openExcel();
bool newExcel();
void exitExcel();
void autoFit(const QVariant &var);
bool fileSaveAs(QString path);
bool readXlsFile(QString path, QList<QStringList> &res);
void setPath(QString path);
void convertToColName(int data, QString &res);
QString to26AlphabetString(int data);
private:
QString filePath;
QAxObject *excel;
QAxObject *workbooks;
QAxObject *workbook;
QAxObject *worksheets;
QAxObject *worksheet;
QAxObject *range;
QAxObject *interior;
QAxObject *cell;
QAxObject *font;
int currRow;
};
#endif // EXCEL_H
定义新建文件、打开文件、关闭文件等函数;
注意:异常情况下需要关闭并退出(此时要delete excel 才能退出进程!)
#include "excel.h"
#include <QFileInfo>
void Excel::SetTitle1(const QVariant &var,const QVariant &value)
{
if(worksheet == nullptr)
{
qDebug()<<"查询 worksheet 失败!";
return;
}
range=worksheet->querySubObject("Range(const QString&)",var);
if(range == nullptr)
{
qDebug()<<"查询 range 失败!";
return;
}
//range->setProperty("MergeCells",true);
range->setProperty("NumberFormatLocal", "@");
range->setProperty("Value",value);
range->setProperty("HorizontalAlignment",-4108);
}
void Excel::autoFit(const QVariant &var)
{
if(worksheet == nullptr)
{
qDebug()<<"查询 worksheet 失败!";
return;
}
range=worksheet->querySubObject("Range(const QString&)",var);
if(range == nullptr)
{
qDebug()<<"查询 range 失败!";
return;
}
QAxObject * cells = range->querySubObject("Columns");
cells->dynamicCall("AutoFit");
delete cells;
}
QVariant Excel::getValue(const QVariant &var)
{
if(worksheet == nullptr)
{
qDebug()<<"查询 worksheet 失败!";
return "";
}
range=worksheet->querySubObject("Range(const QString&)",var);
return range->property("Value");
}
bool Excel::newExcel()
{
excel = new QAxObject("Excel.Application");
if (!excel)
{
qDebug()<<"创建Excel失败!";
return false;
}
excel->dynamicCall("SetVisible(bool Visible)", false); //是否可视化excel
excel->dynamicCall("SetUserControl(bool UserControl)", false); //是否用户可操作
//excel->setProperty("DisplayAlerts", true); //是否弹出警告窗口
workbooks = excel->querySubObject("WorkBooks"); //获取工作簿集合
if(workbooks == nullptr)
{
qDebug()<<"查询 workbooks 失败!";
exitExcel();
return false;
}
workbooks->dynamicCall("Add"); //新建一个工作簿
workbook = excel->querySubObject("ActiveWorkBook"); //获取当前工作簿
if(workbook == nullptr)
{
qDebug()<<"查询 ActiveWorkBook 失败!";
exitExcel();
return false;
}
worksheets = workbook->querySubObject("Sheets"); //获取工作表格集合
if(worksheets == nullptr)
{
qDebug()<<"查询 worksheets 失败!";
exitExcel();
return false;
}
worksheet = worksheets->querySubObject("Item(int)", 1); //获取当前工作表格1,即sheet1
if(worksheet == nullptr)
{
qDebug()<<"查询 worksheet 失败!";
exitExcel();
return false;
}
worksheet->setProperty("Name","数据"); //修改sheet名称
return true;
}
bool Excel::openExcel()
{
excel = new QAxObject("Excel.Application");
if (!excel)
{
qDebug()<<"打开Excel失败!";
return false;
}
excel->dynamicCall("SetVisible(bool Visible)", false); //是否可视化excel
excel->dynamicCall("SetUserControl(bool UserControl)", false); //是否用户可操作
workbooks = excel->querySubObject("WorkBooks"); //获取工作簿集合
if(workbooks == nullptr)
{
qDebug()<<"查询 workbooks 失败!";
exitExcel();
return false;
}
workbooks->dynamicCall("Open(const QString&)", filePath);
//excel->setProperty("DisplayAlerts", true); //是否弹出警告窗口
workbook = excel->querySubObject("ActiveWorkBook"); //获取当前工作簿
if(workbook == nullptr)
{
qDebug()<<"查询 workbook 失败!";
exitExcel();
return false;
}
worksheets = workbook->querySubObject("Sheets"); //获取工作表格集合
if(worksheets == nullptr)
{
qDebug()<<"查询 worksheets 失败!";
exitExcel();
return false;
}
worksheet = workbook->querySubObject("Worksheets(int)", 1);
if(worksheet == nullptr)
{
qDebug()<<"查询 worksheet 失败!";
exitExcel();
return false;
}
return true;
}
void Excel::exitExcel()
{
if(excel != nullptr && workbooks != nullptr)
workbooks->dynamicCall("Close()");
excel->dynamicCall("Quit()");//退出
delete excel;
}
void Excel::setPath(QString path)
{
filePath = path;
}
bool Excel::fileSaveAs(QString path)
{
if(!openExcel())
return false;
workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(path));
exitExcel();
return true;
}
bool Excel::readXlsFile(QString path, QList<QStringList > &res)
{
res.clear();
if(!openExcel())
return false;
if(worksheet->property("Name").toString() != "数据")
{
qDebug()<<"sheet name 不等于 数据!";
exitExcel();
return false;
}
//快速读取数据
QString checkRange = "A1:A7";
QAxObject *Data = worksheet->querySubObject("Range(QString)", checkRange);
QVariant DataQVariant = Data->property("Value");
QVariantList DataList = DataQVariant.toList();
QStringList list = {"任务单号","检验人员","检验日期","从机数量","模块数量","从机地址","相应数量"};
for(int i = 0; i < DataList.size(); i++)
{
QStringList t = DataList[i].toStringList();
if(t[0] != list[i])
{
exitExcel();
return false;
}
}
int moduleNum = getValue("B5:B5").toInt();
if(moduleNum <= 0)
{
qDebug() << "模块数量 " << moduleNum << endl;
exitExcel();
return false;
}
QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
if(usedrange == nullptr)
{
qDebug()<<"readXlsFile 查询 usedrange 失败!";
exitExcel();
return false;
}
QAxObject * rows = usedrange->querySubObject("Rows");
QAxObject * columns = usedrange->querySubObject("Columns");
int intRows = rows->property("Count").toInt();
int intCols = columns->property("Count").toInt();
qDebug() << "读取 " + filePath +" 行数:"<< intRows;
qDebug() << " xls列数:"<<intCols;
if(intRows <= 8)
{
exitExcel();
return false;//只有8行 无数据
}
// 批量载入数据
QString rangStr;
convertToColName(moduleNum+1,rangStr);
QString Range = "A8:" + rangStr + QString::number(intRows);
QAxObject *allEnvData = worksheet->querySubObject("Range(QString)", Range);
QVariant allEnvDataQVariant = allEnvData->property("Value");
QVariantList allEnvDataList = allEnvDataQVariant.toList();
for(int i=0; i < allEnvDataList.size(); i++)
{
QStringList allEnvDataList_i = allEnvDataList[i].toStringList() ;
//qDebug()<< allEnvDataList_i << endl;
res.push_back(allEnvDataList_i);
}
exitExcel();
return true;
}
void Excel::convertToColName(int data, QString &res)
{
Q_ASSERT(data>0 && data<65535);
int tempData = data / 26;
if(tempData > 0)
{
int mode = data % 26;
convertToColName(mode,res);
convertToColName(tempData,res);
}
else
{
res=(to26AlphabetString(data)+res);
}
}
QString Excel::to26AlphabetString(int data)
{
QChar ch = data + 0x40;//A对应0x41
return QString(ch);
}
读写数据类型时QVariant(其他数据类型也可转成QVariant,例如QVariantList)不断包装。
且只能按照行进行打包!
range->setProperty("NumberFormatLocal", "@");
设置显示原始数据格式!
Qt版本为5.13