qt实现数据excel导出数据查询分页实例

#include "mainwindow.h" #include "ui_mainwindow.h" #include <QSqlTableModel> #include <QTableView> #include <QMessageBox> #include <QSqlError> #include<QDebug> #include<QDate> #include <QSqlQuery> #include<QByteArray> #include<QSqlRecord> MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow) { ui->setupUi(this); model = new QSqlTableModel(this); model->setTable("baojing"); //model->removeColumn(0); // 设置编辑策略 model->setEditStrategy(QSqlTableModel::OnManualSubmit); model->select(); ui->tableView->setModel(model); //QSqlQueryModel *queryModel = new QSqlQueryModel; xlsx.write("A1","报警值"); xlsx.write("B1","姓名"); tableName = 0; //初始化页数 pageTotal =100; a =1; //count =0; count = ui->tableView->model()->rowCount(); pages =count/pageTotal; ui->lineEdit_2->setText(QString::number(pages)); } MainWindow::~MainWindow() { delete ui; } void MainWindow::on_cxButton_clicked() { model->setTable("baojing"); //model->removeColumn(0); // 设置编辑策略 model->setEditStrategy(QSqlTableModel::OnManualSubmit); model->select(); ui->tableView->setModel(model); count = ui->tableView->model()->rowCount(); qDebug()<<"总记录"<<count; for(int i=0;i<count;i++){ QString name = model->record(i).value("baojingzhi").toString(); QString bb = model->record(i).value("姓名").toString(); qDebug()<<"报警值"<<i<<":"<<name; xlsx.write(i+2,1,name); xlsx.write(i+2,2,bb); } name = QString("book%1.xlsx").arg(tableName++); qDebug()<<"表名："<<name; xlsx.saveAs(name); /* QSqlQueryModel *queryModel = new QSqlQueryModel; queryModel->setQuery("select * from baojing;"); ui->tableView->setModel(queryModel); QString a = queryModel->record(1).value("baojingzhi").toString(); qDebug()<<"索引1"<<a; */ } void MainWindow::on_cxButton_2_clicked() { model->setTable("baojing"); model->select(); for(int i=0;i<1000;i++){ int rowCount = model->rowCount(); model->insertRow(rowCount); model->setData(model->index(rowCount,0),i); model->setData(model->index(rowCount,1),"i"); model->submitAll(); } } //首页 void MainWindow::on_firstButton_clicked() { QString strFilter = QString("1=1 limit %1,%2").arg(0).arg(10); model->setFilter(strFilter); model->select(); } //上一页 void MainWindow::on_upButton_clicked() { //总页数 // pages = count/pageTotal; if(a==0){ a=pages; } if(a<=1){ on_firstButton_clicked(); return; } //开头 int start = (a-1)*pageTotal; //每页条目 int end = pageTotal; // qDebug()<<"每页开头:"<<start<<"每页结尾："<<end; if(a>1){ this->updateInfo(start,end); } a--; qDebug()<<"上一页的a="<<a; } void MainWindow::updateInfo(int start, int end){ model->setTable("baojing"); QString strFilter = QString(" 1=1 limit %1,%2").arg(start).arg(end); model->setFilter(strFilter); model->select(); } //下一页 void MainWindow::on_downButton_clicked() { //总页数 //pages = count/pageTotal; if(pages<=1){ on_firstButton_clicked(); return; } if(a>pages){ this->on_endButton_clicked(); } //开头 int start = (a-1)*pageTotal; //每页条目 int end = pageTotal; //qDebug()<<"每页开头:"<<start<<"每页结尾："<<end; if(a<pages){ this->updateInfo(start,end); } a++; qDebug()<<"下一页的a="<<a; } //尾页 void MainWindow::on_endButton_clicked() { a=pages; int start = (a-1)*pageTotal; //每页条目 int end = pageTotal; this->updateInfo(start,end); } //跳转 void MainWindow::on_goButton_clicked() { //跳转页 int aim = ui->lineEdit->text().toInt(); a = aim; int start = (a-1)*pageTotal; //每页条目 int end = pageTotal; this->updateInfo(start,end); }