C++ QSqlQueryModel::columnCount方法代码示例

  //Ausleitung als csv-Datei
void MainWindow::on_pushButton_clicked()
{
    QSqlQueryModel *model = (QSqlQueryModel*)ui->listView->model();
    QSqlRecord record = model->record(ui->listView->currentIndex().row());
    QSqlField field_ID = record.field("ID");

    DBank con;
    con.con_open();

    QSqlQuery query(con.myDB);
    query.prepare("SELECT t.Vorname as Vorname, t.Nachname as Nachname, time(t.Endzeit-e.Startzeit, \"unixepoch\") from TEILNEHMER as t JOIN EVENT as e ON e.ID == t.EVENT_ID where e.ID = '"+field_ID.value().toString()+"' ORDER BY t.Endzeit ASC");

    if(query.exec())
    {
       QSqlQueryModel *model=new QSqlQueryModel();
        model->setQuery(query);

        QString DB_Inhalt;
        int rows=model->rowCount();
        int columns=model->columnCount();

        for (int i = 0; i < rows; i++)
        {
            for (int j = 0; j < columns; j++)
            {
                DB_Inhalt += model->data(model->index(i,j)).toString();
                DB_Inhalt += "; ";
            }
            DB_Inhalt += "\n";
        }

        QString filename = QFileDialog::getSaveFileName(this,"Speichern unter","C://","All files (*.*);;ExcelFile(*.csv)");

        QFile csvfile(filename);
        if(csvfile.open(QIODevice::WriteOnly|QIODevice::Truncate))
        {
            QTextStream out(&csvfile);
            out<<DB_Inhalt;
        }
        csvfile.close();
    }
}

void QSqlQueryModel_snippets()
{
    {
//! [16]
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("SELECT name, salary FROM employee");
    model->setHeaderData(0, Qt::Horizontal, tr("Name"));
    model->setHeaderData(1, Qt::Horizontal, tr("Salary"));

//! [17]
    QTableView *view = new QTableView;
//! [17] //! [18]
    view->setModel(model);
//! [18] //! [19]
    view->show();
//! [16] //! [19] //! [20]
    view->setEditTriggers(QAbstractItemView::NoEditTriggers);
//! [20]
    }

//! [21]
    QSqlQueryModel model;
    model.setQuery("SELECT * FROM employee");
    int salary = model.record(4).value("salary").toInt();
//! [21]
    Q_UNUSED(salary);

    {
//! [22]
    int salary = model.data(model.index(4, 2)).toInt();
//! [22]
    Q_UNUSED(salary);
    }

    for (int row = 0; row < model.rowCount(); ++row) {
        for (int col = 0; col < model.columnCount(); ++col) {
            qDebug() << model.data(model.index(row, col));
        }
    }
}

void SongsQueryWideget::export_excel_clicked()
{
    ///建立临时表映射
    ///
    setCursor(Qt::WaitCursor);
    QSqlQueryModel *sqlquery = new QSqlQueryModel(this);
    QSqlQuery _query;
    MediaPagingQuery argu;
    getQueryCondition(argu);
    if(!_sql->queryMedia_All(argu, _query))
        return;
    sqlquery->setQuery(_query);
    int rows = sqlquery->rowCount();
    int columns = sqlquery->columnCount();
    setCursor(Qt::ArrowCursor);
    QString desktopPath = QProcessEnvironment::systemEnvironment().value("USERPROFILE")+"\\Desktop";
    QString fileName = QFileDialog::getSaveFileName(tableView_songsQuery, "保存",
                                                    //QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),
                                                    desktopPath,
                                                    "Excel 文件(*.xls *.xlsx)");
    QProgressDialog *progress = new QProgressDialog(this);
    progress->setLabelText("正在导出表格数据……");
    progress->setRange(0, rows);
    progress->setModal(true);
    progress->setCancelButtonText("取消");
//    progress->setMinimumSize(300, 50);
    progress->setMinimumWidth(400);
    progress->setAutoClose(true);
    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);
//            //数据区
            for(int i=0; i<rows; i++)
            {
                for (int j=0;j<columns; j++)
                {
                    QModelIndex index = sqlquery->index(i, j);
                    QString text = index.data().toString();
//                    table->item(i,j)?table->item(i,j)->text():""
                    worksheet->querySubObject("Cells(int,int)", i+1, j+1)->dynamicCall("SetValue(const QString&)", text);
                }

                QString label_text = QString("正在导出%1行……").arg(i+1);
                progress->setLabelText(label_text);
                progress->setValue(i+1);
                if(progress->wasCanceled())
                {
                    break;
                }
            }

            workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至fileName
            workbook->dynamicCall("Close()");//关闭工作簿
            //关闭excel
            excel->dynamicCall("Quit (void)");
            delete excel;
            excel=NULL;
            QMessageBox box(QMessageBox::Question, "完成", "文件已经导出，是否现在打开？");
            box.setStandardButtons(QMessageBox::Yes|QMessageBox::No);
            box.setButtonText(QMessageBox::Yes, "确定(&Q)");
            box.setButtonText(QMessageBox::No, "取消(&C)");
            if(box.exec() == QMessageBox::Yes)
//            if (QMessageBox::question(NULL,"完成","文件已经导出，是否现在打开？",QMessageBox::Yes|QMessageBox::No)==QMessageBox::Yes)
            {
                QString local_path = QString("file:///") + fileName;
                QDesktopServices::openUrl(QUrl(local_path, QUrl::TolerantMode)); //QDir::toNativeSeparators(fileName)));

            }
        }
        else
        {
            QMessageBox::warning(NULL,"错误","未能创建 Excel 对象，请安装 Microsoft Excel。",QMessageBox::Apply);
        }
    }
//    progress->close();
}