Export Data To Excel In Php Code -My Programming School

Contents

Export Data to Excel in Php Code

In this tutorial we learn how to Export Data to Excel in Php Code and also using mysql. We create the complete project to Export Data to Excel in Php Code and Mysql.

Export Data to Excel is a popular feature of web applications to allow dynamic data export to Excel file to save locally for further use. If you’re a PHP developer and thinking to implement data export to excel, then you’re here at right place. You will learn here how to implement Export Data to Excel in Php Code & MySQL.

Export Data to Excel in Php Example

In our previous tutorial you have learned how to Export HTML Table Data to Excel, now in this tutorial we will explain how to Export Data to Excel in Php Code and MySQL.

We will cover this tutorial step by step to create live demo to implement Export Data to Excel in Php and MySQL. You can also download complete source code of live.

So let’s start implementing Export Data to Excel in Php and MySQL. Before we begin, take a look on files structure for this example.

How to Create MySQL Database Table

As we will cover this tutorial with example to Export Data to Excel in Php Code, so first we will create MySQL database table developers to store developer records.

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

We will also insert few records to this table.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30, 'image_2.jpg'),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25, 'image_2.jpg'),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35, 'image_2.jpg'),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26, 'image_2.jpg'),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23, 'image_2.jpg'),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38, 'image_2.jpg'),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30, 'image_3.jpg');

How to Get Records from MySQL Database Table

In export.php file, we will get developer records from MySQL database table developers and store into an array.

include_once("db_connect.php");
$sqlQuery = "SELECT name, gender, age, skills, address, designation FROM developers LIMIT 10";
$resultSet = mysqli_query($conn, $sqlQuery) or die("database error:". mysqli_error($conn));
$developersData = array();
while( $developer = mysqli_fetch_assoc($resultSet) ) 
	$developersData[] = $developer;

How to Display Records with Export to Excel Button

In index.php file, we will display developer records from $developersData array. We will also add data export button to export data.

<div class="container">		
	<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="dataExport" name="dataExport" 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>Skills</th>			
			<th>Address</th>
			<th>Designation</th>
		</tr>
		<tbody>
			<?php foreach($developersData as $developer)  ?>
			   <tr>
			   <td><?php echo $developer ['name']; ?></td>
			   <td><?php echo $developer ['gender']; ?></td>
			   <td><?php echo $developer ['age']; ?></td>  
				<td><?php echo $developer ['skills']; ?></td>			   
			   <td><?php echo $developer ['address']; ?></td>
			   <td><?php echo $developer ['designation']; ?></td>   
			   </tr>
			<?php  ?>
		</tbody>
    </table>		
</div>

How to Implement Data Export to Excel

Now we will implement Export Data to Excel in Php Code when export button clicked. We will use $developersData array for data exported and saved into an xlsx file.

<?php
if(isset($_POST["dataExport"])) 	
	$fileName = "webdamn_export_".date('Ymd') . ".xls";			
	header("Content-Type: application/vnd.ms-excel");
	header("Content-Disposition: attachment; filename="$fileName"");	
	$showColoumn = false;
	if(!empty($developersData)) 
	  foreach($developersData as $developerInfo) 
		if(!$showColoumn) 		 
		  echo implode("t", array_keys($developerInfo)) . "n";
		  $showColoumn = true;
		
		echo implode("t", array_values($developerInfo)) . "n";
	  
	
	exit;  

?>

Download Export Data to Excel in PHP and MySql

Here you will find Export Data to Excel in Php Code and then You can view the live demo from the Demo link and can download the Export Data to Excel in Php Code with below link.

Download