Data Export to Excel with PHP and MySQL

Today, We want to share with you How to export MySQL data to excel sheet in PHP MySQLi.In this post we will show you source code php export to excel, hear for Database Data Export to Excel File using PHP we will give you demo and example for implement.In this post, we will learn about PHP Split Export Convert Multiple Excel Sheet Files with an example.

export to excel in php

here i learn to export data to excel in php example and export to excel in php with MySQL Database.

Steps 1: Create MySQL Database Table

CREATE TABLE `members` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `location` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `member_type` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Steps 2: Get records from MyQL database and store in an array

<?php
$sql_query = "SELECT name, gender, location, member_type, age FROM members LIMIT 3";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
$members_list = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
	$members_list[] = $rows;
}
?>

Steps 3: Display data records with export button

index.php

<div class="container">	
	<h2>How to Export Data to Excel in PHP - www.pakainfo.com</h2>
	<div class="well-sm col-sm-12">
		<div class="btn-group pull-right">	
			<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">					
				<button type="submit" id="export" name='export' value="Export to excel" class="btn btn-info">Export to excel</button>
			</form>
		</div>
	</div>				  
	<table id="" class="table table-striped table-bordered">
		<tr>
			<th>Name</th>
			<th>Gender</th>
			<th>Age</th>	
			<th>Type</th>
			<th>Location</th>
		</tr>
		<tbody>
			<?php foreach($members_list as $membersData) { ?>
			   <tr>
			   <td><?php echo $membersData ['name']; ?></td>
			   <td><?php echo $membersData ['gender']; ?></td>
			   <td><?php echo $membersData ['age']; ?></td>   
			   <td><?php echo $membersData ['member_type']; ?></td>
			   <td><?php echo $membersData ['location']; ?></td>   
			   </tr>
			<?php } ?>
		</tbody>
    </table>	
</div>

Steps 4: Implement export data to excel with PHP

export.php

<?php
if(isset($_POST["export"])) {	
	$filename = "pakainfo_export_".date('Ymd') . ".xls";			
	header("Content-Type: application/vnd.ms-excel");
	header("Content-Disposition: attachment; filename=\"$filename\"");	
	$check_data_clm = false;
	if(!empty($members_list)) {
	  foreach($members_list as $value) {
		if(!$check_data_clm) {		 
		  echo implode("\t", array_keys($value)) . "\n";
		  $check_data_clm = true;
		}
		echo implode("\t", array_values($value)) . "\n";
	  }
	}
	exit;  
}
?>

I hope you get an idea about how to export sql server data into multiple excel sheets using php?.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.