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

使用的XLS解析库

BasicExcel

头文件:

// 王智泉
#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;
}