title: Creating Excel file from MySQL
body: <p>I am trying to generate an XLS file from a table in a MySQL DB, but the Excel file is not properly formatted &amp; given error when the Excel file generated "The file which you are trying to open is in different format than one specified". When the file is opened the data is not properly formatted.</p>
  
  <p>Any ideas what I am missing?</p>
  
  <pre class="lang-php prettyprint-override"><code>&lt;?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-&gt;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 =&gt; $v )
        {
          xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
          $col++;
        }
  
        $col = 0;
        $row++;
        $first = false;
      }
  
      // go through the data
      foreach( $qrow as $k =&gt; $v )
      {
        // write it out
        xlsWriteLabel( $row, $col, $v );
        $col++;
      }
      // reset col and goto next row
      $col = 0;
      $row++;
  }
  
  xlsEOF();
  exit();
  </code></pre>