重庆移动通话清单统计(EXCEL文件从重庆移动网上下载)

最新推荐文章于 2024-09-18 15:20:49 发布

xck01

最新推荐文章于 2024-09-18 15:20:49 发布

阅读量4k

点赞数

分类专栏： ASP 文章标签： excel vbscript properties html border table

本文链接：https://blog.csdn.net/xck01/article/details/723554

版权

ASP 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>科长电话清单</title>
</head>

<body>
<table border="1" width="90%" align="center">
<tr align="center" bgcolor="#E6E6E6">
   <td>查询号码</td><td>对方号码</td><td>通话时间</td><td>时长</td><td>类型</td><td>费用</td>
</tr>
<%
'建立Connection对象
Dim db,rs,strSql,dbxls
dbxls="book1.xls"
Set db = Server.CreateObject("ADODB.Connection")
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(""&dbxls&"") & ";Extended Properties=Excel 8.0;"
db.Open connstr
'打开记录集，表名一定要以"[表名$]"的格式
strSql="Select 查询号码,对方号码,通话时间,时长,类型,费用 From [Sheet1$] order by 类型"
Set rs=db.Execute(strSql)
'循环读取所有行
   n=0
   dim ydcount,notydcount,hycount,dxcount'移动通话累计和非移动通话累计分钟数,话费合计,短信费合计
   ydcount=0
   notydcount=0
   hycount=0
   hycount=hycount+28'加月租费
   dxcount=0
Do While Not rs.EOF
   n=n+1
   if trim(rs("类型"))="主叫短信" then dxcount=dxcount+ccur(rs("费用"))
    if rs("费用")="28" or rs("类型")="(元)" then
    '路过
    else
    '计算和移动用户的通话累计分钟数
    if rs("类型")="被叫" or rs("类型")="主叫" then
    if instr("134|135|136|137|138|139|159",left(rs("对方号码"),3))>0 then isyd=1 else isyd=0
    hycount=hycount+CCur(rs("费用"))'计算通话费用
    select case isyd
    case 1
    tstr=split(rs("时长"),":")
    s=cint(tstr(0))
    f=cint(tstr(1))
    m=cint(tstr(2))
    s=s*60
    if m<>0 then m=1
    tempcount=s+f+m
    ydcount=ydcount+tempcount
    case 0
    tstr=split(rs("时长"),":")
    s=cint(tstr(0))
    f=cint(tstr(1))
    m=cint(tstr(2))
    s=s*60
    if m<>0 then m=1
    tempcount=s+f+m
    notydcount=notydcount+tempcount
    end select
    end if

   Response.Write "<tr align=/'center/'>"
   Response.Write "<td>" & rs("查询号码") & "</td>"
   Response.Write "<td>" & rs("对方号码") & "</td>"
   Response.Write "<td>" & rs("通话时间") & "</td>"
   Response.Write "<td>" & rs("时长")&"|"&tempcount& "</td>"
   Response.Write "<td>" & rs("类型") & "</td>"
   Response.Write "<td>" & rs("费用") & "</td>"
   Response.Write "</tr>"
   end if
   rs.MoveNext
Loop

'关闭对象
rs.Close
Set rs=nothing
db.Close
Set db=Nothing
%>
</table>
<%response.write"<div align=center>网内通话:"&ydcount&"分钟网外通话:"&notydcount&"分钟合计:"&ydcount+notydcount&"分钟平均每分钟:"&round(hycount/(ydcount+notydcount),3)&"元通话费:"&hycount&"元短信费:"&dxcount&"元</div>" %>
</body>
</html>