3 * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
5 * The majority of this is _NOT_ my code. I simply ported it from the
6 * PERL Spreadsheet::WriteExcel module.
8 * The author of the Spreadsheet::WriteExcel module is John McNamara
11 * I _DO_ maintain this code, and John McNamara has nothing to do with the
12 * porting of this code to PHP. Any questions directly related to this
13 * class library should be directed to me.
15 * License Information:
17 * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
18 * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
20 * This library is free software; you can redistribute it and/or
21 * modify it under the terms of the GNU Lesser General Public
22 * License as published by the Free Software Foundation; either
23 * version 2.1 of the License, or (at your option) any later version.
25 * This library is distributed in the hope that it will be useful,
26 * but WITHOUT ANY WARRANTY; without even the implied warranty of
27 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28 * Lesser General Public License for more details.
30 * You should have received a copy of the GNU Lesser General Public
31 * License along with this library; if not, write to the Free Software
32 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
36 * @const SPREADSHEET_EXCEL_WRITER_ADD token identifier for character "+"
38 define('SPREADSHEET_EXCEL_WRITER_ADD', "+");
41 * @const SPREADSHEET_EXCEL_WRITER_SUB token identifier for character "-"
43 define('SPREADSHEET_EXCEL_WRITER_SUB', "-");
46 * @const SPREADSHEET_EXCEL_WRITER_MUL token identifier for character "*"
48 define('SPREADSHEET_EXCEL_WRITER_MUL', "*");
51 * @const SPREADSHEET_EXCEL_WRITER_DIV token identifier for character "/"
53 define('SPREADSHEET_EXCEL_WRITER_DIV', "/");
56 * @const SPREADSHEET_EXCEL_WRITER_OPEN token identifier for character "("
58 define('SPREADSHEET_EXCEL_WRITER_OPEN', "(");
61 * @const SPREADSHEET_EXCEL_WRITER_CLOSE token identifier for character ")"
63 define('SPREADSHEET_EXCEL_WRITER_CLOSE', ")");
66 * @const SPREADSHEET_EXCEL_WRITER_COMA token identifier for character ","
68 define('SPREADSHEET_EXCEL_WRITER_COMA', ",");
71 * @const SPREADSHEET_EXCEL_WRITER_SEMICOLON token identifier for character ";"
73 define('SPREADSHEET_EXCEL_WRITER_SEMICOLON', ";");
76 * @const SPREADSHEET_EXCEL_WRITER_GT token identifier for character ">"
78 define('SPREADSHEET_EXCEL_WRITER_GT', ">");
81 * @const SPREADSHEET_EXCEL_WRITER_LT token identifier for character "<"
83 define('SPREADSHEET_EXCEL_WRITER_LT', "<");
86 * @const SPREADSHEET_EXCEL_WRITER_LE token identifier for character "<="
88 define('SPREADSHEET_EXCEL_WRITER_LE', "<=");
91 * @const SPREADSHEET_EXCEL_WRITER_GE token identifier for character ">="
93 define('SPREADSHEET_EXCEL_WRITER_GE', ">=");
96 * @const SPREADSHEET_EXCEL_WRITER_EQ token identifier for character "="
98 define('SPREADSHEET_EXCEL_WRITER_EQ', "=");
101 * @const SPREADSHEET_EXCEL_WRITER_NE token identifier for character "<>"
103 define('SPREADSHEET_EXCEL_WRITER_NE', "<>");
105 define('PpsType_Root', 5);
106 define('PpsType_Dir', 1);
107 define('PpsType_File', 2);
108 define('DataSizeSmall', 0x1000);
109 define('LongIntSize', 4);
110 define('PpsSize', 0x80);
112 function Asc2Ucs($sAsc)
114 return implode("\x00", (preg_split('//', $sAsc, -1, PREG_SPLIT_NO_EMPTY)))."\x00";
117 function Ucs2Asc($sUcs)
119 $chars=explode("\x00", $sUcs);
121 return implode("", $chars);
124 function OLEDate2Local($sDateTime)
128 #------------------------------------------------------------------------------
129 # Localtime->OLE Date
130 #------------------------------------------------------------------------------
131 function LocalDate2OLE($raDate)
135 function _leapYear($iYear)
137 return ((($iYear % 4)==0) && (($iYear % 100) || ($iYear % 400)==0)) ? 1 : 0;
140 function _yearDays($iYear)
142 return _leapYear($iYear) ? 366 : 365;
145 function _monthDays($iMon, $iYear)
147 if ($iMon == 1 || $iMon == 3 || $iMon == 5 || $iMon == 7 ||
148 $iMon == 8 || $iMon == 10 || $iMon == 12)
150 elseif ($iMon == 4 || $iMon == 6 || $iMon == 9 || $iMon == 11)
153 return _leapYear($iYear) ? 29 : 28;
157 * This is the OLE::Storage_Lite Perl package ported to PHP
158 * OLE::Storage_Lite was written by Kawai Takanori, kwitknr@cpan.org
177 #------------------------------------------------------------------------------
178 # _new (OLE::Storage_Lite::PPS)
179 # for OLE::Storage_Lite
180 #------------------------------------------------------------------------------
181 function __construct($iNo, $sNm, $iType, $iPrev, $iNext, $iDir,
182 $raTime1st, $raTime2nd, $iStart, $iSize,
183 $sData=false, $raChild=false)
185 #1. Constructor for OLE::Storage_Lite
189 $this->Type = $iType;
190 $this->PrevPps = $iPrev;
191 $this->NextPps = $iNext;
192 $this->DirPps = $iDir;
193 $this->Time1st = $raTime1st;
194 $this->Time2nd = $raTime2nd;
195 $this->StartBlock = $iStart;
196 $this->Size = $iSize;
197 $this->Data = $sData;
198 $this->Child = $raChild;
199 $this->_PPS_FILE = NULL;
202 #------------------------------------------------------------------------------
203 # _DataLen (OLE::Storage_Lite::PPS)
205 #------------------------------------------------------------------------------
208 if ($this->Data===false)
211 if ($this->_PPS_FILE)
212 return filesize($this->_PPS_FILE);
214 return strlen($this->Data);
217 #------------------------------------------------------------------------------
218 # _makeSmallData (OLE::Storage_Lite::PPS)
219 #------------------------------------------------------------------------------
220 function _makeSmallData(&$aList, $rhInfo)
223 $FILE = $rhInfo->_FILEH_;
227 for ($c=0;$c<sizeof($aList);$c++)
231 #1. Make SBD, small data string
233 if ($oPps->Type==PpsType_File)
238 if($oPps->Size < $rhInfo->_SMALL_SIZE)
240 $iSmbCnt = floor($oPps->Size / $rhInfo->_SMALL_BLOCK_SIZE) +
241 (($oPps->Size % $rhInfo->_SMALL_BLOCK_SIZE) ? 1 : 0);
243 for ($i = 0; $i<($iSmbCnt-1); $i++)
244 fputs($FILE, pack("V", $i+$iSmBlk+1));
245 fputs($FILE, pack("V", -2));
247 #1.2 Add to Data String(this will be written for RootEntry)
249 if ($oPps->_PPS_FILE)
252 fseek($oPps->_PPS_FILE, 0, SEEK_SET); #To The Top
253 while ($sBuff=fread($oPps->_PPS_FILE, 4096))
257 $sRes .= $oPps->Data;
258 if($oPps->Size % $rhInfo->_SMALL_BLOCK_SIZE)
260 $sRes .= (str_repeat("\x00",
261 ($rhInfo->_SMALL_BLOCK_SIZE -
262 ($oPps->Size % $rhInfo->_SMALL_BLOCK_SIZE))));
265 $oPps->StartBlock = $iSmBlk;
271 $iSbCnt = floor($rhInfo->_BIG_BLOCK_SIZE / LongIntSize);
272 if ($iSmBlk % $iSbCnt)
273 fputs($FILE, str_repeat(pack("V", -1), $iSbCnt - ($iSmBlk % $iSbCnt)));
274 #2. Write SBD with adjusting length for block
278 #------------------------------------------------------------------------------
279 # _savePpsWk (OLE::Storage_Lite::PPS)
280 #------------------------------------------------------------------------------
281 function _savePpsWk($rhInfo)
284 $FILE=$rhInfo->_FILEH_;
287 str_repeat("\x00", 64 - strlen($this->Name)). # 64
288 pack("v", strlen($this->Name) + 2). # 66
289 pack("c", $this->Type). # 67
290 pack("c", 0x00). #UK # 68
291 pack("V", $this->PrevPps). #Prev # 72
292 pack("V", $this->NextPps). #Next # 76
293 pack("V", $this->DirPps). #Dir # 80
294 "\x00\x09\x02\x00". # 84
295 "\x00\x00\x00\x00". # 88
296 "\xc0\x00\x00\x00". # 92
297 "\x00\x00\x00\x46". # 96
298 "\x00\x00\x00\x00". # 100
299 "\x00\x00\x00\x00\x00\x00\x00\x00".
300 "\x00\x00\x00\x00\x00\x00\x00\x00".
301 pack("V", ($this->StartBlock!==false) ?
302 $this->StartBlock : 0). # 120
303 pack("V", ($this->Size!==false) ?
304 $this->Size : 0). # 124
312 * This is the OLE::Storage_Lite Perl package ported to PHP
313 * OLE::Storage_Lite was written by Kawai Takanori, kwitknr@cpan.org
316 class ole_pps_file extends ole_pps
318 function __construct($sNm, $sData=false, $sFile=false)
322 $this->Type = PpsType_File;
323 $this->PrevPps = false;
324 $this->NextPps = false;
325 $this->DirPps = false;
326 $this->Time1st = false;
327 $this->Time2nd = false;
328 $this->StartBlock = false;
330 $this->Data = ($sFile===false) ? $sData : '';
331 $this->Child = false;
335 if (is_ressource($sFile))
336 $this->_PPS_FILE=$sFile;
339 $fname=tempnam("php_ole");
340 $this->_PPS_FILE=fopen($fname, "r+b");
345 $this->_PPS_FILE=fopen($fname, "r+b");
349 fputs($this->_PPS_FILE, $sData);
353 function append ($sData)
355 if ($this->_PPS_FILE)
356 fputs($this->_PPS_FILE, $sData);
363 * This is the OLE::Storage_Lite Perl package ported to PHP
364 * OLE::Storage_Lite was written by Kawai Takanori, kwitknr@cpan.org
367 class ole_pps_root extends ole_pps
369 function __construct($raTime1st=false, $raTime2nd=false, $raChild=false)
372 $this->Name = Asc2Ucs('Root Entry');
373 $this->Type = PpsType_Root;
374 $this->PrevPps = false;
375 $this->NextPps = false;
376 $this->DirPps = false;
377 $this->Time1st = $raTime1st;
378 $this->Time2nd = $raTime2nd;
379 $this->StartBlock = false;
382 $this->Child = $raChild;
385 #------------------------------------------------------------------------------
386 # save (OLE::Storage_Lite::PPS::Root)
387 #------------------------------------------------------------------------------
388 function save($sFile, $bNoAs=false, $rhInfo=false)
390 #0.Initial Setting for saving
393 $rhInfo=new stdClass();
395 $rhInfo->_BIG_BLOCK_SIZE = $rhInfo->_SMALL_BLOCK_SIZE = 0;
396 $rhInfo->_BIG_BLOCK_SIZE=pow(2, (($rhInfo->_BIG_BLOCK_SIZE) ?
397 _adjust2($rhInfo->_BIG_BLOCK_SIZE) : 9));
398 $rhInfo->_SMALL_BLOCK_SIZE=pow(2, (($rhInfo->_SMALL_BLOCK_SIZE) ?
399 _adjust2($rhInfo->_SMALL_BLOCK_SIZE) : 6));
400 $rhInfo->_SMALL_SIZE = 0x1000;
401 $rhInfo->_PPS_SIZE = 0x80;
404 #1.1 $sFile is Ref of scalar
405 if(is_resource($sFile))
408 $rhInfo->_FILEH_ = $oIo;
410 #1.2 $sFile is a simple filename string
413 $oIo=fopen("$sFile", "wb");
414 $rhInfo->_FILEH_ = $oIo;
418 #1. Make an array of PPS (for Save)
422 $this->_savePpsSetPnt2($list, $aList, $rhInfo);
424 $this->_savePpsSetPnt($list, $aList, $rhInfo);
425 list($iSBDcnt, $iBBcnt, $iPPScnt) = $this->_calcSize($aList, $rhInfo);
427 $this->_saveHeader($rhInfo, $iSBDcnt, $iBBcnt, $iPPScnt);
429 #3.Make Small Data string (write SBD)
430 $sSmWk = $this->_makeSmallData($aList, $rhInfo);
431 $this->Data = $sSmWk; #Small Datas become RootEntry Data
435 $this->_saveBigData($iBBlk, $aList, $rhInfo);
437 $this->_savePps($aList, $rhInfo);
438 #6. Write BD and BDList and Adding Header informations
439 $this->_saveBbd($iSBDcnt, $iBBcnt, $iPPScnt, $rhInfo);
441 fclose($rhInfo->_FILEH_);
444 #------------------------------------------------------------------------------
445 # _calcSize (OLE::Storage_Lite::PPS)
446 #------------------------------------------------------------------------------
447 function _calcSize(&$raList, $rhInfo)
449 #0. Calculate Basic Setting
456 for ($c=0;$c<sizeof($raList);$c++)
460 if($oPps->Type==PpsType_File)
462 $oPps->Size = $oPps->_DataLen(); #Mod
463 if($oPps->Size < $rhInfo->_SMALL_SIZE)
465 $iSBcnt += floor($oPps->Size / $rhInfo->_SMALL_BLOCK_SIZE) +
466 (($oPps->Size % $rhInfo->_SMALL_BLOCK_SIZE) ? 1 : 0);
471 (floor($oPps->Size/ $rhInfo->_BIG_BLOCK_SIZE) +
472 (($oPps->Size % $rhInfo->_BIG_BLOCK_SIZE)? 1: 0));
476 $iSmallLen = $iSBcnt * $rhInfo->_SMALL_BLOCK_SIZE;
477 $iSlCnt = floor($rhInfo->_BIG_BLOCK_SIZE / LongIntSize);
478 $iSBDcnt = floor($iSBcnt / $iSlCnt)+ (($iSBcnt % $iSlCnt) ? 1 : 0);
479 $iBBcnt += (floor($iSmallLen/ $rhInfo->_BIG_BLOCK_SIZE) +
480 (( $iSmallLen% $rhInfo->_BIG_BLOCK_SIZE) ? 1 : 0));
481 $iCnt = sizeof($raList);
482 $iBdCnt = $rhInfo->_BIG_BLOCK_SIZE/PpsSize;
483 $iPPScnt = (floor($iCnt/$iBdCnt) + (($iCnt % $iBdCnt) ? 1 : 0));
485 return array($iSBDcnt, $iBBcnt, $iPPScnt);
488 #------------------------------------------------------------------------------
489 # _adjust2 (OLE::Storage_Lite::PPS::Root)
490 #------------------------------------------------------------------------------
491 function _adjust2($i2)
493 $iWk = log($i2)/log(2);
494 return ($iWk > int($iWk)) ? floor($iWk)+1 : $iWk;
497 #------------------------------------------------------------------------------
498 # _saveHeader (OLE::Storage_Lite::PPS::Root)
499 #------------------------------------------------------------------------------
500 function _saveHeader($rhInfo, $iSBDcnt, $iBBcnt, $iPPScnt)
502 $FILE = $rhInfo->_FILEH_;
504 #0. Calculate Basic Setting
505 $iBlCnt = $rhInfo->_BIG_BLOCK_SIZE / LongIntSize;
506 $i1stBdL = ($rhInfo->_BIG_BLOCK_SIZE - 0x4C) / LongIntSize;
509 $iAll = $iBBcnt + $iPPScnt + $iSBDcnt;
511 $iBdCntW = floor($iAllW / $iBlCnt) + (($iAllW % $iBlCnt) ? 1 : 0);
512 $iBdCnt = floor(($iAll + $iBdCntW) / $iBlCnt) + ((($iAllW+$iBdCntW) % $iBlCnt) ? 1 : 0);
515 #0.1 Calculate BD count
516 if ($iBdCnt > $i1stBdL)
518 // TODO: is do-while correct here?
523 $iBdCntW = floor($iAllW / $iBlCnt) + (($iAllW % $iBlCnt) ? 1 : 0);
524 $iBdCnt = floor(($iAllW + $iBdCntW) / $iBlCnt) + ((($iAllW+$iBdCntW) % $iBlCnt) ? 1 : 0);
526 while($iBdCnt > ($iBdExL*$iBlCnt+ $i1stBdL));
531 "\xD0\xCF\x11\xE0\xA1\xB1\x1A\xE1".
545 pack("V", $iBBcnt+$iSBDcnt). #ROOT START
548 pack("V", 0). #Small Block Depot
551 #2. Extra BDList Start, Count
552 if($iBdCnt < $i1stBdL)
555 pack("V", -2). #Extra BDList Start
556 pack("V", 0) #Extra BDList Count
562 pack("V", $iAll+$iBdCnt).
568 for ($i=0;($i<$i1stBdL) && ($i < $iBdCnt); $i++)
569 fputs($FILE, pack("V", $iAll+$i));
572 // TODO: Check, if str_repeat is binary safe
573 fputs($FILE, str_repeat((pack("V", -1)), ($i1stBdL-$i)));
577 #------------------------------------------------------------------------------
578 # _saveBigData (OLE::Storage_Lite::PPS)
579 #------------------------------------------------------------------------------
580 function _saveBigData(&$iStBlk, &$raList, $rhInfo)
586 $FILE = $rhInfo->_FILEH_;
588 #1.Write Big (ge 0x1000) Data into Block
589 for ($c=0;$c<sizeof($raList);$c++)
592 if($oPps->Type!=PpsType_Dir)
594 #print "PPS: $oPps DEF:", defined($oPps->{Data}), "\n";
595 $oPps->Size = $oPps->_DataLen(); #Mod
596 if(($oPps->Size >= $rhInfo->_SMALL_SIZE) ||
597 (($oPps->Type == PpsType_Root) && $oPps->Data!==false))
605 fseek($oPps->_PPS_FILE, 0, SEEK_SET); #To The Top
606 while ($sBuff=fread($oPps->_PPS_FILE, 4096))
608 $iLen += length($sBuff);
609 fputs($FILE, $sBuff); #Check for update
613 fputs($FILE, $oPps->Data);
614 if ($oPps->Size % $rhInfo->_BIG_BLOCK_SIZE)
616 // TODO: Check, if str_repeat() is binary safe
617 fputs($FILE, str_repeat("\x00",
618 ($rhInfo->_BIG_BLOCK_SIZE -
619 ($oPps->Size % $rhInfo->_BIG_BLOCK_SIZE)))
623 $oPps->StartBlock = $iStBlk;
625 (floor($oPps->Size/ $rhInfo->_BIG_BLOCK_SIZE) +
626 (($oPps->Size % $rhInfo->_BIG_BLOCK_SIZE) ? 1 : 0));
632 #------------------------------------------------------------------------------
633 # _savePps (OLE::Storage_Lite::PPS::Root)
634 #------------------------------------------------------------------------------
635 function _savePps(&$raList, $rhInfo)
638 $FILE = $rhInfo->_FILEH_;
640 for ($c=0;$c<sizeof($raList);$c++)
643 $oItem->_savePpsWk($rhInfo);
646 $iCnt = sizeof($raList);
647 $iBCnt = $rhInfo->_BIG_BLOCK_SIZE / $rhInfo->_PPS_SIZE;
649 fputs($FILE, str_repeat("\x00", (($iBCnt - ($iCnt % $iBCnt)) * $rhInfo->_PPS_SIZE)));
650 return (floor($iCnt / $iBCnt) + (($iCnt % $iBCnt) ? 1 : 0));
653 #------------------------------------------------------------------------------
654 # _savePpsSetPnt2 (OLE::Storage_Lite::PPS::Root)
656 #------------------------------------------------------------------------------
657 function _savePpsSetPnt2(&$aThis, &$raList, $rhInfo)
659 #1. make Array as Children-Relations
661 if (!is_array($aThis) || sizeof($aThis)==0)
663 elseif (sizeof($aThis)==1)
666 array_push($raList, $aThis[0]);
667 $aThis[0]->No = sizeof($raList)-1;
668 $aThis[0]->PrevPps = 0xFFFFFFFF;
669 $aThis[0]->NextPps = 0xFFFFFFFF;
670 $aThis[0]->DirPps = $this->_savePpsSetPnt2($aThis[0]->Child, $raList, $rhInfo);
671 return $aThis[0]->No;
676 $iCnt = sizeof($aThis);
678 $iPos = 0; #int($iCnt/ 2); #$iCnt
681 $aPrev = (sizeof($aThis) > 2) ? array_splice($aWk, 1, 1) : array(); #$iPos);
682 $aNext = array_splice($aWk, 1); #, $iCnt - $iPos -1);
683 $aThis[$iPos]->PrevPps = $this->_savePpsSetPnt2($aPrev, $raList, $rhInfo);
684 array_push($raList, $aThis[$iPos]);
685 $aThis[$iPos]->No = sizeof($raList)-1;
687 #1.3.2 Devide a array into Previous,Next
688 $aThis[$iPos]->NextPps = $this->_savePpsSetPnt2($aNext, $raList, $rhInfo);
689 $aThis[$iPos]->DirPps = $this->_savePpsSetPnt2($aThis[$iPos]->Child, $raList, $rhInfo);
690 return $aThis[$iPos]->No;
694 #------------------------------------------------------------------------------
695 # _savePpsSetPnt2 (OLE::Storage_Lite::PPS::Root)
697 #------------------------------------------------------------------------------
698 function _savePpsSetPnt2s(&$aThis, &$raList, $rhInfo)
700 #1. make Array as Children-Relations
702 if (!is_array($aThis) || sizeof($aThis)==0)
704 elseif (sizeof($aThis)==1)
707 array_push($raList, $aThis[0]);
708 $aThis[0]->No = sizeof($raList)-1;
709 $aThis[0]->PrevPps = 0xFFFFFFFF;
710 $aThis[0]->NextPps = 0xFFFFFFFF;
711 $aThis[0]->DirPps = $this->_savePpsSetPnt2($aThis[0]->Child, $raList, $rhInfo);
712 return $aThis[0]->No;
717 $iCnt = sizeof($aThis);
719 $iPos = 0; #int($iCnt/ 2); #$iCnt
720 array_push($raList, $aThis[$iPos]);
721 $aThis[$iPos]->No = sizeof($raList)-1;
723 #1.3.2 Devide a array into Previous,Next
724 $aPrev = array_splice($aWk, 0, $iPos);
725 $aNext = array_splice($aWk, 1, $iCnt - $iPos - 1);
726 $aThis[$iPos]->PrevPps = $this->_savePpsSetPnt2($aPrev, $raList, $rhInfo);
727 $aThis[$iPos]->NextPps = $this->_savePpsSetPnt2($aNext, $raList, $rhInfo);
728 $aThis[$iPos]->DirPps = $this->_savePpsSetPnt2($aThis[$iPos]->Child, $raList, $rhInfo);
729 return $aThis[$iPos]->No;
733 #------------------------------------------------------------------------------
734 # _savePpsSetPnt (OLE::Storage_Lite::PPS::Root)
735 #------------------------------------------------------------------------------
736 function _savePpsSetPnt(&$aThis, &$raList, $rhInfo)
739 //print "yyy type: ".gettype($aThis)."<br>\n";
740 //print "yyy name: ".$aThis[0]->Name."<br>\n";
742 #1. make Array as Children-Relations
744 if (!is_array($aThis) || sizeof($aThis)==0)
748 elseif (sizeof($aThis)==1)
751 array_push($raList, $aThis[0]);
752 $aThis[0]->No = sizeof($raList)-1;
753 $aThis[0]->PrevPps = 0xFFFFFFFF;
754 $aThis[0]->NextPps = 0xFFFFFFFF;
755 $aThis[0]->DirPps = $this->_savePpsSetPnt($aThis[0]->Child, $raList, $rhInfo);
756 return $aThis[0]->No;
761 $iCnt = sizeof($aThis);
763 $iPos = floor($iCnt/2); #$iCnt
764 array_push($raList, $aThis[$iPos]);
765 $aThis[$iPos]->No = sizeof($raList)-1;
767 #1.3.2 Devide a array into Previous,Next
768 $aPrev = splice($aWk, 0, $iPos);
769 $aNext = splice($aWk, 1, $iCnt - $iPos - 1);
770 $aThis[$iPos]->PrevPps = $this->_savePpsSetPnt($aPrev, $raList, $rhInfo);
771 $aThis[$iPos]->NextPps = $this->_savePpsSetPnt($aNext, $raList, $rhInfo);
772 $aThis[$iPos]->DirPps = $this->_savePpsSetPnt($aThis[$iPos]->Child, $raList, $rhInfo);
773 return $aThis[$iPos]->No;
777 #------------------------------------------------------------------------------
778 # _savePpsSetPnt (OLE::Storage_Lite::PPS::Root)
779 #------------------------------------------------------------------------------
780 function _savePpsSetPnt1(&$aThis, &$raList, $rhInfo)
782 #1. make Array as Children-Relations
784 if (!is_array($aThis) || sizeof($aThis)==0)
788 elseif (sizeof($aThis)==1)
791 array_push($raList, $aThis[0]);
792 $aThis[0]->No = sizeof($raList)-1;
793 $aThis[0]->PrevPps = 0xFFFFFFFF;
794 $aThis[0]->NextPps = 0xFFFFFFFF;
795 $aThis[0]->DirPps = $this->_savePpsSetPnt($aThis[0]->Child, $raList, $rhInfo);
796 return $aThis[0]->No;
801 $iCnt = sizeof($aThis);
803 $iPos = floor($iCnt / 2); #$iCnt
804 array_push($raList, $aThis[$iPos]);
805 $aThis[$iPos]->No = sizeof($raList)-1;
807 #1.3.2 Devide a array into Previous,Next
808 $aPrev = splice($aWk, 0, $iPos);
809 $aNext = splice($aWk, 1, $iCnt - $iPos - 1);
810 $aThis[$iPos]->PrevPps = $this->_savePpsSetPnt($aPrev, $raList, $rhInfo);
811 $aThis[$iPos]->NextPps = $this->_savePpsSetPnt($aNext, $raList, $rhInfo);
812 $aThis[$iPos]->DirPps = $this->_savePpsSetPnt($aThis[$iPos]->Child, $raList, $rhInfo);
813 return $aThis[$iPos]->No;
817 #------------------------------------------------------------------------------
818 # _saveBbd (OLE::Storage_Lite)
819 #------------------------------------------------------------------------------
820 function _saveBbd($iSbdSize, $iBsize, $iPpsCnt, $rhInfo)
822 $FILE = $rhInfo->_FILEH_;
823 #0. Calculate Basic Setting
824 $iBbCnt = $rhInfo->_BIG_BLOCK_SIZE / LongIntSize;
825 $i1stBdL = ($rhInfo->_BIG_BLOCK_SIZE - 0x4C) / LongIntSize;
828 $iAll = $iBsize + $iPpsCnt + $iSbdSize;
830 $iBdCntW = floor($iAllW / $iBbCnt) + (($iAllW % $iBbCnt) ? 1 : 0);
831 $iBdCnt = floor(($iAll + $iBdCntW) / $iBbCnt) + ((($iAllW+$iBdCntW) % $iBbCnt)? 1: 0);
833 #0.1 Calculate BD count
834 if ($iBdCnt >$i1stBdL)
836 // TODO: do-while correct here?
841 $iBdCntW = floor($iAllW / $iBbCnt) + (($iAllW % $iBbCnt) ? 1 : 0);
842 $iBdCnt = floor(($iAllW + $iBdCntW) / $iBbCnt) + ((($iAllW+$iBdCntW) % $iBbCnt) ? 1 : 0);
844 while ($iBdCnt > ($iBdExL*$iBbCnt+$i1stBdL));
851 for ($i = 0; $i<($iSbdSize-1); $i++)
852 fputs($FILE, pack("V", $i+1));
853 fputs($FILE, pack("V", -2));
856 for ($i = 0; $i<($iBsize-1); $i++)
857 fputs($FILE, pack("V", $i+$iSbdSize+1));
858 fputs($FILE, pack("V", -2));
861 for ($i = 0; $i<($iPpsCnt-1); $i++)
862 fputs($FILE, pack("V", $i+$iSbdSize+$iBsize+1));
863 fputs($FILE, pack("V", -2));
864 #1.4 Set for BBD itself ( 0xFFFFFFFD : BBD)
865 for ($i=0; $i<$iBdCnt;$i++)
866 fputs($FILE, pack("V", 0xFFFFFFFD));
867 #1.5 Set for ExtraBDList
868 for ($i=0; $i<$iBdExL;$i++)
869 fputs($FILE, pack("V", 0xFFFFFFFC));
870 #1.6 Adjust for Block
871 if(($iAllW + $iBdCnt) % $iBbCnt)
872 fputs($FILE, str_repeat(pack("V", -1), ($iBbCnt - (($iAllW + $iBdCnt) % $iBbCnt))));
875 if($iBdCnt > $i1stBdL)
879 for ($i=$i1stBdL;$i<$iBdCnt; $i++, $iN++)
885 fputs($FILE, pack("V", $iAll+$iBdCnt+$iNb));
887 fputs($FILE, pack("V", $iBsize+$iSbdSize+$iPpsCnt+$i));
889 if(($iBdCnt-$i1stBdL) % ($iBbCnt-1))
890 fputs($FILE, str_repeat(pack("V", -1), (($iBbCnt-1) - (($iBdCnt-$i1stBdL) % ($iBbCnt-1)))));
891 fputs($FILE, pack("V", -2));
897 * Class for writing Excel BIFF records.
899 * From "MICROSOFT EXCEL BINARY FILE FORMAT" by Mark O'Brien (Microsoft Corporation):
901 * BIFF (BInary File Format) is the file format in which Excel documents are
902 * saved on disk. A BIFF file is a complete description of an Excel document.
903 * BIFF files consist of sequences of variable-length records. There are many
904 * different types of BIFF records. For example, one record type describes a
905 * formula entered into a cell; one describes the size and location of a
906 * window into a document; another describes a picture format.
908 * @author Xavier Noguer <xnoguer@php.net>
909 * @category FileFormats
910 * @package Spreadsheet_Excel_Writer
913 class Spreadsheet_Excel_Writer_BIFFwriter
916 * The BIFF/Excel version (5).
919 var $_BIFF_version = 0x0500;
922 * The byte order of this architecture. 0 => little endian, 1 => big endian
928 * The string containing the data of the BIFF stream
934 * The size of the data in bytes. Should be the same as strlen($this->_data)
940 * The maximun length for a BIFF record. See _addContinue()
942 * @see _addContinue()
952 function __construct()
954 $this->_byte_order = '';
956 $this->_datasize = 0;
957 $this->_limit = 2080;
958 // Set the byte order
959 $this->_setByteOrder();
963 * Determine the byte order and store it as class data to avoid
964 * recalculating it for each call to new().
968 function _setByteOrder()
970 // Check if "pack" gives the required IEEE 64bit float
971 $teststr = pack("d", 1.2345);
972 $number = pack("C8", 0x8D, 0x97, 0x6E, 0x12, 0x83, 0xC0, 0xF3, 0x3F);
973 if ($number == $teststr) {
974 $byte_order = 0; // Little Endian
975 } elseif ($number == strrev($teststr)){
976 $byte_order = 1; // Big Endian
978 // Give up. I'll fix this in a later version.
979 die("Required floating point format ".
980 "not supported on this platform.");
982 $this->_byte_order = $byte_order;
986 * General storage function
988 * @param string $data binary data to prepend
991 function _prepend($data)
993 if (strlen($data) > $this->_limit) {
994 $data = $this->_addContinue($data);
996 $this->_data = $data.$this->_data;
997 $this->_datasize += strlen($data);
1001 * General storage function
1003 * @param string $data binary data to append
1006 function _append($data)
1008 if (strlen($data) > $this->_limit) {
1009 $data = $this->_addContinue($data);
1011 $this->_data = $this->_data.$data;
1012 $this->_datasize += strlen($data);
1016 * Writes Excel BOF record to indicate the beginning of a stream or
1017 * sub-stream in the BIFF file.
1019 * @param integer $type Type of BIFF file to write: 0x0005 Workbook,
1023 function _storeBof($type)
1025 $record = 0x0809; // Record identifier
1027 // According to the SDK $build and $year should be set to zero.
1028 // However, this throws a warning in Excel 5. So, use magic numbers.
1029 if ($this->_BIFF_version == 0x0500) {
1034 } elseif ($this->_BIFF_version == 0x0600) {
1036 $unknown = pack("VV", 0x00000041, 0x00000006); //unknown last 8 bytes for BIFF8
1040 $version = $this->_BIFF_version;
1042 $header = pack("vv", $record, $length);
1043 $data = pack("vvvv", $version, $type, $build, $year);
1044 $this->_prepend($header . $data . $unknown);
1048 * Writes Excel EOF record to indicate the end of a BIFF stream.
1052 function _storeEof()
1054 $record = 0x000A; // Record identifier
1055 $length = 0x0000; // Number of bytes to follow
1056 $header = pack("vv", $record, $length);
1057 $this->_append($header);
1061 * Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In
1062 * Excel 97 the limit is 8228 bytes. Records that are longer than these limits
1063 * must be split up into CONTINUE blocks.
1065 * This function takes a long BIFF record and inserts CONTINUE records as
1068 * @param string $data The original binary data to be written
1069 * @return string A very convenient string of continue blocks
1072 function _addContinue($data)
1074 $limit = $this->_limit;
1075 $record = 0x003C; // Record identifier
1077 // The first 2080/8224 bytes remain intact. However, we have to change
1078 // the length field of the record.
1079 $tmp = substr($data, 0, 2).pack("v", $limit-4).substr($data, 4, $limit - 4);
1081 $header = pack("vv", $record, $limit); // Headers for continue records
1083 // Retrieve chunks of 2080/8224 bytes +4 for the header.
1084 $data_length = strlen($data);
1085 for ($i = $limit; $i < ($data_length - $limit); $i += $limit) {
1087 $tmp .= substr($data, $i, $limit);
1090 // Retrieve the last chunk of data
1091 $header = pack("vv", $record, strlen($data) - $i);
1093 $tmp .= substr($data, $i, strlen($data) - $i);
1100 FIXME: change prefixes
1102 define("OP_BETWEEN", 0x00);
1103 define("OP_NOTBETWEEN", 0x01);
1104 define("OP_EQUAL", 0x02);
1105 define("OP_NOTEQUAL", 0x03);
1106 define("OP_GT", 0x04);
1107 define("OP_LT", 0x05);
1108 define("OP_GTE", 0x06);
1109 define("OP_LTE", 0x07);
1112 * Baseclass for generating Excel DV records (validations)
1114 * @author Herman Kuiper
1115 * @category FileFormats
1116 * @package Spreadsheet_Excel_Writer
1118 class Spreadsheet_Excel_Writer_Validator
1135 * The parser from the workbook. Used to parse validation formulas also
1136 * @var Spreadsheet_Excel_Writer_Parser
1140 function __construct(&$parser)
1142 $this->_parser = $parser;
1143 $this->_type = 0x01; // FIXME: add method for setting datatype
1144 $this->_style = 0x00;
1145 $this->_fixedList = false;
1146 $this->_blank = false;
1147 $this->_incell = false;
1148 $this->_showprompt = false;
1149 $this->_showerror = true;
1150 $this->_title_prompt = "\x00";
1151 $this->_descr_prompt = "\x00";
1152 $this->_title_error = "\x00";
1153 $this->_descr_error = "\x00";
1154 $this->_operator = 0x00; // default is equal
1155 $this->_formula1 = '';
1156 $this->_formula2 = '';
1159 function setPrompt($promptTitle = "\x00", $promptDescription = "\x00", $showPrompt = true)
1161 $this->_showprompt = $showPrompt;
1162 $this->_title_prompt = $promptTitle;
1163 $this->_descr_prompt = $promptDescription;
1166 function setError($errorTitle = "\x00", $errorDescription = "\x00", $showError = true)
1168 $this->_showerror = $showError;
1169 $this->_title_error = $errorTitle;
1170 $this->_descr_error = $errorDescription;
1173 function allowBlank()
1175 $this->_blank = true;
1178 function onInvalidStop()
1180 $this->_style = 0x00;
1183 function onInvalidWarn()
1185 $this->_style = 0x01;
1188 function onInvalidInfo()
1190 $this->_style = 0x02;
1193 function setFormula1($formula)
1195 // Parse the formula using the parser in Parser.php
1196 $this->_parser->parse($formula);
1198 $this->_formula1 = $this->_parser->toReversePolish();
1202 function setFormula2($formula)
1204 // Parse the formula using the parser in Parser.php
1205 $this->_parser->parse($formula);
1207 $this->_formula2 = $this->_parser->toReversePolish();
1211 function _getOptions()
1213 $options = $this->_type;
1214 $options |= $this->_style << 3;
1215 if ($this->_fixedList) {
1218 if ($this->_blank) {
1221 if (!$this->_incell) {
1224 if ($this->_showprompt) {
1225 $options |= 0x40000;
1227 if ($this->_showerror) {
1228 $options |= 0x80000;
1230 $options |= $this->_operator << 20;
1237 $title_prompt_len = strlen($this->_title_prompt);
1238 $descr_prompt_len = strlen($this->_descr_prompt);
1239 $title_error_len = strlen($this->_title_error);
1240 $descr_error_len = strlen($this->_descr_error);
1242 $formula1_size = strlen($this->_formula1);
1243 $formula2_size = strlen($this->_formula2);
1245 $data = pack("V", $this->_getOptions());
1246 $data .= pack("vC", $title_prompt_len, 0x00) . $this->_title_prompt;
1247 $data .= pack("vC", $title_error_len, 0x00) . $this->_title_error;
1248 $data .= pack("vC", $descr_prompt_len, 0x00) . $this->_descr_prompt;
1249 $data .= pack("vC", $descr_error_len, 0x00) . $this->_descr_error;
1251 $data .= pack("vv", $formula1_size, 0x0000) . $this->_formula1;
1252 $data .= pack("vv", $formula2_size, 0x0000) . $this->_formula2;
1259 * Class for generating Excel XF records (formats)
1261 * @author Xavier Noguer <xnoguer@rezebra.com>
1262 * @category FileFormats
1263 * @package Spreadsheet_Excel_Writer
1266 class Spreadsheet_Excel_Writer_Format
1269 * The index given by the workbook when creating a new format.
1275 * Index to the FONT record.
1281 * The font name (ASCII).
1287 * Height of font (1/20 of a point)
1299 * Bit specifiying if the font is italic.
1305 * Index to the cell's color
1311 * The text underline property
1317 * Bit specifiying if the font has strikeout.
1320 var $_font_strikeout;
1323 * Bit specifiying if the font has outline.
1329 * Bit specifiying if the font has shadow.
1335 * 2 bytes specifiying the script type for the font.
1341 * Byte specifiying the font family.
1347 * Byte specifiying the font charset.
1353 * An index (2 bytes) to a FORMAT record (number format).
1359 * Bit specifying if formulas are hidden.
1365 * Bit specifying if the cell is locked.
1371 * The three bits specifying the text horizontal alignment.
1377 * Bit specifying if the text is wrapped at the right border.
1383 * The three bits specifying the text vertical alignment.
1389 * 1 bit, apparently not used.
1392 var $_text_justlast;
1395 * The two bits specifying the text rotation.
1401 * The cell's foreground color.
1407 * The cell's background color.
1413 * The cell's background fill pattern.
1419 * Style of the bottom border of the cell
1425 * Color of the bottom border of the cell.
1431 * Style of the top border of the cell
1437 * Color of the top border of the cell.
1443 * Style of the left border of the cell
1449 * Color of the left border of the cell.
1455 * Style of the right border of the cell
1461 * Color of the right border of the cell.
1466 // Dynamic members must be here (php 8.2)
1474 * @param integer $index the XF index for the format.
1475 * @param array $properties array with properties to be set on initialization.
1477 function __construct($BIFF_version, $index = 0, $properties = array())
1479 $this->_xf_index = $index;
1480 $this->_BIFF_version = $BIFF_version;
1481 $this->font_index = 0;
1482 $this->_font_name = 'Arial';
1484 $this->_bold = 0x0190;
1486 $this->_color = 0x7FFF;
1487 $this->_underline = 0;
1488 $this->_font_strikeout = 0;
1489 $this->_font_outline = 0;
1490 $this->_font_shadow = 0;
1491 $this->_font_script = 0;
1492 $this->_font_family = 0;
1493 $this->_font_charset = 0;
1495 $this->_num_format = 0;
1500 $this->_text_h_align = 0;
1501 $this->_text_wrap = 0;
1502 $this->_text_v_align = 2;
1503 $this->_text_justlast = 0;
1504 $this->_rotation = 0;
1506 $this->_fg_color = 0x40;
1507 $this->_bg_color = 0x41;
1509 $this->_pattern = 0;
1517 $this->_bottom_color = 0x40;
1518 $this->_top_color = 0x40;
1519 $this->_left_color = 0x40;
1520 $this->_right_color = 0x40;
1521 $this->_diag_color = 0x40;
1523 // Set properties passed to Spreadsheet_Excel_Writer_Workbook::addFormat()
1524 foreach ($properties as $property => $value)
1526 if (method_exists($this, 'set'.ucwords($property))) {
1527 $method_name = 'set'.ucwords($property);
1528 $this->$method_name($value);
1535 * Generate an Excel BIFF XF record (style or cell).
1537 * @param string $style The type of the XF record ('style' or 'cell').
1538 * @return string The XF record
1540 function getXf($style)
1542 // Set the type of the XF record and some of the attributes.
1543 if ($style == 'style') {
1546 $style = $this->_locked;
1547 $style |= $this->_hidden << 1;
1550 // Flags to indicate if attributes have been set.
1551 $atr_num = ($this->_num_format != 0)?1:0;
1552 $atr_fnt = ($this->font_index != 0)?1:0;
1553 $atr_alc = ($this->_text_wrap)?1:0;
1554 $atr_bdr = ($this->_bottom ||
1558 $atr_pat = (($this->_fg_color != 0x40) ||
1559 ($this->_bg_color != 0x41) ||
1560 $this->_pattern)?1:0;
1561 $atr_prot = $this->_locked | $this->_hidden;
1563 // Zero the default border colour if the border has not been set.
1564 if ($this->_bottom == 0) {
1565 $this->_bottom_color = 0;
1567 if ($this->_top == 0) {
1568 $this->_top_color = 0;
1570 if ($this->_right == 0) {
1571 $this->_right_color = 0;
1573 if ($this->_left == 0) {
1574 $this->_left_color = 0;
1576 if ($this->_diag == 0) {
1577 $this->_diag_color = 0;
1580 $record = 0x00E0; // Record identifier
1581 if ($this->_BIFF_version == 0x0500) {
1582 $length = 0x0010; // Number of bytes to follow
1584 if ($this->_BIFF_version == 0x0600) {
1588 $ifnt = $this->font_index; // Index to FONT record
1589 $ifmt = $this->_num_format; // Index to FORMAT record
1590 if ($this->_BIFF_version == 0x0500) {
1591 $align = $this->_text_h_align; // Alignment
1592 $align |= $this->_text_wrap << 3;
1593 $align |= $this->_text_v_align << 4;
1594 $align |= $this->_text_justlast << 7;
1595 $align |= $this->_rotation << 8;
1596 $align |= $atr_num << 10;
1597 $align |= $atr_fnt << 11;
1598 $align |= $atr_alc << 12;
1599 $align |= $atr_bdr << 13;
1600 $align |= $atr_pat << 14;
1601 $align |= $atr_prot << 15;
1603 $icv = $this->_fg_color; // fg and bg pattern colors
1604 $icv |= $this->_bg_color << 7;
1606 $fill = $this->_pattern; // Fill and border line style
1607 $fill |= $this->_bottom << 6;
1608 $fill |= $this->_bottom_color << 9;
1610 $border1 = $this->_top; // Border line style and color
1611 $border1 |= $this->_left << 3;
1612 $border1 |= $this->_right << 6;
1613 $border1 |= $this->_top_color << 9;
1615 $border2 = $this->_left_color; // Border color
1616 $border2 |= $this->_right_color << 7;
1618 $header = pack("vv", $record, $length);
1619 $data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align,
1621 $border1, $border2);
1622 } elseif ($this->_BIFF_version == 0x0600) {
1623 $align = $this->_text_h_align; // Alignment
1624 $align |= $this->_text_wrap << 3;
1625 $align |= $this->_text_v_align << 4;
1626 $align |= $this->_text_justlast << 7;
1628 $used_attrib = $atr_num << 2;
1629 $used_attrib |= $atr_fnt << 3;
1630 $used_attrib |= $atr_alc << 4;
1631 $used_attrib |= $atr_bdr << 5;
1632 $used_attrib |= $atr_pat << 6;
1633 $used_attrib |= $atr_prot << 7;
1635 $icv = $this->_fg_color; // fg and bg pattern colors
1636 $icv |= $this->_bg_color << 7;
1638 $border1 = $this->_left; // Border line style and color
1639 $border1 |= $this->_right << 4;
1640 $border1 |= $this->_top << 8;
1641 $border1 |= $this->_bottom << 12;
1642 $border1 |= $this->_left_color << 16;
1643 $border1 |= $this->_right_color << 23;
1644 $diag_tl_to_rb = 0; // FIXME: add method
1645 $diag_tr_to_lb = 0; // FIXME: add method
1646 $border1 |= $diag_tl_to_rb << 30;
1647 $border1 |= $diag_tr_to_lb << 31;
1649 $border2 = $this->_top_color; // Border color
1650 $border2 |= $this->_bottom_color << 7;
1651 $border2 |= $this->_diag_color << 14;
1652 $border2 |= $this->_diag << 21;
1653 $border2 |= $this->_pattern << 26;
1655 $header = pack("vv", $record, $length);
1658 $biff8_options = 0x00;
1659 $data = pack("vvvC", $ifnt, $ifmt, $style, $align);
1660 $data .= pack("CCC", $rotation, $biff8_options, $used_attrib);
1661 $data .= pack("VVv", $border1, $border2, $icv);
1664 return($header . $data);
1668 * Generate an Excel BIFF FONT record.
1670 * @return string The FONT record
1674 $dyHeight = $this->_size * 20; // Height of font (1/20 of a point)
1675 $icv = $this->_color; // Index to color palette
1676 $bls = $this->_bold; // Bold style
1677 $sss = $this->_font_script; // Superscript/subscript
1678 $uls = $this->_underline; // Underline
1679 $bFamily = $this->_font_family; // Font family
1680 $bCharSet = $this->_font_charset; // Character set
1681 $encoding = 0; // TODO: Unicode support
1683 $cch = strlen($this->_font_name); // Length of font name
1684 $record = 0x31; // Record identifier
1685 if ($this->_BIFF_version == 0x0500) {
1686 $length = 0x0F + $cch; // Record length
1687 } elseif ($this->_BIFF_version == 0x0600) {
1688 $length = 0x10 + $cch;
1690 $reserved = 0x00; // Reserved
1691 $grbit = 0x00; // Font attributes
1692 if ($this->_italic) {
1695 if ($this->_font_strikeout) {
1698 if ($this->_font_outline) {
1701 if ($this->_font_shadow) {
1705 $header = pack("vv", $record, $length);
1706 if ($this->_BIFF_version == 0x0500) {
1707 $data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls,
1708 $sss, $uls, $bFamily,
1709 $bCharSet, $reserved, $cch);
1710 } elseif ($this->_BIFF_version == 0x0600) {
1711 $data = pack("vvvvvCCCCCC", $dyHeight, $grbit, $icv, $bls,
1712 $sss, $uls, $bFamily,
1713 $bCharSet, $reserved, $cch, $encoding);
1715 return($header . $data . $this->_font_name);
1719 * Returns a unique hash key for a font.
1720 * Used by Spreadsheet_Excel_Writer_Workbook::_storeAllFonts()
1722 * The elements that form the key are arranged to increase the probability of
1723 * generating a unique key. Elements that hold a large range of numbers
1724 * (eg. _color) are placed between two binary elements such as _italic
1726 * @return string A key for this font
1728 function getFontKey()
1730 $key = "$this->_font_name$this->_size";
1731 $key .= "$this->_font_script$this->_underline";
1732 $key .= "$this->_font_strikeout$this->_bold$this->_font_outline";
1733 $key .= "$this->_font_family$this->_font_charset";
1734 $key .= "$this->_font_shadow$this->_color$this->_italic";
1735 $key = str_replace(' ', '_', $key);
1740 * Returns the index used by Spreadsheet_Excel_Writer_Worksheet::_XF()
1742 * @return integer The index for the XF record
1744 function getXfIndex()
1746 return($this->_xf_index);
1750 * Used in conjunction with the set_xxx_color methods to convert a color
1751 * string into a number. Color range is 0..63 but we will restrict it
1752 * to 8..63 to comply with Gnumeric. Colors 0..7 are repeated in 8..15.
1755 * @param string $name_color name of the color (i.e.: 'blue', 'red', etc..). Optional.
1756 * @return integer The color index
1758 function _getColor($name_color = '')
1781 // Return the default color, 0x7FFF, if undef,
1782 if ($name_color == '') {
1786 // or the color string converted to an integer,
1787 if (isset($colors[$name_color])) {
1788 return($colors[$name_color]);
1791 // or the default color if string is unrecognised,
1792 if (preg_match("/\D/",$name_color)) {
1796 // or an index < 8 mapped into the correct range,
1797 if ($name_color < 8) {
1798 return($name_color + 8);
1801 // or the default color if arg is outside range,
1802 if ($name_color > 63) {
1806 // or an integer in the valid range
1807 return($name_color);
1811 * Set cell alignment.
1814 * @param string $location alignment for the cell ('left', 'right', etc...).
1816 function setAlign($location)
1818 if (preg_match("/\d/",$location)) {
1819 return; // Ignore numbers
1822 $location = strtolower($location);
1824 if ($location == 'left') {
1825 $this->_text_h_align = 1;
1827 if ($location == 'centre') {
1828 $this->_text_h_align = 2;
1830 if ($location == 'center') {
1831 $this->_text_h_align = 2;
1833 if ($location == 'right') {
1834 $this->_text_h_align = 3;
1836 if ($location == 'fill') {
1837 $this->_text_h_align = 4;
1839 if ($location == 'justify') {
1840 $this->_text_h_align = 5;
1842 if ($location == 'merge') {
1843 $this->_text_h_align = 6;
1845 if ($location == 'equal_space') { // For T.K.
1846 $this->_text_h_align = 7;
1848 if ($location == 'top') {
1849 $this->_text_v_align = 0;
1851 if ($location == 'vcentre') {
1852 $this->_text_v_align = 1;
1854 if ($location == 'vcenter') {
1855 $this->_text_v_align = 1;
1857 if ($location == 'bottom') {
1858 $this->_text_v_align = 2;
1860 if ($location == 'vjustify') {
1861 $this->_text_v_align = 3;
1863 if ($location == 'vequal_space') { // For T.K.
1864 $this->_text_v_align = 4;
1869 * Set cell horizontal alignment.
1872 * @param string $location alignment for the cell ('left', 'right', etc...).
1874 function setHAlign($location)
1876 if (preg_match("/\d/",$location)) {
1877 return; // Ignore numbers
1880 $location = strtolower($location);
1882 if ($location == 'left') {
1883 $this->_text_h_align = 1;
1885 if ($location == 'centre') {
1886 $this->_text_h_align = 2;
1888 if ($location == 'center') {
1889 $this->_text_h_align = 2;
1891 if ($location == 'right') {
1892 $this->_text_h_align = 3;
1894 if ($location == 'fill') {
1895 $this->_text_h_align = 4;
1897 if ($location == 'justify') {
1898 $this->_text_h_align = 5;
1900 if ($location == 'merge') {
1901 $this->_text_h_align = 6;
1903 if ($location == 'equal_space') { // For T.K.
1904 $this->_text_h_align = 7;
1909 * Set cell vertical alignment.
1912 * @param string $location alignment for the cell ('top', 'vleft', 'vright', etc...).
1914 function setVAlign($location)
1916 if (preg_match("/\d/",$location)) {
1917 return; // Ignore numbers
1920 $location = strtolower($location);
1922 if ($location == 'top') {
1923 $this->_text_v_align = 0;
1925 if ($location == 'vcentre') {
1926 $this->_text_v_align = 1;
1928 if ($location == 'vcenter') {
1929 $this->_text_v_align = 1;
1931 if ($location == 'bottom') {
1932 $this->_text_v_align = 2;
1934 if ($location == 'vjustify') {
1935 $this->_text_v_align = 3;
1937 if ($location == 'vequal_space') { // For T.K.
1938 $this->_text_v_align = 4;
1943 * This is an alias for the unintuitive setAlign('merge')
1949 $this->setAlign('merge');
1953 * Sets the boldness of the text.
1954 * Bold has a range 100..1000.
1955 * 0 (400) is normal. 1 (700) is bold.
1958 * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
1959 1 maps to 700 (bold text). Valid range is: 100-1000.
1960 It's Optional, default is 1 (bold).
1962 function setBold($weight = 1)
1965 $weight = 0x2BC; // Bold text
1968 $weight = 0x190; // Normal text
1970 if ($weight < 0x064) {
1971 $weight = 0x190; // Lower bound
1973 if ($weight > 0x3E8) {
1974 $weight = 0x190; // Upper bound
1976 $this->_bold = $weight;
1980 /************************************
1981 * FUNCTIONS FOR SETTING CELLS BORDERS
1985 * Sets the width for the bottom border of the cell
1988 * @param integer $style style of the cell border. 1 => thin, 2 => thick.
1990 function setBottom($style)
1992 $this->_bottom = $style;
1996 * Sets the width for the top border of the cell
1999 * @param integer $style style of the cell top border. 1 => thin, 2 => thick.
2001 function setTop($style)
2003 $this->_top = $style;
2007 * Sets the width for the left border of the cell
2010 * @param integer $style style of the cell left border. 1 => thin, 2 => thick.
2012 function setLeft($style)
2014 $this->_left = $style;
2018 * Sets the width for the right border of the cell
2021 * @param integer $style style of the cell right border. 1 => thin, 2 => thick.
2023 function setRight($style)
2025 $this->_right = $style;
2030 * Set cells borders to the same style
2033 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
2035 function setBorder($style)
2037 $this->setBottom($style);
2038 $this->setTop($style);
2039 $this->setLeft($style);
2040 $this->setRight($style);
2044 /*******************************************
2045 * FUNCTIONS FOR SETTING CELLS BORDERS COLORS
2049 * Sets all the cell's borders to the same color
2052 * @param mixed $color The color we are setting. Either a string (like 'blue'),
2053 * or an integer (range is [8...63]).
2055 function setBorderColor($color)
2057 $this->setBottomColor($color);
2058 $this->setTopColor($color);
2059 $this->setLeftColor($color);
2060 $this->setRightColor($color);
2064 * Sets the cell's bottom border color
2067 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2069 function setBottomColor($color)
2071 $value = $this->_getColor($color);
2072 $this->_bottom_color = $value;
2076 * Sets the cell's top border color
2079 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2081 function setTopColor($color)
2083 $value = $this->_getColor($color);
2084 $this->_top_color = $value;
2088 * Sets the cell's left border color
2091 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2093 function setLeftColor($color)
2095 $value = $this->_getColor($color);
2096 $this->_left_color = $value;
2100 * Sets the cell's right border color
2103 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2105 function setRightColor($color)
2107 $value = $this->_getColor($color);
2108 $this->_right_color = $value;
2113 * Sets the cell's foreground color
2116 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2118 function setFgColor($color)
2120 $value = $this->_getColor($color);
2121 $this->_fg_color = $value;
2122 if ($this->_pattern == 0) { // force color to be seen
2123 $this->_pattern = 1;
2128 * Sets the cell's background color
2131 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2133 function setBgColor($color)
2135 $value = $this->_getColor($color);
2136 $this->_bg_color = $value;
2137 if ($this->_pattern == 0) { // force color to be seen
2138 $this->_pattern = 1;
2143 * Sets the cell's color
2146 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2148 function setColor($color)
2150 $value = $this->_getColor($color);
2151 $this->_color = $value;
2155 * Sets the fill pattern attribute of a cell
2158 * @param integer $arg Optional. Defaults to 1. Meaningful values are: 0-18,
2159 * 0 meaning no background.
2161 function setPattern($arg = 1)
2163 $this->_pattern = $arg;
2167 * Sets the underline of the text
2170 * @param integer $underline The value for underline. Possible values are:
2171 * 1 => underline, 2 => double underline.
2173 function setUnderline($underline)
2175 $this->_underline = $underline;
2179 * Sets the font style as italic
2183 function setItalic()
2189 * Sets the font size
2192 * @param integer $size The font size (in pixels I think).
2194 function setSize($size)
2196 $this->_size = $size;
2200 * Sets text wrapping
2204 function setTextWrap()
2206 $this->_text_wrap = 1;
2210 * Sets the orientation of the text
2213 * @param integer $angle The rotation angle for the text (clockwise). Possible
2214 values are: 0, 90, 270 and -1 for stacking top-to-bottom.
2216 function setTextRotation($angle)
2221 $this->_rotation = 0;
2224 $this->_rotation = 3;
2227 $this->_rotation = 2;
2230 $this->_rotation = 1;
2233 $this->_rotation = 0;
2239 * Sets the numeric format.
2240 * It can be date, time, currency, etc...
2243 * @param integer $num_format The numeric format.
2245 function setNumFormat($num_format)
2247 $this->_num_format = $num_format;
2251 * Sets font as strikeout.
2255 function setStrikeOut()
2257 $this->_font_strikeout = 1;
2261 * Sets outlining for a font.
2265 function setOutLine()
2267 $this->_font_outline = 1;
2271 * Sets font as shadow.
2275 function setShadow()
2277 $this->_font_shadow = 1;
2281 * Sets the script type of the text
2284 * @param integer $script The value for script type. Possible values are:
2285 * 1 => superscript, 2 => subscript.
2287 function setScript($script)
2289 $this->_font_script = $script;
2297 function setLocked()
2303 * Unlocks a cell. Useful for unprotecting particular cells of a protected sheet.
2307 function setUnLocked()
2313 * Sets the font family name.
2316 * @param string $fontfamily The font family name. Possible values are:
2317 * 'Times New Roman', 'Arial', 'Courier'.
2319 function setFontFamily($font_family)
2321 $this->_font_name = $font_family;
2326 * Class for parsing Excel formulas
2328 * @author Xavier Noguer <xnoguer@rezebra.com>
2329 * @category FileFormats
2330 * @package Spreadsheet_Excel_Writer
2333 class Spreadsheet_Excel_Writer_Parser
2336 * The index of the character we are currently looking at
2342 * The token we are working on.
2345 var $_current_token;
2348 * The formula to parse
2354 * The character ahead of the current char
2360 * The parse tree to be generated
2366 * The byte order. 1 => big endian, 0 => little endian.
2372 * Array of external sheets
2378 * Array of sheet references in the form of REF structures
2384 * The BIFF version for the workbook
2389 // The Excel ptg indices. Dynamic members must be here (php 8.2)
2393 * The class constructor
2395 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
2396 (optional). 1 => big endian, 0 (default) little endian.
2398 function __construct($byte_order, $biff_version)
2400 $this->_current_char = 0;
2401 $this->_BIFF_version = $biff_version;
2402 $this->_current_token = ''; // The token we are working on.
2403 $this->_formula = ''; // The formula to parse.
2404 $this->_lookahead = ''; // The character ahead of the current char.
2405 $this->_parse_tree = ''; // The parse tree to be generated.
2406 $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's
2407 $this->_byte_order = $byte_order; // Little Endian or Big Endian
2408 $this->_ext_sheets = array();
2409 $this->_references = array();
2413 * Initialize the ptg and function hashes.
2417 function _initializeHashes()
2419 // The Excel ptg indices
2428 'ptgConcat' => 0x08,
2439 'ptgUminus' => 0x13,
2440 'ptgPercent' => 0x14,
2442 'ptgMissArg' => 0x16,
2446 'ptgEndSheet' => 0x1B,
2453 'ptgFuncVar' => 0x22,
2457 'ptgMemArea' => 0x26,
2458 'ptgMemErr' => 0x27,
2459 'ptgMemNoMem' => 0x28,
2460 'ptgMemFunc' => 0x29,
2461 'ptgRefErr' => 0x2A,
2462 'ptgAreaErr' => 0x2B,
2465 'ptgMemAreaN' => 0x2E,
2466 'ptgMemNoMemN' => 0x2F,
2469 'ptgArea3d' => 0x3B,
2470 'ptgRefErr3d' => 0x3C,
2471 'ptgAreaErr3d' => 0x3D,
2472 'ptgArrayV' => 0x40,
2474 'ptgFuncVarV' => 0x42,
2478 'ptgMemAreaV' => 0x46,
2479 'ptgMemErrV' => 0x47,
2480 'ptgMemNoMemV' => 0x48,
2481 'ptgMemFuncV' => 0x49,
2482 'ptgRefErrV' => 0x4A,
2483 'ptgAreaErrV' => 0x4B,
2485 'ptgAreaNV' => 0x4D,
2486 'ptgMemAreaNV' => 0x4E,
2487 'ptgMemNoMemN' => 0x4F,
2488 'ptgFuncCEV' => 0x58,
2489 'ptgNameXV' => 0x59,
2490 'ptgRef3dV' => 0x5A,
2491 'ptgArea3dV' => 0x5B,
2492 'ptgRefErr3dV' => 0x5C,
2493 'ptgAreaErr3d' => 0x5D,
2494 'ptgArrayA' => 0x60,
2496 'ptgFuncVarA' => 0x62,
2500 'ptgMemAreaA' => 0x66,
2501 'ptgMemErrA' => 0x67,
2502 'ptgMemNoMemA' => 0x68,
2503 'ptgMemFuncA' => 0x69,
2504 'ptgRefErrA' => 0x6A,
2505 'ptgAreaErrA' => 0x6B,
2507 'ptgAreaNA' => 0x6D,
2508 'ptgMemAreaNA' => 0x6E,
2509 'ptgMemNoMemN' => 0x6F,
2510 'ptgFuncCEA' => 0x78,
2511 'ptgNameXA' => 0x79,
2512 'ptgRef3dA' => 0x7A,
2513 'ptgArea3dA' => 0x7B,
2514 'ptgRefErr3dA' => 0x7C,
2515 'ptgAreaErr3d' => 0x7D
2518 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
2520 // The following hash was generated by "function_locale.pl" in the distro.
2521 // Refer to function_locale.pl for non-English function names.
2523 // The array elements are as follow:
2524 // ptg: The Excel function ptg code.
2525 // args: The number of arguments that the function takes:
2526 // >=0 is a fixed number of arguments.
2527 // -1 is a variable number of arguments.
2528 // class: The reference, value or array class of the function args.
2529 // vol: The function is volatile.
2531 $this->_functions = array(
2532 // function ptg args class vol
2533 'COUNT' => array( 0, -1, 0, 0 ),
2534 'IF' => array( 1, -1, 1, 0 ),
2535 'ISNA' => array( 2, 1, 1, 0 ),
2536 'ISERROR' => array( 3, 1, 1, 0 ),
2537 'SUM' => array( 4, -1, 0, 0 ),
2538 'AVERAGE' => array( 5, -1, 0, 0 ),
2539 'MIN' => array( 6, -1, 0, 0 ),
2540 'MAX' => array( 7, -1, 0, 0 ),
2541 'ROW' => array( 8, -1, 0, 0 ),
2542 'COLUMN' => array( 9, -1, 0, 0 ),
2543 'NA' => array( 10, 0, 0, 0 ),
2544 'NPV' => array( 11, -1, 1, 0 ),
2545 'STDEV' => array( 12, -1, 0, 0 ),
2546 'DOLLAR' => array( 13, -1, 1, 0 ),
2547 'FIXED' => array( 14, -1, 1, 0 ),
2548 'SIN' => array( 15, 1, 1, 0 ),
2549 'COS' => array( 16, 1, 1, 0 ),
2550 'TAN' => array( 17, 1, 1, 0 ),
2551 'ATAN' => array( 18, 1, 1, 0 ),
2552 'PI' => array( 19, 0, 1, 0 ),
2553 'SQRT' => array( 20, 1, 1, 0 ),
2554 'EXP' => array( 21, 1, 1, 0 ),
2555 'LN' => array( 22, 1, 1, 0 ),
2556 'LOG10' => array( 23, 1, 1, 0 ),
2557 'ABS' => array( 24, 1, 1, 0 ),
2558 'INT' => array( 25, 1, 1, 0 ),
2559 'SIGN' => array( 26, 1, 1, 0 ),
2560 'ROUND' => array( 27, 2, 1, 0 ),
2561 'LOOKUP' => array( 28, -1, 0, 0 ),
2562 'INDEX' => array( 29, -1, 0, 1 ),
2563 'REPT' => array( 30, 2, 1, 0 ),
2564 'MID' => array( 31, 3, 1, 0 ),
2565 'LEN' => array( 32, 1, 1, 0 ),
2566 'VALUE' => array( 33, 1, 1, 0 ),
2567 'TRUE' => array( 34, 0, 1, 0 ),
2568 'FALSE' => array( 35, 0, 1, 0 ),
2569 'AND' => array( 36, -1, 0, 0 ),
2570 'OR' => array( 37, -1, 0, 0 ),
2571 'NOT' => array( 38, 1, 1, 0 ),
2572 'MOD' => array( 39, 2, 1, 0 ),
2573 'DCOUNT' => array( 40, 3, 0, 0 ),
2574 'DSUM' => array( 41, 3, 0, 0 ),
2575 'DAVERAGE' => array( 42, 3, 0, 0 ),
2576 'DMIN' => array( 43, 3, 0, 0 ),
2577 'DMAX' => array( 44, 3, 0, 0 ),
2578 'DSTDEV' => array( 45, 3, 0, 0 ),
2579 'VAR' => array( 46, -1, 0, 0 ),
2580 'DVAR' => array( 47, 3, 0, 0 ),
2581 'TEXT' => array( 48, 2, 1, 0 ),
2582 'LINEST' => array( 49, -1, 0, 0 ),
2583 'TREND' => array( 50, -1, 0, 0 ),
2584 'LOGEST' => array( 51, -1, 0, 0 ),
2585 'GROWTH' => array( 52, -1, 0, 0 ),
2586 'PV' => array( 56, -1, 1, 0 ),
2587 'FV' => array( 57, -1, 1, 0 ),
2588 'NPER' => array( 58, -1, 1, 0 ),
2589 'PMT' => array( 59, -1, 1, 0 ),
2590 'RATE' => array( 60, -1, 1, 0 ),
2591 'MIRR' => array( 61, 3, 0, 0 ),
2592 'IRR' => array( 62, -1, 0, 0 ),
2593 'RAND' => array( 63, 0, 1, 1 ),
2594 'MATCH' => array( 64, -1, 0, 0 ),
2595 'DATE' => array( 65, 3, 1, 0 ),
2596 'TIME' => array( 66, 3, 1, 0 ),
2597 'DAY' => array( 67, 1, 1, 0 ),
2598 'MONTH' => array( 68, 1, 1, 0 ),
2599 'YEAR' => array( 69, 1, 1, 0 ),
2600 'WEEKDAY' => array( 70, -1, 1, 0 ),
2601 'HOUR' => array( 71, 1, 1, 0 ),
2602 'MINUTE' => array( 72, 1, 1, 0 ),
2603 'SECOND' => array( 73, 1, 1, 0 ),
2604 'NOW' => array( 74, 0, 1, 1 ),
2605 'AREAS' => array( 75, 1, 0, 1 ),
2606 'ROWS' => array( 76, 1, 0, 1 ),
2607 'COLUMNS' => array( 77, 1, 0, 1 ),
2608 'OFFSET' => array( 78, -1, 0, 1 ),
2609 'SEARCH' => array( 82, -1, 1, 0 ),
2610 'TRANSPOSE' => array( 83, 1, 1, 0 ),
2611 'TYPE' => array( 86, 1, 1, 0 ),
2612 'ATAN2' => array( 97, 2, 1, 0 ),
2613 'ASIN' => array( 98, 1, 1, 0 ),
2614 'ACOS' => array( 99, 1, 1, 0 ),
2615 'CHOOSE' => array( 100, -1, 1, 0 ),
2616 'HLOOKUP' => array( 101, -1, 0, 0 ),
2617 'VLOOKUP' => array( 102, -1, 0, 0 ),
2618 'ISREF' => array( 105, 1, 0, 0 ),
2619 'LOG' => array( 109, -1, 1, 0 ),
2620 'CHAR' => array( 111, 1, 1, 0 ),
2621 'LOWER' => array( 112, 1, 1, 0 ),
2622 'UPPER' => array( 113, 1, 1, 0 ),
2623 'PROPER' => array( 114, 1, 1, 0 ),
2624 'LEFT' => array( 115, -1, 1, 0 ),
2625 'RIGHT' => array( 116, -1, 1, 0 ),
2626 'EXACT' => array( 117, 2, 1, 0 ),
2627 'TRIM' => array( 118, 1, 1, 0 ),
2628 'REPLACE' => array( 119, 4, 1, 0 ),
2629 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
2630 'CODE' => array( 121, 1, 1, 0 ),
2631 'FIND' => array( 124, -1, 1, 0 ),
2632 'CELL' => array( 125, -1, 0, 1 ),
2633 'ISERR' => array( 126, 1, 1, 0 ),
2634 'ISTEXT' => array( 127, 1, 1, 0 ),
2635 'ISNUMBER' => array( 128, 1, 1, 0 ),
2636 'ISBLANK' => array( 129, 1, 1, 0 ),
2637 'T' => array( 130, 1, 0, 0 ),
2638 'N' => array( 131, 1, 0, 0 ),
2639 'DATEVALUE' => array( 140, 1, 1, 0 ),
2640 'TIMEVALUE' => array( 141, 1, 1, 0 ),
2641 'SLN' => array( 142, 3, 1, 0 ),
2642 'SYD' => array( 143, 4, 1, 0 ),
2643 'DDB' => array( 144, -1, 1, 0 ),
2644 'INDIRECT' => array( 148, -1, 1, 1 ),
2645 'CALL' => array( 150, -1, 1, 0 ),
2646 'CLEAN' => array( 162, 1, 1, 0 ),
2647 'MDETERM' => array( 163, 1, 2, 0 ),
2648 'MINVERSE' => array( 164, 1, 2, 0 ),
2649 'MMULT' => array( 165, 2, 2, 0 ),
2650 'IPMT' => array( 167, -1, 1, 0 ),
2651 'PPMT' => array( 168, -1, 1, 0 ),
2652 'COUNTA' => array( 169, -1, 0, 0 ),
2653 'PRODUCT' => array( 183, -1, 0, 0 ),
2654 'FACT' => array( 184, 1, 1, 0 ),
2655 'DPRODUCT' => array( 189, 3, 0, 0 ),
2656 'ISNONTEXT' => array( 190, 1, 1, 0 ),
2657 'STDEVP' => array( 193, -1, 0, 0 ),
2658 'VARP' => array( 194, -1, 0, 0 ),
2659 'DSTDEVP' => array( 195, 3, 0, 0 ),
2660 'DVARP' => array( 196, 3, 0, 0 ),
2661 'TRUNC' => array( 197, -1, 1, 0 ),
2662 'ISLOGICAL' => array( 198, 1, 1, 0 ),
2663 'DCOUNTA' => array( 199, 3, 0, 0 ),
2664 'ROUNDUP' => array( 212, 2, 1, 0 ),
2665 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
2666 'RANK' => array( 216, -1, 0, 0 ),
2667 'ADDRESS' => array( 219, -1, 1, 0 ),
2668 'DAYS360' => array( 220, -1, 1, 0 ),
2669 'TODAY' => array( 221, 0, 1, 1 ),
2670 'VDB' => array( 222, -1, 1, 0 ),
2671 'MEDIAN' => array( 227, -1, 0, 0 ),
2672 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
2673 'SINH' => array( 229, 1, 1, 0 ),
2674 'COSH' => array( 230, 1, 1, 0 ),
2675 'TANH' => array( 231, 1, 1, 0 ),
2676 'ASINH' => array( 232, 1, 1, 0 ),
2677 'ACOSH' => array( 233, 1, 1, 0 ),
2678 'ATANH' => array( 234, 1, 1, 0 ),
2679 'DGET' => array( 235, 3, 0, 0 ),
2680 'INFO' => array( 244, 1, 1, 1 ),
2681 'DB' => array( 247, -1, 1, 0 ),
2682 'FREQUENCY' => array( 252, 2, 0, 0 ),
2683 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
2684 'REGISTER.ID' => array( 267, -1, 1, 0 ),
2685 'AVEDEV' => array( 269, -1, 0, 0 ),
2686 'BETADIST' => array( 270, -1, 1, 0 ),
2687 'GAMMALN' => array( 271, 1, 1, 0 ),
2688 'BETAINV' => array( 272, -1, 1, 0 ),
2689 'BINOMDIST' => array( 273, 4, 1, 0 ),
2690 'CHIDIST' => array( 274, 2, 1, 0 ),
2691 'CHIINV' => array( 275, 2, 1, 0 ),
2692 'COMBIN' => array( 276, 2, 1, 0 ),
2693 'CONFIDENCE' => array( 277, 3, 1, 0 ),
2694 'CRITBINOM' => array( 278, 3, 1, 0 ),
2695 'EVEN' => array( 279, 1, 1, 0 ),
2696 'EXPONDIST' => array( 280, 3, 1, 0 ),
2697 'FDIST' => array( 281, 3, 1, 0 ),
2698 'FINV' => array( 282, 3, 1, 0 ),
2699 'FISHER' => array( 283, 1, 1, 0 ),
2700 'FISHERINV' => array( 284, 1, 1, 0 ),
2701 'FLOOR' => array( 285, 2, 1, 0 ),
2702 'GAMMADIST' => array( 286, 4, 1, 0 ),
2703 'GAMMAINV' => array( 287, 3, 1, 0 ),
2704 'CEILING' => array( 288, 2, 1, 0 ),
2705 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
2706 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
2707 'LOGINV' => array( 291, 3, 1, 0 ),
2708 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
2709 'NORMDIST' => array( 293, 4, 1, 0 ),
2710 'NORMSDIST' => array( 294, 1, 1, 0 ),
2711 'NORMINV' => array( 295, 3, 1, 0 ),
2712 'NORMSINV' => array( 296, 1, 1, 0 ),
2713 'STANDARDIZE' => array( 297, 3, 1, 0 ),
2714 'ODD' => array( 298, 1, 1, 0 ),
2715 'PERMUT' => array( 299, 2, 1, 0 ),
2716 'POISSON' => array( 300, 3, 1, 0 ),
2717 'TDIST' => array( 301, 3, 1, 0 ),
2718 'WEIBULL' => array( 302, 4, 1, 0 ),
2719 'SUMXMY2' => array( 303, 2, 2, 0 ),
2720 'SUMX2MY2' => array( 304, 2, 2, 0 ),
2721 'SUMX2PY2' => array( 305, 2, 2, 0 ),
2722 'CHITEST' => array( 306, 2, 2, 0 ),
2723 'CORREL' => array( 307, 2, 2, 0 ),
2724 'COVAR' => array( 308, 2, 2, 0 ),
2725 'FORECAST' => array( 309, 3, 2, 0 ),
2726 'FTEST' => array( 310, 2, 2, 0 ),
2727 'INTERCEPT' => array( 311, 2, 2, 0 ),
2728 'PEARSON' => array( 312, 2, 2, 0 ),
2729 'RSQ' => array( 313, 2, 2, 0 ),
2730 'STEYX' => array( 314, 2, 2, 0 ),
2731 'SLOPE' => array( 315, 2, 2, 0 ),
2732 'TTEST' => array( 316, 4, 2, 0 ),
2733 'PROB' => array( 317, -1, 2, 0 ),
2734 'DEVSQ' => array( 318, -1, 0, 0 ),
2735 'GEOMEAN' => array( 319, -1, 0, 0 ),
2736 'HARMEAN' => array( 320, -1, 0, 0 ),
2737 'SUMSQ' => array( 321, -1, 0, 0 ),
2738 'KURT' => array( 322, -1, 0, 0 ),
2739 'SKEW' => array( 323, -1, 0, 0 ),
2740 'ZTEST' => array( 324, -1, 0, 0 ),
2741 'LARGE' => array( 325, 2, 0, 0 ),
2742 'SMALL' => array( 326, 2, 0, 0 ),
2743 'QUARTILE' => array( 327, 2, 0, 0 ),
2744 'PERCENTILE' => array( 328, 2, 0, 0 ),
2745 'PERCENTRANK' => array( 329, -1, 0, 0 ),
2746 'MODE' => array( 330, -1, 2, 0 ),
2747 'TRIMMEAN' => array( 331, 2, 0, 0 ),
2748 'TINV' => array( 332, 2, 1, 0 ),
2749 'CONCATENATE' => array( 336, -1, 1, 0 ),
2750 'POWER' => array( 337, 2, 1, 0 ),
2751 'RADIANS' => array( 342, 1, 1, 0 ),
2752 'DEGREES' => array( 343, 1, 1, 0 ),
2753 'SUBTOTAL' => array( 344, -1, 0, 0 ),
2754 'SUMIF' => array( 345, -1, 0, 0 ),
2755 'COUNTIF' => array( 346, 2, 0, 0 ),
2756 'COUNTBLANK' => array( 347, 1, 0, 0 ),
2757 'ROMAN' => array( 354, -1, 1, 0 )
2762 * Convert a token to the proper ptg value.
2765 * @param mixed $token The token to convert.
2766 * @return mixed the converted token on success. Die if the token
2769 function _convert($token)
2771 if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
2772 return $this->_convertString($token);
2774 } elseif (is_numeric($token)) {
2775 return $this->_convertNumber($token);
2777 // match references like A1 or $A$1
2778 } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
2779 return $this->_convertRef2d($token);
2781 // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
2782 } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2783 return $this->_convertRef3d($token);
2785 // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
2786 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2787 return $this->_convertRef3d($token);
2789 // match ranges like A1:B2
2790 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2791 return $this->_convertRange2d($token);
2793 // match ranges like A1..B2
2794 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2795 return $this->_convertRange2d($token);
2797 // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
2798 } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2799 return $this->_convertRange3d($token);
2801 // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
2802 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2803 return $this->_convertRange3d($token);
2805 // operators (including parentheses)
2806 } elseif (isset($this->ptg[$token])) {
2807 return pack("C", $this->ptg[$token]);
2809 // commented so argument number can be processed correctly. See toReversePolish().
2810 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
2812 return($this->_convertFunction($token,$this->_func_args));
2815 // if it's an argument, ignore the token (the argument remains)
2816 } elseif ($token == 'arg') {
2819 // TODO: use real error codes
2820 die("Unknown token $token");
2824 * Convert a number token to ptgInt or ptgNum
2827 * @param mixed $num an integer or double for conversion to its ptg value
2829 function _convertNumber($num)
2831 // Integer in the range 0..2**16-1
2832 if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
2833 return pack("Cv", $this->ptg['ptgInt'], $num);
2835 if ($this->_byte_order) { // if it's Big Endian
2836 $num = strrev($num);
2838 return pack("Cd", $this->ptg['ptgNum'], $num);
2843 * Convert a string token to ptgStr
2846 * @param string $string A string for conversion to its ptg value.
2847 * @return mixed the converted token on success. PEAR_Error if the string
2848 * is longer than 255 characters.
2850 function _convertString($string)
2852 // chop away beggining and ending quotes
2853 $string = substr($string, 1, strlen($string) - 2);
2854 if (strlen($string) > 255) {
2855 die("String is too long");
2858 if ($this->_BIFF_version == 0x0500) {
2859 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
2860 } elseif ($this->_BIFF_version == 0x0600) {
2861 $encoding = 0; // TODO: Unicode support
2862 return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
2867 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
2868 * args that it takes.
2871 * @param string $token The name of the function for convertion to ptg value.
2872 * @param integer $num_args The number of arguments the function receives.
2873 * @return string The packed ptg for the function
2875 function _convertFunction($token, $num_args)
2877 $args = $this->_functions[$token][1];
2878 $volatile = $this->_functions[$token][3];
2880 // Fixed number of args eg. TIME($i,$j,$k).
2882 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
2884 // Variable number of args eg. SUM($i,$j,$k, ..).
2886 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
2891 * Convert an Excel range such as A1:D4 to a ptgRefV.
2894 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
2896 function _convertRange2d($range)
2898 $class = 2; // as far as I know, this is magick.
2900 // Split the range into 2 cell refs
2901 if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2902 list($cell1, $cell2) = preg_split('/:/', $range);
2903 } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2904 list($cell1, $cell2) = preg_split('/\.\./', $range);
2907 // TODO: use real error codes
2908 die("Unknown range separator");
2911 // Convert the cell references
2912 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2913 list($row1, $col1) = $cell_array1;
2914 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2915 list($row2, $col2) = $cell_array2;
2917 // The ptg value depends on the class of the ptg.
2919 $ptgArea = pack("C", $this->ptg['ptgArea']);
2920 } elseif ($class == 1) {
2921 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
2922 } elseif ($class == 2) {
2923 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
2925 // TODO: use real error codes
2926 die("Unknown class $class");
2928 return $ptgArea . $row1 . $row2 . $col1. $col2;
2932 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
2936 * @param string $token An Excel range in the Sheet1!A1:A2 format.
2937 * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
2939 function _convertRange3d($token)
2941 $class = 2; // as far as I know, this is magick.
2943 // Split the ref at the ! symbol
2944 list($ext_ref, $range) = preg_split('/!/', $token);
2946 // Convert the external reference part (different for BIFF8)
2947 if ($this->_BIFF_version == 0x0500) {
2948 $ext_ref = $this->_packExtRef($ext_ref);
2949 } elseif ($this->_BIFF_version == 0x0600) {
2950 $ext_ref = $this->_getRefIndex($ext_ref);
2953 // Split the range into 2 cell refs
2954 list($cell1, $cell2) = preg_split('/:/', $range);
2956 // Convert the cell references
2957 if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
2958 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2959 list($row1, $col1) = $cell_array1;
2960 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2961 list($row2, $col2) = $cell_array2;
2962 } else { // It's a rows range (like 26:27)
2963 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
2964 list($row1, $col1, $row2, $col2) = $cells_array;
2967 // The ptg value depends on the class of the ptg.
2969 $ptgArea = pack("C", $this->ptg['ptgArea3d']);
2970 } elseif ($class == 1) {
2971 $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
2972 } elseif ($class == 2) {
2973 $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
2975 die("Unknown class $class");
2978 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
2982 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
2985 * @param string $cell An Excel cell reference
2986 * @return string The cell in packed() format with the corresponding ptg
2988 function _convertRef2d($cell)
2990 $class = 2; // as far as I know, this is magick.
2992 // Convert the cell reference
2993 $cell_array = $this->_cellToPackedRowcol($cell);
2994 list($row, $col) = $cell_array;
2996 // The ptg value depends on the class of the ptg.
2998 $ptgRef = pack("C", $this->ptg['ptgRef']);
2999 } elseif ($class == 1) {
3000 $ptgRef = pack("C", $this->ptg['ptgRefV']);
3001 } elseif ($class == 2) {
3002 $ptgRef = pack("C", $this->ptg['ptgRefA']);
3004 // TODO: use real error codes
3005 die("Unknown class $class");
3007 return $ptgRef.$row.$col;
3011 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
3015 * @param string $cell An Excel cell reference
3016 * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
3018 function _convertRef3d($cell)
3020 $class = 2; // as far as I know, this is magick.
3022 // Split the ref at the ! symbol
3023 list($ext_ref, $cell) = preg_split('/!/', $cell);
3025 // Convert the external reference part (different for BIFF8)
3026 if ($this->_BIFF_version == 0x0500) {
3027 $ext_ref = $this->_packExtRef($ext_ref);
3028 } elseif ($this->_BIFF_version == 0x0600) {
3029 $ext_ref = $this->_getRefIndex($ext_ref);
3032 // Convert the cell reference part
3033 list($row, $col) = $this->_cellToPackedRowcol($cell);
3035 // The ptg value depends on the class of the ptg.
3037 $ptgRef = pack("C", $this->ptg['ptgRef3d']);
3038 } elseif ($class == 1) {
3039 $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
3040 } elseif ($class == 2) {
3041 $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
3043 die("Unknown class $class");
3046 return $ptgRef . $ext_ref. $row . $col;
3050 * Convert the sheet name part of an external reference, for example "Sheet1" or
3051 * "Sheet1:Sheet2", to a packed structure.
3054 * @param string $ext_ref The name of the external reference
3055 * @return string The reference index in packed() format
3057 function _packExtRef($ext_ref)
3059 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
3060 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
3062 // Check if there is a sheet range eg., Sheet1:Sheet2.
3063 if (preg_match("/:/", $ext_ref)) {
3064 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
3066 $sheet1 = $this->_getSheetIndex($sheet_name1);
3067 if ($sheet1 == -1) {
3068 die("Unknown sheet name $sheet_name1 in formula");
3070 $sheet2 = $this->_getSheetIndex($sheet_name2);
3071 if ($sheet2 == -1) {
3072 die("Unknown sheet name $sheet_name2 in formula");
3075 // Reverse max and min sheet numbers if necessary
3076 if ($sheet1 > $sheet2) {
3077 list($sheet1, $sheet2) = array($sheet2, $sheet1);
3079 } else { // Single sheet name only.
3080 $sheet1 = $this->_getSheetIndex($ext_ref);
3081 if ($sheet1 == -1) {
3082 die("Unknown sheet name $ext_ref in formula");
3087 // References are stored relative to 0xFFFF.
3088 $offset = -1 - $sheet1;
3090 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
3094 * Look up the REF index that corresponds to an external sheet name
3095 * (or range). If it doesn't exist yet add it to the workbook's references
3096 * array. It assumes all sheet names given must exist.
3099 * @param string $ext_ref The name of the external reference
3100 * @return mixed The reference index in packed() format on success,
3101 * PEAR_Error on failure
3103 function _getRefIndex($ext_ref)
3105 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
3106 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
3108 // Check if there is a sheet range eg., Sheet1:Sheet2.
3109 if (preg_match("/:/", $ext_ref)) {
3110 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
3112 $sheet1 = $this->_getSheetIndex($sheet_name1);
3113 if ($sheet1 == -1) {
3114 die("Unknown sheet name $sheet_name1 in formula");
3116 $sheet2 = $this->_getSheetIndex($sheet_name2);
3117 if ($sheet2 == -1) {
3118 die("Unknown sheet name $sheet_name2 in formula");
3121 // Reverse max and min sheet numbers if necessary
3122 if ($sheet1 > $sheet2) {
3123 list($sheet1, $sheet2) = array($sheet2, $sheet1);
3125 } else { // Single sheet name only.
3126 $sheet1 = $this->_getSheetIndex($ext_ref);
3127 if ($sheet1 == -1) {
3128 die("Unknown sheet name $ext_ref in formula");
3133 // assume all references belong to this document
3134 $supbook_index = 0x00;
3135 $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
3136 $total_references = count($this->_references);
3138 for ($i = 0; $i < $total_references; $i++) {
3139 if ($ref == $this->_references[$i]) {
3144 // if REF was not found add it to references array
3146 $this->_references[$total_references] = $ref;
3147 $index = $total_references;
3150 return pack('v', $index);
3154 * Look up the index that corresponds to an external sheet name. The hash of
3155 * sheet names is updated by the addworksheet() method of the
3156 * Spreadsheet_Excel_Writer_Workbook class.
3159 * @return integer The sheet index, -1 if the sheet was not found
3161 function _getSheetIndex($sheet_name)
3163 if (!isset($this->_ext_sheets[$sheet_name])) {
3166 return $this->_ext_sheets[$sheet_name];
3171 * This method is used to update the array of sheet names. It is
3172 * called by the addWorksheet() method of the
3173 * Spreadsheet_Excel_Writer_Workbook class.
3176 * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
3177 * @param string $name The name of the worksheet being added
3178 * @param integer $index The index of the worksheet being added
3180 function setExtSheet($name, $index)
3182 $this->_ext_sheets[$name] = $index;
3186 * pack() row and column into the required 3 or 4 byte format.
3189 * @param string $cell The Excel cell reference to be packed
3190 * @return array Array containing the row and column in packed() format
3192 function _cellToPackedRowcol($cell)
3194 $cell = strtoupper($cell);
3195 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
3197 die("Column in: $cell greater than 255");
3199 // FIXME: change for BIFF8
3200 if ($row >= 16384) {
3201 die("Row in: $cell greater than 16384 ");
3204 // Set the high bits to indicate if row or col are relative.
3205 if ($this->_BIFF_version == 0x0500) {
3206 $row |= $col_rel << 14;
3207 $row |= $row_rel << 15;
3208 $col = pack('C', $col);
3209 } elseif ($this->_BIFF_version == 0x0600) {
3210 $col |= $col_rel << 14;
3211 $col |= $row_rel << 15;
3212 $col = pack('v', $col);
3214 $row = pack('v', $row);
3216 return array($row, $col);
3220 * pack() row range into the required 3 or 4 byte format.
3221 * Just using maximum col/rows, which is probably not the correct solution
3224 * @param string $range The Excel range to be packed
3225 * @return array Array containing (row1,col1,row2,col2) in packed() format
3227 function _rangeToPackedRange($range)
3229 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
3230 // return absolute rows if there is a $ in the ref
3231 $row1_rel = empty($match[1]) ? 1 : 0;
3233 $row2_rel = empty($match[3]) ? 1 : 0;
3235 // Convert 1-index to zero-index
3238 // Trick poor inocent Excel
3240 $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
3242 // FIXME: this changes for BIFF8
3243 if (($row1 >= 16384) or ($row2 >= 16384)) {
3244 die("Row in: $range greater than 16384 ");
3247 // Set the high bits to indicate if rows are relative.
3248 if ($this->_BIFF_version == 0x0500) {
3249 $row1 |= $row1_rel << 14; // FIXME: probably a bug
3250 $row2 |= $row2_rel << 15;
3251 $col1 = pack('C', $col1);
3252 $col2 = pack('C', $col2);
3253 } elseif ($this->_BIFF_version == 0x0600) {
3254 $col1 |= $row1_rel << 15;
3255 $col2 |= $row2_rel << 15;
3256 $col1 = pack('v', $col1);
3257 $col2 = pack('v', $col2);
3259 $row1 = pack('v', $row1);
3260 $row2 = pack('v', $row2);
3262 return array($row1, $col1, $row2, $col2);
3266 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
3267 * indexed row and column number. Also returns two (0,1) values to indicate
3268 * whether the row or column are relative references.
3271 * @param string $cell The Excel cell reference in A1 format.
3274 function _cellToRowcol($cell)
3276 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
3277 // return absolute column if there is a $ in the ref
3278 $col_rel = empty($match[1]) ? 1 : 0;
3279 $col_ref = $match[2];
3280 $row_rel = empty($match[3]) ? 1 : 0;
3283 // Convert base26 column string to a number.
3284 $expn = strlen($col_ref) - 1;
3286 $col_ref_length = strlen($col_ref);
3287 for ($i = 0; $i < $col_ref_length; $i++) {
3288 $col += (ord($col_ref[$i]) - ord('A') + 1) * pow(26, $expn);
3292 // Convert 1-index to zero-index
3296 return array($row, $col, $row_rel, $col_rel);
3300 * Advance to the next valid token.
3306 $i = $this->_current_char;
3307 $formula_length = strlen($this->_formula);
3308 // eat up white spaces
3309 if ($i < $formula_length) {
3310 while ($this->_formula[$i] == " ") {
3314 if ($i < ($formula_length - 1)) {
3315 $this->_lookahead = $this->_formula[$i+1];
3320 while ($i < $formula_length) {
3321 $token .= $this->_formula[$i];
3322 if ($i < ($formula_length - 1)) {
3323 $this->_lookahead = $this->_formula[$i+1];
3325 $this->_lookahead = '';
3328 if ($this->_match($token) != '') {
3329 //if ($i < strlen($this->_formula) - 1) {
3330 // $this->_lookahead = $this->_formula{$i+1};
3332 $this->_current_char = $i + 1;
3333 $this->_current_token = $token;
3337 if ($i < ($formula_length - 2)) {
3338 $this->_lookahead = $this->_formula[$i+2];
3339 } else { // if we run out of characters _lookahead becomes empty
3340 $this->_lookahead = '';
3344 //die("Lexical error ".$this->_current_char);
3348 * Checks if it's a valid token.
3351 * @param mixed $token The token to check.
3352 * @return mixed The checked token or false on failure
3354 function _match($token)
3357 case SPREADSHEET_EXCEL_WRITER_ADD:
3360 case SPREADSHEET_EXCEL_WRITER_SUB:
3363 case SPREADSHEET_EXCEL_WRITER_MUL:
3366 case SPREADSHEET_EXCEL_WRITER_DIV:
3369 case SPREADSHEET_EXCEL_WRITER_OPEN:
3372 case SPREADSHEET_EXCEL_WRITER_CLOSE:
3375 case SPREADSHEET_EXCEL_WRITER_COMA:
3378 case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
3381 case SPREADSHEET_EXCEL_WRITER_GT:
3382 if ($this->_lookahead == '=') { // it's a GE token
3387 case SPREADSHEET_EXCEL_WRITER_LT:
3388 // it's a LE or a NE token
3389 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
3394 case SPREADSHEET_EXCEL_WRITER_GE:
3397 case SPREADSHEET_EXCEL_WRITER_LE:
3400 case SPREADSHEET_EXCEL_WRITER_EQ:
3403 case SPREADSHEET_EXCEL_WRITER_NE:
3407 // if it's a reference
3408 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
3409 !preg_match("/[0-9]/",$this->_lookahead) and
3410 ($this->_lookahead != ':') and ($this->_lookahead != '.') and
3411 ($this->_lookahead != '!'))
3415 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
3416 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
3417 !preg_match("/[0-9]/",$this->_lookahead) and
3418 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3422 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3423 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
3424 !preg_match("/[0-9]/",$this->_lookahead) and
3425 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3429 // if it's a range (A1:A2)
3430 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3431 !preg_match("/[0-9]/",$this->_lookahead))
3435 // if it's a range (A1..A2)
3436 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3437 !preg_match("/[0-9]/",$this->_lookahead))
3441 // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
3442 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3443 !preg_match("/[0-9]/",$this->_lookahead))
3447 // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
3448 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3449 !preg_match("/[0-9]/",$this->_lookahead))
3453 // If it's a number (check that it's not a sheet name or range)
3454 elseif (is_numeric($token) and
3455 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
3456 ($this->_lookahead != '!') and ($this->_lookahead != ':'))
3460 // If it's a string (of maximum 255 characters)
3461 elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
3465 // if it's a function call
3466 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
3475 * The parsing method. It parses a formula.
3478 * @param string $formula The formula to parse, without the initial equal
3480 * @return mixed true on success, PEAR_Error on failure
3482 function parse($formula)
3484 $this->_current_char = 0;
3485 $this->_formula = $formula;
3486 $this->_lookahead = $formula[1];
3488 $this->_parse_tree = $this->_condition();
3492 * It parses a condition. It assumes the following rule:
3493 * Cond -> Expr [(">" | "<") Expr]
3496 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3498 function _condition()
3500 $result = $this->_expression();
3501 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
3503 $result2 = $this->_expression();
3504 $result = $this->_createTree('ptgLT', $result, $result2);
3505 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
3507 $result2 = $this->_expression();
3508 $result = $this->_createTree('ptgGT', $result, $result2);
3509 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
3511 $result2 = $this->_expression();
3512 $result = $this->_createTree('ptgLE', $result, $result2);
3513 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
3515 $result2 = $this->_expression();
3516 $result = $this->_createTree('ptgGE', $result, $result2);
3517 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
3519 $result2 = $this->_expression();
3520 $result = $this->_createTree('ptgEQ', $result, $result2);
3521 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
3523 $result2 = $this->_expression();
3524 $result = $this->_createTree('ptgNE', $result, $result2);
3530 * It parses a expression. It assumes the following rule:
3531 * Expr -> Term [("+" | "-") Term]
3536 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3538 function _expression()
3540 // If it's a string return a string node
3541 if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
3542 $result = $this->_createTree($this->_current_token, '', '');
3545 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
3548 $result2 = $this->_expression();
3549 $result = $this->_createTree('ptgUminus', $result2, '');
3552 $result = $this->_term();
3553 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
3554 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) {
3556 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) {
3558 $result2 = $this->_term();
3559 $result = $this->_createTree('ptgAdd', $result, $result2);
3562 $result2 = $this->_term();
3563 $result = $this->_createTree('ptgSub', $result, $result2);
3570 * This function just introduces a ptgParen element in the tree, so that Excel
3571 * doesn't get confused when working with a parenthesized formula afterwards.
3575 * @return array The parsed ptg'd tree
3577 function _parenthesizedExpression()
3579 $result = $this->_createTree('ptgParen', $this->_expression(), '');
3584 * It parses a term. It assumes the following rule:
3585 * Term -> Fact [("*" | "/") Fact]
3588 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3592 $result = $this->_fact();
3593 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
3594 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
3596 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) {
3598 $result2 = $this->_fact();
3599 $result = $this->_createTree('ptgMul', $result, $result2);
3602 $result2 = $this->_fact();
3603 $result = $this->_createTree('ptgDiv', $result, $result2);
3610 * It parses a factor. It assumes the following rule:
3618 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3622 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
3623 $this->_advance(); // eat the "("
3624 $result = $this->_parenthesizedExpression();
3625 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
3626 die("')' token expected.");
3628 $this->_advance(); // eat the ")"
3631 // if it's a reference
3632 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
3634 $result = $this->_createTree($this->_current_token, '', '');
3638 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
3639 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3641 $result = $this->_createTree($this->_current_token, '', '');
3645 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3646 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3648 $result = $this->_createTree($this->_current_token, '', '');
3653 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
3654 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
3656 $result = $this->_current_token;
3660 // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
3661 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
3663 $result = $this->_current_token;
3667 // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
3668 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$this->_current_token))
3670 $result = $this->_current_token;
3674 elseif (is_numeric($this->_current_token))
3676 $result = $this->_createTree($this->_current_token, '', '');
3680 // if it's a function call
3681 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
3683 $result = $this->_func();
3686 die("Syntax error: ".$this->_current_token.
3687 ", lookahead: ".$this->_lookahead.
3688 ", current char: ".$this->_current_char);
3692 * It parses a function call. It assumes the following rule:
3693 * Func -> ( Expr [,Expr]* )
3696 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3700 $num_args = 0; // number of arguments received
3701 $function = strtoupper($this->_current_token);
3702 $result = ''; // initialize result
3704 $this->_advance(); // eat the "("
3705 while ($this->_current_token != ')') {
3707 if ($num_args > 0) {
3708 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or
3709 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
3711 $this->_advance(); // eat the "," or ";"
3713 die("Syntax error: comma expected in ".
3714 "function $function, arg #{$num_args}");
3716 $result2 = $this->_condition();
3717 $result = $this->_createTree('arg', $result, $result2);
3718 } else { // first argument
3719 $result2 = $this->_condition();
3720 $result = $this->_createTree('arg', '', $result2);
3724 if (!isset($this->_functions[$function])) {
3725 die("Function $function() doesn't exist");
3727 $args = $this->_functions[$function][1];
3728 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
3729 if (($args >= 0) and ($args != $num_args)) {
3730 die("Incorrect number of arguments in function $function() ");
3733 $result = $this->_createTree($function, $result, $num_args);
3734 $this->_advance(); // eat the ")"
3739 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
3743 * @param mixed $value The value of this node.
3744 * @param mixed $left The left array (sub-tree) or a final node.
3745 * @param mixed $right The right array (sub-tree) or a final node.
3746 * @return array A tree
3748 function _createTree($value, $left, $right)
3750 return array('value' => $value, 'left' => $left, 'right' => $right);
3754 * Builds a string containing the tree in reverse polish notation (What you
3755 * would use in a HP calculator stack).
3756 * The following tree:
3764 * The following tree:
3772 * produces: "36A1*+"
3774 * In fact all operands, functions, references, etc... are written as ptg's
3777 * @param array $tree The optional tree to convert.
3778 * @return string The tree in reverse polish notation
3780 function toReversePolish($tree = array())
3782 $polish = ""; // the string we are going to return
3783 if (empty($tree)) { // If it's the first call use _parse_tree
3784 $tree = $this->_parse_tree;
3786 if (is_array($tree['left'])) {
3787 $converted_tree = $this->toReversePolish($tree['left']);
3788 $polish .= $converted_tree;
3789 } elseif ($tree['left'] != '') { // It's a final node
3790 $converted_tree = $this->_convert($tree['left']);
3791 $polish .= $converted_tree;
3793 if (is_array($tree['right'])) {
3794 $converted_tree = $this->toReversePolish($tree['right']);
3795 $polish .= $converted_tree;
3796 } elseif ($tree['right'] != '') { // It's a final node
3797 $converted_tree = $this->_convert($tree['right']);
3798 $polish .= $converted_tree;
3800 // if it's a function convert it here (so we can set it's arguments)
3801 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
3802 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
3803 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
3804 !is_numeric($tree['value']) and
3805 !isset($this->ptg[$tree['value']]))
3807 // left subtree for a function is always an array.
3808 if ($tree['left'] != '') {
3809 $left_tree = $this->toReversePolish($tree['left']);
3813 // add it's left subtree and return.
3814 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
3816 $converted_tree = $this->_convert($tree['value']);
3818 $polish .= $converted_tree;
3824 * Class for generating Excel Spreadsheets
3826 * @author Xavier Noguer <xnoguer@rezebra.com>
3827 * @category FileFormats
3828 * @package Spreadsheet_Excel_Writer
3831 class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter
3834 * Name of the Worksheet
3840 * Index for the Worksheet
3846 * Reference to the (default) Format object for URLs
3847 * @var object Format
3852 * Reference to the parser used for parsing formulas
3853 * @var object Format
3858 * Filehandle to the temporary file for storing data
3864 * Boolean indicating if we are using a temporary file for storing data
3867 var $_using_tmpfile;
3870 * Maximum number of rows for an Excel spreadsheet (BIFF5)
3876 * Maximum number of columns for an Excel spreadsheet (BIFF5)
3882 * Maximum number of characters for a string (LABEL record in BIFF5)
3888 * First row for the DIMENSIONS record
3890 * @see _storeDimensions()
3895 * Last row for the DIMENSIONS record
3897 * @see _storeDimensions()
3902 * First column for the DIMENSIONS record
3904 * @see _storeDimensions()
3909 * Last column for the DIMENSIONS record
3911 * @see _storeDimensions()
3916 * Array containing format information for columns
3922 * Array containing the selected area for the worksheet
3928 * Array containing the panes for the worksheet
3934 * The active pane for the worksheet
3940 * Bit specifying if panes are frozen
3946 * Bit specifying if the worksheet is selected
3952 * The paper size (for printing) (DOCUMENT!!!)
3958 * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait
3964 * The page header caption
3970 * The page footer caption
3976 * The horizontal centering value for the page
3982 * The vertical centering value for the page
3988 * The margin for the header
3994 * The margin for the footer
4000 * The left margin for the worksheet in inches
4006 * The right margin for the worksheet in inches
4012 * The top margin for the worksheet in inches
4018 * The bottom margin for the worksheet in inches
4021 var $_margin_bottom;
4024 * First row to reapeat on each printed page
4030 * Last row to reapeat on each printed page
4036 * First column to reapeat on each printed page
4042 * First row of the area to print
4048 * Last row to of the area to print
4054 * First column of the area to print
4060 * Last column of the area to print
4066 * Whether to use outline.
4072 * Auto outline styles.
4075 var $_outline_style;
4078 * Whether to have outline summary below.
4081 var $_outline_below;
4084 * Whether to have outline summary at the right.
4087 var $_outline_right;
4090 * Outline row level.
4093 var $_outline_row_level;
4096 * Whether to fit to page when printing or not.
4102 * Number of pages to fit wide
4108 * Number of pages to fit high
4114 * Reference to the total number of strings in the workbook
4120 * Reference to the number of unique strings in the workbook
4126 * Reference to the array containing all the unique strings in the workbook
4132 * Merged cell ranges
4135 var $_merged_ranges;
4138 * Charset encoding currently used when calling writeString()
4141 var $_input_encoding;
4142 // Dynamic members must be here (php8.2)
4146 var $_print_gridlines;
4147 var $_screen_gridlines;
4148 var $_print_headers;
4164 * @param string $name The name of the new worksheet
4165 * @param integer $index The index of the new worksheet
4166 * @param mixed &$activesheet The current activesheet of the workbook we belong to
4167 * @param mixed &$firstsheet The first worksheet in the workbook we belong to
4168 * @param mixed &$url_format The default format for hyperlinks
4169 * @param mixed &$parser The formula parser created for the Workbook
4172 function __construct($BIFF_version, $name,
4173 $index, &$activesheet,
4174 &$firstsheet, &$str_total,
4175 &$str_unique, &$str_table,
4176 &$url_format, &$parser)
4178 // It needs to call its parent's constructor explicitly
4179 parent::__construct();
4180 $this->_BIFF_version = $BIFF_version;
4181 $rowmax = 65536; // 16384 in Excel 5
4184 $this->name = $name;
4185 $this->index = $index;
4186 $this->activesheet = &$activesheet;
4187 $this->firstsheet = &$firstsheet;
4188 $this->_str_total = &$str_total;
4189 $this->_str_unique = &$str_unique;
4190 $this->_str_table = &$str_table;
4191 $this->_url_format = &$url_format;
4192 $this->_parser = &$parser;
4194 //$this->ext_sheets = array();
4195 $this->_filehandle = '';
4196 $this->_using_tmpfile = true;
4197 //$this->fileclosed = 0;
4198 //$this->offset = 0;
4199 $this->_xls_rowmax = $rowmax;
4200 $this->_xls_colmax = $colmax;
4201 $this->_xls_strmax = 255;
4202 $this->_dim_rowmin = $rowmax + 1;
4203 $this->_dim_rowmax = 0;
4204 $this->_dim_colmin = $colmax + 1;
4205 $this->_dim_colmax = 0;
4206 $this->_colinfo = array();
4207 $this->_selection = array(0,0,0,0);
4208 $this->_panes = array();
4209 $this->_active_pane = 3;
4211 $this->selected = 0;
4213 $this->_paper_size = 0x0;
4214 $this->_orientation = 0x1;
4215 $this->_header = '';
4216 $this->_footer = '';
4217 $this->_hcenter = 0;
4218 $this->_vcenter = 0;
4219 $this->_margin_head = 0.50;
4220 $this->_margin_foot = 0.50;
4221 $this->_margin_left = 0.75;
4222 $this->_margin_right = 0.75;
4223 $this->_margin_top = 1.00;
4224 $this->_margin_bottom = 1.00;
4226 $this->title_rowmin = null;
4227 $this->title_rowmax = null;
4228 $this->title_colmin = null;
4229 $this->title_colmax = null;
4230 $this->print_rowmin = null;
4231 $this->print_rowmax = null;
4232 $this->print_colmin = null;
4233 $this->print_colmax = null;
4235 $this->_print_gridlines = 1;
4236 $this->_screen_gridlines = 1;
4237 $this->_print_headers = 0;
4239 $this->_fit_page = 0;
4240 $this->_fit_width = 0;
4241 $this->_fit_height = 0;
4243 $this->_hbreaks = array();
4244 $this->_vbreaks = array();
4246 $this->_protect = 0;
4247 $this->_password = null;
4249 $this->col_sizes = array();
4250 $this->_row_sizes = array();
4253 $this->_print_scale = 100;
4255 $this->_outline_row_level = 0;
4256 $this->_outline_style = 0;
4257 $this->_outline_below = 1;
4258 $this->_outline_right = 1;
4259 $this->_outline_on = 1;
4261 $this->_merged_ranges = array();
4263 $this->_rtl = 0; // Added by Joe Hunt 2009-03-05 for arabic languages
4264 $this->_input_encoding = '';
4266 $this->_dv = array();
4268 $this->_initialize();
4272 * Open a tmp file to store the majority of the Worksheet data. If this fails,
4273 * for example due to write permissions, store the data in memory. This can be
4274 * slow for large files.
4278 function _initialize()
4280 // Open tmp file for storing Worksheet data
4284 $this->_filehandle = $fh;
4286 // If tmpfile() fails store data in memory
4287 $this->_using_tmpfile = false;
4292 * Add data to the beginning of the workbook (note the reverse order)
4293 * and to the end of the workbook.
4296 * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
4297 * @param array $sheetnames The array of sheetnames from the Workbook this
4298 * worksheet belongs to
4300 function close($sheetnames)
4302 $num_sheets = count($sheetnames);
4304 /***********************************************
4305 * Prepend in reverse order!!
4308 // Prepend the sheet dimensions
4309 $this->_storeDimensions();
4311 // Prepend the sheet password
4312 $this->_storePassword();
4314 // Prepend the sheet protection
4315 $this->_storeProtect();
4317 // Prepend the page setup
4318 $this->_storeSetup();
4320 /* FIXME: margins are actually appended */
4321 // Prepend the bottom margin
4322 $this->_storeMarginBottom();
4324 // Prepend the top margin
4325 $this->_storeMarginTop();
4327 // Prepend the right margin
4328 $this->_storeMarginRight();
4330 // Prepend the left margin
4331 $this->_storeMarginLeft();
4333 // Prepend the page vertical centering
4334 $this->_storeVcenter();
4336 // Prepend the page horizontal centering
4337 $this->_storeHcenter();
4339 // Prepend the page footer
4340 $this->_storeFooter();
4342 // Prepend the page header
4343 $this->_storeHeader();
4345 // Prepend the vertical page breaks
4346 $this->_storeVbreak();
4348 // Prepend the horizontal page breaks
4349 $this->_storeHbreak();
4352 $this->_storeWsbool();
4355 $this->_storeGridset();
4358 if ($this->_BIFF_version == 0x0500) {
4359 $this->_storeGuts();
4362 // Prepend PRINTGRIDLINES
4363 $this->_storePrintGridlines();
4365 // Prepend PRINTHEADERS
4366 $this->_storePrintHeaders();
4368 // Prepend EXTERNSHEET references
4369 if ($this->_BIFF_version == 0x0500) {
4370 for ($i = $num_sheets; $i > 0; $i--) {
4371 $sheetname = $sheetnames[$i-1];
4372 $this->_storeExternsheet($sheetname);
4376 // Prepend the EXTERNCOUNT of external references.
4377 if ($this->_BIFF_version == 0x0500) {
4378 $this->_storeExterncount($num_sheets);
4381 // Prepend the COLINFO records if they exist
4382 if (!empty($this->_colinfo)) {
4383 $colcount = count($this->_colinfo);
4384 for ($i = 0; $i < $colcount; $i++) {
4385 $this->_storeColinfo($this->_colinfo[$i]);
4387 $this->_storeDefcol();
4390 // Prepend the BOF record
4391 $this->_storeBof(0x0010);
4394 * End of prepend. Read upwards from here.
4395 ***********************************************/
4398 $this->_storeWindow2();
4399 $this->_storeZoom();
4400 if (!empty($this->_panes)) {
4401 $this->_storePanes($this->_panes);
4403 $this->_storeSelection($this->_selection);
4404 $this->_storeMergedCells();
4405 /* TODO: add data validity */
4406 /*if ($this->_BIFF_version == 0x0600) {
4407 $this->_storeDataValidity();
4413 * Retrieve the worksheet name.
4414 * This is usefull when creating worksheets without a name.
4417 * @return string The worksheet's name
4425 * Retrieves data from memory in one chunk, or from disk in $buffer
4428 * @return string The data
4434 // Return data stored in memory
4435 if (isset($this->_data)) {
4436 $tmp = $this->_data;
4437 unset($this->_data);
4438 $fh = $this->_filehandle;
4439 if ($this->_using_tmpfile) {
4444 // Return data stored on disk
4445 if ($this->_using_tmpfile) {
4446 if ($tmp = fread($this->_filehandle, $buffer)) {
4451 // No data to return
4456 * Sets a merged cell range
4459 * @param integer $first_row First row of the area to merge
4460 * @param integer $first_col First column of the area to merge
4461 * @param integer $last_row Last row of the area to merge
4462 * @param integer $last_col Last column of the area to merge
4464 function setMerge($first_row, $first_col, $last_row, $last_col)
4466 if (($last_row < $first_row) || ($last_col < $first_col)) {
4469 // don't check rowmin, rowmax, etc... because we don't know when this
4470 // is going to be called
4471 $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
4475 * Set this worksheet as a selected worksheet,
4476 * i.e. the worksheet has its tab highlighted.
4482 $this->selected = 1;
4486 * Set this worksheet as the active worksheet,
4487 * i.e. the worksheet that is displayed when the workbook is opened.
4488 * Also set it as selected.
4494 $this->selected = 1;
4495 $this->activesheet = $this->index;
4499 * Set this worksheet as the first visible sheet.
4500 * This is necessary when there are a large number of worksheets and the
4501 * activated worksheet is not visible on the screen.
4505 function setFirstSheet()
4507 $this->firstsheet = $this->index;
4511 * Set the worksheet protection flag
4512 * to prevent accidental modification and to
4513 * hide formulas if the locked and hidden format properties have been set.
4516 * @param string $password The password to use for protecting the sheet.
4518 function protect($password)
4520 $this->_protect = 1;
4521 $this->_password = $this->_encodePassword($password);
4525 * Set the width of a single column or a range of columns.
4528 * @param integer $firstcol first column on the range
4529 * @param integer $lastcol last column on the range
4530 * @param integer $width width to set
4531 * @param mixed $format The optional XF format to apply to the columns
4532 * @param integer $hidden The optional hidden atribute
4533 * @param integer $level The optional outline level
4535 function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
4537 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
4539 // Set width to zero if column is hidden
4540 $width = ($hidden) ? 0 : $width;
4542 for ($col = $firstcol; $col <= $lastcol; $col++) {
4543 $this->col_sizes[$col] = $width;
4548 * Set which cell or cells are selected in a worksheet
4551 * @param integer $first_row first row in the selected quadrant
4552 * @param integer $first_column first column in the selected quadrant
4553 * @param integer $last_row last row in the selected quadrant
4554 * @param integer $last_column last column in the selected quadrant
4556 function setSelection($first_row,$first_column,$last_row,$last_column)
4558 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
4562 * Set panes and mark them as frozen.
4565 * @param array $panes This is the only parameter received and is composed of the following:
4566 * 0 => Vertical split position,
4567 * 1 => Horizontal split position
4568 * 2 => Top row visible
4569 * 3 => Leftmost column visible
4572 function freezePanes($panes)
4575 $this->_panes = $panes;
4579 * Set panes and mark them as unfrozen.
4582 * @param array $panes This is the only parameter received and is composed of the following:
4583 * 0 => Vertical split position,
4584 * 1 => Horizontal split position
4585 * 2 => Top row visible
4586 * 3 => Leftmost column visible
4589 function thawPanes($panes)
4592 $this->_panes = $panes;
4596 * Set the page orientation as portrait.
4600 function setPortrait()
4602 $this->_orientation = 1;
4606 * Set the page orientation as landscape.
4610 function setLandscape()
4612 $this->_orientation = 0;
4616 * Set the paper type. Ex. 1 = US Letter, 9 = A4
4619 * @param integer $size The type of paper size to use
4621 function setPaper($size = 0)
4623 $this->_paper_size = $size;
4628 * Set the page header caption and optional margin.
4631 * @param string $string The header text
4632 * @param float $margin optional head margin in inches.
4634 function setHeader($string,$margin = 0.50)
4636 if (strlen($string) >= 255) {
4637 //carp 'Header string must be less than 255 characters';
4640 $this->_header = $string;
4641 $this->_margin_head = $margin;
4645 * Set the page footer caption and optional margin.
4648 * @param string $string The footer text
4649 * @param float $margin optional foot margin in inches.
4651 function setFooter($string,$margin = 0.50)
4653 if (strlen($string) >= 255) {
4654 //carp 'Footer string must be less than 255 characters';
4657 $this->_footer = $string;
4658 $this->_margin_foot = $margin;
4662 * Center the page horinzontally.
4665 * @param integer $center the optional value for centering. Defaults to 1 (center).
4667 function centerHorizontally($center = 1)
4669 $this->_hcenter = $center;
4673 * Center the page vertically.
4676 * @param integer $center the optional value for centering. Defaults to 1 (center).
4678 function centerVertically($center = 1)
4680 $this->_vcenter = $center;
4684 * Set all the page margins to the same value in inches.
4687 * @param float $margin The margin to set in inches
4689 function setMargins($margin)
4691 $this->setMarginLeft($margin);
4692 $this->setMarginRight($margin);
4693 $this->setMarginTop($margin);
4694 $this->setMarginBottom($margin);
4698 * Set the left and right margins to the same value in inches.
4701 * @param float $margin The margin to set in inches
4703 function setMargins_LR($margin)
4705 $this->setMarginLeft($margin);
4706 $this->setMarginRight($margin);
4710 * Set the top and bottom margins to the same value in inches.
4713 * @param float $margin The margin to set in inches
4715 function setMargins_TB($margin)
4717 $this->setMarginTop($margin);
4718 $this->setMarginBottom($margin);
4722 * Set the left margin in inches.
4725 * @param float $margin The margin to set in inches
4727 function setMarginLeft($margin = 0.75)
4729 $this->_margin_left = $margin;
4733 * Set the right margin in inches.
4736 * @param float $margin The margin to set in inches
4738 function setMarginRight($margin = 0.75)
4740 $this->_margin_right = $margin;
4744 * Set the top margin in inches.
4747 * @param float $margin The margin to set in inches
4749 function setMarginTop($margin = 1.00)
4751 $this->_margin_top = $margin;
4755 * Set the bottom margin in inches.
4758 * @param float $margin The margin to set in inches
4760 function setMarginBottom($margin = 1.00)
4762 $this->_margin_bottom = $margin;
4766 * Set the rows to repeat at the top of each printed page.
4769 * @param integer $first_row First row to repeat
4770 * @param integer $last_row Last row to repeat. Optional.
4772 function repeatRows($first_row, $last_row = null)
4774 $this->title_rowmin = $first_row;
4775 if (isset($last_row)) { //Second row is optional
4776 $this->title_rowmax = $last_row;
4778 $this->title_rowmax = $first_row;
4783 * Set the columns to repeat at the left hand side of each printed page.
4786 * @param integer $first_col First column to repeat
4787 * @param integer $last_col Last column to repeat. Optional.
4789 function repeatColumns($first_col, $last_col = null)
4791 $this->title_colmin = $first_col;
4792 if (isset($last_col)) { // Second col is optional
4793 $this->title_colmax = $last_col;
4795 $this->title_colmax = $first_col;
4800 * Set the area of each worksheet that will be printed.
4803 * @param integer $first_row First row of the area to print
4804 * @param integer $first_col First column of the area to print
4805 * @param integer $last_row Last row of the area to print
4806 * @param integer $last_col Last column of the area to print
4808 function printArea($first_row, $first_col, $last_row, $last_col)
4810 $this->print_rowmin = $first_row;
4811 $this->print_colmin = $first_col;
4812 $this->print_rowmax = $last_row;
4813 $this->print_colmax = $last_col;
4818 * Set the option to hide gridlines on the printed page.
4822 function hideGridlines()
4824 $this->_print_gridlines = 0;
4828 * Set the option to hide gridlines on the worksheet (as seen on the screen).
4832 function hideScreenGridlines()
4834 $this->_screen_gridlines = 0;
4838 * Set the option to print the row and column headers on the printed page.
4841 * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
4843 function printRowColHeaders($print = 1)
4845 $this->_print_headers = $print;
4849 * Set the vertical and horizontal number of pages that will define the maximum area printed.
4850 * It doesn't seem to work with OpenOffice.
4853 * @param integer $width Maximun width of printed area in pages
4854 * @param integer $height Maximun heigth of printed area in pages
4855 * @see setPrintScale()
4857 function fitToPages($width, $height)
4859 $this->_fit_page = 1;
4860 $this->_fit_width = $width;
4861 $this->_fit_height = $height;
4865 * Store the horizontal page breaks on a worksheet (for printing).
4866 * The breaks represent the row after which the break is inserted.
4869 * @param array $breaks Array containing the horizontal page breaks
4871 function setHPagebreaks($breaks)
4873 foreach ($breaks as $break) {
4874 array_push($this->_hbreaks, $break);
4879 * Store the vertical page breaks on a worksheet (for printing).
4880 * The breaks represent the column after which the break is inserted.
4883 * @param array $breaks Array containing the vertical page breaks
4885 function setVPagebreaks($breaks)
4887 foreach ($breaks as $break) {
4888 array_push($this->_vbreaks, $break);
4894 * Set the worksheet zoom factor.
4897 * @param integer $scale The zoom factor
4899 function setZoom($scale = 100)
4901 // Confine the scale to Excel's range
4902 if ($scale < 10 || $scale > 400) {
4906 $this->_zoom = floor($scale);
4910 * Set the scale factor for the printed page.
4911 * It turns off the "fit to page" option
4914 * @param integer $scale The optional scale factor. Defaults to 100
4916 function setPrintScale($scale = 100)
4918 // Confine the scale to Excel's range
4919 if ($scale < 10 || $scale > 400) {
4923 // Turn off "fit to page" option
4924 $this->_fit_page = 0;
4926 $this->_print_scale = floor($scale);
4930 * Map to the appropriate write method acording to the token recieved.
4933 * @param integer $row The row of the cell we are writing to
4934 * @param integer $col The column of the cell we are writing to
4935 * @param mixed $token What we are writing
4936 * @param mixed $format The optional format to apply to the cell
4938 function write($row, $col, $token, $format = null)
4940 // Check for a cell reference in A1 notation and substitute row and column
4941 /*if ($_[0] =~ /^\D/) {
4942 @_ = $this->_substituteCellref(@_);
4945 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
4947 return $this->writeNumber($row, $col, $token, $format);
4948 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
4949 // Match http or ftp URL
4950 return $this->writeUrl($row, $col, $token, '', $format);
4951 } elseif (preg_match("/^mailto:/", $token)) {
4953 return $this->writeUrl($row, $col, $token, '', $format);
4954 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
4955 // Match internal or external sheet link
4956 return $this->writeUrl($row, $col, $token, '', $format);
4957 } elseif (preg_match("/^=/", $token)) {
4959 return $this->writeFormula($row, $col, $token, $format);
4960 } elseif (preg_match("/^@/", $token)) {
4962 return $this->writeFormula($row, $col, $token, $format);
4963 } elseif ($token == '') {
4965 return $this->writeBlank($row, $col, $format);
4967 // Default: match string
4968 return $this->writeString($row, $col, $token, $format);
4973 * Write an array of values as a row
4976 * @param integer $row The row we are writing to
4977 * @param integer $col The first col (leftmost col) we are writing to
4978 * @param array $val The array of values to write
4979 * @param mixed $format The optional format to apply to the cell
4980 * @return mixed PEAR_Error on failure
4983 function writeRow($row, $col, $val, $format = null)
4986 if (is_array($val)) {
4987 foreach ($val as $v) {
4989 $this->writeCol($row, $col, $v, $format);
4991 $this->write($row, $col, $v, $format);
4996 die('$val needs to be an array');
5002 * Write an array of values as a column
5005 * @param integer $row The first row (uppermost row) we are writing to
5006 * @param integer $col The col we are writing to
5007 * @param array $val The array of values to write
5008 * @param mixed $format The optional format to apply to the cell
5009 * @return mixed PEAR_Error on failure
5012 function writeCol($row, $col, $val, $format = null)
5015 if (is_array($val)) {
5016 foreach ($val as $v) {
5017 $this->write($row, $col, $v, $format);
5021 die('$val needs to be an array');
5027 * Returns an index to the XF record in the workbook
5030 * @param mixed &$format The optional XF format
5031 * @return integer The XF record index
5033 function _XF(&$format)
5036 return($format->getXfIndex());
5043 /******************************************************************************
5044 *******************************************************************************
5051 * Store Worksheet data in memory using the parent's class append() or to a
5052 * temporary file, the default.
5055 * @param string $data The binary data to append
5057 function _append($data)
5059 if ($this->_using_tmpfile) {
5060 // Add CONTINUE records if necessary
5061 if (strlen($data) > $this->_limit) {
5062 $data = $this->_addContinue($data);
5064 fwrite($this->_filehandle, $data);
5065 $this->_datasize += strlen($data);
5067 parent::_append($data);
5072 * Substitute an Excel cell reference in A1 notation for zero based row and
5073 * column values in an argument list.
5075 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
5078 * @param string $cell The cell reference. Or range of cells.
5081 function _substituteCellref($cell)
5083 $cell = strtoupper($cell);
5085 // Convert a column range: 'A:A' or 'B:G'
5086 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
5087 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
5088 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
5089 return(array($col1, $col2));
5092 // Convert a cell range: 'A1:B7'
5093 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
5094 list($row1, $col1) = $this->_cellToRowcol($match[1]);
5095 list($row2, $col2) = $this->_cellToRowcol($match[2]);
5096 return(array($row1, $col1, $row2, $col2));
5099 // Convert a cell reference: 'A1' or 'AD2000'
5100 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
5101 list($row1, $col1) = $this->_cellToRowcol($match[1]);
5102 return(array($row1, $col1));
5105 // TODO use real error codes
5106 die("Unknown cell reference $cell");
5110 * Convert an Excel cell reference in A1 notation to a zero based row and column
5111 * reference; converts C1 to (0, 2).
5114 * @param string $cell The cell reference.
5115 * @return array containing (row, column)
5117 function _cellToRowcol($cell)
5119 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
5123 // Convert base26 column string to number
5124 $chars = preg_split('//', $col);
5129 $char = array_pop($chars); // LS char first
5130 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
5134 // Convert 1-index to zero-index
5138 return(array($row, $col));
5142 * Based on the algorithm provided by Daniel Rentz of OpenOffice.
5145 * @param string $plaintext The password to be encoded in plaintext.
5146 * @return string The encoded password
5148 function _encodePassword($plaintext)
5151 $i = 1; // char position
5153 // split the plain text password in its component characters
5154 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
5155 foreach ($chars as $char) {
5156 $value = ord($char) << $i; // shifted ASCII value
5157 $rotated_bits = $value >> 15; // rotated bits beyond bit 15
5158 $value &= 0x7fff; // first 15 bits
5159 $password ^= ($value | $rotated_bits);
5163 $password ^= strlen($plaintext);
5164 $password ^= 0xCE4B;
5170 * This method sets the properties for outlining and grouping. The defaults
5171 * correspond to Excel's defaults.
5173 * @param bool $visible
5174 * @param bool $symbols_below
5175 * @param bool $symbols_right
5176 * @param bool $auto_style
5178 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
5180 $this->_outline_on = $visible;
5181 $this->_outline_below = $symbols_below;
5182 $this->_outline_right = $symbols_right;
5183 $this->_outline_style = $auto_style;
5185 // Ensure this is a boolean vale for Window2
5186 if ($this->_outline_on) {
5187 $this->_outline_on = 1;
5191 /******************************************************************************
5192 *******************************************************************************
5199 * Write a double to the specified row and column (zero indexed).
5200 * An integer can be written as a double. Excel will display an
5201 * integer. $format is optional.
5203 * Returns 0 : normal termination
5204 * -2 : row or column out of range
5207 * @param integer $row Zero indexed row
5208 * @param integer $col Zero indexed column
5209 * @param float $num The number to write
5210 * @param mixed $format The optional XF format
5213 function writeNumber($row, $col, $num, $format = null)
5215 $record = 0x0203; // Record identifier
5216 $length = 0x000E; // Number of bytes to follow
5218 $xf = $this->_XF($format); // The cell format
5220 // Check that row and col are valid and store max and min values
5221 if ($row >= $this->_xls_rowmax) {
5224 if ($col >= $this->_xls_colmax) {
5227 if ($row < $this->_dim_rowmin) {
5228 $this->_dim_rowmin = $row;
5230 if ($row > $this->_dim_rowmax) {
5231 $this->_dim_rowmax = $row;
5233 if ($col < $this->_dim_colmin) {
5234 $this->_dim_colmin = $col;
5236 if ($col > $this->_dim_colmax) {
5237 $this->_dim_colmax = $col;
5240 $header = pack("vv", $record, $length);
5241 $data = pack("vvv", $row, $col, $xf);
5242 $xl_double = pack("d", $num);
5243 if ($this->_byte_order) { // if it's Big Endian
5244 $xl_double = strrev($xl_double);
5247 $this->_append($header.$data.$xl_double);
5252 * Write a string to the specified row and column (zero indexed).
5253 * NOTE: there is an Excel 5 defined limit of 255 characters.
5254 * $format is optional.
5255 * Returns 0 : normal termination
5256 * -2 : row or column out of range
5257 * -3 : long string truncated to 255 chars
5260 * @param integer $row Zero indexed row
5261 * @param integer $col Zero indexed column
5262 * @param string $str The string to write
5263 * @param mixed $format The XF format for the cell
5266 function writeString($row, $col, $str, $format = null)
5268 if ($this->_BIFF_version == 0x0600) {
5269 return $this->writeStringBIFF8($row, $col, $str, $format);
5271 $strlen = strlen($str);
5272 $record = 0x0204; // Record identifier
5273 $length = 0x0008 + $strlen; // Bytes to follow
5274 $xf = $this->_XF($format); // The cell format
5278 // Check that row and col are valid and store max and min values
5279 if ($row >= $this->_xls_rowmax) {
5282 if ($col >= $this->_xls_colmax) {
5285 if ($row < $this->_dim_rowmin) {
5286 $this->_dim_rowmin = $row;
5288 if ($row > $this->_dim_rowmax) {
5289 $this->_dim_rowmax = $row;
5291 if ($col < $this->_dim_colmin) {
5292 $this->_dim_colmin = $col;
5294 if ($col > $this->_dim_colmax) {
5295 $this->_dim_colmax = $col;
5298 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
5299 $str = substr($str, 0, $this->_xls_strmax);
5300 $length = 0x0008 + $this->_xls_strmax;
5301 $strlen = $this->_xls_strmax;
5305 $header = pack("vv", $record, $length);
5306 $data = pack("vvvv", $row, $col, $xf, $strlen);
5307 $this->_append($header . $data . $str);
5312 * Sets Input Encoding for writing strings
5315 * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
5317 function setInputEncoding($encoding)
5319 if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
5320 die("Using an input encoding other than UTF-16LE requires PHP support for iconv");
5322 $this->_input_encoding = $encoding;
5325 /** added 2009-03-05 by Joe Hunt, FA for arabic languages */
5332 * Write a string to the specified row and column (zero indexed).
5333 * This is the BIFF8 version (no 255 chars limit).
5334 * $format is optional.
5335 * Returns 0 : normal termination
5336 * -2 : row or column out of range
5337 * -3 : long string truncated to 255 chars
5340 * @param integer $row Zero indexed row
5341 * @param integer $col Zero indexed column
5342 * @param string $str The string to write
5343 * @param mixed $format The XF format for the cell
5346 function writeStringBIFF8($row, $col, $str, $format = null)
5348 if ($this->_input_encoding == 'UTF-16LE')
5350 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
5353 elseif ($this->_input_encoding != '')
5355 $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
5356 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
5361 $strlen = strlen($str);
5364 $record = 0x00FD; // Record identifier
5365 $length = 0x000A; // Bytes to follow
5366 $xf = $this->_XF($format); // The cell format
5370 // Check that row and col are valid and store max and min values
5371 if ($this->_checkRowCol($row, $col) == false) {
5375 $str = pack('vC', $strlen, $encoding).$str;
5377 /* check if string is already present */
5378 if (!isset($this->_str_table[$str])) {
5379 $this->_str_table[$str] = $this->_str_unique++;
5381 $this->_str_total++;
5383 $header = pack('vv', $record, $length);
5384 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
5385 $this->_append($header.$data);
5390 * Check row and col before writing to a cell, and update the sheet's
5391 * dimensions accordingly
5394 * @param integer $row Zero indexed row
5395 * @param integer $col Zero indexed column
5396 * @return boolean true for success, false if row and/or col are grester
5397 * then maximums allowed.
5399 function _checkRowCol($row, $col)
5401 if ($row >= $this->_xls_rowmax) {
5404 if ($col >= $this->_xls_colmax) {
5407 if ($row < $this->_dim_rowmin) {
5408 $this->_dim_rowmin = $row;
5410 if ($row > $this->_dim_rowmax) {
5411 $this->_dim_rowmax = $row;
5413 if ($col < $this->_dim_colmin) {
5414 $this->_dim_colmin = $col;
5416 if ($col > $this->_dim_colmax) {
5417 $this->_dim_colmax = $col;
5423 * Writes a note associated with the cell given by the row and column.
5424 * NOTE records don't have a length limit.
5427 * @param integer $row Zero indexed row
5428 * @param integer $col Zero indexed column
5429 * @param string $note The note to write
5431 function writeNote($row, $col, $note)
5433 $note_length = strlen($note);
5434 $record = 0x001C; // Record identifier
5435 $max_length = 2048; // Maximun length for a NOTE record
5436 //$length = 0x0006 + $note_length; // Bytes to follow
5438 // Check that row and col are valid and store max and min values
5439 if ($row >= $this->_xls_rowmax) {
5442 if ($col >= $this->_xls_colmax) {
5445 if ($row < $this->_dim_rowmin) {
5446 $this->_dim_rowmin = $row;
5448 if ($row > $this->_dim_rowmax) {
5449 $this->_dim_rowmax = $row;
5451 if ($col < $this->_dim_colmin) {
5452 $this->_dim_colmin = $col;
5454 if ($col > $this->_dim_colmax) {
5455 $this->_dim_colmax = $col;
5458 // Length for this record is no more than 2048 + 6
5459 $length = 0x0006 + min($note_length, 2048);
5460 $header = pack("vv", $record, $length);
5461 $data = pack("vvv", $row, $col, $note_length);
5462 $this->_append($header . $data . substr($note, 0, 2048));
5464 for ($i = $max_length; $i < $note_length; $i += $max_length) {
5465 $chunk = substr($note, $i, $max_length);
5466 $length = 0x0006 + strlen($chunk);
5467 $header = pack("vv", $record, $length);
5468 $data = pack("vvv", -1, 0, strlen($chunk));
5469 $this->_append($header.$data.$chunk);
5475 * Write a blank cell to the specified row and column (zero indexed).
5476 * A blank cell is used to specify formatting without adding a string
5479 * A blank cell without a format serves no purpose. Therefore, we don't write
5480 * a BLANK record unless a format is specified.
5482 * Returns 0 : normal termination (including no format)
5483 * -1 : insufficient number of arguments
5484 * -2 : row or column out of range
5487 * @param integer $row Zero indexed row
5488 * @param integer $col Zero indexed column
5489 * @param mixed $format The XF format
5491 function writeBlank($row, $col, $format)
5493 // Don't write a blank cell unless it has a format
5498 $record = 0x0201; // Record identifier
5499 $length = 0x0006; // Number of bytes to follow
5500 $xf = $this->_XF($format); // The cell format
5502 // Check that row and col are valid and store max and min values
5503 if ($row >= $this->_xls_rowmax) {
5506 if ($col >= $this->_xls_colmax) {
5509 if ($row < $this->_dim_rowmin) {
5510 $this->_dim_rowmin = $row;
5512 if ($row > $this->_dim_rowmax) {
5513 $this->_dim_rowmax = $row;
5515 if ($col < $this->_dim_colmin) {
5516 $this->_dim_colmin = $col;
5518 if ($col > $this->_dim_colmax) {
5519 $this->_dim_colmax = $col;
5522 $header = pack("vv", $record, $length);
5523 $data = pack("vvv", $row, $col, $xf);
5524 $this->_append($header . $data);
5529 * Write a formula to the specified row and column (zero indexed).
5530 * The textual representation of the formula is passed to the parser in
5531 * Parser.php which returns a packed binary string.
5533 * Returns 0 : normal termination
5534 * -1 : formula errors (bad formula)
5535 * -2 : row or column out of range
5538 * @param integer $row Zero indexed row
5539 * @param integer $col Zero indexed column
5540 * @param string $formula The formula text string
5541 * @param mixed $format The optional XF format
5544 function writeFormula($row, $col, $formula, $format = null)
5546 $record = 0x0006; // Record identifier
5548 // Excel normally stores the last calculated value of the formula in $num.
5549 // Clearly we are not in a position to calculate this a priori. Instead
5550 // we set $num to zero and set the option flags in $grbit to ensure
5551 // automatic calculation of the formula when the file is opened.
5553 $xf = $this->_XF($format); // The cell format
5554 $num = 0x00; // Current value of formula
5555 $grbit = 0x03; // Option flags
5556 $unknown = 0x0000; // Must be zero
5559 // Check that row and col are valid and store max and min values
5560 if ($this->_checkRowCol($row, $col) == false) {
5564 // Strip the '=' or '@' sign at the beginning of the formula string
5565 if (preg_match("/^=/", $formula)) {
5566 $formula = preg_replace("/(^=)/", "", $formula);
5567 } elseif (preg_match("/^@/", $formula)) {
5568 $formula = preg_replace("/(^@)/", "", $formula);
5571 $this->writeString($row, $col, 'Unrecognised character for formula');
5575 // Parse the formula using the parser in Parser.php
5576 $this->_parser->parse($formula);
5578 $formula = $this->_parser->toReversePolish();
5580 $formlen = strlen($formula); // Length of the binary string
5581 $length = 0x16 + $formlen; // Length of the record data
5583 $header = pack("vv", $record, $length);
5584 $data = pack("vvvdvVv", $row, $col, $xf, $num,
5585 $grbit, $unknown, $formlen);
5587 $this->_append($header . $data . $formula);
5592 * Write a hyperlink.
5593 * This is comprised of two elements: the visible label and
5594 * the invisible link. The visible label is the same as the link unless an
5595 * alternative string is specified. The label is written using the
5596 * writeString() method. Therefore the 255 characters string limit applies.
5597 * $string and $format are optional.
5599 * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
5602 * Returns 0 : normal termination
5603 * -2 : row or column out of range
5604 * -3 : long string truncated to 255 chars
5607 * @param integer $row Row
5608 * @param integer $col Column
5609 * @param string $url URL string
5610 * @param string $string Alternative label
5611 * @param mixed $format The cell format
5614 function writeUrl($row, $col, $url, $string = '', $format = null)
5616 // Add start row and col to arg list
5617 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
5621 * This is the more general form of writeUrl(). It allows a hyperlink to be
5622 * written to a range of cells. This function also decides the type of hyperlink
5623 * to be written. These are either, Web (http, ftp, mailto), Internal
5624 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
5628 * @param integer $row1 Start row
5629 * @param integer $col1 Start column
5630 * @param integer $row2 End row
5631 * @param integer $col2 End column
5632 * @param string $url URL string
5633 * @param string $string Alternative label
5634 * @param mixed $format The cell format
5638 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
5641 // Check for internal/external sheet links or default to web link
5642 if (preg_match('[^internal:]', $url)) {
5643 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
5645 if (preg_match('[^external:]', $url)) {
5646 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
5648 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
5653 * Used to write http, ftp and mailto hyperlinks.
5654 * The link type ($options) is 0x03 is the same as absolute dir ref without
5655 * sheet. However it is differentiated by the $unknown2 data stream.
5659 * @param integer $row1 Start row
5660 * @param integer $col1 Start column
5661 * @param integer $row2 End row
5662 * @param integer $col2 End column
5663 * @param string $url URL string
5664 * @param string $str Alternative label
5665 * @param mixed $format The cell format
5668 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
5670 $record = 0x01B8; // Record identifier
5671 $length = 0x00000; // Bytes to follow
5674 $format = $this->_url_format;
5677 // Write the visible label using the writeString() method.
5681 $str_error = $this->writeString($row1, $col1, $str, $format);
5682 if (($str_error == -2) || ($str_error == -3)) {
5686 // Pack the undocumented parts of the hyperlink stream
5687 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5688 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
5690 // Pack the option flags
5691 $options = pack("V", 0x03);
5693 // Convert URL to a null terminated wchar string
5694 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5695 $url = $url . "\0\0\0";
5697 // Pack the length of the URL
5698 $url_len = pack("V", strlen($url));
5700 // Calculate the data length
5701 $length = 0x34 + strlen($url);
5703 // Pack the header data
5704 $header = pack("vv", $record, $length);
5705 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5707 // Write the packed data
5708 $this->_append($header . $data .
5709 $unknown1 . $options .
5710 $unknown2 . $url_len . $url);
5715 * Used to write internal reference hyperlinks such as "Sheet1!A1".
5719 * @param integer $row1 Start row
5720 * @param integer $col1 Start column
5721 * @param integer $row2 End row
5722 * @param integer $col2 End column
5723 * @param string $url URL string
5724 * @param string $str Alternative label
5725 * @param mixed $format The cell format
5728 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5730 $record = 0x01B8; // Record identifier
5731 $length = 0x00000; // Bytes to follow
5734 $format = $this->_url_format;
5738 $url = preg_replace('/^internal:/', '', $url);
5740 // Write the visible label
5744 $str_error = $this->writeString($row1, $col1, $str, $format);
5745 if (($str_error == -2) || ($str_error == -3)) {
5749 // Pack the undocumented parts of the hyperlink stream
5750 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5752 // Pack the option flags
5753 $options = pack("V", 0x08);
5755 // Convert the URL type and to a null terminated wchar string
5756 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5757 $url = $url . "\0\0\0";
5759 // Pack the length of the URL as chars (not wchars)
5760 $url_len = pack("V", floor(strlen($url)/2));
5762 // Calculate the data length
5763 $length = 0x24 + strlen($url);
5765 // Pack the header data
5766 $header = pack("vv", $record, $length);
5767 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5769 // Write the packed data
5770 $this->_append($header . $data .
5771 $unknown1 . $options .
5777 * Write links to external directory names such as 'c:\foo.xls',
5778 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
5780 * Note: Excel writes some relative links with the $dir_long string. We ignore
5781 * these cases for the sake of simpler code.
5785 * @param integer $row1 Start row
5786 * @param integer $col1 Start column
5787 * @param integer $row2 End row
5788 * @param integer $col2 End column
5789 * @param string $url URL string
5790 * @param string $str Alternative label
5791 * @param mixed $format The cell format
5794 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5796 // Network drives are different. We will handle them separately
5797 // MS/Novell network drives and shares start with \\
5798 if (preg_match('[^external:\\\\]', $url)) {
5799 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
5802 $record = 0x01B8; // Record identifier
5803 $length = 0x00000; // Bytes to follow
5806 $format = $this->_url_format;
5809 // Strip URL type and change Unix dir separator to Dos style (if needed)
5811 $url = preg_replace('/^external:/', '', $url);
5812 $url = preg_replace('/\//', "\\", $url);
5814 // Write the visible label
5816 $str = preg_replace('/\#/', ' - ', $url);
5818 $str_error = $this->writeString($row1, $col1, $str, $format);
5819 if (($str_error == -2) or ($str_error == -3)) {
5823 // Determine if the link is relative or absolute:
5824 // relative if link contains no dir separator, "somefile.xls"
5825 // relative if link starts with up-dir, "..\..\somefile.xls"
5826 // otherwise, absolute
5828 $absolute = 0x02; // Bit mask
5829 if (!preg_match("/\\\/", $url)) {
5832 if (preg_match("/^\.\.\\\/", $url)) {
5835 $link_type = 0x01 | $absolute;
5837 // Determine if the link contains a sheet reference and change some of the
5838 // parameters accordingly.
5839 // Split the dir name and sheet name (if it exists)
5840 /*if (preg_match("/\#/", $url)) {
5841 list($dir_long, $sheet) = preg_split("/\#/", $url);
5846 if (isset($sheet)) {
5848 $sheet_len = pack("V", strlen($sheet) + 0x01);
5849 $sheet = join("\0", preg_split('//', $sheet));
5856 if (preg_match("/\#/", $url)) {
5862 // Pack the link type
5863 $link_type = pack("V", $link_type);
5865 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
5866 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
5867 $up_count = pack("v", $up_count);
5869 // Store the short dos dir name (null terminated)
5870 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
5872 // Store the long dir name as a wchar string (non-null terminated)
5873 //$dir_long = join("\0", preg_split('//', $dir_long));
5874 $dir_long = $dir_long . "\0";
5876 // Pack the lengths of the dir strings
5877 $dir_short_len = pack("V", strlen($dir_short) );
5878 $dir_long_len = pack("V", strlen($dir_long) );
5879 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
5881 // Pack the undocumented parts of the hyperlink stream
5882 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
5883 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
5884 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
5885 $unknown4 = pack("v", 0x03 );
5887 // Pack the main data stream
5888 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
5903 // Pack the header data
5904 $length = strlen($data);
5905 $header = pack("vv", $record, $length);
5907 // Write the packed data
5908 $this->_append($header. $data);
5914 * This method is used to set the height and format for a row.
5917 * @param integer $row The row to set
5918 * @param integer $height Height we are giving to the row.
5919 * Use null to set XF without setting height
5920 * @param mixed $format XF format we are giving to the row
5921 * @param bool $hidden The optional hidden attribute
5922 * @param integer $level The optional outline level for row, in range [0,7]
5924 function setRow($row, $height, $format = null, $hidden = false, $level = 0)
5926 $record = 0x0208; // Record identifier
5927 $length = 0x0010; // Number of bytes to follow
5929 $colMic = 0x0000; // First defined column
5930 $colMac = 0x0000; // Last defined column
5931 $irwMac = 0x0000; // Used by Excel to optimise loading
5932 $reserved = 0x0000; // Reserved
5933 $grbit = 0x0000; // Option flags
5934 $ixfe = $this->_XF($format); // XF index
5936 // set _row_sizes so _sizeRow() can use it
5937 $this->_row_sizes[$row] = $height;
5939 // Use setRow($row, null, $XF) to set XF format without setting height
5940 if ($height != null) {
5941 $miyRw = $height * 20; // row height
5943 $miyRw = 0xff; // default row height is 256
5946 $level = max(0, min($level, 7)); // level should be between 0 and 7
5947 $this->_outline_row_level = max($level, $this->_outline_row_level);
5950 // Set the options flags. fUnsynced is used to show that the font and row
5951 // heights are not compatible. This is usually the case for WriteExcel.
5952 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
5953 // is collapsed. Instead it is used to indicate that the previous row is
5954 // collapsed. The zero height flag, 0x20, is used to collapse a row.
5960 $grbit |= 0x0040; // fUnsynced
5966 $header = pack("vv", $record, $length);
5967 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
5968 $irwMac,$reserved, $grbit, $ixfe);
5969 $this->_append($header.$data);
5973 * Writes Excel DIMENSIONS to define the area in which there is data.
5977 function _storeDimensions()
5979 $record = 0x0200; // Record identifier
5980 $row_min = $this->_dim_rowmin; // First row
5981 $row_max = $this->_dim_rowmax + 1; // Last row plus 1
5982 $col_min = $this->_dim_colmin; // First column
5983 $col_max = $this->_dim_colmax + 1; // Last column plus 1
5984 $reserved = 0x0000; // Reserved by Excel
5986 if ($this->_BIFF_version == 0x0500) {
5987 $length = 0x000A; // Number of bytes to follow
5988 $data = pack("vvvvv", $row_min, $row_max,
5989 $col_min, $col_max, $reserved);
5990 } elseif ($this->_BIFF_version == 0x0600) {
5992 $data = pack("VVvvv", $row_min, $row_max,
5993 $col_min, $col_max, $reserved);
5995 $header = pack("vv", $record, $length);
5996 $this->_prepend($header.$data);
6000 * Write BIFF record Window2.
6004 function _storeWindow2()
6006 $record = 0x023E; // Record identifier
6007 if ($this->_BIFF_version == 0x0500) {
6008 $length = 0x000A; // Number of bytes to follow
6009 } elseif ($this->_BIFF_version == 0x0600) {
6013 $grbit = 0x00B6; // Option flags
6014 $rwTop = 0x0000; // Top row visible in window
6015 $colLeft = 0x0000; // Leftmost column visible in window
6018 // The options flags that comprise $grbit
6019 $fDspFmla = 0; // 0 - bit
6020 $fDspGrid = $this->_screen_gridlines; // 1
6021 $fDspRwCol = 1; // 2
6022 $fFrozen = $this->_frozen; // 3
6023 $fDspZeros = 1; // 4
6024 $fDefaultHdr = 1; // 5
6025 $fArabic = $this->_rtl; // 6
6026 $fDspGuts = $this->_outline_on; // 7
6027 $fFrozenNoSplit = 0; // 0 - bit
6028 $fSelected = $this->selected; // 1
6032 $grbit |= $fDspGrid << 1;
6033 $grbit |= $fDspRwCol << 2;
6034 $grbit |= $fFrozen << 3;
6035 $grbit |= $fDspZeros << 4;
6036 $grbit |= $fDefaultHdr << 5;
6037 $grbit |= $fArabic << 6;
6038 $grbit |= $fDspGuts << 7;
6039 $grbit |= $fFrozenNoSplit << 8;
6040 $grbit |= $fSelected << 9;
6041 $grbit |= $fPaged << 10;
6043 $header = pack("vv", $record, $length);
6044 $data = pack("vvv", $grbit, $rwTop, $colLeft);
6046 if ($this->_BIFF_version == 0x0500) {
6047 $rgbHdr = 0x00000000; // Row/column heading and gridline color
6048 $data .= pack("V", $rgbHdr);
6049 } elseif ($this->_BIFF_version == 0x0600) {
6050 $rgbHdr = 0x0040; // Row/column heading and gridline color index
6051 $zoom_factor_page_break = 0x0000;
6052 $zoom_factor_normal = 0x0000;
6053 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
6055 $this->_append($header.$data);
6059 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
6063 function _storeDefcol()
6065 $record = 0x0055; // Record identifier
6066 $length = 0x0002; // Number of bytes to follow
6067 $colwidth = 0x0008; // Default column width
6069 $header = pack("vv", $record, $length);
6070 $data = pack("v", $colwidth);
6071 $this->_prepend($header . $data);
6075 * Write BIFF record COLINFO to define column widths
6077 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
6081 * @param array $col_array This is the only parameter received and is composed of the following:
6082 * 0 => First formatted column,
6083 * 1 => Last formatted column,
6084 * 2 => Col width (8.43 is Excel default),
6085 * 3 => The optional XF format of the column,
6086 * 4 => Option flags.
6087 * 5 => Optional outline level
6089 function _storeColinfo($col_array)
6091 if (isset($col_array[0])) {
6092 $colFirst = $col_array[0];
6094 if (isset($col_array[1])) {
6095 $colLast = $col_array[1];
6097 if (isset($col_array[2])) {
6098 $coldx = $col_array[2];
6102 if (isset($col_array[3])) {
6103 $format = $col_array[3];
6107 if (isset($col_array[4])) {
6108 $grbit = $col_array[4];
6112 if (isset($col_array[5])) {
6113 $level = $col_array[5];
6117 $record = 0x007D; // Record identifier
6118 $length = 0x000B; // Number of bytes to follow
6120 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
6121 $coldx *= 256; // Convert to units of 1/256 of a char
6123 $ixfe = $this->_XF($format);
6124 $reserved = 0x00; // Reserved
6126 $level = max(0, min($level, 7));
6127 $grbit |= $level << 8;
6129 $header = pack("vv", $record, $length);
6130 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
6131 $ixfe, $grbit, $reserved);
6132 $this->_prepend($header.$data);
6136 * Write BIFF record SELECTION.
6139 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
6140 * @see setSelection()
6142 function _storeSelection($array)
6144 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
6145 $record = 0x001D; // Record identifier
6146 $length = 0x000F; // Number of bytes to follow
6148 $pnn = $this->_active_pane; // Pane position
6149 $rwAct = $rwFirst; // Active row
6150 $colAct = $colFirst; // Active column
6151 $irefAct = 0; // Active cell ref
6152 $cref = 1; // Number of refs
6154 if (!isset($rwLast)) {
6155 $rwLast = $rwFirst; // Last row in reference
6157 if (!isset($colLast)) {
6158 $colLast = $colFirst; // Last col in reference
6161 // Swap last row/col for first row/col as necessary
6162 if ($rwFirst > $rwLast) {
6163 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
6166 if ($colFirst > $colLast) {
6167 list($colFirst, $colLast) = array($colLast, $colFirst);
6170 $header = pack("vv", $record, $length);
6171 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
6174 $colFirst, $colLast);
6175 $this->_append($header . $data);
6179 * Store the MERGEDCELLS record for all ranges of merged cells
6183 function _storeMergedCells()
6185 // if there are no merged cell ranges set, return
6186 if (count($this->_merged_ranges) == 0) {
6190 $length = 2 + count($this->_merged_ranges) * 8;
6192 $header = pack('vv', $record, $length);
6193 $data = pack('v', count($this->_merged_ranges));
6194 foreach ($this->_merged_ranges as $range) {
6195 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
6197 $this->_append($header . $data);
6201 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
6202 * references in a worksheet.
6204 * Excel only stores references to external sheets that are used in formulas.
6205 * For simplicity we store references to all the sheets in the workbook
6206 * regardless of whether they are used or not. This reduces the overall
6207 * complexity and eliminates the need for a two way dialogue between the formula
6208 * parser the worksheet objects.
6211 * @param integer $count The number of external sheet references in this worksheet
6213 function _storeExterncount($count)
6215 $record = 0x0016; // Record identifier
6216 $length = 0x0002; // Number of bytes to follow
6218 $header = pack("vv", $record, $length);
6219 $data = pack("v", $count);
6220 $this->_prepend($header . $data);
6224 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
6225 * formulas. A formula references a sheet name via an index. Since we store a
6226 * reference to all of the external worksheets the EXTERNSHEET index is the same
6227 * as the worksheet index.
6230 * @param string $sheetname The name of a external worksheet
6232 function _storeExternsheet($sheetname)
6234 $record = 0x0017; // Record identifier
6236 // References to the current sheet are encoded differently to references to
6239 if ($this->name == $sheetname) {
6241 $length = 0x02; // The following 2 bytes
6242 $cch = 1; // The following byte
6243 $rgch = 0x02; // Self reference
6245 $length = 0x02 + strlen($sheetname);
6246 $cch = strlen($sheetname);
6247 $rgch = 0x03; // Reference to a sheet in the current workbook
6250 $header = pack("vv", $record, $length);
6251 $data = pack("CC", $cch, $rgch);
6252 $this->_prepend($header . $data . $sheetname);
6256 * Writes the Excel BIFF PANE record.
6257 * The panes can either be frozen or thawed (unfrozen).
6258 * Frozen panes are specified in terms of an integer number of rows and columns.
6259 * Thawed panes are specified in terms of Excel's units for rows and columns.
6262 * @param array $panes This is the only parameter received and is composed of the following:
6263 * 0 => Vertical split position,
6264 * 1 => Horizontal split position
6265 * 2 => Top row visible
6266 * 3 => Leftmost column visible
6269 function _storePanes($panes)
6274 $colLeft = $panes[3];
6275 if (count($panes) > 4) { // if Active pane was received
6276 $pnnAct = $panes[4];
6280 $record = 0x0041; // Record identifier
6281 $length = 0x000A; // Number of bytes to follow
6283 // Code specific to frozen or thawed panes.
6284 if ($this->_frozen) {
6285 // Set default values for $rwTop and $colLeft
6286 if (!isset($rwTop)) {
6289 if (!isset($colLeft)) {
6293 // Set default values for $rwTop and $colLeft
6294 if (!isset($rwTop)) {
6297 if (!isset($colLeft)) {
6301 // Convert Excel's row and column units to the internal units.
6302 // The default row height is 12.75
6303 // The default column width is 8.43
6304 // The following slope and intersection values were interpolated.
6307 $x = 113.879*$x + 390;
6311 // Determine which pane should be active. There is also the undocumented
6312 // option to override this should it be necessary: may be removed later.
6314 if (!isset($pnnAct)) {
6315 if ($x != 0 && $y != 0) {
6316 $pnnAct = 0; // Bottom right
6318 if ($x != 0 && $y == 0) {
6319 $pnnAct = 1; // Top right
6321 if ($x == 0 && $y != 0) {
6322 $pnnAct = 2; // Bottom left
6324 if ($x == 0 && $y == 0) {
6325 $pnnAct = 3; // Top left
6329 $this->_active_pane = $pnnAct; // Used in _storeSelection
6331 $header = pack("vv", $record, $length);
6332 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
6333 $this->_append($header . $data);
6337 * Store the page setup SETUP BIFF record.
6341 function _storeSetup()
6343 $record = 0x00A1; // Record identifier
6344 $length = 0x0022; // Number of bytes to follow
6346 $iPaperSize = $this->_paper_size; // Paper size
6347 $iScale = $this->_print_scale; // Print scaling factor
6348 $iPageStart = 0x01; // Starting page number
6349 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
6350 $iFitHeight = $this->_fit_height; // Fit to number of pages high
6351 $grbit = 0x00; // Option flags
6352 $iRes = 0x0258; // Print resolution
6353 $iVRes = 0x0258; // Vertical print resolution
6354 $numHdr = $this->_margin_head; // Header Margin
6355 $numFtr = $this->_margin_foot; // Footer Margin
6356 $iCopies = 0x01; // Number of copies
6358 $fLeftToRight = 0x0; // Print over then down
6359 $fLandscape = $this->_orientation; // Page orientation
6360 $fNoPls = 0x0; // Setup not read from printer
6361 $fNoColor = 0x0; // Print black and white
6362 $fDraft = 0x0; // Print draft quality
6363 $fNotes = 0x0; // Print notes
6364 $fNoOrient = 0x0; // Orientation not set
6365 $fUsePage = 0x0; // Use custom starting page
6367 $grbit = $fLeftToRight;
6368 $grbit |= $fLandscape << 1;
6369 $grbit |= $fNoPls << 2;
6370 $grbit |= $fNoColor << 3;
6371 $grbit |= $fDraft << 4;
6372 $grbit |= $fNotes << 5;
6373 $grbit |= $fNoOrient << 6;
6374 $grbit |= $fUsePage << 7;
6376 $numHdr = pack("d", $numHdr);
6377 $numFtr = pack("d", $numFtr);
6378 if ($this->_byte_order) { // if it's Big Endian
6379 $numHdr = strrev($numHdr);
6380 $numFtr = strrev($numFtr);
6383 $header = pack("vv", $record, $length);
6384 $data1 = pack("vvvvvvvv", $iPaperSize,
6392 $data2 = $numHdr.$numFtr;
6393 $data3 = pack("v", $iCopies);
6394 $this->_prepend($header . $data1 . $data2 . $data3);
6398 * Store the header caption BIFF record.
6402 function _storeHeader()
6404 $record = 0x0014; // Record identifier
6406 $str = $this->_header; // header string
6407 $cch = strlen($str); // Length of header string
6408 if ($this->_BIFF_version == 0x0600) {
6409 $encoding = 0x0; // TODO: Unicode support
6410 $length = 3 + $cch; // Bytes to follow
6412 $length = 1 + $cch; // Bytes to follow
6415 $header = pack("vv", $record, $length);
6416 if ($this->_BIFF_version == 0x0600) {
6417 $data = pack("vC", $cch, $encoding);
6419 $data = pack("C", $cch);
6422 $this->_prepend($header.$data.$str);
6426 * Store the footer caption BIFF record.
6430 function _storeFooter()
6432 $record = 0x0015; // Record identifier
6434 $str = $this->_footer; // Footer string
6435 $cch = strlen($str); // Length of footer string
6436 if ($this->_BIFF_version == 0x0600) {
6437 $encoding = 0x0; // TODO: Unicode support
6438 $length = 3 + $cch; // Bytes to follow
6443 $header = pack("vv", $record, $length);
6444 if ($this->_BIFF_version == 0x0600) {
6445 $data = pack("vC", $cch, $encoding);
6447 $data = pack("C", $cch);
6450 $this->_prepend($header . $data . $str);
6454 * Store the horizontal centering HCENTER BIFF record.
6458 function _storeHcenter()
6460 $record = 0x0083; // Record identifier
6461 $length = 0x0002; // Bytes to follow
6463 $fHCenter = $this->_hcenter; // Horizontal centering
6465 $header = pack("vv", $record, $length);
6466 $data = pack("v", $fHCenter);
6468 $this->_prepend($header.$data);
6472 * Store the vertical centering VCENTER BIFF record.
6476 function _storeVcenter()
6478 $record = 0x0084; // Record identifier
6479 $length = 0x0002; // Bytes to follow
6481 $fVCenter = $this->_vcenter; // Horizontal centering
6483 $header = pack("vv", $record, $length);
6484 $data = pack("v", $fVCenter);
6485 $this->_prepend($header . $data);
6489 * Store the LEFTMARGIN BIFF record.
6493 function _storeMarginLeft()
6495 $record = 0x0026; // Record identifier
6496 $length = 0x0008; // Bytes to follow
6498 $margin = $this->_margin_left; // Margin in inches
6500 $header = pack("vv", $record, $length);
6501 $data = pack("d", $margin);
6502 if ($this->_byte_order) { // if it's Big Endian
6503 $data = strrev($data);
6506 $this->_prepend($header . $data);
6510 * Store the RIGHTMARGIN BIFF record.
6514 function _storeMarginRight()
6516 $record = 0x0027; // Record identifier
6517 $length = 0x0008; // Bytes to follow
6519 $margin = $this->_margin_right; // Margin in inches
6521 $header = pack("vv", $record, $length);
6522 $data = pack("d", $margin);
6523 if ($this->_byte_order) { // if it's Big Endian
6524 $data = strrev($data);
6527 $this->_prepend($header . $data);
6531 * Store the TOPMARGIN BIFF record.
6535 function _storeMarginTop()
6537 $record = 0x0028; // Record identifier
6538 $length = 0x0008; // Bytes to follow
6540 $margin = $this->_margin_top; // Margin in inches
6542 $header = pack("vv", $record, $length);
6543 $data = pack("d", $margin);
6544 if ($this->_byte_order) { // if it's Big Endian
6545 $data = strrev($data);
6548 $this->_prepend($header . $data);
6552 * Store the BOTTOMMARGIN BIFF record.
6556 function _storeMarginBottom()
6558 $record = 0x0029; // Record identifier
6559 $length = 0x0008; // Bytes to follow
6561 $margin = $this->_margin_bottom; // Margin in inches
6563 $header = pack("vv", $record, $length);
6564 $data = pack("d", $margin);
6565 if ($this->_byte_order) { // if it's Big Endian
6566 $data = strrev($data);
6569 $this->_prepend($header . $data);
6573 * Merges the area given by its arguments.
6574 * This is an Excel97/2000 method. It is required to perform more complicated
6575 * merging than the normal setAlign('merge').
6578 * @param integer $first_row First row of the area to merge
6579 * @param integer $first_col First column of the area to merge
6580 * @param integer $last_row Last row of the area to merge
6581 * @param integer $last_col Last column of the area to merge
6583 function mergeCells($first_row, $first_col, $last_row, $last_col)
6585 $record = 0x00E5; // Record identifier
6586 $length = 0x000A; // Bytes to follow
6587 $cref = 1; // Number of refs
6589 // Swap last row/col for first row/col as necessary
6590 if ($first_row > $last_row) {
6591 list($first_row, $last_row) = array($last_row, $first_row);
6594 if ($first_col > $last_col) {
6595 list($first_col, $last_col) = array($last_col, $first_col);
6598 $header = pack("vv", $record, $length);
6599 $data = pack("vvvvv", $cref, $first_row, $last_row,
6600 $first_col, $last_col);
6602 $this->_append($header.$data);
6606 * Write the PRINTHEADERS BIFF record.
6610 function _storePrintHeaders()
6612 $record = 0x002a; // Record identifier
6613 $length = 0x0002; // Bytes to follow
6615 $fPrintRwCol = $this->_print_headers; // Boolean flag
6617 $header = pack("vv", $record, $length);
6618 $data = pack("v", $fPrintRwCol);
6619 $this->_prepend($header . $data);
6623 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
6628 function _storePrintGridlines()
6630 $record = 0x002b; // Record identifier
6631 $length = 0x0002; // Bytes to follow
6633 $fPrintGrid = $this->_print_gridlines; // Boolean flag
6635 $header = pack("vv", $record, $length);
6636 $data = pack("v", $fPrintGrid);
6637 $this->_prepend($header . $data);
6641 * Write the GRIDSET BIFF record. Must be used in conjunction with the
6642 * PRINTGRIDLINES record.
6646 function _storeGridset()
6648 $record = 0x0082; // Record identifier
6649 $length = 0x0002; // Bytes to follow
6651 $fGridSet = !($this->_print_gridlines); // Boolean flag
6653 $header = pack("vv", $record, $length);
6654 $data = pack("v", $fGridSet);
6655 $this->_prepend($header . $data);
6659 * Write the GUTS BIFF record. This is used to configure the gutter margins
6660 * where Excel outline symbols are displayed. The visibility of the gutters is
6661 * controlled by a flag in WSBOOL.
6663 * @see _storeWsbool()
6666 function _storeGuts()
6668 $record = 0x0080; // Record identifier
6669 $length = 0x0008; // Bytes to follow
6671 $dxRwGut = 0x0000; // Size of row gutter
6672 $dxColGut = 0x0000; // Size of col gutter
6674 $row_level = $this->_outline_row_level;
6677 // Calculate the maximum column outline level. The equivalent calculation
6678 // for the row outline level is carried out in setRow().
6679 $colcount = count($this->_colinfo);
6680 for ($i = 0; $i < $colcount; $i++) {
6681 // Skip cols without outline level info.
6682 if (count_array($col_level) >= 6) {
6683 $col_level = max($this->_colinfo[$i][5], $col_level);
6687 // Set the limits for the outline levels (0 <= x <= 7).
6688 $col_level = max(0, min($col_level, 7));
6690 // The displayed level is one greater than the max outline levels
6698 $header = pack("vv", $record, $length);
6699 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
6701 $this->_prepend($header.$data);
6706 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
6707 * with the SETUP record.
6711 function _storeWsbool()
6713 $record = 0x0081; // Record identifier
6714 $length = 0x0002; // Bytes to follow
6717 // The only option that is of interest is the flag for fit to page. So we
6718 // set all the options in one go.
6720 /*if ($this->_fit_page) {
6725 // Set the option flags
6726 $grbit |= 0x0001; // Auto page breaks visible
6727 if ($this->_outline_style) {
6728 $grbit |= 0x0020; // Auto outline styles
6730 if ($this->_outline_below) {
6731 $grbit |= 0x0040; // Outline summary below
6733 if ($this->_outline_right) {
6734 $grbit |= 0x0080; // Outline summary right
6736 if ($this->_fit_page) {
6737 $grbit |= 0x0100; // Page setup fit to page
6739 if ($this->_outline_on) {
6740 $grbit |= 0x0400; // Outline symbols displayed
6743 $header = pack("vv", $record, $length);
6744 $data = pack("v", $grbit);
6745 $this->_prepend($header . $data);
6749 * Write the HORIZONTALPAGEBREAKS BIFF record.
6753 function _storeHbreak()
6755 // Return if the user hasn't specified pagebreaks
6756 if (empty($this->_hbreaks)) {
6760 // Sort and filter array of page breaks
6761 $breaks = $this->_hbreaks;
6762 sort($breaks, SORT_NUMERIC);
6763 if ($breaks[0] == 0) { // don't use first break if it's 0
6764 array_shift($breaks);
6767 $record = 0x001b; // Record identifier
6768 $cbrk = count($breaks); // Number of page breaks
6769 if ($this->_BIFF_version == 0x0600) {
6770 $length = 2 + 6*$cbrk; // Bytes to follow
6772 $length = 2 + 2*$cbrk; // Bytes to follow
6775 $header = pack("vv", $record, $length);
6776 $data = pack("v", $cbrk);
6778 // Append each page break
6779 foreach ($breaks as $break) {
6780 if ($this->_BIFF_version == 0x0600) {
6781 $data .= pack("vvv", $break, 0x0000, 0x00ff);
6783 $data .= pack("v", $break);
6787 $this->_prepend($header.$data);
6792 * Write the VERTICALPAGEBREAKS BIFF record.
6796 function _storeVbreak()
6798 // Return if the user hasn't specified pagebreaks
6799 if (empty($this->_vbreaks)) {
6803 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
6804 // It is slightly higher in Excel 97/200, approx. 1026
6805 $breaks = array_slice($this->_vbreaks,0,1000);
6807 // Sort and filter array of page breaks
6808 sort($breaks, SORT_NUMERIC);
6809 if ($breaks[0] == 0) { // don't use first break if it's 0
6810 array_shift($breaks);
6813 $record = 0x001a; // Record identifier
6814 $cbrk = count($breaks); // Number of page breaks
6815 if ($this->_BIFF_version == 0x0600) {
6816 $length = 2 + 6*$cbrk; // Bytes to follow
6818 $length = 2 + 2*$cbrk; // Bytes to follow
6821 $header = pack("vv", $record, $length);
6822 $data = pack("v", $cbrk);
6824 // Append each page break
6825 foreach ($breaks as $break) {
6826 if ($this->_BIFF_version == 0x0600) {
6827 $data .= pack("vvv", $break, 0x0000, 0xffff);
6829 $data .= pack("v", $break);
6833 $this->_prepend($header . $data);
6837 * Set the Biff PROTECT record to indicate that the worksheet is protected.
6841 function _storeProtect()
6843 // Exit unless sheet protection has been specified
6844 if ($this->_protect == 0) {
6848 $record = 0x0012; // Record identifier
6849 $length = 0x0002; // Bytes to follow
6851 $fLock = $this->_protect; // Worksheet is protected
6853 $header = pack("vv", $record, $length);
6854 $data = pack("v", $fLock);
6856 $this->_prepend($header.$data);
6860 * Write the worksheet PASSWORD record.
6864 function _storePassword()
6866 // Exit unless sheet protection and password have been specified
6867 if (($this->_protect == 0) || (!isset($this->_password))) {
6871 $record = 0x0013; // Record identifier
6872 $length = 0x0002; // Bytes to follow
6874 $wPassword = $this->_password; // Encoded password
6876 $header = pack("vv", $record, $length);
6877 $data = pack("v", $wPassword);
6879 $this->_prepend($header . $data);
6884 * Insert a 24bit bitmap image in a worksheet.
6887 * @param integer $row The row we are going to insert the bitmap into
6888 * @param integer $col The column we are going to insert the bitmap into
6889 * @param string $bitmap The bitmap filename
6890 * @param integer $x The horizontal position (offset) of the image inside the cell.
6891 * @param integer $y The vertical position (offset) of the image inside the cell.
6892 * @param integer $scale_x The horizontal scale
6893 * @param integer $scale_y The vertical scale
6895 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
6897 $bitmap_array = $this->_processBitmap($bitmap);
6898 if ($this->isError($bitmap_array)) {
6899 $this->writeString($row, $col, $bitmap_array->getMessage());
6902 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
6904 // Scale the frame of the image.
6906 $height *= $scale_y;
6908 // Calculate the vertices of the image and write the OBJ record
6909 $this->_positionImage($col, $row, $x, $y, $width, $height);
6911 // Write the IMDATA record to store the bitmap data
6913 $length = 8 + $size;
6918 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
6919 $this->_append($header.$data);
6923 * Calculate the vertices that define the position of the image as required by
6926 * +------------+------------+
6928 * +-----+------------+------------+
6930 * | 1 |(A1)._______|______ |
6933 * +-----+----| BITMAP |-----+
6935 * | 2 | |______________. |
6938 * +---- +------------+------------+
6940 * Example of a bitmap that covers some of the area from cell A1 to cell B2.
6942 * Based on the width and height of the bitmap we need to calculate 8 vars:
6943 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
6944 * The width and height of the cells are also variable and have to be taken into
6946 * The values of $col_start and $row_start are passed in from the calling
6947 * function. The values of $col_end and $row_end are calculated by subtracting
6948 * the width and height of the bitmap from the width and height of the
6950 * The vertices are expressed as a percentage of the underlying cell width as
6951 * follows (rhs values are in pixels):
6955 * x2 = (X-1) / W *1024
6956 * y2 = (Y-1) / H *256
6958 * Where: X is distance from the left side of the underlying cell
6959 * Y is distance from the top of the underlying cell
6960 * W is the width of the cell
6961 * H is the height of the cell
6964 * @note the SDK incorrectly states that the height should be expressed as a
6965 * percentage of 1024.
6966 * @param integer $col_start Col containing upper left corner of object
6967 * @param integer $row_start Row containing top left corner of object
6968 * @param integer $x1 Distance to left side of object
6969 * @param integer $y1 Distance to top of object
6970 * @param integer $width Width of image frame
6971 * @param integer $height Height of image frame
6973 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
6975 // Initialise end cell to the same as the start cell
6976 $col_end = $col_start; // Col containing lower right corner of object
6977 $row_end = $row_start; // Row containing bottom right corner of object
6979 // Zero the specified offset if greater than the cell dimensions
6980 if ($x1 >= $this->_sizeCol($col_start)) {
6983 if ($y1 >= $this->_sizeRow($row_start)) {
6987 $width = $width + $x1 -1;
6988 $height = $height + $y1 -1;
6990 // Subtract the underlying cell widths to find the end cell of the image
6991 while ($width >= $this->_sizeCol($col_end)) {
6992 $width -= $this->_sizeCol($col_end);
6996 // Subtract the underlying cell heights to find the end cell of the image
6997 while ($height >= $this->_sizeRow($row_end)) {
6998 $height -= $this->_sizeRow($row_end);
7002 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
7003 // with zero eight or width.
7005 if ($this->_sizeCol($col_start) == 0) {
7008 if ($this->_sizeCol($col_end) == 0) {
7011 if ($this->_sizeRow($row_start) == 0) {
7014 if ($this->_sizeRow($row_end) == 0) {
7018 // Convert the pixel values to the percentage value expected by Excel
7019 $x1 = $x1 / $this->_sizeCol($col_start) * 1024;
7020 $y1 = $y1 / $this->_sizeRow($row_start) * 256;
7021 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object
7022 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object
7024 $this->_storeObjPicture($col_start, $x1,
7031 * Convert the width of a cell from user's units to pixels. By interpolation
7032 * the relationship is: y = 7x +5. If the width hasn't been set by the user we
7033 * use the default value. If the col is hidden we use a value of zero.
7036 * @param integer $col The column
7037 * @return integer The width in pixels
7039 function _sizeCol($col)
7041 // Look up the cell value to see if it has been changed
7042 if (isset($this->col_sizes[$col])) {
7043 if ($this->col_sizes[$col] == 0) {
7046 return(floor(7 * $this->col_sizes[$col] + 5));
7054 * Convert the height of a cell from user's units to pixels. By interpolation
7055 * the relationship is: y = 4/3x. If the height hasn't been set by the user we
7056 * use the default value. If the row is hidden we use a value of zero. (Not
7057 * possible to hide row yet).
7060 * @param integer $row The row
7061 * @return integer The width in pixels
7063 function _sizeRow($row)
7065 // Look up the cell value to see if it has been changed
7066 if (isset($this->_row_sizes[$row])) {
7067 if ($this->_row_sizes[$row] == 0) {
7070 return(floor(4/3 * $this->_row_sizes[$row]));
7078 * Store the OBJ record that precedes an IMDATA record. This could be generalise
7079 * to support other Excel objects.
7082 * @param integer $colL Column containing upper left corner of object
7083 * @param integer $dxL Distance from left side of cell
7084 * @param integer $rwT Row containing top left corner of object
7085 * @param integer $dyT Distance from top of cell
7086 * @param integer $colR Column containing lower right corner of object
7087 * @param integer $dxR Distance from right of cell
7088 * @param integer $rwB Row containing bottom right corner of object
7089 * @param integer $dyB Distance from bottom of cell
7091 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
7093 $record = 0x005d; // Record identifier
7094 $length = 0x003c; // Bytes to follow
7096 $cObj = 0x0001; // Count of objects in file (set to 1)
7097 $OT = 0x0008; // Object type. 8 = Picture
7098 $id = 0x0001; // Object ID
7099 $grbit = 0x0614; // Option flags
7101 $cbMacro = 0x0000; // Length of FMLA structure
7102 $Reserved1 = 0x0000; // Reserved
7103 $Reserved2 = 0x0000; // Reserved
7105 $icvBack = 0x09; // Background colour
7106 $icvFore = 0x09; // Foreground colour
7107 $fls = 0x00; // Fill pattern
7108 $fAuto = 0x00; // Automatic fill
7109 $icv = 0x08; // Line colour
7110 $lns = 0xff; // Line style
7111 $lnw = 0x01; // Line weight
7112 $fAutoB = 0x00; // Automatic border
7113 $frs = 0x0000; // Frame style
7114 $cf = 0x0009; // Image format, 9 = bitmap
7115 $Reserved3 = 0x0000; // Reserved
7116 $cbPictFmla = 0x0000; // Length of FMLA structure
7117 $Reserved4 = 0x0000; // Reserved
7118 $grbit2 = 0x0001; // Option flags
7119 $Reserved5 = 0x0000; // Reserved
7122 $header = pack("vv", $record, $length);
7123 $data = pack("V", $cObj);
7124 $data .= pack("v", $OT);
7125 $data .= pack("v", $id);
7126 $data .= pack("v", $grbit);
7127 $data .= pack("v", $colL);
7128 $data .= pack("v", $dxL);
7129 $data .= pack("v", $rwT);
7130 $data .= pack("v", $dyT);
7131 $data .= pack("v", $colR);
7132 $data .= pack("v", $dxR);
7133 $data .= pack("v", $rwB);
7134 $data .= pack("v", $dyB);
7135 $data .= pack("v", $cbMacro);
7136 $data .= pack("V", $Reserved1);
7137 $data .= pack("v", $Reserved2);
7138 $data .= pack("C", $icvBack);
7139 $data .= pack("C", $icvFore);
7140 $data .= pack("C", $fls);
7141 $data .= pack("C", $fAuto);
7142 $data .= pack("C", $icv);
7143 $data .= pack("C", $lns);
7144 $data .= pack("C", $lnw);
7145 $data .= pack("C", $fAutoB);
7146 $data .= pack("v", $frs);
7147 $data .= pack("V", $cf);
7148 $data .= pack("v", $Reserved3);
7149 $data .= pack("v", $cbPictFmla);
7150 $data .= pack("v", $Reserved4);
7151 $data .= pack("v", $grbit2);
7152 $data .= pack("V", $Reserved5);
7154 $this->_append($header . $data);
7158 * Convert a 24 bit bitmap into the modified internal format used by Windows.
7159 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
7163 * @param string $bitmap The bitmap to process
7164 * @return array Array with data and properties of the bitmap
7166 function _processBitmap($bitmap)
7169 $bmp_fd = @fopen($bitmap,"rb");
7171 die("Couldn't import $bitmap");
7174 // Slurp the file into a string.
7175 $data = fread($bmp_fd, filesize($bitmap));
7177 // Check that the file is big enough to be a bitmap.
7178 if (strlen($data) <= 0x36) {
7179 die("$bitmap doesn't contain enough data.\n");
7182 // The first 2 bytes are used to identify the bitmap.
7183 $identity = unpack("A2ident", $data);
7184 if ($identity['ident'] != "BM") {
7185 die("$bitmap doesn't appear to be a valid bitmap image.\n");
7188 // Remove bitmap data: ID.
7189 $data = substr($data, 2);
7191 // Read and remove the bitmap size. This is more reliable than reading
7192 // the data size at offset 0x22.
7194 $size_array = unpack("Vsa", substr($data, 0, 4));
7195 $size = $size_array['sa'];
7196 $data = substr($data, 4);
7197 $size -= 0x36; // Subtract size of bitmap header.
7198 $size += 0x0C; // Add size of BIFF header.
7200 // Remove bitmap data: reserved, offset, header length.
7201 $data = substr($data, 12);
7203 // Read and remove the bitmap width and height. Verify the sizes.
7204 $width_and_height = unpack("V2", substr($data, 0, 8));
7205 $width = $width_and_height[1];
7206 $height = $width_and_height[2];
7207 $data = substr($data, 8);
7208 if ($width > 0xFFFF) {
7209 die("$bitmap: largest image width supported is 65k.\n");
7211 if ($height > 0xFFFF) {
7212 die("$bitmap: largest image height supported is 65k.\n");
7215 // Read and remove the bitmap planes and bpp data. Verify them.
7216 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
7217 $data = substr($data, 4);
7218 if ($planes_and_bitcount[2] != 24) { // Bitcount
7219 die("$bitmap isn't a 24bit true color bitmap.\n");
7221 if ($planes_and_bitcount[1] != 1) {
7222 die("$bitmap: only 1 plane supported in bitmap image.\n");
7225 // Read and remove the bitmap compression. Verify compression.
7226 $compression = unpack("Vcomp", substr($data, 0, 4));
7227 $data = substr($data, 4);
7230 if ($compression['comp'] != 0) {
7231 die("$bitmap: compression not supported in bitmap image.\n");
7234 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
7235 $data = substr($data, 20);
7237 // Add the BITMAPCOREHEADER data
7238 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
7239 $data = $header . $data;
7241 return (array($width, $height, $size, $data));
7245 * Store the window zoom factor. This should be a reduced fraction but for
7246 * simplicity we will store all fractions with a numerator of 100.
7250 function _storeZoom()
7252 // If scale is 100 we don't need to write a record
7253 if ($this->_zoom == 100) {
7257 $record = 0x00A0; // Record identifier
7258 $length = 0x0004; // Bytes to follow
7260 $header = pack("vv", $record, $length);
7261 $data = pack("vv", $this->_zoom, 100);
7262 $this->_append($header . $data);
7266 * FIXME: add comments
7268 function setValidation($row1, $col1, $row2, $col2, &$validator)
7270 $this->_dv[] = $validator->_getData() .
7271 pack("vvvvv", 1, $row1, $row2, $col1, $col2);
7275 * Store the DVAL and DV records.
7279 function _storeDataValidity()
7281 $record = 0x01b2; // Record identifier
7282 $length = 0x0012; // Bytes to follow
7284 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
7285 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
7286 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
7287 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible
7289 $header = pack('vv', $record, $length);
7290 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
7292 $this->_append($header.$data);
7294 $record = 0x01be; // Record identifier
7295 foreach ($this->_dv as $dv) {
7296 $length = strlen($dv); // Bytes to follow
7297 $header = pack("vv", $record, $length);
7298 $this->_append($header . $dv);
7304 * Class for generating Excel Spreadsheets
7306 * @author Xavier Noguer <xnoguer@rezebra.com>
7307 * @category FileFormats
7308 * @package Spreadsheet_Excel_Writer
7311 class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
7314 * Filename for the Workbook
7321 * @var object Parser
7326 * Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
7332 * The active worksheet of the workbook (0 indexed)
7338 * 1st displayed worksheet in the workbook (0 indexed)
7344 * Number of workbook tabs selected
7350 * Index for creating adding new formats to the workbook
7356 * Flag for preventing close from being called twice.
7363 * The BIFF file size for the workbook.
7365 * @see _calcSheetOffsets()
7370 * The default sheetname for all sheets created.
7376 * The default XF format.
7377 * @var object Format
7382 * Array containing references to all of this workbook's worksheets
7388 * Array of sheetnames for creating the EXTERNSHEET records
7394 * Array containing references to all of this workbook's formats
7400 * Array containing the colour palette
7406 * The default format for URLs.
7407 * @var object Format
7412 * The codepage indicates the text encoding used for strings
7418 * The country code used for localization
7424 * The temporary dir for storing the OLE file
7430 * number of bytes for sizeinfo of strings
7433 var $_string_sizeinfo_size;
7435 // Dynamic members must be here (php8.2)
7436 var $_string_sizeinfo;
7444 * @param string filename for storing the workbook. "-" for writing to stdout.
7447 function __construct($filename)
7449 // It needs to call its parent's constructor explicitly
7450 parent::__construct();
7452 $this->_filename = $filename;
7453 $this->_parser = new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
7455 $this->_activesheet = 0;
7456 $this->_firstsheet = 0;
7457 $this->_selected = 0;
7458 $this->_xf_index = 16; // 15 style XF's and 1 cell XF.
7459 $this->_fileclosed = 0;
7460 $this->_biffsize = 0;
7461 $this->_sheetname = 'Sheet';
7462 $this->_tmp_format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
7463 $this->_worksheets = array();
7464 $this->_sheetnames = array();
7465 $this->_formats = array();
7466 $this->_palette = array();
7467 $this->_codepage = 0x04E4; // FIXME: should change for BIFF8
7468 $this->_country_code = -1;
7469 $this->_string_sizeinfo = 3;
7471 // Add the default format for hyperlinks
7472 $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
7473 $this->_str_total = 0;
7474 $this->_str_unique = 0;
7475 $this->_str_table = array();
7476 $this->_setPaletteXl97();
7477 $this->_tmp_dir = '';
7481 * Calls finalization methods.
7482 * This method should always be the last one to be called on every workbook
7485 * @return mixed true on success. PEAR_Error on failure
7489 if ($this->_fileclosed) { // Prevent close() from being called twice.
7492 $this->_storeWorkbook();
7493 $this->_fileclosed = 1;
7498 * An accessor for the _worksheets[] array
7499 * Returns an array of the worksheet objects in a workbook
7500 * It actually calls to worksheets()
7508 return $this->worksheets();
7512 * An accessor for the _worksheets[] array.
7513 * Returns an array of the worksheet objects in a workbook
7518 function worksheets()
7520 return $this->_worksheets;
7524 * Sets the BIFF version.
7525 * This method exists just to access experimental functionality
7526 * from BIFF8. It will be deprecated !
7527 * Only possible value is 8 (Excel 97/2000).
7528 * For any other value it fails silently.
7531 * @param integer $version The BIFF version
7533 function setVersion($version)
7535 if ($version == 8) { // only accept version 8
7537 $this->_BIFF_version = $version;
7538 // change BIFFwriter limit for CONTINUE records
7539 $this->_limit = 8228;
7540 $this->_tmp_format->_BIFF_version = $version;
7541 $this->_url_format->_BIFF_version = $version;
7542 $this->_parser->_BIFF_version = $version;
7544 $total_worksheets = count($this->_worksheets);
7545 // change version for all worksheets too
7546 for ($i = 0; $i < $total_worksheets; $i++) {
7547 $this->_worksheets[$i]->_BIFF_version = $version;
7550 $total_formats = count($this->_formats);
7551 // change version for all formats too
7552 for ($i = 0; $i < $total_formats; $i++) {
7553 $this->_formats[$i]->_BIFF_version = $version;
7559 * Set the country identifier for the workbook
7562 * @param integer $code Is the international calling country code for the
7565 function setCountry($code)
7567 $this->_country_code = $code;
7571 * Add a new worksheet to the Excel workbook.
7572 * If no name is given the name of the worksheet will be Sheeti$i, with
7576 * @param string $name the optional name of the worksheet
7577 * @return mixed reference to a worksheet object on success, PEAR_Error
7580 function &addWorksheet($name = '')
7582 $index = count($this->_worksheets);
7583 $sheetname = $this->_sheetname;
7586 $name = $sheetname.($index+1);
7589 // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
7590 if ($this->_BIFF_version != 0x0600)
7592 if (strlen($name) > 31) {
7593 die("Sheetname $name must be <= 31 chars");
7597 // Check that the worksheet name doesn't already exist: a fatal Excel error.
7598 $total_worksheets = count($this->_worksheets);
7599 for ($i = 0; $i < $total_worksheets; $i++) {
7600 if ($this->_worksheets[$i]->getName() == $name) {
7601 die("Worksheet '$name' already exists");
7605 $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
7607 $this->_activesheet, $this->_firstsheet,
7608 $this->_str_total, $this->_str_unique,
7609 $this->_str_table, $this->_url_format,
7612 $this->_worksheets[$index] = &$worksheet; // Store ref for iterator
7613 $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
7614 $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
7619 * Add a new format to the Excel workbook.
7620 * Also, pass any properties to the Format constructor.
7623 * @param array $properties array with properties for initializing the format.
7624 * @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format
7626 function &addFormat($properties = array())
7628 $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
7629 $this->_xf_index += 1;
7630 $this->_formats[] = &$format;
7635 * Create new validator.
7638 * @return &Spreadsheet_Excel_Writer_Validator reference to a Validator
7640 function &addValidator()
7642 include_once 'Spreadsheet/Excel/Writer/Validator.php';
7643 /* FIXME: check for successful inclusion*/
7644 $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
7649 * Change the RGB components of the elements in the colour palette.
7652 * @param integer $index colour index
7653 * @param integer $red red RGB value [0-255]
7654 * @param integer $green green RGB value [0-255]
7655 * @param integer $blue blue RGB value [0-255]
7656 * @return integer The palette index for the custom color
7658 function setCustomColor($index, $red, $green, $blue)
7660 // Match a HTML #xxyyzz style parameter
7661 /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
7662 @_ = ($_[0], hex $1, hex $2, hex $3);
7665 // Check that the colour index is the right range
7666 if ($index < 8 or $index > 64) {
7667 // TODO: assign real error codes
7668 die("Color index $index outside range: 8 <= index <= 64");
7671 // Check that the colour components are in the right range
7672 if (($red < 0 or $red > 255) ||
7673 ($green < 0 or $green > 255) ||
7674 ($blue < 0 or $blue > 255))
7676 die("Color component outside range: 0 <= color <= 255");
7679 $index -= 8; // Adjust colour index (wingless dragonfly)
7681 // Set the RGB value
7682 $this->_palette[$index] = array($red, $green, $blue, 0);
7687 * Sets the colour palette to the Excel 97+ default.
7691 function _setPaletteXl97()
7693 $this->_palette = array(
7694 array(0x00, 0x00, 0x00, 0x00), // 8
7695 array(0xff, 0xff, 0xff, 0x00), // 9
7696 array(0xff, 0x00, 0x00, 0x00), // 10
7697 array(0x00, 0xff, 0x00, 0x00), // 11
7698 array(0x00, 0x00, 0xff, 0x00), // 12
7699 array(0xff, 0xff, 0x00, 0x00), // 13
7700 array(0xff, 0x00, 0xff, 0x00), // 14
7701 array(0x00, 0xff, 0xff, 0x00), // 15
7702 array(0x80, 0x00, 0x00, 0x00), // 16
7703 array(0x00, 0x80, 0x00, 0x00), // 17
7704 array(0x00, 0x00, 0x80, 0x00), // 18
7705 array(0x80, 0x80, 0x00, 0x00), // 19
7706 array(0x80, 0x00, 0x80, 0x00), // 20
7707 array(0x00, 0x80, 0x80, 0x00), // 21
7708 array(0xc0, 0xc0, 0xc0, 0x00), // 22
7709 array(0x80, 0x80, 0x80, 0x00), // 23
7710 array(0x99, 0x99, 0xff, 0x00), // 24
7711 array(0x99, 0x33, 0x66, 0x00), // 25
7712 array(0xff, 0xff, 0xcc, 0x00), // 26
7713 array(0xcc, 0xff, 0xff, 0x00), // 27
7714 array(0x66, 0x00, 0x66, 0x00), // 28
7715 array(0xff, 0x80, 0x80, 0x00), // 29
7716 array(0x00, 0x66, 0xcc, 0x00), // 30
7717 array(0xcc, 0xcc, 0xff, 0x00), // 31
7718 array(0x00, 0x00, 0x80, 0x00), // 32
7719 array(0xff, 0x00, 0xff, 0x00), // 33
7720 array(0xff, 0xff, 0x00, 0x00), // 34
7721 array(0x00, 0xff, 0xff, 0x00), // 35
7722 array(0x80, 0x00, 0x80, 0x00), // 36
7723 array(0x80, 0x00, 0x00, 0x00), // 37
7724 array(0x00, 0x80, 0x80, 0x00), // 38
7725 array(0x00, 0x00, 0xff, 0x00), // 39
7726 array(0x00, 0xcc, 0xff, 0x00), // 40
7727 array(0xcc, 0xff, 0xff, 0x00), // 41
7728 array(0xcc, 0xff, 0xcc, 0x00), // 42
7729 array(0xff, 0xff, 0x99, 0x00), // 43
7730 array(0x99, 0xcc, 0xff, 0x00), // 44
7731 array(0xff, 0x99, 0xcc, 0x00), // 45
7732 array(0xcc, 0x99, 0xff, 0x00), // 46
7733 array(0xff, 0xcc, 0x99, 0x00), // 47
7734 array(0x33, 0x66, 0xff, 0x00), // 48
7735 array(0x33, 0xcc, 0xcc, 0x00), // 49
7736 array(0x99, 0xcc, 0x00, 0x00), // 50
7737 array(0xff, 0xcc, 0x00, 0x00), // 51
7738 array(0xff, 0x99, 0x00, 0x00), // 52
7739 array(0xff, 0x66, 0x00, 0x00), // 53
7740 array(0x66, 0x66, 0x99, 0x00), // 54
7741 array(0x96, 0x96, 0x96, 0x00), // 55
7742 array(0x00, 0x33, 0x66, 0x00), // 56
7743 array(0x33, 0x99, 0x66, 0x00), // 57
7744 array(0x00, 0x33, 0x00, 0x00), // 58
7745 array(0x33, 0x33, 0x00, 0x00), // 59
7746 array(0x99, 0x33, 0x00, 0x00), // 60
7747 array(0x99, 0x33, 0x66, 0x00), // 61
7748 array(0x33, 0x33, 0x99, 0x00), // 62
7749 array(0x33, 0x33, 0x33, 0x00), // 63
7754 * Assemble worksheets into a workbook and send the BIFF data to an OLE
7758 * @return mixed true on success. PEAR_Error on failure
7760 function _storeWorkbook()
7762 // Ensure that at least one worksheet has been selected.
7763 if ($this->_activesheet == 0) {
7764 $this->_worksheets[0]->selected = 1;
7767 // Calculate the number of selected worksheet tabs and call the finalization
7768 // methods for each worksheet
7769 $total_worksheets = count($this->_worksheets);
7770 for ($i = 0; $i < $total_worksheets; $i++) {
7771 if ($this->_worksheets[$i]->selected) {
7774 $this->_worksheets[$i]->close($this->_sheetnames);
7777 // Add Workbook globals
7778 $this->_storeBof(0x0005);
7779 $this->_storeCodepage();
7780 if ($this->_BIFF_version == 0x0600) {
7781 $this->_storeWindow1();
7783 if ($this->_BIFF_version == 0x0500) {
7784 $this->_storeExterns(); // For print area and repeat rows
7786 $this->_storeNames(); // For print area and repeat rows
7787 if ($this->_BIFF_version == 0x0500) {
7788 $this->_storeWindow1();
7790 $this->_storeDatemode();
7791 $this->_storeAllFonts();
7792 $this->_storeAllNumFormats();
7793 $this->_storeAllXfs();
7794 $this->_storeAllStyles();
7795 $this->_storePalette();
7796 $this->_calcSheetOffsets();
7798 // Add BOUNDSHEET records
7799 for ($i = 0; $i < $total_worksheets; $i++) {
7800 $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->_offset);
7803 if ($this->_country_code != -1) {
7804 $this->_storeCountry();
7807 if ($this->_BIFF_version == 0x0600) {
7808 //$this->_storeSupbookInternal();
7809 /* TODO: store external SUPBOOK records and XCT and CRN records
7810 in case of external references for BIFF8 */
7811 //$this->_storeExternsheetBiff8();
7812 $this->_storeSharedStringsTable();
7815 // End Workbook globals
7818 // Store the workbook in an OLE container
7819 $res = $this->_storeOLEFile();
7824 * Sets the temp dir used for storing the OLE file
7827 * @param string $dir The dir to be used as temp dir
7828 * @return true if given dir is valid, false otherwise
7830 function setTempDir($dir)
7833 $this->_tmp_dir = $dir;
7840 * Store the workbook in an OLE container
7843 * @return mixed true on success. PEAR_Error on failure
7845 function _storeOLEFile()
7847 if($this->_BIFF_version == 0x0600) {
7848 $OLE = new ole_pps_file(Asc2Ucs('Workbook'));
7850 $OLE = new ole_pps_file(Asc2Ucs('Book'));
7853 $OLE->append($this->_data);
7855 $total_worksheets = count($this->_worksheets);
7856 for ($i = 0; $i < $total_worksheets; $i++) {
7857 while ($tmp = $this->_worksheets[$i]->getData()) {
7862 $root = new ole_pps_root(false, false, array($OLE));
7864 $root->save($this->_filename);
7868 * Calculate offsets for Worksheet BOF records.
7872 function _calcSheetOffsets()
7874 if ($this->_BIFF_version == 0x0600) {
7875 $boundsheet_length = 12; // fixed length for a BOUNDSHEET record
7877 $boundsheet_length = 11;
7880 $offset = $this->_datasize;
7882 if ($this->_BIFF_version == 0x0600) {
7883 // add the length of the SST
7884 /* TODO: check this works for a lot of strings (> 8224 bytes) */
7885 $offset += $this->_calculateSharedStringsSizes();
7886 if ($this->_country_code != -1) {
7887 $offset += 8; // adding COUNTRY record
7889 // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
7890 //$offset += 8; // FIXME: calculate real value when storing the records
7892 $total_worksheets = count($this->_worksheets);
7893 // add the length of the BOUNDSHEET records
7894 for ($i = 0; $i < $total_worksheets; $i++) {
7895 $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
7899 for ($i = 0; $i < $total_worksheets; $i++) {
7900 $this->_worksheets[$i]->_offset = $offset;
7901 $offset += $this->_worksheets[$i]->_datasize;
7903 $this->_biffsize = $offset;
7907 * Store the Excel FONT records.
7911 function _storeAllFonts()
7913 // tmp_format is added by the constructor. We use this to write the default XF's
7914 $format = $this->_tmp_format;
7915 $font = $format->getFont();
7917 // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
7918 // so the following fonts are 0, 1, 2, 3, 5
7920 for ($i = 1; $i <= 5; $i++){
7921 $this->_append($font);
7924 // Iterate through the XF objects and write a FONT record if it isn't the
7925 // same as the default FONT and if it hasn't already been used.
7928 $index = 6; // The first user defined FONT
7930 $key = $format->getFontKey(); // The default font from _tmp_format
7931 $fonts[$key] = 0; // Index of the default font
7933 $total_formats = count($this->_formats);
7934 for ($i = 0; $i < $total_formats; $i++) {
7935 $key = $this->_formats[$i]->getFontKey();
7936 if (isset($fonts[$key])) {
7937 // FONT has already been used
7938 $this->_formats[$i]->font_index = $fonts[$key];
7940 // Add a new FONT record
7941 $fonts[$key] = $index;
7942 $this->_formats[$i]->font_index = $index;
7944 $font = $this->_formats[$i]->getFont();
7945 $this->_append($font);
7951 * Store user defined numerical formats i.e. FORMAT records
7955 function _storeAllNumFormats()
7957 // Leaning num_format syndrome
7958 $hash_num_formats = array();
7959 $num_formats = array();
7962 // Iterate through the XF objects and write a FORMAT record if it isn't a
7963 // built-in format type and if the FORMAT string hasn't already been used.
7964 $total_formats = count($this->_formats);
7965 for ($i = 0; $i < $total_formats; $i++) {
7966 $num_format = $this->_formats[$i]->_num_format;
7968 // Check if $num_format is an index to a built-in format.
7969 // Also check for a string of zeros, which is a valid format string
7970 // but would evaluate to zero.
7972 if (!preg_match("/^0+\d/", $num_format)) {
7973 if (preg_match("/^\d+$/", $num_format)) { // built-in format
7978 if (isset($hash_num_formats[$num_format])) {
7979 // FORMAT has already been used
7980 $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
7983 $hash_num_formats[$num_format] = $index;
7984 $this->_formats[$i]->_num_format = $index;
7985 array_push($num_formats,$num_format);
7990 // Write the new FORMAT records starting from 0xA4
7992 foreach ($num_formats as $num_format) {
7993 $this->_storeNumFormat($num_format,$index);
7999 * Write all XF records.
8003 function _storeAllXfs()
8005 // _tmp_format is added by the constructor. We use this to write the default XF's
8006 // The default font index is 0
8008 $format = $this->_tmp_format;
8009 for ($i = 0; $i <= 14; $i++) {
8010 $xf = $format->getXf('style'); // Style XF
8011 $this->_append($xf);
8014 $xf = $format->getXf('cell'); // Cell XF
8015 $this->_append($xf);
8018 $total_formats = count($this->_formats);
8019 for ($i = 0; $i < $total_formats; $i++) {
8020 $xf = $this->_formats[$i]->getXf('cell');
8021 $this->_append($xf);
8026 * Write all STYLE records.
8030 function _storeAllStyles()
8032 $this->_storeStyle();
8036 * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
8041 function _storeExterns()
8043 // Create EXTERNCOUNT with number of worksheets
8044 $this->_storeExterncount(count($this->_worksheets));
8046 // Create EXTERNSHEET for each worksheet
8047 foreach ($this->_sheetnames as $sheetname) {
8048 $this->_storeExternsheet($sheetname);
8053 * Write the NAME record to define the print area and the repeat rows and cols.
8057 function _storeNames()
8059 // Create the print area NAME records
8060 $total_worksheets = count($this->_worksheets);
8061 for ($i = 0; $i < $total_worksheets; $i++) {
8062 // Write a Name record if the print area has been defined
8063 if (isset($this->_worksheets[$i]->print_rowmin)) {
8064 $this->_storeNameShort(
8065 $this->_worksheets[$i]->index,
8067 $this->_worksheets[$i]->print_rowmin,
8068 $this->_worksheets[$i]->print_rowmax,
8069 $this->_worksheets[$i]->print_colmin,
8070 $this->_worksheets[$i]->print_colmax
8075 // Create the print title NAME records
8076 $total_worksheets = count($this->_worksheets);
8077 for ($i = 0; $i < $total_worksheets; $i++) {
8078 $rowmin = $this->_worksheets[$i]->title_rowmin;
8079 $rowmax = $this->_worksheets[$i]->title_rowmax;
8080 $colmin = $this->_worksheets[$i]->title_colmin;
8081 $colmax = $this->_worksheets[$i]->title_colmax;
8083 // Determine if row + col, row, col or nothing has been defined
8084 // and write the appropriate record
8086 if (isset($rowmin) && isset($colmin)) {
8087 // Row and column titles have been defined.
8088 // Row title has been defined.
8089 $this->_storeNameLong(
8090 $this->_worksheets[$i]->index,
8097 } elseif (isset($rowmin)) {
8098 // Row title has been defined.
8099 $this->_storeNameShort(
8100 $this->_worksheets[$i]->index,
8107 } elseif (isset($colmin)) {
8108 // Column title has been defined.
8109 $this->_storeNameShort(
8110 $this->_worksheets[$i]->index,
8118 // Print title hasn't been defined.
8126 /******************************************************************************
8133 * Stores the CODEPAGE biff record.
8137 function _storeCodepage()
8139 $record = 0x0042; // Record identifier
8140 $length = 0x0002; // Number of bytes to follow
8141 $cv = $this->_codepage; // The code page
8143 $header = pack('vv', $record, $length);
8144 $data = pack('v', $cv);
8146 $this->_append($header . $data);
8150 * Write Excel BIFF WINDOW1 record.
8154 function _storeWindow1()
8156 $record = 0x003D; // Record identifier
8157 $length = 0x0012; // Number of bytes to follow
8159 $xWn = 0x0000; // Horizontal position of window
8160 $yWn = 0x0000; // Vertical position of window
8161 $dxWn = 0x25BC; // Width of window
8162 $dyWn = 0x1572; // Height of window
8164 $grbit = 0x0038; // Option flags
8165 $ctabsel = $this->_selected; // Number of workbook tabs selected
8166 $wTabRatio = 0x0258; // Tab to scrollbar ratio
8168 $itabFirst = $this->_firstsheet; // 1st displayed worksheet
8169 $itabCur = $this->_activesheet; // Active worksheet
8171 $header = pack("vv", $record, $length);
8172 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
8174 $itabCur, $itabFirst,
8175 $ctabsel, $wTabRatio);
8176 $this->_append($header . $data);
8180 * Writes Excel BIFF BOUNDSHEET record.
8181 * FIXME: inconsistent with BIFF documentation
8183 * @param string $sheetname Worksheet name
8184 * @param integer $offset Location of worksheet BOF
8187 function _storeBoundsheet($sheetname,$offset)
8189 $record = 0x0085; // Record identifier
8191 if ($this->_BIFF_version == 0x0600) // Tried to fix the correct handling here, with the
8192 { // corrected specification from M$ - Joe Hunt 2009-03-08
8193 $encoding_string = $this->_input_encoding;
8194 if ($encoding_string == 'UTF-16LE')
8196 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
8199 else if ($encoding_string != '')
8201 $sheetname = iconv($encoding_string, 'UTF-16LE', $sheetname);
8202 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
8205 if ($strlen % 2 != 0)
8209 //$strlen = strlen($sheetname);
8210 $length = 0x08 + $strlen; // Number of bytes to follow
8212 $strlen = strlen($sheetname);
8213 $length = 0x07 + $strlen; // Number of bytes to follow
8216 $grbit = 0x0000; // Visibility and sheet type
8217 $cch = $strlen; // Length of sheet name
8219 $header = pack("vv", $record, $length);
8220 if ($this->_BIFF_version == 0x0600) {
8221 $data = pack("VvCC", $offset, $grbit, $cch, $encoding);
8223 $data = pack("VvC", $offset, $grbit, $cch);
8226 if ($this->_BIFF_version == 0x0600)
8228 $strlen = strlen($sheetname);
8229 $length = 0x08 + $strlen; // Number of bytes to follow
8231 $strlen = strlen($sheetname);
8232 $length = 0x07 + $strlen; // Number of bytes to follow
8235 $grbit = 0x0000; // Visibility and sheet type
8236 $cch = $strlen; // Length of sheet name
8238 $header = pack("vv", $record, $length);
8239 if ($this->_BIFF_version == 0x0600) {
8240 $data = pack("Vvv", $offset, $grbit, $cch);
8242 $data = pack("VvC", $offset, $grbit, $cch);
8244 $this->_append($header.$data.$sheetname);
8248 * Write Internal SUPBOOK record
8252 function _storeSupbookInternal()
8254 $record = 0x01AE; // Record identifier
8255 $length = 0x0004; // Bytes to follow
8257 $header = pack("vv", $record, $length);
8258 $data = pack("vv", count($this->_worksheets), 0x0104);
8259 $this->_append($header . $data);
8263 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
8266 * @param string $sheetname Worksheet name
8269 function _storeExternsheetBiff8()
8271 $total_references = count($this->_parser->_references);
8272 $record = 0x0017; // Record identifier
8273 $length = 2 + 6 * $total_references; // Number of bytes to follow
8275 $supbook_index = 0; // FIXME: only using internal SUPBOOK record
8276 $header = pack("vv", $record, $length);
8277 $data = pack('v', $total_references);
8278 for ($i = 0; $i < $total_references; $i++) {
8279 $data .= $this->_parser->_references[$i];
8281 $this->_append($header . $data);
8285 * Write Excel BIFF STYLE records.
8289 function _storeStyle()
8291 $record = 0x0293; // Record identifier
8292 $length = 0x0004; // Bytes to follow
8294 $ixfe = 0x8000; // Index to style XF
8295 $BuiltIn = 0x00; // Built-in style
8296 $iLevel = 0xff; // Outline style level
8298 $header = pack("vv", $record, $length);
8299 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
8300 $this->_append($header . $data);
8305 * Writes Excel FORMAT record for non "built-in" numerical formats.
8307 * @param string $format Custom format string
8308 * @param integer $ifmt Format index code
8311 function _storeNumFormat($format, $ifmt)
8313 $record = 0x041E; // Record identifier
8315 if ($this->_BIFF_version == 0x0600) {
8316 $length = 5 + strlen($format); // Number of bytes to follow
8318 } elseif ($this->_BIFF_version == 0x0500) {
8319 $length = 3 + strlen($format); // Number of bytes to follow
8322 $cch = strlen($format); // Length of format string
8324 $header = pack("vv", $record, $length);
8325 if ($this->_BIFF_version == 0x0600) {
8326 $data = pack("vvC", $ifmt, $cch, $encoding);
8327 } elseif ($this->_BIFF_version == 0x0500) {
8328 $data = pack("vC", $ifmt, $cch);
8330 $this->_append($header . $data . $format);
8334 * Write DATEMODE record to indicate the date system in use (1904 or 1900).
8338 function _storeDatemode()
8340 $record = 0x0022; // Record identifier
8341 $length = 0x0002; // Bytes to follow
8343 $f1904 = $this->_1904; // Flag for 1904 date system
8345 $header = pack("vv", $record, $length);
8346 $data = pack("v", $f1904);
8347 $this->_append($header . $data);
8352 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
8353 * references in the workbook.
8355 * Excel only stores references to external sheets that are used in NAME.
8356 * The workbook NAME record is required to define the print area and the repeat
8359 * A similar method is used in Worksheet.php for a slightly different purpose.
8361 * @param integer $cxals Number of external references
8364 function _storeExterncount($cxals)
8366 $record = 0x0016; // Record identifier
8367 $length = 0x0002; // Number of bytes to follow
8369 $header = pack("vv", $record, $length);
8370 $data = pack("v", $cxals);
8371 $this->_append($header . $data);
8376 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
8377 * formulas. NAME record is required to define the print area and the repeat
8380 * A similar method is used in Worksheet.php for a slightly different purpose.
8382 * @param string $sheetname Worksheet name
8385 function _storeExternsheet($sheetname)
8387 $record = 0x0017; // Record identifier
8388 $length = 0x02 + strlen($sheetname); // Number of bytes to follow
8390 $cch = strlen($sheetname); // Length of sheet name
8391 $rgch = 0x03; // Filename encoding
8393 $header = pack("vv", $record, $length);
8394 $data = pack("CC", $cch, $rgch);
8395 $this->_append($header . $data . $sheetname);
8400 * Store the NAME record in the short format that is used for storing the print
8401 * area, repeat rows only and repeat columns only.
8403 * @param integer $index Sheet index
8404 * @param integer $type Built-in name type
8405 * @param integer $rowmin Start row
8406 * @param integer $rowmax End row
8407 * @param integer $colmin Start colum
8408 * @param integer $colmax End column
8411 function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
8413 $record = 0x0018; // Record identifier
8414 $length = 0x0024; // Number of bytes to follow
8416 $grbit = 0x0020; // Option flags
8417 $chKey = 0x00; // Keyboard shortcut
8418 $cch = 0x01; // Length of text name
8419 $cce = 0x0015; // Length of text definition
8420 $ixals = $index + 1; // Sheet index
8421 $itab = $ixals; // Equal to ixals
8422 $cchCustMenu = 0x00; // Length of cust menu text
8423 $cchDescription = 0x00; // Length of description text
8424 $cchHelptopic = 0x00; // Length of help topic text
8425 $cchStatustext = 0x00; // Length of status bar text
8426 $rgch = $type; // Built-in name type
8429 $unknown04 = 0xffff-$index;
8430 $unknown05 = 0x0000;
8431 $unknown06 = 0x0000;
8432 $unknown07 = 0x1087;
8433 $unknown08 = 0x8005;
8435 $header = pack("vv", $record, $length);
8436 $data = pack("v", $grbit);
8437 $data .= pack("C", $chKey);
8438 $data .= pack("C", $cch);
8439 $data .= pack("v", $cce);
8440 $data .= pack("v", $ixals);
8441 $data .= pack("v", $itab);
8442 $data .= pack("C", $cchCustMenu);
8443 $data .= pack("C", $cchDescription);
8444 $data .= pack("C", $cchHelptopic);
8445 $data .= pack("C", $cchStatustext);
8446 $data .= pack("C", $rgch);
8447 $data .= pack("C", $unknown03);
8448 $data .= pack("v", $unknown04);
8449 $data .= pack("v", $unknown05);
8450 $data .= pack("v", $unknown06);
8451 $data .= pack("v", $unknown07);
8452 $data .= pack("v", $unknown08);
8453 $data .= pack("v", $index);
8454 $data .= pack("v", $index);
8455 $data .= pack("v", $rowmin);
8456 $data .= pack("v", $rowmax);
8457 $data .= pack("C", $colmin);
8458 $data .= pack("C", $colmax);
8459 $this->_append($header . $data);
8464 * Store the NAME record in the long format that is used for storing the repeat
8465 * rows and columns when both are specified. This shares a lot of code with
8466 * _storeNameShort() but we use a separate method to keep the code clean.
8467 * Code abstraction for reuse can be carried too far, and I should know. ;-)
8469 * @param integer $index Sheet index
8470 * @param integer $type Built-in name type
8471 * @param integer $rowmin Start row
8472 * @param integer $rowmax End row
8473 * @param integer $colmin Start colum
8474 * @param integer $colmax End column
8477 function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
8479 $record = 0x0018; // Record identifier
8480 $length = 0x003d; // Number of bytes to follow
8481 $grbit = 0x0020; // Option flags
8482 $chKey = 0x00; // Keyboard shortcut
8483 $cch = 0x01; // Length of text name
8484 $cce = 0x002e; // Length of text definition
8485 $ixals = $index + 1; // Sheet index
8486 $itab = $ixals; // Equal to ixals
8487 $cchCustMenu = 0x00; // Length of cust menu text
8488 $cchDescription = 0x00; // Length of description text
8489 $cchHelptopic = 0x00; // Length of help topic text
8490 $cchStatustext = 0x00; // Length of status bar text
8491 $rgch = $type; // Built-in name type
8494 $unknown02 = 0x002b;
8496 $unknown04 = 0xffff-$index;
8497 $unknown05 = 0x0000;
8498 $unknown06 = 0x0000;
8499 $unknown07 = 0x1087;
8500 $unknown08 = 0x8008;
8502 $header = pack("vv", $record, $length);
8503 $data = pack("v", $grbit);
8504 $data .= pack("C", $chKey);
8505 $data .= pack("C", $cch);
8506 $data .= pack("v", $cce);
8507 $data .= pack("v", $ixals);
8508 $data .= pack("v", $itab);
8509 $data .= pack("C", $cchCustMenu);
8510 $data .= pack("C", $cchDescription);
8511 $data .= pack("C", $cchHelptopic);
8512 $data .= pack("C", $cchStatustext);
8513 $data .= pack("C", $rgch);
8514 $data .= pack("C", $unknown01);
8515 $data .= pack("v", $unknown02);
8516 // Column definition
8517 $data .= pack("C", $unknown03);
8518 $data .= pack("v", $unknown04);
8519 $data .= pack("v", $unknown05);
8520 $data .= pack("v", $unknown06);
8521 $data .= pack("v", $unknown07);
8522 $data .= pack("v", $unknown08);
8523 $data .= pack("v", $index);
8524 $data .= pack("v", $index);
8525 $data .= pack("v", 0x0000);
8526 $data .= pack("v", 0x3fff);
8527 $data .= pack("C", $colmin);
8528 $data .= pack("C", $colmax);
8530 $data .= pack("C", $unknown03);
8531 $data .= pack("v", $unknown04);
8532 $data .= pack("v", $unknown05);
8533 $data .= pack("v", $unknown06);
8534 $data .= pack("v", $unknown07);
8535 $data .= pack("v", $unknown08);
8536 $data .= pack("v", $index);
8537 $data .= pack("v", $index);
8538 $data .= pack("v", $rowmin);
8539 $data .= pack("v", $rowmax);
8540 $data .= pack("C", 0x00);
8541 $data .= pack("C", 0xff);
8543 $data .= pack("C", 0x10);
8544 $this->_append($header . $data);
8548 * Stores the COUNTRY record for localization
8552 function _storeCountry()
8554 $record = 0x008C; // Record identifier
8555 $length = 4; // Number of bytes to follow
8557 $header = pack('vv', $record, $length);
8558 /* using the same country code always for simplicity */
8559 $data = pack('vv', $this->_country_code, $this->_country_code);
8560 $this->_append($header . $data);
8564 * Stores the PALETTE biff record.
8568 function _storePalette()
8570 $aref = $this->_palette;
8572 $record = 0x0092; // Record identifier
8573 $length = 2 + 4 * count($aref); // Number of bytes to follow
8574 $ccv = count($aref); // Number of RGB values to follow
8575 $data = ''; // The RGB data
8577 // Pack the RGB data
8578 foreach ($aref as $color) {
8579 foreach ($color as $byte) {
8580 $data .= pack("C",$byte);
8584 $header = pack("vvv", $record, $length, $ccv);
8585 $this->_append($header . $data);
8590 * Handling of the SST continue blocks is complicated by the need to include an
8591 * additional continuation byte depending on whether the string is split between
8592 * blocks or whether it starts at the beginning of the block. (There are also
8593 * additional complications that will arise later when/if Rich Strings are
8598 function _calculateSharedStringsSizes()
8600 /* Iterate through the strings to calculate the CONTINUE block sizes.
8601 For simplicity we use the same size for the SST and CONTINUE records:
8602 8228 : Maximum Excel97 block size
8603 -4 : Length of block header
8604 -8 : Length of additional SST header information
8605 -8 : Arbitrary number to keep within _add_continue() limit = 8208
8607 $continue_limit = 8208;
8610 $this->_block_sizes = array();
8613 foreach (array_keys($this->_str_table) as $string) {
8614 $string_length = strlen($string);
8615 $headerinfo = unpack("vlength/Cencoding", $string);
8616 $encoding = $headerinfo["encoding"];
8619 // Block length is the total length of the strings that will be
8620 // written out in a single SST or CONTINUE block.
8621 $block_length += $string_length;
8623 // We can write the string if it doesn't cross a CONTINUE boundary
8624 if ($block_length < $continue_limit) {
8625 $written += $string_length;
8629 // Deal with the cases where the next string to be written will exceed
8630 // the CONTINUE boundary. If the string is very long it may need to be
8631 // written in more than one CONTINUE record.
8632 while ($block_length >= $continue_limit) {
8634 // We need to avoid the case where a string is continued in the first
8635 // n bytes that contain the string header information.
8636 $header_length = 3; // Min string + header size -1
8637 $space_remaining = $continue_limit - $written - $continue;
8640 /* TODO: Unicode data should only be split on char (2 byte)
8641 boundaries. Therefore, in some cases we need to reduce the
8646 // Only applies to Unicode strings
8647 if ($encoding == 1) {
8648 // Min string + header size -1
8651 if ($space_remaining > $header_length) {
8652 // String contains 3 byte header => split on odd boundary
8653 if (!$split_string && $space_remaining % 2 != 1) {
8657 // Split section without header => split on even boundary
8658 else if ($split_string && $space_remaining % 2 == 1) {
8668 if ($space_remaining > $header_length) {
8669 // Write as much as possible of the string in the current block
8670 $written += $space_remaining;
8672 // Reduce the current block length by the amount written
8673 $block_length -= $continue_limit - $continue - $align;
8675 // Store the max size for this block
8676 $this->_block_sizes[] = $continue_limit - $align;
8678 // If the current string was split then the next CONTINUE block
8679 // should have the string continue flag (grbit) set unless the
8680 // split string fits exactly into the remaining space.
8681 if ($block_length > 0) {
8687 // Store the max size for this block
8688 $this->_block_sizes[] = $written + $continue;
8690 // Not enough space to start the string in the current block
8691 $block_length -= $continue_limit - $space_remaining - $continue;
8696 // If the string (or substr) is small enough we can write it in the
8697 // new CONTINUE block. Else, go through the loop again to write it in
8698 // one or more CONTINUE blocks
8699 if ($block_length < $continue_limit) {
8700 $written = $block_length;
8707 // Store the max size for the last block unless it is empty
8708 if ($written + $continue) {
8709 $this->_block_sizes[] = $written + $continue;
8713 /* Calculate the total length of the SST and associated CONTINUEs (if any).
8714 The SST record will have a length even if it contains no strings.
8715 This length is required to set the offsets in the BOUNDSHEET records since
8716 they must be written before the SST records
8719 $tmp_block_sizes = array();
8720 $tmp_block_sizes = $this->_block_sizes;
8723 if (!empty($tmp_block_sizes)) {
8724 $length += array_shift($tmp_block_sizes); // SST
8726 while (!empty($tmp_block_sizes)) {
8727 $length += 4 + array_shift($tmp_block_sizes); // CONTINUEs
8734 * Write all of the workbooks strings into an indexed array.
8735 * See the comments in _calculate_shared_string_sizes() for more information.
8737 * The Excel documentation says that the SST record should be followed by an
8738 * EXTSST record. The EXTSST record is a hash table that is used to optimise
8739 * access to SST. However, despite the documentation it doesn't seem to be
8740 * required so we will ignore it.
8744 function _storeSharedStringsTable()
8746 $record = 0x00fc; // Record identifier
8747 $length = 0x0008; // Number of bytes to follow
8750 // Iterate through the strings to calculate the CONTINUE block sizes
8751 $continue_limit = 8208;
8756 // sizes are upside down
8757 $tmp_block_sizes = $this->_block_sizes;
8758 // $tmp_block_sizes = array_reverse($this->_block_sizes);
8760 // The SST record is required even if it contains no strings. Thus we will
8761 // always have a length
8763 if (!empty($tmp_block_sizes)) {
8764 $length = 8 + array_shift($tmp_block_sizes);
8771 // Write the SST block header information
8772 $header = pack("vv", $record, $length);
8773 $data = pack("VV", $this->_str_total, $this->_str_unique);
8774 $this->_append($header . $data);
8776 /* TODO: not good for performance */
8777 foreach (array_keys($this->_str_table) as $string) {
8779 $string_length = strlen($string);
8780 $headerinfo = unpack("vlength/Cencoding", $string);
8781 $encoding = $headerinfo["encoding"];
8784 // Block length is the total length of the strings that will be
8785 // written out in a single SST or CONTINUE block.
8787 $block_length += $string_length;
8790 // We can write the string if it doesn't cross a CONTINUE boundary
8791 if ($block_length < $continue_limit) {
8792 $this->_append($string);
8793 $written += $string_length;
8797 // Deal with the cases where the next string to be written will exceed
8798 // the CONTINUE boundary. If the string is very long it may need to be
8799 // written in more than one CONTINUE record.
8801 while ($block_length >= $continue_limit) {
8803 // We need to avoid the case where a string is continued in the first
8804 // n bytes that contain the string header information.
8806 $header_length = 3; // Min string + header size -1
8807 $space_remaining = $continue_limit - $written - $continue;
8810 // Unicode data should only be split on char (2 byte) boundaries.
8811 // Therefore, in some cases we need to reduce the amount of available
8812 // space by 1 byte to ensure the correct alignment.
8815 // Only applies to Unicode strings
8816 if ($encoding == 1) {
8817 // Min string + header size -1
8820 if ($space_remaining > $header_length) {
8821 // String contains 3 byte header => split on odd boundary
8822 if (!$split_string && $space_remaining % 2 != 1) {
8826 // Split section without header => split on even boundary
8827 else if ($split_string && $space_remaining % 2 == 1) {
8837 if ($space_remaining > $header_length) {
8838 // Write as much as possible of the string in the current block
8839 $tmp = substr($string, 0, $space_remaining);
8840 $this->_append($tmp);
8842 // The remainder will be written in the next block(s)
8843 $string = substr($string, $space_remaining);
8845 // Reduce the current block length by the amount written
8846 $block_length -= $continue_limit - $continue - $align;
8848 // If the current string was split then the next CONTINUE block
8849 // should have the string continue flag (grbit) set unless the
8850 // split string fits exactly into the remaining space.
8852 if ($block_length > 0) {
8858 // Not enough space to start the string in the current block
8859 $block_length -= $continue_limit - $space_remaining - $continue;
8863 // Write the CONTINUE block header
8864 if (!empty($this->_block_sizes)) {
8866 $length = array_shift($tmp_block_sizes);
8868 $header = pack('vv', $record, $length);
8870 $header .= pack('C', $encoding);
8872 $this->_append($header);
8875 // If the string (or substr) is small enough we can write it in the
8876 // new CONTINUE block. Else, go through the loop again to write it in
8877 // one or more CONTINUE blocks
8879 if ($block_length < $continue_limit) {
8880 $this->_append($string);
8881 $written = $block_length;