Export MySQL data to Excel in PHP

Question

I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code:

$queryexport = ("
SELECT username,password,fullname FROM ecustomer_users
WHERE fk_customer='".$fk_customer."'
");

$row = mysql_fetch_assoc($queryexport);

$result = mysql_query($queryexport);
$header = '';

for ($i = 0; $i < $count; $i++){
   $header .= mysql_field_name($result, $i)."";
   }

while($row = mysql_fetch_row($result)){
   $line = '';
   foreach($row as $value){
          if(!isset($value) || $value == ""){
                 $value = "";
          }else{
                 $value = str_replace('"', '""', $value);
                 $value = '"' . $value . '"' . "";
                 }
          $line .= $value;
          }
   $data .= trim($line)."
";
   $data = str_replace("
", "", $data);

if ($data == "") {
   $data = "
no matching records found
";
   }
}
header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

// output data
echo $header."
".$data;

mysql_close($conn);`

question from:https://stackoverflow.com/questions/15699301/export-mysql-data-to-excel-in-php

Answer 1

Just Try With The Following :

PHP Part :

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = ""; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "";
}
print("
");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/
|

|
|
/", " ", $schema_insert);
        $schema_insert .= "";
        print(trim($schema_insert));
        print "
";
    }   
?>

I think this may help you to resolve your problem.