Export MySQL data to Excel in PHP

0 7555

Here below, in this article we will fetch records from database and export in into the excel format. Then excel will be auto downloaded.

Follow the below steps

Create First File - File Name: index.php

<?php
<a href="export-export.php">Export to excel</a>
?>

Create Second File - File Name: dbconnect.php

<?php session_start();

global $db;
$GLOBALS['db'] = mysqli_connect("localhost","root","","ion8");

?>

Create Third File - File Name: excel-export.php

<?php
include("dbconnect.php"); //database connection

function exportInExcel(Name, $headerRow, $data) {

ini_set('max_execution_time', 1600); //increase max_execution_time to 10 min if data set is very large

Content = implode("\t ", $headerRow)."\n";

foreach($data as $result) {

Content .=  implode("\t ", $result)."\n";

}

header('Content-type: application/ms-excel'); // you can set csv format
header('Content-Disposition: attachment; filename='.Name);

echo Content;
exit;

}

function get_customers_lists(){

$qry = "SELECT * from users WHERE 1  order by id desc limit 0,300";
$product_query=mysqli_query($db,$qry) or die('Mysql Err1:'. mysqli_error($db));

$rec_list = array();

if($product_query)
{
    while($fetch_products=mysqli_fetch_array($product_query))
{
    $rec_list[] = $fetch_products;
}

}

return $rec_list;

}

Name = "excel_".date("d-m-y:h:s").".xls";
// Name = "report_".date("d-m-y:h:s").".csv";

?>

<?php

$fetch_list = get_customers_lists();

$res_data = array();
$headerRow = array('Uid','Name','Email','Mobile','City','State', 'Country','Zip Code','Status','Updated','Date Added');  
foreach($fetch_list as $fetch_detail){

$res_data['id'] = $fetch_detail['id'];
$res_data['name'] = $fetch_detail['name'];
$res_data['email'] = $fetch_detail['email'];
$res_data['mobile'] = $fetch_detail['mobile'];
$res_data['city'] = ucfirst($fetch_detail['city']);
$res_data['state'] = ucfirst($fetch_detail['state']);
$res_data['country'] = ucfirst($fetch_detail['country']);
$res_data['zipcode'] = ucfirst($fetch_detail['zipcode']);
$res_data['status'] = ucfirst($fetch_detail['status']);
$res_data['timestamp'] = ucfirst($fetch_detail['timestamp']);
$res_data['date_added'] = ucfirst($fetch_detail['date_added']);

$data[] = $res_data;

}

exportInExcel(Name, $headerRow, $data);

?>

Do you think there are other concepts that I am include in this blog? Share with me through the comment box!

I would love to hear from you!

Also, don't forget to like this post and share it with your peers!