How To Export MySQL Database Tables To Excel XLS Using PHP?

Export MySQL Database Tables is a familiar operation for many of us. phpMyAdmin is the go-to choice for a database client in PHP. It provides database administration tools and also allows Exporting MySQL Database Tables. The exported data can be in various formats like SQL, CSV as selected.

This tutorial will learn you How to Export MySQL Database Tables from web application to Excel file using PHP programming language. This functionality is mostly required in enterprise-level web applications. There are lots of data is a transfer on a daily basis and manage that into separate excel file. So, at that time this type of functionality is required in web application. This functionality reduces lots of time to take data into an excel file.

In this simple post, we have learned something regarding how to Export MySQL Database Tables To Excel .XLS in PHP. If you have developed any project then that project you have to require this functionality like Exporting Data to Excel Sheet. So we have developed this tutorial, in which we have made simple PHP Script for Export Data from Web to Excel.

In this tutorial, we will show you how to export MySQL data to excel using PHP and HTML. Sometimes there is a need to display your database data in an excel spreadsheet then you can choose this method to export MySQL data to excel. You may also like import excel file data to MySQL using PHP.

Export MySQL Database Tables To Excel XLS Using PHP:

<?php
/****************************************************************************/
// Enter The Required Data Here
/****************************************************************************/
	$connection; // Your Connection Query Variable
	$TableName= 'YourTableName'; // Define Your Table Name
	$xls_filename = 'YourFileName.xls'; // Define Excel (.xls) file name
	$downloadQuery = "SELECT * FROM $TableName";
$result = mysqli_query($connection,$downloadQuery);
if(!$result){
// Error Reporting Message To User
echo "<div class='alert alert-danger'>ERROR # '".mysqli_errno($connection)."' | ERROR: '".mysqli_error($connection)."'. ".exe_contactUs()."</div>";
exit();
die();
} else {

// Header Info Settings
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$xls_filename");
header("Pragma: no-cache");
header("Expires: 0");

/***** Start of Formatting for Excel *****/
// Define separator (defines columns in excel &amp; tabs in word)
$sep = "\t"; // tabbed character

// Start of printing column names as names of MySQL fields
for ($i = 0; $i<mysqli_num_fields($result); $i++) {
echo mysqli_fetch_field_direct($result, $i)->name . "\t";
}
echo "\n";
// End of printing column names

// Start while loop to get data
while($row = mysqli_fetch_array($result)){
//array_walk($row, __NAMESPACE__ . '\cleanData'); // UnComment To Stop Auto Formatting Of Data For Excel XLS Format
$schema_insert = "";
for($j=0; $j<mysqli_num_fields($result); $j++){
if(!isset($row[$j])) {
$schema_insert .= "NULL".$sep;
}
elseif ($row[$j] != "") {
$schema_insert .= "$row[$j]".$sep;
}
else {
$schema_insert .= "".$sep;
}
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
echo trim($schema_insert);
echo "\n";
}
}

/****************************************************************************/
// Stop Auto Formatting Of Data For Excel XLS Format (Not Important, On Demand)
/****************************************************************************/
function cleanData(&$str) {
// escape tab characters
$str = preg_replace("/\t/", "\\t", $str);

// escape new lines
$str = preg_replace("/\r?\n/", "\\n", $str);

// convert 't' and 'f' to boolean values
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';

// force certain number/date formats to be imported as strings
if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
$str = "'$str";
}

// escape fields that include double quotes
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

?>

Customization:

You can change anything in the upper code if you are pro else we do not recommend you to edit the code as it may break while executing and can cause fatal errors to your server.

Troubleshooting the Errors

Do it with concentration and patience. Check your alls steps and again and all codes or scripts. If you find any error you can contact us anytime via comment or better via email, We are always here to help you.

Final Words:

That’s all we have. We hope that you liked this article. If you have any problem with this code in your file then feel free to contact us with a full explanation of your problem. We will reply to you as time allows us or If you have any doubts and problem please comment below. We are happy to help you! If you liked this article, Don’t forget to share this with your friends so they can also take benefit from it and leave your precious feedback in our comment form below. Happy development, See you in the next article.