需求，将excel表格信息导入到Access数据库
QString m_file =QFileDialog::getOpenFileName(this, "打开", QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation), "表格文件( *.xls)");if(UIDemo01::getFromExcel(m_file)){QMessageBox::information(this,tr("提示"),tr("导入成功"));}else{QMessageBox::information(this,tr("提示"),tr("导入失败"));}
getFromExcel为静态函数：bool static getFromExcel(QString m_path);
实现Excel数据的读入功能，读入到QList<QStringList> data;
实现：
bool UIDemo01::getFromExcel(QString m_path){QSqlDatabase dbexcel = QSqlDatabase::addDatabase("QODBC", "excelexport");if(!dbexcel.isValid()){qDebug()<<"打开Excel失败";return false;}QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%1\";DBQ=%2").arg(m_path).arg(m_path);dbexcel.setDatabaseName(dsn);if(!dbexcel.open()){qDebug()<<"打开Excel失败";return false;}QSqlQuery query(dbexcel);query.exec("select * from [Sheet1$]");QStringList list;//一条记录QList<QStringList> data;//所有记录while(query.next()){list.clear(); QString stu_id = query.value(0).toString(); QString stu_name = query.value(1).toString(); list<<stu_id<<stu_name; data<<list;}qDebug()<<data;dbexcel.close();return UIDemo01::save(data);}
save实现数据的导入：
bool UIDemo01::save(QList<QStringList> data){QSqlQuery query;QString create_time=QDate::currentDate().toString("yyyy/MM/dd");foreach (QStringList slist, data){QString sSql = QString("INSERT INTO [db_student_info](stu_no,stu_name,create_time,state) VALUES(");for(int i=0,n=slist.size();i<n;i++){sSql+=QString(":%1").arg(i);if(i!=n-1)sSql+=",";elsesSql+=",'"+create_time+"',"+"1)";}qDebug()<<sSql;query.prepare(sSql);for(int i=0,n=slist.size();i<n;i++){query.bindValue(QString(":%1").arg(i),slist.at(i));}if(!query.exec()) {return false;}}return true;}
注：导入具体字段有所修改
