<?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
|
Download
