使用的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;
- }