[转载]导出SQLServer数据到Excel中
<p>信息来源: CSDN</p><span id="ArticleContent1_ArticleContent1_lblContent"> <div style="BORDER-RIGHT: #cccccc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #cccccc 1px solid; PADDING-LEFT: 4px; FONT-SIZE: 13px; PADDING-BOTTOM: 4px; BORDER-LEFT: #cccccc 1px solid; WIDTH: 98%; WORD-BREAK: break-all; PADDING-TOP: 4px; BORDER-BOTTOM: #cccccc 1px solid; BACKGROUND-COLOR: #eeeeee"><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /><span style="COLOR: #000000"><</span><span style="COLOR: #000000">%@Language</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">VBScript%</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"><</span><span style="COLOR: #000000">html</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"><</span><span style="COLOR: #000000">head</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"><</span><span style="COLOR: #000000">metahttp</span><span style="COLOR: #000000">-</span><span style="COLOR: #000000">equiv</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">Content-Type</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">content</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">text/html;charset=gb2312</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"><</span><span style="COLOR: #000000">title</span><span style="COLOR: #000000">></span><span style="COLOR: #000000">导出SQLServer数据到Excel中</span><span style="COLOR: #000000"></</span><span style="COLOR: #000000">title</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"></</span><span style="COLOR: #000000">head</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"><</span><span style="COLOR: #000000">body</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"><</span><span style="COLOR: #000000">%<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Response.Clear<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Response.ContentType</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">text/xls</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Response.AddHeader</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">content-disposition</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">,</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">attachment;filename=Excel.xls</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #008000">'</span><span style="COLOR: #008000">生成Excel</span><span style="COLOR: #008000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000">Response.Write</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">编号</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Chr</span><span style="COLOR: #000000">(</span><span style="COLOR: #000000">9</span><span style="COLOR: #000000">)</span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">姓名</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Chr</span><span style="COLOR: #000000">(</span><span style="COLOR: #000000">9</span><span style="COLOR: #000000">)</span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">性别</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Chr</span><span style="COLOR: #000000">(</span><span style="COLOR: #000000">9</span><span style="COLOR: #000000">)</span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">年龄</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Chr</span><span style="COLOR: #000000">(</span><span style="COLOR: #000000">13</span><span style="COLOR: #000000">)<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">Set</span><span style="COLOR: #000000">Conn</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">Server.CreateObject(</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">Adodb.Connection</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">)<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />ConnStr</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">Provider=SQLOLEDB.1;PersistSecurityInfo=True;UserID=sa;Password=;InitialCatalog=Info;DataSource=127.0.0.1</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Conn.OpenConnStr<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Sql</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">"</span><span style="COLOR: #000000">SELECT*FROMINFO</span><span style="COLOR: #000000">"</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">Set</span><span style="COLOR: #000000">Rs</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">Conn.Execute(Sql)<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Num</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">Rs.Fields.Count<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">Do</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">While</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Not</span><span style="COLOR: #000000">Rs.Eof<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />MM</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">0</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">While</span><span style="COLOR: #000000">MM</span><span style="COLOR: #000000"><</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CInt</span><span style="COLOR: #000000">(Num)<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Data</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">Data</span><span style="COLOR: #000000">&</span><span style="COLOR: #000000">Rs(MM)</span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Chr</span><span style="COLOR: #000000">(</span><span style="COLOR: #000000">9</span><span style="COLOR: #000000">)<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />MM</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000">MM</span><span style="COLOR: #000000">+</span><span style="COLOR: #000000">1</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">Wend</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Response.WriteData</span><span style="COLOR: #000000">&</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Chr</span><span style="COLOR: #000000">(</span><span style="COLOR: #000000">13</span><span style="COLOR: #000000">)<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Data</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">""</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Rs.MoveNext:</span><span style="COLOR: #0000ff">Loop</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">Set</span><span style="COLOR: #000000">Rs</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Nothing</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Conn.Close<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #0000ff">Set</span><span style="COLOR: #000000">Conn</span><span style="COLOR: #000000">=</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">Nothing</span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Response.Flush<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />Response.End<br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" />%</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"></</span><span style="COLOR: #000000">body</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"><br /><img src="http://dicky.cnblogs.com/Images/OutliningIndicators/None.gif" align="top" src_cetemp="/Images/OutliningIndicators/None.gif" /></span><span style="COLOR: #000000"></</span><span style="COLOR: #000000">html</span><span style="COLOR: #000000">></span></div></span><div style="FONT-SIZE: 14px; LINE-HEIGHT: 25px"><strong></strong></div><p />页:
[1]