用PHP将MySQL数据导出到Excel

Question

我正在尝试将MySQL数据保存到Excel文件，但是Excel单元格出现问题。我所有的文本都放在一个单元格中，我希望每个行值都在单独的Excel单元格中。这是我的代码：

$queryexport = ("

SELECT username,password,fullname FROM ecustomer_users

WHERE fk_customer='".$fk_customer."'

");

$row = mysql_fetch_assoc($queryexport);

$result = mysql_query($queryexport);

$header = '';

for ($i = 0; $i < $count; $i++){

   $header .= mysql_field_name($result, $i)."\t";

   }

while($row = mysql_fetch_row($result)){

   $line = '';

   foreach($row as $value){

          if(!isset($value) || $value == ""){

                 $value = "\t";

          }else{

                 $value = str_replace('"', '""', $value);

                 $value = '"' . $value . '"' . "\t";

                 }

          $line .= $value;

          }

   $data .= trim($line)."\n";

   $data = str_replace("\r", "", $data);

if ($data == "") {

   $data = "\nno matching records found\n";

   }

}

header("Content-type: application/vnd.ms-excel; name='excel'");

header("Content-Disposition: attachment; filename=exportfile.xls");

header("Pragma: no-cache");

header("Expires: 0");

// output data

echo $header."\n".$data;

mysql_close($conn);`

Answer 1

尝试以下方法：PHP部分：<?php/*******EDIT LINES 3-8*******/$DB_Server = "localhost"; //MySQL Server&nbsp; &nbsp;&nbsp;$DB_Username = "username"; //MySQL Username&nbsp; &nbsp; &nbsp;$DB_Password = "password";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//MySQL Password&nbsp; &nbsp; &nbsp;$DB_DBName = "databasename";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//MySQL Database Name&nbsp;&nbsp;$DB_TBLName = "tablename"; //MySQL Table Name&nbsp; &nbsp;$filename = "excelfilename";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//File Name/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/&nbsp; &nbsp;&nbsp;//create MySQL connection&nbsp; &nbsp;$sql = "Select * from $DB_TBLName";$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());//select database&nbsp; &nbsp;$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());&nbsp; &nbsp;//execute query&nbsp;$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());&nbsp; &nbsp;&nbsp;$file_ending = "xls";//header info for browserheader("Content-Type: application/xls");&nbsp; &nbsp;&nbsp;header("Content-Disposition: attachment; filename=$filename.xls");&nbsp;&nbsp;header("Pragma: no-cache");&nbsp;header("Expires: 0");/*******Start of Formatting for Excel*******/&nbsp; &nbsp;//define separator (defines columns in excel & tabs in word)$sep = "\t"; //tabbed character//start of printing column names as names of MySQL fieldsfor ($i = 0; $i < mysql_num_fields($result); $i++) {echo mysql_field_name($result,$i) . "\t";}print("\n");&nbsp; &nbsp;&nbsp;//end of printing column names&nbsp;&nbsp;//start while loop to get data&nbsp; &nbsp; while($row = mysql_fetch_row($result))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; $schema_insert = "";&nbsp; &nbsp; &nbsp; &nbsp; for($j=0; $j<mysql_num_fields($result);$j++)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(!isset($row[$j]))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $schema_insert .= "NULL".$sep;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; elseif ($row[$j] != "")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $schema_insert .= "$row[$j]".$sep;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $schema_insert .= "".$sep;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; $schema_insert = str_replace($sep."$", "", $schema_insert);&nbsp; &nbsp; &nbsp; &nbsp; $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);&nbsp; &nbsp; &nbsp; &nbsp; $schema_insert .= "\t";&nbsp; &nbsp; &nbsp; &nbsp; print(trim($schema_insert));&nbsp; &nbsp; &nbsp; &nbsp; print "\n";&nbsp; &nbsp; }&nbsp; &nbsp;?>我认为这可以帮助您解决问题。

Answer 2

如果您只是想将查询数据转储到excel中，则必须经常这样做，并且使用html表是一种非常简单的方法。我使用mysqli进行数据库查询，并使用以下代码导出到excel：header("Content-Type: application/xls");&nbsp; &nbsp;&nbsp;header("Content-Disposition: attachment; filename=filename.xls");&nbsp;&nbsp;header("Pragma: no-cache");&nbsp;header("Expires: 0");echo '<table border="1">';//make the column headers what you want in whatever order you wantecho '<tr><th>Field Name 1</th><th>Field Name 2</th><th>Field Name 3</th></tr>';//loop the query data to the table in same order as the headerswhile ($row = mysqli_fetch_assoc($result)){&nbsp; &nbsp; echo "<tr><td>".$row['field1']."</td><td>".$row['field2']."</td><td>".$row['field3']."</td></tr>";}echo '</table>';