/
CObjectList *pubTable = new CObjectList ;
CListCtrl* m_pCListCtr_pub = (CListCtrl*)GetDlgItem(IDC_LIST_PUB);
COperationExcel pub ;
pub.ReadExcel(pubTable , m_pCListCtr_pub);
/**/
//清空表
while (!pubTable->IsEmpty())
{
delete pubTable->GetHead();
pubTable->RemoveHead();
}
delete pubTable;
//
CString COperationExcel::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
// 获取已安装驱动的名称(涵数在odbcinst.h里)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
// 检索已安装的驱动是否有Excel...
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//发现 !
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0');
return sDriver;
}
CString COperationExcel::GetFilePathName()
{
CString FilePathName ;
CFileDialog dlg(TRUE,"xls file(*.xls)|*.xls", NULL,
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
"xls file(*.xls)|*.xls||"); //TRUE为OPEN对话框,FALSE为SAVE AS对话框
if(dlg.DoModal()==IDOK)
{
FilePathName = dlg.GetPathName();
//SetWindowText( FilePathName );
}
return FilePathName;
}
void COperationExcel::ReadExcel(CObjectList *m_PublicTable , CListCtrl *m_ListCtrl1)
{
CString FilePathName =GetFilePathName() ;
if(FilePathName.IsEmpty())
{
return ;
}
CDatabase db ;
CString sDriver, sDsn , sSql ; // , sFile将被读取的Excel文件名
// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
AfxMessageBox(_T( "没有安装Excel驱动!" ));
return ;
}
// 创建进行存取的字符串
sDsn.Format(_T( "ODBC;DRIVER={%s};DSN='';DBQ=%s" ), sDriver, FilePathName);
TRY
{
// 打开数据库(既Excel文件) //读入EXCEL
db.Open(NULL, false , false , sDsn);
CRecordset rs(&db);
// 设置读取的查询语句.
sSql .Format( _T("SELECT * FROM %s" ),"[Sheet1$]" ); //字段名称要分大小写公共数据
// 执行查询语句
rs.Open(CRecordset::forwardOnly, sSql,CRecordset::none);
//测试取得表中字段名称
short sFieldCount = rs.GetODBCFieldCount();
CODBCFieldInfo fieldinfo;
CStringArray strArrPublicFields;
for( int i=0; i < sFieldCount; i++)
{
rs.GetODBCFieldInfo(i,fieldinfo);
//把读到的表头项写一个字符串数组
strArrPublicFields.Add(_T(fieldinfo.m_strName.GetBuffer(fieldinfo.m_strName.GetLength())));
}
//为表格控件设置表头
DWORD dwExtStyles1 = m_ListCtrl1->GetExtendedStyle();
m_ListCtrl1->SetExtendedStyle(dwExtStyles1 | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
for(i = 0 ; i < strArrPublicFields.GetSize() ; i++)
{
m_ListCtrl1->InsertColumn(i,strArrPublicFields.GetAt(i),LVCFMT_LEFT,100,i);
}
i =0 ;
// 获取查询结果//读取记录
while (!rs.IsEOF())
{
//把记录中的数据写入到内存链表
CStringArray *pNewItem = new CStringArray ;
for(i = 0 ; i < strArrPublicFields.GetSize() ; i++)
{
CString str ;
rs.GetFieldValue(strArrPublicFields.GetAt(i), str); pNewItem->Add(str);
}
//把记录加入公共数据表
m_PublicTable->AddHead(pNewItem);
//delete pNewItem; 这里不能删除,删除会导致链表内的数据不存在了
// 移到下一行
rs.MoveNext();
}
rs.Close();
// 关闭数据库
db.Close();
CStringArray *TempItem;
POSITION pos=m_PublicTable->GetHeadPosition();
//循环遍历找出容器中的所有信息
while (pos!=NULL)
{
TempItem =(CStringArray* ) m_PublicTable->GetNext(pos);
for(i = 0 ; i < TempItem->GetSize() ; i++)
{
//把数据写入表格控件
if (i == 0)
m_ListCtrl1->InsertItem(0, TempItem->GetAt(0) );//插入行
else
m_ListCtrl1->SetItemText(0,i,TempItem->GetAt(i));//设置该行的不同列的显示字符
}
}
}
CATCH(CDBException, e)
{
// 数据库操作产生异常时...
AfxMessageBox(_T( "数据库错误: " ) + e->m_strError);
}
END_CATCH;
}
void COutToExcel::TestWrite()
{
//写入EXCEL
//CString str = _T(GetFileNameXls("Save"));
//CString sExcelFile = str ; // 要建立的Excel文件
CString sDriver = _T( "MICROSOFT EXCEL DRIVER (*.XLS)" ); // Excel安装驱动
CString sExcelFile = _T( "./data/SeriesData.xls" ); // 将被读取的Excel文件名
TRY
{
// 创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
sDriver, sExcelFile, sExcelFile);
// 创建数据库 (既Excel表格文件)
if ( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
//捕获异常
try
{
//删除表
database.ExecuteSQL(_T( "DROP TABLE Test" ));
}
catch (CDBException *pe)
{
// The error code is in pe->m_nRetCode
pe->ReportError();
pe->Delete();
}
//创建表
database.ExecuteSQL(_T( "CREATE TABLE Test (Name TEXT, Val NUMBER, WeightMeter NUMBER, UnitPrice NUMBER)" ));
// 插入数值
database.ExecuteSQL(_T( "INSERT INTO Test (Name,Val,WeightMeter,UnitPrice) VALUES ('铝型材喷涂',0.0,0.0,29.5)" ));
}
// 关闭数据库
database.Close();
}
CATCH_ALL(e)
{
TRACE1( "Excel驱动没有安装: %s" ,sDriver);
}
END_CATCH_ALL;
}