qt读写Excel并与数据库导入导出

效果如图：

1，在第二个输入框中输入1或者2，代表你要读取文档的第一个还是第二个表格
2，点击读取选择Excel表格，显示如上。
3，在导入数据库要先填一个数据库表格名字在第一个输入框
4，点击创建数据库表，
5，点击导入数据库。
6，点击写入Excel生成新的Excel表格文档

代码：

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QScopedPointer>
#include <QList>
#include <QVariant>
#include "ExcelBase.h"
#include "QVariantListListModel.h"

#include "userdatabase.h"
namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();

private slots:
    //读取表格
    void on_btn_readExcel_clicked();
    //写表格
    void on_btn_writeExcel_clicked();
    //更新数据
    void on_btn_updata_clicked();
    //导入
    void on_btn_importExcel_clicked();
    //导出
    void on_btn_export_clicked();
    //添加
    void on_btn_addExcel_clicked();
    //创建表格
    void on_btn_createTable_clicked();

private:
    Ui::MainWindow *ui;
    QVariantListListModel* md;
    QScopedPointer<ExcelBase> m_xls;
    QList< QList<QVariant> > m_datas;
    userDataBasePri m_database;
    bool createTableState;
    bool xlsReadState;
};

#endif // MAINWINDOW_H

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QFileDialog>
#include <QElapsedTimer>
#include <QDebug>
#include <QStandardItem>
#include <QMessageBox>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow),
    m_xls(NULL)
{
    ui->setupUi(this);
    createTableState = false;
    xlsReadState = false;
    md = new QVariantListListModel(this);
    md->setVariantListListPtr(&m_datas);

    ui->tablenums_lineEdit->setPlaceholderText("请输入要导入的第几个表");
    ui->tablenums_lineEdit->setValidator(new QIntValidator(ui->tablenums_lineEdit));
}

MainWindow::~MainWindow()
{
    delete ui;
}


void MainWindow::on_btn_export_clicked()
{
    //导出的方法，从数据库表中读出写入到下面的对象中然后刷新在界面显示
    QVariantListListModel *mds  = qobject_cast<QVariantListListModel*>(ui->tableView->model());
    //然后点击写入建立出新表
}

void MainWindow::on_btn_addExcel_clicked()
{
    //添加也是同理，将新增添加到下面对象中然后调用mds->updateData();
    QVariantListListModel*mds  = qobject_cast<QVariantListListModel*>(ui->tableView->model());
    mds->updateData();
}

/*!
  @file    userDataBase.hpp
  @author  chen
  @date    2018/9
  @brief   The file Declare database interface
  @https     https://blog.youkuaiyun.com/cqltbe131421
  @verbatim
          <author>    <time>   <version>  <desc>
         chen 2018/9   0.1.0     build this module
  @endverbatim
*/

#ifndef USERDATABASE_H
#define USERDATABASE_H
#include <QTextCodec>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QTime>
#include <QSqlError>
#include <QtDebug>
#include <QSqlDriver>
#include <QSqlRecord>

/*!
  @addtogroup  userDataInfo Patient information class
  @{
*/

class userDataBasePri
{
public:
    userDataBasePri();//构造函数中创建数据库文件
    ~userDataBasePri();
    //创建数据库表
    bool createTable(QStringList strlist,QString tablename);
    //插入数据
    bool insert(QStringList strlist, QString tablename);
    //更新数据
    bool updateById(QStringList strlist,QStringList datalist,QString tablename);
    //查询ID
    bool queryID(QString,QString tablename);
    //删除ID
    bool deleteById(QString id,QString table);
    //查询某表总数
    qint32 countAllRowNum(QString table);

private:
    QSqlDatabase mdatabase;//数据库
};

#endif // USERDATABASE_H

/*
  @file    userDataBase.cpp
  @author  chen
  @date    2017/03
  @brief   The file Definition database interface
  @par     History
  @verbatim
          <author>    <time>   <version>  <desc>
         chen 2017/06   0.1.0     build this module
  @endverbatim
*/

#include "userdatabase.h"
#include <QDebug>

#define DATA_BASE_NAME ("qt_excel.db")//数据库文件名


userDataBasePri::userDataBasePri()
{

    if (QSqlDatabase::contains("fundus_sqllite")) {//连接数据库
        mdatabase = QSqlDatabase::database("fundus_sqllite");
    } else {
        mdatabase = QSqlDatabase::addDatabase("QSQLITE","fundus_sqllite");
    }

    mdatabase.setDatabaseName(DATA_BASE_NAME);//设置数据库文件名
    //mdatabase.setUserName("fundus");//用户
    //mdatabase.setPassword("nopassword");//密码-无密码

    if (! mdatabase.open()){//打开数据库
        qCritical() << mdatabase.lastError().text();
        return;
    }
}

userDataBasePri::~userDataBasePri()
{
    mdatabase.close();
}
//创建数据库表 strlist表头内容，tablename表名
bool userDataBasePri::createTable(QStringList strlist,QString tablename)
{
    QString midstr;
    midstr.append (strlist.at (0));
    midstr +=  " varchar PRIMARY KEY";
    for(int i = 1;i < strlist.size ();i++)
    {
        midstr.append (",");
        midstr.append (strlist.at (i));
        midstr.append (" varchar (100)");
    }
    QString tableCreate = QString("CREATE TABLE IF NOT EXISTS "
                                  "%1(%2)").arg(tablename).arg (midstr);
    qDebug()<<"tableCreate ==>"<<tableCreate;
    //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    QSqlQuery query(mdatabase);
    bool result = query.exec(tableCreate);
    if (!result) {
        qDebug()<<"success";
        qCritical() << mdatabase.lastError().text();
        return false;
    }

    return true;
}
//插入信息，strlist插入内容，tablename插入到那个表
bool userDataBasePri::insert(QStringList strlist,QString tablename)
{
    QSqlQuery query(mdatabase);
    QString midstr;
    for(int i = 0;i < strlist.size ();i++)
    {
        if(i == 0)
        {
            midstr.append ("?");
        }else{
            midstr.append (",?");
        }
    }
    QString statement = QString("INSERT INTO %1 ").arg(tablename) +  QString("values(%1)").arg (midstr);
    qDebug()<<"statement==>"<<statement<<":::::strlist.size ()=="<<strlist.size ();
    query.prepare(statement);
    for(int i = 0;i < strlist.size ();i++)
    {
        query.bindValue(i, strlist.at (i));
    }

    if(! query.exec())
    {
        qCritical() << "insert error" << query.lastError().text();
        return false;
    }
    return true;
}
//更新内容，strlist更新的字段，datalist更新的数据，tablename更新那个表格
bool userDataBasePri::updateById(QStringList strlist,QStringList datalist,QString tablename)
{
    QSqlQuery query(mdatabase);
    QString midstr;
    for(int i = 1;i < strlist.size ();i++)
    {
        if(i == 1)
        {
            midstr.append (strlist.at (i) + "="+"? ");
        }else{
            midstr.append (",");
            midstr.append (strlist.at (i) + "="+"? ");
        }
    }
    midstr.append ("WHERE " + strlist.at (0) + " == "+ datalist.at (0));

    QString statement = QString("UPDATE %1 SET ").arg(tablename) +  midstr;
    query.prepare(statement);

    for(int i = 0;i < strlist.size ();i++)
    {
        query.bindValue(i, strlist.at (i));
    }

    if(! query.exec())
    {
        qCritical() << "insert error" << query.lastError().text();
        return false;
    }
    return true;
}
//查询，str根据什么查询，tablename查询那个表
bool userDataBasePri::queryID(QString str,QString tablename)
{
    QSqlQuery query(mdatabase);
    QString statement = QString("SELECT * FROM %1 WHERE id == '%2'").arg(tablename).arg(str);

    //qDebug() << statement;
    if (! query.exec(statement)) {
        qCritical() << "query.exec queryID " << query.lastError().text();;
    }

    while (query.next()) {
        qDebug() << "queryID was found";
        return true;
    }
    return false;
}
//查询，str根据什么删除，从tablename表删除
bool userDataBasePri::deleteById(QString id,QString table)
{
    QSqlQuery query(mdatabase);
    query.prepare(QString("DELETE FROM %1 WHERE id == '%2'").arg(table).arg(id));
    if(! query.exec())
    {
        qCritical() << "deleteById error = " << query.lastError().text();;
        return false;
    }
    return true;
}
//查询表格中总数
qint32 userDataBasePri::countAllRowNum(QString table)
{
    QSqlQuery query(mdatabase);
    query.prepare(QString("SELECT COUNT(*) FROM %1").arg(table));
    if(!query.exec())
    {
        qCritical() << "countAllRowNum error = " << query.lastError().text();;
        return -1;
    }

    if (query.next()) {
        qDebug() << "patient size = " << query.value(0).toInt();
        return query.value(0).toInt();
    }
    return 0;
}