Export MySQL data to Excel in PHP

by Yogesh Khanna 30-Jul-18

0 1051


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","ion8_advance","ION@4u$24)","ion8");

?>

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

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

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

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

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

foreach($data as $result) {

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

}

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

echo $fileContent;
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;

}

$fileName = "excel_".date("d-m-y:h:s").".xls";
// $fileName = "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($fileName, $headerRow, $data);

?>



Share:


Comments

    No Comments Yet.

Leave a Reply