KodeInfo | Learning resources for web and mobile development

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

December 2nd, 2014 08:18:06 by Imran Iqbal Comments(0) - Views(50446)

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 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 a HTML layout using Twitter Bootstrap to display data into table and buttons to fire export actions.

Step 4: jQuery code for 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 turorial code here.

Author

  • Imran Iqbal
    Imran Iqbal

    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.

Related

WHY USE A FRAMEWORK OVER PLAIN PHP

WHY USE A FRAMEWORK OVER PLAIN PHP
read more

GETTING STARTED WITH LARAVEL

GETTING STARTED WITH LARAVEL
read more

UNDERSTANDING LARAVEL STRUCTURE

UNDERSTANDING LARAVEL STRUCTURE
read more

UNDERSTANDING LARAVEL ROUTES

UNDERSTANDING LARAVEL ROUTES
read more

comments powered by Disqus