Export MySql data into Excel or CSV format

To generate reports or to print data or for some other purpose we often need data to be export in excel or csv format. It is quiet easy to fetch records from mysql and export the result set as .csv file or .xls file and let user to force download it. Here is the code:

Here, first we will extract the data which we want to export.
Then, will make make headers for exported file and then append the exported data
then will forcefully download the file.

<?php
/*
* Export Mysql Data in excel or CSV format using PHP
* Downloaded from http://DevZone.co.in
*/

// Connect to database server and select 
$con=mysqli_connect('localhost','root','','adventureworks');

if (mysqli_connect_errno()) { 
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// retrive data which you want to export
$query = "SELECT * FROM vendor limit 60";
$header = '';
$data ='';

$export = mysqli_query($con,$query ) or die(mysqli_error($con));

// extract the field names for header 

while ($fieldinfo=mysqli_fetch_field($export))
{
	$header .= $fieldinfo->name."\t";
}

// export data 
while( $row = mysqli_fetch_row( $export ) )
{
    $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 Record(s) Found!\n";                        
}

// allow exported file to download forcefully
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=devzone_co_in_export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>

<?php

* Export Mysql Data in excel or CSV format using PHP

* Downloaded from http://DevZone.co.in

// Connect to database server and select

$con=mysqli_connect('localhost','root','','adventureworks');

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

// retrive data which you want to export

$query = "SELECT * FROM vendor limit 60";

$header = '';

$data ='';

$export = mysqli_query($con,$query ) or die(mysqli_error($con));

// extract the field names for header

while ($fieldinfo=mysqli_fetch_field($export))

{

$header .= $fieldinfo->name."\t";

}

// export data

while( $row = mysqli_fetch_row( $export ) )

{

$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 Record(s) Found!\n";

}

// allow exported file to download forcefully

header("Content-type: application/octet-stream");

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

header("Pragma: no-cache");

header("Expires: 0");

print "$header\n$data";

–Export MySql data into Excel or CSV format–

Export MySql data into Excel or CSV format

Related Posts