//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 EmployeeDataModel::readAllRecords() { QSqlQueryModel model; model.setQuery(QLatin1String("SELECT * FROM employee")); int colunmCount = model.columnCount(); int rowCount = model.rowCount(); QSqlRecord rec = model.record(); // int recCount = rec.count(); for (int i=0; i<rowCount; i++) { qDebug() << "Record : " << i; rec = model.record(i); for (int j=0; j<colunmCount; j++) { QString value = rec.value(j).toString(); qDebug() << "ColumnCount : " << j << " - Value = " << value; } } }
void ItemLineEdit::sHandleCompleter() { if (!hasFocus()) return; QString stripped = text().trimmed().toUpper(); if (stripped.isEmpty()) return; int width = 0; QSqlQueryModel* model = static_cast<QSqlQueryModel *>(_completer->model()); QTreeView * view = static_cast<QTreeView *>(_completer->popup()); _parsed = true; XSqlQuery numQ; if (_useQuery) { numQ.prepare(QString("SELECT * FROM (%1) data WHERE (item_number ~* :number) LIMIT 10") .arg(QString(_sql)).remove(";")); numQ.bindValue(":number", "^" + stripped); } else { QString pre( "SELECT DISTINCT item_id, item_number, " "(item_descrip1 || ' ' || item_descrip2) AS itemdescrip, " "item_upccode AS description " ); QStringList clauses; clauses = _extraClauses; clauses << "(item_number ~* :searchString OR item_upccode ~* :searchString)"; numQ.prepare(buildItemLineEditQuery(pre, clauses, QString::null, _type).replace(";"," ORDER BY item_number LIMIT 10;")); numQ.bindValue(":searchString", QString(text().trimmed().toUpper()).prepend("^")); } numQ.exec(); if (numQ.first()) { int numberCol = numQ.record().indexOf("item_number"); int descripCol = numQ.record().indexOf("itemdescrip"); model->setQuery(numQ); _completer->setCompletionPrefix(stripped); for (int i = 0; i < model->columnCount(); i++) { if ( (i == numberCol) || (i == descripCol) ) { view->resizeColumnToContents(i); width += view->columnWidth(i); } else view->hideColumn(i); } } else model->setQuery(QSqlQuery()); if (width > 350) width = 350; QRect rect; rect.setHeight(height()); rect.setWidth(width); rect.setBottomLeft(QPoint(0, height() - 2)); _completer->complete(rect); _parsed = false; }
void VirtualClusterLineEdit::sHandleCompleter() { if (!hasFocus()) return; QString stripped = text().trimmed().toUpper(); if (stripped.isEmpty()) return; int width = 0; QSqlQueryModel* model = static_cast<QSqlQueryModel *>(_completer->model()); QTreeView * view = static_cast<QTreeView *>(_completer->popup()); _parsed = true; XSqlQuery numQ; numQ.prepare(_query + _numClause + (_extraClause.isEmpty() || !_strict ? "" : " AND " + _extraClause) + ((_hasActive && ! _showInactive) ? _activeClause : "") + QString(" ORDER BY %1 LIMIT 10;").arg(_numColName)); numQ.bindValue(":number", "^" + stripped); numQ.exec(); if (numQ.first()) { int numberCol = numQ.record().indexOf("number"); int nameCol = numQ.record().indexOf("name"); int descripCol = numQ.record().indexOf("description"); model->setQuery(numQ); _completer->setCompletionPrefix(stripped); for (int i = 0; i < model->columnCount(); i++) { if ( (i != numberCol) && (!_hasName || i != nameCol ) && (!_hasDescription || i != descripCol) ) { view->hideColumn(i); } } view->resizeColumnToContents(numberCol); width += view->columnWidth(numberCol); if (_hasName) { view->resizeColumnToContents(nameCol); width += view->columnWidth(nameCol); } if (_hasDescription) { view->resizeColumnToContents(descripCol); width += view->columnWidth(descripCol); } } else model->setQuery(QSqlQuery()); if (width > 350) width = 350; QRect rect; rect.setHeight(height()); rect.setWidth(width); rect.setBottomLeft(QPoint(0, height() - 2)); _completer->complete(rect); _parsed = false; }
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(); }