QT odbc 处理excel

QT 处理excel 一般有window自带的一些接口，但是如果想是简单的记录处理一些数据可以使用odbc处理，类似于操作数据库，很方便

创建表

void ExeclForm::on_pushButton_4_clicked()
{
    QString sheetName = "sheet1";
    QString filename = "C:\\text.xls";
    db = QSqlDatabase::addDatabase("QODBC","excelexport");
    if(!db.isValid()) return;
    QString dsn = "DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"C:\\text.xls\";DBQ=C:\\text.xls";
    qDebug()<<dsn;
    db.setDatabaseName(dsn);
    if(!db.open())
    {
        //QMessageBox::
        db.close();
        return;
    }
    QSqlQuery query(db);
    QString sSql;
    sSql = QString("DROP TABLE [%1]").arg(sheetName);
    query.exec(sSql);
    sSql = QString("CREATE TABLE[%1] (NAME varchar(20), AGE varchar(20))").arg(sheetName);
    query.prepare(sSql);
    query.exec();
    db.close();
    QSqlDatabase::removeDatabase("excelexport");
}

插入数据

void ExeclForm::on_pushButton_2_clicked()
{
    QString sheetName = "sheet1";
    QString filename = "C:\\text.xls";
    db = QSqlDatabase::addDatabase("QODBC","excelexport");
    if(!db.isValid()) return;
    db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"C:\\text.xls\";DBQ=C:\\text.xls");
    if(!db.open())
    {
        return;
    }
    QSqlQuery query(db);
    QString sSql = QStringLiteral("INSERT INTO [sheet1$] (NAME, AGE) VALUES ('111','222')").arg(sheetName);
    query.exec(sSql);
    db.close();
    QSqlDatabase::removeDatabase("excelexport");
}

读取数据

void ExeclForm::on_pushButton_3_clicked()
{
    QString sheetName = "sheet1";
    QString filename = "C:\\text.xls";
    db = QSqlDatabase::addDatabase("QODBC","excelexport");
    if(!db.isValid()) return;
    db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"C:\\text.xls\";DBQ=C:\\text.xls");
    if(!db.open())
    {
        return;
    }
    QSqlQuery query(db);
    QString sSql = QStringLiteral("select * from [%1$]").arg(sheetName);
    bool b = query.exec(sSql);
    while(query.next()){
        QString title = query.value(0).toString();
        qDebug() << title;
    }
    db.close();
    QSqlDatabase::removeDatabase("excelexport");
}

还有很多操作可以通过修改sql 来完成

需要注意的是，数据表名需要使用 [$]