mySQL to XLS using PHP?

Question

Hi guys, how can I create a .XLS document from a mySQL table using PHP?

I have tried just about everything, with no success.

Basically, I need to take form data, and input it into a database, which I have done, and then I need to retrieve that table data and parse it into a microsoft excel file, which needs to be saved automatically onto the web server.

    <?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  = "-";
     $dbuser  = "-";
     $dbpass  = "-";
     $dbname  = "-";
     $dbtable = "-";

// END CHANGING STUFF

$cdate = date("Y-m-d"); // get current date


// 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." WHERE date ='$cdate'";
$qr = mysql_query( $q ) or die( mysql_error() );

// 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();
?>

I just can't seem to figure out how to integrate fwrite into all that to write the generated data into a .xls file, how would I go about doing that?

I need to get this working quite urgently, so any help would be greatly appreciated. Thanx guys.

Answer 1

If you have some sort of front end for your database (like phpMyAdmin or SQLyog) you can export the table (or the result of any SELECT query) to CSV and open that in Excel.

EDIT after comments: I created an XLS once. It was a bit different, but what I did was put this at the top of my PHP (before any output was generated):

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=\"name.xls\"");

And in the rest of the script I just echoed out a table (table, tr, td... etc) The execution of this script would give the user a download. I think there are some different options for the Content-disposition attribute (maybe there is one that lets the script save the file).

Answer 2

If I get it right, the script you have posted is working right and creates a correct xls file, and you want just to save the output, look at http://php.net/manual/en/book.outcontrol.php. With ob_get_clean(), you can get the created xls file and write it somewhere to the server.

Maybe, you could consider also other options, such as saving your data to some other format Excel can read (.csv, probably it can read also some html/xml table).

Answer 3

I tried it with ob_get_clean(), but it didn't work, here is the code I've been working on now:

<?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  = "-";
     $dbuser  = "-";
     $dbpass  = "-";
     $dbname  = "-";
     $dbtable = "-";

// END CHANGING STUFF

$cdate = date("Y-m-d"); // get current date


// 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() {
    $output = pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
    return;
}

// This one makes the end of the xls file
function xlsEOF() {
    $output .= pack("ss", 0x0A, 0x00);
    return;
}

// this will write text in the cell you specify
function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    $output .= pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    $file = fopen("exported.xls","w");
    fwrite($file, "$output");
    fclose($file);
    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." WHERE date ='$cdate'";
$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();



?>

I don't even know if any of this makes sense, but I added fwrite into the xlsBOF, xlsEOF and xlsWriteLabel functions to try and write the data to the exported.xls file, could it work like that?

Answer 4

I've used the PEAR Spreadsheet_Excel_Writer a lot in my projects and it works well. It generates Excel 5.0-level files, though, so it might be not enough for your purposes if you need anyhing more advanced than that, but it will generate a native .xls, and not just a .csv masquerading as .xls.

Answer 5

Everything is working, here is where the answer is located. :-)

http://stackoverflow.com/questions/2477414/saving-a-xls-file-with-fwrite