使用的XLS解析库
头文件:
// 王智泉 #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; size_t maxRows; size_t maxCols; };
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,s.c_str(),slength,0); std::wstring r(len,L'\0'); MultiByteToWideChar(CP_ACP,&r[0],len); return r; } std::string ws2s(const std::wstring& s) { string result; //获取缓冲区大小,并申请空间,缓冲区大小事按字节计算的 int len = WideCharToMultiByte(CP_ACP,s.size(),NULL,NULL); char* buffer = new char[len + 1]; //宽字节编码转换成多字节编码 WideCharToMultiByte(CP_ACP,buffer,len,NULL); buffer[len] = '\0'; //删除缓冲区并返回值 result.append(buffer); delete[] buffer; return result; } std::string s2utf8(const std::string & str) { int nwLen = ::MultiByteToWideChar(CP_ACP,str.c_str(),-1,0); wchar_t * pwBuf = new wchar_t[nwLen + 1];//一定要加1,不然会出现尾巴 ZeroMemory(pwBuf,nwLen * 2 + 2); ::MultiByteToWideChar(CP_ACP,str.length(),pwBuf,nwLen); int nLen = ::WideCharToMultiByte(CP_UTF8,NULL); char * pBuf = new char[nLen + 1]; ZeroMemory(pBuf,nLen + 1); ::WideCharToMultiByte(CP_UTF8,nwLen,pBuf,nLen,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; // 加载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; } 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(),&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,"哈哈",MB_OK); res = sqlite3_exec(db,&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,&errMsg); if (res != sqlITE_OK) { std::string errorInfo = "执行 sql 出错."; errorInfo += errMsg; MessageBox(NULL,MB_OK); return FALSE; } } return TRUE; }