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

@DoulatKhan Please take care with your edits - Stack Snippets are for code samples that can run in the browser, right on the Stack Overflow page - JavaScript, HTML and CSS, with all the necessary pieces to run. PHP is not an in-browser language. And your unnecessary title edit actually added a spelling error ("generate", not "genrate"). — Paul Roub
– Paul Roub, Commented Nov 9, 2016 at 13:55

Marcel Wasko · Accepted Answer · 2013-06-24 12:02:24Z

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

h2ooooooo · Accepted Answer · 2013-06-24 11:48:41Z

3

use http://phpexcel.codeplex.com/

C'est la meilleur solution pour générer un fichier excel Vous pouvez même créer plusieurs feuilles dans le fichier et formater les cellules (couleur, police, bordure, ...)

Google translate:

This is the best solution to generate an excel file You can even create multiple sheets in the file and format the cells (color, font, border, ...)

edited Jun 24, 2013 at 11:48

h2ooooooo

39.6k8 gold badges70 silver badges107 bronze badges

answered Jun 16, 2013 at 13:55

janhsh

962 bronze badges

3 Comments

Dipak Saraf Over a year ago

The first error which appears when the files is opened is "The file you are trying to open is the different format than specified by the file extension"

Dipak Saraf Over a year ago

The first error which appears when the files is opened is "The file you are trying to open is the different format than specified by the file extension". The second error is field of the table is not are not getting downloaded separately in each row and column. but in first column. and the following character is appearing between each fields. ``

Dipak Saraf Over a year ago

Basically I am wanting this solution, since this functionality I will be adding to a WordPress Page Template, and the same will be querying the DB and making the XLS file available for download or mail. So I would want this to be easier as possible, so not wanting to use third party function like PHPExcel. Any Suggestions?

Shahzad Barkati · Accepted Answer · 2017-02-21 03:48:12Z

<?php 
 //download.php page code
 //THIS PROGRAM WILL FETCH THE RESULT OF SQL QUERY AND WILL DOWNLOAD IT. (IF YOU HAVE ANY QUERY CONTACT:[email protected])
//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
?>

Abid Ali · Accepted Answer · 2018-05-04 05:32:46Z

Here is simple Excel file generation function, very fast and exactly .xls file.

$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);
}

Ömürcan Cengiz · Accepted Answer · 2019-10-29 15:29:01Z

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

Collectives™ on Stack Overflow

Creating Excel file from MySQL

5 Answers 5

Comments

3 Comments

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

1 Comment

Comments

1 Comment

Linked

Related