File: printform-xls.php

<?PHP
/**
* @name printform-xls.php, contains class CPrintFormXls,
* for generating "on the fly" filled Excel document based on XLS template file,
* loaded configuration (config is XML file!) and provided user data.
* PHPExcel classes used for reading/writing XLS body, see http://www.codeplex.com/PHPExcel
* @version 1.01 build 0005 2010-07-06
* @Author Alexander Selifonov, <alex [at] selifan.ru>
* @Link: http://www.selifan.ru
*
* @license http://www.opensource.org/licenses/bsd-license.php    BSD
*
* To make this code working, don't forget to add these lines:
* require_once('PHPExcel.php');
* require_once('PHPExcel/IOFactory.php');
* require_once('PHPExcel/Reader/Excel5.php'); # Or other Excel formats if needed
* (if You'going to output to PDF, include respective modules !
**/
class CPrintFormXls {
    const USE_BUFFER_FILE = false; # set it to TRUE if 'php://output' writing fails for any reason...
    private $_data = array();
    private $_templatefile = '';
    private $_outname = '';
    private $_tofile  = false;
    private $_configfile = '';
#    private $_protection = false;
    private $_config = array();
    private $_sheetdefs = array();
    private $errormessage = '';
    private $_outfmt = 'Excel5'; # XLS output file format (anyone supported by PHPExcel, including 'pdf' !)
    private $_objReader = null;
    private $_objPHPExcel = null;
    private $_worksheet = null;
    public function CPrintFormXls($param='', $data=null, $outname='', $tofile=false) {
        if(is_array($param)) {
            if(isset($param['template'])) $this->_templatefile = $param['template'];
            if(isset($param['data']))     $this->_data   = $param['data'];
            if(isset($param['outname']))  $this->_outname= $param['outname'];
            if(isset($param['tofile']))    $this->_tofile  = $param['tofile'];
            if(isset($param['outfmt']))    $this->_outfmt  = $param['outfmt'];
            if(isset($param['configfile'])) {
               $this->_configfile  = $param['configfile'];
            }
        }
        elseif(is_scalar($param)) {
            $this->_configfile = $param;
            $this->_data = $data;
            $this->_out = $outname;
            $this->_tofile = $tofile;
        }
        if(!empty($this->_configfile)) $this->LoadConfig($this->_configfile);
    }
    /**
    * Loads configuration from prepared XML file, see it's format in docs and examples
    *
    * @param mixed $cfgname full path-filename to config XML file
    * @Returns true if configuration successfully loaded, false otherwise
    */
    public function LoadConfig($cfgname=null) {
        if(!$cfgname) $cfgname = $this->_configfile;
        else $this->_configfile = $cfgname;
        $ret = true;
        $this->_config = array('title'=>'','description'=>'', 'author'=>'','templatefile'=>''
           ,'stringcharset'=>'','protectsheets'=>0,'protectbook'=>0
           ,'dateformat'=>'dd/mm/yyyy');

        if(is_file($cfgname)) $xml = @simplexml_load_file($cfgname);
        elseif(substr($cfgname,0,5)=='<'.'?xml') $xml = @simplexml_load_string($cfgname);
        # $xp = $xml->xpath('//testformat');    echo '<pre>'; print_r($xp);echo '</pre>';
        if(!($xml) || !isset($xml->sheets)) {
            $this->_errormessage = 'Wrong XML file or no file, '.$cfgname;
            echo $this->_errormessage ;
            return false;
        }
        if(isset($xml->dateformat)) $this->_config['dateformat'] = (string)$xml->dateformat;

        if(isset($xml->protectsheets)) $this->_config['protectsheets'] = (int)$xml->protectsheets; # protect worksheets by password
        if(isset($xml->password)) $this->_config['password'] = (string)$xml->password; # password to protect with

        if(isset($xml->protectbook)) $this->_config['protectbook'] = (int)$xml->protectbook; # protect workbook
        if(isset($xml->bookpassword)) $this->_config['bookpassword'] = (int)$xml->bookpassword;

        if(isset($xml->title)) $this->_config['title'] = (string)$xml->title;
        if(isset($xml->description)) $this->_config['description'] = (string)$xml->description;
        if(isset($xml->author)) $this->_config['author'] = (string)$xml->author;

        if(isset($xml->stringcharset)) $this->_config['stringcharset'] = strtoupper($xml->stringcharset);
        # if not UTF-8 and not '', use iconv() to make UTF-8 !

        if(isset($xml->templatefile)) $this->_config['templatefile'] = $xml->templatefile;
        $fileversion = isset($xml->version) ? $xml->version : 1; # for future needs

        if(!empty($this->_config['templatefile'])) $this->_templatefile = $this->_config['templatefile'];

        $this->_sheetdefs = array();
        $isheet = 0;
        $fldcnt = 0;
        foreach($xml->sheets->children() as $key => $sheetitem) {
            $offset = isset($sheetitem['offset']) ? (int) $sheetitem['offset'] : $isheet;
            $this->_sheetdefs[$isheet] = array('offset'=>$offset, 'fields'=>array());

            # echo "[$key], off=$offset;"; print_r($sheetitem); echo '<hr />'; //debug
            foreach($sheetitem->children() as $key=>$item) {
                $fldname = isset($item['name'])? trim("{$item['name']}") : '';
                if(!$fldname) continue;
                $newar = array('col'=>0,'row'=>0);
                $newar['name'] = strtolower($fldname);
                $newar['col'] = isset($item['col']) ? intval($item['col']) : '0';
                $newar['row'] = isset($item['row']) ? intval($item['row']) : '0';
                $newar['type'] = isset($item['type']) ? strtolower(trim($item['type'])) : '';
                $newar['convert'] = isset($item['convert']) ? (string) $item['convert'] : ''; # user function to convert value
                $newar['scatter'] = isset($item['scatter']) ? (int)$item['scatter'] : 0; # scatter chars to adjacent cells, one by one
#                echo 'subs:'; print_r($newar); echo '<hr />';
                $this->_sheetdefs[$isheet]['fields'][] = $newar;
                $fldcnt++;
            }
            $isheet++;
        }
        if(!$fldcnt) {
            $this->_errormessage = 'No valid worksheet definitions found (no fields defined)!';
            $ret = false;
        }
        return $ret;
    }

    public function SetTitle($strg) { $this->_config['title'] = $strg; }
    public function SetDescription($strg) { $this->_config['description'] = $strg; }
    public function SetAuthor($strg) { $this->_config['author'] = $strg; }

    public function ProtectSheets($protect, $password=null, $options = false) {
        $this->_config['protectsheets'] = $protect;
        if($password!==null) $this->_config['password'] = $password;
        $this->_config['protectoptions'] = $options;
    }
    /**
    * sets workbook protection
    *
    * @param mixed $protect true means "set protecting"
    * @param mixed $password
    */
    public function ProtectBook($protect, $password=null) {
        $this->_config['protectbook'] = $protect;
        if($password!==null ) $this->_config['bookpassword'] = $password;
    }
    /**
    * Adding data to be populated in template
    *
    * @param mixed $param must be an associative array or string - variable name (in that case second parameter should be passed)
    */
    public function AddData($param, $pval=null) {
        if(is_array($param)) $this->_data = array_merge($this->_data, $param);
        elseif(is_string($param)) $this->_data[$param] = $pval;
    }
    /**
    * Populates and sends/saves final Xls document body
    *
    * @param mixed $dest if output filename passed, result will be saved into it,
    * otherwise will be sent to client stream
    * @return true if XLS file generated/echoed, false if some errors
    */
    public function Render() {
        ini_set('max_execution_time', 600);
        if(count($this->_sheetdefs)<1) {
            $this->errormessage = 'Configuration not loaded, Rendering impossible !';
            return false;
        }

        if(empty($this->_outname)) {
            $this->_outname = $this->_templatefile;
            $off = max(strrpos($this->_outname, '/'), strrpos($this->_outname, '\\'));
            if($off!==false) $this->_outname = substr($this->_outname, $off+1);
        }

        if(!$this->CreateXlsObject()) return false;

        $creator = empty($this->_config['author']) ? 'CPrintFormXls, PHPExcel wrapper class' : $this->_config['author'];
        $this->_objPHPExcel->getProperties()->setCreator($creator);

        if(!empty($this->_config['title'])) $this->_objPHPExcel->getProperties()->setDescription($this->_config['title']);
        if(!empty($this->_config['description'])) $this->_objPHPExcel->getProperties()->setTitle($this->_config['description']);

        # Populating with data...
        foreach($this->_sheetdefs as $sheetdef) {

            $this->_worksheet = $this->_objPHPExcel->getSheet($sheetdef['offset']);
            foreach($sheetdef['fields'] as $no=>$fcfg) {
                $fldname = $fcfg['name'];
                if(!isset($this->_data[$fldname])) continue;
                if(!is_scalar($this->_data[$fldname])) continue;
                $strval = $this->_data[$fldname];
                if(!empty($fcfg['convert']) && function_exists($fcfg['convert'])) # user converter function
                   $strval = call_user_func($fcfg['convert'], $strval);
                switch($fcfg['type']) {
                    case 'date':
                        $this->XlsSetDate($fcfg['row'],$fcfg['col'],$strval);
                        break;
                    case 'bool':
                    case 'logical':
                        $strval = $strval ? 'X' : '';
                        $this->_worksheet->setCellValueByColumnAndRow($fcfg['col'], $fcfg['row'], $strval);
                        break;
                    case 'bool-invert':
                    case 'logical-invert':
                        $strval = $strval ? '' : 'X';
                        $this->_worksheet->setCellValueByColumnAndRow($fcfg['col'], $fcfg['row'], $strval);
                        break;
                    default:
                        if(is_string($strval)) {
                            $strval = $this->ConvertCset($strval);
                        }
                        $this->_worksheet->setCellValueByColumnAndRow($fcfg['col'], $fcfg['row'], $strval);
                        break;
                }
            }
            if($this->_config['protectsheets']) {
                $pwd = isset($this->_config['password']) ? $this->_config['password'] : '';
                $this->_worksheet->getProtection()->setPassword($pwd);
                $this->_worksheet->getProtection()->setSheet(true);
                $this->_worksheet->getProtection()->setSort(true);
                $this->_worksheet->getProtection()->setInsertRows(true);
                $this->_worksheet->getProtection()->setFormatCells(true);
            }
            if($this->_config['protectbook']) { # whole workbook protecting
                $pwd = isset($this->_config['bookpassword']) ? (string)$this->_config['bookpassword'] : '';
                $this->_objPHPExcel->getSecurity()->setLockWindows(true)->setLockStructure(true)->setWorkbookPassword($pwd);
#                $this->_objPHPExcel->getSecurity()->setLockStructure(true);
#                $this->_objPHPExcel->getSecurity()->setWorkbookPassword($pwd);
            }
        }
        if($this->_outfmt == 'pdf') {
            $objWriter = new PHPExcel_Writer_PDF($this->_objPHPExcel);
        }
        else {
            $objWriter = @PHPExcel_IOFactory::createWriter($this->_objPHPExcel, $this->_outfmt);
            if(!$objWriter) {
                $this->errormessage = 'PHPExcel: Unsupported writer for format '.$this->_outfmt;
                return false;
            }
        }
        if(self::USE_BUFFER_FILE && !$this->_tofile) {
            @mkdir('./tmp');
            $tmpname = './tmp/'.mt_rand(10000000000,99999999999).'.$$$';
            $objWriter->save($tmpname);
        }
        if($this->_tofile) { # just save to specified file
            if(strtolower($this->_templatefile)==strtolower($this->_outname)) $this->_outname .= '.xls'; # protect accidental template overwriting !
            $objWriter->save($this->_outname);
        }
        else {
            @Header('Pragma: no-cache');
            @Header('Pragma: public');
            @Header('Cache-Control: must-revalidate, post-check=0, pre-check=0');

            if($this->_outfmt == 'pdf')
               @Header('Content-Type: application/pdf');
            else
               @Header('Content-Type: application/vnd.ms-excel');

            @Header("Content-Disposition: attachment; filename=\"{$this->_outname}\"");
            @header('Cache-Control: max-age=0');
            if(self::USE_BUFFER_FILE) {
                $xlbody = file_get_contents($tmpname);
                @Header('Content-Length: '.strlen($xlbody));
                echo($xlbody);
                unlink($tmpname);
            }
            else $objWriter->save('php://output'); # direct output instead of reading from saved temp.file
        }
        unset($this->_worksheet, $this->_objPHPExcel, $objWriter);
        return true;
    }

    private function XlsSetDate($row,$col, $value) {
        if(is_string($value)) { # try to convert 'dddd-mm-yy' or 'dd-mm-yyyy' to 'EXCEL' integer
            $elm = preg_split("/[\s-.\/]+/", $value);
            if(count($elm)<3) return;
            $yy = intval($elm[0]); $mm = intval($elm[1]); $dd = intval($elm[2]); # default fmt is yyyy-mm-dd
            if($elm[2]>90) { # ok, it's must be mm/dd/yyyy OR dd.mm.yyyy
                $yy = intval($elm[2]);
                $mm = intval($elm[0]); $dd = intval($elm[1]);
            }
            if($mm>12) { # swap if wrong month (too big, may be it's day no.)
                $tmp = $dd; $mm = $dd; $dd=$mm; $mm=$tmp;
            }
            $value = floor(mktime(0,0,1,$mm,$dd,$yy) / 86400) + 25570; # 25570 - correcting from mktime to Excel date number
        }
        $this->_worksheet->setCellValueByColumnAndRow($col, $row, $value);
        $k1 = floor($col/26);
        $k2 = intval($col % 26);
        $colname = (($k1>0)? chr(64+$k1):'') .chr(65+$k2).$row; # col,row number to "AZ5" notation
        $this->_worksheet->getStyle($colname)->getNumberFormat()->setFormatCode($this->_config['dateformat']);
    }

    # returns last error message
    public function GetErrorMessage() { return $this->errormessage; }
    /**
    * @return PHPExcel object to manupulate in user's code
    */
    private function CreateXlsObject() {
        if(is_object($this->_objPHPExcel)) return true;
        $this->_objReader = @PHPExcel_IOFactory::createReader('Excel5'); # TODO : investigate support of reading other XLS[x]
        if(!$this->_objReader) {
            $this->errormessage = 'PHPExcel classes not included !';
            return false;
        }
        try {  $this->_objPHPExcel = $this->_objReader->load($this->_templatefile); }
        catch (Exception $e) {
            $this->errormessage = 'Loading XLS file error, error message is: '.$e->getMessage();
             return false;
        }
        return true;
    }
    public function GetXlsObject() {
        if(!$this->_objPHPExcel) $this->CreateXlsObject();
        return $this->_objPHPExcel;
    }
    private function ConvertCset($strval) {
        $ret = ($this->_config['stringcharset']!='' && $this->_config['stringcharset']!='UTF-8') ?
         iconv($this->_config['stringcharset'],'UTF-8',$strval) : $strval;
        return $ret;
    }
} # class CPrintFormXls definition end