Export HTML Table Data to Excel or CSV and Download using PHP & MySQL

[norebro_social_networks icon_layout=”boxed”]

Here in this tutorial quickly i am going to explain how to export grid data or HTML table data to excel file or CSV ( comma-seperated values). Export/Import is very common functionality for web development, some time we need to export all grid data into excel file or csv file for reporting purposes. In those cases we need below method to export data into excel file or csv file. it’s very easy and simple steps in PHP, we need to set header information to force browser to open download window.

Step 1: Sample array data or MYSQL data in key-value pair

$data = array(
     '0' => array('Name'=> 'user1', 'Status' =>'complete', 'Priority'=>'Low', 'Salary'=>'001'),
     '1' => array('Name'=> 'user2', 'Status' =>'inprogress', 'Priority'=>'Low', 'Salary'=>'111'),
     '2' => array('Name'=> 'user3', 'Status' =>'hold', 'Priority'=>'Low', 'Salary'=>'333'),
     '3' => array('Name'=> 'user4', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'444'),
     '4' => array('Name'=> 'user5', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'777'),
     '5' => array('Name'=> 'user6', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'777')
    );

Step 2: PHP to code for post action and also setting up header information for Excel or CSV files

if(isset($_POST["ExportType"]))
{
    switch($_POST["ExportType"])
    {
        case "export-to-excel" :
            // Submission from
            $filename = $_POST["ExportType"] . ".xls";       
            header("Content-Type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=\"$filename\"");
            ExportFile($data);
            //$_POST["ExportType"] = '';
            exit();
        case "export-to-csv" :
            // Submission from
            $filename = $_POST["ExportType"] . ".csv";       
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-type: text/csv");
            header("Content-Disposition: attachment; filename=\"$filename\"");
            header("Expires: 0");
            ExportCSVFile($data);
            //$_POST["ExportType"] = '';
            exit();
        default :
            die("Unknown action : ".$_POST["action"]);
            break;
    }
}
 
function ExportCSVFile($records) {
    // create a file pointer connected to the output stream
    $fh = fopen( 'php://output', 'w' );
    $heading = false;
        if(!empty($records))
          foreach($records as $row) {
            if(!$heading) {
              // output the column headings
              fputcsv($fh, array_keys($row));
              $heading = true;
            }
            // loop over the rows, outputting them
             fputcsv($fh, array_values($row));
              
          }
          fclose($fh);
}
 
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;
}

Above code tells the browser to forcefully open download window for exporting as Excel or CSV file by using header information.

Step 3: HTML layout using Bootstrap with buttons to export data.

<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">
 
<!-- Optional theme -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap-theme.min.css">
 
<!-- Jquery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
 
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js"></script>
 
<style type="text/css">
    .container{
        margin-top: 20px;
    }
</style>
 
<title>KodeInfo.com : Export to excel or CSV file</title>
 
<div class="container">
    <div class="row clearfix">
        <div class="col-md-12 column">
            <div class="panel panel-default">
                <div class="panel-heading">               
                    <h3 class="panel-title" style="line-height:35px;">Source code : PHP export to Excel or CSV file  <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 id="export-to-csv"><a href="#">Export to csv</a></li>
                            <li class="divider"></li>
                            <li><a href="#">Other</a></li>
                          </ul>
                        </div>
                    </h3>
                </div>
 
                <div class="panel-body">
                    <form action="index.php" method="post" id="export-form">
                        <input type="hidden" value='' id='hidden-type' name='ExportType'/>
                    </form>
                      
                     <table id="" class="table table-striped table-bordered">
                        <tr>
                            <th>Name</th>
                            <th>Status</th>
                            <th>Priority</th>
                            <th>Salary</th>
                        </tr>
                         
                        <tbody>
                          <?php foreach($data 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 ['Salary']?></td>
                          </tr>
                          <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

Above code forms an HTML layout using Twitter Bootstrap to display data into table and buttons to fire export actions.

Step 4: jQuery code for the click event

$(document).ready(function() {
     
        jQuery('#export-menu li').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
                case 'export-to-csv' :
                $('#hidden-type').val(target);
                //alert($('#hidden-type').val());
                $('#export-form').submit();
                $('#hidden-type').val('');
                break
            }
        });
    });

Above code tackle, export action button click event to export data into a respect format.

You can find the official tutorial code here.

php export csv

Imran Member since April 21, 2018

Imran is a web developer and consultant from India. He is the founder of KodeInfo, the PHP and Laravel Community . In the meantime he follows other projects, works as a freelance backend consultant for PHP applications and studies IT Engineering . He loves to learn new things, not only about PHP or development but everything.