从QTableView中导出数据到excel（一）

最新推荐文章于 2024-01-09 11:13:36 发布

吴小白白白

最新推荐文章于 2024-01-09 11:13:36 发布

阅读量2.8k

点赞数 3

分类专栏： Qt

本文链接：https://blog.csdn.net/zuoweijie_/article/details/102956266

版权

Qt 专栏收录该内容

23 篇文章 6 订阅

订阅专栏

void HistoricalData::on_pbnExportData_clicked()
{
    QString title = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    QString fileName = QFileDialog::getSaveFileName(this, tr("保存"), "", tr("Excel 文件(*.xls *.xlsx)"));
    if(fileName != ""){
        QAxObject *excel = new QAxObject;
        if(excel->setControl("Excel.Application")){
            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 i, j;
            int colcount = ui->tableView->model()->columnCount();
            int rowcount = ui->tableView->model()->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);//xlCenter
            range->setProperty("VerticalAlignment", -4108);//xlCenter
            //列标题
            for(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", ui->tableView->columnWidth(i)/6);
                cell=worksheet->querySubObject("Cells(int,int)", 2, i+1);
                //QTableView 获取表格头部文字信息
                columnName = ui->tableView->model()->headerData(i, Qt::Horizontal, Qt::DisplayRole).toString();
                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);//xlCenter
                cell->setProperty("VerticalAlignment", -4108);//xlCenter
            }

            //数据区
            //QTableView 获取表格数据部分
            for(i=0; i<rowcount; i++) //行数
            {
                for (j=0; j<colcount; j++)   //列数
                {
                    QModelIndex index = ui->tableView->model()->index(i, j);
                    QString strdata = ui->tableView->model()->data(index).toString();
                    worksheet->querySubObject("Cells(int,int)", i+3, j+1)->dynamicCall("SetValue(const QString&)", strdata);
                }
            }

            //画框线
            QString lrange;
            lrange.append("A2:");
            lrange.append(colcount - 1 + 'A');
            lrange.append(QString::number(rowcount + 2));
            range = worksheet->querySubObject("Range(const QString&)", lrange);
            range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
            range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
            //调整数据区行高
            QString rowsName;
            rowsName.append("2:");
            rowsName.append(QString::number(rowcount + 2));
            range = worksheet->querySubObject("Range(const QString&)", rowsName);
            range->setProperty("RowHeight", 20);
            workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName));//保存至fileName
            workbook->dynamicCall("Close()");//关闭工作簿
            excel->dynamicCall("Quit()");//关闭excel
            delete excel;
            excel = nullptr;
            if (QMessageBox::question(nullptr, tr("完成"), tr("文件已经导出，是否现在打开？"), QMessageBox::Yes|QMessageBox::No) == QMessageBox::Yes)
            {
                QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(fileName)));
            }
        }
        else
        {
            QMessageBox::warning(nullptr, tr("错误"), tr("未能创建 Excel 对象，请安装 Microsoft Excel。"), QMessageBox::Apply);
        }
    }
}

从QTableView中导出数据到excel，导出数据很慢。