<?php

/**
 * Simple excel generating from PHP5
 *
 * @package Utilities
 * @license http://www.opensource.org/licenses/mit-license.php
 * @author Oliver Schwarz <oliver.schwarz@gmail.com>
 * @version 1.0
 */

/**
 * Generating excel documents on-the-fly from PHP5
 * 
 * Uses the excel XML-specification to generate a native
 * XML document, readable/processable by excel.
 * 
 * @package Utilities
 * @subpackage Excel
 * @author Oliver Schwarz <oliver.schwarz@vaicon.de>
 * @version 1.1
 * 
 * @todo Issue #4: Internet Explorer 7 does not work well with the given header
 * @todo Add option to give out first line as header (bold text)
 * @todo Add option to give out last line as footer (bold text)
 * @todo Add option to write to file
 */
class Excel_XML {

    /**
     * Header (of document)
     * @var string
     */
    private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
    /**
     * Footer (of document)
     * @var string
     */
    private $footer = "</Workbook>";
    /**
     * Lines to output in the excel document
     * @var array
     */
    private $lines = array();
    /**
     * Used encoding
     * @var string
     */
    private $sEncoding;
    /**
     * Convert variable types
     * @var boolean
     */
    private $bConvertTypes;
    /**
     * Worksheet title
     * @var string
     */
    private $sWorksheetTitle;
    //------------------------------- added by Safaa --------------------------//
    /**
     * Worksheet2 title
     * @var string
     */
    private $sWorksheetTitle2;
    /**
     * Lines to output in the excel document sheet 2
     * @var array
     */
    private $lines2 = array();
    //------------------------------- added by Safaa --------------------------//

    /**
     * Constructor
     *
     * The constructor allows the setting of some additional
     * parameters so that the library may be configured to
     * one's needs.
     *
     * On converting types:
     * When set to true, the library tries to identify the type of
     * the variable value and set the field specification for Excel
     * accordingly. Be careful with article numbers or postcodes
     * starting with a '0' (zero)!
     *
     * @param string $sEncoding Encoding to be used (defaults to UTF-8)
     * @param boolean $bConvertTypes Convert variables to field specification
     * @param string $sWorksheetTitle Title for the worksheet
     */
    public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1', $sWorksheetTitle2 = 'Table2') {
        $this->bConvertTypes = $bConvertTypes;
        $this->setEncoding($sEncoding);
        $this->setWorksheetTitle($sWorksheetTitle);
        $this->setWorksheetTitle($sWorksheetTitle2, true);
    }

    /**
     * Set encoding
     * @param string Encoding type to set
     */
    public function setEncoding($sEncoding) {
        $this->sEncoding = $sEncoding;
    }

    /**
     * Set worksheet title
     *
     * Strips out not allowed characters and trims the
     * title to a maximum length of 31.
     *
     * @param string $title Title for worksheet
     */
    public function setWorksheetTitle($title, $second=null) {
        $title = preg_replace("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
        $title = substr($title, 0, 31);
        //------------------------------- added by Safaa --------------------------//
        if ($second != null && $second == true)
            $this->sWorksheetTitle2 = $title;
        else {
            $this->sWorksheetTitle = $title;
        }
        //------------------------------- added by Safaa --------------------------//
    }

    /**
     * Add row
     *
     * Adds a single row to the document. If set to true, self::bConvertTypes
     * checks the type of variable and returns the specific field settings
     * for the cell.
     *
     * @param array $array One-dimensional array with row content
     */
    private function addRow($array) {
        $cells = "";
        foreach ($array as $k => $v):
            $type = 'String';
            if ($this->bConvertTypes === true && is_numeric($v)):
                $type = 'Number';
            endif;
            $v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
            $cells .= "<Cell><Data ss:Type=\"$type\">" . $v . "</Data></Cell>\n";
        endforeach;
        $this->lines[] = "<Row>\n" . $cells . "</Row>\n";
    }

    /**
     * Add an array to the document
     * @param array 2-dimensional array
     * @param array 2-dimensional array
     */
    public function addArray($array) {
        foreach ($array as $k => $v)
            $this->addRow($v);
    }

    /**
     * Generate the excel file
     * @param string $filename Name of excel file to generate (...xls)
     */
    public function generateXML($filename = 'excel-export', $correctFilename=true) {
        // correct/validate filename
//                if($correctFilename)
//                    $filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);
        // deliver header (as recommended in php manual)
        header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
        header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");

        // print out document to the browser
        // need to use stripslashes for the damn ">"
        echo stripslashes(sprintf($this->header, $this->sEncoding));
        echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle . "\">\n<Table>\n";
        foreach ($this->lines as $line)
            echo $line;

        echo "</Table>\n</Worksheet>\n";
        echo $this->footer;
    }

    //------------------------------- added by Safaa --------------------------//

    /**
     * Generate the excel file with two sheets
     * @param string $filename Name of excel file to generate (...xls)
     */
    public function generateXMLWithTwoSheets($filename = 'excel-export', $correctFilename=true) {
        // correct/validate filename
//                if($correctFilename)
//                    $filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);
        // deliver header (as recommended in php manual)
        header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
        header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");

        // print out document to the browser
        // need to use stripslashes for the damn ">"
        echo stripslashes(sprintf($this->header, $this->sEncoding));
        //--------------------------------------------------
        //first sheet
        echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle . "\">\n<Table>\n";
        foreach ($this->lines as $line)
            echo $line;

        echo "</Table>\n</Worksheet>";

        //first sheet
        echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle2 . "\">\n<Table>\n";
        foreach ($this->lines2 as $line2)
            echo $line2;

        echo "</Table>\n</Worksheet>\n";
        //--------------------------------------------------
        echo $this->footer;
    }

    /**
     * Add an array to the document
     * @param array 2-dimensional array
     * @param array 2-dimensional array
     */
    public function addArrayTwoSheets($array1, $array2) {
        foreach ($array1 as $k1 => $v1)
            $this->addRow($v1);
        foreach ($array2 as $k2 => $v2)
            $this->addRowSheet2($v2);
    }

    /**
     * Add row
     *
     * Adds a single row to the document. If set to true, self::bConvertTypes
     * checks the type of variable and returns the specific field settings
     * for the cell.
     *
     * @param array $array2 One-dimensional array with row content
     */
    private function addRowSheet2($array2) {
        $cells2 = "";
        foreach ($array2 as $k2 => $v2):
            $type2 = 'String';
            if ($this->bConvertTypes === true && is_numeric($v2)):
                $type2 = 'Number';
            endif;
            $v2 = htmlentities($v2, ENT_COMPAT, $this->sEncoding);
            $cells2 .= "<Cell><Data ss:Type=\"$type2\">" . $v2 . "</Data></Cell>\n";
        endforeach;
        $this->lines2[] = "<Row>\n" . $cells2 . "</Row>\n";
    }

    //------------------------------- added by Safaa --------------------------//
}
?>