//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();
}
