Home / Snippets / excel_spreadsheet

This section is new and undergoing snippet addition, as of 13/6/11

Excel Spreadsheet With PHP

Use the following class to create an Excel spreadsheet on the fly with PHP:

<?php
/*
 * ConvertToXLS - Create an XLS Spreadsheet on the fly with PHP
 *
 * @author Conn Warwicker
 * @site http://www.cmrwarwicker.com
 */
class ConvertToXLS
{

    public $data; // The data to be put into the file
    public $rows; // Number of rows being used
    public $cols; // Number of columns in that row being used
    public $tempfile; // Name of file to temporarily create
    public $tempdir; // Directory to place file in temporarily

    /*
     * Construct the object and assign values to variables
     */
    public function __construct()
    {
        $this->data = pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); // Start XLS File
        $this->rows = 0; // Set rows to 0
        $this->cols = 0; // Set cols to 0
        $this->tempfile = "temp.xls";
        $this->tempdir = "/"; // path to directory you want to use
    }

    /*
     * Add a column of data to the file
     * @param string $val The value to enter into the column
     */
    public function addColumn($val)
    {
        if(ctype_digit($val)) // If it's a number
        {
            $this->data .= pack("sssss", 0x203, 14, $this->rows, $this->cols, 0x0) . pack("d", $val);
        }
        else // If it's not a number, must be text
        {
            $this->data .= pack("ssssss", 0x204, 8 + strlen($val), $this->rows, $this->cols, 0x0, strlen($val)) . $val;
        }
        $this->cols++; // Increment columns, so next addition goes into the next column, not this one again
    }

    /*
     * Add a row to the file
     */
    public function addRow()
    {
        $this->rows++; // Increment rows
        $this->cols = 0; // Reset columns
    }

    /*
     * Generate the spreadsheet file and prompt to download
     */
    public function generateXLS()
    {
        $this->data .= pack("ss", 0x0A, 0x00); // End of file
        file_put_contents($this->tempdir . $this->tempfile, $this->data); // Put content into the "temp.xls" file

        $store = $this->tempdir . $this->tempfile; // Full path to temp file

        // Set header to download XLS file
        header('Cache-Control: public, must-revalidate');
        header('Pragma: hack'); 
        header('Content-Type: application/octet-stream');
        header('Content-Length: '.(string)(filesize($store)));
        header('Content-Disposition: attachment; filename="'.$this->tempfile.'"');
        header('Content-Transfer-Encoding: binary');

        if(file_exists($store))
        {
        readfile($store); // Read the file, if it has been created successfully
        }

        unlink($store); // Delete the file again
    }

}
?>

To use the class, first create an instance of the class. Then add columns of data. When you want to move down to a new row, add a new row. And once you have finished adding all the data you want, call the generateXLS() method.

Have a play with the example below:

<?php
require_once('ConvertToXLS.inc.php'); // If this is what you call the class file

$myXLS = new ConvertToXLS();
$myXLS->addColumn("Word");
$myXLS->addColumn("Number");
$myXLS->addRow();
$myXLS->addColumn("Bucket");
$myXLS->addColumn("130");
$myXLS->addRow();
$myXLS->addColumn("Crocodile");
$myXLS->addColumn("388");
$myXLS->generateXLS();
?>

Comments

No Comments Have Been Made