PHP生成excel并向其输入数据
- PHP中文网原创
- 2016-05-25 17:13:061263浏览
download_excel.php
<?php
$sql_excel="";
if(isset($_SESSION["excel"]))
{
$sql_excel=$_SESSION["excel"];
}
unset($_SESSION["excel"]);
$conn=mysql_connect("localhost","root","admin");
mysql_select_db("sql1");
$result=mysql_query($sql_excel);
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=export_data.xls");
$result=mysql_query($sql_excel);
echo "id"."\t";
echo "收款合同号"."\t";
echo "渠道名称"."\t";
echo "累计渠道代收金额"."\t";
echo "累计渠道成本"."\t";
echo "累计调整渠道成本"."\t";
echo "累计应收账款"."\t";
echo "累计回款"."\t";
echo "累计未回款"."\t";
echo "当月渠道代收金额"."\t";
echo "当月渠道成本"."\t";
echo "当月调整渠道成本（元）"."\t";
echo "当月应收账款"."\t";
echo "当月回款"."\t";
echo "当月未回款"."\t";
echo "\n";
$sum_channel_money=0;
$sum_add_channel_cost=0;
$sum_channel_change_cost=0;
$sum_add_accounts=0;
$sum_add_back_money=0;
$sum_add_noback_money=0;
$sum_month_channel_money=0;
$sum_month_channel_cost=0;
$sum_month_channel_change_cost=0;
$sum_month_accounts=0;
$sum_month_back_money=0;
$sum_month_noback_money=0;
while($out=mysql_fetch_array($result))
{
// foreach ($out as $values)
// echo $values."\t";
// echo "\n";
echo $out["id"]."\t";
echo $out['collection_contract_id']."\t";
echo $out['channel_name']."\t";
echo $out['channel_money']."\t";
$sum_channel_money=$sum_channel_money+$out['channel_money'];
echo $out['add_channel_cost']."\t";
$sum_add_channel_cost=$sum_add_channel_cost+$out['add_channel_cost'];
echo $out['add_channel_change_cost']."\t";
$sum_channel_change_cost=$sum_channel_change_cost+$out['add_channel_change_cost'];
echo $out['add_accounts']."\t";
$sum_add_accounts=$sum_add_accounts+$out['add_accounts'];
echo $out['add_back_money']."\t";
$sum_add_back_money=$sum_add_back_money+$out['add_back_money'];
echo $out['add_noback_money']."\t";
$sum_add_noback_money=$sum_add_noback_money+$out['add_noback_money'];
echo $out['month_channel_money']."\t";
$sum_month_channel_money=$sum_month_channel_money+$out['month_channel_money'];
echo $out['month_channel_cost']."\t";
$sum_month_channel_cost=$sum_month_channel_cost+$out['month_channel_cost'];
echo $out['month_channel_change_cost']."\t";
$sum_month_channel_change_cost=$sum_month_channel_change_cost+$out['month_channel_change_cost'];
echo $out['month_accounts']."\t";
$sum_month_accounts=$sum_month_accounts+$out['month_accounts'];
echo $out['month_back_money']."\t";
$sum_month_back_money=$sum_month_back_money+$out['month_back_money'];
echo $out['month_noback_money']."\t";
$sum_month_noback_money=$sum_month_noback_money+$out['month_noback_money'];
echo "\n";
}
echo "总计"."\t";
echo ""."\t";
echo ""."\t";
echo $sum_channel_money."\t";
echo $sum_add_channel_cost."\t";
echo $sum_channel_change_cost."\t";
echo $sum_add_accounts."\t";
echo $sum_add_back_money."\t";
echo $sum_add_noback_money."\t";
echo $sum_month_channel_money."\t";
echo $sum_month_channel_cost."\t";
echo $sum_month_channel_change_cost."\t";
echo $sum_month_accounts."\t";
echo $sum_month_back_money."\t";
echo $sum_month_noback_money."\t";
?>index2.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script>
function submit_query()
{
var time2=document.getElementById("time2").value;
var key=document.getElementById("key").value;
// pattern_time2=/^[0-9A-Za-z]+$/;
// if(pattern_time2.test(time2)==false)
// {
// alert("收款合同号不合法!");
// }
// if(key.length==0)
// {
// alert("渠道名称不能为空!");
// }
// if(pattern_time2.test(time2)&&key.length>0)
// {
var form1=document.getElementById("form1");
form1.submit();
// }
}
function excel()
{
window.location.href="download_excel.php";
}
</script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无线支付数据报表平台</title>
<link href="styles/layout.css" rel="stylesheet" type="text/css" />
<link href="styles/wysiwyg.css" rel="stylesheet" type="text/css" />
<!-- Theme Start -->
<link href="themes/blue/styles.css" rel="stylesheet" type="text/css" />
<!-- Theme End -->
</head>
<body id="homepage">
<p id="header"><img src="img/logo.gif" alt="无线支付平台" class="logo" width="468px" height="45" /></p>
<!-- Top Breadcrumb Start -->
<p id="breadcrumb">
<ul>
<li><img src="img/icons/icon_breadcrumb.png" alt="Location" /></li>
<li><strong>当前位置:</strong></li>
<li><a href="#" title="">平台首页</a></li>
- 渠道应收表
</ul>
</p>
<!-- Top Breadcrumb End -->
<!-- Right Side/Main Content Start -->
<p id="rightside">
<!-- Alternative Content Box Start -->
<p class="contentcontainer" style="position:absolute;width:990px;overflow:auto;" >
<p class="headings altheading" style="width:2000px;">
<h2><strong>渠道应收表</strong></h2>
</p>
<p class="contentbox" style="width:2000px;">
<table width="100%">
<tbody>
<tr style="font-size:14px" width="100%">
<td><table>
<form action="index2.php" method="get" id="form1">
<tr>
<td> 收款合同号:
<input style="width:100px" name="time2" type="text" value="{$time2_value}" />
渠道名称关键字:
<input name="key" type="text" value="{$key_value}" />
<input name="button" type="submit" value="查询" onclick="submit_query()" />
<input name="button" type="button" value="导出数据" onclick="excel()" />
</td>
</tr>
</form>
</table></td>
</tr>
<tr>
<td><hr /></td>
</tr>
</tbody>
</table>
<table width="100%">
<thead>
<tr>
<th><p align="center">收款合同号</p></th>
<th><p align="center">渠道名称</p></th>
<th><p align="center">累计渠道代收金额</p></th>
<th><p align="center">累计渠道成本</p></th>
<th><p align="center">累计调整渠道成本</p></th>
<th><p align="center">累计应收账款</p></th>
<th><p align="center">累计回款</p></th>
<th><p align="center">累计未回款</p></th>
<th><p align="center">当月渠道代收金额</p></th>
<th><p align="center">当月渠道成本</p></th>
<th><p align="center">当月调整渠道成本（元）</p></th>
<th><p align="center">当月应收账款</p></th>
<th><p align="center">当月回款</p></th>
<th><p align="center">当月未回款</p></th>
</tr>
</thead>
<tbody>
{$outpagelist}
</tbody>
<tfoot>
{$tablefoot}
</tfoot>
</table>
<ul class="pagination">
<li class="text">前一页</li>
<li class="page"><a href="#" title="">1</a></li>
<li><a href="#" title="">2</a></li>
<li><a href="#" title="">3</a></li>
<li><a href="#" title="">4</a></li>
<li class="text"><a href="#" title="">下一页</a></li>
</ul>
<p style="clear: both;"></p>
</p>
</p>
<!-- Alternative Content Box End -->
</p>
<!-- Right Side/Main Content End -->
<!-- Left Dark Bar Start -->
<p id="leftside">
<p class="user">
<img src="img/avatar.png" width="44" height="44" class="hoverimg" alt="Avatar" />
<p>欢迎您:</p>
<p class="username">边乐乐</p>
<p class="userbtn"><a href="#" title="">登 出</a></p>
</p>
<p class="notifications" style="display:none">
<p class="notifycount"><a href="" title="" class="notifypop">10</a></p>
<p><a href="" title="" class="notifypop">您有新的消息</a></p>
<p class="smltxt">(点击查看)</p>
</p>
<ul id="nav">
<li>
<a class="collapsed heading">收入</a>
<ul class="navigation">
<li><a href="index.html" title="">产品交易明细表</a></li>
<li><a href="#" title="">产品收入汇总表</a></li>
</ul>
</li>
<li>
<a class="collapsed heading">应收及收款</a>
<ul class="navigation">
<li><a href="index2.html" title="">渠道应收表</a></li>
<li><a href="#" title="">渠道回款录入及表格</a></li>
<li><a href="#" title="">交易明细及回款拆分表</a></li>
</ul>
</li>
<li><a class="collapsed heading">分成</a>
<ul class="navigation">
<li><a href="#" title="">供应商结算明细表</a></li>
<li><a href="index3.html" title="">供应商结算汇总表</a></li>
<li><a href="index4.html" title="">供应商结算支付明细表</a></li>
<li><a href="index5.html" title="">供应商结算支付汇总表</a></li>
<li><a href="#" title="">供应商结算余额表</a></li>
</ul>
</li>
<li>
<a class="collapsed heading">基础合同管理</a>
<ul class="navigation">
<li><a href="#" title="">付款合同信息表</a></li>
<li><a href="#" title="">收款合同信息表</a></li>
</ul>
</li>
<li>
<a style="background: url('themes/blue/img/bg_navigation_out.png') repeat scroll 0 -38px transparent; border-bottom: 1px solid #006F93; border-top: 1px solid #2DABD5; text-shadow: 1px 1px 1px #0D2C35; color: #FFFFFF; cursor: pointer; display: block; font-size: 16px; font-weight: 700; line-height: 1.2em; padding: 9px 10px; width: 205px;">退出登录</a>
</li>
</ul>
</p>
<!-- Left Dark Bar End -->
<!-- Notifications Box/Pop-Up Start -->
<p id="notificationsbox">
<h4><strong>新 消 息</strong></h4>
<ul>
<li>
<a href="#" title=""><img src="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
<h5><a href="#" title="">您的应用:XXXX1被驳回</a></h5>
<p>来自NPP审核组 2011-07-29 12:00:00 </p>
</li>
<li>
<a href="#" title=""><img src="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
<h5><a href="#" title="">您的应用:XXXX2审核通过</a></h5>
<p>来自NPP审核组 2011-07-29 12:00:00 </p>
</li>
<li>
<a href="#" title=""><img src="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
<h5><a href="#" title="">您的应用:XXXX2审核被驳回</a></h5>
<p>来自NPP审核组 2011-07-29 12:00:00 </p>
</li>
<li>
<a href="#" title=""><img src="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
<h5><a href="#" title="">API（J2ME版）已推出新版本</a></h5>
<p>来自NPP开发组 2011-07-29 12:00:00 </p>
</li>
<li>
<a href="#" title=""><img src="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
<h5><a href="#" title="">NOKIA支付平台推出新支付方式</a></h5>
<p>来自NPP运营组 2011-07-29 12:00:00 </p>
</li>
<li>
<a href="#" title=""><img src="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
<h5><a href="#" title="">NOKIA支付平台新接入新支付渠道</a></h5>
<p>来自NPP运营组 2011-07-29 12:00:00 </p>
</li>
</ul>
<p class="loadmore"><a href="#" title="">查看更多</a></p>
</p>
<!-- Notifications Box/Pop-Up End -->
<script type="text/javascript" src="scripts/enhance.js"></script>
<script type='text/javascript' src='scripts/excanvas.js'></script>
<script type='text/javascript' src='scripts/jquery.min.js'></script>
<script type='text/javascript' src='scripts/jquery-ui.min.js'></script>
<script type='text/javascript' src='scripts/jquery.wysiwyg.js'></script>
<script type='text/javascript' src='scripts/visualize.jQuery.js'></script>
<script type="text/javascript" src='scripts/functions.js'></script>
<!--[if IE 6]>
<script type='text/javascript' src='scripts/png_fix.js'></script>
<script type='text/javascript'>
DD_belatedPNG.fix('img, .notifycount, .selected');
</script>
<![endif]-->
</body>
</html>index2.php
<?php
$collection_contract_id="";
$channel_name="";
function check_text($text)
{
if(isset($_REQUEST[$text]))
{
return $_REQUEST[$text];
}
}
include_once 'smarty_inc.php';
$sql="select * from channel_accounts where ";
if(isset($_GET["time2"])&&$_GET["time2"]!="")
{
$collection_contract_id=$_GET["time2"];
if(!preg_match("/^[A-Za-z0-9]+$/", $collection_contract_id))
{
echo "<script>alert('收款合同号输入不合法!');</script>";
$smarty->display("index2.html");
exit();
}
$sql.=" collection_contract_id='".$collection_contract_id."' and ";
}
if(isset($_GET["key"])&&$_GET["key"]!="")
{
$channel_name=$_GET["key"];
if($channel_name=="")
{
echo "<script>alert('渠道名称输入不合法!');</script>";
$smarty->display("index2.html");
exit();
}
$sql.=" channel_name='".$channel_name."' and ";
}
$sql.=" 1=1 ";
//分页
if (isset($_GET['page']) && $_GET['page'] > 0)
{
$page = $_GET['page'];
}
else
{
$page = 1;
}
$eachPages = 10;
$url = $_SERVER['PHP_SELF'];
$sql2 = "SELECT count(*) as total FROM (".$sql.") as a";
$result = mysql_query($sql2);
list($rowNums) = mysql_fetch_row($result);
$pageNums = ceil($rowNums / $eachPages);
$rowFrom = ($page > 1) ? (($page - 1) * $eachPages) : 0;
$sql2 = "".$sql." LIMIT ".$rowFrom.",".$eachPages."";
$result = mysql_query($sql2);
//------分页结束-------
//显示数据
$_SESSION["excel"]=$sql;
$result=mysql_query($sql2);
$outpagelist="";//输出数据的变量
while($out=mysql_fetch_array($result))
{
$outpagelist.="<tr>";
$outpagelist.="<td>".$out['collection_contract_id']."</td>";
$outpagelist.="<td>".$out['channel_name']."</td>";
$outpagelist.="<td>".$out['channel_money']."</td>";
$outpagelist.="<td>".$out['add_channel_cost']."</td>";
$outpagelist.="<td>".$out['add_channel_change_cost']."</td>";
$outpagelist.="<td>".$out['add_accounts']."</td>";
$outpagelist.="<td>".$out['add_back_money']."</td>";
$outpagelist.="<td>".$out['add_noback_money']."</td>";
$outpagelist.="<td>".$out['month_channel_money']."</td>";
$outpagelist.="<td>".$out['month_channel_cost']."</td>";
$outpagelist.="<td>".$out['month_channel_change_cost']."</td>";
$outpagelist.="<td>".$out['month_accounts']."</td>";
$outpagelist.="<td>".$out['month_back_money']."</td>";
$outpagelist.="<td>".$out['month_noback_money']."</td>";
$outpagelist.="</tr>";
}
//记录的总条数
// $sql_total_number="select * from channel_accounts";
$query=mysql_query($sql);
$num=mysql_num_rows($query);
//----------显示Page导航----------
$tablefoot="";
$tablefoot.= "<table>";
$tablefoot.='<tr><td>记录共有:'.$num. '条</td><td>';
$tablefoot.= ($page == 1) ? '首页' : '<a href="'.$url.'?page=1">'.'首页'.'</a>';
$tablefoot.= ' ';
$tablefoot.= ($page == 1) ? '上一页' : '<a href="'.$url.'?page=' . ($page - 1) . '&channel_name='.$channel_name.'&collection_contract_id='.$collection_contract_id.'" >'.'上一页' .'</a>';
$tablefoot.= ' ';
$tablefoot.= ($page == $pageNums) ? '下一页' : '<a href="'.$url.'?page='. ($page + 1) . '&channel_name='.$channel_name.'&collection_contract_id='.$collection_contract_id.'" >'.'下一页' .'</a>';
$tablefoot.= ' ';
$tablefoot.= ($page == $pageNums) ? '尾页' : '<a href="'.$url.'?page='.$pageNums.'&channel_name='.$channel_name.'&collection_contract_id='.$collection_contract_id.'">'.'尾页' .'</a>';
$tablefoot.= '</td></tr></table>';
if($pageNums==0)
{
$page=0;
$tablefoot="";
}
//------结束-------
$smarty->assign("tablefoot",$tablefoot);
$smarty->assign("time2_value",check_text("time2"));
$smarty->assign("key_value",check_text("key"));
$smarty->assign("outpagelist",$outpagelist);
$smarty->display("index2.html");
?>
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
上一篇：php文件缓存(改进型)下一篇：PHP导出Excel的类库,支持附带图片