c++ 读写Excel及数据导入SQLServer(二)
}
}
}
}
CATCH(CDBException, e)
{
// 数据库操作产生异常时...
AfxMessageBox("数据库错误: " + e->m_strError);
}
END_CATCH;
}
//写Excel
void WriteFromExcel(int num,CString str1,CString str2,CString str3)
{
CString path=GetAddr();
if(path.IsEmpty())
::MessageBox(NULL,"获取路径错误",NULL,MB_OK);
else
{
// 新建Excel文件名及路径，TestSheet为内部表名
CSpreadSheet SS(path,"StudentsOut");
CStringArray sampleArray, testRow;
SS.BeginTransaction();
// 加入标题
sampleArray.RemoveAll();
sampleArray.Add("ID");
sampleArray.Add("myname");
sampleArray.Add("age");
SS.AddHeaders(sampleArray);
testRow.Add(str1);
testRow.Add(str2);
testRow.Add(str3);
SS.AddRow(testRow,num,true);
SS.Commit();
}
}
//查询
bool selectExcel()
{
_ConnectionPtr m_pConnection; //connection object's pointer
_CommandPtr m_pCommand; //command object's pointer
_ParameterPtr m_pParameter; //Parameter object's pointer
_RecordsetPtr m_pRecordset;
HRESULT hr;
try
{
// 创建连接对象
hr=m_pConnection.CreateInstance(__uuidof(Connection));
m_pRecordset.CreateInstance(__uuidof(Recordset));
if(!SUCCEEDED(hr)) return FALSE;
// 连接
数据库
m_pConnection->ConnectionString="File Name=LinkDatabase.udl";
m_pConnection->ConnectionTimeout=20;//等待连接的时间为20s
hr=m_pConnection->Open("","","",adModeUnknown);
if(!SUCCEEDED(hr)) return FALSE;
// 查询数据库
_variant_t RecordsAffected;
std::string sql= "select * from Students";
char * str=(char*)sql.c_str();
m_pRecordset=m_pConnection->Execute(str,&RecordsAffected,adCmdText);
//m_pRecordset-> Open(str, _variant_t((IDispatch *)m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
int i=2;
// 遍历查询结果
while (!m_pRecordset->adoEOF)
{
printf("%s\t",(char*)(_bstr_t)m_pRecordset->GetCollect("ID"));
printf("%s\t",(char*)(_bstr_t)m_pRecordset->GetCollect("myname"));
printf("%s\n",(char*)(_bstr_t)m_pRecordset->GetCollect("age"));
CString str1=(CString)m_pRecordset->GetCollect("ID");
CString str2=(CString)m_pRecordset->GetCollect("myname");
CString str3=(CString)m_pRecordset->GetCollect("age");
WriteFromExcel(i++,str1,str2,str3);
m_pRecordset->MoveNext();
}
m_pRecordset->Close();
// 关闭数据库连接
if(m_pConnection!=NULL)
{
m_pConnection->Close();
m_pConnection = NULL ;
}
}
catch(_com_error e)
{
string ErrorMessage("数据库连接关闭失败:"),Description,Source;
Description=e.Description();
Source=e.Source();
ErrorMessage+=e.ErrorMessage();
ErrorMessage=ErrorMessage+"\r\n"+Source+"\r\n"+Description;
::MessageBox(NUL