Qt 导出Excel表(QAxObject类)
在Qt软件开发过程中,有时候软件导出数据,生成一个Excel表,在网上查阅了相关资料,了解了实现的原理,下面进行整理。
日期 | 作者 | 版本 |
---|---|---|
2021年07月05日 | Mister H | V1.0 |
模块添加:在.pro文件 追加 Qt += axcontainer
QAxObject 类可以导出数据到excel,这种方法比较依赖于电脑上打开excel的软件。
实例代码如下
//建立Excel对象
QAxObject *excel = new QAxObject("Excel.Application");
//如果为了看自己的程序到底怎样工作,可以设置为true
excel->dynamicCall("SetVisible(bool)", false);
excel->setProperty("Visible", false);
QAxObject *workbooks = excel->querySubObject("WorkBooks");
workbooks->dynamicCall("Add");
//获取当前工作簿
QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
//获取工作表集合
QAxObject *worksheets = workbook->querySubObject("Sheets");
//获取工作表集合的工作表1,即sheet1
QAxObject *worksheet = worksheets->querySubObject("Item(int)",1);
//获取图片显示路径
QAxObject * shapes = worksheet->querySubObject("Shapes");
// QString picDir=QString("F:\\swpu\\qt\\qt_excel\\tool\\1.png");
QString picDir = QDir::currentPath().append("/1.jpg");
//qDebug()<<"picDir:"<<picDir;
//设置图片大小
shapes->dynamicCall("AddPicture( QString&, bool, bool, double, double, double, double)",picDir,true,true,0,0,360,196);
//连接Excel控件
if(excel->setControl("Excel.Application"))
{
//不显示当前窗体
excel->dynamicCall("SetVisible (bool Visible)","false");
//不显示任何警告消息,如果为true那么在关闭是会出现类似"文件已修改,是否保存"的提示
// excel->setProperty("DisplayAlerts", false);
//获取工作簿集合
// QAxObject *workBooks = excel->querySubObject("WorkBooks");
//新建一个工作簿
workbooks->dynamicCall("Add");
//获取当前工作簿
// QAxObject *workBook = excel->querySubObject("ActiveWorkBook");
//获取第一个工作表(后面的参数代表的是第几张工作表)
QAxObject *workSheet = workbook->querySubObject("Worksheets(int)", 1);
int colCount = tableWidget->columnCount();
int rowCount = tableWidget->rowCount();
QAxObject *cell, *col;
//标题行
cell = workSheet->querySubObject("Cells(int, int)", 1 + 17 , 1);
cell->dynamicCall("SetValue(const QString&)", "Qt 导入Excel");
cell->querySubObject("Font")->setProperty("Size", 18);
//调整行高
workSheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
//合并标题行
QString cellTitle;
cellTitle.append("A18:");
cellTitle.append(QChar( colCount - 1 + 'A'));
cellTitle.append(QString::number(1));
QAxObject *range = workSheet->querySubObject("Range(const QString&)", cellTitle);
//range->setProperty("RowHeight", 20);
//列标题
for (int i = 0; i < colCount; i++)
{
QString columnName;
columnName.append(QChar(i + 'A'));
columnName.append(":");
columnName.append(QChar(i + 'A'));
col = workSheet->querySubObject("Columns(const QString&)", columnName);
col->setProperty("ColumnWidth", tableWidget->columnWidth(i)/6+10);
cell = workSheet->querySubObject("Cells(int, int)", 2 + 17, i+1);
columnName = tableWidget->horizontalHeaderItem(i)->text();
//qDebug() << "columnName = " << columnName;
cell->dynamicCall("SetValue(const QString&)", columnName);
cell->querySubObject("Font")->setProperty("Bold", true);
cell->querySubObject("Interior")->setProperty("Color", QColor(191, 191, 191));
cell->setProperty("HorizontalAlignment", -4108);
cell->setProperty("VertivcalAlignment", -4108);
}
//处理数据
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
if(tableWidget->item(i, j) == NULL)
{
//qDebug() << "tableWidget2->item(i, j)为NULL ";
continue ;
}
workSheet->querySubObject("Cells(int, int)", i + 3 + 17, j + 1)->dynamicCall(
"SetValue(const QString&)", tableWidget->item(i, j)? tableWidget->item(i, j)->text() : "");
//qDebug() << "tableWidget->item(i, j)->text() = " << tableWidget->item(i, j)->text();
}
}
//画框线
QString l_range;
l_range.append(QString("A%1:").arg(rowCount + 19)); //设置最后的行单元
l_range.append(colCount -1 + 'A'); //设置 最上方 A B C D E F
l_range.append(QString::number(19)); //设置初始行单元
range = workSheet->querySubObject("Range(const QString&)", l_range);
range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
//调整数据区行高
QString rowsName;
rowsName.append("20:");
rowsName.append(QString::number(tableWidget->rowCount() + 2));
range = workSheet->querySubObject("Range(const QString&)", rowsName);
range->setProperty("RowHeight", 20);
//设置单元格背景颜色
QAxObject* cell1=workSheet->querySubObject("Cells(int,int)",1, 1);
QAxObject* border1 = cell1->querySubObject("Interior");
border1->setProperty("Color",tableWidget2->item(1, 1)->backgroundColor());