Location: PHPKode > projects > Chalange > com_chalange/helpers/excel_writer.php
<?php
/*
 * @package Joomla 1.5
 * @copyright Copyright (C) 2005 Open Source Matters. All rights reserved.
 * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.php
 *
 * @component SQL 2 Excel Component
 * @copyright Copyright (C) Joomla-R-Us, joomla-r-us.com
 * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL
 */

// no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );

require_once ( JPATH_SITE.DS.'components'.DS.'com_chalange'.DS.'helpers'.DS.'parms.php');

class writeExcel
{
	//Crea un excell y lo rellena con los datos de los corredores inscritos a una carrera dada
   function createRegisteredSheet($filename, $registered){
    	require_once(JPATH_SITE.DS.'components'.DS.'com_chalange'.DS.'helpers'.DS.'PEAR'.DS.'Writer_joomlarus.php');
      	writeExcel::ExcelHeader($filename);
    	$workbook = new Spreadsheet_Excel_Writer_joomlarus();
    	$workbook->setVersion(8);
   		$worksheet1 =& $workbook->addWorksheet(JText::_('REGISTERED SHEET'));
   		$worksheet1->setInputEncoding('utf-8');

   		//ancho de las columnas
   		$worksheet1->setColumn(0, 0, 10);
   		$worksheet1->setColumn(1, 1, 35);
   		$worksheet1->setColumn(2, 2, 15);
   		$worksheet1->setColumn(3, 3, 15);
   		$worksheet1->setColumn(4, 4, 25);
   		$worksheet1->setColumn(5, 5, 25);
   		$worksheet1->setColumn(6, 6, 15);
   		//cabecera de la tabla
   		$format_header=&  writeExcel::addFormat($workbook, 'Arial',12,'bold','black','none');
   		writeExcel::writeCell($worksheet1, 0, 0,JText::_('dorsal'),$format_header); 
   		writeExcel::writeCell($worksheet1, 0, 1,JText::_('Names'),$format_header); 
   		writeExcel::writeCell($worksheet1, 0, 2,JText::_('cod_uci'),$format_header); 
   		writeExcel::writeCell($worksheet1, 0, 3,JText::_('numero licencia'),$format_header); 
   		writeExcel::writeCell($worksheet1, 0, 4,JText::_('team'),$format_header); 
   		writeExcel::writeCell($worksheet1, 0, 5,JText::_('sponsor'),$format_header); 
   		writeExcel::writeCell($worksheet1, 0, 6,JText::_('category'),$format_header); 
   		$n=count($registered);
   		//rellenemos la tabla con los campos pasados
		for($i=0; $i<$n; $i++){
			$row =& $registered[$i];
   			writeExcel::writeCell($worksheet1, $i+1, 0, $row->dorsal);
   			writeExcel::writeCell($worksheet1, $i+1, 1, $row->name);
   			writeExcel::writeCell($worksheet1, $i+1, 2, $row->codUCI);
   			writeExcel::writeCell($worksheet1, $i+1, 3, $row->nroLicence);
   			writeExcel::writeCell($worksheet1, $i+1, 4, $row->club);
   			writeExcel::writeCell($worksheet1, $i+1, 5, $row->sponsor);
   			writeExcel::writeCell($worksheet1, $i+1, 6, $row->category);
		}
   		
   		//writeExcel::writeCell($worksheet1, 0, 0,   JText::_( 'Workbook does not contain any valid worksheets with data.') );
   		//writeExcel::writeCell($worksheet1, 1, 0,   JText::_( 'Turn on SQL Error Messages in SQL 2 Excel Component to debug the problem.') );      	
      	$workbook->close();		
   }
	

	
   function writeFile($wbParms, $wsParms, $cmpParms, $substParms)
   {
   
   	// Get Excel Writer
   	$writerVersion = writeExcel::get_writer($wbParms, $cmpParms);
   	
	switch ($writerVersion) {
    	case '2000_utf8':
    		require_once(JPATH_SITE.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'PEAR'.DS.'Writer_joomlarus.php');
        	break;
    	case '2000_limited':
    	default:
    		require_once(JPATH_SITE.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'Writer'.DS.'Worksheet.php');
       		require_once(JPATH_SITE.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'Writer'.DS.'Workbook.php');
	}   
	
   
   	$db	= & JFactory::getDBO();  	
	$wbParms = $wbParms[0];
	$extraParms = array();
	$extraParms['SQL2EXCEL_WB_LINK'] = $wbParms->link_title;
	$extraParms['SQL2EXCEL_WB_DLCNT'] = $wbParms->count + 1;

      	// SheetNames unique?
      	writeExcel::checkSheetNames($wsParms, $cmpParms, $substParms);

      	// Filename
      	$fileName = trim($wbParms->filename);
      	$fileName = writeExcel::replace_vars($fileName, $substParms, $extraParms, $cmpParms ); 
      	$fileName = trim($fileName);
      	if ( $fileName == '' || $fileName == null ) {
      		$fileName = 'download.xls';
      	}
	// Check extension
	$path_info = pathinfo($fileName);
	if ( !isset($path_info['extension']) || 
	     ( isset($path_info['extension']) && strtolower($path_info['extension']) != 'xls' ) ) {
		$fileName .= '.xls';
	}	 
      	$extraParms['SQL2EXCEL_WB_FN'] = $fileName;
      	

      	// Write Header
      	writeExcel::ExcelHeader($fileName);
      	
      	
      	// Create a workbook
      	if ( $writerVersion == '2000_utf8' ) {
		$workbook = new Spreadsheet_Excel_Writer_joomlarus();
	
		// Set Workbook Version
		$workbook->setVersion(8);
	} else {
	      	$workbook = new Workbook("-");
	}
	

	$i=1;
	$nrWorksheets=0;
	$marginScaleFactor = (real) Sql2excelParms::get($cmpParms,'marginscalefactor', 1);
	$removeCR = Sql2excelParms::get($cmpParms,'removereturnxls', 0);
	
	foreach ( $wsParms as $ws ) {

		$sheetName = $ws->sheetname;
		$sheetName = writeExcel::replace_vars($sheetName, $substParms,0,$cmpParms);
		$sheetName = substr($sheetName,0,40);
		if ( trim($sheetName) == '' ) {
			$wsIDnr = $nrWorksheets + 1;
			$sheetName = 'Sheet ' . $wsIDnr;
		}
		$extraParms['SQL2EXCEL_WS_SN'] = $sheetName;
		$extraParms['SQL2EXCEL_WS_DLCNT'] = $ws->count + 1;

		$query = $ws->query;
		$extraParms['SQL2EXCEL_WS_SQL_RAW'] = $query;
		
		// Substitute variables in SQL query?
		if ( Sql2excelParms::get($cmpParms,'subst_sql', '1') ) {
			$query = writeExcel::replace_vars($query, $substParms,0,$cmpParms);
		}
		$extraParms['SQL2EXCEL_WS_SQL'] = $query;

		$formulas = explode(',', $ws->formulas );
		
		if ( trim($query) != '' ) {
			// Run query!
			$rows = writeExcel::getResults($db, $query, $cmpParms);


			$extraParms['SQL2EXCEL_WS_ROWS'] = count($rows);
		} else {
			$rows = '';
			$extraParms['SQL2EXCEL_WS_ROWS'] = 0;
		}

		// Create a worksheet
		$worksheet1 =& $workbook->addWorksheet(substr($sheetName,0,30));

		$nrWorksheets++;

		// Set encoding
		if ( $writerVersion == '2000_utf8' ) {
			$worksheet1->setInputEncoding('utf-8');
		}
		
		// Zoom level
		if ( $ws->zoom != '' && $ws->zoom != 100 ) {
			$worksheet1->setZoom($ws->zoom);
		}		

		// Set Margins, if defined
		$print_parms = $ws->print_parms;
		$print_parms = explode(',',$print_parms);
		if ( isset($print_parms[0]) && $print_parms[0] != '' ) {
			$worksheet1->setMarginLeft($print_parms[0]*$marginScaleFactor);
		}
		if ( isset($print_parms[1]) && $print_parms[1] != '' ) {
			$worksheet1->setMarginRight($print_parms[1]*$marginScaleFactor);
		}			
		if ( isset($print_parms[2]) && $print_parms[2] != '' ) {
			$worksheet1->setMarginTop($print_parms[2]*$marginScaleFactor);
		}
		if ( isset($print_parms[3]) && $print_parms[3] != '' ) {
			$worksheet1->setMarginBottom($print_parms[3]*$marginScaleFactor);
		}

		// Set column widths - if defined
		$colWidths = $ws->colwidths;
		$colWidths = explode(',', $colWidths );
		for ($i=0; $i<count($colWidths); $i++) 
		{
			if ( is_numeric($colWidths[$i]) && $colWidths[$i] >= 0 ) {
				$worksheet1->setColumn($i, $i, $colWidths[$i]);
			}
		}


		// Add Header, if specified
		$substHeadFoot = Sql2excelParms::get($cmpParms,'subst_head_footer', '1');      			
		$currRow = 0;
		$header = $ws->header;
		if ( $header != '' ) {
			$headertextArr = explode("\n",$header);				
			if ( is_array($headertextArr) ) {
				if ( $ws->header_parms == '' )
					$ws->header_parms = 'Arial,10,normal,black,none';

				$hparms = explode(",",$ws->header_parms);
				$format_header 		=&  writeExcel::addFormat($workbook, $hparms[0], $hparms[1], $hparms[2], $hparms[3], $hparms[4]);
				$format_header_url 	=&  writeExcel::addFormat($workbook, $hparms[0], $hparms[1], $hparms[2], 'blue', $hparms[4]);

				foreach ($headertextArr as $headerrow) {
					$headerrow = trim($headerrow);
					if ( $headerrow != null ) {
						if ( $substHeadFoot ) {
							$headerrow = writeExcel::replace_vars($headerrow, $substParms, $extraParms, $cmpParms );
						}
						if ( writeExcel::is_url($headerrow) )
							writeExcel::writeCell($worksheet1, $currRow, 0, $headerrow, $format_header_url);
						else
							writeExcel::writeCell($worksheet1, $currRow, 0, $headerrow, $format_header);

					}
					$currRow++;
				 }
			}
		}
      			
		$mindatarow = $currRow;
		$maxdatarow = $currRow;
		
		if ( is_array($rows) && is_object($rows[0]) ) {
      			
      			// Add column titles
			$colNames = writeExcel::getColumnNames($rows);
			//Number of Columns in user query (exclude JoomFish fields if any)
			$nrCols = writeExcel::getColumnCount($colNames, $query);
      			
      			if ( $ws->show_colheader <> 0 ) {
				$headerTextSize = $ws->heading_text_size;
				if ( $headerTextSize == null || $headerTextSize == 0 ) { $headerTextSize = 10; }
				$headerTextCol = $ws->heading_text_col;
				if ( $headerTextCol == null || $headerTextCol == '' ) { $headerTextCol = 'black'; }
				$headerBgCol = $ws->heading_bg_col;
				if ( $headerBgCol == null || $headerBgCol == '' ) { $headerBgCol = 'white'; }


				if ( $writerVersion == '2000_utf8' ) {
					$formatot =& $workbook->addFormat(); 
					$formatot->setSize($headerTextSize);			
					$formatot->setAlign('center');			
					$formatot->setColor($headerTextCol);
					$formatot->setPattern();
					$formatot->setFgColor($headerBgCol);
				} else {
					$formatot =& $workbook->add_format(); 
					$formatot->set_size($headerTextSize);			
					$formatot->set_align('center');			
					$formatot->set_color($headerTextCol);
					$formatot->set_pattern();
					$formatot->set_fg_color($headerBgCol);			
				}

				for ($i=0; $i<$nrCols; $i++) 
				{
					writeExcel::writeCell($worksheet1, $currRow, $i, $colNames[$i], $formatot, $formulas);
				}
				$currRow++;
			}
     			
      			// Add all the data      			
      			$mindatarow = $currRow + 1;
      			$maxdatarow = $currRow + count($rows);
			if ( $ws->data_parms == '' )
				$ws->data_parms = 'Arial,10,normal,black,None,none,1,silver,1';      			
      			$dparms = explode(",",$ws->data_parms);      			
      			if ( sizeof($dparms) >= 9 ) {    				
   				$format_data 		=&  writeExcel::addFormat($workbook, $dparms[0], $dparms[1], $dparms[2], $dparms[3]);
   				$format_data_url 	=&  writeExcel::addFormat($workbook, $dparms[0], $dparms[1], $dparms[2], 'blue');  		
      			} else {
      				$format_data = null;
      			}      			      			
      			foreach ( $rows as $row ) {
         			for ( $i=0; $i<$nrCols; $i++) {           			
         				if ( sizeof($dparms) >= 9  &&  $format_data != null ) {
         					if ( writeExcel::is_url($row->$colNames[$i]) )
         						writeExcel::writeCell($worksheet1, $currRow, $i, $row->$colNames[$i], $format_data_url, $formulas,$mindatarow,$maxdatarow,$removeCR);
         					else
							writeExcel::writeCell($worksheet1, $currRow, $i, $row->$colNames[$i], $format_data, $formulas,$mindatarow,$maxdatarow,$removeCR);
					} else {
						writeExcel::writeCell($worksheet1, $currRow, $i, $row->$colNames[$i], 0, $formulas,$mindatarow,$maxdatarow,$removeCR);					
					}					
         			}
        			$currRow++;
      			}
      			
      		} elseif ( ( $rows == '' || ( is_array($rows) && !is_object($rows[0]) ) ) && $query != '' ) {
      			// Empty recordset.       			
      			$includeNoRec = $ws->include_no_records;
      			if ( $includeNoRec ) {
      				$errMsg = $ws->error_norecords;
      				if ( trim($errMsg) == '' ) {
      					$errMsg = JText::_( 'No records found' );
      					writeExcel::writeCell($worksheet1, $currRow, 0, $errMsg);
      				} else {
      					$errMsg = explode("\n", $errMsg);
      					$i=0;
      					foreach ( $errMsg as $row ) {
						$row = trim($row);
						$row = writeExcel::replace_vars($row, $substParms, $extraParms, $cmpParms );
      						writeExcel::writeCell($worksheet1, $currRow + $i, 0, $row);
      						$i++;
      					}
      				}
      			}
      		} elseif ( $rows != '' && is_string($rows) && Sql2excelParms::get($cmpParms,'show_sql_errors', 1)  ) {
      			$errMsg = explode("\n", $rows);
      			$i=0;
      			foreach ( $errMsg as $row ) {
				$row = trim($row);				
      				writeExcel::writeCell($worksheet1, $i, 0, $row);
      				$i++;
      			}
      			
      		}
      		
		// Add Footer, if specified
		$footer = $ws->footer;
		$footertextArr = explode("\n",$footer);
		if ( is_array($footertextArr) ) {

			if ( $ws->footer_parms == '' )
				$ws->footer_parms = 'Arial,10,normal,black,none';

			$fparms = explode(",",$ws->footer_parms);
			$format_footer 		=&  writeExcel::addFormat($workbook, $fparms[0], $fparms[1], $fparms[2], $fparms[3], $fparms[4]);
			$format_footer_url 	=&  writeExcel::addFormat($workbook, $fparms[0], $fparms[1], $fparms[2], 'blue', $fparms[4]);

			foreach ($footertextArr as $footerrow) {
				$footerrow = trim($footerrow);
				if ( $footerrow != null ) {
					if ( $substHeadFoot ) {
						$footerrow = writeExcel::replace_vars($footerrow, $substParms, $extraParms, $cmpParms );
					}
					if ( writeExcel::is_url($footerrow) )
						writeExcel::writeCell($worksheet1, $currRow, 0, $footerrow, $format_footer_url);
					else
						writeExcel::writeCell($worksheet1, $currRow, 0, $footerrow, $format_footer);
				}
				$currRow++;
			}
		 }  

	}

	if ( $nrWorksheets == 0 ) {
      		$worksheet1 =& $workbook->addWorksheet('Sheet 1');
      		writeExcel::writeCell($worksheet1, 0, 0,   JText::_( 'Workbook does not contain any valid worksheets with data.') );
      		writeExcel::writeCell($worksheet1, 1, 0,   JText::_( 'Turn on SQL Error Messages in SQL 2 Excel Component to debug the problem.') );
      	}
      	
      	$workbook->close();
    
   }
   
   
   ////////////////////////////////////////////////////////////
   // Check for bad words in an array
   //   Returns '' if ok
   //   Returns Error Message if something bad is found.
   ////////////////////////////////////////////////////////////
   function check_bad_words_arr($cmpParms, $wordArr) {
   	
   	
   	// Get list of bad words to check
   	$badWordsArr = writeExcel::get_bad_words_array($cmpParms);
   	
   	$badWordsMsg = '';
   	
   	if ( is_array($badWordsArr) && is_array($wordArr) ) {   		
		$varNames = array_keys($wordArr);
		foreach ( $varNames as $varName ) {
			$varValue = $wordArr{$varName};
			$varValueArr = explode(' ', $varValue);
			foreach ( $varValueArr as $token ) {
				$token = strtoupper(trim($token));
				if ($token != '' && in_array($token, $badWordsArr) ) {
					$badWordsMsg =  JText::_( 'BAD_WORDS_ERROR_MSG');
				}
			}
		}
   	}
   	return $badWordsMsg;
   }
   
   
   function get_bad_words_array($cmpParms)
   {
   	$badWordsArr = null;   	
   	$badWordsStr = Sql2excelParms::get($cmpParms,'badwords', 'ALTER,CREATE,DELETE,DROP,GRANT,INSERT,KILL,LOAD,LOCK,RENAME,REPLACE,REVOKE,SET,TRUNCATE,UPDATE');   	
   	if ( trim($badWordsStr) != '' ) {
   	   	$badWordsStr = strtoupper($badWordsStr);
   		$badWordsArr = explode(',',$badWordsStr);
   	}
   	return $badWordsArr;
   }
   

   ////////////////////////////////////////////////////////////
   // Check final SQL for bad words
   //   Returns '' if ok
   //   Returns Error Message if something bad is found.
   ////////////////////////////////////////////////////////////
   function check_sql_bad_words($cmpParms, $sql) {
   
   	// Get list of bad words to check
   	$badWordsArr = writeExcel::get_bad_words_array($cmpParms);
   
   	$sql = str_replace("\n", ' ', $sql);
   	$sqlWordArr = explode(' ', $sql);
   	
   	return writeExcel::check_bad_words_arr($cmpParms, $sqlWordArr);
   }
  
  
   
   function replace_vars($str, $commonVars, $extraVars=0, $cmpParms=0) {

   	if ( !is_object($cmpParms) ) {
   		$cmpParms = Sql2excelParms::getParms();	
   	}
   	
	$i=0;	
	$strArr = explode("\n", $str);
	foreach ( $strArr as $row ) {
		$row = writeExcel::replace_date_codes($row, $cmpParms);
		$row = writeExcel::replace_vars_arr($row, $commonVars);
		$row = writeExcel::replace_vars_arr($row, $extraVars);
		$strArr[$i] = writeExcel::replace_date_codes($row, $cmpParms);
		$i++;
	}  	
   	return implode("\n",$strArr);
   }

   function replace_vars_arr($str, $varArr) {
	$retStr = $str;
	if ( is_array($varArr) ) {
		$varNames = array_keys($varArr);
		foreach ( $varNames as $varName ) {
			if ( substr($varName,0,16) == 'SQL2EXCEL_WS_SQL' ) {
				// Possible multi-line parameters, convert to one line
				$varValues = explode("\n", $varArr{$varName});
				$varValue = '';
				foreach ( $varValues as $row ) {
					$varValue .= trim($row) . ' ';
				}
				$retStr = str_replace('{' . $varName . '}', trim($varValue), $retStr);
			} else { 
				$retStr = str_replace('{' . $varName . '}', $varArr{$varName}, $retStr);	
			}
		}
	}
	return $retStr;
   }


   function writeCell(&$worksheet1, $row, $col, $str, $format=0, $formulas=0, $mindatarow=0, $maxdatarow=65535, $removeCR=0 ) {
   	
   	if ( $removeCR > 0 ) { $str = writeExcel::removeCR($str, $removeCR); }   	   	
	if ( substr(trim($str),0,1) == '=' || substr(trim($str),0,1) == '@' ) {
		$colUser = $col+1;
		$rowUser = $row+1;		
		if ( is_array($formulas) & in_array($colUser, $formulas) ) {
			// Formula! => Parse and replace Row and Col ID's
			$str = writeExcel::replace_row_col_ids($str, $rowUser, $colUser, $mindatarow, $maxdatarow);
			$worksheet1->write($row, $col, $str, $format); 
		} else {
			// Export as Text
			$worksheet1->writeString($row, $col, $str, $format); 
		}		

	} elseif( is_string($str) ) {
		if ( substr($str,0,1) == "0" && ( strlen($str) > 1 && strpos($str, ".") === false )) {
			$worksheet1->writeString($row, $col, $str, $format); 
		} else {
	   		$worksheet1->write($row, $col, $str, $format);
	   	}
	}	
   }
   
   function removeCR($str,$removeCR) {

   	$repl = " ";
	switch ($removeCR) {
    	case 2:
        	$repl = "\n";
        	break;
    	case 3:
        	$repl = "||";
        	break;
    	case 4:
       	 	$repl = "<br>";
        	break;
	case 5:
       	 	$repl = "";
        	break;        	
	}
		
	$str = str_replace("\r\n",$repl, $str);
	$str = str_replace("\n\r",$repl, $str);
	$str = str_replace("\n",$repl, $str);
	$str = str_replace("\r",$repl, $str);	
	
	return $str;
   } 


   function replace_row_col_ids($str, $row, $col,$mindatarow=0, $maxdatarow=0) {
	$retStr = $str;
	$pos = strpos($str, '{ROWID');
        if ( $pos > 0 ) {
	   $endPos = strpos($str, '}', $pos );
	   $leftStr = substr($str,0,$pos);
	   $rightStr =  substr($str,$endPos+1);
	   $midStr = substr($str,$pos+1,$endPos-$pos-1);
	   $midStr = str_replace('ROWID', $row, $midStr);
	   eval("\$mStr = $midStr;");
	   $retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col);
	} 

	$pos = strpos($str, '{COLID');
        if ( $pos > 0 ) {
	   $endPos = strpos($str, '}', $pos );
	   $leftStr = substr($str,0,$pos);
	   $rightStr =  substr($str,$endPos+1);
	   $midStr = substr($str,$pos+1,$endPos-$pos-1);
	   $midStr = str_replace('COLID', $col, $midStr);
	   eval("\$mStr = $midStr;");
	   $retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col);
	}  
	
	$pos = strpos($str, '{MINROWID');
        if ( $pos > 0 ) {
	   $endPos = strpos($str, '}', $pos );
	   $leftStr = substr($str,0,$pos);
	   $rightStr =  substr($str,$endPos+1);
	   $midStr = substr($str,$pos+1,$endPos-$pos-1);
	   $midStr = str_replace('MINROWID', $mindatarow, $midStr);
	   eval("\$mStr = $midStr;");
	   $retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col, $mindatarow, $maxdatarow);
	} 
	
	$pos = strpos($str, '{MAXROWID');
        if ( $pos > 0 ) {
	   $endPos = strpos($str, '}', $pos );
	   $leftStr = substr($str,0,$pos);
	   $rightStr =  substr($str,$endPos+1);
	   $midStr = substr($str,$pos+1,$endPos-$pos-1);
	   $midStr = str_replace('MAXROWID', $maxdatarow, $midStr);
	   eval("\$mStr = $midStr;");
	   $retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col, $mindatarow, $maxdatarow);
	} 	
	
	return $retStr;
   }
   
   
   function replace_date_codes($str, $cmpParms)
   {

	$retStr = $str;
	$dateMode = Sql2excelParms::get($cmpParms,'datemode', 0);
	
	// date() vs JDate / strtime() codes
	$dcodes = array('d' => array('d','d'),	// Day of month 01-31
			'D' => array('D','a'),	// Mon through Sun
			'j' => array('j','d'),	// Day of the month, 1-31
			'l' => array('l','A'),	// Sunday through Saturday
			'N' => array('N','w'),	// 1 (for Monday) through 7 (for Sunday)
			'S' => array('S',''),	// st, nd, rd or th. Works well with j
			'w' => array('w','w'),	// 0 (for Sunday) through 6 (for Saturday)
			'z' => array('z','j'),	// 0 through 365
			'W' => array('W','W'),	// Week number  (not exactly same)
			'F' => array('F','B'),	// January through December
			'm' => array('m','m'),	// Month, 01 through 12
			'M' => array('M','b'),	// Jan through Dec
			'n' => array('n','m'),	// Month, 1 through 12
			't' => array('t',''),	// Number days in given month 28 to 31
			'L' => array('L',''),	// Whether it's a leap year (0 or 1)
			'o' => array('o','G'),	// ISO-8601 year number.
			'Y' => array('Y','Y'),	// Year YYYY
			'y' => array('y','y'),	// Year YY
			'a' => array('a','p'),	// am/pm
			'A' => array('A','p'),	// AM/PM
			'B' => array('B',''),	// Swatch Internet time  000-999
			'g' => array('g','I'),	// Hour 1 through 12
			'G' => array('G','H'),	// Hour 0 through 23
			'h' => array('h','I'),	// Hour 01 through 12
			'H' => array('H','H'),	// Hour 00 through 23			
			'i' => array('i','M'),	// Minutes with leading zeros, 00 - 59
			's' => array('s','S'),	// Seconds, with leading zeros
			'u' => array('u',''),	// Microseconds 
			'e' => array('e',''),	// Timezone abbreviation / offset  			(depends on OS in Joomla mode)
			'I' => array('I',''),	// daylight saving time (1 or 0 )
			'O' => array('O',''),	// Difference to Greenwich time (GMT) in hours		(depends on OS in Joomla mode)
			'P' => array('P',''),	// Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3)
			'T' => array('T',''),	// Timezone abbreviation
			'Z' => array('Z',''),	// Timezone offset in seconds. 
			'c' => array('c',''),	// ISO 8601 date (added in PHP 5)
			'r' => array('r',''),	// RFC 2822 formatted date
			'U' => array('U','s')	// Seconds since the Unix Epoch 
			);

	global $mainframe;
	$mdate = JFactory::getDate();
	$mdate->setOffset($mainframe->getCfg('offset'));
			
	foreach(array_keys($dcodes) as $key) {
		$code = $dcodes{$key}[$dateMode];
		$search = '{' . $key . '}';
		if ( $dateMode == 1 ) {
			$replace = $mdate->toFormat('%'.$code);
			if ( $key == 'z' ) { $replace--; }
			if ( ( $key == 'j' || $key == 'n' || $key == 'G' || $key == 'g' ) && substr($replace,0,1) == '0' ) { $replace = substr($replace,1); }
			if ( $key == 'N' && $replace == 0 ) { $replace = 7; }
			if ( $key == 'a' ) { $replace = strtolower($replace); }
			if ( $key == 'U' ) { $replace = time(); }
			if ( $key == 't' ) { $replace = writeExcel::days_in_month($mdate->toFormat('%m'), $mdate->toFormat('%Y')); }
			if ( $key == 'L' ) { $replace = 0; if ( writeExcel::isleapyear($mdate->toFormat('%Y'))) { $replace = 1; } }
			if ( $key == 'I' ) { $replace = date('I'); }
		} else {
			$replace = date($code);
		}

		$retStr = str_replace($search,$replace,$retStr);
	}

	return $retStr;   
   }
   
   
   function getResults($db, $query, $cmpParms) {
   
   	// Check for bad words in the SQL?
   	$badWordsMsg = '';
   	if ( Sql2excelParms::get($cmpParms,'badwordssql', '1') ) {
   		$badWordsMsg = writeExcel::check_sql_bad_words($cmpParms, $query);
   	}
   	
   	if ( $badWordsMsg == '' ) { 

   		// Parse the query for multiple statements
		// Make sure we have only \n for line breaks
		$queryStr = writeExcel::removeCR($query,2);
		
		// Split statements that end with ; and a new line
   		$queryTokens = explode(";\n",$query);
		$errMsg = '';
		$result = true;
		

		// Execute all statements (if more than one)
		foreach ( $queryTokens as $queryStmt ) {
			if ( is_string($queryStmt) && strlen( $queryStmt) > 0 ) {
				$db->setQuery( $queryStmt );
				$ret = $db->query();
			}
		}			
			
		$rows = $db->loadObjectList();
				
		if ( !$rows ) {
			if ( is_null($rows) ) {
				return $db->getErrorMsg();
			} else {
				return '';  // No results returned
			}
		} else {
			return $rows;
		}
	} else {
		return $badWordsMsg;
	}
   }      
   
   function getColumnNames($rows)
   {
      $firstRow = $rows[0];
      if ( is_object($firstRow) ) {
      		$colNames = array_keys(get_object_vars($firstRow));
      } else {
      		$colNames = array();
      }
      return $colNames;
   }
   
   // Joom!Fish adds columns to query.
   //
   // This function gets the column count from the original query string
   function getColumnCount($colNames, $query)
   {
	$query = trim(strtoupper($query));
	$query = str_replace("\n", ' ', $query);

	$query = writeExcel::remove_quote($query,'`');
	$query = writeExcel::remove_quote($query,"'");
	$query = writeExcel::remove_quote($query,'"');


     	// Parentesis
      	$startP=0;
      	$retStr = '';
      	for ( $i=0; $i<strlen($query);$i++) {
         	$char = substr($query,$i,1);
         	if ( $char == "(" ) { 
         		$startP++; 
         	} elseif ( $char == ")" ) {
         		$startP--;
         		$char = 'Y';
         	}
         
         	if ( $startP > 0 ) { $char = 'Y'; }         
         
         	$retStr .= $char;
      	}      
      
      	$pos = strpos($retStr, ' FROM ');      
      	if ( $pos > 0 && ! strpos($retStr,'*') ) {
         	$retStr = substr($retStr,0,$pos);
         	$cols = explode(',', $retStr);
         	if ( count($cols) < count($colNames) ) {
         		return count($cols);
         	} else {
         		return count($colNames);
         	}
      	} else {
         	return count($colNames);
      	}      
   }  
   
   
   function remove_quote($query, $match) {
	$found = false;
      	$retStr = '';
      	for ( $i=0; $i<strlen($query);$i++) {
         	$char = substr($query,$i,1);
         	if ( $found && $char != $match ) {
         		$char = 'X';
         	} elseif ( $found && $char == $match ) {
              	$found = false;
              	$char = 'X';
         	} elseif ( !$found && $char == $match ) {
              	$found = true;
              	$char = 'X';
         	}
         
         	$retStr .= $char;
     	 }
     	 return $retStr;
   }
   
   
   function convert_utf8($str) {   
   	if ( $this->_writer_version == '2000_limited' ) {
 		$ret = $str;
           	if ( function_exists('iconv') ) { 
			$s = @iconv('UTF-8', 'cp1252//TRANSLIT', $str); 
			$ret = preg_replace("/([\xC2\xC4])([\x80-\xBF])/e",  "chr(ord('\\1')<<6&0xC0|ord('\\2')&0x3F)", $s); 
		} elseif (function_exists('mb_detect_encoding') && function_exists('utf8_decode') && mb_detect_encoding($str) == 'UTF-8' )  { 
			$ret = utf8_decode($str); 
          	} 
          	return $ret;  
      	} else {
      		return $str;
      	}
   }

   function ExcelHeader($fileName) {
	header("Content-type: application/vnd.ms-excel");
	header("Content-Disposition: attachment; filename=\"$fileName\"" );
	header("Expires: 0");
	header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
	header("Pragma: public");
   }



   // Which Excel Writer Version to use
   function get_writer($wbParms,$cmpParms)
   {   
	$writerVersion = Sql2excelParms::get($cmpParms,'excelwriter', 'auto');   	
   	if ( $writerVersion == 'auto' ) {
   		if ( function_exists('iconv') && function_exists('mb_strlen') ) {
   			$writerVersion = '2000_utf8';
   		} else {
   			$writerVersion = '2000_limited';
   		}
   	}   	
   	$this->_writer_version = $writerVersion;      	
   	return $writerVersion;
   }
   
   
   function &addFormat(&$workbook, $fontFamily='Arial', $fontSize=0, $fontWeight='normal', $fontCol='none', $fontBgCol='none')
   {
	$myformat =& $workbook->addFormat(); 
	if ( $fontFamily != 'Arial' ) {
		$myformat->setFontFamily($fontFamily);
	}
	$myformat->setSize($fontSize);
	if ( $fontWeight == 'bold' ) {
		$myformat->setBold();
	}
	if ( $fontCol != 'none' &&  $fontCol != '' ) {
		$myformat->setColor($fontCol);
	}
	if ( $fontBgCol != 'none' &&  $fontBgCol != '' ) {
		$myformat->setPattern();
		$myformat->setFgColor($fontBgCol);   
	}
	return $myformat;
   }
   
   
   function is_url($str)
   {
   	if ( substr($str,0,7) == 'http://' ) 
   		return true;
   	else
   		return false;
   }

   // calculate number of days in a month 
   function days_in_month($month, $year) { 
	return $month == 2 ? ($year % 4 ? 28 : ($year % 100 ? 29 : ($year % 400 ? 28 : 29))) : (($month - 1) % 7 % 2 ? 30 : 31); 
   } 

   // Is Leap year?
   function isleapyear($year = '') {
	if (empty($year)) {
		$year = date('Y');
	}
	$year = (int) $year;
	if ($year % 4 == 0) {
     		if ($year % 100 == 0) {
        		return ($year % 400 == 0);
     		} else {
        	return true;
     		}
 	} else {
     		return false;
 	}
   }   

   function checkSheetNames($wsParms, $cmpParms, $substParms)
   {
	
	$sheetNames = array();
	$i=0;
	$str = '';
	foreach ($wsParms as $ws) {
		$sheetNames[$i] = strtolower(trim($ws->sheetname));
		$str .= '<li>' . $ws->ws_id . ' - ' . $ws->sheetname . '</li>';
		$i++;
	}
	
	$uniqueNames = array_unique($sheetNames);
		
	if ( count($sheetNames) != count($uniqueNames) ) {
		JError::raiseError(500, '<h1>' . JText::_('Non unique Sheet Names!') . '</h1><p>' . JText::_('The Worksheet Sheet Names does not have unique names.<br>Two more more Worksheets have the same Sheet Name.') . '</p><p>' . JText::_('Please rename the Sheet Names on the Worksheets so that they become unique.') . '</p><ul>' . $str . '</ul>' );
		exit;
	}
	
   }
   
}
?>
Return current item: Chalange