excel.cpp

&#61557 职责描述：

// Excel.cpp: implementation of the CExcel class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "Excel.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CExcel::CExcel()
{

}

CExcel::~CExcel()
{

}

CExcel::CExcel( CString strFile , CString strSheetOrSpread , BOOL bBackup ):
	m_sFile( strFile ) , m_TotalColumns( 0 ) , m_TotalLows( 0 ) ,
	m_pDatabase ( NULL ) , m_pSet( NULL )
{
	ASSERT( !strFile.IsEmpty() );
	ASSERT( !strSheetOrSpread.IsEmpty() );

	CString strtemp = strFile ;
	strtemp = strtemp.Right( 4 );
	strtemp.MakeLower();

	if( strtemp == ".xls" )
	{
		m_bExcel = TRUE;
		m_sSpread = ",;,?";
	}
	else
	{
		m_bExcel = FALSE ;
	}

	m_sSheetName = strSheetOrSpread;

	if( m_bExcel )	//是excel 文件
	{
		m_pDatabase = new CDatabase;

		//搜索驱动程序
		this->GetExcelDriver();

		//设置 DSN
		m_sDsn.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", m_sExcelDriver, m_sFile, m_sFile);
			
		
		if( Open() )
		{
			//AfxMessageBox( "a" );
		}


		//AfxMessageBox( "AA" );
	}
}

void CExcel::Commit()
{

}

CString CExcel::GetExcelDriver()
{
	char szBuf[2001];
	WORD cbBufMax = 2000;
	WORD cbBufOut;
	char *pszBuf = szBuf;

	// 获得驱动器列表
	if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
	{
		m_sExcelDriver = "";
	}

	//搜索驱动器
	do
	{
		if( strstr( pszBuf , "Excel" ) != 0 ) //找到 excel 驱动
		{
			m_sExcelDriver = CString( pszBuf );
			break;
		}
		//放置下一个位置
		pszBuf = strchr( pszBuf , '\0' ) + 1 ;

	}while( pszBuf[1] != '\0' );
	

	return m_sExcelDriver;
}

BOOL CExcel::Open()
{

	if( m_bExcel )
	{
		//打开数据库
		m_pDatabase->OpenEx( m_sDsn, CDatabase::noOdbcDialog );
		//打开 sheet
		m_pSet = new CRecordset( m_pDatabase );
		//固定语法的 SQL 语句
		m_sSql.Format( "SELECT * FROM [%s$A1:IV65536]" , m_sSheetName );

		try
		{
			//打开表 只读
			//{"SELECT * FROM [demo$A1:IV65536]"}
			m_pSet->Open( CRecordset::forwardOnly, m_sSql, CRecordset::readOnly);
			
		}
		catch( ... )
		{
			delete m_pSet ;
			m_pSet = NULL;
			m_pDatabase->Close();
			return FALSE;
		}

		if( (m_TotalColumns = m_pSet->m_nResultCols) != 0 )
		{
			m_aRows.RemoveAll();
			m_bAppend = TRUE;

			//计算行数
			m_TotalLows++;

			CString strtemp;
			CString strtempSql;

			for( int i = 0 ; i < m_TotalColumns ; i++  )
			{
				strtempSql = m_pSet->m_rgODBCFieldInfos[i].m_strName;
				m_aFildName.Add( strtempSql );

				if( i != m_TotalColumns -1 )
				{
					strtemp  = strtemp + "\"" + strtempSql + "\"" + m_sSpread ;
				}
				else	//最后一行
				{
					strtemp = strtemp + "\"" + strtempSql + "\"";
				}
			}

			//读头
			this->m_aRows.Add( strtemp );

			//
			while( !m_pSet->IsEOF() )
			{
				this->m_TotalLows++;
				try
				{
					strtemp.Empty();

					for( short ncol = 0 ; ncol < m_TotalColumns ; ncol++ )
					{
						m_pSet->GetFieldValue( ncol , strtempSql );
						if( ncol != m_TotalColumns - 1)
						{
							strtemp = strtemp + "\"" + strtempSql + "\"" + m_sSpread;
						}
						else
						{
							strtemp = strtemp + "\"" + strtempSql + "\"";
						}
					}
					m_aRows.Add( strtemp );
					m_pSet->MoveNext();
				}
				catch( ... )
				{
					delete m_pSet ;
					m_pSet = NULL;
					m_pDatabase->Close();
					return FALSE;
				}
				
			}
		}
		m_pSet->Close();
		delete m_pSet;
		m_pSet = NULL;
		m_pDatabase->Close();
		return TRUE;
	}
	//else


	return TRUE;
}

short CExcel::GetTotalCuloumns()
{
	return m_TotalColumns;
}

short CExcel::GetTotalLows()
{
	return m_TotalLows;
}

BOOL CExcel::ReadRow( CStringArray &strarray , int nRow )
{
	//ASSERT( nRow < m_aRows.GetSize() );

	if( nRow > m_aRows.GetSize() )
	{
		AfxMessageBox( " 不存在该行 " );
		return FALSE;
	}

	CString str;

	//清空
	strarray.RemoveAll();

	str = m_aRows.GetAt( nRow );
	AfxMessageBox( str );


	return TRUE;
}

CString CExcel::ReadRow( int nRow  )
{
	return ( m_aRows.GetAt( nRow ) );
}

CString CExcel::Read( CString &str , int nRow , int nCuloumn )
{
	
	if( ( nCuloumn > m_TotalColumns ) || ( nRow > m_TotalLows ) )
	{
		return "";
	}

	CString sSpread;
	sSpread.Format( "%s" , m_sSpread );
	
	CString strl;
	CString strRow = ReadRow( nRow );
	CString strr = strRow;
	
	int nPos = 0 ;
	for( int index = 0 ; index < nCuloumn -1 ; index ++ )
	{
		nPos = strr.Find( sSpread );
		strl = strr.Left( nPos );
		strr = strr.Right( strr.GetLength() - strl.GetLength() - sSpread.GetLength() );
	}

	nPos = strr.Find( sSpread );
	if( nPos != -1 )
	{
		strr = strr.Left(nPos);
	}

	//去掉引号

	strr = strr.Left( strr.GetLength() -1 );
	strr = strr.Right( strr.GetLength() - 1 );

	str = strr;

	return strr;
}

CString CExcel::Read( int nRow  , int nCuloumn )
{
	if( ( nCuloumn > m_TotalColumns ) || ( nRow > m_TotalLows ) )
	{
		return "";
	}

	CString sSpread;
	sSpread.Format( "%s" , m_sSpread );
	
	CString strl;
	CString strRow = ReadRow( nRow );
	CString strr = strRow;
	
	int nPos = 0 ;
	for( int index = 0 ; index < nCuloumn -1 ; index ++ )
	{
		nPos = strr.Find( sSpread );
		strl = strr.Left( nPos );
		strr = strr.Right( strr.GetLength() - strl.GetLength() - sSpread.GetLength() );
	}

	nPos = strr.Find( sSpread );
	if( nPos != -1 )
	{
		strr = strr.Left(nPos);
	}

	//去掉引号

	strr = strr.Left( strr.GetLength() -1 );
	strr = strr.Right( strr.GetLength() - 1 );

	return strr;
}