首頁 >php教程 >PHP源码 >PHP生成excel并向其输入数据

PHP生成excel并向其输入数据

PHP中文网
PHP中文网原創
2016-05-25 17:13:061269瀏覽

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[&#39;collection_contract_id&#39;]."\t";
   	  echo $out[&#39;channel_name&#39;]."\t";
   	  echo $out[&#39;channel_money&#39;]."\t";
   	  $sum_channel_money=$sum_channel_money+$out[&#39;channel_money&#39;];
   	  echo $out[&#39;add_channel_cost&#39;]."\t";
   	  $sum_add_channel_cost=$sum_add_channel_cost+$out[&#39;add_channel_cost&#39;];
   	  echo $out[&#39;add_channel_change_cost&#39;]."\t";
   	  $sum_channel_change_cost=$sum_channel_change_cost+$out[&#39;add_channel_change_cost&#39;];
   	  echo $out[&#39;add_accounts&#39;]."\t";
   	  $sum_add_accounts=$sum_add_accounts+$out[&#39;add_accounts&#39;];
   	  echo $out[&#39;add_back_money&#39;]."\t";
   	  $sum_add_back_money=$sum_add_back_money+$out[&#39;add_back_money&#39;];
   	  echo $out[&#39;add_noback_money&#39;]."\t";
   	  $sum_add_noback_money=$sum_add_noback_money+$out[&#39;add_noback_money&#39;];
   	  echo $out[&#39;month_channel_money&#39;]."\t";
   	  $sum_month_channel_money=$sum_month_channel_money+$out[&#39;month_channel_money&#39;];
   	  echo $out[&#39;month_channel_cost&#39;]."\t";
   	  $sum_month_channel_cost=$sum_month_channel_cost+$out[&#39;month_channel_cost&#39;];
   	  echo $out[&#39;month_channel_change_cost&#39;]."\t";
   	  $sum_month_channel_change_cost=$sum_month_channel_change_cost+$out[&#39;month_channel_change_cost&#39;];
   	  echo $out[&#39;month_accounts&#39;]."\t";
   	  $sum_month_accounts=$sum_month_accounts+$out[&#39;month_accounts&#39;];
   	  echo $out[&#39;month_back_money&#39;]."\t";
   	  $sum_month_back_money=$sum_month_back_money+$out[&#39;month_back_money&#39;];
   	  echo $out[&#39;month_noback_money&#39;]."\t";
   	  $sum_month_noback_money=$sum_month_noback_money+$out[&#39;month_noback_money&#39;];
   	  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(&#39;themes/blue/img/bg_navigation_out.png&#39;) 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=&#39;text/javascript&#39; src=&#39;scripts/excanvas.js&#39;></script>
	<script type=&#39;text/javascript&#39; src=&#39;scripts/jquery.min.js&#39;></script>
    <script type=&#39;text/javascript&#39; src=&#39;scripts/jquery-ui.min.js&#39;></script>
	<script type=&#39;text/javascript&#39; src=&#39;scripts/jquery.wysiwyg.js&#39;></script>
    <script type=&#39;text/javascript&#39; src=&#39;scripts/visualize.jQuery.js&#39;></script>
    <script type="text/javascript" src=&#39;scripts/functions.js&#39;></script>
    
    <!--[if IE 6]>
    <script type=&#39;text/javascript&#39; src=&#39;scripts/png_fix.js&#39;></script>
    <script type=&#39;text/javascript&#39;>
      DD_belatedPNG.fix(&#39;img, .notifycount, .selected&#39;);
    </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 &#39;smarty_inc.php&#39;;
   $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(&#39;收款合同号输入不合法!&#39;);</script>";
   	  	  $smarty->display("index2.html");
   	  	  exit();
   	  }
   	  $sql.=" collection_contract_id=&#39;".$collection_contract_id."&#39; and ";
   }
   if(isset($_GET["key"])&&$_GET["key"]!="")
   {
   	  $channel_name=$_GET["key"];
      if($channel_name=="")
   	  {
   	  	  echo "<script>alert(&#39;渠道名称输入不合法!&#39;);</script>";
   	  	  $smarty->display("index2.html");
   	  	  exit();
   	  }
   	  $sql.=" channel_name=&#39;".$channel_name."&#39; and ";
   }
   $sql.=" 1=1 ";
   
   
   //分页
   if (isset($_GET[&#39;page&#39;]) && $_GET[&#39;page&#39;] > 0) 
	{
    	$page = $_GET[&#39;page&#39;];
	} 
	else 
	{
    	$page = 1;
	}
	
	$eachPages = 10;
	$url = $_SERVER[&#39;PHP_SELF&#39;];
	$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[&#39;collection_contract_id&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;channel_name&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;channel_money&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;add_channel_cost&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;add_channel_change_cost&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;add_accounts&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;add_back_money&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;add_noback_money&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;month_channel_money&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;month_channel_cost&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;month_channel_change_cost&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;month_accounts&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;month_back_money&#39;]."</td>";
   	   $outpagelist.="<td>".$out[&#39;month_noback_money&#39;]."</td>";
   	   $outpagelist.="</tr>";
   }
    
    //记录的总条数
  //  $sql_total_number="select * from channel_accounts";
    $query=mysql_query($sql);
    $num=mysql_num_rows($query);
    
    //----------显示Page导航----------
	$tablefoot="";
    $tablefoot.= "<table>";
    $tablefoot.=&#39;<tr><td>记录共有:&#39;.$num. &#39;条</td><td>&#39;;
    $tablefoot.= ($page == 1) ? &#39;首页&#39; : &#39;<a href="&#39;.$url.&#39;?page=1">&#39;.&#39;首页&#39;.&#39;</a>&#39;;
    $tablefoot.= &#39;  &#39;;
    $tablefoot.= ($page == 1) ? &#39;上一页&#39; : &#39;<a href="&#39;.$url.&#39;?page=&#39; . ($page - 1) . &#39;&channel_name=&#39;.$channel_name.&#39;&collection_contract_id=&#39;.$collection_contract_id.&#39;" >&#39;.&#39;上一页&#39; .&#39;</a>&#39;;
    $tablefoot.= &#39;  &#39;;
    $tablefoot.= ($page == $pageNums) ? &#39;下一页&#39; : &#39;<a href="&#39;.$url.&#39;?page=&#39;. ($page + 1) . &#39;&channel_name=&#39;.$channel_name.&#39;&collection_contract_id=&#39;.$collection_contract_id.&#39;" >&#39;.&#39;下一页&#39; .&#39;</a>&#39;;
    $tablefoot.= &#39;  &#39;;	
    $tablefoot.= ($page == $pageNums) ? &#39;尾页&#39; : &#39;<a href="&#39;.$url.&#39;?page=&#39;.$pageNums.&#39;&channel_name=&#39;.$channel_name.&#39;&collection_contract_id=&#39;.$collection_contract_id.&#39;">&#39;.&#39;尾页&#39; .&#39;</a>&#39;;
    $tablefoot.= &#39;</td></tr></table>&#39;;
    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