导出数据库数据为excel格式


Exporting database data into excel format

我有以下代码从数据库导出数据到excel。

<?PHP
include '../connection.php';
  $colnames = array(
    "fname" => "First Name",
    "lname" => "Last Name",
    "email" => "Email Address",
    "phone" => "Phone No.",
    "timeFrom" => "Time From",
    "timeto" => "Time to",
    "comments" => "User Comments"
  );

   function cleanData(&$str)
     {
       $str = preg_replace("/'t/", "''t", $str);
       $str = preg_replace("/'r?'n/", "''n", $str);
       if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
     }

   function map_colnames($input){
    global $colnames;
    return isset($colnames[$input])? $colnames[$input]:$input;
   }
  // filename for download
  $filename = "website_data_" . date('Ymd') . ".xls";
  header("Content-Disposition: attachment; filename='"$filename'"");
  header("Content-Type: application/vnd.ms-excel");
  $flag = false;
  $result = mysql_query("SELECT * FROM futurehomeowner1 ORDER BY id") or die(mysql_error());
  while(false !== ($row = mysql_fetch_assoc($result))) {
    if(!$flag) {
      // display field/column names as first row
      $firstline=array_map('map_colnames',array_keys($row));
      fputs($firstline,',','"');
      echo implode("'t", array_keys($row)) . "'r'n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("'t", array_values($row)) . "'r'n";
  }
  exit;
?>

它工作得很好,从数据库到excel获取数据。我想使用标题名称作为我自己的,而不是数据库列名。问题是,当excel文件下载没有标题名称,并显示此消息"Warning: fputs() expects parameter 1 to be resource, array given"。下面的消息数据显示正确,但在一列。

您以错误的方式使用fputs,您应该传递对文件的引用,然后传递字符串。首先,你应该打开一个文件处理程序到你的文件名,以便稍后传递它。

<?PHP
include '../connection.php';
  $colnames = array(
    "fname" => "First Name",
    "lname" => "Last Name",
    "email" => "Email Address",
    "phone" => "Phone No.",
    "timeFrom" => "Time From",
    "timeto" => "Time to",
    "comments" => "User Comments"
  );

   function cleanData(&$str)
     {
       $str = preg_replace("/'t/", "''t", $str);
       $str = preg_replace("/'r?'n/", "''n", $str);
       if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
     }

   function map_colnames($input){
    global $colnames;
    return isset($colnames[$input])? $colnames[$input]:$input;
   }
  // filename for download
  $filename = "website_data_" . date('Ymd') . ".xls";
  $handler = fopen($filename, "w+");
  header("Content-Disposition: attachment; filename='"$filename'"");
  header("Content-Type: application/vnd.ms-excel");
  $flag = false;
  $result = mysql_query("SELECT * FROM futurehomeowner1 ORDER BY id") or die(mysql_error());
  while(false !== ($row = mysql_fetch_assoc($result))) {
    if(!$flag) {
      // display field/column names as first row
      $firstline=array_map('map_colnames',array_keys($row));
      fputs($handler, implode("'t",$firstline));
      $flag = true;
    }
    array_walk($row, 'cleanData');
    fputs($handler, implode("'t", array_values($row)) . "'r'n");
  }
  exit;
?>

查看PHP手册