<?php
	require_once 'Spreadsheet/Excel/Writer.php';

function Source2Excel($filename, $contents, $blnsend=true){
	$workbook = new Spreadsheet_Excel_Writer();
	if($blnsend) $workbook->send($filename);
//	$workbook->setTempDir('/daten/daten/test/tmp');

	// Alle Sheets anlegen
	while ($tagsheet=new Tag('sheet', $contents) ){
		$worksheet =& $workbook->addWorksheet($tagsheet->TagPara['name']);

		// Alle Zellen beschreiben
		while ($tag=new Tag('th', $tagsheet->Text)){
			createdata($workbook,$worksheet,$tag);
			#$tagsheet->Text=str_replace($tag->CONTENT, "",$tagsheet->Text);
			$tagsheet->Text=delTag($tagsheet->Text,$tag);
		}

		while ($tag=new Tag('td', $tagsheet->Text,true)){
			createdata($workbook,$worksheet,$tag);
			$tagsheet->Text=delTag($tagsheet->Text,$tag);
		}
		while ($tag=new Tag('img', $tagsheet->Text)){
			//insertBitmap (integer $row, integer $col, string $bitmap [, integer $x=0 [, integer $y=0 [, integer $scale_x=1 [, integer $scale_y=1]]]])
			$worksheet->insertBitmap($tag->TagPara['row'],$tag->TagPara['col'],$tag->Text);
			$tagsheet->Text=delTag($tagsheet->Text,$tag);
			#$tagsheet->Text=str_replace($tag->CONTENT, "",$tagsheet->Text);
		}
		$contents=str_replace($tagsheet->CONTENT, "", $contents);
	}

	// workbook senden
	$workbook->close();
//echo $contents;
}

// entferne erstes Vorkommen
function delTag($area,$tag){
	$pos = strpos($area, $tag->CONTENT);
	return substr_replace($area, '', $pos, strlen($tag->CONTENT));
}

# Align Bold Bottom Top Left Right Border
# BorderColor BottomColor TopColor RightColor LeftColor FgColor BgColor Color
# Pattern Underline TextRotation Size at Script
function createdata(&$workbook,&$worksheet,$tag){
//	$worksheet->write($tag->TagPara['col'], $tag->TagPara['row'], $tag->Text);
	$format=array();
	$h=$w=0;
	foreach(array_keys($tag->TagPara) as $par){
		$mx=$my=0;
		switch($par){
			case "width":
				$w=$tag->TagPara['width'];
				break;
			case "height":
				$h=$tag->TagPara['height'];
				break;
			case "col":
				$y=$tag->TagPara['row'];
				break;
			case "row":
				$x=$tag->TagPara['col'];
				break;
			case "cell":
				preg_match("/^([A-Za-z]+)(\d+)\$/", $tag->TagPara['cell'], $matches);
				if(sizeof($matches)){
					$n=0;
					$erg=0;
					for($i=strlen($matches[1])-1;$i>=0;$i--){
						$wert= ord(strtoupper(substr($matches[1],$i,1)))-ord('A')."<br>\n";
						$erg+=$n*26+$wert;
						$n++;
					}
					$x=$erg;
					$y=$matches[2]-1;
				}else{
					$temp=explode(":",$tag->TagPara['cell']);
					$x=$temp[0];
					$y=$temp[1];
				}
				break;
			case "href":
				$url=$tag->TagPara['href'];
				break;
			case "type":
				break;
			case "colspan":
				$mx=$tag->TagPara['colspan'];
				break;
			case "rowspan":
				$my=$tag->TagPara['rowspan'];
				break;
			default:
				$format[$par]=$tag->TagPara[$par];
//				echo "TM $par = ".$format[$par]."\n";

		}
	}
	if($w) $worksheet->setColumn($x,$x,$w);
	if($h){
		$worksheet->setRow($y,$h);
//		echo "$y : $h, ";
	}
	if($my+$mx) $worksheet->mergeCells($y,$x,$y+$my,$x+$mx);

	if($format) $f=& $workbook->addFormat($format);
	unset($format);
	
//	$f =& $workbook->addFormat(array('Size' => 10, 'Align' => 'center', 'Color' => 'white', 'Pattern' => 1, 'FgColor' => 'magenta'));
//	$f=& $workbook->addFormat(array('Size'=>10,'Align'=>'center','Color'=>'yellow', 'FgColor'=>'magenta'));
	switch($tag->TagPara["type"]){
		case "Number":
			$worksheet->writeNumber($y, $x, $tag->Text, $f);
			break;
		case "String":
			$worksheet->writeString($y, $x, $tag->Text, $f);
			break;
		case "Note":
			$worksheet->writeNote($y, $x, $tag->Text, $f);
			break;
		case "Formula":
			$worksheet->writeFormula($y, $x, $tag->Text, $f);
			break;
		case "Url":
			$worksheet->writeUrl($y, $x, $url, $tag->Text, $f);
			break;
		default:
			$worksheet->write($y, $x, $tag->Text, $f);
	}

//	$worksheet->setHeader("HALLO TEST",1.5);
//	$worksheet->setFooter("HALLO TEST",1.5);
}
?>