Creating Excel file from MySQL

Question

I am trying to generate an XLS file from a table in a MySQL database, but the Excel file is not formatted properly and an Excel file is generated when an error occurs. "The file you are trying to open is in a different format than the one indicated." When a file is opened, the data is not formatted properly.

Any ideas what I am missing?

<?php $host = 'XXXXXXX'; $dbname = 'XXXXXXXX'; $username = 'XXXXXXXX'; $password = 'XXXXXXXX'; function xlsBOF() { echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); return; } function xlsEOF() { echo pack("ss", 0x0A, 0x00); return; } function xlsWriteLabel($Row, $Col, $Value ) { $L = strlen($Value); echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); echo $Value; return; } function xlsWriteNumber($Row, $Col, $Value) { echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); echo pack("d", $Value); return; } try { $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); echo "Connected to $dbname at $host successfully."; $conn = null; } catch (PDOException $pe) { die("Could not connect to the database $dbname :" . $pe->getMessage()); } $q = "SELECT * FROM tablename"; $qr = mysql_query( $q ) or die( mysql_error() ); header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Disposition: attachment;filename=export_".$dbtable.".xls "); header("Content-Transfer-Encoding: binary "); xlsBOF(); $col = 0; $row = 0; $first = true; while( $qrow = mysql_fetch_assoc( $qr ) ) { if( $first ) { foreach( $qrow as $k => $v ) { xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) ); $col++; } $col = 0; $row++; $first = false; } // go through the data foreach( $qrow as $k => $v ) { // write it out xlsWriteLabel( $row, $col, $v ); $col++; } // reset col and goto next row $col = 0; $row++; } xlsEOF(); exit(); 

Answer 1

I'm not sure about .xls, but to output the MySQL result as a CSV table, the fputcsv function does this without much trouble:

 // Clear any previous output ob_end_clean(); // I assume you already have your $result $num_fields = mysql_num_fields($result); // Fetch MySQL result headers $headers = array(); $headers[] = "[Row]"; for ($i = 0; $i < $num_fields; $i++) { $headers[] = strtoupper(mysql_field_name($result , $i)); } // Filename with current date $current_date = date("y/m/d"); $filename = "MyFileName" . $current_date . ".csv"; // Open php output stream and write headers $fp = fopen('php://output', 'w'); if ($fp && $result) { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename='.$filename); header('Pragma: no-cache'); header('Expires: 0'); echo "Title of Your CSV File\n\n"; // Write mysql headers to csv fputcsv($fp, $headers); $row_tally = 0; // Write mysql rows to csv while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $row_tally = $row_tally + 1; echo $row_tally.","; fputcsv($fp, array_values($row)); } die; } 

Answer 2

use http://phpexcel.codeplex.com/

C'est la meilleur solution pour générer un fichier excel Vous pouvez même créer plusieurs feuilles dans le fichier et formater les cellules (cooler, police, border, ...)

Google translate :

This is the best solution to create excel file. You can even create multiple files in a file and format cells (color, font, border, ...)

Answer 3

 <?php //download.php page code //THIS PROGRAM WILL FETCH THE RESULT OF SQL QUERY AND WILL DOWNLOAD IT. (IF YOU HAVE ANY QUERY CONTACT: [e-mail] ) //include the database file connection include_once('database.php'); //will work if the link is set in the indx.php page if(isset($_GET['name'])) { $name=$_GET['name']; //to rename the file header('Content-Disposition: attachment; filename='.$name.'.xls'); header('Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0'); header('Pragma: no-cache'); header('Content-Type: application/x-msexcel; charset=windows-1251; format=attachment;'); $msg=""; $var=""; //write your query $sql="select * from tablename"; $res = mysql_query($sql); $numcolumn = mysql_num_fields($res); //will fetch number of field in table $msg="<table><tr><td>Sl No</td>"; for ( $i = 0; $i < $numcolumn; $i++ ) { $msg.="<td>"; $msg.= mysql_field_name($res, $i); //will store column name of the table to msg variable $msg.="</td>"; } $msg.="</tr>"; $i=0; $count=1; //used to print sl.no while($row=mysql_fetch_array($res)) //fetch all the row as array { $msg.="<tr><td>".$count."</td>"; for($i=0;$i< $numcolumn;$i++) { $var=$row[$i]; //will store all the values of row $msg.="<td>".$var."</td>"; } $count=$count+1; $msg.="</tr>"; } $msg.="</table>"; echo $msg; //will print the content in the exel page } ?> <?php //index.php page $name="any file name"; echo "<a href='download.php?name=".$name."'>Click to download</a>"; //link to download file ?>