PHP Classes

File: printform-xls.php

Recommend this page to a friend!
  Classes of Alexander Selifonov  >  PHPExcel wrapper for populating XLS files with user data  >  printform-xls.php  >  Download  
File: printform-xls.php
Role: Class source
Content type: text/plain
Description: Main class source
Class: PHPExcel wrapper for populating XLS files with user data
Generate Excel spreadsheets from templates
Author: By
Last change: new version (feature request)
Date: 12 years ago
Size: 15,992 bytes
 

Contents

Class file image Download
<?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