<?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;
	}

	/**
	 * creates new sheet and selects it as current sheet
	 */
	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;
	}

	/**
	 * Gets worksheet if it exists.
	 */
	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();
	}
}

?>