286 lines
11 KiB
C++
286 lines
11 KiB
C++
#include "historicalrecords.h"
|
||
#include "ui_historicalrecords.h"
|
||
#include<QMessageBox>
|
||
#include<QFileDialog>
|
||
#include<QAxObject>
|
||
#include<QStandardItemModel>
|
||
historicalrecords::historicalrecords(QWidget *parent)
|
||
: QWidget(parent)
|
||
, ui(new Ui::historicalrecords)
|
||
{
|
||
ui->setupUi(this);
|
||
this->setWindowFlags(Qt::FramelessWindowHint); // 隐藏标题栏
|
||
ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
|
||
ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
|
||
ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
|
||
ui->tableView->setAlternatingRowColors(true);
|
||
|
||
model = new QSqlTableModel(this);
|
||
model->setTable("testing");//打开数据库中名为“”的表
|
||
model->select();
|
||
|
||
ui->tableView->setModel(model);
|
||
ui->tableView->show();
|
||
model->setHeaderData(0,Qt::Horizontal,"记录");
|
||
model->setHeaderData(1,Qt::Horizontal,"操作类型");
|
||
model->setHeaderData(3,Qt::Horizontal,"样品名称");
|
||
model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
|
||
model->setHeaderData(4,Qt::Horizontal,"采集地点");
|
||
model->setHeaderData(5,Qt::Horizontal,"采集时间");
|
||
model->setHeaderData(6,Qt::Horizontal,"水样类型");
|
||
model->setHeaderData(7,Qt::Horizontal,"进样量");
|
||
model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
|
||
model->setHeaderData(9,Qt::Horizontal,"开始时间");
|
||
model->setHeaderData(10,Qt::Horizontal,"结束时间");
|
||
|
||
ui->pushButton->setFixedSize(150,50);
|
||
ui->pushButton_2->setFixedSize(150,50);
|
||
ui->pushButton_4->setFixedSize(150,50);
|
||
ui->pushButton_3->setFixedSize(150,50);
|
||
|
||
ui->lineEdit->setFixedSize(300,50);
|
||
ui->lineEdit_2->setFixedSize(300,50);
|
||
ui->widget->setFixedHeight(60);
|
||
}
|
||
|
||
historicalrecords::~historicalrecords()
|
||
{
|
||
delete ui;
|
||
}
|
||
|
||
//查询
|
||
void historicalrecords::on_pushButton_clicked()
|
||
{
|
||
|
||
QString number=ui->lineEdit->text();
|
||
QSqlQuery query;
|
||
QString str=QString("samplename = '%1'").arg(number);
|
||
qDebug() <<str;
|
||
model->setFilter(str);
|
||
model->select();
|
||
model->setHeaderData(0,Qt::Horizontal,"记录");
|
||
model->setHeaderData(1,Qt::Horizontal,"操作类型");
|
||
model->setHeaderData(3,Qt::Horizontal,"样品名称");
|
||
model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
|
||
model->setHeaderData(4,Qt::Horizontal,"采集地点");
|
||
model->setHeaderData(5,Qt::Horizontal,"采集时间");
|
||
model->setHeaderData(6,Qt::Horizontal,"水样类型");
|
||
model->setHeaderData(7,Qt::Horizontal,"进样量");
|
||
model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
|
||
model->setHeaderData(9,Qt::Horizontal,"开始时间");
|
||
model->setHeaderData(10,Qt::Horizontal,"结束时间");
|
||
|
||
|
||
|
||
|
||
}
|
||
void historicalrecords::exportTableViewToExcel(QTableView* tableView) {
|
||
// 创建Excel应用程序对象
|
||
QAxObject* excel = new QAxObject("Excel.Application", nullptr);
|
||
if (!excel) {
|
||
QMessageBox::warning(nullptr, "Error", "Could not create Excel application object.");
|
||
return;
|
||
}
|
||
|
||
// 显示Excel应用程序（可选）
|
||
excel->setProperty("Visible", true);
|
||
|
||
// 获取工作簿对象
|
||
QAxObject* workbooks = excel->querySubObject("Workbooks");
|
||
QAxObject* workbook = workbooks->querySubObject("Add()");
|
||
|
||
// 获取工作表对象
|
||
QAxObject* worksheet = workbook->querySubObject("Worksheets(1)");
|
||
|
||
// 获取QTableView的模型
|
||
QAbstractItemModel* model = tableView->model();
|
||
if (!model) {
|
||
QMessageBox::warning(nullptr, "Error", "No model associated with the table view.");
|
||
delete excel;
|
||
return;
|
||
}
|
||
|
||
int rowCount = model->rowCount();
|
||
int columnCount = model->columnCount();
|
||
|
||
// 将模型数据写入Excel工作表
|
||
for (int row = 2; row <= rowCount; ++row) { // Excel的行和列是从1开始的
|
||
for (int column = 1; column <= columnCount; ++column) {
|
||
QModelIndex index = model->index(row - 1, column - 1); // Qt的行和列是从0开始的
|
||
QVariant value = model->data(index);
|
||
QAxObject* cell = worksheet->querySubObject("Cells(int,int)", row, column);
|
||
cell->setProperty("Value", value.toString());
|
||
}
|
||
}
|
||
model->setHeaderData(0,Qt::Horizontal,"记录");
|
||
model->setHeaderData(1,Qt::Horizontal,"操作类型");
|
||
model->setHeaderData(3,Qt::Horizontal,"样品名称");
|
||
model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
|
||
model->setHeaderData(4,Qt::Horizontal,"采集地点");
|
||
model->setHeaderData(5,Qt::Horizontal,"采集时间");
|
||
model->setHeaderData(6,Qt::Horizontal,"水样类型");
|
||
model->setHeaderData(7,Qt::Horizontal,"进样量");
|
||
model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
|
||
model->setHeaderData(9,Qt::Horizontal,"开始时间");
|
||
model->setHeaderData(10,Qt::Horizontal,"结束时间");
|
||
|
||
|
||
|
||
|
||
QAxObject* cell1 = worksheet->querySubObject("Cells(int,int)",1, 1);
|
||
cell1->setProperty("Value", "记录");
|
||
QAxObject* cell3 = worksheet->querySubObject("Cells(int,int)",1, 2);
|
||
cell3->setProperty("Value", "样品名称");
|
||
QAxObject* cell4 = worksheet->querySubObject("Cells(int,int)",1, 3);
|
||
cell4->setProperty("Value", "采集地点");
|
||
QAxObject* cell5 = worksheet->querySubObject("Cells(int,int)",1, 4);
|
||
cell5->setProperty("Value", "采集时间");
|
||
QAxObject* cell6 = worksheet->querySubObject("Cells(int,int)",1, 5);
|
||
cell6->setProperty("Value", "水样类型");
|
||
QAxObject* cell7 = worksheet->querySubObject("Cells(int,int)",1, 6);
|
||
cell7->setProperty("Value", "操作类型");
|
||
QAxObject* cell8 = worksheet->querySubObject("Cells(int,int)",1, 7);
|
||
cell8->setProperty("Value", "进样量");
|
||
QAxObject* cell9 = worksheet->querySubObject("Cells(int,int)",1, 8);
|
||
cell9->setProperty("Value", "洗脱时间");
|
||
QAxObject* cell10 = worksheet->querySubObject("Cells(int,int)",1, 9);
|
||
cell10->setProperty("Value", "开始时间");
|
||
QAxObject* cell11 = worksheet->querySubObject("Cells(int,int)",1, 10);
|
||
cell11->setProperty("Value", "结束时间");
|
||
QAxObject* cell2 = worksheet->querySubObject("Cells(int,int)",1, 11);
|
||
cell2->setProperty("Value", "操作类型");
|
||
|
||
}
|
||
//导出
|
||
void historicalrecords::on_pushButton_2_clicked()
|
||
{
|
||
// exportTableViewToExcel( ui->tableView);
|
||
// QString desktopPath = QStandardPaths::writableLocation(QStandardPaths::DesktopLocation);
|
||
// QString filePath = QDir(desktopPath).filePath("mydata.csv");
|
||
// QString fileName = QFileDialog::getSaveFileName();
|
||
exportTableViewToCsv("");
|
||
|
||
|
||
|
||
|
||
|
||
}
|
||
void historicalrecords::exportTableViewToCsv(const QString &filePat)
|
||
{
|
||
QAbstractItemModel *model = ui->tableView->model();
|
||
if (!model) {
|
||
qDebug() << "No model set on the table view.";
|
||
return;
|
||
}
|
||
QString filePath = QFileDialog::getSaveFileName(this, "Export to CSV", "", "CSV Files (*.csv)");
|
||
|
||
QFile file(filePath);
|
||
if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
|
||
qDebug() << "Could not open file for writing:" << file.errorString();
|
||
return;
|
||
}
|
||
|
||
QTextStream out(&file);
|
||
// QFile file(QFileDialog::getSaveFileName());
|
||
// if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
|
||
// qDebug() << "Could not open file for writing:" << file.errorString();
|
||
// return;
|
||
// }
|
||
|
||
// QTextStream out(&file);
|
||
|
||
// 写入表头（可选）
|
||
int columnCount = model->columnCount();
|
||
for (int col = 0; col < columnCount; ++col) {
|
||
out << model->headerData(col, Qt::Horizontal, Qt::DisplayRole).toString();
|
||
if (col < columnCount - 1) {
|
||
out << ",";
|
||
}
|
||
}
|
||
out << "\n";
|
||
|
||
// 写入数据行
|
||
int rowCount = model->rowCount();
|
||
for (int row = 0; row < rowCount; ++row) {
|
||
for (int col = 0; col < columnCount; ++col) {
|
||
QVariant data = model->data(model->index(row, col), Qt::DisplayRole);
|
||
out << data.toString().replace(",", ";"); // 替换逗号以避免CSV格式问题
|
||
if (col < columnCount - 1) {
|
||
out << ",";
|
||
}
|
||
}
|
||
out << "\n";
|
||
}
|
||
|
||
file.close();
|
||
qDebug() << "Data exported to CSV successfully.";
|
||
}
|
||
QString historicalrecords::getDesktopPath()
|
||
{
|
||
return QStandardPaths::writableLocation(QStandardPaths::DesktopLocation);
|
||
}
|
||
|
||
|
||
//刷新
|
||
void historicalrecords::on_pushButton_3_clicked()
|
||
{
|
||
ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
|
||
ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
|
||
ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
|
||
ui->tableView->setAlternatingRowColors(true);
|
||
// model = new QSqlQueryModel;
|
||
model = new QSqlTableModel(this);
|
||
model->setTable("testing");//打开数据库中名为“”的表
|
||
model->select();
|
||
ui->tableView->setModel(model);
|
||
ui->tableView->show();
|
||
|
||
model->setHeaderData(0,Qt::Horizontal,"记录");
|
||
model->setHeaderData(1,Qt::Horizontal,"操作类型");
|
||
model->setHeaderData(3,Qt::Horizontal,"样品名称");
|
||
model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
|
||
model->setHeaderData(4,Qt::Horizontal,"采集地点");
|
||
model->setHeaderData(5,Qt::Horizontal,"采集时间");
|
||
model->setHeaderData(6,Qt::Horizontal,"水样类型");
|
||
model->setHeaderData(7,Qt::Horizontal,"进样量");
|
||
model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
|
||
model->setHeaderData(9,Qt::Horizontal,"开始时间");
|
||
model->setHeaderData(10,Qt::Horizontal,"结束时间");
|
||
|
||
|
||
}
|
||
|
||
|
||
//查询
|
||
void historicalrecords::on_pushButton_4_clicked()
|
||
{
|
||
|
||
QString number=ui->lineEdit_2->text();
|
||
QSqlQuery query;
|
||
QString str=QString("SELECT * FROM testing WHERE time like '%%1%'").arg(number);
|
||
qDebug() <<str;
|
||
query.prepare(str);
|
||
if (query.exec()) { // 确保查询执行成功
|
||
QSqlQueryModel *model = new QSqlQueryModel;
|
||
model->setQuery(query); // 先设置查询，这样模型就会包含查询结果
|
||
ui->tableView->setModel(model);
|
||
ui->tableView->show(); // 显示视图，此时视图将显示查询结果
|
||
model->setHeaderData(0,Qt::Horizontal,"记录");
|
||
model->setHeaderData(1,Qt::Horizontal,"操作类型");
|
||
model->setHeaderData(3,Qt::Horizontal,"样品名称");
|
||
model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
|
||
model->setHeaderData(4,Qt::Horizontal,"采集地点");
|
||
model->setHeaderData(5,Qt::Horizontal,"采集时间");
|
||
model->setHeaderData(6,Qt::Horizontal,"水样类型");
|
||
model->setHeaderData(7,Qt::Horizontal,"进样量");
|
||
model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
|
||
model->setHeaderData(9,Qt::Horizontal,"开始时间");
|
||
model->setHeaderData(10,Qt::Horizontal,"结束时间");
|
||
|
||
} else {
|
||
|
||
qDebug() << "Query failed:" << query.lastError().text();
|
||
}
|
||
}
|
||
|