使用的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, 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;
// 加载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() , 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;
}