Question

ASP中将SQL内指定表格导出为Excel

求源码，网上直接找来的就免了。请给一个有说明的，我小白
数据库地址 127.0.0.1
数据库名称 newdata
数据库登录用户 admin
登录密码 123456
表名称 myaddress
表项很多

求一个傻瓜源码，可加分
1楼的朋友，你那个是ASP的程序么？我感觉不是哦~~~~~
2楼的朋友，你那个应该是制定江sql内的表列到excel表列然后导出吧？生成的文件如果在网站目录下我感觉比较不安全，能不能通过web方式在本地生成。另外1楼的朋友那个代码应该是不用指定列的，我很喜欢，可以看不懂。并不是我懒啊~~~说过了是小白~~~有注释我当然欢迎阿~~~给个能用的asp吧~~空间不支持。net

3楼的朋友~~~你给出的不错哦，但是我的这个数据库是sql~~怎么连？

Answer 1

ASP将ACCESS数据导出至EXCEL

<script for="window" event="onload">
window.resizeTo(400,300)
</script>
<%
set conn=Server.CreateObject("adodb.connection")
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& Server.MapPath("../data/xin#tong.mdb")'//////链接数据库
conn.open connstr
dim rs,sql,filename,fs,myfile,x
Set fs=server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename=Server.MapPath(""&request("exit")&".xls")'/////数据表保存的文件名
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile=fs.CreateTextFile(filename,true)
'///////从数据库中把你想放到EXCEL中的数据查出来
Set rs=Server.CreateObject("ADODB.Recordset")
sql="select * from "&request("exit")&""
rs.open sql,conn,1,1
if not rs.EOF then
dim strLine,responsestr
strLine=""
For each x in rs.fields
strLine=strLine & x.name & chr(9)
Next
'--将表的列名先写入EXCEL
myfile.writeline strLine
Do while Not rs.EOF
strLine=""
for each x in rs.Fields
strLine=strLine & x.value & chr(9)
next
'--将表的数据写入EXCEL
myfile.writeline strLine
rs.MoveNext
loop
end if
rs.Close
set rs=nothing
Response.Write "<BR><BR><center><b>导出成功,请选择继续操作</b></center>"
response.Write "<table width=90% cellspacing=1 cellpadding=3 align=center>"
Response.Write "<tr align=center> <td>"
response.write ("<font color=green>√</font><a href='"&request("exit")&".xls'>下载</a>") & "  <font color= green>√</font><a href=javascript:window.close()>关闭</a>"
Response.Write "</td></tr></table>"
%>

ASP 的以前做的.应该注释的地方都注释了,不明白的再问我.

Answer 2

<!--#include file="../../public/conn.asp" -->

<%
Dim sql : sql = "Select * From [user] "

Dim xmlStr
xmlStr = "<table border='1' cellspacing='0' cellpadding='0'>"
xmlStr = xmlStr & "<tr><th colspan='10'>申报单位明细表</th></tr>"
xmlStr = xmlStr & "<tr>"
xmlStr = xmlStr & "<td>序号</td><td>单位名称</td><td>单位地址</td><td>负责人</td><td>职工总数</td><td>联系人</td><td>联系电话</td><td>归口部门</td><td>单位类别</td><td>审核状态</td>"
xmlStr = xmlStr & "</tr>"

OpenConn()
Dim rs : Set rs = conn.execute(sql)
dim i : i=1
do while not rs.eof
xmlStr = xmlStr & "<tr>"
xmlStr = xmlStr & "<td>" & i & "</td>"
xmlStr = xmlStr & "<td>" & rs("companyName") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Address") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Corporation") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Personnel") & "</td>"
xmlStr = xmlStr & "<td>" & rs("receiver") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Phone")& "</td>"
xmlStr = xmlStr & "<td>" & rs("departMent") & "</td>"
xmlStr = xmlStr & "<td>" & rs("kind")& "</td>"
xmlStr = xmlStr & "<td>" & iif(rs("LockUser")=0,"未审","已审") & "</td>"
xmlStr = xmlStr & "</tr>" &vbCrLf
rs.movenext
i=i+1
loop
Co(rs) : CloseConn()
xmlStr = xmlStr & "</table>"

Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","attachment;filename=sbdwUser.xls"

Response.Write xmlStr
%>
这是我写的一个程序,给你参考,其实非常简单,就是增加了
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","attachment;filename=sbdwUser.xls"
这两句,它的作用是把本来输出到浏览器的内容输出到EXCEL文件

Answer 3

private void Button1_Click(object sender, System.EventArgs e)
{
DataTable dt=new DataTable();
SqlConnection con=new SqlConnection("Server=127.0.0.1 ;user id=admin;pwd=123456;database=newdata");
con.Open();
string sql="select * from myaddress";
SqlDataAdapter sda=new SqlDataAdapter(sql,con);
sda.Fill(dt);
con.Close();
int lie = dt.Columns.Count;
int row=dt.Rows.Count;
if(row<=0)
{
this.msg.Text="无可导出数据！";//有一个MSG的label用于提示消息
}
else
{
string mm=Server.MapPath(".")+"\\Document\\ss.xls";//需要在该程序所在文件夹下新建一个叫DOCUMENT的文件夹

if (System.IO.File.Exists(mm.ToString()))
{
System.IO.File.Delete(mm.ToString());
}

Excel.Application oExcel;
oExcel = new Excel.Application();
try
{

Excel.Workbook oBook;
Object oMissing = System.Reflection.Missing.Value;
oBook = oExcel.Workbooks.Add(oMissing);
HttpResponse response = HttpContext.Current.Response;
int t=0;
string[,] strData = new string[row,lie];
for(int i=0;i<row;i++)//内容
{
for(int j=0;j<lie;j++)
{

strData[i,t++]=dt.Rows[i][j].ToString();
}
t=0;
}
Excel.Worksheet oSheet = (Excel.Worksheet)oBook.ActiveSheet;
Excel.Range rg = oSheet.get_Range(oSheet.Cells[1,1],oSheet.Cells[row,lie]);
rg.Select();
rg.Value2 = strData;
oBook.Saved = true;
oExcel.UserControl = false;
oSheet.Columns.AutoFit();
oExcel.ActiveWorkbook.SaveCopyAs(mm);
//oBook.Close(null,null,null);
oBook.Close(false,oMissing,oMissing);
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(rg);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);

oExcel=null;
oSheet=null;
oBook=null;
rg=null;

System.GC.Collect();
string filename="ss.xls";//导出的EXCEL表名为ss.xls
System.IO.FileInfo info = new System.IO.FileInfo(Server.MapPath("~/") + "\\Document\\" + filename);
Response.AddHeader("content-type", "application/zip");
Response.AddHeader("content-length", info.Length.ToString());
Response.AddHeader("content-disposition", "attachment;filename=" + filename + "");
Response.WriteFile(Server.MapPath("~/") + "\\Document\\" + filename);
}
catch(Exception ex)
{
this.msg.Text=ex.ToString();
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
System.GC.Collect();
}
}

//附说一句：鉴于你这么懒，也就没给你过多注释了。。