Asp 导出Excel

最新推荐文章于 2022-01-17 15:52:17 发布

晓风拂月

最新推荐文章于 2022-01-17 15:52:17 发布

阅读量2.4k

点赞数

分类专栏： Asp 文章标签： asp sql session excel

本文链接：https://blog.csdn.net/jinhuiSystem/article/details/6144266

版权

Asp 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

<%
Dim xlWorkSheet
Dim xlApplication
Set xlApplication = Server.CreateObject("Excel.Application")
xlApplication.Visible = False
xlApplication.Workbooks.Add
Set xlWorksheet = xlApplication.Worksheets(1)
set oRS=Server.CreateObject("ADODB.Recordset")
if Request("ImportExcel")="ImportExcel" then
  'If Response.IsClientConnected Then
'  Response.Flush
'  Else
'  Response.End
'  End If
  Response.Expires = 0
  Response.Expiresabsolute = Now() - 1
  Response.AddHeader "Content-type","application/octet-stream"
  Response.AddHeader "Accept-Ranges","bytes"
  Response.AddHeader "Content-Disposition","attachment; filename=kl178exp.xls"
  'Response.ContentType = "application/msexcel"
  sql =session("SQL")
  oRS.open sql,conns,1,3,1
  xlWorksheet.Cells(1,1).Value = "用户名"
  xlWorksheet.Cells(1,2).Value = "真实姓名"
  xlWorksheet.Cells(1,3).Value = "用户类型"
  xlWorksheet.Cells(1,4).Value = "注册时间"
  xlWorksheet.Cells(1,5).Value = "首次登录时间"
  xlWorksheet.Cells(1,6).Value = "最后登录时间"
  xlWorksheet.Cells(1,7).Value = "使用频率"
  xlWorksheet.Cells(1,8).Value = "停留时间"
  xlWorksheet.Cells(1,9).Value = "券商名称"
  xlWorksheet.Cells(1,10).Value = "业务员"
  xlWorksheet.Cells(1,1).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,2).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,3).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,4).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,5).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,6).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,7).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,8).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,9).Interior.ColorIndex = 16
  xlWorksheet.Cells(1,10).Interior.ColorIndex=16
  xlWorksheet.Cells(1,4).ColumnWidth=20
  xlWorksheet.Cells(1,5).ColumnWidth=20
  xlWorksheet.Cells(1,6).ColumnWidth=20

  iRow = 2
  If Not oRS.EOF Then
   Do Until oRS.EOF
  xlWorksheet.Cells(iRow,1).Value = oRS("UserName")
  xlWorksheet.Cells(iRow,2).Value = oRS("realName")
  xlWorksheet.Cells(iRow,3).Value = GetUserType(oRS("Usertype"))
  xlWorksheet.Cells(iRow,4).Value = FormatDate(oRS("RegisterTime"))
  xlWorksheet.Cells(iRow,5).Value = FormatDate(oRS("FirstLoginTime"))
  xlWorksheet.Cells(iRow,6).Value = FormatDate(oRS("LastLoginTime"))
  xlWorksheet.Cells(iRow,7).Value = oRS("UsedGroup")
  xlWorksheet.Cells(iRow,8).Value = oRS("UsedTime")
  xlWorksheet.Cells(iRow,9).Value = GetCompanyNameByID(oRS("CompanyID"))
  xlWorksheet.Cells(iRow,10).Value =oRS("introducer")
  iRow = iRow + 1
  oRS.MoveNext
  Loop
  End If
   rannum=int(1000*rnd)
   YoureIp=trim(Request.ServerVariables("REMOTE_ADDR"))
   YoureIp=left(YoureIp,len(YoureIp)-4)
   YoureIp=replace(YoureIp,".","")
  rndfile=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)&rannum&YoureIp
  strFile = rndfile
  xlWorksheet.SaveAs Server.MapPath(strFile & ".xls")
  xlApplication.Quit
  'Close the Workbook
  Set xlWorksheet = Nothing
  Set xlApplication = Nothing
  response.Redirect(strFile & ".xls")
end if