关于将数据库的一个表保存为excel文件的超级疑惑问题,路过的进来看看,谢谢!
我在将sql server中的一个表的记录依次读出并写入到excel文件中,可是
有时能保存成功,有时不能保存成功,当然是不同的数据,但是这个表的结构一样阿
,不知道为什么,代码在下面,谢谢帮助!!!
nStatSelect=AfxMessageBox("是否将表JFZX_GKJ的数据保存为excel文件?",MB_OKCANCEL);
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
CString sExcelFile,sPath;
CString sSql;
//获取主程序所在路径,存在sPath中
GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);
//AfxMessageBox(sPath);
sPath.ReleaseBuffer ();
//AfxMessageBox(sPath);
int nPos;
nPos=sPath.ReverseFind ('\\');
sPath=sPath.Left (nPos);
sExcelFile = sPath + "\\execl6664.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) )
{
database.SetQueryTimeout(100000000);
database.SetLoginTimeout(100000000);
sSql = "CREATE TABLE Exceldemo (calling TEXT,acct TEXT,thisexchange_id NUMBER,name TEXT, n17990thiscounts NUMBER, n17990lastcounts NUMBER, n17990subcounts NUMBER,i17951thiscounts NUMBER,i17951lastcounts NUMBER, i17951subcounts NUMBER, n17911thiscounts NUMBER,n17911lastcounts NUMBER, n17911subcounts NUMBER, n193thiscounts NUMBER, n193lastcounts NUMBER, n193subcounts NUMBER,n17951thiscounts NUMBER, n17951lastcounts NUMBER,n17951subcounts NUMBER,i193thiscounts NUMBER,i193lastcounts NUMBER, i193subcounts NUMBER, n17910thiscounts NUMBER,n17910lastcounts NUMBER,n17910subcounts NUMBER,n17991thiscounts NUMBER, n17991lastcounts NUMBER,n17991subcounts NUMBER,n068thiscounts NUMBER, n068lastcounts NUMBER,n068subcounts NUMBER,n17950thiscounts NUMBER,n17950lastcounts NUMBER,n17950subcounts NUMBER, i17911thiscounts NUMBER,i17911lastcounts NUMBER,i17911subcounts NUMBER,i068thiscounts NUMBER, i068lastcounts NUMBER,i068subcounts NUMBER,i17991thiscounts NUMBER, i17991lastcounts NUMBER, i17991subcounts NUMBER, address TEXT)";
database.ExecuteSQL(sSql);
///在这里循环取出数据库中的记录,然后插入到excel文件中
edit_stat_reading->SetWindowText("两个库中表的数据正在导入...");
bt_sql=(_bstr_t)" SELECT * FROM " +(_bstr_t)str_filename+(_bstr_t)" order by calling ";
try
{
hr_sql=rs_sql.CreateInstance (__uuidof(Recordset));
hr_sql=rs_sql->Open (bt_sql,m_pConnect_sql.GetInterfacePtr (),adOpenDynamic,adLockOptimistic,adCmdText);
}
catch(_com_error e)
{
strErrMsg_sql.Format("sqlserver执行数据库操作失败!\r\n错误信息:%s",e.ErrorMessage());
AfxMessageBox(strErrMsg_sql);
}
//////////
//_variant_t vIndex=(long)0;
_variant_t vcalling,vacct,vthisexchange_id,vname;
_variant_t vn17990thiscounts,vn17990lastcounts,vn17990subcounts,vi17951thiscounts,vi17951lastcounts,vi17951subcounts,vn17911thiscounts,vn17911lastcounts,vn17911subcounts,vn193thiscounts,vn193lastcounts;
_variant_t vn193subcounts,vn17951thiscounts,vn17951lastcounts,vn17951subcounts,vi193thiscounts,vi193lastcounts,vi193subcounts,vn17910thiscounts,vn17910lastcounts,vn17910subcounts,vn17991thiscounts;
_variant_t vn17991lastcounts,vn17991subcounts,vn068thiscounts,vn068lastcounts,vn068subcounts,vn17950thiscounts,vn17950lastcounts,vn17950subcounts,vi17911thiscounts,vi17911lastcounts,vi17911subcounts;
_variant_t vi068thiscounts,vi068lastcounts,vi068subcounts,vi17991thiscounts,vi17991lastcounts,vi17991subcounts,vaddress;
//rs_stat->MoveFirst();
//CString message;
CString strValues,strTemp1,strTemp2;
while(!rs_sql->EndOfFile)
{
vcalling=rs_sql->GetCollect(_variant_t((long)0));
vacct=rs_sql->GetCollect(_variant_t((long)1));
vthisexchange_id=rs_sql->GetCollect(_variant_t((long)2));
vname=rs_sql->GetCollect(_variant_t((long)3));
vn17990thiscounts=rs_sql->GetCollect(_variant_t((long)4));
vn17990lastcounts=rs_sql->GetCollect(_variant_t((long)5));
vn17990subcounts=rs_sql->GetCollect(_variant_t((long)6));
vi17951thiscounts=rs_sql->GetCollect(_variant_t((long)7));
vi17951lastcounts=rs_sql->GetCollect(_variant_t((long)8));
vi17951subcounts=rs_sql->GetCollect(_variant_t((long)9));
vn17911thiscounts=rs_sql->GetCollect(_variant_t((long)10));
vn17911lastcounts=rs_sql->GetCollect(_variant_t((long)11));
vn17911subcounts=rs_sql->GetCollect(_variant_t((long)12));
vn193thiscounts=rs_sql->GetCollect(_variant_t((long)13));
vn193lastcounts=rs_sql->GetCollect(_variant_t((long)14));
vn193subcounts=rs_sql->GetCollect(_variant_t((long)15));
vn17951thiscounts=rs_sql->GetCollect(_variant_t((long)16));
vn17951lastcounts=rs_sql->GetCollect(_variant_t((long)17));
vn17951subcounts=rs_sql->GetCollect(_variant_t((long)18));
vi193thiscounts=rs_sql->GetCollect(_variant_t((long)19));
vi193lastcounts=rs_sql->GetCollect(_variant_t((long)20));
vi193subcounts=rs_sql->GetCollect(_variant_t((long)21));
vn17910thiscounts=rs_sql->GetCollect(_variant_t((long)22));
vn17910lastcounts=rs_sql->GetCollect(_variant_t((long)23));
vn17910subcounts=rs_sql->GetCollect(_variant_t((long)24));
vn17991thiscounts=rs_sql->GetCollect(_variant_t((long)25));
vn17991lastcounts=rs_sql->GetCollect(_variant_t((long)26));
vn17991subcounts=rs_sql->GetCollect(_variant_t((long)27));
vn068thiscounts=rs_sql->GetCollect(_variant_t((long)28));
vn068lastcounts=rs_sql->GetCollect(_variant_t((long)29));
vn068subcounts=rs_sql->GetCollect(_variant_t((long)30));
vn17950thiscounts=rs_sql->GetCollect(_variant_t((long)31));
vn17950lastcounts=rs_sql->GetCollect(_variant_t((long)32));
vn17950subcounts=rs_sql->GetCollect(_variant_t((long)33));
vi17911thiscounts=rs_sql->GetCollect(_variant_t((long)34));
vi17911lastcounts=rs_sql->GetCollect(_variant_t((long)35));
vi17911subcounts=rs_sql->GetCollect(_variant_t((long)36));
vi068thiscounts=rs_sql->GetCollect(_variant_t((long)37));
vi068lastcounts=rs_sql->GetCollect(_variant_t((long)38));
vi068subcounts=rs_sql->GetCollect(_variant_t((long)39));
vi17991thiscounts=rs_sql->GetCollect(_variant_t((long)40));
vi17991lastcounts=rs_sql->GetCollect(_variant_t((long)41));
vi17991subcounts=rs_sql->GetCollect(_variant_t((long)42));
vaddress=rs_sql->GetCollect(_variant_t((long)43));
rs_sql->MoveNext();