php - 如何使用 PHP 将 MySQL 表导出到 Excel？

Question

我目前正在为我的学校开发一个应用程序来记录每个类(class)的类(class)清洁度结果，因此我需要使用 PHP 将 MySQL 表格中整理的结果转换为 Microsoft Excel，最好也能够通过 Android 操作系统手机打开。

我使用了以下 PHP 代码:

<?PHP

$mysqli_user = "(user)";
$mysqli_password = "(password)";
$mysqli_host = "(host)";
$mysqli_database = "(database)";


  $filename = "grading_results_" . time() . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

$link = mysqli_connect($mysqli_host,$mysqli_user,$mysqli_password,$mysqli_database);
$query = 'SELECT * FROM (table_name)';

$result = mysqli_query($link, $query);

while ($row = mysqli_fetch_row($result)){
 print implode("\t", $row) . "\n";
}
mysqli_close($link);
?>

这是我的表格在 phpMyAdmin 中的样子:

https://www.dropbox.com/s/7pr3gh06zta5d8u/Snip20150618_2.png?dl=0

这是我使用此代码转换后 Excel 文件的样子。

https://www.dropbox.com/s/571m9lfj64tklpc/Snip20150618_3.png?dl=0

为什么没有列和行？我需要 Excel 文件的格式和样式与 phpMyAdmin 中的表格完全相同。任何人都可以帮助编辑我的代码而不是为我提供全新的代码吗？

预先感谢您的回答!

Answer 1

手动

1. 运行本地主机并登录 phpMyAdmin
2. 点击您的数据库，然后点击您想要获取的表Excel。
3. 

然后

然后按“GO”按钮

使用代码

define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS","");
define ("DB_NAME","DATABASE_NAME");

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

然后

$setCounter = 0;

$setExcelName = "download_excal_file";

$setSql = "YOUR SQL QUERY GOES HERE";

$setRec = mysql_query($setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
    $setMainHeader .= mysql_field_name($setRec, $i)."\t";
}

while($rec = mysql_fetch_row($setRec))  {
  $rowLine = '';
  foreach($rec as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
//It escape all the special charactor, quotes from the data.
      $value = strip_tags(str_replace('"', '""', $value));
      $value = '"' . $value . '"' . "\t";
    }
    $rowLine .= $value;
  }
  $setData .= trim($rowLine)."\n";
}
  $setData = str_replace("\r", "", $setData);

if ($setData == "") {
  $setData = "no matching records found";
}

$setCounter = mysql_num_fields($setRec);



//This Header is used to make data download instead of display the data
 header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$setExcelName."_Report.xls");
header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."\n".$setData."\n";

More About Code