Generate Excel Sheet in PHP

Hello readers, in this post we are going to see the code to  generate excel sheet in PHP by exporting contents from database i.e., directly outputting your query result to excel sheet (.xls file) and adjusting HTTP header fields

<?php// DATABASE DETAILS$DB_USERNAME="root";$DB_PASSWORD="root";$DB_HOST="localhost";$DB_NAME="local_test";//ESTABLISHING CONNECTION$conn=mysql_connect($DB_HOST,$DB_USERNAME,$DB_PASSWORD);$db=mysql_select_db($DB_NAME,$conn);if(!$conn)die("Sorry colud not connected to database");$query="SELECT * FROM MY_TABLE";//FIRING QUERY AND COLLECTING RESULT$result = mysql_query($query);// Header info settingsheader("Content-Type: application/xls");header("Content-Disposition: attachment; filename=output.xls");header("Pragma: no-cache");header("Expires: 0");// Define separator (defines columns in excel &amp; tabs in word)$sep = "\t"; // tabbed character// Start of printing column names as names of MySQL fieldsfor ($i = 0; $i<mysql_num_fields($result); $i++) { echo mysql_field_name($result, $i) . "\t";}print("\n");// End of printing column names// Start while loop to get datawhile($row = mysql_fetch_row($result)){  $schema_insert = "";  for($j=0; $j<mysql_num_fields($result); $j++)  {				if(!isset($row[$j])) {				  $schema_insert .= "NULL".$sep;				}				elseif ($row[$j] != "") {				 $schema_insert .= "$row[$j]".$sep;				}				else {				 $schema_insert .= "".$sep;				} } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n";}?>

 

Comments

Popular posts from this blog

MATLAB code for Circular Convolution using Matrix method

Positive number pipe in angular 2+