asp 生成excel 报表问题 急求

[gbsck]

在查询结果　有几个字段　我要生成EXCEL报表形式！
谁能教教我啊，最好源码比较清楚的。
谢谢各位了。

[gbsck]

谢谢各位了！

[ybfqlyq]

SO
<%Response.ContentType = "application/vnd.ms-excel"%>
把數據讀出到表格裡

[jspadmin]

<script language="javascript">
function tableToExcel() {
window.clipboardData.setData("Text",document.all('theObjTable').outerHTML);
try
{
var ExApp = new ActiveXObject("Excel.Application")
var ExWBk = ExApp.workbooks.add()
var ExWSh = ExWBk.worksheets(1)
ExApp.DisplayAlerts = false
ExApp.visible = true
}
catch(e)
{
alert("您的电脑没有安装Microsoft Excel软件！")
return false
}
ExWBk.worksheets(1).Paste;
}
</script>

<table id=theObjTable>
<tr>
<td>a</td>
<td>b</td>
</tr>
</table>

<input type="button" value="导入到excel" onclick="tableToExcel()">



ASP对Excel的基本操作
1、 建立Excel对象
set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false 不显示警告
objExcelApp.Application.Visible = false 不显示界面
2、 新建Excel文件
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
3、 读取已有Excel文件
strAddr = Server.MapPath(".")
objExcelApp.WorkBooks.Open(strAddr & "\Templet\Table.xls")
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
4、 另存Excel文件
objExcelBook.SaveAs strAddr & "\Temp\Table.xls"
5、 保存Excel文件
objExcelBook.Save （笔者测试时保存成功，页面报错。）
6、 退出Excel操作
objExcelApp.Quit 一定要退出
set objExcelApp = Nothing

[tigerwen01]

<script language="javascript">
function tableToExcel() {
window.clipboardData.setData("Text",document.all('theObjTable').outerHTML);
try
{
var ExApp = new ActiveXObject("Excel.Application")
var ExWBk = ExApp.workbooks.add()
var ExWSh = ExWBk.worksheets(1)
ExApp.DisplayAlerts = false
ExApp.visible = true
}
catch(e)
{
alert("您的电脑没有安装Microsoft Excel软件！")
return false
}
ExWBk.worksheets(1).Paste;
}
</script>

<table id=theObjTable>
<tr>
<td> <%=从数据库里取出数据%> </td>
<td><%=从数据库里取出数据%></td>
</tr>
<tr>
<td><%=从数据库里取出数据%></td>
<td><%=从数据库里取出数据%></td>
</tr>

</table>

<input type="button" value="导入到excel" onclick="tableToExcel()">

[beyoo]

为了你能看的更明白些。把全部代码贴出。

set rs = server.CreateObject("adodb.recordset")
rs.Open sql,conn
if rs.EOF and rs.BOF then
'写入未生成单号的所有记录到EXCEL表中
sql = "select * from outward "
sql = sql + " right outer join outward_detail on outward.outw_id = outward_detail.detail_outw_id "
sql = sql + " join guest on outward_detail.detail_guest_id = guest.guest_id "
sql = sql + " join dc on outward.outw_dc_id = dc.dc_id "
'2002-9-17修改为左连接
'sql = sql + " join detail_info on outward.outw_id = detail_info.info_outw_id "
'sql = sql + " left outer join detail_info on detail_info.info_detail_id = outward_detail.detail_id "
sql = sql + " join product on outward_detail.detail_prod_id = product.prod_id "
sql = sql + " join prod_type on product.prod_flag = prod_type.id "
sql = sql + " where outward.outw_date >= '"&start_date&"' and outward.outw_date <= '"&end_date&"' "
sql = sql + " and outward.outw_companyid = "&newcompanyid&" "
if smallinput <> 0 then
sql = sql + " and outward.outw_dc_id = "&smallinput&" "
else
if flag > 3 then
sql = sql + " and outward.outw_dc_id in ("&dcservice&") "
end if
end if
if cstr(wood_type) = "1" then
sql = sql + " and product.prod_dir_flag = 0 "
elseif cstr(wood_type) = "2" then
sql = sql + " and product.prod_dir_flag = 1 "
end if

if clng(oddsia) = 1 then
if smalllocation <> 0 then
sql = sql + " and outward_detail.detail_prod_id = "&smalllocation&" "
end if
elseif clng(oddsia) = 2 then
if prod_type2 <> "" then
sql = sql + " and product.prod_type = '"&prod_type2&"' "
end if
if prod_color <> "" then
sql = sql + " and product.prod_color = '"&prod_color&"' "
end if
end if
sql = sql + " and outward_detail.detail_elink_id = 0 "
sql = sql + " order by outward_detail.detail_id asc "
'response.Write sql
'response.End
set rs_2 = server.CreateObject("adodb.recordset")
rs_2.Open sql,conn,1,1
if not rs_2.EOF then

Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
'filename = "D:\mir_20020418\companyadmin\"&Session("companymanager")&"_online.xls"
filename = server.MapPath(""&User&"_online_out.xls")
filename2 = "operate\"&User&"_online_out.xls"
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)
strline = strline & "填写日期" & chr(9) & "SRA" & chr(9) & "DC编号" & chr(9) & "城市" & chr(9) & "客户名称" & chr(9) & "客户地址" & chr(9) & "联系电话" & chr(9) & "传真" & chr(9) & "收货代码" & chr(9) & "客服出库方式" & chr(9) & "产品类别" & chr(9) & "产品编号" & chr(9) & "品名" & chr(9) & "产品型号" & chr(9) & "产品颜色" & chr(9) & "数量" & chr(9) & "易联单号" & chr(9) & "航空单号" & chr(9) & "航班号" & chr(9) & "预计出发时间" & chr(9) & "预计到达时间" & chr(9) & "签收时间" & chr(9) & "签收人" & chr(9) & "身份证" & chr(9) & "预计到达时间" & chr(9) & "实际重量" & chr(9) & "记费重量" & chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine

[beyoo]

do while not rs_2.EOF
strLine=""
'for each x in rs.Fields
' strLine= strLine & x.value & chr(9)
'next
outw_date = rs_2("outw_date")
outw_sra = trim(rs_2("outw_sra"))
if isnumeric(outw_sra) = true then
outw_sra = outw_sra & "'"
end if
dc_code = rs_2("dc_code")
dc_name = rs_2("dc_name")
guest_user = rs_2("guest_user")
guest_address = rs_2("guest_address")
guest_tel = rs_2("guest_tel")
guest_fax = rs_2("guest_fax")
guest_takecode = rs_2("guest_takecode")
'info_type = rs_2("info_type")
prod_no = rs_2("prod_no")
prod_pinming = rs_2("prod_pinming")
type_name = rs_2("type_name")
prod_type = rs_2("prod_type")
prod_color = rs_2("prod_color")
detail_no = rs_2("detail_no")
elink_odd = " "
rec_idcard = " "
air_take_no = " "
air_line_no = " "
air_etd = " "
air_eta = " "
'info_yuji_time = rs_2("info_yuji_time")
database_outw_id = rs_2("detail_outw_id")
database_guest_id = rs_2("detail_guest_id")
sql = "select * from detail_info where info_detail_id = (select min(detail_id) as detail_id from outward_detail where detail_outw_id = "&database_outw_id&" and detail_guest_id = "&database_guest_id&") "
set rst = conn.execute(sql)
if not rst.eof then
info_type = rst("info_type")
info_takeman_id = rst("info_takeman_id")
info_yuji_time = rst("info_yuji_time")
else
info_type = " "
info_takeman_id = " "
info_yuji_time = " "
end if
rst.close
set rst = nothing



strLine = strLine & outw_date& chr(9) & outw_sra & chr(9) & dc_code& chr(9) & dc_name& chr(9) & guest_user& chr(9) & guest_address& chr(9) & guest_tel& chr(9) & guest_fax& chr(9) & guest_takecode& chr(9)& info_type& chr(9)& type_name & chr(9) & prod_no& chr(9) & prod_pinming& chr(9) & prod_type& chr(9) & prod_color& chr(9) & detail_no& chr(9) & elink_odd & chr(9) & air_take_no & chr(9) & air_line_no & chr(9) & air_etd & chr(9) & air_eta & chr(9) & " " & chr(9) & " " & chr(9) & rec_idcard & chr(9) & info_yuji_time & chr(9) & " " & chr(9) & " "
'--将表的数据写入EXCEL
myfile.writeline strLine

rs_2.MoveNext
loop
else
tt = 0
response.Write "无记录!"
response.Write "<a href=""javascript:history.go(-1)""><font size=+2> 后 退 </font></a>"
end if
rs_2.Close
set rs_2 = nothing

[beyoo]

else

Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
'filename = "D:\mir_20020418\companyadmin\"&Session("companymanager")&"_online.xls"
filename = server.MapPath(""&User&"_online_out.xls")
filename2 = "operate\"&User&"_online_out.xls"
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)


dim strLine,responsestr
strLine=""
'For each x in rs.fields
' strLine= strLine & x.name & chr(9)
'Next
strline = strline & "填写日期" & chr(9) & "SRA" & chr(9) & "DC编号" & chr(9) & "城市" & chr(9) & "客户名称" & chr(9) & "客户地址" & chr(9) & "联系电话" & chr(9) & "传真" & chr(9) & "收货代码" & chr(9) & "客服出库方式" & chr(9) & "产品类别" & chr(9) & "产品编号" & chr(9) & "品名" & chr(9) & "产品型号" & chr(9) & "产品颜色" & chr(9) & "数量" & chr(9) & "易联单号" & chr(9) & "航空单号" & chr(9) & "航班号" & chr(9) & "预计出发时间" & chr(9) & "预计到达时间" & chr(9) & "签收时间" & chr(9) & "签收人" & chr(9) & "身份证" & chr(9) & "预计到达时间" & chr(9) & "实际重量" & chr(9) & "记费重量" & chr(9) & "调度出库方式" & chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine
Do while Not rs.EOF
strLine=""
'for each x in rs.Fields
' strLine= strLine & x.value & chr(9)
'next
outw_sra = trim(rs("outw_sra"))
if isnumeric(outw_sra) = true then
outw_sra = outw_sra & "'"
end if
elink_odd = trim(rs("elink_odd"))
if isnumeric(elink_odd) = true then
elink_odd = elink_odd & "'"
end if
rec_idcard = trim(rs("rec_idcard"))
if isnumeric(rec_idcard) = true then
rec_idcard = rec_idcard & "'"
end if
database_outw_id = rs("detail_outw_id")
database_guest_id = rs("detail_guest_id")
transport_type = rs("transport_type")
if transport_type = 1 then
transport_type_no = "汽运"
elseif transport_type = 2 then
transport_type_no = "空运"
elseif transport_type = 3 then
transport_type_no = "铁路"
elseif transport_type = 4 then
transport_type_no = "EMS"
else
transport_type_no = " "
end if


sql = "select * from detail_info where info_detail_id = (select min(detail_id) as detail_id from outward_detail where detail_outw_id = "&database_outw_id&" and detail_guest_id = "&database_guest_id&") "
set rst = conn.execute(sql)
if not rst.eof then
info_type = rst("info_type")
info_takeman_id = rst("info_takeman_id")
info_yuji_time = rst("info_yuji_time")
else
info_type = " "
info_takeman_id = " "
info_yuji_time = " "
end if
rst.close
set rst = nothing

transport_type = rs("transport_type")
if transport_type = 2 then
sql = "select * from air where air_elink_id = "&rs("elink_id")&""
set rs_air = conn.execute(sql)
if not rs_air.eof then
air_take_no = rs_air("air_take_no")
air_line_no = rs_air("air_line_no")
air_etd = rs_air("air_etd")
air_eta = rs_air("air_eta")
else
air_take_no = " "
air_line_no = " "
air_etd = " "
air_eta = " "
end if
rs_air.close
set rs_air = nothing
else
air_take_no = " "
air_line_no = " "
air_etd = " "
air_eta = " "
end if

[喜受写代码的小强]

good