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

Not related to your question, but you should really use an array to build the lines of your file. Then you just have to use [`implode`](http://www.php.net/manual/en/function.implode.php) to glue the elements together. This saves you the hassle of having to check if your current line buffer is empty or not when you want to append the delimiter. — Miklos Aubert, Mar 29 '13 at 07:48
@Miklos - why use implode? PHP provides the built-in function fputcsv() for writing CSV/TSV/etc files — Mark Baker, Mar 30 '13 at 10:53
@Mark thanks for the heads-up, PHP is not my main skill so I didn't know about that function. — Miklos Aubert, Mar 30 '13 at 12:57

John Peter · Accepted Answer · 2013-03-29T09:51:18.440

73

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.

edited Mar 29 '13 at 09:51

answered Mar 29 '13 at 09:06

John Peter

2,870
3
27
46

2

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 Mar 29 '13 at 09:08
9

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;` – kuldeep.kamboj Mar 29 '13 at 09:13
@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? – Juan Velez Apr 11 '14 at 17:19
@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 Mar 22 '15 at 03:26
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"; } ?> – Shashi Roy Mar 22 '15 at 03:49
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. – Jagan Akash Sep 03 '15 at 05:14
How do i protect above excel download ? If i use session then browser print all the data instead download as excel – Inderjeet Sep 01 '18 at 10:22
@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? – Muhammad Hassan Sep 20 '18 at 09:01
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 Nov 22 '18 at 13:42
Tried it on google chrome as well, same issue. it's printing the results to browser – php_javascript_html_dev Nov 22 '18 at 13:50
Is its possible to do same thing for XLSX files exporting..... what is _Content-Type_ for xlsx? – Jaykumar Gondaliya Apr 12 '19 at 08:46
@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); – John Peter Aug 27 '19 at 10:51
doesn't works cause isn't xls it's csv https://en.wikipedia.org/wiki/Microsoft_Excel#File_formats – Vasilii Suricov Sep 20 '19 at 15:21

Dileep kurahe · Answer 2 · 2014-11-18T05:44:27.327

17

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

edited Nov 18 '14 at 05:44

answered Nov 18 '14 at 05:38

Dileep kurahe

259
3
12

When I use this code it works perfectly, however it also writes the whole web page to the file. Any idea why? – parrigin777 Aug 16 '18 at 15:32
Use PHPExcel library for better solution of excel sheet export import – Dileep kurahe Dec 21 '18 at 12:15
You can add at the end of your while : die(); It will stop the loading of your page, content should no longer be displayed – Peter Artoung Feb 25 '19 at 13:37
definitly not =\ – Vasilii Suricov Sep 20 '19 at 15:22
2

I have Asked Question, Can You Give me reputation for asking question and flag it as helpful? – Samir Lakhani Jan 16 '20 at 15:50

score 12 · Answer 3 · answered Jan 27 '17 at 16:42

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

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. — bansal, Jun 11 '18 at 12:16
add `exit();` after `echo '';` if you got your page's unnecessary content too in export.. — Shurvir Mori, Nov 16 '21 at 08:28

A.A Noman · Answer 4 · 2019-12-30T09:08:28.647

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

@krupeshAnadkat, You are most welcome and my pleasure to help something — A.A Noman, Nov 21 '19 at 20:22

score 10 · Answer 5 · edited Jun 19 '15 at 14:40

10

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

https://github.com/PHPOffice/PHPExcel

edited Jun 19 '15 at 14:40

Byron Wall

3,970
2
13
29

answered Oct 31 '13 at 21:55

andrunix

1,704
1
14
23

Deprecated, as stated on their own Github page. – acidrums4 Sep 12 '18 at 13:22
2

@acidrums4: You can use PhpSpreadsheet instead. It's the successor of PHPExcel: https://github.com/PHPOffice/PhpSpreadsheet – Roel Koops Feb 10 '19 at 11:09

score 5 · Answer 6 · answered Mar 29 '13 at 08:05

I think you should try with this API

http://code.google.com/p/php-excel/source/browse/trunk/php-excel.class.php

With This

Create a quick export from a database table into Excel

Compile some statistical records with a few calculations and deliver
the result in an Excel worksheet

Gather the items off your (web-based) todo list, put them in a
worksheet and use it as a foundation for some more statistics
magic.**

score 3 · Answer 7 · edited Oct 31 '13 at 21:52

3

Try this code:

<?php
    header("Content-type: application/vnd-ms-excel");

    header("Content-Disposition: attachment; filename=hasil-export.xls");

    include 'view-lap.php';
?>

edited Oct 31 '13 at 21:52

Spooky

2,966
8
27
41

answered Oct 31 '13 at 21:17

Jek Tv

39
1

score 3 · Answer 8 · answered Nov 22 '15 at 16:41

try this code

data.php

    <table border="1">
<tr>
    <th>NO.</th>
    <th>NAME</th>
    <th>Major</th>
</tr>
<?php
//connection to mysql
mysql_connect("localhost", "root", ""); //server , username , password
mysql_select_db("codelution");

//query get data
$sql = mysql_query("SELECT * FROM student ORDER BY id ASC");
$no = 1;
while($data = mysql_fetch_assoc($sql)){
    echo '
    <tr>
        <td>'.$no.'</td>
        <td>'.$data['name'].'</td>
        <td>'.$data['major'].'</td>
    </tr>
    ';
    $no++;
}
?>

code for excel file

export.php

<?php
// The function header by sending raw excel
header("Content-type: application/vnd-ms-excel");
// Defines the name of the export file "codelution-export.xls"
header("Content-Disposition: attachment; filename=codelution-export.xls");
// Add data table
include 'data.php';
?>

if mysqli version

$sql="SELECT * FROM user_details";
$result=mysqli_query($conn,$sql);
if(mysqli_num_rows($result) > 0)
{
    $no = 1;
            while($data = mysqli_fetch_assoc($result))
            {echo '
    <tr>
        <<td>'.$no.'</td>
        <td>'.$data['name'].'</td>
        <td>'.$data['major'].'</td>

    </tr>
    ';
    $no++;

http://codelution.com/development/web/easy-ways-to-export-data-from-mysql-to-excel-with-php/

score 2 · Answer 9 · answered Feb 20 '18 at 08:46

You can export the data from MySQL to Excel by using this simple code.

<?php
include('db_con.php');


$stmt=$db_con->prepare('select * from books');
$stmt->execute();


$columnHeader ='';
$columnHeader = "Sr NO"."\t"."Book Name"."\t"."Book Author"."\t"."Book 
ISBN"."\t";


$setData='';

while($rec =$stmt->FETCH(PDO::FETCH_ASSOC))
{
 $rowData = '';
 foreach($rec as $value)
 {
  $value = '"' . $value . '"' . "\t";
  $rowData .= $value;
 }
 $setData .= trim($rowData)."\n";
}


header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=Book record 
sheet.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo ucwords($columnHeader)."\n".$setData."\n";

?>

complete code here php export to excel

Trialsman · Answer 10 · 2016-02-29T23:30:16.933

Posts by John Peter and Dileep kurahe helped me to develop what I consider as being a simpler and cleaner solution, just in case anyone else is still looking. (I am not showing any database code because I actually used a $_SESSION variable.)

The above solutions invariably caused an error upon loading in Excel, about the extension not matching the formatting type. And some of these solutions create a spreadsheet with the data across the page in columns where it would be more traditional to have column headings and list the data down the rows. So here is my simple solution:

$filename = "webreport.csv";
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename");  
header("Pragma: no-cache"); 
header("Expires: 0");
foreach($results as $x => $x_value){
    echo '"'.$x.'",' . '"'.$x_value.'"' . "\r\n";
}

Change to .csv (which Excel instantly updates to .xls and there is no error upon loading.)
Use the comma as delimiter.
Double quote the Key and Value to escape any commas in the data.
I also prepended column headers to $results so the spreadsheet looked even nicer.

Samir Lakhani · Answer 11 · 2016-12-17T18:27:29.227

Try the Following Code Please. just only update two values.
1.your_database_name 2.table_name

 <?php
    $host="localhost";
    $username="root";
    $password="";
    $dbname="your_database_name";
    $con = new mysqli($host, $username, $password,$dbname); 

        $sql_data="select * from table_name";
        $result_data=$con->query($sql_data);
        $results=array();
    filename = "Webinfopen.xls"; // File Name
    // Download file
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Type: application/vnd.ms-excel");

    $flag = false;
    while ($row = mysqli_fetch_assoc($result_data)) {
        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";
    }
    ?>

score 0 · Answer 12 · answered Apr 16 '21 at 07:16

This is baes on John Peter's answer above. The code is working perfectly but I needed it for WordPress. So, I did something like this:

<?php

require '../../../wp-load.php';

$file_name = "registered-users";
$args = array( 'role' => 'client',
   'meta_query' => array( array(
       'key' => '_dt_transaction_archived',
       'compare' => 'NOT EXISTS'
   ) ),
   'order' => 'DESC',
   'orderby' => 'ID'
);
$users = get_users( $args );
$file_ending = "xls";

// Header info for browser
header( "Content-Type: application/xls" );
header( "Content-Disposition: attachment; filename=$file_name.$file_ending" );
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

print( "First Name" . $sep );
print( "Last Name" . $sep );
print( "E-Mail" . $sep );
print( "\n" );
// end of printing column names

// start foreach loop to get data
$schema_insert = "";

foreach ($users as $user) {
    if ( $user ) {
        $schema_insert = "$user->first_name" . $sep;
        $schema_insert .= "$user->last_name" . $sep;
        $schema_insert .= "$user->user_email" . $sep;
        print "\n";
        $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 ) );
    }
}

Export MySQL data to Excel in PHP

12 Answers12

Linked

Related