qt实现的sqlites数据库文件与csv文件相互转换案例代码

#include "inOrOutConf.h" #include <QLineEdit> #include <QPushButton> #include <QLabel> #include <QFormLayout> #include <QHBoxLayout> #include <QVBoxLayout> #include <QGridLayout> #include <QCoreApplication> #include <QFile> #include <QSqlDatabase> #include <QSqlQuery> #include <QSqlError> #ifdef WIN32 #include <QtPlugin> Q_IMPORT_PLUGIN(QSQLiteDriverPlugin) #endif #include <QVector> #include <QTextCodec> #include <QDebug> // #include "sqlite/sqlite3.h" // #include "sqlite/CSQLite.h" #include "file/myfiledialog.h" #include "file/strcode.hpp" // //101YC配置表 // typedef struct _s101yc{ // int INDEXID; // int DEVID; // char NAME[256]; // unsigned int LOCALADDR; // double COEF; // double THV; // char NAMETOMST[256]; // unsigned int ZEROVAL; // unsigned int SAMPADDR; // unsigned int SAMPDEVID; // char EXPLAIN[256]; // }s101yc; // //101YX配置表 // typedef struct _s101yx{ // int INDEXID; // int DEVID; // char NAME[256]; // unsigned int LOCALADDR; // unsigned int NOT; // unsigned int SAMPDEVID; // unsigned int SAMPADDR; // char NAMETOMST[256]; // }s101yx; // //101YK配置表 // typedef struct _s101yk{ // int INDEXID; // unsigned int LOCALADDR; // int DEVID; // char NAME[256]; // unsigned int SAMPDEVID; // unsigned int SAMPADDR; // char NAMETOMST[256]; // }s101yk; // //YC // int GetS101yc(const char * dbfile, const char* condition, s101yc * ycs, int size) // { // CSQLiteDB db; // if( !db.open(dbfile) ){ // printf("open db: error!", dbfile); // return -1; // } // char sql[1024] ={0}; // if( strlen(condition) > 0 ) // sprintf(sql,"select * from s101yc where %s", condition); // else // sprintf(sql,"select * from s101yc"); // int icount = 0; // CSQLiteQuery query; // try{ // query = db.execQuery(sql); // while( !query.eof() && icount<size ){ // ycs[icount].INDEXID = query.getIntField("INDEXID"); // ycs[icount].DEVID = query.getIntField("DEVID"); // strcpy( ycs[icount].NAME,query.getStringField("NAME")); // ycs[icount].LOCALADDR = query.getIntField("LOCALADDR"); // ycs[icount].COEF = query.getFloatField("COEF"); // ycs[icount].THV = query.getFloatField("THV"); // strcpy( ycs[icount].NAMETOMST,query.getStringField("NAMETOMST")); // ycs[icount].ZEROVAL = query.getIntField("ZEROVAL"); // ycs[icount].SAMPADDR = query.getIntField("SAMPADDR"); // ycs[icount].SAMPDEVID = query.getIntField("SAMPDEVID"); // strcpy( ycs[icount].EXPLAIN,query.getStringField("EXPLAIN")); // icount++; // query.nextRow(); // } // }catch (CSQLiteException& e) { // printf("db query error %d\n",e.errorCode()); // return -1; // } // return icount; // }; InOrOutConf::InOrOutConf(QWidget * parent,Qt::WindowFlags f) : QWidget(parent,f) { pathEdit = new QLineEdit(this); pathEdit->setMinimumWidth(200); pathset = new QPushButton(tr("dbOpen"),this); pathset->setMaximumWidth(72); connect(pathset,SIGNAL(clicked()),this,SLOT(setDBPath())); //layout QFormLayout *formLayout_00 = new QFormLayout; formLayout_00->setContentsMargins(0,0,0,0); formLayout_00->addRow(tr("dbPath:"), pathEdit); QHBoxLayout *m_QHBoxLayout_00 = new QHBoxLayout; m_QHBoxLayout_00->setContentsMargins(0,0,0,0); m_QHBoxLayout_00->addLayout(formLayout_00); m_QHBoxLayout_00->addWidget(pathset); dirEdit = new QLineEdit(this); dirEdit->setMinimumWidth(200); dirset = new QPushButton(tr("scvDirCheck"),this); dirset->setMaximumWidth(72); connect(dirset,SIGNAL(clicked()),this,SLOT(setCsvPath())); //layout QFormLayout *formLayout_01 = new QFormLayout; formLayout_01->setContentsMargins(0,0,0,0); formLayout_01->addRow(tr("scvDir:"), dirEdit); QHBoxLayout *m_QHBoxLayout_01 = new QHBoxLayout; m_QHBoxLayout_01->setContentsMargins(0,0,0,0); m_QHBoxLayout_01->addLayout(formLayout_01); m_QHBoxLayout_01->addWidget(dirset); QVBoxLayout *m_QVBoxLayout = new QVBoxLayout; m_QVBoxLayout->addLayout(m_QHBoxLayout_00); m_QVBoxLayout->addLayout(m_QHBoxLayout_01); warLabel = new QLabel(this); inButton = new QPushButton(tr("scvToDB"),this); inButton->setMaximumWidth(72); connect(inButton,SIGNAL(clicked()),this,SLOT(scvToDB())); outButton = new QPushButton(tr("DBToCsv"),this); outButton->setMaximumWidth(72); connect(outButton,SIGNAL(clicked()),this,SLOT(DBToCsv())); close = new QPushButton(tr("Close"),this); close->setMaximumWidth(72); connect(close,SIGNAL(clicked()),this,SLOT(close())); QGridLayout *_QGridLayout = new QGridLayout(this); _QGridLayout->addLayout(m_QVBoxLayout,0,0,2,10); _QGridLayout->addWidget(warLabel,2,0,1,10); _QGridLayout->addWidget(inButton,3,7,1,1); _QGridLayout->addWidget(outButton,3,8,1,1); _QGridLayout->addWidget(close,3,9,1,1); #ifdef WIN32 divPath = "\\"; #else divPath = "/"; #endif }; InOrOutConf::~InOrOutConf() { }; void InOrOutConf::setDBPath() { QString dbPath = QCoreApplication::applicationDirPath()+divPath+"exxconf.db"; MyFileDialog *fileDialog = new MyFileDialog(this,4,tr("open DB file"),dbPath); if(fileDialog->exec() == QDialog::Accepted) { dbPath = fileDialog->selectedFiles()[0]; } qDebug() << "dbPath = "<<dbPath<<"\n"; this->pathEdit->setText(dbPath); }; void InOrOutConf::setCsvPath() { QString csvDir = QCoreApplication::applicationDirPath(); MyFileDialog *fileDialog = new MyFileDialog(this,3,tr("open CSV dir"),csvDir); if(fileDialog->exec() == QDialog::Accepted) { csvDir = fileDialog->selectedFiles()[0]; } qDebug() << "csvDir = "<<csvDir<<"\n"; this->dirEdit->setText(csvDir); }; bool executeSQL(QSqlDatabase db,QString _sql) { QSqlQuery query(db); return query.exec(_sql); }; bool checkTable(QSqlDatabase db, QString _table) { QString _sql = QString("select count(*) from sqlite_master where type='table' and name='%1'").arg(_table); QSqlQuery query(db); query.exec(_sql); bool _existTable = true; if (query.next()) { int _countTable = query.value(0).toInt(); if (1!=_countTable) { _existTable = false; } }else{ _existTable = false; } return _existTable; }; bool opendb(QSqlDatabase &db, QString _dbn,QString _dbLink) { QString dbv = "QSQLITE"; // QString _dbLink = "db_sqlite_record"; if (QSqlDatabase::contains(_dbLink)) { db = QSqlDatabase::database(_dbLink); }else{ db = QSqlDatabase::addDatabase(dbv,_dbLink); } // checkDBLink(_dbLink); // db = QSqlDatabase::addDatabase(dbv,_dbLink); db.setDatabaseName(_dbn); if (db.open()) { return true; }else{ qDebug() << "open db failed!"<< "---"<< db.lastError().text()<<" \n"; return false; } }; bool checkdb_etconf(QSqlDatabase &db, QString _dbn) { if (!opendb(db,_dbn,"db_sqlite_etconf")) { return false; }else{ if(!executeSQL(db,"PRAGMA synchronous = OFF")) { qDebug() << db.lastError() << "\n"; } } if(!checkTable(db,"s101yc")) { return false; } if(!checkTable(db,"s101yx")) { return false; } if(!checkTable(db,"s101yk")) { return false; } if(!checkTable(db,"s101yt")) { return false; } return true; }; bool readToVector(QString _file,QVector<QVector<QString> > &_lists, QString _div) { QFile file(_file); if (!file.open(QFile::ReadOnly)) { qDebug() << "conn't open file " << _file << "!\n"; return false; }else{ QTextStream stream( &file ); QString line; while ( !stream.atEnd() ) { line = stream.readLine(); QStringList _strL = line.split(QRegExp(_div)); // QStrin