Creating Excel file from MySQL
I'm not sure about .xls but for outputting a MySQL result as a CSV table the fputcsv function does it without much fuss:
// 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;
}
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 (couleur, police, bordure, ...)
Google translate:
This is the best solution to generate an excel file You can even create multiple sheets in the file and format the cells (color, font, border, ...)
<?php
//download.php page code
//THIS PROGRAM WILL FETCH THE RESULT OF SQL QUERY AND WILL DOWNLOAD IT. (IF YOU HAVE ANY QUERY CONTACT:[email protected])
//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
?>