[转载]C++读取Excel文件(97-2000) *.xls

原帖在这里：http://hi.baidu.com/shaka_mabo/blog/item/5d93499b4b997ebdc8eaf436.html

                 谢谢这位老兄分享，我转放我空间以备后需。

 

// ReadExcel.h

#ifndef __SNOWWIN_EXCEL_FILE_H__
#define __SNOWWIN_EXCEL_FILE_H__

#include <odbcinst.h>
#include <afxdb.h>
#include <vector>

/// 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<Column>* 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<Column>* columns );

/// 把一个列表框里的内容保存到一个xls文件，指定每一列的类型
bool saveTable( CString fileName, CString tableName, CListCtrl* list, std::vector<DataType>* 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 <CString> &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<Column>* columns )
{
if( !rs_ )return false;
if( columns->size() <= 0 )return false;

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

CString buf;

std::vector<Column>::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<Column>* 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<DataType>* 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 <CString> &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();
}