Export php result table to EXCE sheet

Posted on 2005-04-20
Last Modified: 2008-02-01
Greetings experts,
I am in an urgent need for your help regarding some codes to export php result into excel sheet.
Lets say I have the following php codes :-

$hostname = "HOSTNAME";
$username = "sa";
$password = "sapassword";
$dbName = "mydatabase";
MSSQL_CONNECT($hostname,$username,$password) or DIE("DATABASE FAILED TO RESPOND.");
mssql_select_db($dbName) or DIE("Table unavailable");

$query = "SELECT Nationality,applicants,familysize FROM custormerstable";
$result = mssql_query($query);
echo ("<CAPTION><B><FONT SIZE='4' color='black' > Customers by Nationality</FONT></B></CAPTION><br>");
echo ("\n<table border=2 BORDERCOLORLIGHT='black' BORDERCOLORDARK='black' width='95%'>\n<thead>\n<tr>\t\n");
echo ("<th align='center'style='background-color:#66FF00'>No</th>");
echo ("<th align='center'style='background-color:#66FF00'>Nationality</th>");
echo ("<th align='center'style='background-color:#66FF00'>Applications</th>");
echo ("<th align='center'style='background-color:#66FF00'>Size</th>");
echo ("\n</tr>\n</thead>\n<tbody>");
for ($i = 0; $i < mssql_num_rows($result); $i++) {
echo("\n<tr style='background-color:#CCFF99'>");
echo ("\n\t<td><center>" . $k . "</center></td>");
$row_array = mssql_fetch_row($result);
for ($j = 0; $j < mssql_num_fields($result); $j++) {
echo ("\n\t<td><CENTER>" . $row_array[$j] . "</CENTER></td>");
echo ("\n</tr>");
echo ("\n</tbody>\n</table>");

The result would show something like :-

No   Nationality  Applications  Size
1          USA          12            22  
2           UK            6             24
3          AUL           2              7
4           TR           4              15

(sometimes the result wold be more than 4 rows .. not necessarly to be 4 rows only)
Now .. what I want to do is .. to put a button ( like form/ action submit ) at the end of the result table .. something like ( click here to exprot to excel sheet) and when you click on that button it should take you to microsoft excel and show you the results in a spreadsheet table.
I heard that the date has problems when exporting php to excel .. ifyou can give me the correct codes that could work even with numbers and dates fields I will be very greatful.
Thank you very much.
Question by:npiut

    Author Comment

    Thank you very much
    LVL 19

    Expert Comment

    header("Content-Type: application/; name='excel'");
    header("Content-disposition:  attachment; filename="yourfile.xls");

    If  you put that at the top of your document it should do the trick..


    Author Comment

    I dont' think i understand you correctly.. what document ? the php file ?
    php file called ( nationality.php ) it starts with :-
    then the php codes here and at the end :-
    I tried several places for your code but couldn't figure out how it works .. also .. what I need is a button at the end of the table ..( excel sheet to be generated when required only).
    LVL 3

    Accepted Solution

    Try this.

    // connection to mysql server
    $con = mysql_connect("localhost","username","password");

    // selecting the db

    // preparing sql query
    $query = "SELECT Nationality,applicants,familysize FROM custormerstable";
    $result = mysql_query($query);

    // this is the title of the excel sheet
    $title = "No,Nationality,Applications,Size\n";

    // looping the query resuls
    while ($row = mysql_fetch_array($result)) {
       // concatinating the contents with \n character
       $content .= $cnt.",".$row['Nationality'].",".$row['applicants'].",".$row['familysize']."\n";

    $export_data = $title.$content;

    $filename = "your writable folder/export.csv";

    // Let's make sure the file exists and is writable first.
    if (is_writable($filename)) {

       // In our example we're opening $filename in write mode.
       // that's where $export_data will go when we fwrite() it.
       if (!$handle = fopen($filename, 'w')) {
            print "Cannot open file ($filename)";

       // Write $export_data to our opened file.
       if (!fwrite($handle, $export_data)) {
           print "Cannot write to file ($filename)";
       print "Success, wrote to file ($filename)";
       header("Pragma: public"); // required
       header("Expires: 0");
       header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
       header("Cache-Control: private",false); // required for certain browsers
       header("Content-Type: application/ms-excel");
       header("Content-Disposition: attachment; filename=Sales_Report.csv");
       header("Content-Transfer-Encoding: binary");
       header("Content-Length: ".filesize($filename));
    } else {
       print "The file $filename is not writable";

    Hope this helps
    LVL 9

    Expert Comment

    There is a real cool PEAR class that does it for you: PEAR::spreadsheet-excel-writer
    LVL 19

    Expert Comment

    Doesn't matter where you want could put it in the same file and only execute that part upon submittion of your form...but if you can't figure it out...try designbai's code...that should work...


    Author Comment

    Thank you very much designbai .. it worked perfectly .. thanks all for your supports

    Author Comment

    One more question though .. when I run it it display all the results on the screen .. how can I change it so that it just say ( result were sent to export.csv (without displaying the results on the screen with all those column's contents ) ?
    I tried to remark the line print "Success, wrote to file ($filename)"; with //
    didn't work though .. it still display all results on the screen.
    Thanks again

    Author Comment

    Strange .. when I run it again it asks me save it or open it and it saves it under Sales_Report.csv ? Am I doing something wrong ??
    LVL 3

    Expert Comment

    Nothing strange. Dont worry. The following line is the line which you are taking about. In that we have specified the file name.

    Our whole code will create a .csv file and give the option to download the file to the user. Sales_report.csv is nothing but the file name to save it. Changing the filename in the following line will give you different filename while saving.

    header("Content-Disposition: attachment; filename=Sales_Report.csv");

    hope you understand.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now