Mysql数据库基本操作，Mysql数据导出到excel、pdf，excel数据导入数据库

最新推荐文章于 2022-12-04 00:31:55 发布

仗剑走天涯、

最新推荐文章于 2022-12-04 00:31:55 发布

阅读量537

点赞数 1

分类专栏： QT、c++ 文章标签： qt 数据库

本文链接：https://blog.csdn.net/m0_54346389/article/details/115530193

版权

这篇博客介绍了如何使用Qt的QSqlTableModel模块进行MySQL数据库的基本操作，包括删除和添加数据，以及数据的撤销删除。此外，还详细阐述了如何将数据库中的数据导出到Excel和PDF文件，特别是如何在PDF上绘制网格。同时，博主分享了将Excel数据导入到MySQL数据库的方法，最后提供了整个程序的概述。

摘要由CSDN通过智能技术生成

操作数据库是用了qt集成的QSqlTableModel的模块

删除添加撤销删除数据

void report::on_pushButton_5_clicked()//删除整行数据
{
   
    int row=ui->tableView->currentIndex().row();
    modeq->removeRow(row);
    int ok=QMessageBox::warning(this,tr("Delete！"),tr("Delete?"),QMessageBox::Yes,QMessageBox::No);
    if(ok==QMessageBox::No)
    {
   
        modeq->revertAll();
    }else{
   
        modeq->submitAll();
    }
}

void report::on_pushButton_7_clicked()//撤销数据
{
   
    modeq->revertAll();
}

void report::on_pushButton_3_clicked()//添加数据
{
   
    modeq->database().transaction();
    if (modeq->submitAll()) {
   
    modeq->database().commit(); //提交
    } else {
   
    modeq->database().rollback(); //回滚
    QMessageBox::warning(this, tr("tableModel"),
    tr("ERROR: %1").arg(modeq->lastError().text()));
    }
}

从数据库取出数据保存至excel

void report::on_pushButton_2_clicked()
{
   
    QString filepath = QFileDialog::getSaveFileName(this, tr("Save as..."),
                                                  QString(), tr("EXCEL files (*.xls)"));
    if (filepath != "")
    {
   
        int row = ui->tableView->model()->rowCount();
        int col = ui->tableView->model()->columnCount();
        QList<QString> list;
        //添加列标题
        QString HeaderRow;
        for (int i = 0; i < col; i++)
        {
   
            HeaderRow.append(ui->tableView->model()->headerData(i, Qt::Horizontal, Qt::DisplayRole).toString() + "\t");
        }
        list.push_back(HeaderRow);
        for (int i = 0; i < row; i++)
        {
   
            QString rowStr = "";
            for (int j = 0; j < col; j++){
   
                QModelIndex index = ui->tableView->model()->index(i, j);
                rowStr += ui->tableView->model()->data(index).toString() + "\t";
            }
            list.push_back(rowStr);
        }
        QTextEdit textEdit;
        for (int i = 0; i < list.size(); i++)
        {
   
            textEdit.append(list.at(i));
        }
        QFile file(filepath);
        if (file.open(QFile::WriteOnly | QIODevice::Text))
        {
   
            QTextStream ts(&file);
            ts.setCodec("GB2312");
            ts << textEdit.document()->toPlainText();
            file.close();
        }
    }
}

从数据库取出数据保存至pdf，并且在pdf上画出网格

注意：：从数据库提取数据的方式可以采用map与vector的结合，我懒得改了，详情参考我写的这篇文章链接地址

void report::on_pushButton_4_clicked()
{
   
     int row=modeq->rowCount();
     modeq->setData(modeq->index(row,0),row);
     QSqlRecord record1=modeq->record();
     modeq->insertRecord(modeq->rowCount(),record1);
}


void report::on_pushButton_12_clicked()
{
   
#if 0
    QString filepath = QFileDialog::getSaveFileName(this, tr("Save as..."),
                                                  QString(), tr("PDF files (*.pdf)"));
    QPrinter pdf;
    pdf.setOrientation(QPrinter::Portrait);//设置pdf横纵属性
    pdf.setPageSize(QPrinter::A4);
    pdf.setOutputFormat(QPrinter::PdfFormat);
    pdf.setOutputFileName(filepath);
    QPainter *painter = new QPainter(this);
    painter->begin(&pdf);

    painter->setRenderHint(QPainter::Antialiasing,true);
    painter->setPen(QPen(QColor(255,255,255),2));
    painter->setBrush(QColor(255,255,255));
    QRect rect(0,0,800,600);
    painter->end();
#endif
    QString filepath = QFileDialog::getSaveFileName(this, tr("Save as..."),
                                                  QString(), tr("PDF files (*.pdf)"));
//    QString filepath ="C:/Users/NEU%204012/Desktop/3.pdf";
    QPrinter pdf;
//    (QPrinter::ScreenResolution)
    pdf.setOrientation(QPrinter::Landscape);//设置pdf横纵属性
    pdf.setPageSize(QPrinter::A4);
    pdf.setOutputFormat(QPrinter::PdfFormat);
    pdf.setOutputFileName(filepath);
    QPainter *painter = new QPainter(this);
    painter->begin(&pdf);
        painter->setRenderHint(QPainter::Antialiasing, true);
        // 设置画笔颜色、宽度
        painter->setPen(QPen(QColor(255, 255, 255), 2));
        // 设置画刷颜色
        painter->setBrush(QColor(255, 255, 255));
        QRect rect(0,0,800,600);
        //整张图设置画刷白底
        painter->fillRect(rect,QColor(255, 255, 255));
        painter->drawRect(rect);
        //画数据部分的线条
        painter->setPen(QPen(QColor(0, 0, 0), 1));
        QVector<QLine> lines;
        lines.append(QLine(QPoint(10,10),QPoint(1360,10)));//上边
        lines.append(QLine(QPoint(10,10),QPoint(10,960)));//左边
        lines.append(QLine(QPoint(1360,10),QPoint(1360,960)));//右边
        lines.append(QLine(QPoint(10,960),QPoint(1360,960)));//下边
        int widthgrid = 10;
        int hightgrid = 10;
        int addw=64.3;
        int addh=38;
        int width = 11;
        int hight = 29;
        int filedhiht=25;
        int addwidth = 20;
        int addhight = 30;
        for(int i=0;i<=24;i++)
        {
   
            lines.append(QLine(QPoint(10,hightgrid),QPoint(1360,hightgrid)));
            hightgrid+=38;
        }

        for(int j=0;j<=20;j++)
        {
   
            lines.append(QLine(QPoint(widthgrid,10),QPoint(widthgrid,960)));
            widthgrid+=64.3;
        }
        painter->drawLines(lines);
        QFont font;
        QSqlQuery query;
#if 1
        //数据字段
        query.exec("PRAGMA table_info('sheet2021')");
        QVector<QString>filed(50);
        int filenumber=0;
        QFont font1;
        font1.setPointSize(5);
        painter->setFont(font1);
        font.setFamily("黑体");
        font1.setItalic(true);
        while (query.next())
        {
   
            if(filenumber<=20){
   
                filed[filenumber]=query.value(1).toString();
                filenumber++;
                painter->drawText(10+addw*(filenumber-1),filedhiht, filed[filenumber-1]);
            }
            else {
   
                break;
                 }

        }
        query.clear();
#endif

#if 1

        font.setPointSize(7);
        font.setFamily("黑体");
        font.setItalic(true);
        painter->setFont(font);
        //add data

        query.exec("select * from sheet2021");
        int j=0;
        QVector<QString>a0(1000);
        QVector<QString>a1(1000);
        QVector<QString>a2(1000);
        QVector<QString>a3(1000);
        QVector<QString>a4(1000);
        QVector<QString>a5(1000);
        QVector<QString>a6(1000);
        QVector<QString>a7(1000);
        QVector<QString>a8(1000);
        QVector<QString>a9(1000);
        QVector<QString>a10(1000);
        QVector<QString>a11(1000);
        QVector<QString>a12(1000);
        QVector<QString>a13(1000);
        QVector<QString>a14(1000);
        QVector<QString>a15(1000);
        QVector<QString>a16(1000);
        QVector<QString>a17(1000);
        QVector<QString>a18(1000);
        QVector<QString>a19(1000);
        QVector<QString>a20(1000);
        int pdfend=0;
        int pdfpage=0;
        while(query.next())
        {
   
            pdfpage++;
            qDebug()<<"asd1"<<pdfpage;
            if(pdfpage<=24) {
   
                hight +=38;
                a7[j]=query.value(7).toString();
                a8[j]=query.value(8).toString();

                a0[j]=query.value(0).toString();
                a1[j]=query.value(1).toString();
                a2[j]=query.value(2).toString();
                a3[j]=query.value(3).toString();
                a4[j]=query.value(4).toString();
                a5[j

最低0.47元/天解锁文章

仗剑走天涯、

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
Mysql数据库基本操作，Mysql数据导出到excel、pdf，excel数据导入数据库

操作数据库是用了qt集成的QSqlTableModel的模块删除添加撤销删除数据void report::on_pushButton_5_clicked()//删除整行数据{ int row=ui->tableView->currentIndex().row(); modeq->removeRow(row); int ok=QMessageBox::warning(this,tr("Delete！"),tr("Delete?"),QMessageBox::Ye
复制链接

扫一扫

专栏目录