QOdbc读写excel

#include QApplication #include QSqlDatabase #include QDebug #include QMessageBox #include QSqlQuery #include QVariant #include QTime #include QTextCodec void writeExcel(QString excelFilePath) { // 创建一个数据库实例， 设置连接字符串 QSqlDa

#include

#include 

#include 

#include 

#include 

#include 

#include 

#include 

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 <
 }
 // 关闭数据库
 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();
}
写入结果：
读取打印结果