表格导出为Excel
- 注意:演示所用到的软件为Qt5.14.2,编译器为MinGW 64-bit,电脑必须装有office
- 所用的类为 QAxObject,QAxObject可以实例化为一个空对象,使用它应该封装的COM对象的名称,或者使用一个指向表示现有COM对象的IUnknown的指针。如果COM对象实现了IDispatch接口,则该对象的属性、方法和事件将作为Qt属性、槽和信号可用。基类QAxBase提供了通过IUnknown指针直接访问COM对象的API。
简而言之,可以通过QAxObject 来读取、修改Excel表格 - 在.pro中添加 QT += axcontainer
- 添加头文件
#include <QFileDialog>
#include <QDesktopServices>
话不多说,代码搞起
QTableWidget导出为Excel
void MainWindow::WidgetExcalByHtml(QTableWidget *tableWidget, QString &title)
{
QString fileName = QFileDialog::getSaveFileName(tableWidget, "保存", QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation), "Excel 文件(*.xls *.xlsx)");
if(fileName != "")
{
QAxObject *excel = new QAxObject;
if(excel->setControl("Excel.Application")) //连接Excel控件
{
excel->dynamicCall("SetVisible (bool Visible)","false"); //不显示当前窗体
excel->setProperty("DisplayAlerts", false); //不显示任何警告消息,如果为true那么在关闭是会出现类似"文件已修改,是否保存"的提示
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, 1);
cell->dynamicCall("SetValue(const QString&)", title);
cell->querySubObject("Font")->setProperty("Size", 18);
//调整行高
workSheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
//合并标题行
QString cellTitle;
cellTitle.append("A1:");
cellTitle.append(QChar(colCount - 1 + 'A'));
cellTitle.append(QString::number(1));
QAxObject *range = workSheet->querySubObject("Range(const QString&)", cellTitle);
range->setProperty("WrapText", true);
range->setProperty("MergeCells", true);
range->setProperty("HorizontalAlignment", -4108);
range->setProperty("VertivcalAlignment", -4108);
//列标题
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);
cell = workSheet->querySubObject("Cells(int, int)", 2, i+1);
columnName = tableWidget->horizontalHeaderItem(i)->text();
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++)
{
workSheet->querySubObject("Cells(int, int)", i +