将 php 表导出到 Excel

Exporting a php table to Excel

提问by Mahmoud

I am workin on a php assignment...i am new in php..and have developed a cart...i would like user to have an option of clicking a button and exporting the table data i generated to excel..

我正在处理一项 php 任务...我是 php 新手...并且已经开发了一个购物车...我希望用户可以选择单击一个按钮并将我生成的表格数据导出到 excel ..

This is my code so far:-

到目前为止，这是我的代码：-

    <?php
session_start();
if (!isset($_SESSION["customer"])) {
    header("location: customer_login.php"); 
    exit();
   }
//error script
error_reporting(E_ALL);
ini_set('display_errors','1');
//connect to the database
include "../storescripts/connect_to_mysql.php";
?>
<?php
///////////////////////////////////////////////////////////////////////////////////
//               SECTION ONE
///////////////////////////////////////////////////////////////////////////////////
if(isset($_POST['pid'])){
   $pid=$_POST['pid'];
   $wasFound=false;
   $i=0;

   //if the cart session is set or empty
if(!isset($_SESSION["cart_array"]) || count($_SESSION["cart_array"])< 1){
   //Runs if the cart is empty
   $_SESSION["cart_array"]= array(0 => array("item_id"=>$pid,"quantity"=>1));   
 }else{
   //Runs if the cart has at least one item in it
   foreach($_SESSION["cart_array"] as $each_item){
    $i++;
   while(list($key,$value)= each($each_item)){
   if($key == "item_id"&&$value==$pid){
//the ite is in the cart..hence we adjust the quantity
   array_splice($_SESSION["cart_array"],$i-1,1,array(array("item_id"=>$pid,"quantity"=>$each_item['quantity']+1)));
   $wasFound=true;
   }//close if conditio
   }//close while loop
  }//close foreach loop
  if($wasFound==false){
  array_push($_SESSION["cart_array"],array("item_id"=>$pid,"quantity"=>1));
   }
  }
  header("location: cart.php");
}
?>
<?php
///////////////////////////////////////////////////////////////////////////////////
//               SECTION TWO
///////////////////////////////////////////////////////////////////////////////////
//if usser chooses to empty their sopping cart
   if(isset($_GET['cmd'])&& $_GET['cmd']=="emptycart"){
      unset($_SESSION["cart_array"]);
      }
?>
<?php
///////////////////////////////////////////////////////////////////////////////////
//               SECTION THREE
///////////////////////////////////////////////////////////////////////////////////
//if usser chooses to empty their sopping cart
   if(isset($_POST['item_to_adjust'])&& $_POST['item_to_adjust']!=""){
      //execute some code
      $item_to_adjust=$_POST['item_to_adjust'];
      $quantity=$_POST['quantity'];
      $quantity=preg_replace('#[^0-9]#i', '',$quantity);
      if($quantity >= 1000){$quantity=999;}
      if($quantity < 1){$quantity= 1;}
      $i=0;
   foreach($_SESSION["cart_array"] as $each_item){
    $i++;
   while(list($key,$value)= each($each_item)){
   if($key == "item_id"&&$value==$item_to_adjust){
//the ite is in the cart..hence we adjust the quantity
   array_splice($_SESSION["cart_array"],$i-1,1,array(array("item_id"=>$item_to_adjust,"quantity"=>$quantity)));
   }//close if conditio
   }//close while loop
  }//close foreach loop
 }
?>
<?php
///////////////////////////////////////////////////////////////////////////////////
//               SECTION FOUR
///////////////////////////////////////////////////////////////////////////////////
if(isset($_POST['index_to_remove'])&&$_POST['index_to_remove']!=""){
      //access the array and run code to remove
   $key_to_remove= $_POST['index_to_remove'];
if(count($_SESSION["cart_array"])<=1){
   unset($_SESSION["cart_array"]);
}else{
   unset($_SESSION["cart_array"]["$key_to_remove"]);
   sort($_SESSION["cart_array"]);
   }
}
?>
<?php
///////////////////////////////////////////////////////////////////////////////////
//               SECTION FIVE
///////////////////////////////////////////////////////////////////////////////////

$cartOutput="";
$cartTotal="";
   if(!isset($_SESSION["cart_array"]) || count($_SESSION["cart_array"])< 1){
      $cartOutput="<h2 align='center'>Your Cart Is Empty</h2>";
}else{
   $i=0;
   foreach($_SESSION["cart_array"] as $each_item){
   $item_id=$each_item['item_id'];
   $sql=mysql_query("SELECT * FROM products WHERE id='$item_id' LIMIT 1");
   while($row=mysql_fetch_array($sql)){
      $product_name=$row["product_name"];
      $price=$row['price'];
      $details=$row['details'];
   }
      $pricetotal= $price*$each_item['quantity'];
      $cartTotal=$pricetotal + $cartTotal;

       //setlocale(LC_MONETARY,"en_KSHs");
      //$pricetotal= money_format("%10.2n", $pricetotal);
      //dynamic table assembly

      $cartOutput .="<tr align='center'>";
      $cartOutput .='<td><a href="../home.php?id=' . $item_id . '">' .$product_name . '</a><br/><img src="../inventory_images/' . $item_id . '.jpg" alt="' . $product_name . '" width="40" height="52" border="1"/></td>';
      $cartOutput .='<td>' . $details . '</td>';
      $cartOutput .='<td><form action="cart.php" method="post">
      <input name="quantity" type="text" value="' . $each_item['quantity'] . '" size="1" maxlength="3" />
      <input name="adjustBtn' . $item_id . '" type="image" value="change" src="../images/buttons/button_save.gif"/>
      <input name="item_to_adjust" type="hidden" value="' . $item_id . '" />
      </form></td>';
      //$cartOutput .='<td>' . $each_item['quantity'] . '</td>';
      $cartOutput .='<td>' . $price . '</td>';
      $cartOutput .='<td>' . $pricetotal . '</td>';
      $cartOutput .='<td><form action="cart.php" method="post">
      <input name="deleteBtn' . $item_id . '" type="image" value="X" src="../images/buttons/button_delete.gif"/>
      <input name="index_to_remove" type="hidden" value="' . $i . '" /
      ></form></td>';
      $cartOutput .='</tr>';
      $i++;
   }

  $cartTotal="<div align='right'>Your Total is KSHs. ".$cartTotal."</div>";
 }
?>

回答by Jeremy

If you absolutely need to export the data to a native Excel file have a look at PHPExcel. It's an excellent library for manipulating Excel files but it would be complicated for a newcomer to PHP. Otherwise, if you're happy exporting as a CSV and converting to an Excel file later look at the native fputcsv() function.

如果您绝对需要将数据导出到本机 Excel 文件，请查看PHPExcel。这是一个用于操作 Excel 文件的优秀库，但对于 PHP 新手来说会很复杂。否则，如果您愿意导出为 CSV 并稍后转换为 Excel 文件，请查看本机 fputcsv() 函数。

回答by Mark Baker

There's a working example of using the PHPexcellibrary to export data from a SQL query (database table) to an Excel (xls) file in my response to this recent question, demonstrating just how easy it actually is.

在我对这个最近问题的回答中，有一个使用PHPexcel库将数据从 SQL 查询（数据库表）导出到 Excel (xls) 文件的工作示例，演示了它实际上是多么容易。

回答by chenio

<?
header('Content-type: application/vnd.ms-excel');
    header("Content-Disposition: attachment; filename=catalogo.xls");
    header("Pragma: no-cache");
    header("Expires: 0"); 

$table = "<table>";
$table .="<tr><td>Hello world</td></tr>";
$table .="</table>";

echo $table
?>