学习心得
来源：互联网 发布：软件开发编码流程 编辑：程序博客网 时间：2024/05/17 01:45
关键代码：
（1） 数据库连接类（CAdoConn）
类的声明如下：
#import "C:/Program Files/Common Files/System/ado/msado15.dll" no_namespace rename("EOF","adoEOF")
class CAdoConn
{
public:
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
public:
CAdoConn();
virtual ~CAdoConn();
void OnInitADOConn();
_RecordsetPtr& GetRecordSet(_bstr_t bstrSQL);
BOOL ExecuteSQL(_bstr_t bstrSQL);
void ExitConnection();
};
类的实现如下：
#include "AdoConn.h"
CAdoConn::CAdoConn()
{
}
CAdoConn::~CAdoConn()
{
}
void CAdoConn::OnInitADOConn()
{
::CoInitialize(NULL);
try
{
m_pConnection.CreateInstance(_uuidof(Connection));
_bstr_t strConnect="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://123//123//作业计划管理.mdb;Persist Security Info=False"; //利用字符串进行连接数据库
//_bstr_t strConnect="Provider=SQLOLEDB.1;Server=NTSERVER;Database=NumData;User ID=sa";//利用DSN进行连接数据库
m_pConnection->Open(strConnect,"","",adModeUnknown);
}
catch(_com_error e)
{
m_pConnection=NULL;
AfxMessageBox(e.Description());
}
}
_RecordsetPtr& CAdoConn::GetRecordSet(_bstr_t bstrSQL)
{
try
{
if(m_pConnection==NULL)
{
OnInitADOConn();
}
m_pRecordset.CreateInstance(_uuidof(Recordset));
m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
}
return m_pRecordset;
}
BOOL CAdoConn::ExecuteSQL(_bstr_t bstrSQL)
{
try
{
if(m_pConnection==NULL)
{
OnInitADOConn();
}
m_pConnection->Execute(bstrSQL,NULL,adCmdText);
return true;
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
return false;
}
}
void CAdoConn::ExitConnection()
{
if(m_pRecordset!=NULL)
{
m_pRecordset->Close();
}
m_pConnection->Close();
::CoUninitialize();
}
（2） 数据库调用初始化列表框代码：
#include "AdoConn.h"
void CMAdd::InitList()
{
CAdoConn m_ado;
m_ado.OnInitADOConn();
CString m_str="Select * from ZY order by ID";
_RecordsetPtr m_rec=m_ado.GetRecordSet((_bstr_t)m_str);
if (m_rec->adoEOF)
{
MessageBox("No Record!");
return;
}
else
{
while (!m_rec->adoEOF)
{
_variant_t m_add=m_rec->Fields->Item[_variant_t("ZYH")]->Value;
CString str_Add=_com_util::ConvertBSTRToString(_bstr_t(m_add));
m_listSource.AddString(str_Add);
m_rec->MoveNext();
}
}
m_ado.ExitConnection();
}
可以在对话框初始化函数中调用。
（3） 从列表框向另外一个列表框添加数据和删除数据的代码：
void CMAdd::OnAdd()
{
// TODO: Add your control notification handler code here
int nIndex=m_listSource.GetCurSel();//获取当前选中项
if (nIndex!=LB_ERR)
{
CString strSel;
m_listSource.GetText(nIndex,strSel);
m_listResult.AddString(strSel);
m_listSource.DeleteString(nIndex);
}
}
void CMAdd::OnDelete()
{
// TODO: Add your control notification handler code here
int nIndex=m_listResult.GetCurSel();//获取当前选中项
if (nIndex!=LB_ERR)
{
CString strSel;
m_listResult.GetText(nIndex,strSel);
m_listSource.AddString(strSel);
m_listResult.DeleteString(nIndex);
}
}
其中m_listSource为源数据源控件变量。
（4） 启动Execl代码：
首先要引入Excel类库。对于Excel2003，类库的引入是Excel.exe文件。
添加需要的类_Application,_WorkBook,_WorkSheet,WorkBooks,WorkSheets.
另外由于需要调用Excel，因此需要在建立工程的时候，使用自动化技术。
#include "excel.h"
void CTestExcelDlg::OnButton1()
{
// TODO: Add your control notification handler code here
_Application app; // app is the Excel _Application object
Workbooks oBooks;
Worksheets oSheets;
_Workbook oBook;
_Worksheet oSheet;
Range oRange;
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
// Start Excel and get Application object.
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Cannot start Excel and get Application object.");
return;
}
else
{
//Make the application visible and give the user control of
//Microsoft Excel.
app.SetVisible(TRUE);
app.SetUserControl(TRUE);
oBooks=app.GetWorkbooks();
//oBook=oBooks.Add(covOptional);//添加空的Excel文档
oBook=oBooks.Open("D://1.xls",covOptional,covOptional,covOptional,covOptional
,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional
,covOptional,covOptional,covOptional);//打开已有的Excel文档
oSheets=oBook.GetWorksheets();
oSheet=oSheets.GetItem(COleVariant(short(1)));
oRange=oSheet.GetRange(COleVariant("A1"),covOptional);
oRange=oRange.GetResize(COleVariant(short(5)),COleVariant(short(5)));
COleSafeArray saRet;
DWORD numElements[2];
numElements[0]=5;
numElements[1]=5;
long index[2];
if (m_fillStr)
{
saRet.Create(VT_BSTR,2,numElements);
}
else
{
saRet.Create(VT_R8,2,numElements);
}
for (int iRow=1;iRow<=5;iRow++)
{
for (int iCol=1;iCol<=5;iCol++)
{
index[0]=iRow-1;
index[1]=iCol-1;
if(m_fillStr)
{
CString szTemp;
szTemp.Format("%d|%d",iRow,iCol);
BSTR bstr=szTemp.AllocSysString();
saRet.PutElement(index,bstr);
SysFreeString(bstr);
}
else
{
double d=iRow*iCol;
saRet.PutElement(index,&d);
}
}
}
oRange.SetValue(covOptional,COleVariant(saRet));
}
}
其中m_fillStr是一个控制变量，实现对数据的不同填充格式。
构造Excel的控制类
#include "excel.h"
class CMExcel
{
public:
bool OpenExcel(CString PathOfFile);
bool OpenExcel();
CMExcel();
virtual ~CMExcel();
private:
_Application m_app;
_Workbook m_book;
_Worksheet m_sheet;
Workbooks m_books;
Worksheets m_sheets;
Range m_range;
};
实现文件
CMExcel::CMExcel()
{
}
CMExcel::~CMExcel()
{
}
bool CMExcel::OpenExcel()
{
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
if (!m_app.CreateDispatch("Excel.Application"))
{
//AfxMessageBox("创建Excel实例失败！");
return false;
}
else
{
m_app.SetVisible(TRUE);
m_app.SetUserControl(TRUE);
m_books=m_app.GetWorkbooks();
m_book=m_books.Add(covOptional);
m_sheets=m_book.GetWorksheets();
m_sheet=m_sheets.GetItem(COleVariant(short(1)));
m_range=m_sheet.GetRange(COleVariant("A1"),covOptional);
return true;
}
}
bool CMExcel::OpenExcel(CString PathOfFile)
{
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
if (!m_app.CreateDispatch("Excel.Application"))
{
return false;
}
else
{
m_app.SetVisible(TRUE);
m_app.SetUserControl(TRUE);
m_books=m_app.GetWorkbooks();
m_book=m_books.Open(PathOfFile,covOptional,covOptional,covOptional,covOptional
,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional
,covOptional,covOptional,covOptional);
m_sheets=m_book.GetWorksheets();
m_sheet=m_sheets.GetItem(COleVariant(short(1)));
m_range=m_sheet.GetRange(COleVariant("A1"),covOptional);
return true;
}
}
