Export to excel using PHP and Jquery
In order to export MySQL table data to excel let's create a HTML page and put two anchor tags, one is to export to excel and the other to export to CSV (Comma Seperated Value). Write a jquery function to submit the form which makes call to the PHP function to export data returned to excel.
HTML Page<form id="site" method="post" enctype="multipart/form-data">
<div>
<a id="studentsExportExcel" href="javascript:void(0)" onclick="exportExcel(this)">Export to excel (.xls)</a>
<a id="studentsExportCSV" href="javascript:void(0)" onclick="exportExcel(this)">Export to csv</a>
</div>
<input type="hidden" value="" id="hidden-type" name="studentsExportType"/>
</form>
Jquery
$(document).ready(function () {
function exportExcel(obj) {
var target = $(obj).attr('id');
switch (target) {
case 'studentsExportExcel':
case 'studentsExportCSV':
$('#hidden-type').val(target);
$('#site').submit();
$('#hidden-type').val('');
break;
}
}
});
PHP Function
public function students()
{
if(isset($_POST["studentsExportType"]))
{
switch($_POST["studentsExportType"])
{
case "studentsExportExcel" :
// Submission from
$filename = "students". date('dMY') .".xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
students_export_excel();
$_POST["studentsExportType"] = '';
exit();
case "studentsExportCSV":
$filename = "students". date('dMY') . ".csv";
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");
$handle = fopen('php://output', 'w');
$data = get_students();
foreach ($data as $data)
{
fputcsv($handle, $data);
}
fclose($handle);
exit();
default :
die("Unknown action : ".$_POST["action"]);
break;
}
}
}
function students_export_excel()
{
$heading = false;
$students = get_students();
if(!empty($students))
foreach($students as $row) {
if(!$heading) {
// display field/column names as a first row
echo implode("\t", array_keys($row)) . "\n";
$heading = true;
}
echo implode("\t", array_values($row)) . "\n";
}
exit;
}
function get_students()
{
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM students";
$result = $conn->query($sql);
return $result;
}
