将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 += ")";

补充：软件开发 , C++ ,