<?php
/*
********************************************************
TinyButStrong plug-in: Excel Spreadsheets
Version 1.1.0, on 2011-02-03, by Skrol29, for TBS version >= 3.6.2
********************************************************
*/
// Name of the class is a keyword used for Plug-In authentication. So i'ts better to save it into a constant.
define('TBS_EXCEL','clsTbsExcel');
define('TBS_EXCEL_FILENAME', 'clsTbsExcel.FILENAME'); // deprecated command (=1)
define('TBS_EXCEL_DOWNLOAD',1); // deprecated command (=2) & Render option (by default) = TBS_OUTPUT
define('TBS_EXCEL_INLINE',4); // deprecated command (=3) & Render option (do not use value 2 wich is reserved for TBS_EXIT)
define('TBS_EXCEL_FILE',8); // Render option
define('TBS_EXCEL_STRING',16); // Render option
class clsTbsExcel {
// TBS events -----------------------------
function OnInstall() {
$this->Version = '1.1.0'; // Version can be displayed using [onshow..tbs_info] since TBS 3.2.0
// Constants for the plug-in
$this->TypeLst = array('xlNum'=>'Number', 'xlDT'=>'DateTime', 'xlStr'=>'String', 'xlErr'=>'Error', 'xlBoo'=>'Boolean');
$this->TypeLst2 = array('number'=>'Number', 'date'=>'DateTime', 'string'=>'String', 'error'=>'Error', 'boolean'=>'Boolean');
// Usefulle properies
$this->Oldies_FileName = '';
$this->TemplateFileName = '';
return array('OnCommand','BeforeLoadTemplate','AfterShow','OnOperation','BeforeMergeBlock','AfterMergeBlock','OnCacheField');
}
function OnCommand($Cmd,$Value='') {
if ($Cmd==TBS_EXCEL_FILENAME) { // deprecated, compatibility with version 1.0
// Change file name
$this->Oldies_FileName = $Value;
} elseif ($Cmd==TBS_EXCEL_DOWNLOAD) { // deprecated, compatibility with version 1.0
// Force output to download file
$this->TBS->Render = TBS_EXCEL_DOWNLOAD;
} elseif ($Cmd==TBS_EXCEL_INLINE) { // deprecated, compatibility with version 1.0
// Enables output to display inline (Internet Exlorer only)
$this->TBS->Render = TBS_EXCEL_INLINE;
}
}
function BeforeLoadTemplate(&$File, &$Charset) {
if ($this->TemplateFileName==='') $this->TemplateFileName = $File;
if ($Charset==='') $this->TBS->LoadTemplate('', array(&$this,'ConvXmlUtf8')); // Define the function for string conversion
}
function OnOperation($FieldName,&$Value,&$PrmLst,&$Source,$PosBeg,$PosEnd,$Loc) {
if (isset($this->TypeLst[$PrmLst['ope']])) {
if (!isset($Loc->xlType)) $this->tag_ChangeCellType($Source,$Loc,$this->TypeLst[$PrmLst['ope']]);
} elseif ($PrmLst['ope']==='xlPushRef') {
$this->tag_ChangeFormula($Source,$Loc,$Value,true);
}
}
function OnCacheField($BlockName,&$Loc,&$Txt,$PrmProc) {
if (isset($Loc->PrmLst['ope'])) {
// in this event, ope parameter is not exploded yet
$ope_lst = explode(',', $Loc->PrmLst['ope']);
$ope_att = false;
foreach ($ope_lst as $ope) {
$ope=trim($ope,' ');
if (isset($this->TypeLst[$ope])) $ope_att = $this->TypeLst[$ope];
}
// if a the cell type must be modified, then we add a new TBS field for changing the attribute value
if ($ope_att!==false) {
$this->tag_ChangeCellFormat($Loc, $ope_att);
$newfield = '['.$BlockName.';att=Data#ss:Type;if 1=1;then '.$ope_att.']';
$Txt = substr_replace($Txt, $newfield, $Loc->PosEnd+1,0); // the new field is added after the current field so it will be scanned by TBS
$Loc->xlType = true; // avoid a double process of such ope values
}
}
}
function BeforeMergeBlock(&$TplSource,&$BlockBeg,&$BlockEnd,$PrmLst,&$DataSrc,&$LocR) {
/* Manage attribute ssIndex.
Attribute ssIndex can be put by Excel on the first <Row> and <Cell> item of a <Table> element.
But this attribute must be put only once per series, it must not be repeated on next items when it is merged for a block.
So if we have it (it must be on the first item) we save it, delete it, and then and add it only for the first item merged (see AfterMergeBlock).
*/
$this->ssIndex = false;
if ($LocR->SectionNbr>0) {
$Src1 =& $LocR->SectionLst[1]->Src ;
// Check if the first block begins with Row or Cell.
$Start = '';
if (substr($Src1,0,4)==='<Row') $Start='<Row' ;
if (substr($Src1,0,5)==='<Cell') $Start='<Cell' ;
if ($Start!=='') {
// The attribute ss:Index can be there.
// Search for the end of the tag
$p = strpos($Src1,'>');
if ($p!==false) {
$tag = substr($Src1,0,$p+1);
// Searche for attribute ss:Index
$p = strpos($tag,'ss:Index');
if ($p!==false) {
// ss:Index is found, we have to save it and take it off from the block's source
$p1 = strpos($tag,'"',$p);
if ($p1!==false) {
$p2 = strpos($tag,'"',$p1+1);
if ($p2!==false) {
$len = $p2 - $p + 1;
$this->ssIndex = true;
$this->ssIndexStart = $Start;
$this->ssIndexSource = substr($Src1,$p,$len);
$Src1 = substr_replace($Src1,str_repeat(' ',$len),$p,$len);
}
}
}
}
}
}
}
function AfterMergeBlock(&$Buffer,&$DataSrc,&$LocR) {
// Replace attribute ss:Index if necessary, only one time for the block.
if ($this->ssIndex) {
$len = strlen($this->ssIndexStart);
if (substr($Buffer,0,$len)===$this->ssIndexStart) {
$Buffer = substr_replace($Buffer,' '.$this->ssIndexSource,$len,0);
}
$this->ssIndex = false;
}
}
function AfterShow(&$Render, $File='') {
$TBS =& $this->TBS;
// Delete optional XML attributes that could become invalide after the merge
$this->tag_DelOptionalTableAtt();
if ( ($TBS->ErrCount>0) && (!$TBS->NoErr) ) {
$TBS->meth_Misc_Alert('Show() Method', 'The output is cancelled by the Excel plugin because at least one TBS error has occured.');
exit;
}
if ($File==='') {
if ($this->Oldies_FileName==='') {
if ($this->TemplateFileName==='') {
$File = 'result_'.date('Y-m-d').'.xml';
} else {
$File = basename($this->TemplateFileName);
}
} else {
$File = $this->Oldies_FileName;
}
}
// Makes a download instead of displaying the result.
if (($Render & TBS_OUTPUT)==TBS_OUTPUT) { // TBS_OUTPUT = TBS_EXCEL_DOWNLOAD
// content downloaded
$this->f_HttpHeader_Download($File);
} elseif (($Render & TBS_EXCEL_INLINE)==TBS_EXCEL_INLINE) {
// content displayed in the browser
$this->f_HttpHeader_Inline($File);
$Render = $Render - TBS_EXCEL_INLINE;
if (($Render & TBS_OUTPUT)!=TBS_OUTPUT) $Render = $Render + TBS_OUTPUT; // needed for final output
} elseif (($Render & TBS_EXCEL_FILE)==TBS_EXCEL_FILE) {
// content saved in a file
$hndl = fopen($File, 'w');
fwrite($hndl, $this->TBS->Source);
fclose($hndl);
} elseif (($Render & TBS_EXCEL_STRING)==TBS_EXCEL_STRING) {
// content returned in the TBS->Source property (nothing to do)
}
// the TBS::Show() method will then recover the process and perform the TBS_OUTPUT and TBS_EXIT options, if any
}
// --------------------------
// Functions for internal job
// --------------------------
function ConvXmlOnly($Txt, $ConvBr) {
// Used by TBS to convert special chars and new lines.
$x = htmlspecialchars($Txt);
if ($ConvBr) $this->ConvBr($x);
return $x;
}
function ConvXmlUtf8($Txt, $ConvBr) {
// Used by TBS to convert special chars and new lines.
$x = htmlspecialchars(utf8_encode($Txt));
if ($ConvBr) $this->ConvBr($x);
return $x;
}
function f_HttpHeader_Download($FileName) {
header ('Pragma: no-cache');
// header ('Content-type: application/x-msexcel');
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('Cache-Control: public');
header('Content-Description: File Transfer');
header('Content-Transfer-Encoding: binary');
header('Content-Length: '.strlen($this->TBS->Source));
}
function f_HttpHeader_Inline($FileName) {
header('Content-Type: application/x-msexcel; name="'.$FileName.'"');
header('Content-Disposition: inline; filename="'.$FileName.'"');
}
function tag_ChangeCellType(&$Txt, $Loc, $Type) {
// Change the cell type. This function assumes that the current cell has a type set to String. String type is expected since the cell contains a TBS tag.
if ($Type==='String') return true;
$t0 = clsTinyButStrong::f_Xml_FindTagStart($Txt,'Data',true,$Loc->PosBeg,false,true);
if ($t0===false) return false; // error in the XML structure
$te = strpos($Txt, '>', $t0);
if ( ($te===false) || ($te>$Loc->PosBeg) ) return false; // error in the XML structure
$len = $te - $t0 + 1;
$tag = substr($Txt, $t0, $len);
$len = strlen($tag);
$att = ' ss:Type="String"';
$att2 = ' ss:Type="'.$Type.'"';
$tag = str_replace($att, $att2, $tag);
$Txt = substr_replace($Txt, $tag, $t0, $len);
$diff = strlen($tag) - $len;
if ($diff!==0) {
$Loc->PosBeg += $diff;
$Loc->PosEnd += $diff;
}
$this->tag_ChangeCellFormat($Loc, $Type);
return true;
}
function tag_ChangeCellFormat(&$Loc, $Type) {
if ($Type==='DateTime') {
$Loc->PrmLst['frm'] = 'yyyy-mm-ddThh:nn:ss';
// activate the FRM mode
$Loc->ConvMode = 0;
$Loc->ConvProtect = false;
}
}
function tag_ChangeFormula(&$Txt,&$Loc,&$Value,$First) {
// The process assumes that the TBS is embeded into a N("") function
if (isset($Loc->xlExend)) {
if (!$Loc->xlExend) return false;
} else {
$Loc->xlExend = false;
// So we first search for the begining of the TBS expression. Going backward.
$p = $Loc->PosBeg - 1;
$cont = true;
$exp_n = false; // true if N is met
$exp_beg = false; //
do {
$x = $Txt[$p];
if ($p<2) {
$cont = false;
} elseif ($x==='"') {
$cont = false; // end of the formula
} elseif ($x==='(') { // begining of the function's arguments
$exp_n = true;
} elseif (($x==='+') or ($x==='&')) { // The expression can be added with + or & (wich is saved as &)
if ($exp_n) {
$exp_beg = $p;
$cont = false;
}
}
if ($cont) $p--;
} while ($cont);
if ($exp_beg===false) return false;
// Search for the end of the TBS expression.
$frm_end = strpos($Txt,'"',$Loc->PosEnd+1);
if ($frm_end===false) return false;
$exp_end = strpos($Txt,')',$Loc->PosEnd+1);
if ($exp_end===false) return false;
if ($exp_end>$frm_end) return false;
// Now we searching backard, the relative cell
$cont = true;
$br_o = false; // position of [
$br_c = false; // position of ]
$p = $exp_beg - 1;
do {
$x = $Txt[$p];
if ($p<2) {
$cont = false;
} elseif ($x===']') {
if ($br_o===false) $br_c = $p;
} elseif ($x==='[') {
if ($br_c!==false) $br_o = $p;
$cont=false;
} elseif ($x==='"') {
$cont=false;
}
if ($cont) $p--;
} while ($cont);
if ($br_o===false) return false;
// Calculate the relative index
$x = intval(substr($Txt,$br_o+1,$br_c-$br_o-1));
if ($x==0) return false;
// Save information in the locator, useful for cached locators
$Loc->xlVal = $x;
$Loc->xlExtraStr = substr($Txt,$br_c,$exp_beg-$br_c);
$Loc->xlExend = true;
$Loc->PosBeg = $br_o + 1;
$Loc->PosEnd = $exp_end;
$Loc->ConvMode = -1; // No contents conversion
}
// Calculate the new index
$v = intval($Value);
$x = $Loc->xlVal;
if ($x!=0) $x = $x + $v - 1;
// Replace the index value
$Value = strval($x).$Loc->xlExtraStr;
}
function tag_DelOptionalTableAtt() {
// Two Table attributes give the size of the zone, this zone may be extended after a MergeBlock()
// Hopefully, those attributes are optional, so we delete them.
$Txt =& $this->TBS->Source;
$att_lst = array('ss:ExpandedColumnCount', 'ss:ExpandedRowCount');
$Pos = 0;
while ( ($Loc=clsTinyButStrong::f_Xml_FindTag($Txt,'Table',true,$Pos,true,false,true,true))!==false ) {
foreach ($att_lst as $att) {
// delete the attributes by replacing them with spaces
$att = strtolower($att); // The TBS method does turn attributes anmes into lowercase.
if (isset($Loc->PrmPos[$att])) {
$p = $Loc->PrmPos[$att];
$n = $p[3] - $p[0] + 1;
$Txt = substr_replace($Txt, str_repeat(' ',$n), $p[0], $n);
}
}
$Pos = $Loc->PosEnd+1;
}
}
}
|
Download
