|
|
// 设计目的: 直接读取xls文件, 替代ini
// Open与Close必须成对出现,否则将不能再次读取数据
// 打开一个xls文件的消耗非常大,所以不要在游戏循环里做Open
#pragma once
#ifndef _WINDOWS_
#include <afxdisp.h>
#include <afxdb.h>
#endif
#include <odbcinst.h>
#include <string>
#include <vector>
#include <map>
class YXLSReader
{
public:
YXLSReader();
virtual ~YXLSReader();
// 装载xls文件
// pFile: xls文件名
// pTable: 表头名
BOOL Open(const LPSTR pFile, const LPSTR pTable);
// 清除内存中所有表单数据
VOID Close();
// 得到一个浮点数
// nRowID: excel表中第一列的数字ID号
// lpKey: excel表中自定义的第一排的属性项
// fDefault: 如果读取失败返回一个默认值
FLOAT GetFloat(size_t nRowID, LPSTR lpKey, FLOAT fDefault);
// 得到一个整数
// nRowID: excel表中第一列的数字ID号
// lpKey: excel表中自定义的第一排的属性项
// nDefault: 如果读取失败返回一个默认值
UINT GetInt(size_t nRowID, LPSTR lpKey, UINT nDefault);
// 得到一个字符串
// nRowID: excel表中第一列的数字ID号
// lpKey: excel表中自定义的第一排的属性项
// lpDefault: 如果读取失败返回一个默认值
// lpOut: 输出缓冲
// dwSize: 输出缓冲大小
VOID GetString(size_t nRowID, LPSTR lpKey, LPSTR lpDefault, LPSTR lpOut, DWORD dwSize);
// 得到一个字符串
// nRowID: excel表中第一列的数字ID号
// lpKey: excel表中自定义的第一排的属性项
VOID GetString(size_t nRowID, LPSTR lpKey, LPSTR lpDefault, std::string& strOut);
// 得到第 nRowID 行所有key 对应的字符串形式值
// pair-first: key
// pair-second: value
BOOL GetStringRow(size_t nRowID, std::vector<std::pair<std::string, std::string> >& vOut);
// 得到一共有多少行
size_t GetTotalRow() { return m_vIniFormat.size(); }
// 分析一个字符串的类型
// 返回 1 整形 例如 -1 0 100
// 2 浮点 例如 0.1 1.93 -10.2
// 3 字符串 例如 aa bbs ddees
int GetType(const std::string& strIn);
int GetType(const LPSTR pIn);
private:
BOOL GetDriver(std::string& strDriver);
BOOL GetValue(size_t nRowID, LPSTR lpKey, std::map<std::string, std::string>::iterator& iter);
private:
std::vector<std::map<std::string, std::string> > m_vIniFormat;
BOOL m_bOpen;
};
//------------------------------------------------------------------------------------------------
#include "xlsReader.h"
#include <algorithm>
YXLSReader::~YXLSReader()
{
m_vIniFormat.clear();
}
YXLSReader::YXLSReader()
{
m_bOpen = FALSE;
}
BOOL YXLSReader::GetDriver(std::string& strDriver)
{
CHAR cBuf[2000];
WORD wBufOut;
if( !SQLGetInstalledDrivers( cBuf, 2000, &wBufOut) )
return FALSE;
PCHAR pBuf = cBuf;
do
{
if( strstr( pBuf, "Excel" ) != 0 )
{
strDriver = pBuf;
return TRUE;
}
pBuf = strchr( pBuf, '\0' ) + 1;
}while( pBuf[1] != '\0' );
return FALSE;
}
BOOL YXLSReader::Open(const LPSTR pFile, const LPSTR pTable)
{
if( m_bOpen )
return FALSE;
CString sDsn;
std::string strDriver;
if( !GetDriver( strDriver ) )
return FALSE;
CHAR cDsn[1024];
sprintf( cDsn, "DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=TRUE;DBQ=%s", strDriver.c_str(), pFile );
CDatabase* pDatabase = new CDatabase;
try
{
if( !pDatabase->OpenEx( cDsn, CDatabase: penReadOnly | CDatabase::noOdbcDialog ) )
{
pDatabase->Close();
return FALSE;
}
}
catch(...)
{
pDatabase->Close();
return FALSE;
}
CRecordset* pSheet = new CRecordset( pDatabase );
CHAR cSQL[1024];
sprintf( cSQL, "SELECT * FROM [%s$A1:IV65536]", pTable );
try
{
if( !pSheet->Open( AFX_DB_USE_DEFAULT_TYPE, cSQL, CRecordset::readOnly ) )
{
pSheet->Close();
pDatabase->Close();
return FALSE;
}
}
catch(...)
{
pSheet->Close();
pDatabase->Close();
return FALSE;
}
std::vector<std::string> vRowKey;
if( pSheet->m_nResultCols )
{
// 读出所有key
for( SWORD i = 0; i < pSheet->m_nResultCols; ++i )
{
if( find( vRowKey.begin(), vRowKey.end(), pSheet->m_rgODBCFieldInfos.m_strName.GetBuffer() ) != vRowKey.end() )
{
// 有重复key输出警告
printf( "warning: same key %s\n", pSheet->m_rgODBCFieldInfos.m_strName.GetBuffer() );
continue;
}
vRowKey.push_back( /*strlwr(*/ pSheet->m_rgODBCFieldInfos.m_strName.GetBuffer() /*)*/ );
}
}
std::map<std::string, std::string> mTemp;
int nCount(0);
while( !pSheet->IsEOF() )
{
mTemp.clear();
for( SWORD i = 0; i < pSheet->m_nResultCols; ++i )
{
CString strValue;
pSheet->GetFieldValue( i, strValue );
// 去除整数后面的"."如6在excel中表示为6.0
std::string strTemp( strValue );
std::string::size_type nPos = strTemp.find( "." );
std::string::size_type nPosBak(nPos++);
bool bDelete(true);
if( nPos != std::string::npos )
{
for( ; nPos < strTemp.size(); ++nPos )
{
if( strTemp[nPos] != '0' )
{
bDelete = false;
break;
}
}
}
if( bDelete && nPosBak != std::string::npos )
strTemp.erase( strTemp.begin() + nPosBak, strTemp.end() );
std::string strKey = vRowKey;
mTemp[vRowKey] = strTemp/*strlwr( strValue.GetBuffer() )*/;
}
m_vIniFormat.push_back( mTemp );
pSheet->MoveNext();
}
pSheet->Close();
pDatabase->Close();
m_bOpen = TRUE;
delete pSheet;
delete pDatabase;
return TRUE;
}
VOID YXLSReader::Close()
{
m_bOpen = FALSE;
m_vIniFormat.clear();
}
BOOL YXLSReader::GetValue(size_t nRowID, LPSTR lpKey, std::map<std::string, std::string>::iterator& iter)
{
if( nRowID > m_vIniFormat.size() + 1 || nRowID <= 1 )
return FALSE;
iter = m_vIniFormat[nRowID-2].find( lpKey );
if( iter == m_vIniFormat[nRowID-2].end() )
return FALSE;
return TRUE;
}
FLOAT YXLSReader::GetFloat(size_t nRowID, LPSTR lpKey, FLOAT fDefault)
{
std::map<std::string, std::string>::iterator iter;
if( !GetValue( nRowID, lpKey, iter ) )
return fDefault;
return float( atof( iter->second.c_str() ) );
}
UINT YXLSReader::GetInt(size_t nRowID, LPSTR lpKey, UINT nDefault)
{
std::map<std::string, std::string>::iterator iter;
if( !GetValue( nRowID, lpKey, iter ) )
return nDefault;
return UINT( atol( iter->second.c_str() ) );
}
VOID YXLSReader::GetString(size_t nRowID, LPSTR lpKey, LPSTR lpDefault, LPSTR lpOut, DWORD dwSize)
{
std::map<std::string, std::string>::iterator iter;
if( !GetValue( nRowID, lpKey, iter ) )
{
strncpy( lpOut, lpDefault, dwSize );
return;
}
strncpy( lpOut, iter->second.c_str(), dwSize );
}
VOID YXLSReader::GetString(size_t nRowID, LPSTR lpKey, LPSTR lpDefault, std::string& strOut)
{
std::map<std::string, std::string>::iterator iter;
if( !GetValue( nRowID, lpKey, iter ) )
{
strOut = lpDefault;
return;
}
strOut = iter->second;
}
BOOL YXLSReader::GetStringRow(size_t nRowID, std::vector<std::pair<std::string, std::string> >& vOut)
{
if( nRowID > m_vIniFormat.size() + 1 || nRowID <= 1 )
return FALSE;
for( std::map<std::string, std::string>::iterator iter = m_vIniFormat[nRowID-2].begin(); iter != m_vIniFormat[nRowID-2].end(); ++iter )
{
//if( iter->second != "" )
// vOut.push_back( *iter );
std::string strFirst = iter->first;
std::string strSecond = iter->second;
vOut.push_back( *iter );
}
return TRUE;
}
int YXLSReader::GetType(const std::string& strIn)
{
// 查看是否有"."
std::string::size_type nPos = strIn.find( "." );
// 有可能是个整数或者字符串
if( nPos == std::string::npos )
{
// 判断是否为整数
for( size_t i = 0; i < strIn.size(); ++i )
{
if( !i && strIn[0] == '-' )
continue;
if( strIn != '\0' && strIn < '0' || strIn > '9' )
return 3;
}
return 1;
}
else
{
// '.'两边都有数字才认为是浮点数
if( !nPos )
return 3;
if( strIn[nPos-1] < '0' ||
strIn[nPos-1] > '9' ||
strIn[nPos+1] < '0' ||
strIn[nPos+1] > '9' )
return 3;
// 判断是否为浮点数
for( size_t i = 0; i < strIn.size(); ++i )
{
if( !i && strIn[0] == '-' )
continue;
if( strIn == '.' )
{
if( i != nPos )
return 3;
else
continue;
}
if( strIn != '\0' && strIn < '0' || strIn > '9' )
return 3;
}
return 2;
}
}
int YXLSReader::GetType(const LPSTR pIn)
{
std::string strIn( pIn );
return GetType( strIn );
}
//--------------------------------------------------------------------------------------------------------------
#include "xlsReader.h"
#include <hash_map>
#pragma warning( disable: 4996 )
class CTimer
{
public:
CTimer() { QueryPerformanceFrequency( &m_LargeInt ); m_dfFreq = (double)m_LargeInt.QuadPart; }
inline void SetBeginPerformace() { QueryPerformanceCounter( &m_LargeInt ); m_nBeginTimes = m_LargeInt.QuadPart; }
inline void SetEndPerformace() { QueryPerformanceCounter( &m_LargeInt ); m_nEndTimes = m_LargeInt.QuadPart; }
inline double GetLapseTime() { return (m_nEndTimes - m_nBeginTimes) / m_dfFreq; }
private:
LONGLONG m_nBeginTimes;
LONGLONG m_nEndTimes;
LARGE_INTEGER m_LargeInt;
double m_dfFreq;
};
// ------------------------Sec--------------------------------Key----------Value-----------------
typedef std::hash_map<std::string, std::vector<std::pair<std::string, std::string> > > INIFORMAT;
BOOL GetSectionKey(const std::string& strIn, std::string& strSec, std::string& strKey)
{
std::string::size_type nPos = strIn.find( "@@" );
if( nPos == std::string::npos )
return FALSE;
strSec.clear();
strKey.clear();
strSec.insert( strSec.begin(), strIn.begin(), strIn.begin() + nPos );
strKey.insert( strKey.begin(), strIn.begin() + nPos + 2, strIn.end() );
return TRUE;
}
int main()
{
CTimer timer;
UINT unTotal = GetPrivateProfileInt( "total_export", "count", -1, ".\\config.ini" );
if( unTotal == -1 )
{
printf( "无法获取需要导出的表单总数,请检查config.ini:[total_export]\n" );
printf( "按Enter键退出\n" );
getchar();
return 0;
}
else if( !unTotal )
{
printf( "没有需要导出的表单, 请检查config.ini:[total_export]\n" );
printf( "按Enter键退出\n" );
getchar();
return 0;
}
std::vector<std::pair<std::string, std::string> > vFileSheet;
for( UINT i = 1; i <= unTotal; ++i )
{
char cSec[MAX_PATH], cFile[MAX_PATH], cSheet[MAX_PATH];
sprintf( cSec, "export_%d", i );
GetPrivateProfileString( cSec, "file", "", cFile, MAX_PATH, ".\\config.ini" );
GetPrivateProfileString( cSec, "sheet", "", cSheet, MAX_PATH, ".\\config.ini" );
vFileSheet.push_back( std::make_pair( cFile, cSheet ) );
}
YXLSReader xlsReader;
for( size_t i = 0; i < vFileSheet.size(); ++i )
{
timer.SetBeginPerformace();
if( !xlsReader.Open( (LPSTR)vFileSheet.first.c_str(), (LPSTR)vFileSheet.second.c_str() ) )
{
printf( "不能打开文件%s的%s表单,此表将被忽略\n", vFileSheet.first.c_str(), vFileSheet.second.c_str() );
continue;
}
timer.SetEndPerformace();
printf( "装载%s-%s...用时:%f(ms)\n", vFileSheet.first.c_str(), vFileSheet.second.c_str(), timer.GetLapseTime() * 1000.0f );
std::vector<std::vector<std::pair<std::string, std::string> > > vTable;
vTable.resize( xlsReader.GetTotalRow() );
for( size_t j = 1; j <= xlsReader.GetTotalRow(); ++j )
{
if( !xlsReader.GetStringRow( j + 1, vTable[j-1] ) )
DebugBreak();
}
printf( "数据处理...\n" );
INIFORMAT hmIniFormat;
std::string strSaveFile;
for( size_t j = 0; j < vTable.size(); ++j )
{
for( size_t k = 0; k < vTable[j].size(); ++k )
{
// 整理具有相同sec的属性项
std::string strSec, strKey;
if( GetSectionKey( vTable[j][k].first, strSec, strKey ) )
{
INIFORMAT::iterator iter = hmIniFormat.find( strSec );
if( iter != hmIniFormat.end() )
iter->second.push_back( std::make_pair( strKey, vTable[j][k].second ) );
else
hmIniFormat[strSec].push_back( std::make_pair( strKey, vTable[j][k].second ) );
}
else if( vTable[j][k].first == "SaveFile" )
{
strSaveFile.clear();
strSaveFile += ".\\\\export\\\\";
strSaveFile += vTable[j][k].second;
}
else
{
if( vTable[j][k].first == "resid" )
continue;
for( size_t x = 0; x < vTable[j].size(); ++x )
{
if( vTable[j][x].first == "resid" )
{
strSec = "resfile_";
strSec += vTable[j][x].second;
}
}
INIFORMAT::iterator iter = hmIniFormat.find( strSec );
if( iter != hmIniFormat.end() )
iter->second.push_back( std::make_pair( vTable[j][k].first, vTable[j][k].second ) );
else
hmIniFormat[strSec].push_back( std::make_pair( vTable[j][k].first, vTable[j][k].second ) );
}
}
if( !strSaveFile.empty() )
{
for( INIFORMAT::iterator iter = hmIniFormat.begin(); iter != hmIniFormat.end(); ++iter )
{
for( size_t i = 0; i < iter->second.size(); ++i )
WritePrivateProfileString( iter->first.c_str(), iter->second.first.c_str(), iter->second.second.c_str(), strSaveFile.c_str() );
}
}
hmIniFormat.clear();
}
xlsReader.Close();
}
printf( "完成,按Enter键退出\n" );
getchar();
return 0;
}
|
|
发表于 2006-8-7 18:52:00
