Exporting Data from MySQL to Excel using PHP

I’ll show you how to export grid data to an excel file in this post. Export/Import is a relatively popular functionality for web development; nevertheless, there are times when we need to export entire grid data into an excel file.

in which case we should use the approach described below. In PHP, we simply need to set header information to force the browser to launch the download window.

Video Tutorial

If you are more comfortable in watching a video that explains about Exporting Data to Excel with PHP and MySQL, then you should watch this video tutorial.

You can also check other tutorial of Export Data with PHP,

• Exporting Data to Excel with PHP and MySQL
• Export Data to CSV and Download Using PHP and MySQL
• Import CSV File Into MySql Using PHP
• Export HTML Table Data to Excel, CSV, PNG and PDF using jQuery Plugin
• Export the jQuery Datatable data to PDF,Excel,CSV and Copy

Export MySQL Data To Excel in PHP

Because Excel is the finest format for storing data in a file, exporting data in Excel format is a very important tool that allows users to save data for offline use. You’ll learn how to use PHP and MySQL to export data to Excel.

Prerequisites:

Before we get started, ensure that you have the following set up:

1. A working PHP environment on your web server.
2. Access to a MySQL database with the data you wish to export.
3. Basic familiarity with PHP programming.

So the file structure for this example is the following:

• index.php: This is the entry file.
• connection.php: This file is used to connect MySQL with PHP
• generate_excel.php: This is the main PHP file that’ll have an export method to export data into the excel.

Create MySQL Database Table

Let’s create a tasks table that ll all tasks records which will export later on in excel format.

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Status` varchar(255) NOT NULL,
  `Priority` varchar(255) NOT NULL,
  `Date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for table `tasks`
--
ALTER TABLE `tasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

Now, I’ll insert some sample data into the tasks table.

INSERT INTO `tasks` (`id`, `Name`, `Status`, `Priority`, `Date`) VALUES
(1, 'Task1', 'Completed', 'Low', '2021-09-01'),
(2, 'Task2', 'InProgress', 'High', '2021-03-17'),
(3, 'Mysql', 'Hold', 'Low', '2021-09-22'),
(4, 'API', 'Pending', 'Low', '2021-09-06');

Create MySQL Connection With PHP

We’ll create a connection.php file and add the below code. in this file, We’ll pass the database hostname, database username, database password, and database name.

<?php
Class dbObj{
 /* Database connection start */ var $dbhost = "localhost";
 var $username = "root";
 var $password = "";
 var $dbname = "test";
 var $conn;
 function getConnstring() {
  $con = mysqli_connect($this->dbhost, $this->username, $this->password, $this->dbname) or die("Connection failed: " . mysqli_connect_error());

  /* check connection */  if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
  } else {
   $this->conn = $con;
  }
  return $this->conn;
 }
}
?>

Get the Tasks Data from MySQL Database Table

We’ll receive entries from a MySQL database table tasks and put them in an array so we can show them and export them to an excel file. Added below code into the top of the generate_excel.php file.

include_once("connection.php");
$db = new dbObj();
$connString =  $db->getConnstring();

$sql_query = "SELECT * FROM tasks";
$resultset = mysqli_query($connString, $sql_query) or die("database error:". mysqli_error($conn));
$tasks = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
 $tasks[] = $rows;
}

Export Data to Excel

Let’s create export features using PHP and export data into excel. We’ll also force the to browser download the file instead of display it. We’ll add the below code into the generate_excel.php file.

if(isset($_POST["ExportType"]))
{
  
    switch($_POST["ExportType"])
    {
        case "export-to-excel" :
            // Submission from
   $filename = "phpflow_data_export_".date('Ymd') . ".xls";   
            header("Content-Type: application/vnd.ms-excel");
   header("Content-Disposition: attachment; filename=\"$filename\"");
   ExportFile($tasks);
   //$_POST["ExportType"] = '';
            exit();
        default :
            die("Unknown action : ".$_POST["action"]);
            break;
    }
}
function ExportFile($records) {
 $heading = false;
  if(!empty($records))
    foreach($records 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;
}

the code shown above, The switch case block will execute based on the parameter value and the method invoked.
Browsers are being forced to download an excel file.

Create HTML and Display Records with Export Button

Define html layout for display data in table and button to fire export-to-csv action. Added below code into the index.php file.

<?php 
include_once("generate_excel.php");
?>

<meta charset="UTF-8" />
<title>Simple Example of Export Excel file using PHP and MySQL</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap-theme.min.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
<title>phpflow.com : Demo of export to excel file</title>

<div id="container">
    <div class="col-sm-6 pull-left">
        <div class="well well-sm col-sm-12">
            <div class="btn-group pull-right">
                <button type="button" class="btn btn-info">Action</button>
                <button type="button" class="btn btn-info dropdown-toggle" data-toggle="dropdown">
                    <span class="caret"></span>
                    <span class="sr-only">Toggle Dropdown</span>
                </button>

                <ul class="dropdown-menu" role="menu" id="export-menu">
                    <li id="export-to-excel"><a href="#">Export to excel</a></li>

                    <li class="divider"></li>

                    <li><a href="#">Other</a></li>
                </ul>
            </div>
        </div>

        <form action="generate_excel.php" method="post" id="export-form">
            <input type="hidden" value="" id="hidden-type" name="ExportType" />
        </form>

        <table id="" class="table table-striped table-bordered">
            <tbody>
                <tr>
                    <th>Name</th>

                    <th>Status</th>

                    <th>Priority</th>

                    <th>Date</th>
                </tr>
            </tbody>
            <tbody>
                <?php foreach($tasks as $row):?>

                <tr>
                    <td><?php echo $row ['Name']?></td>

                    <td><?php echo $row ['Status']?></td>

                    <td><?php echo $row ['Priority']?></td>

                    <td><?php echo $row ['Date']?></td>
                </tr>

                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>

We’ve imported the ‘generate_excel.php’ file at the top of the code, which will yield task data for display in the HTML table. We’ve established a dropwodn with a ‘export to excel’ option. All task data will be displayed in an HTML table.

Submit Form Using jQuery

We’ve included a dropdown and need to catch and fire events so that when a user selects an option, the form is submitted. We’ll paste the code below at the bottom of the index.php file.

<script type="text/javascript">
$(document).ready(function() {
jQuery('#Export to excel').bind("click", function() {
var target = $(this).attr('id');
switch(target) {
 case 'export-to-excel' :
 $('#hidden-type').val(target);
 //alert($('#hidden-type').val());
 $('#export-form').submit();
 $('#hidden-type').val('');
 break
}
});
    });
</script>

Result:

I hope it helps you!.

Demo & Download