关于将数据库的一个表保存为excel文件的超级疑惑问题，路过的进来看看，谢谢！

qdcnc168 2003-10-16 08:42:37
我在将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();
...全文
34 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
qdcnc168 2003-10-16
  • 打赏
  • 举报
 回复
好像是vaddress属性里面有异常的字符，比如‘什么，或者是半角汉字等等，请问怎么可以查到一个表的某个属性里面含有单引号’阿？
谢谢帮助！
qdcnc168 2003-10-16
  • 打赏
  • 举报
 回复
//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;
蒋晟 2003-10-16
  • 打赏
  • 举报
 回复
不要这么Insert Into吧，代码太长了，看的头昏
为什么用Record/Field看看，你这么InsertInto的话在字段值中包含引号的时候会出问题，用参数化查询的话参数个数也太多了一点。



Command what is yours
Conquer what is not

http://www.csdn.net/develop/author/netauthor/jiangsheng/
excel文件导入hive乱码_把数据库数据导出到excel
SQL Server BI Step by Step 2--- 使用SSIS进行简单的数据导入导出让我们首先开始学习SSIS吧,利用SSIS把SQL Server中的数据导出.首先,打开Vs.net 2005,选择商业智能项目,然后选择模板中的Integration Service项目,输入项目名称:创建好后,我们就在默认的Package包中进行设计(关于一...文章长征6号2016-11-281...
SpringBoot使用Excel导入数据
上一次出了一篇关于excel表格导出教程，相信看过的小伙伴已经能把数据库的数据以excel表格的形式从后端导出到前端了，这一篇文章我们来看看excel的导入，从前端文件上传excel表格，后端对excel表格内容进行读取，并存储到数据库。 1.导入依赖: 首先介绍一下我们需要的pom依赖： <!--easypoi依赖，excel导入导出的主要依赖--> <dependency> <groupId>cn.afterturn</groupId&.
json文件数据转存mysql数据工具，安利一波
因为我要找到那种能最省时最省力的方法，大家可以想想看，类似转换的问题不是现在才有的，以前肯定是出现过这样的类似问题，我也不是第一个被困扰的那类人，那么这类的问题肯定有前辈已经集成为某些方法开发了API供我们使用，嗯嗯，没错工具一定是会有的！，这个是个良心网址，也不收取任何的费用，导入数据即可生成，至少我觉得是非常实用的，在此强力推荐，安利一波。
在Excel中使用SQL语句查询和筛选
本文转自：http://blog.sina.com.cn/s/blog_5fc375650102e1g5.html 今天在微博上看到@数据分析精选 分享的一篇文章，是关于《在Excel中使用SQL语句实现精确查询》，觉得非常受用，分享给大家。 微博上有人回复评论说直接用vlookup、或者导入数据库进行查询处理就好了，岂不是更高效、更灵活；其实给人的第一直观感觉是这样子的，但是我们多想一步，这...
爬虫实战：爬取豆瓣电影 Top-250 到 Excel 表格中
最近在家无聊自学了python的一些基础知识。后来看到许多朋友都在写爬虫，自己感觉很有意思，也想试一下 >____< 其实本来我是想将数据爬取到excel之后再增加一些数据库操作，然后用flash建立一个网站将数据导入这个网站中…还想着下个模板优化一下网站界面实现数据可视化来着… 但！！！当我万分激动地上号我的pycharm，使用pip安装sqlite3之后，发现我的pycharm无法进行SQLite数据库操作，如图： 但是导入模块时，模块名并没有红色下划线，说明导入成功了啊！而且，当我用尝试
数据库

4,011

社区成员

39,817

社区内容

发帖
与我相关
我的任务
社区描述
VC/MFC 数据库
社区管理员
  • 数据库
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章