exce

// ReadExcel.h

#ifndef __SNOWWIN_EXCEL_FILE_H__
#define __SNOWWIN_EXCEL_FILE_H__

#include
#include
#include

/// excel表中字段的类型
enum DataType
{
DT_TEXT, //!< 字符串
DT_NUMBER //!< 数值型
};

/// 列结构
struct Column
{
CString name; //!< 列的名称
DataType type; //!< 这一列的数据类型
};

/// excel文件读写类
class ExcelFile
{
public:
ExcelFile( void );
~ExcelFile( void );

/// 打开文件，最先要执行这个，如果文件不存在会新建这个文件
bool open( CString fileName );

/// 关闭文件
void close( void );

//-------------创建表和对表进行删添操作-------------------
/// 创建表，columns是表的字段定义
bool createTable( CString tableName, std::vector* columns );

/// 清空表
void clearTable( CString tableName );

/// 添加一行，eg: dataArray = " 1,'aaa','zxvcx',50 ";
bool addRow( CString tableName, CString dataArray );
//--------------------------------------------------------

//---------------------进行顺序读操作---------------------
/// 开始读取，这个函数执行后才能执行下面的其它函数
bool beginRead( CString tableName );

/// 把当前行移到下一行
void moveNext( void );

/// 读当前行的第column列的字段的数据
void readData( short column, CString& data );

/// 读当前行的列名为column的字段的数据
void readData( CString column, CString& data );

/// 是否已经读完
bool isEOF( void );
//--------------------------------------------------------

//------如果对表进行比较复杂的操作，可以用下面的函数------
/// 通过sql语句在这里对表进行插、删、改操作
void doSQL( CString sql );

/// 通过select语句对表进行查寻操作，通过返回的指针可以得到查寻结果
CRecordset* select( CString selectSentence );
//--------------------------------------------------------

//-----------用来在list Control 控件里显示和操作的函数-----------
/// 在一个列表框里把一个表显示出来
bool showTable( CString tableName, CListCtrl* list );

/// 把一个列表框里的内容保存到一个xls文件,指定每一列的名字和类型
bool saveTable( CString fileName, CString tableName, CListCtrl* list, std::vector* columns );

/// 把一个列表框里的内容保存到一个xls文件，指定每一列的类型
bool saveTable( CString fileName, CString tableName, CListCtrl* list, std::vector* types );

/// 把一个列表框里的内容保存到一个xls文件里，全部以TEXT类型
bool saveTableAsText( CString fileName, CString tableName, CListCtrl* list );
//---------------------------------------------------------------

// 取列数
int getColCount() const;

private:
///获取ODBC中Excel驱动的函数
CString getExcelDriver( void );

CDatabase db_;
CRecordset * rs_;

public:
// 读表
void StartRead(CString fileName, CString tableName);
bool ReadLine(std::vector &lineText);
void EndRead();
private:
};

#endif //__SNOWWIN_EXCEL_FILE_H__

// ReadExcel.cpp

#include "stdafx.h"
#include "ReadExcel.h"

ExcelFile::ExcelFile( void ):rs_(0)
{
}
ExcelFile::~ExcelFile( void )
{
close();
}

///获取ODBC中Excel驱动的函数
CString ExcelFile::getExcelDriver( void )
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;

// 获取已安装驱动的名称
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))return "";

do
{
if (strstr(pszBuf, "Excel") != 0)
{
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0' ) + 1;
}
while (pszBuf[1] != '\0' );

return sDriver;
}

bool ExcelFile::open( CString fileName )
{
CString driver = getExcelDriver();
if( driver.GetLength() <= 0 )return false;

CString connect;
connect.Format( "DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", driver, /*"Book1.xls"*/fileName, fileName );
TRY
{
if( !db_.OpenEx( connect, CDatabase::noOdbcDialog ) )
return false;

rs_ = new CRecordset( &db_ );
}
CATCH_ALL(e)
{
CString buf;
buf.Format("Excel驱动没有安装: %s!",driver);
AfxMessageBox( buf );
return false;
}
END_CATCH_ALL;
return true;
}

/// 关闭文件
void ExcelFile::close( void )
{
if( rs_ )
{
rs_->Close();
delete rs_;
rs_ = 0;
}
db_.Close();
}

void ExcelFile::doSQL( CString sql )
{
if( !rs_ )return;
db_.ExecuteSQL( sql );
}

bool ExcelFile::createTable( CString tableName, std::vector* columns )
{
if( !rs_ )return false;
if( columns->size() <= 0 )return false;

CString sql;
sql.Format( "create table %s (", tableName );

CString buf;

std::vector::iterator it, end = columns->end();
for( it = columns->begin(); it != end; ++it )
{
buf.Format( "%s %s,", it->name, ((it->type==DT_TEXT)?"TEXT":"NUMBER") );
sql += buf;
}
sql.Delete( sql.GetLength()-1, 1 );
sql += ")";
db_.ExecuteSQL( sql );

return true;
}

void ExcelFile::clearTable( CString tableName )
{
CString sql;
sql.Format( "drop table [%s$A1:IV65536]", tableName );
db_.ExecuteSQL( sql );
}

bool ExcelFile::addRow( CString tableName, CString dataArray )
{
if( !rs_ )return false;

CString sql;
sql.Format( "insert into %s values( %s )", tableName, dataArray );
db_.ExecuteSQL( sql );

return true;
}

bool ExcelFile::beginRead( CString tableName )
{
if( !rs_ )return false;

CString sql;
sql.Format( "select * from [%s$A1:IV65536]", tableName );
//sql.Format( "select * from [" & %s &"$]", tableName );
rs_->Open( CRecordset::forwardOnly, sql, CRecordset::readOnly );

return true;
}

void ExcelFile::moveNext( void )
{
if( !rs_ )return;
rs_->MoveNext();
}

void ExcelFile::readData( short column, CString& data )
{
if( !rs_ )return;
rs_->GetFieldValue( column, data );
}
void ExcelFile::readData( CString column, CString& data )
{
if( !rs_ )return;
rs_->GetFieldValue( column, data );
}

bool ExcelFile::isEOF( void )
{
if( !rs_ )return false;
return ( rs_->IsEOF() ? true : false );
}

/// 在一个列表框里把一个表显示出来
bool ExcelFile::showTable( CString tableName, CListCtrl* list )
{
if( !rs_ )return false;
if( !list )return false;

list->DeleteAllItems();
CHeaderCtrl* pHeader = (CHeaderCtrl*)list->GetHeaderCtrl();//>GetDlgItem(0);
int nColumnCount = pHeader->GetItemCount();
for( int i = 0; i < nColumnCount; ++i )
list->DeleteColumn( 0 );

CString buf;
beginRead( tableName );
int colCount = rs_->GetODBCFieldCount();
CODBCFieldInfo fi;
for( int i = 0; i < colCount; ++i )
{
rs_->GetODBCFieldInfo( (short)i, fi );
list->InsertColumn( i, fi.m_strName, LVCFMT_LEFT, 110 );
}

int line = 0;

while( !isEOF() )
{
line = list->GetItemCount();
list->InsertItem( line, "空行" );
for( int i = 0; i < colCount; ++i )
{
readData( i, buf );
if( buf.GetLength() > 2 )
{
if( buf[buf.GetLength()-1] == '0' && buf[buf.GetLength()-2] == '.' )
buf.Delete( buf.GetLength()-2, 2 );
}
list->SetItemText( line, i, buf );
}
moveNext();
}
return true;
}

/// 把一个列表框里的内容保存到一个xls文件
bool ExcelFile::saveTable( CString fileName, CString tableName, CListCtrl* list, std::vector* columns )
{
if( !list ) return false;

CHeaderCtrl* pHeader = (CHeaderCtrl*)list->GetHeaderCtrl();//>GetDlgItem(0);
int colCount = pHeader->GetItemCount();

if( columns->size() < colCount ) return false;

::DeleteFile( fileName );
if( !open( fileName ) )return false;

if( !createTable( tableName, columns ) )return false;
CString str, buf;
for( int i = 0; i < list->GetItemCount(); ++i )
{
str.Empty();
for( int j = 0; j < colCount; ++j )
{
if( (*columns)[j].type == DT_TEXT )
buf.Format( "\'%s\',", list->GetItemText( i, j ) );
else
{
if( list->GetItemText( i, j ).GetLength()<= 0 )
buf = "0,";
else
buf.Format( "%s,",list->GetItemText( i, j ) );
}
str += buf;
}
str.Delete( str.GetLength()-1 );
addRow( tableName, str );
}
return true;
}

/// 把一个列表框里的内容保存到一个xls文件，指定每一列的类型
bool ExcelFile::saveTable( CString fileName, CString tableName, CListCtrl* list, std::vector* types )
{
if( !list ) return false;
if( !types ) return false;
CHeaderCtrl* pHeader = (CHeaderCtrl*)list->GetHeaderCtrl();//>GetDlgItem(0);
int colCount = pHeader->GetItemCount();

while( types->size() < colCount )types->push_back( DT_TEXT );

std::vector< Column > v;
Column c;

TCHAR buff[128];
HD_ITEM hditem;
hditem.mask = HDI_TEXT | HDI_FORMAT;
hditem.pszText = buff;
hditem.cchTextMax = 128;

for( int i = 0; i < colCount; ++i )
{
pHeader->GetItem( i, &hditem);
c.name = hditem.pszText;
c.type = (*types)[i];
v.push_back( c );
}

return saveTable( fileName, tableName, list, &v );
}

/// 把一个列表框里的内容保存到一个xls文件里，全部以TEXT类型
bool ExcelFile::saveTableAsText( CString fileName, CString tableName, CListCtrl* list )
{
if( !list ) return false;
std::vector< Column > v;
Column c;

CHeaderCtrl* pHeader = (CHeaderCtrl*)list->GetHeaderCtrl();//>GetDlgItem(0);
int colCount = pHeader->GetItemCount();

TCHAR buff[128];
HD_ITEM hditem;
hditem.mask = HDI_TEXT | HDI_FORMAT;
hditem.pszText = buff;
hditem.cchTextMax = 128;

for( int i = 0; i < colCount; ++i )
{
pHeader->GetItem( i, &hditem);
c.name = hditem.pszText;
c.type = DT_TEXT;
v.push_back( c );
}

return saveTable( fileName, tableName, list, &v );
}

int ExcelFile::getColCount() const
{
if (rs_)
return rs_->GetODBCFieldCount();
else
return 0;
}

void ExcelFile::StartRead(CString fileName, CString tableName)
{
open(fileName);
beginRead(tableName);
}
bool ExcelFile::ReadLine(std::vector &lineText)
{
if (isEOF())
return false;
CString temp("");
for (short i = 0; i < getColCount(); ++i)
{
readData(i, temp);
if( temp.GetLength() > 2 )
{
if( temp[temp.GetLength()-1] == '0' && temp[temp.GetLength()-2] == '.' )
temp.Delete( temp.GetLength()-2, 2 );
}
lineText.push_back(temp);
temp = "";
}
moveNext();
return true;
}
void ExcelFile::EndRead()
{
close();
}