向指定的www.xls文件中插入表,禁止插入相同的表的控制
CString CExcle2Dlg::GetSpecialPath()
{
int nPos;
CString strTempPath;
GetModuleFileName(NULL,strTempPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);
strTempPath.ReleaseBuffer ();
nPos=strTempPath.ReverseFind ('\\');
strTempPath=strTempPath.Left (nPos);
return (strTempPath + "\\DemoExcle.xls");
}
//使用CDatabase、CRecordset时,需要使用的头文件
#include <odbcinst.h>
#include <afxdb.h>
// 递进初始化 CDatabase、CRecordset对象 以及初始化之后的相关操作
// num 控制Table的名字
// 函数功能仅仅是创建新表 并向 新表中插入几条数据
void CExcle2Dlg::AddTableS(CString num)
{
m_Driver ="Microsoft Excel Driver (*.xls)";
m_ExcelFile = GetSpecialPath();
//1.通过连接数据源间接连接到驱动上
//m_DSN.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",m_Driver,m_ExcelFile);
//2.直接连接到驱动上
m_DSN.Format("DRIVER={%s};DNS='';FIRSTROWHASNAME=1;READONLY=FALSE;CREATE_DB\"%s\";DBQ=%s",
m_Driver,m_ExcelFile,m_ExcelFile);
m_Database = new CDatabase;
BOOL bRetDatabase=m_Database->OpenEx(m_DSN, CDatabase::noOdbcDialog);
if(!bRetDatabase)
return;
CString str;
CStringArray arrSQL;
str.Format("CREATE TABLE [GGGG%s] ([姓名] char(255), [年龄] char(255))",num);
arrSQL.Add(str);str.Empty();
str.Format("INSERT INTO [GGGG%s$A1:IV1] ([姓名], [年龄]) VALUES ('谢志敏', '26岁')",num);
arrSQL.Add(str);str.Empty();
str.Format("INSERT INTO [GGGG%s$A1:IV2] ([姓名], [年龄]) VALUES ('张海平', '24岁')",num);
arrSQL.Add(str);str.Empty();
str.Format("INSERT INTO [GGGG%s$A1:IV3] ([姓名], [年龄]) VALUES ('李志超', '26岁')",num);
arrSQL.Add(str);str.Empty();
str.Format("INSERT INTO [GGGG%s$A1:IV4] ([姓名], [年龄]) VALUES ('刘永健', '25岁')",num);
arrSQL.Add(str);str.Empty();
str.Format("INSERT INTO [GGGG%s$A1:IV5] ([姓名], [年龄]) VALUES ('等异形', '26岁')",num);
arrSQL.Add(str);str.Empty();
m_Recordset = new CRecordset(m_Database);
BOOL bRetRecordset=FALSE;
try
{
str.Format("SELECT * FROM [GGGG%s$A1:IV65536]",num);
bRetRecordset=m_Recordset->Open(CRecordset::forwardOnly,str, CRecordset::readOnly);
delete m_Recordset;
str.Empty();
str.Format("Excle中已经有表%s",num);
::MessageBox(0,str,"提示",0);
str.Empty();
delete m_Recordset;
m_Recordset=NULL;
return;
}
catch (...)
{
// m_Recordset->Open打不开此表,就会抛异常到这边,就可以安心的创建新表了
for (int i=0;i<6;i++)
{
m_Database->ExecuteSQL(arrSQL.GetAt(i));
}
str.Empty();
delete m_Recordset;
m_Recordset=NULL;
}
// 这个是对表的访问 ,因为上面一定会创建表成功
m_Recordset = new CRecordset(m_Database);
try
{
str.Format("SELECT * FROM [GGGG%s$A1:IV65536]",num);
bRetRecordset=m_Recordset->Open(CRecordset::forwardOnly,str, CRecordset::readOnly);
str.Empty();
}
catch (...)
{
str.Empty();
::MessageBox(0,0,0,0);
delete m_Recordset;
m_Recordset=NULL;
}
m_Recordset->Close();
m_Database->Close();
}
测试
void CExcle2Dlg::OnButton1()
{
// TODO: Add your control notification handler code here
Invalidate(FALSE);
CString str;
str.Empty();
m_CtrlEdit.GetWindowText(str);
AddTableS(str);
}