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 & 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
Post a Comment