PHP生成excel并向其输入数据
- PHP中文网Original
- 2016-05-25 17:13:061218browse
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");
?>
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：php文件缓存(改进型)Next article：PHP导出Excel的类库,支持附带图片