Posted By

brownrl on 06/26/09

Tagged

php excel xls

Versions (?)

Who likes this?

11 people have marked this snippet as a favorite

brownrl
jseltzer
vali29
calvingilbert
jcroom
joethermal
esritter
dimispaceman
beneberle
jay3917
ringo380

PHP Export MySql Table to Xls

/ Published in: PHP

URL: http://www.goingson.be

Face it!

Whenever you make a website/app you always need to make XLS export for the simple people. I mean heaven to betsy if they have to take a csv file, or use phpMyAdmin.

Here are 3 little functions and a sample skeleton that I use from time to time when I need to export out some data to an xls file.

Place the file in a safe place or if you want a completely open to the world place... :D Then replace the XXXXX's with the appropriate data.

Give the pointy haired boss the links!

Expand | Embed | Plain Text

<?php
 
// DB TABLE Exporter
//
// How to use:
//
// Place this file in a safe place, edit the info just below here
// browse to the file, enjoy!
 
// CHANGE THIS STUFF FOR WHAT YOU NEED TO DO
 
     $dbhost  = "127.0.0.1:3306";
     $dbuser  = "XXXXX";
     $dbpass  = "XXXXX";
     $dbname  = "XXXXX";
     $dbtable = "XXXXX";
 
// END CHANGING STUFF
 
 
// first thing that we are going to do is make some functions for writing out
// and excel file. These functions do some hex writing and to be honest I got 
// them from some where else but hey it works so I am not going to question it 
// just reuse
 
 
// This one makes the beginning of the xls file
function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
    return;
}
 
// This one makes the end of the xls file
function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}
 
// this will write text in the cell you specify
function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
    return;
}
 
 
 
// make the connection an DB query
$dbc = mysql_connect( $dbhost , $dbuser , $dbpass ) or die( mysql_error() );
mysql_select_db( $dbname );
$q = "SELECT * FROM ".$dbtable."";
$qr = mysql_query( $q ) or die( mysql_error() );
 
 
// Ok now we are going to send some headers so that this 
// thing that we are going make comes out of browser
// as an xls file.
// 
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");
 
//this line is important its makes the file name
header("Content-Disposition: attachment;filename=export_".$dbtable.".xls ");
 
header("Content-Transfer-Encoding: binary ");
 
// start the file
xlsBOF();
 
// these will be used for keeping things in order.
$col = 0;
$row = 0;
 
// This tells us that we are on the first row
$first = true;
 
while( $qrow = mysql_fetch_assoc( $qr ) )
{
    // Ok we are on the first row
    // lets make some headers of sorts
    if( $first )
    {
        foreach( $qrow as $k => $v )
        {
            // take the key and make label
            // make it uppper case and replace _ with ' '
            xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
            $col++;
        }
 
        // prepare for the first real data row
        $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();
?>

Report this snippet Tweet

Comments

Subscribe to comments

Posted By: shana on October 1, 2009

Thank u for the code....But how can we increase the column width of the xls file

Posted By: brownrl on October 5, 2009

You don't the Excel has a default size. The best thing to do is select all Ctrl-A then double click the edge of the first column and all columns will spread out to the size that is needed. This code here makes the absolute most basic excel file possible. There might be some other php libs out to do more fancy stuff.

Posted By: mdemetri2 on November 26, 2009

Hi, just tried to implement this - I am getting a load of "Warning: Cannot modify header information - headers already sent by (output started at /path/testdl.php:6) in /home/sites/atkinsintelligence.com/public_html/RBS/testdl.php on line 77" through to line 87. When you open up the page testdl.php its not opening as an excel file just test in the browser.

Posted By: mdemetri2 on November 26, 2009

Posted By: brownrl on November 27, 2009

hmmmm

What is happening according to your error is that on line 6 of your php you are outputting something... in my file line 6 is a comment...

So when you get down to the part where the php is constructing and xls file and not an text/html file the web server is saying:

"Yo jerk! your already started outputting text/html we can start output binary/xls"

Check you line 6, post your code?

Posted By: bell418 on May 19, 2010

Thanks a lot for this code....it works fine :) I messed around for a while with more 'complex' codes, with no acceptable result.

good work!

Posted By: brownrl on September 17, 2010

I use this base script numerous times. Normally I add a quick "if" there to convert "date" and "timestamp" fields from integers to human readable dates. I also have used this in combination with another html form to make a universal db exporter.

This here is simply the base.

Posted By: mateinone on April 1, 2011

Thanks, Sorry this is probably a simple error, but it is not writing to a file, it is writing to my webpage in a manner as follows �� 1210049297 This is something that would be a great asset to me if I could get it to work

Posted By: mateinone on April 1, 2011

Sorry about the multiple posts, I refreshed the page and each time I just noticed it was posting the same details again

Posted By: brownrl on April 8, 2011

thats odd.

check to make absolutely sure that you are not already starting output some where else in your program. A blank line of html will tell apache that this is now text and no longer allow the binary header stuff to go.

You output looks like binary stuff, so really I believe that you are already outputting to the screen somewhere and you have error reporting turned off. Otherwise you would see an error like above: Warning: Cannot modify header information - headers already sent by

Posted By: srosca on May 21, 2011

How can I save the file to the server and not the users computer?

Posted By: brownrl on June 27, 2011

probably by outputing the data to file instead of out to the screen? So instead of "echo" add the data to a temp string, then write the big temp string to a file. fileputcontents( $temp , $somefile ).

Posted By: mdemetri2 on March 30, 2012

Hi, been using this code for a while, but have now noticed that it is limited to 255 characters in each cell. Any db table field that holds any more than 255 characters it just doesnt export that field into a cell in Excel.....is there a way to increase the limit?

Posted By: brownrl on March 30, 2012

http://stackoverflow.com/questions/7044790/problem-downloading-data-to-excel-using-php-pack-function

talks about the char max. Never needed to have more than 255 chars in a cell. Take a look.

Comment:

You need to login to post a comment.