ADO数据库连接(ACCESS and EXCEL)实现数据源转换(从Excel到Access)
sqlExcute();//不影响结果集的数据库操作封装函数见上个博客
sqlOpen();//影响结果集的数据库操作封装函数见上个博客
int CTYPE_TRANSFORMDlg::excel_to_access()
{
/**数据转换/
CString sql;
CString columname;
BSTR filedname;
Fields * fields = NULL;
HRESULT hr;
long filedcount;
if(sqlOpen(CString (_T(“EXCEL”)),CString(_T(“select from [sheet2
]
"
)
)
)
<
0
)
/
/
/
∗
i
n
s
e
r
t
i
n
t
o
[
s
h
e
e
t
2
]")))<0)///*insert into [sheet2
]")))<0)///∗insertinto[sheet2] values(‘fff’,33)/
{
AfxMessageBox(L"Excute failed”);
return -1;
}
hr = m_ree->get_Fields (&fields); //得到记录集的字段集和
///
if(FAILED(hr))
{
AfxMessageBox(L"Excute failed");
return -1;
}
filedcount = (m_ree->Fields)->Count;
if(sqlExcute(CString (_T(“ACCESS”)),CString(_T(“create table [food]”)))<0)
{
AfxMessageBox(L"Excute failed");
}
for(int i = 0;i < filedcount;++i)
{
//fields->Item[(long)i]->get_Name(&filedname);
m_ree->GetFields()->GetItem((long)i)->get_Name(&filedname);
columname = COLE2T(filedname);//
sql.Format(_T(“alter table food add [%s] varchar(10)”),columname);
if(sqlExcute(CString (_T(“ACCESS”)),sql)<0)
{
AfxMessageBox(L"Excute failed");
}
}
if(sqlOpen(CString (_T("ACCESS")),CString(_T("select *from food")))<0)///*insert into [sheet2$] values('fff',33)*/
{
AfxMessageBox(L"Excute failed");
return -1;
}
_variant_t var;
while(!m_ree->GetadoEOF())//看是否是位于最后一个记录之后
{
int i = 0;
while(i < filedcount)
{
var = m_ree->GetCollect((long)i);//返回第一行第一列的值
if(var.vt!=NULL)//判断是否为空
{
if(i == 0)
m_rea->AddNew();//添加一条新纪录
m_rea->PutCollect((long)i, var);修改记录
m_rea->Update();/更新到数据库
}
i++;
}
m_ree->MoveNext();
}
/*CString str;
str.Format(L"%d",a);
a = m_ree->GetRecordCount();*/
m_ree->Close();
m_rea->Close();
m_ree = NULL;
m_rea = NULL;
/****************************************************************************************************/
return 0;
}
/
实现excel数据导入到ACCESS数据库
没有判断数据类型和没有添加文件选择和路径判断
//