<?php
function calcImagePosition($objWorksheet, $col_array, $imgWidth, $exceltopixel=8, $mergedCell=false){
	if( is_array($col_array) ){
		foreach($col_array as $col=>$colwidth){
			$pagewidth += $objWorksheet->getColumnDimension($col)->getWidth();
			//echo " ,".$objWorksheet->getColumnDimension($col)->getWidth();
		}
	}

	$pagewidth_pixel = $pagewidth * $exceltopixel;
	$margin = (int)(($pagewidth_pixel - $imgWidth)/2);
	if($margin>0){
		//var_dump($coord, $offsetX)
		
		//DO NOT USE mergedCell option
		//Excel does not support image in merged cell coordinate system
		if($mergedCell){
			$col_array_key=key($col_array);
			array($col_array_key[0], $margin);	
		}
		else{
			foreach($col_array as $col=>$colwidth){
				$colpixel = $objWorksheet->getColumnDimension($col)->getWidth() * $exceltopixel;
				if($margin>=$colpixel){
					$margin -= $colpixel;	
				}
				else{
					$offsetX = $margin;
					$coord = $col;
					break;
				}
				$pagewidth += $objWorksheet->getColumnDimension($col)->getWidth();
			}
			//var_dump($coord, $offsetX); exit;
			return array($coord, $offsetX);
		}
	}
	$col_array_key=key($col_array);
//	var_dump( array($col_array_key[0], 0)); exit;		
	return array($col_array_key[0], 0);
}

function getNumRowMargin($h, $heightPerRow=17){
	$row = (int)($h/$heightPerRow);
	$marginY = $h - ($row*$heightPerRow);
	return array($row, $marginY);	
}

// for reference only 
// magic number for formatLongStrCell
function chrWidthOfFont($lang, $size){	
	switch(strtoupper($lang)){
	case 'EN':
		switch($size){
		case 7://tbc
			return 0.50;
		case 8:
			return 0.60;
		case 9://tbc
			return 0.65;
		case 10:
			return 0.75;
		case 11://tbc
			return 0.90;
		case 12://tbc
			return 1.05;
		case 13:
			return 1.25;
		default:
			echo "Font size $size is not defined"; exit;
		}	
	case 'SC':
	case 'TC':
		switch($size){
		case 7://tbc
			return 1.4;
		case 8:
			return 1.5;
		case 9://tbc
			return 1.6;
		case 10:
			return 1.7;
		case 11://tbc
			return 1.85;
		case 12://tbc
			return 2.0;
		case 13:
			return 2.2;
		default:
			echo "Font size $size is not defined"; exit;
		}
	
	default:
		echo "Language type $lang is not defined"; exit;	
	}
}

//test to see the result string can be formatted to 1 line by changing font size
//return: 
//int - the best font size for display in 1 line
//false - cannout display in 1 line
function formatLongStrCellFont($objWorksheet, $fixwidth, $col_array, $str, $lang, $maxfont=10, $minfont=7){
	
	for($curfont=$maxfont; $curfont>=$minfont; $curfont--){
		$chrwid = chrWidthOfFont($lang, $curfont);
		$test = formatLongStrCell($objWorksheet, $fixwidth, $col_array, $str, $chrwid);
		if(sizeof($test)<=1){
			return $curfont;
		}		
	}
	return false;
}

function formatLongStrCell($objWorksheet, $fixwidth, $col_array, $str, $chrWidth=1){
	$colwidth=0;
	if(is_array($col_array)){
		foreach($col_array as $col=>$val){
			$colwidth += $objWorksheet->getColumnDimension($col)->getWidth();
			//echo " ,".$objWorksheet->getColumnDimension($col)->getWidth();
		}
	}else{
		var_dump($col_array);
		exit('Invalid column array');	
	}
	//var_dump($colwidth); exit;
	$chrPerLine = (int)($colwidth/$chrWidth);
	//var_dump('div', $chrPerLine,$colwidth,$chrWidth);
	if($fixwidth){
		$newtext = utf8_fixwordwrap($str, $chrPerLine, "^br/^", true);
	}else{
		$newtext = utf8_wordwrap($str, $chrPerLine, "^br/^", true);
	}
	//preg_match("/\p{Han}+/u", $utf8_str); utf char check
	$formattedStr_a = explode('^br/^', $newtext);
	foreach($formattedStr_a as $k=>$str){
		if(trim($str)==''){
			unset($formattedStr_a[$k]);	
		}
	}
	return $formattedStr_a;
}

function utf8_wordwrap($string, $width=75, $break="\n", $cut=false){
	if($cut) {
		// Match anything 1 to $width chars long followed by whitespace or EOS,
		// otherwise match anything $width chars long
		$search = '/(.{1,'.$width.'})(?:\s|$)|(.{'.$width.'})/uS';
		$replace = '$1$2'.$break;
	} else {
		// Anchor the beginning of the pattern with a lookahead
		// to avoid crazy backtracking when words are longer than $width
		$pattern = '/(?=\s)(.{1,'.$width.'})(?:\s|$)/uS';
		$replace = '$1'.$break;
	}
	return preg_replace($search, $replace, $string);
}

//this function may create empty rows at the end of the string
function utf8_fixwordwrap($string, $width=75, $break="\n", $cut=false){
	$strlen = mb_strlen($string);
	if($strlen<=0){
		return;	
	}
	$newStr = '';
	for($i=0; $i*$width<=mb_strlen($string) && $strlen; $i++){
		//var_dump($i*$width, mb_strlen($string));
		$newStr .= mb_substr($string, $i*$width, $width, 'utf8').$break;
	}
	//echo "$newStr"; exit;
	return $newStr;
}

function print_formatted_date($objWorksheet, $rpt_date, $cellname, $font='Times New Roman'){
	$objRichText = new PHPExcel_RichText();
	$s0 = $objRichText->createTextRun(t($rpt_date->format('j')));
	$s0->getFont()->setName($font);
	
	$ss = $objRichText->createTextRun(t($rpt_date->format('S')));
	$ss->getFont()->setSuperScript(true);
	$ss->getFont()->setName($font);
	
	$s1 = $objRichText->createTextRun(t($rpt_date->format(' F Y')));	
	$s1->getFont()->setName($font);
	
	$objWorksheet->getCell($cellname)->setValue($objRichText);
	//$objWorksheet->getStyle($cellname)->getFont()->setName('Times New Roman');	
}

function doubleFormatCell($objWorksheet, $cellname, $str1, $str2, $bold=false, $fontsize=false, $strFont1='標楷體', $strFont2='Times New Roman'){
	$objRichText = new PHPExcel_RichText();
	$s0 = $objRichText->createTextRun($str1);
	$s0->getFont()->setName($strFont1);
	$s0->getFont()->setBold($bold);
	
	$s1 = $objRichText->createTextRun($str2);
	$s1->getFont()->setName($strFont2);	
	$s1->getFont()->setBold($bold);
	
	$objWorksheet->getCell($cellname)->setValue($objRichText);
}

function writeNVP($objWorksheet, $Startcol, &$row, $n1, $n2, $n3, $skiprow=0, $fontsize=false){
	$colname = array('A','B','C','D','E','F','G','H','I','J',
						'K','L','M','N','O','P','Q','R','S','T',
						'U','V','W','X','Y','Z');
	if($n3){
		//var_dump($Startcol+1, $n3);
		$objWorksheet->setCellValueByColumnAndRow($Startcol+0, $row, t($n1) );
		$objWorksheet->setCellValueByColumnAndRow($Startcol+1, $row, t($n2) );
		$objWorksheet->setCellValueByColumnAndRow($Startcol+2, $row, t($n3) );
		if($fontsize){
			//var_dump($row, $Startcol, ($Startcol+0), ($Startcol+2), $sCol, $eCol); exit;
			$sCol = $colname[($Startcol+0)];
			
			$eCol = $colname[($Startcol+2)];
			
			$objWorksheet->getStyle($sCol.$row.":".$eCol.$row)->getFont()->setSize($fontsize);	
		}
		for($i=0; $i<$skiprow+1; $i++){
			$row++;
		}
	}
}

function hasChinese($utf8_str){
	return preg_match("/\p{Han}+/u", $utf8_str);
}

function t($str){
	return html_entity_decode($str, ENT_QUOTES);
}

function str2br($str, $searchStr='%N%', $replaceStr='<br/>'){
	return str_replace($searchStr, $replaceStr, $str);
}

$boldfont = array("font" => array( "bold" => true));