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)."\t";
   }

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

if ($data == "") {
   $data = "\nno matching records found\n";
   }
}
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."\n".$data;

mysql_close($conn);`

Solution 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 = "\t"; //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) . "\t";
}
print("\n");    
//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("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

I think this may help you to resolve your problem.

Solution 2

Try this code. It's definitly working.

<?php
// Connection 

$conn=mysql_connect('localhost','root','');
$db=mysql_select_db('excel',$conn);

$filename = "Webinfopen.xls"; // File Name
// Download file
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$user_query = mysql_query('select name,work from info');
// Write data to file
$flag = false;
while ($row = mysql_fetch_assoc($user_query)) {
    if (!$flag) {
        // display field/column names as first row
        echo implode("\t", array_keys($row)) . "\r\n";
        $flag = true;
    }
    echo implode("\t", array_values($row)) . "\r\n";
}
?>

Solution 3

If you just want your query data dumped into excel I have to do this frequently and using an html table is a very simple method. I use mysqli for db queries and the following code for exports to excel:

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");


echo '<table border="1">';
//make the column headers what you want in whatever order you want
echo '<tr><th>Field Name 1</th><th>Field Name 2</th><th>Field Name 3</th></tr>';
//loop the query data to the table in same order as the headers
while ($row = mysqli_fetch_assoc($result)){
    echo "<tr><td>".$row['field1']."</td><td>".$row['field2']."</td><td>".$row['field3']."</td></tr>";
}
echo '</table>';

Solution 4

This is new version of php code

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "your_dbname";
//mysql and db connection

$con = new mysqli($servername, $username, $password, $dbname);

if ($con->connect_error) {  //error check
    die("Connection failed: " . $con->connect_error);
}
else
{

}


$DB_TBLName = "your_table_name"; 
$filename = "excelfilename";  //your_file_name
$file_ending = "xls";   //file_extention

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.'.'.$file_ending");  
header("Pragma: no-cache"); 
header("Expires: 0");

$sep = "\t";

$sql="SELECT * FROM $DB_TBLName"; 
$resultt = $con->query($sql);
while ($property = mysqli_fetch_field($resultt)) { //fetch table field name
    echo $property->name."\t";
}

print("\n");    

while($row = mysqli_fetch_row($resultt))  //fetch_table_data
{
    $schema_insert = "";
    for($j=0; $j< mysqli_num_fields($resultt);$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("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
}

Solution 5

PHPExcel is your friend. Very easy to use and works like a charm.

https://github.com/PHPOffice/PHPExcel

View more solutions

Comments

[kuldeep.kamboj]

He is clearly asking excel not a csv solution. Also for a CSV file you must use application/csv or text/csv content-type not application/vnd.ms-excel (At least that is the standard for CSV files.)

[kuldeep.kamboj]

One more thing even CSV solution is ok, There are no need to use PHP for generate a csv from mysql table. It can be generated by sql statement SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

[Juan Velez]

@JohnPeter I tried using the code but when I run the page all it does is output the contents of the table into the web browser. I am setting the $filename variable to "testfile", could this be where I am going wrong? Also, where does this code output the excel file to? Do I need to specify the directory in the $filename variable?

[Shashi Roy]

@JohnPeter : Thanks for the code above, This works good, but what if I want only selected column(with changed custom-column name ) to be exported in the excel sheet? I am stuck with that. I would not like my user to know the name of the column name and the encrypted password stored in the user table.

[Shashi Roy]

I got the solution --- $sep = "\t"; //tabbed character echo "Name" . "\t"; echo "Email" . "\t"; print("\n"); //end of printing column names //start while loop to get data while($row = mysql_fetch_row($result)) { echo $row[1] . "\t";; echo $row[2] . "\t"; print "\n"; } ?>

[Jagan Akash]

I also tried this code, it is working when i check in console but not downloading. Pls help me. I dont know why it is not downloading.

[bansal]

wow, that's working....but when opening it after the download, the excel shows a warning that the extension and content do not match so the data may be corrupted.

[parrigin777]

When I use this code it works perfectly, however it also writes the whole web page to the file. Any idea why?

[Inderjeet]

How do i protect above excel download ? If i use session then browser print all the data instead download as excel

[acidrums4]

Deprecated, as stated on their own Github page.

[Muhammad Hassan]

@JohnPeter This Code is working fine but converting values, formulas, dates of MQSQL Table etc in Excel results like solving the formula and changing the formats But I need to save simple like text so is this possible in this code?

[php_javascript_html_dev]

maybe i am doing something wrong, but its printing the results to my firefox browser. no file download prompt comes up. everyone else seems to be having success with it so done know.

[php_javascript_html_dev]

Tried it on google chrome as well, same issue. it's printing the results to browser

[Dileep kurahe]

Use PHPExcel library for better solution of excel sheet export import

[Roel Koops]

@acidrums4: You can use PhpSpreadsheet instead. It's the successor of PHPExcel: github.com/PHPOffice/PhpSpreadsheet

[Peter Artoung]

You can add at the end of your while : die(); It will stop the loading of your page, content should no longer be displayed

[Jaykumar Gondaliya]

Is its possible to do same thing for XLSX files exporting..... what is Content-Type for xlsx?

[John Peter]

@JaykumarGondaliya : For xlsx try with the following : $file = "myfile.xlsx"; header('Content-disposition: attachment; filename='.$file); header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.‌​sheet'); header('Content-Length: ' . filesize($file)); header('Content-Transfer-Encoding: binary'); header('Cache-Control: must-revalidate'); header('Pragma: public'); ob_clean(); flush(); readfile($file);

[Vasilii Suricov]

doesn't works cause isn't xls it's csv en.wikipedia.org/wiki/Microsoft_Excel#File_formats

[Vasilii Suricov]

definitly not =\

[A.A Noman]

@krupeshAnadkat, You are most welcome and my pleasure to help something

[Samir Lakhani]

I have Asked Question, Can You Give me reputation for asking question and flag it as helpful?

[Shurvir Mori]

add exit(); after echo '</table>'; if you got your page's unnecessary content too in export..