將XLS文件導成Sqlite數據庫文件

使用的XLS解析庫

BasicExcel

頭文件:

[cpp] 
#pragma once 
 
namespace YExcel 
{ 
    class BasicExcelWorksheet; 
} 
 
struct sqlite3; 
 
class XlsToSqlite 
{ 
public: 
    XlsToSqlite(void); 
    virtual ~XlsToSqlite(void); 
 
    void convert(const char* xlsFile, const char* sqlLiteFile); 
 
private: 
 
    void parserSheet(YExcel::BasicExcelWorksheet* sheet); 
 
    int createTable(YExcel::BasicExcelWorksheet* sheet); 
 
    int insertValue(YExcel::BasicExcelWorksheet* sheet); 
 
private: 
 
    sqlite3* db; 
}; 

cpp:
[cpp]
#include "StdAfx.h" 
 
#include "XlsToSqlite.h" 
#include "BasicExcel.hpp" 
#include "sqlite3.h" 
#include <vector> 
#include <string> 
 
std::wstring s2ws(const std::string& s) 
{ 
    int len; 
    int slength = (int)s.length() + 1; 
    len = MultiByteToWideChar(CP_ACP, 0, s.c_str(), slength, 0, 0);  
    std::wstring r(len, L'\0'); 
    MultiByteToWideChar(CP_ACP, 0, s.c_str(), slength, &r[0], len); 
    return r; 
} 
 
std::string ws2s(const std::wstring& s) 
{ 
    string result;   
    //獲取緩沖區大小，並申請空間，緩沖區大小事按字節計算的   
    int len = WideCharToMultiByte(CP_ACP, 0, s.c_str(), s.size(), NULL, 0, NULL, NULL);   
    char* buffer = new char[len + 1];   
    //寬字節編碼轉換成多字節編碼   
    WideCharToMultiByte(CP_ACP, 0, s.c_str(), s.size(), buffer, len, NULL, NULL);   
    buffer[len] = '\0';   
    //刪除緩沖區並返回值   
    result.append(buffer);   
    delete[] buffer;   
    return result;  
} 
 
using namespace YExcel; 
 
XlsToSqlite::XlsToSqlite(void) 
{ 
} 
 
 
XlsToSqlite::~XlsToSqlite(void) 
{ 
} 
 
void XlsToSqlite::convert(const char* xlsFile, const char* sqlLiteFile) 
{ 
     
    BasicExcel e; 
 
    // 加載excel 
    if (!e.Load(xlsFile)) 
    { 
        MessageBox(NULL, (std::string("打開XLS文件:'") + xlsFile + std::string("'錯誤，請確認文件是否存在，或者被其它程序打開")).c_str(), "錯誤", MB_OK); 
        return; 
    }    
 
    DeleteFile(sqlLiteFile); 
 
    // 加載SQLite 
    int res = sqlite3_open(sqlLiteFile, &db); 
 
    if( res ){ 
        MessageBox(NULL, (std::string("Can't open database: ") + sqlite3_errmsg(db)).c_str(), "", MB_OK); 
        sqlite3_close(db); 
        return; 
    } 
    size_t maxSheets = e.GetTotalWorkSheets(); 
    for (size_t i = 0; i < maxSheets; ++i) 
    { 
        this->parserSheet(e.GetWorksheet(i)); 
    } 
    sqlite3_close(db); 
} 
 
// ====================================================================================== 
void XlsToSqlite::parserSheet(YExcel::BasicExcelWorksheet* sheet) 
{ 
    if (NULL == sheet) 
    { 
        return; 
    } 
 
    // 得到行和列的數量 
    size_t maxRows = sheet->GetTotalRows(); 
    size_t maxCols = sheet->GetTotalCols(); 
    ASSERT(maxCols > 0); 
 
    if (this->createTable(sheet)) 
    { 
        this->insertValue(sheet); 
    }    
} 
 
// ====================================================================================== 
int XlsToSqlite::createTable(YExcel::BasicExcelWorksheet* sheet) 
{ 
 
    // 得到表名 
    std::string tableName = ws2s(sheet->GetUnicodeSheetName()); 
    size_t maxCols = sheet->GetTotalCols(); 
    char* errMsg = NULL; 
     
    // 刪除 
    std::string SQL = "DROP TABLE "; 
    SQL += tableName; 
    int res= sqlite3_exec(db , SQL.c_str() , 0 , 0 , &errMsg); 
    if (res != SQLITE_OK) 
    { 
        std::cout << "執行SQL 出錯." << errMsg << std::endl; 
    } 
     
    SQL.clear(); 
    SQL = "CREATE TABLE " + tableName + " ("; 
    for (size_t c = 0; c < maxCols; ++c) // 得到字段名 
    { 
        BasicExcelCell* cell = sheet->Cell(0, c); 
        if(cell->Type() == BasicExcelCell::UNDEFINED) 
        { 
            std::string errorInfo = "創建表'" + tableName + "'中的字段時出現無法識別字段或空字段."; 
            MessageBox(NULL, errorInfo.c_str(), "錯誤", MB_OK); 
            return FALSE; 
        } 
 
        SQL += ws2s(cell->GetWString()) ; 
        SQL += c < maxCols - 1 ? " varchar(128)," : " varchar(128)"; 
    } 
    SQL += ")"; 
 
    res = sqlite3_exec(db , SQL.c_str() ,0 ,0, &errMsg); 
 
    if (res != SQLITE_OK) 
    { 
        std::string errorInfo = "執行創建table的SQL 出錯."; 
        errorInfo += errMsg; 
        MessageBox(NULL, errorInfo.c_str(), "錯誤", MB_OK); 
        return FALSE; 
    } 
    else 
    { 
        std::cout << "創建table的SQL成功執行."<< std::endl; 
    } 
 
    return TRUE; 
} 
 
// ====================================================================================== 
int XlsToSqlite::insertValue(YExcel::BasicExcelWorksheet* sheet) 
{ 
    // 得到行和列的數量 
    std::string tableName = ws2s(sheet->GetUnicodeSheetName()); 
    size_t maxRows = sheet->GetTotalRows(); 
    size_t maxCols = sheet->GetTotalCols(); 
    char* errMsg = NULL; 
    ASSERT(maxCols > 0); 
 
    // 得到鍵值 
    std::string cellString; 
    char tmpStr[256] = {0}; 
    for (size_t r=1; r<maxRows; ++r) 
    { 
        std::string SQL = "INSERT INTO " + tableName + " VALUES ("; 
        for (size_t c = 0; c < maxCols; ++c) 
        { 
            BasicExcelCell* cell = sheet->Cell(r,c); 
            cellString.clear(); 
            switch (cell->Type()) 
            { 
            case BasicExcelCell::UNDEFINED: 
                printf("          "); 
                break; 
 
            case BasicExcelCell::INT: 
                 
                sprintf(tmpStr, "%10d", cell->GetInteger()); 
                cellString = tmpStr; 
                break; 
 
            case BasicExcelCell::DOUBLE: 
                sprintf(tmpStr, "%10.6lf", cell->GetDouble()); 
                cellString = tmpStr; 
                break; 
 
            case BasicExcelCell::STRING: 
                sprintf(tmpStr, "%10s", cell->GetString()); 
                cellString = tmpStr; 
                break; 
 
            case BasicExcelCell::WSTRING: 
                cellString = ws2s(cell->GetWString()); 
                break; 
            } 
 
            cellString   = c < maxCols - 1 && !cellString.empty() ? "'" + cellString + "'," :  "'" + cellString + "'"; 
            SQL += cellString; 
        } 
        SQL += ")"; 
        int res = sqlite3_exec(db , SQL.c_str() ,0 ,0, &errMsg); 
 
        if (res != SQLITE_OK) 
        { 
            std::cout << "執行創建table的SQL 出錯." << errMsg << std::endl; 
            return FALSE; 
        } 
    } 
    return TRUE; 
}