Creating Excel file from MySQL

Question

I am trying to generate an XLS file from a table in a MySQL DB, but the Excel file is not properly formatted & 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.

Any ideas what I am missing?

<?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();

Answer 1

I'm not sure about .xls but for outputting a MySQL result as a CSV table the fputcsv function does it without much fuss:

// 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;
}