qt实现sqlite数据库文件与csv文件相互转换

一、使用场景

假设一：目前有一个项目记录数据是采用sqlite文件存储的，数据量很大，里面的记录数据客户需要将其导入到execl中进行报表编辑及汇报工作；

假设二：用户手里有点表配置表格，需要导入到工程的sqlite配置文件中给应用程序使用，配量很大。

二、实现设计

在sqlite数据库文件读写方面，基于QT插件库提供的QSQLiteDriverPlugin实现数据库打开、读写、查询等操作。

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()<

在针对csv（XLS 工作表）的文件读写方面，是基于QFile实现的FileQt类封装实现csv文件的有序读取、写入等操作。同时支持直接的.xls文件操作.

//QString _div为csv文件中每行的分割符，QVector > &_lists为读取的数据信息，采用二元数组存储行、列数据
bool readToVector(QString _file,QVector > &_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));
            // QStringList _strL = line.split(QRegExp(_div),QString::SkipEmptyParts);
            QVector _onev;
            for (int i = 0; i < _strL.size(); i++)
            {
                _onev.push_back(_strL[i]);
            }
            _lists.push_back(_onev);
        }
        file.close();

        if (_lists.empty())
        {
            return false;
        }else{
            return true;
        }
    }
}

bool writeListInfo(QVector list,const QString path,bool appendf)
{
    QFile f(path);
    if(!f.open(QFile::WriteOnly | QFile::Text | (appendf?QFile::Append:QFile::Truncate)))
        return false;

    QTextStream out(&f);

    // out.setCodec("UTF-8"); //中文编码
    for (int i = 0; i < list.size(); i++)
    {
        out << QString(list[i]);
    }
    f.close();
    return true;
};

/*
*这类函数的意义主要是csv文件里面只有数据，转存到sqlite数据文件中，需要构建表格，就是需要指定表格
*的表名、列名，表名采用csv文件名来表示，列名需要额外指定，本文直接硬代码实现，当然也可以在csv文件
*里面的第一列增加列名，在程序处理时先读取第一列，进行列名命名，在进行转换导入。
*/
void inYx(QSqlDatabase &db,QString _path)
{
    QVector > _lists;
    readToVector(_path,_lists,",");

    QSqlQuery query(db);
    query.exec("DELETE FROM s101yx");

    QVariantList indexids;
    QVariantList devids;
    QVariantList names;
    QVariantList localaddrs;
    QVariantList nots;
    QVariantList sampdevids;
    QVariantList sampaddrs;
    QVariantList nametomsts;

    int _count = 0;
    QString _sql = "INSERT INTO s101yx (INDEXID,DEVID,NAME,LOCALADDR,\"NOT\",SAMPDEVID,SAMPADDR,NAMETOMST) "
                   "VALUES (?,?,?,?,?,?,?,?)";

    for (int i = 0; i < _lists.size(); i++)
    {
        if (8!=_lists[i].size())
        {
            continue;
        }
        indexids << _lists[i][0].toInt();
        devids << _lists[i][1].toInt();
        names << _lists[i][2];
        // names << QString("%1").arg(_lists[i][2].toAscii().data());
        localaddrs << _lists[i][3].toInt();
        nots << _lists[i][4].toInt();
        sampdevids << _lists[i][5].toInt();
        sampaddrs << _lists[i][6].toInt();
        nametomsts << _lists[i][7];
        // nametomsts << QString("%1").arg(_lists[i][7].toAscii().data());

        _count+=1;
        if (_count>=200||(i==(_lists.size()-1)))
        {
            try{
                query.prepare(_sql);
                query.addBindValue(indexids);
                query.addBindValue(devids);
                query.addBindValue(names);
                query.addBindValue(localaddrs);
                query.addBindValue(nots);
                query.addBindValue(sampdevids);
                query.addBindValue(sampaddrs);
                query.addBindValue(nametomsts);

                if (!query.execBatch())
                {
                    qDebug() << query.lastError();
                }
            }catch(...){
                qDebug() << query.lastError();
            }
            indexids.clear();
            devids.clear();
            names.clear();
            localaddrs.clear();
            nots.clear();
            sampdevids.clear();
            sampaddrs.clear();
            nametomsts.clear();

            _count = 0;
        }
    }
};
...

提供一个简要对话框设置数据库文件路径及csv文件路径，提供转换选择按钮实现文件数据转换输出。

void InOrOutConf::DBToCsv()
{
    QString _dbn = this->pathEdit->text();
    QFile _file(_dbn);
    if (!_file.exists())
    {
        warLabel->setText("db isn't exist!");
        return;
    }
    // s101yc * ycs = new s101yc[10];
    // int ret = GetS101yc(_dbn.toStdString().c_str(),"",ycs,10);
    // if(ret>0){
    //  for (int i = 0; i < ret; i++)
    //  {
    //      qDebug() <<  QString("%1").arg(ycs[i].NAME)<setText("db isn't table we need!");
        return;
    }
    warLabel->setText("db to csv runing!");
    //这里本应通过读取表名（或配置实现）+路径来命名csv文件路径的，本案例测试时，强行指定文件名
    outYc(db,this->dirEdit->text()+divPath+"s101yc.csv");
    outYx(db,this->dirEdit->text()+divPath+"s101yx.csv");
    outYk(db,this->dirEdit->text()+divPath+"s101yk.csv");
    outYt(db,this->dirEdit->text()+divPath+"s101yt.csv");
    warLabel->setText("db to csv finish!");
    db.close();
};

void InOrOutConf::scvToDB()
{
    QString _dbn = this->pathEdit->text();
    QFile _file(_dbn);
    if (!_file.exists())
    {
        warLabel->setText("db isn't exist!");
        return;
    }

    QSqlDatabase db;
    if(!checkdb_etconf(db,_dbn))
    {
        warLabel->setText("db isn't table we need!");
        return;
    }
    warLabel->setText("csv to db runing!");
    //这里本应通过配置定义+路径来读取csv文件，本案例测试时，强行指定文件名
    inYc(db,this->dirEdit->text()+divPath+"s101yc.csv");
    inYx(db,this->dirEdit->text()+divPath+"s101yx.csv");
    inYk(db,this->dirEdit->text()+divPath+"s101yk.csv");
    inYt(db,this->dirEdit->text()+divPath+"s101yt.csv");
    warLabel->setText("csv to db finish!");
    db.close();
};

三、工程实现

本文采用qt5.8来实现的，并对该版本进行了静态编译,win系统是win10-64bit，linux系统是centos7-64bit系统,同时静态编译了plugins\sqldrivers插件。

整个工程目录结构如下：

sqlite_and_csv_trans
    bin
    file
        FileQt.h
        FileQt.cpp
        myfiledialog.h
        myfiledialog.cpp
        strcode.hpp
        strcode.cpp
    languages
        dbInOrOut_cn.qm
        dbInOrOut_cn.ts
    view
        inOrOutConf.h
        inOrOutConf.cpp
    dbInOrOut.qrc
    dbIntOrOut.pro
    main.cpp

dbIntOrOut.pro:

QT      += core
QT      += gui
QT      += sql

greaterThan(QT_MAJOR_VERSION, 4) {
    QT += printsupport
    QT += concurrent
    QT += widgets
}

TEMPLATE = app
DESTDIR = bin

CONFIG += qt warn_on debug_and_release
CONFIG += static

CONFIG(debug, debug|release) {
    TARGET          = dbInOrOutd
    OBJECTS_DIR     = debug/obj
    MOC_DIR         = debug/moc
    CONFIG          += console
    DEFINES         += _DEBUG
}else{
    TARGET          = dbInOrOut
    OBJECTS_DIR     = release/obj
    MOC_DIR         = release/moc
    DEFINES         += NODEBUG
}

DEFINES +=  QTWIN_32 \
        QT_CORE_LIB \
        QT_GUI_LIB \
        QT_SQL_LIB \
        QT_THREAD_SUPPORT
#请指定qt的sqlite插件路径
QT_SQL = D:\\workForSoftware\\Qt\\Qt5.8.0\\5.8\\msvc2015_64_static\\plugins\\sqldrivers

win32{
    CONFIG(debug, debug|release) {
        LIBS             += $$(QT_SQL)\\qsqlited.lib
    } else {
        LIBS             += $$(QT_SQL)\\qsqlite.lib
    }
}
HEADERS  += view/inOrOutConf.h \
        file/myfiledialog.h \
        file/strcode.hpp \

SOURCES += main.cpp\
    view/inOrOutConf.cpp \
    file/myfiledialog.cpp \
    file/strcode.cpp \

TRANSLATIONS += languages/dbInOrOut_cn.ts
RESOURCES += dbInOrOut.qrc

dbInOrOut.qrc

        languages/dbInOrOut_cn.qm

strcode.hpp，实现字符编码转换

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

#ifndef STRCODE_HPP
#define STRCODE_HPP

#include
#ifdef WIN32
#include
#endif

#ifdef __linux__
int code_convert(char *from_charset,char *to_charset,char *inbuf,size_t inlen,char *outbuf,size_t outlen);
int u2g(char *inbuf,int inlen,char *outbuf,int outlen);
int g2u(char *inbuf,size_t inlen,char *outbuf,size_t outlen);
int u2a(char *inbuf,int inlen,char *outbuf,int outlen);
int a2u(char *inbuf,int inlen,char *outbuf,int outlen);
#endif

#ifdef WIN32
std::wstring Utf82Unicode(const std::string& utf8string);
std::string WideByte2Acsi(std::wstring& wstrcode);
std::wstring Acsi2WideByte(std::string& strascii);
std::string Unicode2Utf8(const std::wstring& widestring);
#endif
bool UnicodeToAnsi(wchar_t* lpString,char* szAnsi);//(in,out)
bool AnsiToUnicode(char* lpString,wchar_t* szUnicode);//(in,out)
char* U2G(const char* utf8);
char* G2U(const char* gb2312);

std::string UTF_82ASCII(std::string &strUtf8Code);
std::string ASCII2UTF_8(std::string& strAsciiCode);
//using namespace std;
bool containStr(std::string _str, std::string _strsub);

int enc_unicode_to_utf8_one(unsigned long unic, unsigned char *pOutput, int outSize);
// int enc_utf8_to_unicode_one(const unsigned char* pInput, unsigned long *Unic);
//
/*
std::string ws2s(const std::wstring& ws);
std::wstring s2ws(const std::string& s);
std::wstring UTF2Uni(const char* src, std::wstring &t);
int Uni2UTF( const std::wstring& strRes, char *utf8, int nMaxSize );
std::string s2utfs(const  std::string&  strSrc);
std::string  utfs2s(const std::string& strutf);
*/

#endif //STRCODE_HPP

strcode.cpp

#include "strcode.hpp"

#include
#include
#include
#include
#include
#include
#include

#ifdef __linux__
#include
#endif

#ifdef __linux__
//代码转换:从一种编码转为另一种编码
int code_convert(char *from_charset,char *to_charset,char *inbuf,size_t inlen,char *outbuf,size_t outlen)
{
    iconv_t cd;
    int rc;
    char **pin = &inbuf;
    char **pout = &outbuf;

    cd = iconv_open(to_charset,from_charset);
    if (cd==0) return -1;
    memset(outbuf,0,outlen);
    if (iconv(cd,pin,&inlen,pout,&outlen)==-1) return -1;
    iconv_close(cd);
    return 0;
}
//UNICODE码转为GB2312码
int u2g(char *inbuf,int inlen,char *outbuf,int outlen)
{
    return code_convert("utf-8","gb2312",inbuf,inlen,outbuf,outlen);
}
//GB2312码转为UNICODE码
int g2u(char *inbuf,size_t inlen,char *outbuf,size_t outlen)
{
    return code_convert("gb2312","utf-8",inbuf,inlen,outbuf,outlen);
}

int u2a(char *inbuf,int inlen,char *outbuf,int outlen)
{
    return code_convert("utf-8","ascii",inbuf,inlen,outbuf,outlen);
}

int a2u(char *inbuf,int inlen,char *outbuf,int outlen)
{
    return code_convert("ascii","utf-8",inbuf,inlen,outbuf,outlen);
}

#endif

#ifdef WIN32
//UTF-8¡ÁaUnicode
std::wstring Utf82Unicode(const std::string& utf8string)
{
    int widesize = ::MultiByteToWideChar(CP_UTF8, 0, utf8string.c_str(), -1, NULL, 0);
    if (widesize == ERROR_NO_UNICODE_TRANSLATION)
    {
        throw std::exception("Invalid UTF-8 sequence.");
    }
    if (widesize == 0)
    {
        throw std::exception("Error in conversion.");
    }

    std::vector resultstring(widesize);

    int convresult = ::MultiByteToWideChar(CP_UTF8, 0, utf8string.c_str(), -1, &resultstring[0], widesize);

    if (convresult != widesize)
    {
        throw std::exception("La falla!");
    }

    return std::wstring(&resultstring[0]);
};

//unicode ¡Áa?a ascii

std::string WideByte2Acsi(std::wstring& wstrcode)
{
    int asciisize = ::WideCharToMultiByte(CP_OEMCP, 0, wstrcode.c_str(), -1, NULL, 0, NULL, NULL);
    if (asciisize == ERROR_NO_UNICODE_TRANSLATION)
    {
        throw std::exception("Invalid UTF-8 sequence.");
    }
    if (asciisize == 0)
    {
        throw std::exception("Error in conversion.");
    }
    std::vector resultstring(asciisize);
    int convresult =::WideCharToMultiByte(CP_OEMCP, 0, wstrcode.c_str(), -1, &resultstring[0], asciisize, NULL, NULL);

    if (convresult != asciisize)
    {
        throw std::exception("La falla!");
    }

    return std::string(&resultstring[0]);
};
///

//ascii ¡Áa Unicode

std::wstring Acsi2WideByte(std::string& strascii)
{
    int widesize = MultiByteToWideChar (CP_ACP, 0, (char*)strascii.c_str(), -1, NULL, 0);
    if (widesize == ERROR_NO_UNICODE_TRANSLATION)
    {
        throw std::exception("Invalid UTF-8 sequence.");
    }
    if (widesize == 0)
    {
        throw std::exception("Error in conversion.");
    }
    std::vector resultstring(widesize);
    int convresult = MultiByteToWideChar (CP_ACP, 0, (char*)strascii.c_str(), -1, &resultstring[0], widesize);

    if (convresult != widesize)
    {
        throw std::exception("La falla!");
    }

    return std::wstring(&resultstring[0]);
};

//Unicode ¡Áa Utf8

std::string Unicode2Utf8(const std::wstring& widestring)
{
    int utf8size = ::WideCharToMultiByte(CP_UTF8, 0, widestring.c_str(), -1, NULL, 0, NULL, NULL);
    if (utf8size == 0)
    {
        throw std::exception("Error in conversion.");
    }

    std::vector resultstring(utf8size);

    int convresult = ::WideCharToMultiByte(CP_UTF8, 0, widestring.c_str(), -1, &resultstring[0], utf8size, NULL, NULL);

    if (convresult != utf8size)
    {
        throw std::exception("La falla!");
    }

    return std::string(&resultstring[0]);
};
#endif

//mbstowcs()函数和wcstombs()函数，选自CRT库。平台无关，但需设定local。不设定Local,中文会乱码
bool UnicodeToAnsi(wchar_t* lpString,char* szAnsi)//(in,out)
{
    size_t nLen=wcslen(lpString)*2;//wcslen
    char* lpszBuf=(char*)malloc(nLen);//malloc  and
    setlocale(LC_ALL,"");
    size_t nRet=wcstombs(lpszBuf,lpString,nLen);//wcstombs
    if(nRet and
    setlocale(LC_ALL,"");
    size_t nRet = mbstowcs(lpszBuf, lpString, nLen);
    if(nRet(strUtf8Code.c_str()),strUtf8Code.size(),lpszBuf,256);
    return std::string(lpszBuf);
    #endif
};

//ascii ¡Áa Utf8
std::string ASCII2UTF_8(std::string& strAsciiCode)
{
    #ifdef WIN32
    std::string strRet("");
    //?¨¨¡ã? ascii ¡Áa?a unicode
    std::wstring wstr = Acsi2WideByte(strAsciiCode);
    //¡Á?o¨®¡ã? unicode ¡Áa?a utf8
    strRet = Unicode2Utf8(wstr);
    return strRet;
    #endif
    #ifdef linux
    char lpszBuf[256]={0};
    a2u(const_cast(strAsciiCode.c_str()),strAsciiCode.size(),lpszBuf,256);
    return std::string(lpszBuf);
    #endif
};

/
bool containStr(std::string _str, std::string _strsub)
{
    std::string::size_type pos = _str.find(_strsub);//²éÕÒ_strsub±êʶ
    if(pos!=std::string::npos){
        return true;
    }
    return false;
};

/
/*****************************************************************************
 * 将一个字符的Unicode(UCS-2和UCS-4)编码转换成UTF-8编码.

 *
 * 参数:
 *    unic     字符的Unicode编码值
 *    pOutput  指向输出的用于存储UTF8编码值的缓冲区的指针
 *    outsize  pOutput缓冲的大小
 *
 * 返回值:
 *    返回转换后的字符的UTF8编码所占的字节数, 如果出错则返回 0 .
 *
 * 注意:
 *     1. UTF8没有字节序问题, 但是Unicode有字节序要求;
 *        字节序分为大端(Big Endian)和小端(Little Endian)两种;
 *        在Intel处理器中采用小端法表示, 在此采用小端法表示. (低地址存低位)
 *     2. 请保证 pOutput 缓冲区有最少有 6 字节的空间大小!

 ****************************************************************************/
int enc_unicode_to_utf8_one(unsigned long unic, unsigned char *pOutput, int outSize)
{
    assert(pOutput != NULL);
    assert(outSize >= 6);

    if ( unic = 0x00000080 && unic > 6) & 0x1F) | 0xC0;
        return 2;
    }
    else if ( unic >= 0x00000800 && unic >  6) & 0x3F) | 0x80;
        *pOutput     = ((unic >> 12) & 0x0F) | 0xE0;
        return 3;
    }
    else if ( unic >= 0x00010000 && unic >  6) & 0x3F) | 0x80;
        *(pOutput+1) = ((unic >> 12) & 0x3F) | 0x80;
        *pOutput     = ((unic >> 18) & 0x07) | 0xF0;
        return 4;
    }
    else if ( unic >= 0x00200000 && unic >  6) & 0x3F) | 0x80;
        *(pOutput+2) = ((unic >> 12) & 0x3F) | 0x80;
        *(pOutput+1) = ((unic >> 18) & 0x3F) | 0x80;
        *pOutput     = ((unic >> 24) & 0x03) | 0xF8;
        return 5;
    }
    else if ( unic >= 0x04000000 && unic >  6) & 0x3F) | 0x80;
        *(pOutput+3) = ((unic >> 12) & 0x3F) | 0x80;
        *(pOutput+2) = ((unic >> 18) & 0x3F) | 0x80;
        *(pOutput+1) = ((unic >> 24) & 0x3F) | 0x80;
        *pOutput     = ((unic >> 30) & 0x01) | 0xFC;
        return 6;
    }

    return 0;
}

/*****************************************************************************
 * 将一个字符的UTF8编码转换成Unicode(UCS-2和UCS-4)编码.

 *
 * 参数:
 *    pInput      指向输入缓冲区, 以UTF-8编码
 *    Unic        指向输出缓冲区, 其保存的数据即是Unicode编码值,
 *                类型为unsigned long .
 *
 * 返回值:
 *    成功则返回该字符的UTF8编码所占用的字节数; 失败则返回0.

 *
 * 注意:
 *     1. UTF8没有字节序问题, 但是Unicode有字节序要求;
 *        字节序分为大端(Big Endian)和小端(Little Endian)两种;
 *        在Intel处理器中采用小端法表示, 在此采用小端法表示. (低地址存低位)
 ****************************************************************************/
 /*
int enc_utf8_to_unicode_one(const unsigned char* pInput, unsigned long *Unic)
{
    assert(pInput != NULL && Unic != NULL);

    // b1 表示UTF-8编码的pInput中的高字节, b2 表示次高字节, ...

    char b1, b2, b3, b4, b5, b6;

    *Unic = 0x0; // 把 *Unic 初始化为全零
    int utfbytes = enc_get_utf8_size(*pInput);
    unsigned char *pOutput = (unsigned char *) Unic;

    switch ( utfbytes )
    {
        case 0:
            *pOutput     = *pInput;
            utfbytes    += 1;
            break;
        case 2:
            b1 = *pInput;
            b2 = *(pInput + 1);
            if ( (b2 & 0xE0) != 0x80 )
                return 0;
            *pOutput     = (b1 << 6) + (b2 & 0x3F);
            *(pOutput+1) = (b1 >> 2) & 0x07;
            break;
        case 3:
            b1 = *pInput;
            b2 = *(pInput + 1);
            b3 = *(pInput + 2);
            if ( ((b2 & 0xC0) != 0x80) || ((b3 & 0xC0) != 0x80) )
                return 0;
            *pOutput     = (b2 << 6) + (b3 & 0x3F);
            *(pOutput+1) = (b1 << 4) + ((b2 >> 2) & 0x0F);
            break;
        case 4:
            b1 = *pInput;
            b2 = *(pInput + 1);
            b3 = *(pInput + 2);
            b4 = *(pInput + 3);
            if ( ((b2 & 0xC0) != 0x80) || ((b3 & 0xC0) != 0x80)
                    || ((b4 & 0xC0) != 0x80) )
                return 0;
            *pOutput     = (b3 << 6) + (b4 & 0x3F);
            *(pOutput+1) = (b2 << 4) + ((b3 >> 2) & 0x0F);
            *(pOutput+2) = ((b1 << 2) & 0x1C)  + ((b2 >> 4) & 0x03);
            break;
        case 5:
            b1 = *pInput;
            b2 = *(pInput + 1);
            b3 = *(pInput + 2);
            b4 = *(pInput + 3);
            b5 = *(pInput + 4);
            if ( ((b2 & 0xC0) != 0x80) || ((b3 & 0xC0) != 0x80)
                    || ((b4 & 0xC0) != 0x80) || ((b5 & 0xC0) != 0x80) )
                return 0;
            *pOutput     = (b4 << 6) + (b5 & 0x3F);
            *(pOutput+1) = (b3 << 4) + ((b4 >> 2) & 0x0F);
            *(pOutput+2) = (b2 << 2) + ((b3 >> 4) & 0x03);
            *(pOutput+3) = (b1 << 6);
            break;
        case 6:
            b1 = *pInput;
            b2 = *(pInput + 1);
            b3 = *(pInput + 2);
            b4 = *(pInput + 3);
            b5 = *(pInput + 4);
            b6 = *(pInput + 5);
            if ( ((b2 & 0xC0) != 0x80) || ((b3 & 0xC0) != 0x80)
                    || ((b4 & 0xC0) != 0x80) || ((b5 & 0xC0) != 0x80)
                    || ((b6 & 0xC0) != 0x80) )
                return 0;
            *pOutput     = (b5 << 6) + (b6 & 0x3F);
            *(pOutput+1) = (b5 << 4) + ((b6 >> 2) & 0x0F);
            *(pOutput+2) = (b3 << 2) + ((b4 >> 4) & 0x03);
            *(pOutput+3) = ((b1 << 6) & 0x40) + (b2 & 0x3F);
            break;
        default:
            return 0;
            break;
    }

    return utfbytes;
}
*/
///
/*
std::string ws2s(const std::wstring& ws)
{
    std::string curLocale = setlocale(LC_ALL, NULL); // curLocale = "C";
    setlocale(LC_ALL, "chs");
    const wchar_t* _Source = ws.c_str();
    size_t _Dsize = 2 * ws.size() + 1;
    char *_Dest = new char[_Dsize];
    memset(_Dest,0,_Dsize);
    wcstombs(_Dest,_Source,_Dsize);
    std::string result = _Dest;
    delete []_Dest;
    setlocale(LC_ALL, curLocale.c_str());
    return result;
}
*/
/*
std::wstring s2ws(const std::string& s)
{
    setlocale(LC_ALL, "chs");
    const char* _Source = s.c_str();
    size_t _Dsize = s.size() + 1;
    wchar_t *_Dest = new wchar_t[_Dsize];
    wmemset(_Dest, 0, _Dsize);
    int nret = mbstowcs(_Dest,_Source,_Dsize);
    std::wstring result = _Dest;
    delete []_Dest;
    setlocale(LC_ALL, "C");

    return result;

}
*/

/*
std::wstring UTF2Uni(const char* src, std::wstring &t)
{
    if (src == NULL)
    {
        return L"";
    }

    int size_s = strlen(src);
    int size_d = size_s + 10;          //?

    wchar_t *des = new wchar_t[size_d];
    memset(des, 0, size_d * sizeof(wchar_t));

    int s = 0, d = 0;
    bool toomuchbyte = true; //set true to skip error prefix.

    while (s < size_s && d < size_d)
    {
        unsigned char c = src[s];
        if ((c & 0x80) == 0)
        {
            des[d++] += src[s++];
        }
        else if((c & 0xE0) == 0xC0)  ///< 110x-xxxx 10xx-xxxx
        {
            WCHAR &wideChar = des[d++];
            wideChar  = (src[s + 0] & 0x3F) << 6;
            wideChar |= (src[s + 1] & 0x3F);

            s += 2;
        }
        else if((c & 0xF0) == 0xE0)  ///< 1110-xxxx 10xx-xxxx 10xx-xxxx
        {
            WCHAR &wideChar = des[d++];

            wideChar  = (src[s + 0] & 0x1F) << 12;
            wideChar |= (src[s + 1] & 0x3F) << 6;
            wideChar |= (src[s + 2] & 0x3F);

            s += 3;
        }
        else if((c & 0xF8) == 0xF0)  ///< 1111-0xxx 10xx-xxxx 10xx-xxxx 10xx-xxxx
        {
            WCHAR &wideChar = des[d++];

            wideChar  = (src[s + 0] & 0x0F) << 18;
            wideChar  = (src[s + 1] & 0x3F) << 12;
            wideChar |= (src[s + 2] & 0x3F) << 6;
            wideChar |= (src[s + 3] & 0x3F);

            s += 4;
        }
        else
        {
            WCHAR &wideChar = des[d++]; ///< 1111-10xx 10xx-xxxx 10xx-xxxx 10xx-xxxx 10xx-xxxx

            wideChar  = (src[s + 0] & 0x07) << 24;
            wideChar  = (src[s + 1] & 0x3F) << 18;
            wideChar  = (src[s + 2] & 0x3F) << 12;
            wideChar |= (src[s + 3] & 0x3F) << 6;
            wideChar |= (src[s + 4] & 0x3F);

            s += 5;
        }
    }

    t = des;
    delete[] des;
    des = NULL;

    return t;
}
*/

/*
int Uni2UTF( const std::wstring& strRes, char *utf8, int nMaxSize )
{
    if (utf8 == NULL) {
        return -1;
    }
    int len = 0;
    int size_d = nMaxSize;

    for (std::wstring::const_iterator it = strRes.begin(); it != strRes.end(); ++it)
    {
        wchar_t wchar = *it;
        if (wchar < 0x80)
        {  //
            //length = 1;
            utf8[len++] = (char)wchar;
        }
        else if(wchar < 0x800)
        {
            //length = 2;

            if (len + 1 >= size_d)
                return -1;

            utf8[len++] = 0xc0 | ( wchar >> 6 );
            utf8[len++] = 0x80 | ( wchar & 0x3f );
        }
        else if(wchar < 0x10000 )
        {
            //length = 3;
            if (len + 2 >= size_d)
                return -1;

            utf8[len++] = 0xe0 | ( wchar >> 12 );
            utf8[len++] = 0x80 | ( (wchar >> 6) & 0x3f );
            utf8[len++] = 0x80 | ( wchar & 0x3f );
        }
        else if( wchar < 0x200000 )
        {
            //length = 4;
            if (len + 3 >= size_d)
                return -1;

            utf8[len++] = 0xf0 | ( (int)wchar >> 18 );
            utf8[len++] = 0x80 | ( (wchar >> 12) & 0x3f );
            utf8[len++] = 0x80 | ( (wchar >> 6) & 0x3f );
            utf8[len++] = 0x80 | ( wchar & 0x3f );
        }

    }

    return len;
}
*/
/*
std::string s2utfs(const  std::string&  strSrc)
{
    std::string  strRes;
    std::wstring  wstrUni = s2ws(strSrc);

    char*  chUTF8 = new char[wstrUni.length() * 3];
    memset(chUTF8,0x00,wstrUni.length() * 3);
    Uni2UTF(wstrUni,chUTF8, wstrUni.length() * 3);
    strRes = chUTF8;
    delete  []chUTF8;
    return strRes;
}
*/
/*
std::string  utfs2s(const std::string& strutf)
{
    std::wstring  wStrTmp;
    UTF2Uni( strutf.c_str(),wStrTmp);
    return ws2s(wStrTmp);
}
*/

myfiledialog.h基于QFileDialog实现自定义对话框

#ifndef MYFILEDIALOG_H
#define MYFILEDIALOG_H

#include

class MyFileDialog : public QFileDialog
{
    Q_OBJECT
public:
    MyFileDialog( QWidget * parent = 0
        , int _model = 0
        , const QString & caption = QString()
        , const QString & directory = QString()
        , const QString & filter = QString());
    ~MyFileDialog();
private:
    void init(int _model);
private:
    QString initDir;
    /* data */
};

#endif //MYFILEDIALOG_H

myfiledialog.cpp

#include "myfiledialog.h"

#include

MyFileDialog::MyFileDialog(QWidget * parent
        , int _model
        , const QString & caption
        , const QString & directory
        , const QString & filter )
    : QFileDialog(parent,caption,directory,filter)
    , initDir(directory)
{
    init(_model);
}

MyFileDialog::~MyFileDialog()
{

}

void MyFileDialog::init(int _model)
{
    this->setWindowTitle(tr("File Path Set Dialog"));
    this->setLabelText(QFileDialog::LookIn,tr("MyLookIn"));
    this->setLabelText(QFileDialog::FileName,tr("MyfileName"));
    this->setLabelText(QFileDialog::FileType,tr("MyFileType"));
    QString _appDir = QCoreApplication::applicationDirPath();
    switch(_model)
    {
        case 0:
            this->setAcceptMode(QFileDialog::AcceptSave);
            this->setLabelText(QFileDialog::Accept,tr("mySave"));
            this->setDirectory(_appDir);
            this->setNameFilter(tr("csv Files (*.csv)"));
            break;
        case 1:
            this->setAcceptMode(QFileDialog::AcceptSave);
            this->setLabelText(QFileDialog::Accept,tr("mySave"));
            this->setDirectory(_appDir);
            this->setNameFilter(tr("xml Files (*.xml)"));
            break;
        case 2:
            this->setAcceptMode(QFileDialog::AcceptOpen);
            this->setLabelText(QFileDialog::Accept,tr("myOpen"));
            this->setDirectory(_appDir);
            this->setNameFilter(tr("xml Files (*.xml)"));
            break;
        case 3:
        {

            // this->setAcceptMode(QFileDialog::AcceptOpen);
            this->setFilter(QDir::NoDotAndDotDot);
            this->setFileMode(QFileDialog::Directory);
            this->setOptions(QFileDialog::ReadOnly);
            this->setLabelText(QFileDialog::Accept,tr("myChoose"));
        }
            break;
        case 4:
        {
            this->setAcceptMode(QFileDialog::AcceptOpen);
            this->setLabelText(QFileDialog::Accept,tr("myOpen"));
            this->setNameFilter(tr("sqlite Files (*.db)"));
        }
            break;
        case 5:
        {
            this->setAcceptMode(QFileDialog::AcceptOpen);
            this->setLabelText(QFileDialog::Accept,tr("myOpen"));
            this->setFilter(QDir::Files);
        }
            break;
        case 6:
        {
            this->setAcceptMode(QFileDialog::AcceptSave);
            this->setLabelText(QFileDialog::Accept,tr("mySave"));
            this->setNameFilter(tr("csv Files (*.csv)"));
        }
        case 7:
        {
            this->setAcceptMode(QFileDialog::AcceptOpen);
            this->setLabelText(QFileDialog::Accept,tr("myOpen"));
            this->setDirectory(_appDir);
            this->setNameFilter(tr("csv Files (*.csv);;"
                "ini files (*.ini);;"
                "Text files (*.txt);;"
                "xml files (*.xml);;"
                "Any files (*)"));
        }
            break;
        case 8:
        {
            this->setAcceptMode(QFileDialog::AcceptOpen);
            this->setLabelText(QFileDialog::Accept,tr("myOpen"));
            this->setNameFilter(tr("Any files (*)"));
        }
            break;
        default:
            // this->setDirectory(_appDir);
            break;
    }
    this->setLabelText(QFileDialog::Reject,tr("myCancel"));
}

FileQt.h的FileQt类是一个单体类，可以全局调用，实现txt、csv、ini、xls等文件读取、写入操作，支持文件目录、文件名等查询、读取、删除等操作，可以依据读写文件信息的API扩展新的功能或调整实现新文件类型的支持。

#ifndef FILEQT_H
#define FILEQT_H

#include
#include
#include

class FileQt
{
public:
    static FileQt* getInstance();
    static void Destroy();
    ~FileQt();

    bool writeListInfo(QVector list,const QString path,bool appendf=true);
    bool readListInfo(QVector > &list,const QString path,QString _div);
    bool readListInfo(QVector > &list,const QString path);
    bool readListInfo_dot(QVector > &list,const QString path);
    bool readListInfo(QVector &list,const QString path);
    void getAllFileName_dot(const QString directory, const QString extName, QStringList& fileNames);
    void getAllFileName(const QString& directory, const QString& extName, QStringList& fileNames);
    void getSubDir(const QString& directory, QStringList& subdirs);

    bool readToVectorXML(QString _file, QStringList elements, QVector &_lists);
    bool readToVectorXML(QString _file, QStringList elements, QVector > &_lists,QString _div=",");
#ifdef WIN32
    bool readToVectorXLS(QString _file, QVector > &_lists, int sheet = 1);
#endif
private:
    bool readToVector(QString _file, QVector > &_lists,QString _div="\\s+");
    bool readToVector(QString _file, QVector &_lists);
private:
    FileQt();
    FileQt(const FileQt& ) {} // copy constructor
    FileQt& operator=(const FileQt& ) { return *this; } // assignment operator
private:
    static FileQt* instance;
};

#endif //FILEQT_H

FileQt.cpp

#include "FileQt.h"

#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#ifdef WIN32
#include
#endif

FileQt* FileQt::instance = NULL;
FileQt* FileQt::getInstance()
{
    if(NULL == FileQt::instance)
    {
        FileQt::instance = new FileQt();
    }
    return FileQt::instance;
}

void FileQt::Destroy()
{
    if(NULL!=FileQt::instance){
        delete FileQt::instance;
        FileQt::instance = NULL;
    }
}

FileQt::FileQt()
{
}

FileQt::~FileQt()
{
}

bool FileQt::readListInfo(QVector > &list,const QString path,QString _div)
{
    return readToVector(path,list,_div);
}

bool FileQt::readListInfo(QVector > &list,const QString path)
{
    return readToVector(path,list,"\\W+");
}

bool FileQt::readListInfo_dot(QVector > &list,const QString path)
{
    return readToVector(path,list,",");
}

bool FileQt::readListInfo(QVector &list,const QString path)
{
    return readToVector(path,list);
}

bool FileQt::readToVector(QString _file,QVector > &_lists, QString _div)
{
    #ifdef WIN32
    if (_file.contains(".xls", Qt::CaseInsensitive))
    {
        return readToVectorXLS(_file,_lists);
    }
    #endif
    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));
            // QStringList _strL = line.split(QRegExp(_div),QString::SkipEmptyParts);
            QVector _onev;
            for (int i = 0; i < _strL.size(); i++)
            {
                _onev.push_back(_strL[i]);
            }
            _lists.push_back(_onev);
        }
        file.close();

        if (_lists.empty())
        {
            return false;
        }else{
            return true;
        }
    }
}

bool FileQt::readToVector(QString _file, QVector &_lists)
{
    QFile file(_file);
    if (!file.open(QFile::ReadOnly))
    {
        qDebug() << "conn't open file  " << _file << "!\n";
        return false;
    }else{
        QTextStream stream( &file );
        stream.setCodec("UTF-8"); //中文编码
        QString line;
        while ( !stream.atEnd() ) {
            line = stream.readLine();
            _lists.push_back(line);
        }
        file.close();

        if (_lists.empty())
        {
            return false;
        }else{
            return true;
        }
    }
}

void FileQt::getAllFileName_dot(const QString directory, const QString extName, QStringList& fileNames)
{
    QDir _dir(directory);
    QStringList filters;
    filters << ("*."+extName);
    _dir.setNameFilters(filters);
    _dir.setFilter(QDir::Files | QDir::NoDotAndDotDot | QDir::NoSymLinks);
    _dir.setSorting(QDir::Name);
    fileNames = _dir.entryList();
    qDebug() << fileNames << "\n";
}

void FileQt::getAllFileName(const QString& directory, const QString& extName, QStringList& fileNames)
{
    QDir _dir(directory);
    QStringList filters;
    filters << ("*."+extName);
    _dir.setNameFilters(filters);
    _dir.setFilter(QDir::Files | QDir::NoDotAndDotDot | QDir::NoSymLinks);
    _dir.setSorting(QDir::Name);
    QStringList _files = _dir.entryList();
    for (int i = 0; i < _files.size(); i++)
    {
        fileNames << _files[i].left(_files[i].lastIndexOf("."));
    }
    qDebug() << fileNames << "\n";
}

void FileQt::getSubDir(const QString& directory, QStringList& subdirs)
{
    QDir _dir(directory);
    _dir.setFilter(QDir::Dirs | QDir::NoDotAndDotDot | QDir::NoSymLinks);
    _dir.setSorting(QDir::Name);
    subdirs = _dir.entryList();
    qDebug() << subdirs << "\n";
}

bool FileQt::readToVectorXML(QString _file, QStringList elements, QVector &_lists)
{
    QFile inputFile(_file);
    if(!inputFile.open(QFile::ReadOnly))
    {
        qDebug() << "cann't create file: "<< _file <toUnicode(data);
    QXmlStreamReader reader(str);
    //reader.readNext();

    bool readF = false;
    while (!reader.atEnd()&&!reader.hasError())
    {
        reader.readNext();
        if (reader.error())
        {
            qDebug()< > &_lists,QString _div)
{
    QVector _list;
    if(readToVectorXML(_file,elements,_list)){
        for (int i = 0; i < _list.size(); i++)
        {
            QStringList _linelist = _list[i].split(QRegExp(_div));
            QVector _linevect;
            for (int j = 0; j < _linelist.size(); j++)
            {
                _linevect << _linelist[j];
            }
            _lists.push_back(_linevect);
        }
        return true;
    }
    return false;
}

#ifdef WIN32
bool FileQt::readToVectorXLS(QString _file, QVector > &_lists, int sheet)
{
    if(sheetdynamicCall("SetVisible(bool)", false);//不显示窗体
    excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。

    workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
    workbook = workbooks->querySubObject("Open(QString, QVariant)", _file); //打开工作簿
    qDebug() << "workbooks size = " << workbook->querySubObject("WorkSheets")->property("Count").toInt() << "\n";
    if(sheet>workbook->querySubObject("WorkSheets")->property("Count").toInt()){
        delete workbook;
        workbook = NULL;
        delete workbooks;
        workbooks = NULL;
        delete excel;
        excel = NULL;
        return false;
    }
    QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", sheet);//打开第一个sheet

    QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
    int intRowStart = usedrange->property("Row").toInt();
    int intColStart = usedrange->property("Column").toInt();

    QAxObject * rows = usedrange->querySubObject("Rows");
    int intRows = rows->property("Count").toInt();

    QAxObject * columns = usedrange->querySubObject("Columns");
    int intCols = columns->property("Count").toInt();

    for (int i = intRowStart; i < intRowStart + intRows; i++)//row
    {
        QVector _onev;
        for (int j = intColStart; j < intColStart + intCols; j++)//column
        {
            // QAxObject * range = worksheet->querySubObject("Cells(QVariant,QVariant)", i, j ); //获取单元格
            // // qDebug() << i << j << range->property("Value");
            // _onev.push_back(range->property("Value").toString());

            _onev.push_back(
                worksheet->querySubObject("Cells(QVariant,QVariant)", i, j )->property("Value").toString());
        }
        _lists.push_back(_onev);
    }
    workbook->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭excel

    delete columns;
    columns = NULL;
    delete rows;
    rows = NULL;
    delete usedrange;
    usedrange = NULL;
    delete worksheet;
    worksheet = NULL;
    delete workbook;
    workbook = NULL;
    delete workbooks;
    workbooks = NULL;
    delete excel;
    excel = NULL;

    return true;
}
#endif

bool FileQt::writeListInfo(QVector list,const QString path,bool appendf)
{
    QFile f(path);
    if(!f.open(QFile::WriteOnly | QFile::Text | (appendf?QFile::Append:QFile::Truncate)))
        return false;

    QTextStream out(&f);

    out.setCodec("UTF-8"); //中文编码
    for (int i = 0; i < list.size(); i++)
    {
        out << QString(list[i]);
    }
    f.close();
    return true;
}

提供sqlite数据库文件路径及csv文件目录编辑以及两个转换按钮。

#ifndef INOROUT_CONF_H
#define INOROUT_CONF_H

#include

class QLineEdit;
class QPushButton;
class QLabel;

class InOrOutConf : public QWidget
{
    Q_OBJECT
public:
    InOrOutConf(QWidget * parent = 0,Qt::WindowFlags f = 0);
    ~InOrOutConf();

private slots:
    void setDBPath();
    void setCsvPath();
    void scvToDB();
    void DBToCsv();
private:
    QString divPath;
    QLineEdit *pathEdit;    //db路径编辑框
    QPushButton *pathset;   //db路径打开

    QLineEdit *dirEdit; //文件目录编辑框
    QPushButton *dirset;    //文件目录打开

    QPushButton *inButton;  //csv到db
    QPushButton *outButton; //db到csv
    QPushButton *close; //

    QLabel *warLabel;
};

#endif //INOROUT_CONF_H

inOrOutConf.cpp，注意inYc、outYc等API主要实现关于表格名、列表名特定处理，暂没做抽象提取及可配置实现。

#include "inOrOutConf.h"

#include
#include
#include
#include
#include
#include
#include

#include
#include
#include
#include
#include

#ifdef WIN32
#include
Q_IMPORT_PLUGIN(QSQLiteDriverPlugin)
#endif

#include
#include
#include

// #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() && icountsetMinimumWidth(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 = "<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 = "<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()< > &_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));
            // QStringList _strL = line.split(QRegExp(_div),QString::SkipEmptyParts);
            QVector _onev;
            for (int i = 0; i < _strL.size(); i++)
            {
                _onev.push_back(_strL[i]);
            }
            _lists.push_back(_onev);
        }
        file.close();

        if (_lists.empty())
        {
            return false;
        }else{
            return true;
        }
    }
}

void inYc(QSqlDatabase &db,QString _path)
{
    QVector > _lists;
    readToVector(_path,_lists,",");

    QSqlQuery query(db);
    query.exec("DELETE FROM s101yc");

    QVariantList indexids;
    QVariantList devids;
    QVariantList names;
    QVariantList localaddrs;
    QVariantList coefs;
    QVariantList thvs;
    QVariantList nametomsts;
    QVariantList zerovals;
    QVariantList sampaddrs;
    QVariantList sampdevids;
    QVariantList explains;

    int _count = 0;
    QString _sql = "INSERT INTO s101yc (INDEXID,DEVID,NAME,LOCALADDR,COEF,THV,NAMETOMST,ZEROVAL,SAMPADDR,SAMPDEVID,EXPLAIN) "
                   "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

    for (int i = 0; i < _lists.size(); i++)
    {
        if (11!=_lists[i].size())
        {
            continue;
        }
        indexids << _lists[i][0].toInt();
        devids << _lists[i][1].toInt();
        names << _lists[i][2];
        // names << QString("%1").arg(_lists[i][2].toAscii().data());
        localaddrs << _lists[i][3].toInt();
        coefs << _lists[i][4].toDouble();
        thvs << _lists[i][5].toDouble();
        nametomsts << _lists[i][6];
        // nametomsts << QString("%1").arg(_lists[i][6].toAscii().data());
        zerovals << _lists[i][7].toInt();
        sampaddrs << _lists[i][8].toInt();
        sampdevids << _lists[i][9].toInt();
        explains << _lists[i][10];
        // explains << QString("%1").arg(_lists[i][10].toAscii().data());
        _count+=1;
        if (_count>=200||(i==(_lists.size()-1)))
        {
            try{
                query.prepare(_sql);
                query.addBindValue(indexids);
                query.addBindValue(devids);
                query.addBindValue(names);
                query.addBindValue(localaddrs);
                query.addBindValue(coefs);
                query.addBindValue(thvs);
                query.addBindValue(nametomsts);
                query.addBindValue(zerovals);
                query.addBindValue(sampaddrs);
                query.addBindValue(sampdevids);
                query.addBindValue(explains);

                if (!query.execBatch())
                {
                    qDebug() << query.lastError();
                }
            }catch(...){
                qDebug() << query.lastError();
            }
            indexids.clear();
            devids.clear();
            names.clear();
            localaddrs.clear();
            coefs.clear();
            thvs.clear();
            nametomsts.clear();
            zerovals.clear();
            sampaddrs.clear();
            sampdevids.clear();
            explains.clear();
            _count = 0;
        }

    }
};

void inYx(QSqlDatabase &db,QString _path)
{
    QVector > _lists;
    readToVector(_path,_lists,",");

    QSqlQuery query(db);
    query.exec("DELETE FROM s101yx");

    QVariantList indexids;
    QVariantList devids;
    QVariantList names;
    QVariantList localaddrs;
    QVariantList nots;
    QVariantList sampdevids;
    QVariantList sampaddrs;
    QVariantList nametomsts;

    int _count = 0;
    QString _sql = "INSERT INTO s101yx (INDEXID,DEVID,NAME,LOCALADDR,\"NOT\",SAMPDEVID,SAMPADDR,NAMETOMST) "
                   "VALUES (?,?,?,?,?,?,?,?)";

    for (int i = 0; i < _lists.size(); i++)
    {
        if (8!=_lists[i].size())
        {
            continue;
        }
        indexids << _lists[i][0].toInt();
        devids << _lists[i][1].toInt();
        names << _lists[i][2];
        // names << QString("%1").arg(_lists[i][2].toAscii().data());
        localaddrs << _lists[i][3].toInt();
        nots << _lists[i][4].toInt();
        sampdevids << _lists[i][5].toInt();
        sampaddrs << _lists[i][6].toInt();
        nametomsts << _lists[i][7];
        // nametomsts << QString("%1").arg(_lists[i][7].toAscii().data());

        _count+=1;
        if (_count>=200||(i==(_lists.size()-1)))
        {
            try{
                query.prepare(_sql);
                query.addBindValue(indexids);
                query.addBindValue(devids);
                query.addBindValue(names);
                query.addBindValue(localaddrs);
                query.addBindValue(nots);
                query.addBindValue(sampdevids);
                query.addBindValue(sampaddrs);
                query.addBindValue(nametomsts);

                if (!query.execBatch())
                {
                    qDebug() << query.lastError();
                }
            }catch(...){
                qDebug() << query.lastError();
            }
            indexids.clear();
            devids.clear();
            names.clear();
            localaddrs.clear();
            nots.clear();
            sampdevids.clear();
            sampaddrs.clear();
            nametomsts.clear();

            _count = 0;
        }
    }
};

void inYk(QSqlDatabase &db,QString _path)
{
    QVector > _lists;
    readToVector(_path,_lists,",");

    QSqlQuery query(db);
    query.exec("DELETE FROM s101yk");

    QVariantList indexids;
    QVariantList localaddrs;
    QVariantList devids;
    QVariantList names;
    QVariantList sampaddrs;
    QVariantList sampdevids;
    QVariantList nametomsts;

    int _count = 0;
    QString _sql = "INSERT INTO s101yk (INDEXID,LOCALADDR,DEVID,NAME,SAMPADDR,SAMPDEVID,NAMETOMST) "
                   "VALUES (?,?,?,?,?,?,?)";

    for (int i = 0; i < _lists.size(); i++)
    {
        if (7!=_lists[i].size())
        {
            continue;
        }
        indexids << _lists[i][0].toInt();
        localaddrs << _lists[i][1].toInt();
        devids << _lists[i][2].toInt();
        names << _lists[i][3];
        // names << QString("%1").arg(_lists[i][3].toAscii().data());
        sampaddrs << _lists[i][4].toInt();
        sampdevids << _lists[i][5].toInt();
        nametomsts << _lists[i][6];
        // nametomsts << QString("%1").arg(_lists[i][6].toAscii().data());

        _count+=1;
        if (_count>=200||(i==(_lists.size()-1)))
        {
            try{
                query.prepare(_sql);
                query.addBindValue(indexids);
                query.addBindValue(localaddrs);
                query.addBindValue(devids);
                query.addBindValue(names);
                query.addBindValue(sampaddrs);
                query.addBindValue(sampdevids);
                query.addBindValue(nametomsts);

                if (!query.execBatch())
                {
                    qDebug() << query.lastError();
                }
            }catch(...){
                qDebug() << query.lastError();
            }
            indexids.clear();
            localaddrs.clear();
            devids.clear();
            names.clear();
            sampaddrs.clear();
            sampdevids.clear();
            nametomsts.clear();

            _count = 0;
        }
    }
};

void inYt(QSqlDatabase &db,QString _path)
{
    QVector > _lists;
    readToVector(_path,_lists,",");

    QSqlQuery query(db);
    query.exec("DELETE FROM s101yt");

    QVariantList indexids;
    QVariantList localaddrs;
    QVariantList devids;
    QVariantList names;
    QVariantList sampaddrs;
    QVariantList sampdevids;
    QVariantList nametomsts;

    int _count = 0;
    QString _sql = "INSERT INTO s101yt (INDEXID,LOCALADDR,DEVID,NAME,SAMPADDR,SAMPDEVID,NAMETOMST) "
                   "VALUES (?,?,?,?,?,?,?)";

    for (int i = 0; i < _lists.size(); i++)
    {
        if (7!=_lists[i].size())
        {
            continue;
        }
        indexids << _lists[i][0].toInt();
        localaddrs << _lists[i][1].toInt();
        devids << _lists[i][2].toInt();
        names << _lists[i][3];
        // names << QString("%1").arg(_lists[i][3].toAscii().data());
        sampaddrs << _lists[i][4].toInt();
        sampdevids << _lists[i][5].toInt();
        nametomsts << _lists[i][6];
        // nametomsts << QString("%1").arg(_lists[i][6].toAscii().data());

        _count+=1;
        if (_count>=200||(i==(_lists.size()-1)))
        {
            try{
                query.prepare(_sql);
                query.addBindValue(indexids);
                query.addBindValue(localaddrs);
                query.addBindValue(devids);
                query.addBindValue(names);
                query.addBindValue(sampaddrs);
                query.addBindValue(sampdevids);
                query.addBindValue(nametomsts);

                if (!query.execBatch())
                {
                    qDebug() << query.lastError();
                }
            }catch(...){
                qDebug() << query.lastError();
            }
            indexids.clear();
            localaddrs.clear();
            devids.clear();
            names.clear();
            sampaddrs.clear();
            sampdevids.clear();
            nametomsts.clear();

            _count = 0;
        }
    }
};

void InOrOutConf::scvToDB()
{
    QString _dbn = this->pathEdit->text();
    QFile _file(_dbn);
    if (!_file.exists())
    {
        warLabel->setText("db isn't exist!");
        return;
    }

    QSqlDatabase db;
    if(!checkdb_etconf(db,_dbn))
    {
        warLabel->setText("db isn't table we need!");
        return;
    }
    warLabel->setText("csv to db runing!");
    inYc(db,this->dirEdit->text()+divPath+"s101yc.csv");
    inYx(db,this->dirEdit->text()+divPath+"s101yx.csv");
    inYk(db,this->dirEdit->text()+divPath+"s101yk.csv");
    inYt(db,this->dirEdit->text()+divPath+"s101yt.csv");
    warLabel->setText("csv to db finish!");
    db.close();
};

bool writeListInfo(QVector list,const QString path,bool appendf)
{
    QFile f(path);
    if(!f.open(QFile::WriteOnly | QFile::Text | (appendf?QFile::Append:QFile::Truncate)))
        return false;

    QTextStream out(&f);

    // out.setCodec("UTF-8"); //中文编码
    for (int i = 0; i < list.size(); i++)
    {
        out << QString(list[i]);
    }
    f.close();
    return true;
};

void outYc(QSqlDatabase &db,QString _path)
{
    QSqlQuery query(db);

    QString _sql = "select INDEXID,DEVID,NAME,LOCALADDR,COEF,THV,NAMETOMST,ZEROVAL,SAMPADDR,SAMPDEVID,EXPLAIN from s101yc";
    query.prepare(_sql);
    query.exec();

    QVector list;
    QString _sb =  QObject::tr("shebei");
    QString _cj = QObject::tr("caiji");
    while (query.next()) {
        QString _line = "";
        for (int i = 0; i < 11; i++)
        {
            _line+=query.value(i).toString();
            if (i!=10)
            {
                _line+=",";
            }
        }
        _line+="\n";
        // _line = QString("%1").arg(ASCII2UTF_8(_line.toStdString()).c_str());
        // _line.replace(QString("??"), _sb);
        // _line.replace(QString("?"), _cj);
        list.push_back(_line);
        // char *buf= new char[512];
        // buf = G2U(_line.toStdString().c_str());
        // list.push_back( QString("%1").arg(buf));
        // qDebug()<1000)
        {
            writeListInfo(list,_path,true);
            list.clear();
        }
    }
    writeListInfo(list,_path,true);
    list.clear();
};

void outYx(QSqlDatabase &db,QString _path)
{
    QSqlQuery query(db);

    QString _sql = "select INDEXID,DEVID,NAME,LOCALADDR,\"NOT\",SAMPDEVID,SAMPADDR,NAMETOMST from s101yx";
    query.prepare(_sql);
    query.exec();

    QVector list;
    while (query.next()) {
        QString _line = "";
        for (int i = 0; i < 8; i++)
        {
            _line+=query.value(i).toString();
            if (i!=7)
            {
                _line+=",";
            }
        }
        _line+="\n";
        // _line = QString("%1").arg(ASCII2UTF_8(_line.toStdString()).c_str());
        // _line.replace(QString("??"), QObject::tr("shebei"));
        // _line.replace(QString("?"), QObject::tr("caiji"));
        list.push_back(_line);
        // char *buf= new char[512];
        // buf = G2U(_line.toStdString().c_str());
        // list.push_back( QString("%1").arg(buf));
        // qDebug()<1000)
        {
            writeListInfo(list,_path,true);
            list.clear();
        }
    }
    writeListInfo(list,_path,true);
    list.clear();
};

void outYk(QSqlDatabase &db,QString _path)
{
    QSqlQuery query(db);

    QString _sql = "select INDEXID,LOCALADDR,DEVID,NAME,SAMPADDR,SAMPDEVID,NAMETOMST from s101yk";
    query.prepare(_sql);
    query.exec();

    QVector list;
    while (query.next()) {
        QString _line = "";
        for (int i = 0; i < 7; i++)
        {
            _line+=query.value(i).toString();
            if (i!=6)
            {
                _line+=",";
            }
        }
        _line+="\n";
        // _line = QString("%1").arg(ASCII2UTF_8(_line.toStdString()).c_str());
        // _line.replace(QString("??"), QObject::tr("shebei"));
        // _line.replace(QString("?"), QObject::tr("caiji"));
        list.push_back(_line);
        // char *buf= new char[512];
        // buf = G2U(_line.toStdString().c_str());
        // list.push_back( QString("%1").arg(buf));
        // qDebug()<1000)
        {
            writeListInfo(list,_path,true);
            list.clear();
        }
    }
    writeListInfo(list,_path,true);
    list.clear();
};

void outYt(QSqlDatabase &db,QString _path)
{
    QSqlQuery query(db);

    QString _sql = "select INDEXID,LOCALADDR,DEVID,NAME,SAMPADDR,SAMPDEVID,NAMETOMST from s101yt";
    query.prepare(_sql);
    query.exec();

    QVector list;
    while (query.next()) {
        QString _line = "";
        for (int i = 0; i < 7; i++)
        {
            _line+=query.value(i).toString();
            if (i!=6)
            {
                _line+=",";
            }
        }
        _line+="\n";
        // _line = QString("%1").arg(ASCII2UTF_8(_line.toStdString()).c_str());
        // _line.replace(QString("??"), QObject::tr("shebei"));
        // _line.replace(QString("?"), QObject::tr("caiji"));
        list.push_back(_line);
        // char *buf= new char[512];
        // buf = G2U(_line.toStdString().c_str());
        // list.push_back( QString("%1").arg(buf));
        // qDebug()<1000)
        {
            writeListInfo(list,_path,true);
            list.clear();
        }
    }
    writeListInfo(list,_path,true);
    list.clear();
};
void InOrOutConf::DBToCsv()
{
    QString _dbn = this->pathEdit->text();
    QFile _file(_dbn);
    if (!_file.exists())
    {
        warLabel->setText("db isn't exist!");
        return;
    }
    // s101yc * ycs = new s101yc[10];
    // int ret = GetS101yc(_dbn.toStdString().c_str(),"",ycs,10);
    // if(ret>0){
    //  for (int i = 0; i < ret; i++)
    //  {
    //      qDebug() <<  QString("%1").arg(ycs[i].NAME)<setText("db isn't table we need!");
        return;
    }
    warLabel->setText("db to csv runing!");
    outYc(db,this->dirEdit->text()+divPath+"s101yc.csv");
    outYx(db,this->dirEdit->text()+divPath+"s101yx.csv");
    outYk(db,this->dirEdit->text()+divPath+"s101yk.csv");
    outYt(db,this->dirEdit->text()+divPath+"s101yt.csv");
    warLabel->setText("db to csv finish!");
    db.close();
};

main.cpp

#include
#include
#include

#include "view/inOrOutConf.h"
//主函数入口
int main(int argc, char *argv[])
{
    //app构造
    QApplication app(argc, argv);

    //languages translation, main event is translate Marco tr("")
    QTranslator translator;
    //装载翻译文件
    //lupdate *.pro导出源文代码
    translator.load(":/languages/dbInOrOut_cn.qm");
    //app.setFont(QFont("wenquanyi")); //set font stype lib
    app.installTranslator(&translator);
    // //设置本地语言
    // QTextCodec::setCodecForLocale(QTextCodec::codecForLocale());
    // QTextCodec::setCodecForCStrings(QTextCodec::codecForLocale());
    // QTextCodec::setCodecForTr(QTextCodec::codecForLocale());

    InOrOutConf w;
    w.show();
    return app.exec();
}

四、编译及测试

qmake -o Makefile dbIntOrOut.pro && make debug | make release(win use nmake)

运行效果如下：

五、附件

上面已近给出了全部源码，如果还是无法建立工程，以下是完整工程代码下载连接：

qt实现的sqlites数据库文件与csv文件相互转换案例代码-C++文档类资源-CSDN下载

Original: https://blog.csdn.net/py8105/article/details/127133837
Author: py_free
Title: qt实现sqlite数据库文件与csv文件相互转换