Export PHP data to .xls file

Here, I’m going to tell you how to export your php data to .xls file. First of all, you have to prepare your data in this format.

$export_data = array(
	  array(
	            'Product' => 'Product 1',
	            'Valid From' => '2016-04-06 19:02:00',
	            'Valid Till' => '2016-04-07 19:04:00',
	            'Views' => 17,
	            'Quantity Request' => 4,
	            'Number of Accepted Requests' => 0,
	            'Number of Rejected Requests' => 4,
	            'Vendor' => 1,
	            'System' => 3
	        ),
	  array(
	            'Product' => 'Product 2',
	            'Valid From' => '2016-04-08 19:00:00',
	            'Valid Till' => '2016-04-15 19:00:00',
	            'Views' => 19,
	            'Quantity Request' => 1,
	            'Number of Accepted Requests' => 1,
	            'Number of Rejected Requests' => 0,
	            'Vendor' => 1,
	            'System' => 0
	        ),
	  array(
	            'Product' => 'Product 3',
	            'Valid From' => '2016-04-08 15:30:00',
	            'Valid Till' => '2016-04-12 04:30:00',
	            'Views' => 6,
	            'Quantity Request' => 1,
	            'Number of Accepted Requests' => 1,
	            'Number of Rejected Requests' => 0,
	            'Vendor' => 1,
	            'System' => 0
	        )
	);

You can even import data from the database and manage to put it in this format.

After fetching data in variable $export_data, put the code below to download a .xls file.

$fileName = "export_data" . rand(1,100) . ".xls";

if ($export_data) {
	function filterData(&$str) {
		$str = preg_replace("/\t/", "\\t", $str);
		$str = preg_replace("/\r?\n/", "\\n", $str);
		if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
	}

	// headers for download
	header("Content-Disposition: attachment; filename=\"$fileName\"");
	header("Content-Type: application/vnd.ms-excel");

	$flag = false;
	foreach($export_data as $row) {
		if(!$flag) {
			// display column names as first row
			echo implode("\t", array_keys($row)) . "\n";
			$flag = true;
		}
		// filter data
		array_walk($row, 'filterData');
		echo implode("\t", array_values($row)) . "\n";
	}
	exit;			
}

Here, array_walk function is used which runs each array element in a user-defined function. The parameter for this function are array’s keys and values.

The output will a .xls file:

Hope, this may help you. 🙂