Download Mysql Table Data into Excel Sheet Format in PHP

Abhigyan Singh 12th Jul 2020

This tutorial will explain about downloading the mysql data into excel sheet format using PHP mysql. Here we will select all the data as per our mysql query and generate a excel file according to that data and we can set a selected column name as the excel sheet header. We will make use of PHP and Mysql to do this functionality. This downloading option is very important in web application. Using this code we can generate a report from our Mysql data. We can generate different type of Report from User Report, Admin Report or Leads Report etc. When anyone needs to generate any types of report, this code is very helpful.

Also read This: (Import Excel file data in mysql database using PHP)

Explanation:

First we will have a Mysql Table with some data. Then we will make a mysql connectivity using this code.

define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS","");
define ("DB_NAME","DATABASE_NAME");

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

Here our query will select all the row from that table and generate a excel file. After that we will download the file.

<?php

$setCounter = 0;

$setExcelName = "download_excal_file";

$setSql = "YOUR SQL QUERY GOES HERE";

$setRec = mysql_query($setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
$setMainHeader .= mysql_field_name($setRec, $i)."t";
}

while($rec = mysql_fetch_row($setRec)) {
$rowLine = '';
foreach($rec as $value) {
if(!isset($value) || $value == "") {
$value = "t";
} else {
//It escape all the special charactor, quotes from the data.
$value = strip_tags(str_replace('"', '""', $value));
$value = '"' . $value . '"' . "t";
}
$rowLine .= $value;
}
$setData .= trim($rowLine)."n";
}
$setData = str_replace("r", "", $setData);

if ($setData == "") {
$setData = "nno matching records foundn";
}

$setCounter = mysql_num_fields($setRec);

//This Header is used to make data download instead of display the data
header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$setExcelName."_Reoprt.xls");

header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."n".$setData."n";

?>

Just download the code and start creating report from your mysql table.

Note: here By just changing the header type we can generate other types of file like csv. Docx, txt file.

Also read This: (Import Excel file data in mysql database using PHP)

Authored By Abhigyan Singh

He is a continuous blogger and has blogged on different topic. He loves to surf Internet and always trying to get new Idea about new Technology and Innovations and sharing these great information to all the technology lovers.

Also on DiscussDesk