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

前端之家收集整理的这篇文章主要介绍了将XLS文件导成Sqlite数据库文件前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

使用的XLS解析库

BasicExcel


文件:

  1. // 王智泉
  2. #pragma once
  3.  
  4. namespace YExcel
  5. {
  6. class BasicExcelWorksheet;
  7. }
  8.  
  9. struct sqlite3;
  10.  
  11. class XlsTosqlite
  12. {
  13. public:
  14. XlsTosqlite(void);
  15. virtual ~XlsTosqlite(void);
  16.  
  17. void convert(const char* xlsFile,const char* sqlLiteFile);
  18.  
  19. private:
  20.  
  21. void parserSheet(YExcel::BasicExcelWorksheet* sheet);
  22.  
  23. int createTable(YExcel::BasicExcelWorksheet* sheet);
  24.  
  25. int insertValue(YExcel::BasicExcelWorksheet* sheet);
  26.  
  27. private:
  28.  
  29. sqlite3* db;
  30.  
  31. size_t maxRows;
  32. size_t maxCols;
  33. };


cpp:
  1. // 王智泉
  2. #include "StdAfx.h"
  3.  
  4. #include "XlsTosqlite.h"
  5. #include "BasicExcel.hpp"
  6. #include "sqlite3.h"
  7. #include <vector>
  8. #include <string>
  9.  
  10. std::wstring s2ws(const std::string& s)
  11. {
  12. int len;
  13. int slength = (int)s.length() + 1;
  14. len = MultiByteToWideChar(CP_ACP,s.c_str(),slength,0);
  15. std::wstring r(len,L'\0');
  16. MultiByteToWideChar(CP_ACP,&r[0],len);
  17. return r;
  18. }
  19.  
  20. std::string ws2s(const std::wstring& s)
  21. {
  22. string result;
  23. //获取缓冲区大小,并申请空间,缓冲区大小事按字节计算的
  24. int len = WideCharToMultiByte(CP_ACP,s.size(),NULL,NULL);
  25. char* buffer = new char[len + 1];
  26. //宽字节编码转换成多字节编码
  27. WideCharToMultiByte(CP_ACP,buffer,len,NULL);
  28. buffer[len] = '\0';
  29. //删除缓冲区并返回值
  30. result.append(buffer);
  31. delete[] buffer;
  32. return result;
  33. }
  34.  
  35. std::string s2utf8(const std::string & str)
  36. {
  37. int nwLen = ::MultiByteToWideChar(CP_ACP,str.c_str(),-1,0);
  38.  
  39. wchar_t * pwBuf = new wchar_t[nwLen + 1];//一定要加1,不然会出现尾巴
  40. ZeroMemory(pwBuf,nwLen * 2 + 2);
  41.  
  42. ::MultiByteToWideChar(CP_ACP,str.length(),pwBuf,nwLen);
  43.  
  44. int nLen = ::WideCharToMultiByte(CP_UTF8,NULL);
  45.  
  46. char * pBuf = new char[nLen + 1];
  47. ZeroMemory(pBuf,nLen + 1);
  48.  
  49. ::WideCharToMultiByte(CP_UTF8,nwLen,pBuf,nLen,NULL);
  50.  
  51. std::string retStr(pBuf);
  52.  
  53. delete []pwBuf;
  54. delete []pBuf;
  55.  
  56. pwBuf = NULL;
  57. pBuf = NULL;
  58.  
  59. return retStr;
  60. }
  61.  
  62. using namespace YExcel;
  63.  
  64. XlsTosqlite::XlsTosqlite(void)
  65. : maxRows(0),maxCols(0)
  66. {
  67. }
  68.  
  69.  
  70. XlsTosqlite::~XlsTosqlite(void)
  71. {
  72. }
  73.  
  74. void XlsTosqlite::convert(const char* xlsFile,const char* sqlLiteFile)
  75. {
  76. BasicExcel e;
  77.  
  78. // 加载excel
  79. if (!e.Load(xlsFile))
  80. {
  81. MessageBox(NULL,(std::string("打开XLS文件:'") + xlsFile + std::string("'错误,请确认文件是否存在,或者被其它程序打开")).c_str(),"错误",MB_OK);
  82. return;
  83. }
  84.  
  85. DeleteFile(sqlLiteFile);
  86.  
  87. // 加载sqlite
  88. int res = sqlite3_open(sqlLiteFile,&db);
  89.  
  90. if( res ){
  91. MessageBox(NULL,(std::string("Can't open database: ") + sqlite3_errmsg(db)).c_str(),"",MB_OK);
  92. sqlite3_close(db);
  93. return;
  94. }
  95. size_t maxSheets = e.GetTotalWorkSheets();
  96. for (size_t i = 0; i < maxSheets; ++i)
  97. {
  98. this->parserSheet(e.GetWorksheet(i));
  99. }
  100. sqlite3_close(db);
  101. }
  102.  
  103. // ======================================================================================
  104. void XlsTosqlite::parserSheet(YExcel::BasicExcelWorksheet* sheet)
  105. {
  106. if (NULL == sheet)
  107. {
  108. return;
  109. }
  110.  
  111. if (this->createTable(sheet))
  112. {
  113. this->insertValue(sheet);
  114. }
  115. }
  116.  
  117. // ======================================================================================
  118. int XlsTosqlite::createTable(YExcel::BasicExcelWorksheet* sheet)
  119. {
  120.  
  121. // 得到表名
  122. std::string tableName = ws2s(sheet->GetUnicodeSheetName());
  123. // 得到行和列的数量
  124. maxRows = sheet->GetTotalRows();
  125. maxCols = sheet->GetTotalCols();
  126. char* errMsg = NULL;
  127. // 删除
  128. std::string sql = "DROP TABLE ";
  129. sql += tableName;
  130. int res= sqlite3_exec(db,sql.c_str(),&errMsg);
  131. if (res != sqlITE_OK)
  132. {
  133. std::cout << "执行sql 出错." << errMsg << std::endl;
  134. }
  135. sql.clear();
  136. sql = "CREATE TABLE " + tableName + " (";
  137. std::string slipt;
  138. for (size_t c = 0; c < maxCols; ++c) // 得到字段名
  139. {
  140. BasicExcelCell* cell = sheet->Cell(0,c);
  141. if(cell->Type() == BasicExcelCell::UNDEFINED || c >= maxCols)
  142. {
  143. slipt.empty();
  144. maxCols = c; // 表格的宽度只到最后一个非空字段
  145. break;
  146. }
  147. else
  148. {
  149. sql += slipt;
  150. slipt = ",";
  151. }
  152.  
  153. sql += ws2s(cell->GetWString()) + " varchar(0)";
  154. }
  155. sql += ")";
  156.  
  157. //MessageBox(NULL,"哈哈",MB_OK);
  158.  
  159. res = sqlite3_exec(db,&errMsg);
  160.  
  161. if (res != sqlITE_OK)
  162. {
  163. std::string errorInfo = "执行创建table的sql 出错.";
  164. errorInfo += errMsg;
  165. MessageBox(NULL,errorInfo.c_str(),MB_OK);
  166. return FALSE;
  167. }
  168. else
  169. {
  170. std::cout << "创建table的sql成功执行."<< std::endl;
  171. }
  172.  
  173. return TRUE;
  174. }
  175.  
  176. // ======================================================================================
  177. int XlsTosqlite::insertValue(YExcel::BasicExcelWorksheet* sheet)
  178. {
  179. // 得到行和列的数量
  180. std::string tableName = ws2s(sheet->GetUnicodeSheetName());
  181. char* errMsg = NULL;
  182. ASSERT(maxCols > 0);
  183.  
  184. // 得到键值
  185. std::string cellString;
  186. char tmpStr[256] = {0};
  187. for (size_t r=1; r<maxRows; ++r)
  188. {
  189. std::string sql = "INSERT INTO " + tableName + " VALUES (";
  190. for (size_t c = 0; c < maxCols; ++c)
  191. {
  192. BasicExcelCell* cell = sheet->Cell(r,c);
  193. cellString.clear();
  194. switch (cell->Type())
  195. {
  196. case BasicExcelCell::UNDEFINED:
  197. printf(" ");
  198. break;
  199.  
  200. case BasicExcelCell::INT:
  201. sprintf(tmpStr,"%10d",cell->GetInteger());
  202. cellString = tmpStr;
  203. break;
  204.  
  205. case BasicExcelCell::DOUBLE:
  206. sprintf(tmpStr,"%10.6lf",cell->GetDouble());
  207. cellString = tmpStr;
  208. break;
  209.  
  210. case BasicExcelCell::STRING:
  211. {
  212. sprintf(tmpStr,"%10s",cell->GetString());
  213. cellString = tmpStr;
  214. cellString = s2utf8(cellString); // 如果是字符串,将其转换成UTF-8编码
  215. }
  216. break;
  217.  
  218. case BasicExcelCell::WSTRING:
  219. {
  220. cellString = ws2s(cell->GetWString());
  221. cellString = s2utf8(cellString); // 如果是字符串,将其转换成UTF-8编码
  222. }
  223. break;
  224. }
  225.  
  226. cellString = c < maxCols - 1 && !cellString.empty() ? "'" + cellString + "'," : "'" + cellString + "'";
  227. sql += cellString;
  228. }
  229. sql += ")";
  230. int res = sqlite3_exec(db,&errMsg);
  231.  
  232. if (res != sqlITE_OK)
  233. {
  234. std::string errorInfo = "执行 sql 出错.";
  235. errorInfo += errMsg;
  236. MessageBox(NULL,MB_OK);
  237. return FALSE;
  238. }
  239. }
  240. return TRUE;
  241. }

猜你在找的Sqlite相关文章