Saving a .xls file with fwrite

Question

hi guys, I have to create a script that takes a mySQL table, and exports it into .XLS format, and then saves that file into a specified folder on the web host.

I got it working, but now I can't seem to get it to automatically save the file to the location without prompting the user.

It needs to run every day at a specified time, so it can save the previous days leads into a .XLS file on the web host.

Here is the code:

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


// 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 tried using, fwrite to accomplish this, but it didn't seem to go very well, I removed the header information too, but nothing worked.

Here is the original code, as I found it, any help would be greatly appreciated. :-)

Thanx in advance. :-)

Answer 1

Is it xls or xsl file format ? Got me confused.

• I guess it's XLS :

First : do you need to set up font styles, use multiple tabs, use formulas ? If so try to go for an excel library such as phpwriteexcel.

Otherwise, a simple csv file is enough (comma separated value, created very easily from arrays, read perfectly with excel and other spreadsheet softwares).

Then, to save it automatically without prompting : go for a planified task / cron task, calling your script.

Answer 2

First, since you are saving this to disk via cron you should remove all of the header() calls as you suspected. In order to rewrite as little of your code as possible, I would recommend using output buffering (http://www.php.net/manual/en/ref.outcontrol.php). To accomplish this, place a call to ob_start() before your file output begins:

ob_start();
// start the file
xlsBOF();

And after your output ends, close the output buffer, capture its contents and write them to a file:

xlsEOF();
// $filename should be set to some writeable location
file_put_contents($filename, ob_get_clean());

Answer 3

Here is the final code, it works like a charm.

<?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
         $cdate = date("Y-m-d");
         $dbhost  = "-";
         $dbuser  = "-";
         $dbpass  = "-";
         $dbname  = "-";
         $dbtable = "-";
         $filename = "exported_on_$cdate.xls";

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

    //start the object
     ob_start();

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

    //write the contents of the object to a file
    file_put_contents($filename, ob_get_clean());

    ?>

Thanx for all the help guys!!!