QOdbc读写excel

#include <QSqlDatabase>

#include <QDebug>

#include <QMessageBox>

#include <QSqlQuery>

#include <QVariant>

#include <QTime>

#include <QTextCodec>

void writeExcel(QString excelFilePath)

{

    // 创建一个数据库实例， 设置连接字符串

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC","excelexport");

    QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN=''; FIRSTROWHASNAMES=1; READONLY=FALSE;CREATE_DB="%1";DBQ=%2").

            arg(excelFilePath).arg(excelFilePath);

    db.setDatabaseName(dsn);

    // 打开数据库

    if (!db.open())

    {

        qDebug()<< "open false";

        QMessageBox::about(NULL, "r", "open false");

    }

    // 创建表格

    QString sql = "create table sheet (name TEXT, age NUMBER)";

    QSqlQuery query(db);

    if (!query.exec(sql))

    {

        qDebug()<< "create table false！";

        QMessageBox::about(NULL, "r", "create table false！");

    }

    // 写入数据

    db.exec( "insert into sheet(name, age) values('ctb', '28')");

    db.exec( "insert into sheet(name, age) values('xw', '19')");

    db.exec( "insert into sheet(name, age) values('lg', '34')");

    // 关闭数据库

    db.close();

}

void readExcel(const QString excelPath)

{

    // excel 数据库连接字符串 需要QODBC 驱动

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC","excelexport");

    QString connString = QString("Driver={Microsoft Excel Driver (*.xls)};Readonly=1;DriverId=790;Dbq=%1;DefaultDir=D:\").arg(excelPath);

    db.setDatabaseName(connString);

    // 打开数据库

    if (!db.open())

    {

        qDebug()<< "open false";

        QMessageBox::about(NULL, "r", "open false");

    }

    //查询数据

    QString sql = "Select * from [sheet$]";

    QSqlQuery query(sql, db);

    while (query.next()) {

        //读取数据

        QString name = query.value(0).toString();

        int age = query.value(1).toInt();

        qDebug()<< name << age <<endl;

    }

    // 关闭数据库

    db.close();

}

int main(int argc, char *argv[])

{

    QApplication a(argc, argv);

    //中文支持

    QTextCodec::setCodecForLocale(QTextCodec::codecForName("system"));

    QTextCodec::setCodecForTr(QTextCodec::codecForName("system"));

    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("system"));

    writeExcel("D:\test01.xls");

    readExcel("D:\test01.xls");

    return a.exec();

}

写入结果：

读取打印结果