KjwSimpleXmlWorkbook.php

<?php // vim:ts=4:sw=4:noet
/*====================================================================== 
Copyright (C) 2006-2009, Walter Doekes
This file is part of KjwLib-php45.
 
KjwLib-php45 is free software: you can redistribute it and/or modify 
it under the terms of the GNU General Public License as published by 
the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version. 

KjwLib-php45 is distributed in the hope that it will be useful, 
but WITHOUT ANY WARRANTY; without even the implied warranty of 
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
GNU General Public License for more details. 

You should have received a copy of the GNU General Public License 
along with KjwLib-php45.  If not, see <http://www.gnu.org/licenses/>. 
======================================================================*/

/***
Usage example:

require_once('/var/www/default/libs/kjwlib/KjwLib.php');
require_once('/var/www/default/libs/kjwlib/KjwSimpleXmlWorkbook.php');
$sql = kjwsql_from_url('mssql://sql.server.tlb/some_data');
$excel = new KjwSimpleXmlWorkbook();
$res = $sql->execute("SELECT TOP 10 * FROM data_tbl ORDER BY data_id");
$excel->newWorksheetFromResultSet($res, 'my data');
$excel->offerAsDownload();
***/

require_once(dirname(__FILE__) . '/KjwObject.php');

class KjwSimpleXmlWorkbook extends KjwObject {
        var $_null = null;

        function KjwSimpleXmlWorkbook() {
                $this->_initEmpty();
        }

        function _initEmpty() {
                $this->_encoding = 'UTF-8';
                $this->_author = 'KjwSimpleXmlWorkbook.php';
                $this->_company = 'All4students / KjwLib';
                $this->_data = array();
                $this->_createDate = strftime('%Y-%m%dT%H:%M:%SZ');
                $this->_currentWorksheet = null;
        }

        function &newWorkbook() {
                $this->_initEmpty();
                return $this;
        }

        function &newWorksheet($sheetName) {
                if (($sheet = $this->_getWorksheet($sheetName)) !== null)
                        return false;
                array_push($this->_data, array('_TAG' => 'Worksheet', 'Name' => $sheetName, '_DATA' => array()));
                $this->_currentWorksheet = &$this->_getWorksheet($sheetName);
                return $this;
        }

        function &newWorksheetFromResultSet(&$result, $sheetName) {
                $this->newWorksheet($sheetName);
                $at = $result->at();
                $result->seekAbs(0);
                while (($row = $result->getNext())) {
                    if ($result->at() == 1)
                    $this->addHeaderRow(array_keys($row));
                    $this->addRow($row);
                }
                $result->seekAbs($at);
                return $this;
        }

        function &_getWorksheet($sheetName) {
                foreach ($this->_data as $key => $value) {
                        if ($value['Name'] == $sheetName)
                                return $this->_data[$key];
                }
                return $this->_null;
        }

        function setAuthor($author) {
                $this->_author = $author;
        }

        function setCompany($company) {
                $this->_company = $company;
        }

        function _toCellData($value, $styleId = null) {
                assert(!is_object($value) && !is_array($value));
                $valueType = 'String';
                if (is_double($value) || is_float($value) || is_integer($value)) {
                        $valueType = 'Number';
                } elseif (is_bool($value)) {
                        $valueType = 'Boolean';
                        $value = (int)$value;
                } elseif (preg_match('/^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})$/', $value, $m)) {
                        if ($styleId === null)
                                $styleId = 'Date';
                        $valueType = 'DateTime';
                        $value = sprintf("%04d-%02d-%02dT00:00:00.000", $m[1], $m[2], $m[3]);
                } elseif (preg_match('/^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) ([0-9]{1,2}):([0-9]{2})/', $value, $m)) {
                        if ($styleId === null)
                                $styleId = 'DateTime';
                        $valueType = 'DateTime';
                        $value = sprintf("%04d-%02d-%02dT%02d:%02d:00.000", $m[1], $m[2], $m[3], $m[4], $m[5]);
                } elseif (preg_match('/^([0-9]{1,2}):([0-9]{2})/', $value, $m)) {
                        if ($styleId === null)
                                $styleId = 'Time';
                        $valueType = 'DateTime';
                        $value = sprintf("1899-12-31T%02d:%02d:00.000", $m[1], $m[2]);
                }

                $cell = array('_TAG' => 'Cell', '_DATA' => array(
                                '_TAG' => 'Data', 'ss:Type' => $valueType, '_DATA' => $value));
                if ($styleId !== null)
                        $cell['ss:StyleID'] = $styleId;
                return $cell;
        }

        function addRow($row) {
                $newCells = array();
                foreach ($row as $value)
                        array_push($newCells, $this->_toCellData($value));
                array_push($this->_currentWorksheet['_DATA'], array('_TAG' => 'Row', '_DATA' => $newCells));
        }

        function addHeaderRow($row) {
                $newCells = array();
                foreach ($row as $value)
                        array_push($newCells, $this->_toCellData($value, 'Head'));
                array_push($this->_currentWorksheet['_DATA'], array('_TAG' => 'Row', '_DATA' => $newCells));
        }

        function _countRowColumnStats($worksheet) {
                $rowCount = 0;
                $columnCount = 0;
                $columnSizes = array();
                foreach ($worksheet['_DATA'] as $row) {
                        ++$rowCount;
                        $columnCount = max($columnCount, sizeof($row['_DATA']));
                        for ($i = 0; $i < sizeof($row['_DATA']); ++$i) {
                                $columnSizes[$i] = max((int)@$columnSizes[$i], 2);
                                if (@$row['_DATA'][$i]['ss:StyleID'] != 'Head') {
                                        if (isset($row['_DATA'][$i]['_DATA'])
                                                        && is_array($row['_DATA'][$i]['_DATA'])
                                                        && $row['_DATA'][$i]['_DATA']['_TAG'] == 'Data') {
                                                $columnSizes[$i] = max($columnSizes[$i],
                                                                strlen(@$row['_DATA'][$i]['_DATA']['_DATA']));
                                        }
                                }
                        }
                }
                return array($rowCount, $columnCount, $columnSizes);
        }

        function _xmlToString($data, $indent, $noIndent = false) {
                assert(!is_string($data));
                $output = '';

                if (is_array($data) && isset($data['_TAG'])) {
                        $output = ($noIndent ? '' : str_repeat(' ', $indent)) . '<' . $data['_TAG'];
                        foreach ($data as $key => $value) {
                                if ($key[0] != '_')
                                        $output .= " $key=\"" . htmlentities($value, ENT_COMPAT, $this->_encoding) . '"';;
                        }
                        if (!isset($data['_DATA'])) {
                                $output .= "/>" . ($noIndent ? '' : "\n");
                                return $output;
                        }
                        // This takes care of the is_string-case.
                        if (!is_array($data['_DATA'])) {
                                $output .= '>' . htmlentities($data['_DATA'], ENT_COMPAT, $this->_encoding) . "</$data[_TAG]>"
                                                . ($noIndent ? '' : "\n");
                                return $output;
                        }

                        if ($data['_TAG'] == 'Cell') {
                                $output .= ">" . $this->_xmlToString($data['_DATA'], $indent + 1, true) . "</$data[_TAG]>\n";
                        } else {
                                $output .= ">\n"
                                        . $this->_xmlToString($data['_DATA'], $indent + 1)
                                        . str_repeat(' ', $indent) . "</$data[_TAG]>" . ($noIndent ? '' : "\n");
                        }

                } elseif (is_array($data)) {
                        foreach ($data as $value) {
                                $output .= $this->_xmlToString($value, $indent);
                        }

                } else {
                        assert(0);
                }
                return $output;                         
        }

        function toString() {
        $author = htmlentities($this->_author, ENT_COMPAT, 'UTF-8');
        $company = htmlentities($this->_company, ENT_COMPAT, 'UTF-8');
        $createDate = htmlentities($this->_createDate);

        $output = <<<__EOF__
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 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">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>$author</Author>
  <LastAuthor>$author</LastAuthor>
  <Created>$createDate</Created>
  <Company>$company</Company>
  <Version>11.5606</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>12465</WindowHeight>
  <WindowWidth>13020</WindowWidth>
  <WindowTopX>600</WindowTopX>
  <WindowTopY>240</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="Head">
    <Font x:Family="Swiss" ss:Bold="1"/>
  </Style>
  <Style ss:ID="Time">
    <NumberFormat ss:Format="Short Time"/>
  </Style>
  <Style ss:ID="Date">
    <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="DateTime">
    <NumberFormat ss:Format="General Date"/>
  </Style>
 </Styles>
__EOF__;
        foreach ($this->_data as $worksheet) {
                        $worksheetName = htmlentities($worksheet['Name'], ENT_COMPAT, $this->_encoding);
                        list($rowCount, $columnCount, $columnSizes) = $this->_countRowColumnStats($worksheet);
                        $output .= <<<__EOF__

 <Worksheet ss:Name="$worksheetName">
  <Table ss:ExpandedColumnCount="$columnCount" ss:ExpandedRowCount="$rowCount" x:FullColumns="1" x:FullRows="1">

__EOF__;
                        for ($i = 0; $i < sizeof($columnSizes); ++$i) {
                                $output .= "   <Column ss:Index=\"" . ($i + 1) . "\" ss:AutoFitWidth=\"0\""
                                                . " ss:Width=\"" . min($columnSizes[$i] * 7, 200) . "\"/>\n";
                        }
                        foreach ($worksheet['_DATA'] as $row) {
                                $output .= $this->_xmlToString($row, 3);
                        }

                        $output .= <<<__EOF__
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
__EOF__;
                }
                $output .= <<<__EOF__

</Workbook>
__EOF__;

                return $output;
        }

        function offerAsDownload($filename = 'data.xls') {
                header("Content-Disposition: attachment; filename=\"" . urlencode($filename) . "\"");
                header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
                echo $this->toString();
        }
}

?>

---