关于将数据库的一个表保存为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();

3 个解决方案

#1

//strTemp1.Format("%i",vduration1.lVal);
//AfxMessageBox("正在准备写入");
//strTemp2.Format("%i",vcounts1.lVal);
strValues=(LPCTSTR)(_bstr_t)vcalling;
strValues=strValues+"','"+(LPCTSTR)(_bstr_t)vacct;
strTemp1.Format("%i",vthisexchange_id.lVal);
strValues=strValues+"','"+strTemp1;
strValues=strValues+"','"+(LPCTSTR)(_bstr_t)vname;
strTemp1.Format("%i",vn17990thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17990lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17990subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17951thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17951lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17951subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17911thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17911lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17911subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn193thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn193lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn193subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17951thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17951lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17951subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi193thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi193lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi193subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17910thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17910lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17910subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17991thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17991lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17991subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn068thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn068lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn068subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17950thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17950lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vn17950subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17911thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17911lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17911subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi068thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi068lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi068subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17991thiscounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17991lastcounts.lVal);
strValues=strValues+"','"+strTemp1;
strTemp1.Format("%i",vi17991subcounts.lVal);
strValues=strValues+"','"+strTemp1;
strValues=strValues+"','"+(LPCTSTR)(_bstr_t)vaddress;
//AfxMessageBox(strValues);

//AfxMessageBox(strTemp2);
//strValues=(LPCTSTR)(_bstr_t)vcalling_nbr1+"','"+(LPCTSTR)(_bstr_t)vsdate1+"','"+
// (LPCTSTR)(_bstr_t)vstime1+"','"+(LPCTSTR)(_bstr_t)vcdno1+"','"+(LPCTSTR)(_bstr_t)vacct1+"','"+strTemp1+"','"+strTemp2+"','"+
// (LPCTSTR)(_bstr_t)vname1+"','"+(LPCTSTR)(_bstr_t)vtype1+"','"+(LPCTSTR)(_bstr_t)vexchange_id1+"','"+
// (LPCTSTR)(_bstr_t)vaddress1;
//sSql = (_variant_t)"INSERT INTO Exceldemo (CALLING_NBR ,SDATE ,STIME ,CDNO ,ACCT ,DURATION ,COUNTS ,NAME ,TYPE ,EXCHANGE_ID ,ADDRESS ) VALUES ("+(_variant_t)vcalling_nbr1+","+(_variant_t)vsdate1+","+(_variant_t)vstime1+","+(_variant_t)vcdno1+","+(_variant_t)vacct1+","+(_variant_t)vduration1+","+(_variant_t)vcounts1+","+(_variant_t)vname1+","+(_variant_t)vtype1+","+(_variant_t)vexchange_id1+","+(_variant_t)vaddress1+")";
//sSql = "INSERT INTO Exceldemo (CALLING_NBR ,SDATE ,STIME ,CDNO ,ACCT ,DURATION ,COUNTS ,NAME ,TYPE ,EXCHANGE_ID ,ADDRESS ) VALUES ('"+xx+"','2','3','4','5',11,22,'7','8','9','10')";
sSql = "INSERT INTO Exceldemo (calling ,acct ,thisexchange_id ,name , n17990thiscounts , n17990lastcounts , n17990subcounts ,i17951thiscounts ,i17951lastcounts , i17951subcounts , n17911thiscounts ,n17911lastcounts  , n17911subcounts , n193thiscounts , n193lastcounts , n193subcounts ,n17951thiscounts , n17951lastcounts ,n17951subcounts  ,i193thiscounts  ,i193lastcounts , i193subcounts , n17910thiscounts ,n17910lastcounts  ,n17910subcounts  ,n17991thiscounts  , n17991lastcounts ,n17991subcounts  ,n068thiscounts  , n068lastcounts ,n068subcounts  ,n17950thiscounts ,n17950lastcounts  ,n17950subcounts  , i17911thiscounts ,i17911lastcounts  ,i17911subcounts  ,i068thiscounts  , i068lastcounts ,i068subcounts  ,i17991thiscounts  , i17991lastcounts , i17991subcounts , address  ) VALUES ('"+strValues+"')";
        database.ExecuteSQL(sSql);
//AfxMessageBox("写入了一条");
// return 1;
}
//////////////////
      

  }      
    // 关闭数据库
    database.Close();

AfxMessageBox("Excel文件写入成功！");
  }
  CATCH_ALL(e)
  {
    TRACE1("Excel驱动没有安装: %s",sDriver);
  }
  END_CATCH_ALL;

#2

好像是vaddress属性里面有异常的字符，比如‘什么，或者是半角汉字等等，请问怎么可以查到一个表的某个属性里面含有单引号’阿？
谢谢帮助！

#3

不要这么Insert Into吧，代码太长了，看的头昏
为什么用Record/Field看看，你这么InsertInto的话在字段值中包含引号的时候会出问题，用参数化查询的话参数个数也太多了一点。



Command what is yours
Conquer what is not

http://www.csdn.net/develop/author/netauthor/jiangsheng/