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
?>

Tags:

Mysql

Php

Excel