加载头文件
#include<QAxObject>
在qt project settings中加入ActiveQtC 模块
1,设置execl文件标题
方式一:
通过单元格逐个设置标题元素;
QFileInfo fileInfo("test.xlsx");
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;
//设置标题
QString A = "A" + QString::number(1);//设置要操作的单元格A1
QString B = "B" + QString::number(1); //B1
QString C = "C" + QString::number(1); //C1
cellA = m_workSheet->querySubObject("Range(QVariant, QVariant)", A);//获取单元格
cellB = m_workSheet->querySubObject("Range(QVariant, QVariant)", B);
cellC = m_workSheet->querySubObject("Range(QVariant, QVariant)", C);
cellA->dynamicCall("SetValue(const QVariant&)", QVariant("文件名字"));//设置单元格的值
cellB->dynamicCall("SetValue(const QVariant&)", QVariant("文件类型"));
cellC->dynamicCall("SetValue(const QVariant&)", QVariant("文件大小"));
QAxObject *range = m_workSheet->querySubObject("Range(const Qvariant&)", QVariant(QString("A1:Q1")));
if (range == NULL)
{
return false;
}
// 设置自动适配宽度
range->setProperty("VerticalAlignment", -4108); // 水平居中
range->setProperty("HorizontalAlignment", -4108); // 垂直居中
range->setProperty("ColumnWidth", 15);
if (m_workSheets == NULL)
{
return false;
}
方式二:
通过QTableView关联模型列数设置,
首先,标题元素写入到文件title.txt中,title.txt内容格式如下:
文件名字
文件类型
文件大小
读取文件title.txt中内容到QStringList中,循环设置标题,
代码如下:
QStringList tmp_list;
QFile file("title.txt");
if (!file.open(QIODevice::ReadOnly))
{
LOG(ERROR) << "打开标题文本文件失败";
return false;
}
QTextStream stream(&file);
stream.setCodec("UTF-8");
while (!stream.atEnd())
{
QString line = stream.readLine();
tmp_list << line;
}
file.close();
for (int j = 1;j< ui.tableView->model()->columnCount() -1 ;j++)
{
m_workSheet->querySubObject("Cells(int, int)", 1, j)->dynamicCall("SetValue(const QVariant&)", tmp_list.at(j - 1));
}
2 ,写数据
同理,写数据也可以逐个单元格写或通过关联模型循环写入execl文件,元素数据比较多的情况下方式二比较方便;
方式一:
QAxObject *cellA, *cellB, *cellC;
//设置标题 cellrow是execl文件行号,标题是第1行,数据从第2行开始写
QString A = "A" + QString::number(cellrow);//设置要操作的单元格A1
QString B = "B" + QString::number(cellrow);
QString C = "C" + 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);
cellA->dynamicCall("SetValue(const QVariant&)", QVariant(record.value("package_serial").toString()));//设置单元格的值
cellB->dynamicCall("SetValue(const QVariant&)", QVariant(record.value("sender").toString()));
cellC->dynamicCall("SetValue(const QVariant&)", QVariant(record.value("receiver").toString()));
方式二:
int i = cellrow;//从第2行开始写,cellrow是行号
for (int j = 1; j <= ui.tableView->model()->columnCount() -1 ; j++)
{
m_workSheet->querySubObject("Cells(int, int)", i, j)->dynamicCall("SetValue(const QVariant&)", ui.tableView->model()->index(i - 2, j).data().toString());
}
3,保存文件
if (m_excel == NULL ||
m_workBooks == NULL ||
m_workBook == NULL)
{
return false;
}
QFileInfo fileInfo("test.xlsx");
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)");
if(m_workSheet)
{
delete m_workSheet;
m_workSheet = NULL;
}
if(m_workSheets)
{
delete m_workSheets;
m_workSheets = NULL;
}
if(m_workBook)
{
delete m_workBook;
m_workBook = NULL;
}
if(m_workBooks)
{
delete m_workBooks;
m_workBooks = NULL;
}
if(m_excel)
{
delete m_excel;
m_excel = NULL;
}