<?php 
set_time_limit(10); // Set maximum execution time to 10 seconds.
error_reporting(E_ALL ^E_NOTICE); // Notice errors break the Excel file format.

define('TEXT_FORMAT',     0);
define('NUM_FORMAT',      1);
define('MONEY_FORMAT',    2);
define('DATE_FORMAT',     3);
define('TIME_FORMAT',     4);
define('DATETIME_FORMAT', 5);

vendor('excel_writer/ExcelWriter');
vendor('ole/OleRoot');
vendor('ole/OleFile');

class excelHelper extends ExcelWriter {

  var $workbook;
  var $worksheets = array();
  var $formats    = array();
  
  var $font   = 'Arial';
  var $size   = 10;
  var $align  = 'left';
  var $valign = 'vcenter';
  var $bold   = 0;
  var $italic = 0;

  /**
   * Creates the necessary objects and a temporary Excel file. Sets the
   * directory for temporary file creation and sets the version to
   * Excel 97 (support UTF-8).
   *
   * @param string  $filename  Name of the downloadable file
   */
  function excelHelper($filename = 'data.xls') {
    $this->workbook =& new ExcelWriter();
    $this->workbook->setTempDir(TMP."cache");

    $this->workbook->setVersion(8); // Set workbook to Excel 97 (for UTF-8 support)
  }

  /**
   * Returns an array with parameters that can be used to create an XF
   * formatting array. Default values are overridden when specified in
   * the parameter array.
   *
   * @param  array  $params  Array with parameters to define formatting.
   *                         Keys refer to the style properties.
	 * @return string          Formatting array.
   */
  function _getFormatArray($params = NULL) {
    $temp = array('font'   => $this->font,
                  'size'   => $this->size,
                  'bold'   => $this->bold,
                  'italic' => $this->italic,
                  'align'  => $this->align,
                  'valign' => $this->valign);
    if(isset($params)) {
      foreach($params as $key => $value) {
        $temp[$key] = $value;
      }
    }
    return $temp;
  }

  /**
   * Returns the Julian date of the Windows Excel epoch (1 Jan 1900).
   *
 	 * @return integer  Julian date
   */
  function _GetExcelEpoch() {
    return GregorianToJD(1,1,1900); // Windows Excel epoch
  }

  /**
   * Initializes the default formats that can be used. After changing
   * the default properties this method has to be called to initialize 
   * the formatting arrays.
   */
  function initFormats() {
    // initialize default formats:
    $text = $this->_getFormatArray();
    $text['textwrap'] = 1;
    $text['numformat'] = '@';
    $this->formats[TEXT_FORMAT] =& $this->workbook->addformat($text);

    $num = $this->_getFormatArray();
    $num['align'] = 'right';
    $this->formats[NUM_FORMAT] =& $this->workbook->addformat($num);

    $num['numformat'] = '[$EUR-413] #,##0.00;[$EUR-413] #,##0.00-';
    $this->formats[MONEY_FORMAT] =& $this->workbook->addformat($num);

    $num['numformat'] = 'dd-mm-yyyy';
    $this->formats[DATE_FORMAT] =& $this->workbook->addformat($num);

    $num['numformat'] = 'hh:mm:ss';
    $this->formats[TIME_FORMAT] =& $this->workbook->addformat($num);

    $num['numformat'] = 'dd-mm-yyyy hh:mm:ss';
    $this->formats[DATETIME_FORMAT] =& $this->workbook->addformat($num);
  }

  /**
   * Creates a worksheet in the Excel file, sets its encoding to UTF-8
   * and returns a reference to the worksheet.
   *
   * @param  string  $name  Name of the worksheet.
	 * @return object         Worksheet object.
   */
  function &AddWorksheet($name = NULL) {
    $this->worksheets[] =& $this->workbook->addWorksheet($name);
    $this->worksheets[count($this->worksheets) - 1]->setInputEncoding('UTF-8');
    return $this->worksheets[count($this->worksheets) - 1];
  }

  /**
   * Adds a formatting array to the Excel workbook and returns the index
   * of the array.
   *
   * @param  array  $params  Array with parameters to define formatting.
   *                         Keys refer to the style properties.
	 * @return integer         Index of the formatting array.
   */
  function AddFormat($params) {
    $this->formats[] =& $this->workbook->addformat($this->_getFormatArray($params));
    return (count($this->formats) - 1);
  }

  /**
   * Adds a color to the color palette of the workbook.
   *
   * @param  integer $index  Index on the color palette. Existing colors
   *                         will automatically be overridden.
   * @param  mixed   $color  Can be an array of Red, Green and Blue values
   *                         or a hexadecimal representation of the color.
   * @return integer         The palette index for the custom color.
   */
  function setColor($index, $color) {
    if(!is_array($color)) {
      $temp = str_split($color, 2);
      $color[] = hexdec($temp[0]);
      $color[] = hexdec($temp[1]);
      $color[] = hexdec($temp[2]);
    }
    return $this->workbook->setCustomColor($index, $color[0], $color[1], $color[2]);
  }

  /**
   * Converts a MySQL Datetime field value to Excel datetime values.
   *
   * @param string  $datetime  MySQL datetime (dd-mm-yyyy hh:mm:ss)
   * @param float              Excel datetime value.
   */
  function MysqlDatetimeToExcel($datetime) {
    $tmp = explode(" ", $datetime);
    $date = explode("-", $tmp[0]);
    if(isset($tmp[1])) $time = explode(":", $tmp[1]);
    $date1 = GregorianToJD($date[1],$date[2],$date[0]);
    $epoch = $this->_GetExcelEpoch();
    $frac = (($time[0] * 60 * 60) + ($time[1] * 60) + $time[2])/(24*60*60);
    
    return ($date1 - $epoch + 2 + $frac);
  }

  /**
   * Converts a UNIX timestamp value to Excel datetime values.
   *
   * @param int  $timestamp  UNIX timestamp
   * @param float            Excel datetime value.
   */
  function TimestampToExcel($timestamp) {
    return $this->MysqlDatetimeToExcel(date("d-m-Y H:i:s", $timestamp));
  }

  /**
   * Writes a $token (string, number, array, link, formula etc.) to the 
   * specified row and column on the specified worksheet.
   *
   * @param  object   $worksheet  Reference to the worksheet
   * @param  integer  $row        Row number (starting at zero; A1 is 0,0)
   * @param  integer  $col        Column number (starting at zero)
   * @param  mixed    $token      Data to write to the worksheet
   * @param  integer  $format     Index of the formatting array to use
   * @return boolean              False when an error occurs, otherwise True.
   */
  function write(&$worksheet, $row, $col, $token, $format = 0) {
    return $worksheet->write($row, $col, $token, $this->formats[$format]);
  }
  
  /**
   * Sends the temporary Excel file as a string to the render engine
   * and clears all objects.
   */
  function OutputFile() {
    $this->workbook->Close();
    echo file_get_contents($this->workbook->filename, "rb");
  }

}
?>