将XLS文件导成Sqlite数据库文件

使用的XLS解析库

BasicExcel

头文件:

// 王智泉#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; }std::string s2utf8(const std::string & str) { int nwLen = ::MultiByteToWideChar(CP_ACP, 0, str.c_str(), -1, NULL, 0); wchar_t * pwBuf = new wchar_t[nwLen + 1];//一定要加1，不然会出现尾巴 ZeroMemory(pwBuf, nwLen * 2 + 2); ::MultiByteToWideChar(CP_ACP, 0, str.c_str(), str.length(), pwBuf, nwLen); int nLen = ::WideCharToMultiByte(CP_UTF8, 0, pwBuf, -1, NULL, NULL, NULL, NULL); char * pBuf = new char[nLen + 1]; ZeroMemory(pBuf, nLen + 1); ::WideCharToMultiByte(CP_UTF8, 0, pwBuf, nwLen, pBuf, nLen, NULL, NULL); std::string retStr(pBuf); delete []pwBuf; delete []pBuf; pwBuf = NULL; pBuf  = NULL; return retStr; } using namespace YExcel;XlsToSqlite::XlsToSqlite(void): maxRows(0), maxCols(0){}XlsToSqlite::~XlsToSqlite(void){}void XlsToSqlite::convert(const char* xlsFile, const char* sqlLiteFile){BasicExcel e;// 加载excelif (!e.Load(xlsFile)){MessageBox(NULL, (std::string("打开XLS文件:'") + xlsFile + std::string("'错误，请确认文件是否存在，或者被其它程序打开")).c_str(), "错误", MB_OK);return;}DeleteFile(sqlLiteFile);// 加载SQLiteint 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;}if (this->createTable(sheet)){this->insertValue(sheet);}}// ======================================================================================int XlsToSqlite::createTable(YExcel::BasicExcelWorksheet* sheet){// 得到表名std::string tableName = ws2s(sheet->GetUnicodeSheetName());// 得到行和列的数量maxRows = sheet->GetTotalRows();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 + " (";std::string slipt;for (size_t c = 0; c < maxCols; ++c)// 得到字段名{BasicExcelCell* cell = sheet->Cell(0, c);if(cell->Type() == BasicExcelCell::UNDEFINED || c >= maxCols){slipt.empty();maxCols = c;// 表格的宽度只到最后一个非空字段break;}else{SQL += slipt;slipt = ",";}SQL += ws2s(cell->GetWString()) + " varchar(0)";}SQL += ")";//MessageBox(NULL, SQL.c_str(), "哈哈", MB_OK);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());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;cellString = s2utf8(cellString);// 如果是字符串，将其转换成UTF-8编码}break;case BasicExcelCell::WSTRING:{cellString = ws2s(cell->GetWString());cellString = s2utf8(cellString);// 如果是字符串，将其转换成UTF-8编码}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::string errorInfo = "执行　SQL 出错.";errorInfo +=  errMsg;MessageBox(NULL, errorInfo.c_str(), "错误", MB_OK);return FALSE;}}return TRUE;}