【2021-1-5】QT+SQLsever数据库的数据管理系统

此文转载自：https://blog.csdn.net/qq_45738269/article/details/112218031#commentBox

QT+SQLsever数据库的数据管理系统

目录

该系统的功能有：图表显示，图像界面对数据的增删改查，界面跳转缓冲进度条，开机界面，角色管理等。

登录界面

登录界面
数据库连接：

 /********************************数据库连接******************************/
    QSqlDatabase db=QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName(QString("DRIVER={SQL SERVER};"
                               "SERVER=%1;" //服务器名称
                               "DATABASE=%2;"//数据库名
                               "UID=%3;"//登录名
                               "PWD=%4;"//密码
                               ).arg("……")
                                .arg("……")
                                .arg("……")
                                .arg("……"));
     if (!db.open())
     {
          qDebug()<<"connect sql server failed!";//数据库登录失败时输出
     }else
     {
          qDebug()<<"connect sql server successfully!";//数据库登录成功时输出
     }

回车快捷键：

/************回车键按钮快捷键***********/
    ui->pushButton->setDefault(true);//按钮默认选中
    ui->pushButton->setShortcut(QKeySequence::InsertParagraphSeparator);//将小键盘回车键与登录按钮绑定在一起
    ui->pushButton->setShortcut(Qt::Key_Enter);//将字母区回车键与登录按钮绑定在一起
    ui->pushButton->setShortcut(Qt::Key_Return);//将小键盘回车键与登录按钮绑定在一起

窗口关闭询问是否退出：

#include <QCloseEvent>

void closeEvent(QCloseEvent *event);

/*************窗口关闭时询问是否退出*************/
void Denglu::closeEvent(QCloseEvent *event)
{
   QMessageBox::StandardButton result=QMessageBox::question(this, "确认", "确定要退出本系统吗？",
                      QMessageBox::Yes|QMessageBox::No |QMessageBox::Cancel,
                      QMessageBox::No);

    if (result==QMessageBox::Yes)
        event->accept();
    else
        event->ignore();
}

输入框提示：

ui->lineEdit->setPlaceholderText("请输入！");

界面跳转缓冲：

#include <QProgressDialog>
#include <QTimer>


           //跳转到主界面
           this->hide();
            //新建对象，参数含义：对话框正文，取消按钮名称，进度条范围
            QProgressDialog *pd;
            pd = new QProgressDialog("正在跳转...","取消",0,100,this);
            //模态对话框
            pd->setWindowModality(Qt::WindowModal);
            //如果进度条运行的时间小于5，进度条就不会显示，默认是4S
            //pd->setMinimumDuration(5);
            //设置标题
            pd->setWindowTitle("界面跳转中请稍后");
            //处理过程。。。
            pd->setRange(0, 100000);
            pd->setMinimumDuration(0);
            pd->setAttribute(Qt::WA_DeleteOnClose, true);
            //QProgressBar q;
            for (int i = 0; i < 100000; i++)
            {
               pd->setValue(i);
               QCoreApplication::processEvents();
                       if(pd->wasCanceled())
                           break;
            }
           MainWindow *w = new MainWindow;
           w->show();
           pd->hide();

统计图表制作

柱状图制作：

/*****************柱状图************************/
void MainWindow::onLine(){
    /******************定义变量****************/
    int i =0;
    int j =0;
    int x[200];//数字存储
    int y[200];
    for(j=0;j<200;j++){
        x[j]=0;
        y[j]=0;
    }
    QString n0[200];//商品信息名称
    QString n1[100];//维修单产品类型
    QString n2[3000];//送货单名称及规格
    int n3[3000];//送货单名称及规格，数量
    QString str;
    QSqlQuery query;
    int a=0,b=0;
    /*************执行**********/
    i=0;
    str = QString("select 名称 from 商品信息");
    query.exec(str);
        while(query.next()){
            n0[i] = query.value(0).toString();
            i++;
        }
    i=0;
    str = QString("select 产品类型 from 维修售后统计表 where 修好日期>'%1'and 修好日期<'%2'").arg(time1).arg(time2);
        query.exec(str);
        while(query.next()){
            n1[i] = query.value(0).toString();
            i++;
        }
    i=0;
    str = QString("select 名称及规格,sum(出库数量) from (select 名称及规格,sum(送货单.数量) as 出库数量 from 送货单,商品信息 where 商品信息.名称=送货单.名称及规格 and (送货单.消息类型='正常' or 送货单.消息类型='赠送' or 送货单.消息类型='现金') and 送货日期>'%1'and 送货日期<'%2'group by 名称及规格 union select 名称及规格,-sum(送货单.数量) from 送货单,商品信息 where 商品信息.名称=送货单.名称及规格 and 送货单.消息类型='退货'and 送货日期>'%1'and 送货日期<'%2'group by 名称及规格)as a group by 名称及规格").arg(time3).arg(time4);
            query.exec(str);
            while(query.next()){
                n2[i] = query.value(0).toString();
                n3[i] = query.value(1).toInt();
                i++;
            }
            for(i=0;i<200;i++){
                for(j=0;j<100;j++){ 
                    if(n0[i]==n2[j]){
                        y[i]=n3[j];
                    }
                    if(n0[i]==n1[j]){
                        x[i]=x[i]+1;
                    }
                }
            }
            QString str02 = QString("select 产品类型 from 维修售后统计表 where 修好日期>'%1'and 修好日期<'%2'").arg(time1).arg(time2);
            query.exec(str02);
            while (query.next()) {
                a++;
            }
            ui->la1->setText(QString::number(a));
            for(j=0;j<200;j++){
                b += y[j];
            }
            ui->la3->setText(QString::number(b));
        /******************画柱状图*******************/
        QBarSet *set0 = new QBarSet("XXXXX");
        QBarSet *set1 = new QBarSet("XXXXX");
        for(j=0;j<200;j++){
          *set0 << x[j];
          *set1 << y[j];
        }
        QBarSeries *series = new QBarSeries();
        QBarSeries *series1 = new QBarSeries();
        series->append(set0);
        series1->append(set1);

        series->setLabelsPosition(QAbstractBarSeries::LabelsInsideEnd); // 设置数据系列标签的位置于数据柱内测上方
        series->setLabelsVisible(true); // 设置显示数据系列标签
        connect(series, SIGNAL(hovered(bool, int, QBarSet*)), this, SLOT(sltTooltip(bool, int, QBarSet*)));
        series1->setLabelsPosition(QAbstractBarSeries::LabelsInsideEnd); // 设置数据系列标签的位置于数据柱内测上方
        series1->setLabelsVisible(true); // 设置显示数据系列标签
        connect(series1, SIGNAL(hovered(bool, int, QBarSet*)), this, SLOT(sltTooltip_2(bool, int, QBarSet*)));

        QChart *chart = new QChart();
        QChart *chart1 = new QChart();
        chart->addSeries(series);
        chart1->addSeries(series1);
        chart->setTitle("XXXXXX表");
        chart1->setTitle("XXXXX表");
        chart->setAnimationOptions(QChart::SeriesAnimations);
        chart1->setAnimationOptions(QChart::SeriesAnimations);

        for(i=0;i<200;i++){
            categories << n0[i];
            categories1 << n0[i];
        }
        QBarCategoryAxis *axis = new QBarCategoryAxis();
        QBarCategoryAxis *axis1 = new QBarCategoryAxis();
        axis->append(categories);
        axis1->append(categories1);
        axis->setGridLineVisible(true);
        axis1->setGridLineVisible(true);

        chart->createDefaultAxes();//创建默认的左侧的坐标轴（根据 QBarSet 设置的值）
        chart1->createDefaultAxes();
        chart->setAxisX(axis, series);//设置坐标轴
        chart1->setAxisX(axis1,series1);

        chart->legend()->setVisible(true); //设置图例为显示状态
        chart1->legend()->setVisible(true);
        chart->legend()->setAlignment(Qt::AlignBottom);//设置图例的显示位置在底部
        chart1->legend()->setAlignment(Qt::AlignBottom);//设置图例的显示位置在底部
        ui->chart->setChart(chart);
        ui->chart_2->setChart(chart1);
}

鼠标悬停提示：

/*****************鼠标悬停提示*********************/
void MainWindow::sltTooltip(bool b , int i, QBarSet* bar){
    if(b){
        ui->chart->setStyleSheet("QToolTip{border:1px solid rgb(118, 118, 118); background-color: #ffffff; color:#484848; font-size:12px;}"); //设置边框, 边框色, 背景色, 字体色, 字号
        ui->chart->setToolTip("X: "+categories.at(i)+"
"+QString("Y: %1 ").arg(bar->at(i)));
    }else{
        ui->chart->setStyleSheet("QToolTip{border:1px solid rgb(118, 118, 118); background-color: #ffffff; color:#484848; font-size:12px;}"); //设置边框, 边框色, 背景色, 字体色, 字号
        ui->chart->setToolTip("鼠标所处位置无数据");
    }
}

QT界面之间传递参数

定义全局变量：

int a;
extern int a ;

信号与槽连接：

#include <QProgressDialog>
#include <QTimer>

private slots:
    void onUpdate();
signals:
    void sendData(QString);   //用来传递数据的信号
this->hide();
    //新建对象，参数含义：对话框正文，取消按钮名称，进度条范围
            QProgressDialog *pd;
            pd = new QProgressDialog("正在跳转...","取消",0,100,this);
            //模态对话框
            pd->setWindowModality(Qt::WindowModal);
            //如果进度条运行的时间小于5，进度条就不会显示，默认是4S
            //pd->setMinimumDuration(5);
            //设置标题
            pd->setWindowTitle("界面跳转中请稍后");
            //处理过程。。。
            pd->setRange(0, 100000);
            pd->setMinimumDuration(0);
            pd->setAttribute(Qt::WA_DeleteOnClose, true);
            //QProgressBar q;
            for (int i = 0; i < 100000; i++)
            {
               pd->setValue(i);
            }
            Peijianxijiexiugai *p = new Peijianxijiexiugai;
            p->show();
            connect(this, SIGNAL(sendData(QString)),p, SLOT(receiveData(QString)));
            emit sendData(modelid);
           pd->hide();

左键点击tablevie后跳出菜单

private:
    Ui::Peijianxijie *ui;
    QSqlQueryModel *model = new QSqlQueryModel;
    QString modelid;
    QString date1;
    QString date2;
    QString name;
    
modelid=model->data(model->index(index.row(),0)).toString();//获取选中行数据
    name=model->data(model->index(index.row(),1)).toString();
    date1=model->data(model->index(index.row(),4)).toString();
    date2=model->data(model->index(index.row(),5)).toString();
    ui->l1->setText(date1);
    ui->l2->setText(date2);

    QMenu *menu = new QMenu(this);
    QAction *LookZhuban = new QAction(tr("……"), this);
    QAction *LookShou = new QAction(tr("……"), this);
    QAction *Update = new QAction(tr("……"),this);
    LookZhuban->setData(0);
    Update->setData(1);
    LookShou->setData(3);
    menu->addAction(Update);
    menu->addAction(LookZhuban);
    menu->addAction(LookShou);
    connect(LookZhuban, SIGNAL(triggered()), this, SLOT(onb1()));
    connect(LookShou, SIGNAL(triggered()), this, SLOT(onb2()));
    connect(Update, SIGNAL(triggered()), this, SLOT(onUpdate()));
    menu->exec(cursor().pos());
    //释放内存
    QList<QAction*> list = menu->actions();
    foreach (QAction* pAction, list) delete pAction;
    delete menu;

点击表格在label上显示图片、下载、上传到数据库

图片上传：

QString id = ui->lineEdit_3->text();
    if(id==NULL){
           QMessageBox::question(NULL, "消息提示框", "请输入id号", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
       }else{
           //获取图片文件
           QString strFileName = QFileDialog::getOpenFileName(this, tr("Open Image"), ".", tr("Image Files(*.jpg *.png *.bmp)"));
           if (strFileName.isEmpty())
           {
           QMessageBox::information(NULL, tr("Warning"), tr("You didn't select any files."));
           return;
           }

           //插入数据库 TEST_PIC为数据库表明  CONTENT为存储照片字段 为Blob类型
           //将照片以二进制流的方式存到数据库
           QPixmap pixmap(strFileName);
           QByteArray byteArray = QByteArray();
           QBuffer buffer(&byteArray);
           buffer.open(QIODevice::WriteOnly);
           pixmap.save(&buffer,"png",0);

           QString strQSL = QString("update XXXXX表 set 照片=? where id = '%1'").arg(id);
           QSqlQuery query;
           query.prepare(strQSL);
           query.addBindValue(byteArray);
           int a = query.exec();
           if(a){
               QMessageBox::question(NULL, "消息提示框", "上传成功，请更新表格查看", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
           }else{
               QMessageBox::question(NULL, "消息提示框", "上传失败", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
           }
       }

图片下载：

QString id = ui->lineEdit_3->text();
    if(id==NULL){
           QMessageBox::question(NULL, "消息提示框", "请输入id号", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
       }else{
           //从数据库读取照片
           QByteArray byteText;
           QString strsql = QString("select 照片 from XXXXX表 where id='%1'").arg(id);
           QSqlQuery query;
           query.exec(strsql);
           while(query.next()){
             byteText = query.value(0).toByteArray();
           }
           if(byteText.size()==0){
              QMessageBox::question(NULL, "消息提示框", "导出失败，该条消息中照片列表为空。", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
           }else{
               QString filename1 = QFileDialog::getSaveFileName(this,tr("Save Image"),"",tr("Images (*.png)")); //选择路径
               QString strsql = QString("select 照片 from XXXXX表 where id='%1' ").arg(id);
               int a = query.exec(strsql);
               bool b;
               while (query.next()) {
                 byteText = query.value(0).toByteArray();
                 QPixmap pix;
                 pix.loadFromData(byteText,"png");
                 b = pix.save(QString(filename1));//保存从数据库读取的照片到本地
               }
               if(a&&b){
                   QMessageBox message(QMessageBox::NoIcon,  "消息提示框",  "文件已成功导出，是否打开文件", QMessageBox::Yes | QMessageBox::No, NULL);
                    if(message.exec() == QMessageBox::Yes)
                   {
                        QDesktopServices::openUrl(QUrl::fromLocalFile(filename1));
                   }
               }else{
                   QMessageBox::question(NULL, "消息提示框", "照片下载失败", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
               }
           }
       }

图片显示：

QString id = ui->lineEdit_3->text();
    if(id==NULL){
           QMessageBox::question(NULL, "消息提示框", "请输入id号", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
       }else{
           //从数据库读取照片
           QByteArray byteText;
           QString strsql = QString("select 照片 from XXXXX表 where id='%1'").arg(id);
           QSqlQuery query;
           query.exec(strsql);
           while(query.next()){
             byteText = query.value(0).toByteArray();
           }
           if(byteText.size()==0){
              QMessageBox::question(NULL, "消息提示框", "显示失败，该条消息中照片列表为空。", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
           }else{
               //QString filename1 = QFileDialog::getSaveFileName(this,tr("Save Image"),"",tr("Images (*.png)")); //选择路径
               QString strsql = QString("select 照片 from XXXXX表 where id='%1' ").arg(id);
               query.exec(strsql);
               while (query.next()) {
                 byteText = query.value(0).toByteArray();
                 QPixmap pix;
                 pix.loadFromData(byteText,"png");
                 ui->label_3->setPixmap(pix);
               }
           }
       }

保存widget为PDF

QPrinter printer_pixmap(QPrinter::HighResolution);
    printer_pixmap.setPageSize(QPrinter::A4);  //设置纸张大小为A4
    printer_pixmap.setOutputFormat(QPrinter::PdfFormat);  //设置输出格式为pdf
    QString filename1 = QFileDialog::getSaveFileName(this,tr("Save PDF"),"",tr("PDF (*.pdf)")); //选择路径
    if(filename1 !=""){
    printer_pixmap.setOutputFileName(filename1);   //设置输出路径
    QPixmap pixmap = QPixmap::grabWidget(ui->widget, ui->widget->rect());  //获取界面的图片


    QPainter painter_pixmap;
    painter_pixmap.begin(&printer_pixmap);
    QRect rect = painter_pixmap.viewport();
    int multiple = rect.width()/pixmap.width();
    painter_pixmap.scale(multiple, multiple); //将图像(所有要画的东西)在pdf上放大multiple-1倍
    painter_pixmap.drawPixmap(40, 10, pixmap);  //画图
    painter_pixmap.end();

    QMessageBox message(QMessageBox::NoIcon,  "消息提示框",  "文件已成功导出，是否打开文件", QMessageBox::Yes | QMessageBox::No, NULL);
     if(message.exec() == QMessageBox::Yes)
    {
         QDesktopServices::openUrl(QUrl::fromLocalFile(filename1));
    }
    }

保存tableview为Excel

private slots:
     void Table2ExcelByHtml(QTableView *tableView, QString &title);


void MainWindow::Table2ExcelByHtml(QTableView *tableView, QString &title)
{
    QString fileName = QFileDialog::getSaveFileName(tableView, "保存",QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation), "Excel 文件(*.xls *.xlsx)");
    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);

            int colCount = tableView->model()->columnCount();
            int rowCount = tableView->model()->rowCount();

            QAxObject *cell, *col;

            //标题行
            cell = workSheet->querySubObject("Cells(int, int)", 1, 1);
            cell->dynamicCall("SetValue(const QString&)", title);
            cell->querySubObject("Font")->setProperty("Size", 18);
            //调整行高
            workSheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
            //合并标题行
            QString cellTitle;
            cellTitle.append("A1:");
            cellTitle.append(QChar(colCount - 1 + 'A'));
            cellTitle.append(QString::number(1));
            QAxObject *range = workSheet->querySubObject("Range(const QString&)", cellTitle);
            range->setProperty("WrapText", true);
            range->setProperty("MergeCells", true);
            range->setProperty("HorizontalAlignment", -4108);
            range->setProperty("VertivcalAlignment", -4108);

            //列标题
            for (int i = 0; i < colCount; i++)
            {
                QString columnName;
                columnName.append(QChar(i + 'A'));
                columnName.append(":");
                columnName.append(QChar(i + 'A'));
                col = workSheet->querySubObject("Columns(const QString&)", columnName);
                col->setProperty("ColumnWidth", tableView->columnWidth(i)/6);
                cell = workSheet->querySubObject("Cells(int, int)", 2, i+1);
                columnName = tableView->model()->headerData(i, Qt::Horizontal, Qt::DisplayRole).toString();
                cell->dynamicCall("SetValue(const QString&)", columnName);
                cell->querySubObject("Font")->setProperty("Bold", true);
                cell->querySubObject("Interior")->setProperty("Color", QColor(191, 191, 191));
                cell->setProperty("HorizontalAlignment", -4108);
                cell->setProperty("VertivcalAlignment", -4108);
            }

            //处理数据
            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    QModelIndex index = tableView->model()->index(i, j);
                    QString strData = tableView->model()->data(index).toString();
                    workSheet->querySubObject("Cells(int, int)", i + 3, j + 1)->dynamicCall("SetValue(const QString&)", strData);
                }
            }

            //画框线
            QString l_range;
            l_range.append("A2:");
            l_range.append(colCount -1 + 'A');
            l_range.append(QString::number(tableView->model()->rowCount() + 2));
            range = workSheet->querySubObject("Range(const QString&)", l_range);
            range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
            range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));

            //调整数据区行高
            QString rowsName;
            rowsName.append("2:");
            rowsName.append(QString::number(tableView->model()->rowCount() + 2));
            range = workSheet->querySubObject("Range(const QString&)", rowsName);
            range->setProperty("RowHeight", 20);
            workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName)); //保存到fileName

            workBook->dynamicCall("Close()"); //关闭工作簿
            excel->dynamicCall("Quit()"); //关闭excel
            delete  excel;
            excel = NULL;
            QMessageBox message(QMessageBox::NoIcon,  "消息提示框",  "文件已成功导出，是否打开文件", QMessageBox::Yes | QMessageBox::No, NULL);
            //message.setWindowFlags(Qt::WindowStaysOnTopHint);
             if(message.exec() == QMessageBox::Yes)
            {
                 QDesktopServices::openUrl(QUrl::fromLocalFile(fileName));
            }
        }
        else
        {
            QMessageBox::warning(NULL, tr("错误"), tr("未能创建 Excel 对象，请安装 Microsoft Excel。"), QMessageBox::Apply);
        }
    }
}

void MainWindow::on_pushButton_8_clicked()
{
    QString fileName = "Excel";

    Table2ExcelByHtml(ui->tableView, fileName);
}

lineedit美化，combo box 美化

如图
如图

美化：

/*下拉列表框*/

QComboBox
{
    background:white;
    padding-left:5px ;
    border-top-left-radius:3px;
    border-top-right-radius:3px;
    border: 1px solid rgb(0 ,0 , 0);//边框颜色设置
}

QComboBox::drop-down
{
    width:20px;
    border:0px; 
    border-radius:0px; 
    background:white; 
    border-left:0px ; 
    padding-right:5px;
    border:none;
	
	border-image: url(:/jpg/11.png);
}


ui->LineEdit_3->setStyleSheet("*{font-family:Microsoft YaHei;font-size:12px;}QLineEdit{font-size:13px;background:transparent;border:none;border-bottom:1px solid rgb(229, 229, 229);}QLineEdit:hover{border-bottom:1px solid rgb(193,193, 193);}QLineEdit:focus{border-bottom:1px solid rgb(18, 183, 245);}");

作者有话说

工程部分代码放上来以便我下次使用，部分注释是错误的请仔细辨别。

   

更多内容详见微信公众号：Python测试和开发

Python测试和开发

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/phyger/p/14247473.html