从MySQL创建Excel文件

Question

我正在尝试从MySQL数据库中的表格生成XLS文件，但是Excel文件格式不正确，并在生成Excel文件时出现错误：“您要打开的文件与指定的格式不同”。当文件被打开时，数据未能正确格式化。是否有任何想法我可能遗漏了什么？

<?php
$host = 'XXXXXXX';
$dbname = 'XXXXXXXX';
$username = 'XXXXXXXX';
$password = 'XXXXXXXX';

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
    return;
}

function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
    return;
}

function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
}

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    echo "Connected to $dbname at $host successfully.";
    $conn = null;
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}

$q = "SELECT * FROM tablename";
$qr = mysql_query( $q ) or die( mysql_error() );

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");

header("Content-Disposition: attachment;filename=export_".$dbtable.".xls ");

header("Content-Transfer-Encoding: binary ");

xlsBOF();

$col = 0;
$row = 0;

$first = true;

while( $qrow = mysql_fetch_assoc( $qr ) )
{
    if( $first )
    {
      foreach( $qrow as $k => $v )
      {
        xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
        $col++;
      }

      $col = 0;
      $row++;
      $first = false;
    }

    // go through the data
    foreach( $qrow as $k => $v )
    {
      // write it out
      xlsWriteLabel( $row, $col, $v );
      $col++;
    }
    // reset col and goto next row
    $col = 0;
    $row++;
}

xlsEOF();
exit();

- Dipak Saraf

---

请解释“格式不正确”。 - Mark

为什么不使用现有的众多 PHP-Excel 库之一呢？ - user557846

@DoulatKhan请注意您的编辑 - Stack Snippets是可以在浏览器中运行的代码示例，直接在Stack Overflow页面上进行JavaScript、HTML和CSS编辑，并且具有所有必要的运行部件。PHP不是一种浏览器语言。而且您没有必要进行的标题编辑实际上添加了一个拼写错误（应为"generate"而非"genrate"）。 - Paul Roub

Answer 1

我不确定.xls文件，但是如果要将MySQL查询结果以CSV格式输出，则可以使用fputcsv函数轻松完成：

// Clear any previous output
ob_end_clean();
// I assume you already have your $result
$num_fields = mysql_num_fields($result);

// Fetch MySQL result headers
$headers = array();
$headers[] = "[Row]";
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = strtoupper(mysql_field_name($result , $i));
}

// Filename with current date
$current_date = date("y/m/d");
$filename = "MyFileName" . $current_date . ".csv";

// Open php output stream and write headers
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename='.$filename);
    header('Pragma: no-cache');
    header('Expires: 0');
    echo "Title of Your CSV File\n\n";
    // Write mysql headers to csv
    fputcsv($fp, $headers);
    $row_tally = 0;
    // Write mysql rows to csv
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $row_tally = $row_tally + 1;
    echo $row_tally.",";
        fputcsv($fp, array_values($row));
    }
    die;
}

- Marcel Wasko

Answer 2

使用http://phpexcel.codeplex.com/是最佳的生成Excel文件的解决方案。您甚至可以在文件中创建多个工作表并格式化单元格（颜色，字体，边框等）。

- janhsh

---

打开文件时出现的第一个错误是：“您尝试打开的文件与文件扩展名指定的格式不同。” - Dipak Saraf

打开文件时出现的第一个错误是“您尝试打开的文件与文件扩展名指定的格式不同”。第二个错误是表格字段在每行和每列中没有单独下载，而是在第一列中。并且在每个字段之间出现以下字符。 - Dipak Saraf

基本上我想要这个解决方案，因为我将把这个功能添加到WordPress页面模板中，并且相同的功能将查询数据库并使XLS文件可供下载或邮件。所以我希望这尽可能地简单，不想使用像PHPExcel这样的第三方函数。有什么建议吗？ - Dipak Saraf

Answer 3

<?php 
 //download.php page code
 //THIS PROGRAM WILL FETCH THE RESULT OF SQL QUERY AND WILL DOWNLOAD IT. (IF YOU HAVE ANY QUERY CONTACT:[e-mail])
//include the database file connection
include_once('database.php');
//will work if the link is set in the indx.php page
if(isset($_GET['name']))
{
    $name=$_GET['name']; //to rename the file
    header('Content-Disposition: attachment; filename='.$name.'.xls'); 
    header('Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0');
    header('Pragma: no-cache');
    header('Content-Type: application/x-msexcel; charset=windows-1251; format=attachment;');
    $msg="";
    $var="";
    //write your query      
    $sql="select * from tablename";
    $res = mysql_query($sql);
    $numcolumn = mysql_num_fields($res); //will fetch number of field in table
    $msg="<table><tr><td>Sl No</td>";
    for ( $i = 0; $i < $numcolumn; $i++ ) {
        $msg.="<td>";
        $msg.= mysql_field_name($res, $i);  //will store column name of the table to msg variable
        $msg.="</td>";

    }
    $msg.="</tr>";
    $i=0;
    $count=1; //used to print sl.no
    while($row=mysql_fetch_array($res))  //fetch all the row as array
    {

        $msg.="<tr><td>".$count."</td>";
        for($i=0;$i< $numcolumn;$i++)
        {
            $var=$row[$i]; //will store all the values of row 
            $msg.="<td>".$var."</td>";
        }
        $count=$count+1;
        $msg.="</tr>";
    }

    $msg.="</table>";
    echo $msg;  //will print the content in the exel page
}
?>

<?php
//index.php page
$name="any file name";
echo "<a href='download.php?name=".$name."'>Click to download</a>"; //link to download file
?>

- rahul patel

---

好的，我将尽力以通俗易懂的方式为您翻译。需要翻译的内容是：“Please explain your answer”。 - Machavity

Answer 4

这是一个简单的Excel文件生成函数，非常快速且生成的文件格式为.xls。

$filename = "sample_php_excel.xls";
$data = array(
array("User Name" => "Abid Ali", "Q1" => "$32055", "Q2" => "$31067", "Q3" => 32045, "Q4" => 39043),
array("User Name" => "Sajid Ali", "Q1" => "$25080", "Q2" => "$20677", "Q3" => 32025, "Q4" => 34010),
array("User Name" => "Wajid Ali", "Q1" => "$93067", "Q2" => "$98075", "Q3" => 95404, "Q4" => 102055),
);
to_xls($data, $filename);

function to_xls($data, $filename){
$fp = fopen($filename, "w+");
$str = pack(str_repeat("s", 6), 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); // s | v
fwrite($fp, $str);
if (is_array($data) && !empty($data)){
    $row = 0;
    foreach (array_values($data) as $_data){
        if (is_array($_data) && !empty($_data)){
            if ($row == 0){
                foreach (array_keys($_data) as $col => $val){
                    _xlsWriteCell($row, $col, $val, $fp);
                }
                $row++;
            }
            foreach (array_values($_data) as $col => $val){
                _xlsWriteCell($row, $col, $val, $fp);
            }
            $row++;
        }
    }
}
$str = pack(str_repeat("s", 2), 0x0A, 0x00);
fwrite($fp, $str);
fclose($fp);
}

function _xlsWriteCell($row, $col, $val, $fp){
if (is_float($val) || is_int($val)){
    $str  = pack(str_repeat("s", 5), 0x203, 14, $row, $col, 0x0);
    $str .= pack("d", $val);
} else {
    $l    = strlen($val);
    $str  = pack(str_repeat("s", 6), 0x204, 8 + $l, $row, $col, 0x0, $l);
    $str .= $val;
}
fwrite($fp, $str);
}

- Abid Ali

Answer 5

<?php 
  ob_end_clean();
  $num_fields = mysql_num_fields($result);
  $headers = array();
  $headers[] = "[Row]";
  for ($i = 0; $i < $num_fields; $i++) 
     $headers[] = strtoupper(mysql_field_name($result , $i));

  $current_date = date("y/m/d");
  $filename = "MyFileName" . $current_date . ".csv";

  $fp = fopen('php://output', 'w');
  if ($fp && $result) {
      header('Content-Type: text/csv');
      header('Content-Disposition: attachment; filename='.$filename);
      header('Pragma: no-cache');
      header('Expires: 0');
      echo "Title of Your CSV File\n\n";
      fputcsv($fp, $headers);
      $row_tally = 0;
      // Write mysql rows to csv
      while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        $row_tally = $row_tally + 1;
        echo $row_tally.",";
        fputcsv($fp, array_values($row));
      }
     die;
   }
?>

- Manikandan

---

请解释您的答案。 - slfan