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()
951 function __construct()
953 $this->_byte_order = '';
955 $this->_datasize = 0;
956 $this->_limit = 2080;
957 // Set the byte order
958 $this->_setByteOrder();
962 * Determine the byte order and store it as class data to avoid
963 * recalculating it for each call to new().
967 function _setByteOrder()
969 // Check if "pack" gives the required IEEE 64bit float
970 $teststr = pack("d", 1.2345);
971 $number = pack("C8", 0x8D, 0x97, 0x6E, 0x12, 0x83, 0xC0, 0xF3, 0x3F);
972 if ($number == $teststr) {
973 $byte_order = 0; // Little Endian
974 } elseif ($number == strrev($teststr)){
975 $byte_order = 1; // Big Endian
977 // Give up. I'll fix this in a later version.
978 die("Required floating point format ".
979 "not supported on this platform.");
981 $this->_byte_order = $byte_order;
985 * General storage function
987 * @param string $data binary data to prepend
990 function _prepend($data)
992 if (strlen($data) > $this->_limit) {
993 $data = $this->_addContinue($data);
995 $this->_data = $data.$this->_data;
996 $this->_datasize += strlen($data);
1000 * General storage function
1002 * @param string $data binary data to append
1005 function _append($data)
1007 if (strlen($data) > $this->_limit) {
1008 $data = $this->_addContinue($data);
1010 $this->_data = $this->_data.$data;
1011 $this->_datasize += strlen($data);
1015 * Writes Excel BOF record to indicate the beginning of a stream or
1016 * sub-stream in the BIFF file.
1018 * @param integer $type Type of BIFF file to write: 0x0005 Workbook,
1022 function _storeBof($type)
1024 $record = 0x0809; // Record identifier
1026 // According to the SDK $build and $year should be set to zero.
1027 // However, this throws a warning in Excel 5. So, use magic numbers.
1028 if ($this->_BIFF_version == 0x0500) {
1033 } elseif ($this->_BIFF_version == 0x0600) {
1035 $unknown = pack("VV", 0x00000041, 0x00000006); //unknown last 8 bytes for BIFF8
1039 $version = $this->_BIFF_version;
1041 $header = pack("vv", $record, $length);
1042 $data = pack("vvvv", $version, $type, $build, $year);
1043 $this->_prepend($header . $data . $unknown);
1047 * Writes Excel EOF record to indicate the end of a BIFF stream.
1051 function _storeEof()
1053 $record = 0x000A; // Record identifier
1054 $length = 0x0000; // Number of bytes to follow
1055 $header = pack("vv", $record, $length);
1056 $this->_append($header);
1060 * Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In
1061 * Excel 97 the limit is 8228 bytes. Records that are longer than these limits
1062 * must be split up into CONTINUE blocks.
1064 * This function takes a long BIFF record and inserts CONTINUE records as
1067 * @param string $data The original binary data to be written
1068 * @return string A very convenient string of continue blocks
1071 function _addContinue($data)
1073 $limit = $this->_limit;
1074 $record = 0x003C; // Record identifier
1076 // The first 2080/8224 bytes remain intact. However, we have to change
1077 // the length field of the record.
1078 $tmp = substr($data, 0, 2).pack("v", $limit-4).substr($data, 4, $limit - 4);
1080 $header = pack("vv", $record, $limit); // Headers for continue records
1082 // Retrieve chunks of 2080/8224 bytes +4 for the header.
1083 $data_length = strlen($data);
1084 for ($i = $limit; $i < ($data_length - $limit); $i += $limit) {
1086 $tmp .= substr($data, $i, $limit);
1089 // Retrieve the last chunk of data
1090 $header = pack("vv", $record, strlen($data) - $i);
1092 $tmp .= substr($data, $i, strlen($data) - $i);
1099 FIXME: change prefixes
1101 define("OP_BETWEEN", 0x00);
1102 define("OP_NOTBETWEEN", 0x01);
1103 define("OP_EQUAL", 0x02);
1104 define("OP_NOTEQUAL", 0x03);
1105 define("OP_GT", 0x04);
1106 define("OP_LT", 0x05);
1107 define("OP_GTE", 0x06);
1108 define("OP_LTE", 0x07);
1111 * Baseclass for generating Excel DV records (validations)
1113 * @author Herman Kuiper
1114 * @category FileFormats
1115 * @package Spreadsheet_Excel_Writer
1117 class Spreadsheet_Excel_Writer_Validator
1134 * The parser from the workbook. Used to parse validation formulas also
1135 * @var Spreadsheet_Excel_Writer_Parser
1139 function __construct(&$parser)
1141 $this->_parser = $parser;
1142 $this->_type = 0x01; // FIXME: add method for setting datatype
1143 $this->_style = 0x00;
1144 $this->_fixedList = false;
1145 $this->_blank = false;
1146 $this->_incell = false;
1147 $this->_showprompt = false;
1148 $this->_showerror = true;
1149 $this->_title_prompt = "\x00";
1150 $this->_descr_prompt = "\x00";
1151 $this->_title_error = "\x00";
1152 $this->_descr_error = "\x00";
1153 $this->_operator = 0x00; // default is equal
1154 $this->_formula1 = '';
1155 $this->_formula2 = '';
1158 function setPrompt($promptTitle = "\x00", $promptDescription = "\x00", $showPrompt = true)
1160 $this->_showprompt = $showPrompt;
1161 $this->_title_prompt = $promptTitle;
1162 $this->_descr_prompt = $promptDescription;
1165 function setError($errorTitle = "\x00", $errorDescription = "\x00", $showError = true)
1167 $this->_showerror = $showError;
1168 $this->_title_error = $errorTitle;
1169 $this->_descr_error = $errorDescription;
1172 function allowBlank()
1174 $this->_blank = true;
1177 function onInvalidStop()
1179 $this->_style = 0x00;
1182 function onInvalidWarn()
1184 $this->_style = 0x01;
1187 function onInvalidInfo()
1189 $this->_style = 0x02;
1192 function setFormula1($formula)
1194 // Parse the formula using the parser in Parser.php
1195 $this->_parser->parse($formula);
1197 $this->_formula1 = $this->_parser->toReversePolish();
1201 function setFormula2($formula)
1203 // Parse the formula using the parser in Parser.php
1204 $this->_parser->parse($formula);
1206 $this->_formula2 = $this->_parser->toReversePolish();
1210 function _getOptions()
1212 $options = $this->_type;
1213 $options |= $this->_style << 3;
1214 if ($this->_fixedList) {
1217 if ($this->_blank) {
1220 if (!$this->_incell) {
1223 if ($this->_showprompt) {
1224 $options |= 0x40000;
1226 if ($this->_showerror) {
1227 $options |= 0x80000;
1229 $options |= $this->_operator << 20;
1236 $title_prompt_len = strlen($this->_title_prompt);
1237 $descr_prompt_len = strlen($this->_descr_prompt);
1238 $title_error_len = strlen($this->_title_error);
1239 $descr_error_len = strlen($this->_descr_error);
1241 $formula1_size = strlen($this->_formula1);
1242 $formula2_size = strlen($this->_formula2);
1244 $data = pack("V", $this->_getOptions());
1245 $data .= pack("vC", $title_prompt_len, 0x00) . $this->_title_prompt;
1246 $data .= pack("vC", $title_error_len, 0x00) . $this->_title_error;
1247 $data .= pack("vC", $descr_prompt_len, 0x00) . $this->_descr_prompt;
1248 $data .= pack("vC", $descr_error_len, 0x00) . $this->_descr_error;
1250 $data .= pack("vv", $formula1_size, 0x0000) . $this->_formula1;
1251 $data .= pack("vv", $formula2_size, 0x0000) . $this->_formula2;
1258 * Class for generating Excel XF records (formats)
1260 * @author Xavier Noguer <xnoguer@rezebra.com>
1261 * @category FileFormats
1262 * @package Spreadsheet_Excel_Writer
1265 class Spreadsheet_Excel_Writer_Format
1268 * The index given by the workbook when creating a new format.
1274 * Index to the FONT record.
1280 * The font name (ASCII).
1286 * Height of font (1/20 of a point)
1298 * Bit specifiying if the font is italic.
1304 * Index to the cell's color
1310 * The text underline property
1316 * Bit specifiying if the font has strikeout.
1319 var $_font_strikeout;
1322 * Bit specifiying if the font has outline.
1328 * Bit specifiying if the font has shadow.
1334 * 2 bytes specifiying the script type for the font.
1340 * Byte specifiying the font family.
1346 * Byte specifiying the font charset.
1352 * An index (2 bytes) to a FORMAT record (number format).
1358 * Bit specifying if formulas are hidden.
1364 * Bit specifying if the cell is locked.
1370 * The three bits specifying the text horizontal alignment.
1376 * Bit specifying if the text is wrapped at the right border.
1382 * The three bits specifying the text vertical alignment.
1388 * 1 bit, apparently not used.
1391 var $_text_justlast;
1394 * The two bits specifying the text rotation.
1400 * The cell's foreground color.
1406 * The cell's background color.
1412 * The cell's background fill pattern.
1418 * Style of the bottom border of the cell
1424 * Color of the bottom border of the cell.
1430 * Style of the top border of the cell
1436 * Color of the top border of the cell.
1442 * Style of the left border of the cell
1448 * Color of the left border of the cell.
1454 * Style of the right border of the cell
1460 * Color of the right border of the cell.
1469 * @param integer $index the XF index for the format.
1470 * @param array $properties array with properties to be set on initialization.
1472 function __construct($BIFF_version, $index = 0, $properties = array())
1474 $this->_xf_index = $index;
1475 $this->_BIFF_version = $BIFF_version;
1476 $this->font_index = 0;
1477 $this->_font_name = 'Arial';
1479 $this->_bold = 0x0190;
1481 $this->_color = 0x7FFF;
1482 $this->_underline = 0;
1483 $this->_font_strikeout = 0;
1484 $this->_font_outline = 0;
1485 $this->_font_shadow = 0;
1486 $this->_font_script = 0;
1487 $this->_font_family = 0;
1488 $this->_font_charset = 0;
1490 $this->_num_format = 0;
1495 $this->_text_h_align = 0;
1496 $this->_text_wrap = 0;
1497 $this->_text_v_align = 2;
1498 $this->_text_justlast = 0;
1499 $this->_rotation = 0;
1501 $this->_fg_color = 0x40;
1502 $this->_bg_color = 0x41;
1504 $this->_pattern = 0;
1512 $this->_bottom_color = 0x40;
1513 $this->_top_color = 0x40;
1514 $this->_left_color = 0x40;
1515 $this->_right_color = 0x40;
1516 $this->_diag_color = 0x40;
1518 // Set properties passed to Spreadsheet_Excel_Writer_Workbook::addFormat()
1519 foreach ($properties as $property => $value)
1521 if (method_exists($this, 'set'.ucwords($property))) {
1522 $method_name = 'set'.ucwords($property);
1523 $this->$method_name($value);
1530 * Generate an Excel BIFF XF record (style or cell).
1532 * @param string $style The type of the XF record ('style' or 'cell').
1533 * @return string The XF record
1535 function getXf($style)
1537 // Set the type of the XF record and some of the attributes.
1538 if ($style == 'style') {
1541 $style = $this->_locked;
1542 $style |= $this->_hidden << 1;
1545 // Flags to indicate if attributes have been set.
1546 $atr_num = ($this->_num_format != 0)?1:0;
1547 $atr_fnt = ($this->font_index != 0)?1:0;
1548 $atr_alc = ($this->_text_wrap)?1:0;
1549 $atr_bdr = ($this->_bottom ||
1553 $atr_pat = (($this->_fg_color != 0x40) ||
1554 ($this->_bg_color != 0x41) ||
1555 $this->_pattern)?1:0;
1556 $atr_prot = $this->_locked | $this->_hidden;
1558 // Zero the default border colour if the border has not been set.
1559 if ($this->_bottom == 0) {
1560 $this->_bottom_color = 0;
1562 if ($this->_top == 0) {
1563 $this->_top_color = 0;
1565 if ($this->_right == 0) {
1566 $this->_right_color = 0;
1568 if ($this->_left == 0) {
1569 $this->_left_color = 0;
1571 if ($this->_diag == 0) {
1572 $this->_diag_color = 0;
1575 $record = 0x00E0; // Record identifier
1576 if ($this->_BIFF_version == 0x0500) {
1577 $length = 0x0010; // Number of bytes to follow
1579 if ($this->_BIFF_version == 0x0600) {
1583 $ifnt = $this->font_index; // Index to FONT record
1584 $ifmt = $this->_num_format; // Index to FORMAT record
1585 if ($this->_BIFF_version == 0x0500) {
1586 $align = $this->_text_h_align; // Alignment
1587 $align |= $this->_text_wrap << 3;
1588 $align |= $this->_text_v_align << 4;
1589 $align |= $this->_text_justlast << 7;
1590 $align |= $this->_rotation << 8;
1591 $align |= $atr_num << 10;
1592 $align |= $atr_fnt << 11;
1593 $align |= $atr_alc << 12;
1594 $align |= $atr_bdr << 13;
1595 $align |= $atr_pat << 14;
1596 $align |= $atr_prot << 15;
1598 $icv = $this->_fg_color; // fg and bg pattern colors
1599 $icv |= $this->_bg_color << 7;
1601 $fill = $this->_pattern; // Fill and border line style
1602 $fill |= $this->_bottom << 6;
1603 $fill |= $this->_bottom_color << 9;
1605 $border1 = $this->_top; // Border line style and color
1606 $border1 |= $this->_left << 3;
1607 $border1 |= $this->_right << 6;
1608 $border1 |= $this->_top_color << 9;
1610 $border2 = $this->_left_color; // Border color
1611 $border2 |= $this->_right_color << 7;
1613 $header = pack("vv", $record, $length);
1614 $data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align,
1616 $border1, $border2);
1617 } elseif ($this->_BIFF_version == 0x0600) {
1618 $align = $this->_text_h_align; // Alignment
1619 $align |= $this->_text_wrap << 3;
1620 $align |= $this->_text_v_align << 4;
1621 $align |= $this->_text_justlast << 7;
1623 $used_attrib = $atr_num << 2;
1624 $used_attrib |= $atr_fnt << 3;
1625 $used_attrib |= $atr_alc << 4;
1626 $used_attrib |= $atr_bdr << 5;
1627 $used_attrib |= $atr_pat << 6;
1628 $used_attrib |= $atr_prot << 7;
1630 $icv = $this->_fg_color; // fg and bg pattern colors
1631 $icv |= $this->_bg_color << 7;
1633 $border1 = $this->_left; // Border line style and color
1634 $border1 |= $this->_right << 4;
1635 $border1 |= $this->_top << 8;
1636 $border1 |= $this->_bottom << 12;
1637 $border1 |= $this->_left_color << 16;
1638 $border1 |= $this->_right_color << 23;
1639 $diag_tl_to_rb = 0; // FIXME: add method
1640 $diag_tr_to_lb = 0; // FIXME: add method
1641 $border1 |= $diag_tl_to_rb << 30;
1642 $border1 |= $diag_tr_to_lb << 31;
1644 $border2 = $this->_top_color; // Border color
1645 $border2 |= $this->_bottom_color << 7;
1646 $border2 |= $this->_diag_color << 14;
1647 $border2 |= $this->_diag << 21;
1648 $border2 |= $this->_pattern << 26;
1650 $header = pack("vv", $record, $length);
1653 $biff8_options = 0x00;
1654 $data = pack("vvvC", $ifnt, $ifmt, $style, $align);
1655 $data .= pack("CCC", $rotation, $biff8_options, $used_attrib);
1656 $data .= pack("VVv", $border1, $border2, $icv);
1659 return($header . $data);
1663 * Generate an Excel BIFF FONT record.
1665 * @return string The FONT record
1669 $dyHeight = $this->_size * 20; // Height of font (1/20 of a point)
1670 $icv = $this->_color; // Index to color palette
1671 $bls = $this->_bold; // Bold style
1672 $sss = $this->_font_script; // Superscript/subscript
1673 $uls = $this->_underline; // Underline
1674 $bFamily = $this->_font_family; // Font family
1675 $bCharSet = $this->_font_charset; // Character set
1676 $encoding = 0; // TODO: Unicode support
1678 $cch = strlen($this->_font_name); // Length of font name
1679 $record = 0x31; // Record identifier
1680 if ($this->_BIFF_version == 0x0500) {
1681 $length = 0x0F + $cch; // Record length
1682 } elseif ($this->_BIFF_version == 0x0600) {
1683 $length = 0x10 + $cch;
1685 $reserved = 0x00; // Reserved
1686 $grbit = 0x00; // Font attributes
1687 if ($this->_italic) {
1690 if ($this->_font_strikeout) {
1693 if ($this->_font_outline) {
1696 if ($this->_font_shadow) {
1700 $header = pack("vv", $record, $length);
1701 if ($this->_BIFF_version == 0x0500) {
1702 $data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls,
1703 $sss, $uls, $bFamily,
1704 $bCharSet, $reserved, $cch);
1705 } elseif ($this->_BIFF_version == 0x0600) {
1706 $data = pack("vvvvvCCCCCC", $dyHeight, $grbit, $icv, $bls,
1707 $sss, $uls, $bFamily,
1708 $bCharSet, $reserved, $cch, $encoding);
1710 return($header . $data . $this->_font_name);
1714 * Returns a unique hash key for a font.
1715 * Used by Spreadsheet_Excel_Writer_Workbook::_storeAllFonts()
1717 * The elements that form the key are arranged to increase the probability of
1718 * generating a unique key. Elements that hold a large range of numbers
1719 * (eg. _color) are placed between two binary elements such as _italic
1721 * @return string A key for this font
1723 function getFontKey()
1725 $key = "$this->_font_name$this->_size";
1726 $key .= "$this->_font_script$this->_underline";
1727 $key .= "$this->_font_strikeout$this->_bold$this->_font_outline";
1728 $key .= "$this->_font_family$this->_font_charset";
1729 $key .= "$this->_font_shadow$this->_color$this->_italic";
1730 $key = str_replace(' ', '_', $key);
1735 * Returns the index used by Spreadsheet_Excel_Writer_Worksheet::_XF()
1737 * @return integer The index for the XF record
1739 function getXfIndex()
1741 return($this->_xf_index);
1745 * Used in conjunction with the set_xxx_color methods to convert a color
1746 * string into a number. Color range is 0..63 but we will restrict it
1747 * to 8..63 to comply with Gnumeric. Colors 0..7 are repeated in 8..15.
1750 * @param string $name_color name of the color (i.e.: 'blue', 'red', etc..). Optional.
1751 * @return integer The color index
1753 function _getColor($name_color = '')
1776 // Return the default color, 0x7FFF, if undef,
1777 if ($name_color == '') {
1781 // or the color string converted to an integer,
1782 if (isset($colors[$name_color])) {
1783 return($colors[$name_color]);
1786 // or the default color if string is unrecognised,
1787 if (preg_match("/\D/",$name_color)) {
1791 // or an index < 8 mapped into the correct range,
1792 if ($name_color < 8) {
1793 return($name_color + 8);
1796 // or the default color if arg is outside range,
1797 if ($name_color > 63) {
1801 // or an integer in the valid range
1802 return($name_color);
1806 * Set cell alignment.
1809 * @param string $location alignment for the cell ('left', 'right', etc...).
1811 function setAlign($location)
1813 if (preg_match("/\d/",$location)) {
1814 return; // Ignore numbers
1817 $location = strtolower($location);
1819 if ($location == 'left') {
1820 $this->_text_h_align = 1;
1822 if ($location == 'centre') {
1823 $this->_text_h_align = 2;
1825 if ($location == 'center') {
1826 $this->_text_h_align = 2;
1828 if ($location == 'right') {
1829 $this->_text_h_align = 3;
1831 if ($location == 'fill') {
1832 $this->_text_h_align = 4;
1834 if ($location == 'justify') {
1835 $this->_text_h_align = 5;
1837 if ($location == 'merge') {
1838 $this->_text_h_align = 6;
1840 if ($location == 'equal_space') { // For T.K.
1841 $this->_text_h_align = 7;
1843 if ($location == 'top') {
1844 $this->_text_v_align = 0;
1846 if ($location == 'vcentre') {
1847 $this->_text_v_align = 1;
1849 if ($location == 'vcenter') {
1850 $this->_text_v_align = 1;
1852 if ($location == 'bottom') {
1853 $this->_text_v_align = 2;
1855 if ($location == 'vjustify') {
1856 $this->_text_v_align = 3;
1858 if ($location == 'vequal_space') { // For T.K.
1859 $this->_text_v_align = 4;
1864 * Set cell horizontal alignment.
1867 * @param string $location alignment for the cell ('left', 'right', etc...).
1869 function setHAlign($location)
1871 if (preg_match("/\d/",$location)) {
1872 return; // Ignore numbers
1875 $location = strtolower($location);
1877 if ($location == 'left') {
1878 $this->_text_h_align = 1;
1880 if ($location == 'centre') {
1881 $this->_text_h_align = 2;
1883 if ($location == 'center') {
1884 $this->_text_h_align = 2;
1886 if ($location == 'right') {
1887 $this->_text_h_align = 3;
1889 if ($location == 'fill') {
1890 $this->_text_h_align = 4;
1892 if ($location == 'justify') {
1893 $this->_text_h_align = 5;
1895 if ($location == 'merge') {
1896 $this->_text_h_align = 6;
1898 if ($location == 'equal_space') { // For T.K.
1899 $this->_text_h_align = 7;
1904 * Set cell vertical alignment.
1907 * @param string $location alignment for the cell ('top', 'vleft', 'vright', etc...).
1909 function setVAlign($location)
1911 if (preg_match("/\d/",$location)) {
1912 return; // Ignore numbers
1915 $location = strtolower($location);
1917 if ($location == 'top') {
1918 $this->_text_v_align = 0;
1920 if ($location == 'vcentre') {
1921 $this->_text_v_align = 1;
1923 if ($location == 'vcenter') {
1924 $this->_text_v_align = 1;
1926 if ($location == 'bottom') {
1927 $this->_text_v_align = 2;
1929 if ($location == 'vjustify') {
1930 $this->_text_v_align = 3;
1932 if ($location == 'vequal_space') { // For T.K.
1933 $this->_text_v_align = 4;
1938 * This is an alias for the unintuitive setAlign('merge')
1944 $this->setAlign('merge');
1948 * Sets the boldness of the text.
1949 * Bold has a range 100..1000.
1950 * 0 (400) is normal. 1 (700) is bold.
1953 * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
1954 1 maps to 700 (bold text). Valid range is: 100-1000.
1955 It's Optional, default is 1 (bold).
1957 function setBold($weight = 1)
1960 $weight = 0x2BC; // Bold text
1963 $weight = 0x190; // Normal text
1965 if ($weight < 0x064) {
1966 $weight = 0x190; // Lower bound
1968 if ($weight > 0x3E8) {
1969 $weight = 0x190; // Upper bound
1971 $this->_bold = $weight;
1975 /************************************
1976 * FUNCTIONS FOR SETTING CELLS BORDERS
1980 * Sets the width for the bottom border of the cell
1983 * @param integer $style style of the cell border. 1 => thin, 2 => thick.
1985 function setBottom($style)
1987 $this->_bottom = $style;
1991 * Sets the width for the top border of the cell
1994 * @param integer $style style of the cell top border. 1 => thin, 2 => thick.
1996 function setTop($style)
1998 $this->_top = $style;
2002 * Sets the width for the left border of the cell
2005 * @param integer $style style of the cell left border. 1 => thin, 2 => thick.
2007 function setLeft($style)
2009 $this->_left = $style;
2013 * Sets the width for the right border of the cell
2016 * @param integer $style style of the cell right border. 1 => thin, 2 => thick.
2018 function setRight($style)
2020 $this->_right = $style;
2025 * Set cells borders to the same style
2028 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
2030 function setBorder($style)
2032 $this->setBottom($style);
2033 $this->setTop($style);
2034 $this->setLeft($style);
2035 $this->setRight($style);
2039 /*******************************************
2040 * FUNCTIONS FOR SETTING CELLS BORDERS COLORS
2044 * Sets all the cell's borders to the same color
2047 * @param mixed $color The color we are setting. Either a string (like 'blue'),
2048 * or an integer (range is [8...63]).
2050 function setBorderColor($color)
2052 $this->setBottomColor($color);
2053 $this->setTopColor($color);
2054 $this->setLeftColor($color);
2055 $this->setRightColor($color);
2059 * Sets the cell's bottom border color
2062 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2064 function setBottomColor($color)
2066 $value = $this->_getColor($color);
2067 $this->_bottom_color = $value;
2071 * Sets the cell's top border color
2074 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2076 function setTopColor($color)
2078 $value = $this->_getColor($color);
2079 $this->_top_color = $value;
2083 * Sets the cell's left border color
2086 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2088 function setLeftColor($color)
2090 $value = $this->_getColor($color);
2091 $this->_left_color = $value;
2095 * Sets the cell's right border color
2098 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2100 function setRightColor($color)
2102 $value = $this->_getColor($color);
2103 $this->_right_color = $value;
2108 * Sets the cell's foreground color
2111 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2113 function setFgColor($color)
2115 $value = $this->_getColor($color);
2116 $this->_fg_color = $value;
2117 if ($this->_pattern == 0) { // force color to be seen
2118 $this->_pattern = 1;
2123 * Sets the cell's background color
2126 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2128 function setBgColor($color)
2130 $value = $this->_getColor($color);
2131 $this->_bg_color = $value;
2132 if ($this->_pattern == 0) { // force color to be seen
2133 $this->_pattern = 1;
2138 * Sets the cell's color
2141 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63]).
2143 function setColor($color)
2145 $value = $this->_getColor($color);
2146 $this->_color = $value;
2150 * Sets the fill pattern attribute of a cell
2153 * @param integer $arg Optional. Defaults to 1. Meaningful values are: 0-18,
2154 * 0 meaning no background.
2156 function setPattern($arg = 1)
2158 $this->_pattern = $arg;
2162 * Sets the underline of the text
2165 * @param integer $underline The value for underline. Possible values are:
2166 * 1 => underline, 2 => double underline.
2168 function setUnderline($underline)
2170 $this->_underline = $underline;
2174 * Sets the font style as italic
2178 function setItalic()
2184 * Sets the font size
2187 * @param integer $size The font size (in pixels I think).
2189 function setSize($size)
2191 $this->_size = $size;
2195 * Sets text wrapping
2199 function setTextWrap()
2201 $this->_text_wrap = 1;
2205 * Sets the orientation of the text
2208 * @param integer $angle The rotation angle for the text (clockwise). Possible
2209 values are: 0, 90, 270 and -1 for stacking top-to-bottom.
2211 function setTextRotation($angle)
2216 $this->_rotation = 0;
2219 $this->_rotation = 3;
2222 $this->_rotation = 2;
2225 $this->_rotation = 1;
2228 $this->_rotation = 0;
2234 * Sets the numeric format.
2235 * It can be date, time, currency, etc...
2238 * @param integer $num_format The numeric format.
2240 function setNumFormat($num_format)
2242 $this->_num_format = $num_format;
2246 * Sets font as strikeout.
2250 function setStrikeOut()
2252 $this->_font_strikeout = 1;
2256 * Sets outlining for a font.
2260 function setOutLine()
2262 $this->_font_outline = 1;
2266 * Sets font as shadow.
2270 function setShadow()
2272 $this->_font_shadow = 1;
2276 * Sets the script type of the text
2279 * @param integer $script The value for script type. Possible values are:
2280 * 1 => superscript, 2 => subscript.
2282 function setScript($script)
2284 $this->_font_script = $script;
2292 function setLocked()
2298 * Unlocks a cell. Useful for unprotecting particular cells of a protected sheet.
2302 function setUnLocked()
2308 * Sets the font family name.
2311 * @param string $fontfamily The font family name. Possible values are:
2312 * 'Times New Roman', 'Arial', 'Courier'.
2314 function setFontFamily($font_family)
2316 $this->_font_name = $font_family;
2321 * Class for parsing Excel formulas
2323 * @author Xavier Noguer <xnoguer@rezebra.com>
2324 * @category FileFormats
2325 * @package Spreadsheet_Excel_Writer
2328 class Spreadsheet_Excel_Writer_Parser
2331 * The index of the character we are currently looking at
2337 * The token we are working on.
2340 var $_current_token;
2343 * The formula to parse
2349 * The character ahead of the current char
2355 * The parse tree to be generated
2361 * The byte order. 1 => big endian, 0 => little endian.
2367 * Array of external sheets
2373 * Array of sheet references in the form of REF structures
2379 * The BIFF version for the workbook
2385 * The class constructor
2387 * @param integer $byte_order The byte order (Little endian or Big endian) of the architecture
2388 (optional). 1 => big endian, 0 (default) little endian.
2390 function __construct($byte_order, $biff_version)
2392 $this->_current_char = 0;
2393 $this->_BIFF_version = $biff_version;
2394 $this->_current_token = ''; // The token we are working on.
2395 $this->_formula = ''; // The formula to parse.
2396 $this->_lookahead = ''; // The character ahead of the current char.
2397 $this->_parse_tree = ''; // The parse tree to be generated.
2398 $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's
2399 $this->_byte_order = $byte_order; // Little Endian or Big Endian
2400 $this->_ext_sheets = array();
2401 $this->_references = array();
2405 * Initialize the ptg and function hashes.
2409 function _initializeHashes()
2411 // The Excel ptg indices
2420 'ptgConcat' => 0x08,
2431 'ptgUminus' => 0x13,
2432 'ptgPercent' => 0x14,
2434 'ptgMissArg' => 0x16,
2438 'ptgEndSheet' => 0x1B,
2445 'ptgFuncVar' => 0x22,
2449 'ptgMemArea' => 0x26,
2450 'ptgMemErr' => 0x27,
2451 'ptgMemNoMem' => 0x28,
2452 'ptgMemFunc' => 0x29,
2453 'ptgRefErr' => 0x2A,
2454 'ptgAreaErr' => 0x2B,
2457 'ptgMemAreaN' => 0x2E,
2458 'ptgMemNoMemN' => 0x2F,
2461 'ptgArea3d' => 0x3B,
2462 'ptgRefErr3d' => 0x3C,
2463 'ptgAreaErr3d' => 0x3D,
2464 'ptgArrayV' => 0x40,
2466 'ptgFuncVarV' => 0x42,
2470 'ptgMemAreaV' => 0x46,
2471 'ptgMemErrV' => 0x47,
2472 'ptgMemNoMemV' => 0x48,
2473 'ptgMemFuncV' => 0x49,
2474 'ptgRefErrV' => 0x4A,
2475 'ptgAreaErrV' => 0x4B,
2477 'ptgAreaNV' => 0x4D,
2478 'ptgMemAreaNV' => 0x4E,
2479 'ptgMemNoMemN' => 0x4F,
2480 'ptgFuncCEV' => 0x58,
2481 'ptgNameXV' => 0x59,
2482 'ptgRef3dV' => 0x5A,
2483 'ptgArea3dV' => 0x5B,
2484 'ptgRefErr3dV' => 0x5C,
2485 'ptgAreaErr3d' => 0x5D,
2486 'ptgArrayA' => 0x60,
2488 'ptgFuncVarA' => 0x62,
2492 'ptgMemAreaA' => 0x66,
2493 'ptgMemErrA' => 0x67,
2494 'ptgMemNoMemA' => 0x68,
2495 'ptgMemFuncA' => 0x69,
2496 'ptgRefErrA' => 0x6A,
2497 'ptgAreaErrA' => 0x6B,
2499 'ptgAreaNA' => 0x6D,
2500 'ptgMemAreaNA' => 0x6E,
2501 'ptgMemNoMemN' => 0x6F,
2502 'ptgFuncCEA' => 0x78,
2503 'ptgNameXA' => 0x79,
2504 'ptgRef3dA' => 0x7A,
2505 'ptgArea3dA' => 0x7B,
2506 'ptgRefErr3dA' => 0x7C,
2507 'ptgAreaErr3d' => 0x7D
2510 // Thanks to Michael Meeks and Gnumeric for the initial arg values.
2512 // The following hash was generated by "function_locale.pl" in the distro.
2513 // Refer to function_locale.pl for non-English function names.
2515 // The array elements are as follow:
2516 // ptg: The Excel function ptg code.
2517 // args: The number of arguments that the function takes:
2518 // >=0 is a fixed number of arguments.
2519 // -1 is a variable number of arguments.
2520 // class: The reference, value or array class of the function args.
2521 // vol: The function is volatile.
2523 $this->_functions = array(
2524 // function ptg args class vol
2525 'COUNT' => array( 0, -1, 0, 0 ),
2526 'IF' => array( 1, -1, 1, 0 ),
2527 'ISNA' => array( 2, 1, 1, 0 ),
2528 'ISERROR' => array( 3, 1, 1, 0 ),
2529 'SUM' => array( 4, -1, 0, 0 ),
2530 'AVERAGE' => array( 5, -1, 0, 0 ),
2531 'MIN' => array( 6, -1, 0, 0 ),
2532 'MAX' => array( 7, -1, 0, 0 ),
2533 'ROW' => array( 8, -1, 0, 0 ),
2534 'COLUMN' => array( 9, -1, 0, 0 ),
2535 'NA' => array( 10, 0, 0, 0 ),
2536 'NPV' => array( 11, -1, 1, 0 ),
2537 'STDEV' => array( 12, -1, 0, 0 ),
2538 'DOLLAR' => array( 13, -1, 1, 0 ),
2539 'FIXED' => array( 14, -1, 1, 0 ),
2540 'SIN' => array( 15, 1, 1, 0 ),
2541 'COS' => array( 16, 1, 1, 0 ),
2542 'TAN' => array( 17, 1, 1, 0 ),
2543 'ATAN' => array( 18, 1, 1, 0 ),
2544 'PI' => array( 19, 0, 1, 0 ),
2545 'SQRT' => array( 20, 1, 1, 0 ),
2546 'EXP' => array( 21, 1, 1, 0 ),
2547 'LN' => array( 22, 1, 1, 0 ),
2548 'LOG10' => array( 23, 1, 1, 0 ),
2549 'ABS' => array( 24, 1, 1, 0 ),
2550 'INT' => array( 25, 1, 1, 0 ),
2551 'SIGN' => array( 26, 1, 1, 0 ),
2552 'ROUND' => array( 27, 2, 1, 0 ),
2553 'LOOKUP' => array( 28, -1, 0, 0 ),
2554 'INDEX' => array( 29, -1, 0, 1 ),
2555 'REPT' => array( 30, 2, 1, 0 ),
2556 'MID' => array( 31, 3, 1, 0 ),
2557 'LEN' => array( 32, 1, 1, 0 ),
2558 'VALUE' => array( 33, 1, 1, 0 ),
2559 'TRUE' => array( 34, 0, 1, 0 ),
2560 'FALSE' => array( 35, 0, 1, 0 ),
2561 'AND' => array( 36, -1, 0, 0 ),
2562 'OR' => array( 37, -1, 0, 0 ),
2563 'NOT' => array( 38, 1, 1, 0 ),
2564 'MOD' => array( 39, 2, 1, 0 ),
2565 'DCOUNT' => array( 40, 3, 0, 0 ),
2566 'DSUM' => array( 41, 3, 0, 0 ),
2567 'DAVERAGE' => array( 42, 3, 0, 0 ),
2568 'DMIN' => array( 43, 3, 0, 0 ),
2569 'DMAX' => array( 44, 3, 0, 0 ),
2570 'DSTDEV' => array( 45, 3, 0, 0 ),
2571 'VAR' => array( 46, -1, 0, 0 ),
2572 'DVAR' => array( 47, 3, 0, 0 ),
2573 'TEXT' => array( 48, 2, 1, 0 ),
2574 'LINEST' => array( 49, -1, 0, 0 ),
2575 'TREND' => array( 50, -1, 0, 0 ),
2576 'LOGEST' => array( 51, -1, 0, 0 ),
2577 'GROWTH' => array( 52, -1, 0, 0 ),
2578 'PV' => array( 56, -1, 1, 0 ),
2579 'FV' => array( 57, -1, 1, 0 ),
2580 'NPER' => array( 58, -1, 1, 0 ),
2581 'PMT' => array( 59, -1, 1, 0 ),
2582 'RATE' => array( 60, -1, 1, 0 ),
2583 'MIRR' => array( 61, 3, 0, 0 ),
2584 'IRR' => array( 62, -1, 0, 0 ),
2585 'RAND' => array( 63, 0, 1, 1 ),
2586 'MATCH' => array( 64, -1, 0, 0 ),
2587 'DATE' => array( 65, 3, 1, 0 ),
2588 'TIME' => array( 66, 3, 1, 0 ),
2589 'DAY' => array( 67, 1, 1, 0 ),
2590 'MONTH' => array( 68, 1, 1, 0 ),
2591 'YEAR' => array( 69, 1, 1, 0 ),
2592 'WEEKDAY' => array( 70, -1, 1, 0 ),
2593 'HOUR' => array( 71, 1, 1, 0 ),
2594 'MINUTE' => array( 72, 1, 1, 0 ),
2595 'SECOND' => array( 73, 1, 1, 0 ),
2596 'NOW' => array( 74, 0, 1, 1 ),
2597 'AREAS' => array( 75, 1, 0, 1 ),
2598 'ROWS' => array( 76, 1, 0, 1 ),
2599 'COLUMNS' => array( 77, 1, 0, 1 ),
2600 'OFFSET' => array( 78, -1, 0, 1 ),
2601 'SEARCH' => array( 82, -1, 1, 0 ),
2602 'TRANSPOSE' => array( 83, 1, 1, 0 ),
2603 'TYPE' => array( 86, 1, 1, 0 ),
2604 'ATAN2' => array( 97, 2, 1, 0 ),
2605 'ASIN' => array( 98, 1, 1, 0 ),
2606 'ACOS' => array( 99, 1, 1, 0 ),
2607 'CHOOSE' => array( 100, -1, 1, 0 ),
2608 'HLOOKUP' => array( 101, -1, 0, 0 ),
2609 'VLOOKUP' => array( 102, -1, 0, 0 ),
2610 'ISREF' => array( 105, 1, 0, 0 ),
2611 'LOG' => array( 109, -1, 1, 0 ),
2612 'CHAR' => array( 111, 1, 1, 0 ),
2613 'LOWER' => array( 112, 1, 1, 0 ),
2614 'UPPER' => array( 113, 1, 1, 0 ),
2615 'PROPER' => array( 114, 1, 1, 0 ),
2616 'LEFT' => array( 115, -1, 1, 0 ),
2617 'RIGHT' => array( 116, -1, 1, 0 ),
2618 'EXACT' => array( 117, 2, 1, 0 ),
2619 'TRIM' => array( 118, 1, 1, 0 ),
2620 'REPLACE' => array( 119, 4, 1, 0 ),
2621 'SUBSTITUTE' => array( 120, -1, 1, 0 ),
2622 'CODE' => array( 121, 1, 1, 0 ),
2623 'FIND' => array( 124, -1, 1, 0 ),
2624 'CELL' => array( 125, -1, 0, 1 ),
2625 'ISERR' => array( 126, 1, 1, 0 ),
2626 'ISTEXT' => array( 127, 1, 1, 0 ),
2627 'ISNUMBER' => array( 128, 1, 1, 0 ),
2628 'ISBLANK' => array( 129, 1, 1, 0 ),
2629 'T' => array( 130, 1, 0, 0 ),
2630 'N' => array( 131, 1, 0, 0 ),
2631 'DATEVALUE' => array( 140, 1, 1, 0 ),
2632 'TIMEVALUE' => array( 141, 1, 1, 0 ),
2633 'SLN' => array( 142, 3, 1, 0 ),
2634 'SYD' => array( 143, 4, 1, 0 ),
2635 'DDB' => array( 144, -1, 1, 0 ),
2636 'INDIRECT' => array( 148, -1, 1, 1 ),
2637 'CALL' => array( 150, -1, 1, 0 ),
2638 'CLEAN' => array( 162, 1, 1, 0 ),
2639 'MDETERM' => array( 163, 1, 2, 0 ),
2640 'MINVERSE' => array( 164, 1, 2, 0 ),
2641 'MMULT' => array( 165, 2, 2, 0 ),
2642 'IPMT' => array( 167, -1, 1, 0 ),
2643 'PPMT' => array( 168, -1, 1, 0 ),
2644 'COUNTA' => array( 169, -1, 0, 0 ),
2645 'PRODUCT' => array( 183, -1, 0, 0 ),
2646 'FACT' => array( 184, 1, 1, 0 ),
2647 'DPRODUCT' => array( 189, 3, 0, 0 ),
2648 'ISNONTEXT' => array( 190, 1, 1, 0 ),
2649 'STDEVP' => array( 193, -1, 0, 0 ),
2650 'VARP' => array( 194, -1, 0, 0 ),
2651 'DSTDEVP' => array( 195, 3, 0, 0 ),
2652 'DVARP' => array( 196, 3, 0, 0 ),
2653 'TRUNC' => array( 197, -1, 1, 0 ),
2654 'ISLOGICAL' => array( 198, 1, 1, 0 ),
2655 'DCOUNTA' => array( 199, 3, 0, 0 ),
2656 'ROUNDUP' => array( 212, 2, 1, 0 ),
2657 'ROUNDDOWN' => array( 213, 2, 1, 0 ),
2658 'RANK' => array( 216, -1, 0, 0 ),
2659 'ADDRESS' => array( 219, -1, 1, 0 ),
2660 'DAYS360' => array( 220, -1, 1, 0 ),
2661 'TODAY' => array( 221, 0, 1, 1 ),
2662 'VDB' => array( 222, -1, 1, 0 ),
2663 'MEDIAN' => array( 227, -1, 0, 0 ),
2664 'SUMPRODUCT' => array( 228, -1, 2, 0 ),
2665 'SINH' => array( 229, 1, 1, 0 ),
2666 'COSH' => array( 230, 1, 1, 0 ),
2667 'TANH' => array( 231, 1, 1, 0 ),
2668 'ASINH' => array( 232, 1, 1, 0 ),
2669 'ACOSH' => array( 233, 1, 1, 0 ),
2670 'ATANH' => array( 234, 1, 1, 0 ),
2671 'DGET' => array( 235, 3, 0, 0 ),
2672 'INFO' => array( 244, 1, 1, 1 ),
2673 'DB' => array( 247, -1, 1, 0 ),
2674 'FREQUENCY' => array( 252, 2, 0, 0 ),
2675 'ERROR.TYPE' => array( 261, 1, 1, 0 ),
2676 'REGISTER.ID' => array( 267, -1, 1, 0 ),
2677 'AVEDEV' => array( 269, -1, 0, 0 ),
2678 'BETADIST' => array( 270, -1, 1, 0 ),
2679 'GAMMALN' => array( 271, 1, 1, 0 ),
2680 'BETAINV' => array( 272, -1, 1, 0 ),
2681 'BINOMDIST' => array( 273, 4, 1, 0 ),
2682 'CHIDIST' => array( 274, 2, 1, 0 ),
2683 'CHIINV' => array( 275, 2, 1, 0 ),
2684 'COMBIN' => array( 276, 2, 1, 0 ),
2685 'CONFIDENCE' => array( 277, 3, 1, 0 ),
2686 'CRITBINOM' => array( 278, 3, 1, 0 ),
2687 'EVEN' => array( 279, 1, 1, 0 ),
2688 'EXPONDIST' => array( 280, 3, 1, 0 ),
2689 'FDIST' => array( 281, 3, 1, 0 ),
2690 'FINV' => array( 282, 3, 1, 0 ),
2691 'FISHER' => array( 283, 1, 1, 0 ),
2692 'FISHERINV' => array( 284, 1, 1, 0 ),
2693 'FLOOR' => array( 285, 2, 1, 0 ),
2694 'GAMMADIST' => array( 286, 4, 1, 0 ),
2695 'GAMMAINV' => array( 287, 3, 1, 0 ),
2696 'CEILING' => array( 288, 2, 1, 0 ),
2697 'HYPGEOMDIST' => array( 289, 4, 1, 0 ),
2698 'LOGNORMDIST' => array( 290, 3, 1, 0 ),
2699 'LOGINV' => array( 291, 3, 1, 0 ),
2700 'NEGBINOMDIST' => array( 292, 3, 1, 0 ),
2701 'NORMDIST' => array( 293, 4, 1, 0 ),
2702 'NORMSDIST' => array( 294, 1, 1, 0 ),
2703 'NORMINV' => array( 295, 3, 1, 0 ),
2704 'NORMSINV' => array( 296, 1, 1, 0 ),
2705 'STANDARDIZE' => array( 297, 3, 1, 0 ),
2706 'ODD' => array( 298, 1, 1, 0 ),
2707 'PERMUT' => array( 299, 2, 1, 0 ),
2708 'POISSON' => array( 300, 3, 1, 0 ),
2709 'TDIST' => array( 301, 3, 1, 0 ),
2710 'WEIBULL' => array( 302, 4, 1, 0 ),
2711 'SUMXMY2' => array( 303, 2, 2, 0 ),
2712 'SUMX2MY2' => array( 304, 2, 2, 0 ),
2713 'SUMX2PY2' => array( 305, 2, 2, 0 ),
2714 'CHITEST' => array( 306, 2, 2, 0 ),
2715 'CORREL' => array( 307, 2, 2, 0 ),
2716 'COVAR' => array( 308, 2, 2, 0 ),
2717 'FORECAST' => array( 309, 3, 2, 0 ),
2718 'FTEST' => array( 310, 2, 2, 0 ),
2719 'INTERCEPT' => array( 311, 2, 2, 0 ),
2720 'PEARSON' => array( 312, 2, 2, 0 ),
2721 'RSQ' => array( 313, 2, 2, 0 ),
2722 'STEYX' => array( 314, 2, 2, 0 ),
2723 'SLOPE' => array( 315, 2, 2, 0 ),
2724 'TTEST' => array( 316, 4, 2, 0 ),
2725 'PROB' => array( 317, -1, 2, 0 ),
2726 'DEVSQ' => array( 318, -1, 0, 0 ),
2727 'GEOMEAN' => array( 319, -1, 0, 0 ),
2728 'HARMEAN' => array( 320, -1, 0, 0 ),
2729 'SUMSQ' => array( 321, -1, 0, 0 ),
2730 'KURT' => array( 322, -1, 0, 0 ),
2731 'SKEW' => array( 323, -1, 0, 0 ),
2732 'ZTEST' => array( 324, -1, 0, 0 ),
2733 'LARGE' => array( 325, 2, 0, 0 ),
2734 'SMALL' => array( 326, 2, 0, 0 ),
2735 'QUARTILE' => array( 327, 2, 0, 0 ),
2736 'PERCENTILE' => array( 328, 2, 0, 0 ),
2737 'PERCENTRANK' => array( 329, -1, 0, 0 ),
2738 'MODE' => array( 330, -1, 2, 0 ),
2739 'TRIMMEAN' => array( 331, 2, 0, 0 ),
2740 'TINV' => array( 332, 2, 1, 0 ),
2741 'CONCATENATE' => array( 336, -1, 1, 0 ),
2742 'POWER' => array( 337, 2, 1, 0 ),
2743 'RADIANS' => array( 342, 1, 1, 0 ),
2744 'DEGREES' => array( 343, 1, 1, 0 ),
2745 'SUBTOTAL' => array( 344, -1, 0, 0 ),
2746 'SUMIF' => array( 345, -1, 0, 0 ),
2747 'COUNTIF' => array( 346, 2, 0, 0 ),
2748 'COUNTBLANK' => array( 347, 1, 0, 0 ),
2749 'ROMAN' => array( 354, -1, 1, 0 )
2754 * Convert a token to the proper ptg value.
2757 * @param mixed $token The token to convert.
2758 * @return mixed the converted token on success. Die if the token
2761 function _convert($token)
2763 if (preg_match("/^\"[^\"]{0,255}\"$/", $token)) {
2764 return $this->_convertString($token);
2766 } elseif (is_numeric($token)) {
2767 return $this->_convertNumber($token);
2769 // match references like A1 or $A$1
2770 } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) {
2771 return $this->_convertRef2d($token);
2773 // match external references like Sheet1!A1 or Sheet1:Sheet2!A1
2774 } elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2775 return $this->_convertRef3d($token);
2777 // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1
2778 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z](\d+)$/u",$token)) {
2779 return $this->_convertRef3d($token);
2781 // match ranges like A1:B2
2782 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2783 return $this->_convertRange2d($token);
2785 // match ranges like A1..B2
2786 } elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/",$token)) {
2787 return $this->_convertRange2d($token);
2789 // match external ranges like Sheet1!A1 or Sheet1:Sheet2!A1:B2
2790 } elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2791 return $this->_convertRange3d($token);
2793 // match external ranges like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
2794 } elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?(\d+)\:([A-Ia-i]?[A-Za-z])?(\d+)$/u",$token)) {
2795 return $this->_convertRange3d($token);
2797 // operators (including parentheses)
2798 } elseif (isset($this->ptg[$token])) {
2799 return pack("C", $this->ptg[$token]);
2801 // commented so argument number can be processed correctly. See toReversePolish().
2802 /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))
2804 return($this->_convertFunction($token,$this->_func_args));
2807 // if it's an argument, ignore the token (the argument remains)
2808 } elseif ($token == 'arg') {
2811 // TODO: use real error codes
2812 die("Unknown token $token");
2816 * Convert a number token to ptgInt or ptgNum
2819 * @param mixed $num an integer or double for conversion to its ptg value
2821 function _convertNumber($num)
2823 // Integer in the range 0..2**16-1
2824 if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {
2825 return pack("Cv", $this->ptg['ptgInt'], $num);
2827 if ($this->_byte_order) { // if it's Big Endian
2828 $num = strrev($num);
2830 return pack("Cd", $this->ptg['ptgNum'], $num);
2835 * Convert a string token to ptgStr
2838 * @param string $string A string for conversion to its ptg value.
2839 * @return mixed the converted token on success. PEAR_Error if the string
2840 * is longer than 255 characters.
2842 function _convertString($string)
2844 // chop away beggining and ending quotes
2845 $string = substr($string, 1, strlen($string) - 2);
2846 if (strlen($string) > 255) {
2847 die("String is too long");
2850 if ($this->_BIFF_version == 0x0500) {
2851 return pack("CC", $this->ptg['ptgStr'], strlen($string)).$string;
2852 } elseif ($this->_BIFF_version == 0x0600) {
2853 $encoding = 0; // TODO: Unicode support
2854 return pack("CCC", $this->ptg['ptgStr'], strlen($string), $encoding).$string;
2859 * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of
2860 * args that it takes.
2863 * @param string $token The name of the function for convertion to ptg value.
2864 * @param integer $num_args The number of arguments the function receives.
2865 * @return string The packed ptg for the function
2867 function _convertFunction($token, $num_args)
2869 $args = $this->_functions[$token][1];
2870 $volatile = $this->_functions[$token][3];
2872 // Fixed number of args eg. TIME($i,$j,$k).
2874 return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);
2876 // Variable number of args eg. SUM($i,$j,$k, ..).
2878 return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);
2883 * Convert an Excel range such as A1:D4 to a ptgRefV.
2886 * @param string $range An Excel range in the A1:A2 or A1..A2 format.
2888 function _convertRange2d($range)
2890 $class = 2; // as far as I know, this is magick.
2892 // Split the range into 2 cell refs
2893 if (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\:([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2894 list($cell1, $cell2) = preg_split('/:/', $range);
2895 } elseif (preg_match("/^([A-Ia-i]?[A-Za-z])(\d+)\.\.([A-Ia-i]?[A-Za-z])(\d+)$/", $range)) {
2896 list($cell1, $cell2) = preg_split('/\.\./', $range);
2899 // TODO: use real error codes
2900 die("Unknown range separator");
2903 // Convert the cell references
2904 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2905 list($row1, $col1) = $cell_array1;
2906 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2907 list($row2, $col2) = $cell_array2;
2909 // The ptg value depends on the class of the ptg.
2911 $ptgArea = pack("C", $this->ptg['ptgArea']);
2912 } elseif ($class == 1) {
2913 $ptgArea = pack("C", $this->ptg['ptgAreaV']);
2914 } elseif ($class == 2) {
2915 $ptgArea = pack("C", $this->ptg['ptgAreaA']);
2917 // TODO: use real error codes
2918 die("Unknown class $class");
2920 return $ptgArea . $row1 . $row2 . $col1. $col2;
2924 * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to
2928 * @param string $token An Excel range in the Sheet1!A1:A2 format.
2929 * @return mixed The packed ptgArea3d token on success, PEAR_Error on failure.
2931 function _convertRange3d($token)
2933 $class = 2; // as far as I know, this is magick.
2935 // Split the ref at the ! symbol
2936 list($ext_ref, $range) = preg_split('/!/', $token);
2938 // Convert the external reference part (different for BIFF8)
2939 if ($this->_BIFF_version == 0x0500) {
2940 $ext_ref = $this->_packExtRef($ext_ref);
2941 } elseif ($this->_BIFF_version == 0x0600) {
2942 $ext_ref = $this->_getRefIndex($ext_ref);
2945 // Split the range into 2 cell refs
2946 list($cell1, $cell2) = preg_split('/:/', $range);
2948 // Convert the cell references
2949 if (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/", $cell1)) {
2950 $cell_array1 = $this->_cellToPackedRowcol($cell1);
2951 list($row1, $col1) = $cell_array1;
2952 $cell_array2 = $this->_cellToPackedRowcol($cell2);
2953 list($row2, $col2) = $cell_array2;
2954 } else { // It's a rows range (like 26:27)
2955 $cells_array = $this->_rangeToPackedRange($cell1.':'.$cell2);
2956 list($row1, $col1, $row2, $col2) = $cells_array;
2959 // The ptg value depends on the class of the ptg.
2961 $ptgArea = pack("C", $this->ptg['ptgArea3d']);
2962 } elseif ($class == 1) {
2963 $ptgArea = pack("C", $this->ptg['ptgArea3dV']);
2964 } elseif ($class == 2) {
2965 $ptgArea = pack("C", $this->ptg['ptgArea3dA']);
2967 die("Unknown class $class");
2970 return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;
2974 * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.
2977 * @param string $cell An Excel cell reference
2978 * @return string The cell in packed() format with the corresponding ptg
2980 function _convertRef2d($cell)
2982 $class = 2; // as far as I know, this is magick.
2984 // Convert the cell reference
2985 $cell_array = $this->_cellToPackedRowcol($cell);
2986 list($row, $col) = $cell_array;
2988 // The ptg value depends on the class of the ptg.
2990 $ptgRef = pack("C", $this->ptg['ptgRef']);
2991 } elseif ($class == 1) {
2992 $ptgRef = pack("C", $this->ptg['ptgRefV']);
2993 } elseif ($class == 2) {
2994 $ptgRef = pack("C", $this->ptg['ptgRefA']);
2996 // TODO: use real error codes
2997 die("Unknown class $class");
2999 return $ptgRef.$row.$col;
3003 * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a
3007 * @param string $cell An Excel cell reference
3008 * @return mixed The packed ptgRef3d token on success, PEAR_Error on failure.
3010 function _convertRef3d($cell)
3012 $class = 2; // as far as I know, this is magick.
3014 // Split the ref at the ! symbol
3015 list($ext_ref, $cell) = preg_split('/!/', $cell);
3017 // Convert the external reference part (different for BIFF8)
3018 if ($this->_BIFF_version == 0x0500) {
3019 $ext_ref = $this->_packExtRef($ext_ref);
3020 } elseif ($this->_BIFF_version == 0x0600) {
3021 $ext_ref = $this->_getRefIndex($ext_ref);
3024 // Convert the cell reference part
3025 list($row, $col) = $this->_cellToPackedRowcol($cell);
3027 // The ptg value depends on the class of the ptg.
3029 $ptgRef = pack("C", $this->ptg['ptgRef3d']);
3030 } elseif ($class == 1) {
3031 $ptgRef = pack("C", $this->ptg['ptgRef3dV']);
3032 } elseif ($class == 2) {
3033 $ptgRef = pack("C", $this->ptg['ptgRef3dA']);
3035 die("Unknown class $class");
3038 return $ptgRef . $ext_ref. $row . $col;
3042 * Convert the sheet name part of an external reference, for example "Sheet1" or
3043 * "Sheet1:Sheet2", to a packed structure.
3046 * @param string $ext_ref The name of the external reference
3047 * @return string The reference index in packed() format
3049 function _packExtRef($ext_ref)
3051 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
3052 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
3054 // Check if there is a sheet range eg., Sheet1:Sheet2.
3055 if (preg_match("/:/", $ext_ref)) {
3056 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
3058 $sheet1 = $this->_getSheetIndex($sheet_name1);
3059 if ($sheet1 == -1) {
3060 die("Unknown sheet name $sheet_name1 in formula");
3062 $sheet2 = $this->_getSheetIndex($sheet_name2);
3063 if ($sheet2 == -1) {
3064 die("Unknown sheet name $sheet_name2 in formula");
3067 // Reverse max and min sheet numbers if necessary
3068 if ($sheet1 > $sheet2) {
3069 list($sheet1, $sheet2) = array($sheet2, $sheet1);
3071 } else { // Single sheet name only.
3072 $sheet1 = $this->_getSheetIndex($ext_ref);
3073 if ($sheet1 == -1) {
3074 die("Unknown sheet name $ext_ref in formula");
3079 // References are stored relative to 0xFFFF.
3080 $offset = -1 - $sheet1;
3082 return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);
3086 * Look up the REF index that corresponds to an external sheet name
3087 * (or range). If it doesn't exist yet add it to the workbook's references
3088 * array. It assumes all sheet names given must exist.
3091 * @param string $ext_ref The name of the external reference
3092 * @return mixed The reference index in packed() format on success,
3093 * PEAR_Error on failure
3095 function _getRefIndex($ext_ref)
3097 $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.
3098 $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.
3100 // Check if there is a sheet range eg., Sheet1:Sheet2.
3101 if (preg_match("/:/", $ext_ref)) {
3102 list($sheet_name1, $sheet_name2) = preg_split('/:/', $ext_ref);
3104 $sheet1 = $this->_getSheetIndex($sheet_name1);
3105 if ($sheet1 == -1) {
3106 die("Unknown sheet name $sheet_name1 in formula");
3108 $sheet2 = $this->_getSheetIndex($sheet_name2);
3109 if ($sheet2 == -1) {
3110 die("Unknown sheet name $sheet_name2 in formula");
3113 // Reverse max and min sheet numbers if necessary
3114 if ($sheet1 > $sheet2) {
3115 list($sheet1, $sheet2) = array($sheet2, $sheet1);
3117 } else { // Single sheet name only.
3118 $sheet1 = $this->_getSheetIndex($ext_ref);
3119 if ($sheet1 == -1) {
3120 die("Unknown sheet name $ext_ref in formula");
3125 // assume all references belong to this document
3126 $supbook_index = 0x00;
3127 $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
3128 $total_references = count($this->_references);
3130 for ($i = 0; $i < $total_references; $i++) {
3131 if ($ref == $this->_references[$i]) {
3136 // if REF was not found add it to references array
3138 $this->_references[$total_references] = $ref;
3139 $index = $total_references;
3142 return pack('v', $index);
3146 * Look up the index that corresponds to an external sheet name. The hash of
3147 * sheet names is updated by the addworksheet() method of the
3148 * Spreadsheet_Excel_Writer_Workbook class.
3151 * @return integer The sheet index, -1 if the sheet was not found
3153 function _getSheetIndex($sheet_name)
3155 if (!isset($this->_ext_sheets[$sheet_name])) {
3158 return $this->_ext_sheets[$sheet_name];
3163 * This method is used to update the array of sheet names. It is
3164 * called by the addWorksheet() method of the
3165 * Spreadsheet_Excel_Writer_Workbook class.
3168 * @see Spreadsheet_Excel_Writer_Workbook::addWorksheet()
3169 * @param string $name The name of the worksheet being added
3170 * @param integer $index The index of the worksheet being added
3172 function setExtSheet($name, $index)
3174 $this->_ext_sheets[$name] = $index;
3178 * pack() row and column into the required 3 or 4 byte format.
3181 * @param string $cell The Excel cell reference to be packed
3182 * @return array Array containing the row and column in packed() format
3184 function _cellToPackedRowcol($cell)
3186 $cell = strtoupper($cell);
3187 list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);
3189 die("Column in: $cell greater than 255");
3191 // FIXME: change for BIFF8
3192 if ($row >= 16384) {
3193 die("Row in: $cell greater than 16384 ");
3196 // Set the high bits to indicate if row or col are relative.
3197 if ($this->_BIFF_version == 0x0500) {
3198 $row |= $col_rel << 14;
3199 $row |= $row_rel << 15;
3200 $col = pack('C', $col);
3201 } elseif ($this->_BIFF_version == 0x0600) {
3202 $col |= $col_rel << 14;
3203 $col |= $row_rel << 15;
3204 $col = pack('v', $col);
3206 $row = pack('v', $row);
3208 return array($row, $col);
3212 * pack() row range into the required 3 or 4 byte format.
3213 * Just using maximum col/rows, which is probably not the correct solution
3216 * @param string $range The Excel range to be packed
3217 * @return array Array containing (row1,col1,row2,col2) in packed() format
3219 function _rangeToPackedRange($range)
3221 preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);
3222 // return absolute rows if there is a $ in the ref
3223 $row1_rel = empty($match[1]) ? 1 : 0;
3225 $row2_rel = empty($match[3]) ? 1 : 0;
3227 // Convert 1-index to zero-index
3230 // Trick poor inocent Excel
3232 $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!)
3234 // FIXME: this changes for BIFF8
3235 if (($row1 >= 16384) or ($row2 >= 16384)) {
3236 die("Row in: $range greater than 16384 ");
3239 // Set the high bits to indicate if rows are relative.
3240 if ($this->_BIFF_version == 0x0500) {
3241 $row1 |= $row1_rel << 14; // FIXME: probably a bug
3242 $row2 |= $row2_rel << 15;
3243 $col1 = pack('C', $col1);
3244 $col2 = pack('C', $col2);
3245 } elseif ($this->_BIFF_version == 0x0600) {
3246 $col1 |= $row1_rel << 15;
3247 $col2 |= $row2_rel << 15;
3248 $col1 = pack('v', $col1);
3249 $col2 = pack('v', $col2);
3251 $row1 = pack('v', $row1);
3252 $row2 = pack('v', $row2);
3254 return array($row1, $col1, $row2, $col2);
3258 * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero
3259 * indexed row and column number. Also returns two (0,1) values to indicate
3260 * whether the row or column are relative references.
3263 * @param string $cell The Excel cell reference in A1 format.
3266 function _cellToRowcol($cell)
3268 preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);
3269 // return absolute column if there is a $ in the ref
3270 $col_rel = empty($match[1]) ? 1 : 0;
3271 $col_ref = $match[2];
3272 $row_rel = empty($match[3]) ? 1 : 0;
3275 // Convert base26 column string to a number.
3276 $expn = strlen($col_ref) - 1;
3278 $col_ref_length = strlen($col_ref);
3279 for ($i = 0; $i < $col_ref_length; $i++) {
3280 $col += (ord($col_ref[$i]) - ord('A') + 1) * pow(26, $expn);
3284 // Convert 1-index to zero-index
3288 return array($row, $col, $row_rel, $col_rel);
3292 * Advance to the next valid token.
3298 $i = $this->_current_char;
3299 $formula_length = strlen($this->_formula);
3300 // eat up white spaces
3301 if ($i < $formula_length) {
3302 while ($this->_formula[$i] == " ") {
3306 if ($i < ($formula_length - 1)) {
3307 $this->_lookahead = $this->_formula[$i+1];
3312 while ($i < $formula_length) {
3313 $token .= $this->_formula[$i];
3314 if ($i < ($formula_length - 1)) {
3315 $this->_lookahead = $this->_formula[$i+1];
3317 $this->_lookahead = '';
3320 if ($this->_match($token) != '') {
3321 //if ($i < strlen($this->_formula) - 1) {
3322 // $this->_lookahead = $this->_formula{$i+1};
3324 $this->_current_char = $i + 1;
3325 $this->_current_token = $token;
3329 if ($i < ($formula_length - 2)) {
3330 $this->_lookahead = $this->_formula[$i+2];
3331 } else { // if we run out of characters _lookahead becomes empty
3332 $this->_lookahead = '';
3336 //die("Lexical error ".$this->_current_char);
3340 * Checks if it's a valid token.
3343 * @param mixed $token The token to check.
3344 * @return mixed The checked token or false on failure
3346 function _match($token)
3349 case SPREADSHEET_EXCEL_WRITER_ADD:
3352 case SPREADSHEET_EXCEL_WRITER_SUB:
3355 case SPREADSHEET_EXCEL_WRITER_MUL:
3358 case SPREADSHEET_EXCEL_WRITER_DIV:
3361 case SPREADSHEET_EXCEL_WRITER_OPEN:
3364 case SPREADSHEET_EXCEL_WRITER_CLOSE:
3367 case SPREADSHEET_EXCEL_WRITER_COMA:
3370 case SPREADSHEET_EXCEL_WRITER_SEMICOLON:
3373 case SPREADSHEET_EXCEL_WRITER_GT:
3374 if ($this->_lookahead == '=') { // it's a GE token
3379 case SPREADSHEET_EXCEL_WRITER_LT:
3380 // it's a LE or a NE token
3381 if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {
3386 case SPREADSHEET_EXCEL_WRITER_GE:
3389 case SPREADSHEET_EXCEL_WRITER_LE:
3392 case SPREADSHEET_EXCEL_WRITER_EQ:
3395 case SPREADSHEET_EXCEL_WRITER_NE:
3399 // if it's a reference
3400 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and
3401 !preg_match("/[0-9]/",$this->_lookahead) and
3402 ($this->_lookahead != ':') and ($this->_lookahead != '.') and
3403 ($this->_lookahead != '!'))
3407 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
3408 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
3409 !preg_match("/[0-9]/",$this->_lookahead) and
3410 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3414 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3415 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$token) and
3416 !preg_match("/[0-9]/",$this->_lookahead) and
3417 ($this->_lookahead != ':') and ($this->_lookahead != '.'))
3421 // if it's a range (A1:A2)
3422 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3423 !preg_match("/[0-9]/",$this->_lookahead))
3427 // if it's a range (A1..A2)
3428 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$token) and
3429 !preg_match("/[0-9]/",$this->_lookahead))
3433 // If it's an external range like Sheet1!A1 or Sheet1:Sheet2!A1:B2
3434 elseif (preg_match("/^\w+(\:\w+)?\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3435 !preg_match("/[0-9]/",$this->_lookahead))
3439 // If it's an external range like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1:B2
3440 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\!([A-Ia-i]?[A-Za-z])?[0-9]+:([A-Ia-i]?[A-Za-z])?[0-9]+$/u",$token) and
3441 !preg_match("/[0-9]/",$this->_lookahead))
3445 // If it's a number (check that it's not a sheet name or range)
3446 elseif (is_numeric($token) and
3447 (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and
3448 ($this->_lookahead != '!') and ($this->_lookahead != ':'))
3452 // If it's a string (of maximum 255 characters)
3453 elseif (preg_match("/^\"[^\"]{0,255}\"$/",$token))
3457 // if it's a function call
3458 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))
3467 * The parsing method. It parses a formula.
3470 * @param string $formula The formula to parse, without the initial equal
3472 * @return mixed true on success, PEAR_Error on failure
3474 function parse($formula)
3476 $this->_current_char = 0;
3477 $this->_formula = $formula;
3478 $this->_lookahead = $formula[1];
3480 $this->_parse_tree = $this->_condition();
3484 * It parses a condition. It assumes the following rule:
3485 * Cond -> Expr [(">" | "<") Expr]
3488 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3490 function _condition()
3492 $result = $this->_expression();
3493 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LT) {
3495 $result2 = $this->_expression();
3496 $result = $this->_createTree('ptgLT', $result, $result2);
3497 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GT) {
3499 $result2 = $this->_expression();
3500 $result = $this->_createTree('ptgGT', $result, $result2);
3501 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_LE) {
3503 $result2 = $this->_expression();
3504 $result = $this->_createTree('ptgLE', $result, $result2);
3505 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_GE) {
3507 $result2 = $this->_expression();
3508 $result = $this->_createTree('ptgGE', $result, $result2);
3509 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_EQ) {
3511 $result2 = $this->_expression();
3512 $result = $this->_createTree('ptgEQ', $result, $result2);
3513 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_NE) {
3515 $result2 = $this->_expression();
3516 $result = $this->_createTree('ptgNE', $result, $result2);
3522 * It parses a expression. It assumes the following rule:
3523 * Expr -> Term [("+" | "-") Term]
3528 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3530 function _expression()
3532 // If it's a string return a string node
3533 if (preg_match("/^\"[^\"]{0,255}\"$/", $this->_current_token)) {
3534 $result = $this->_createTree($this->_current_token, '', '');
3537 } elseif ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB) {
3540 $result2 = $this->_expression();
3541 $result = $this->_createTree('ptgUminus', $result2, '');
3544 $result = $this->_term();
3545 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) or
3546 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_SUB)) {
3548 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_ADD) {
3550 $result2 = $this->_term();
3551 $result = $this->_createTree('ptgAdd', $result, $result2);
3554 $result2 = $this->_term();
3555 $result = $this->_createTree('ptgSub', $result, $result2);
3562 * This function just introduces a ptgParen element in the tree, so that Excel
3563 * doesn't get confused when working with a parenthesized formula afterwards.
3567 * @return array The parsed ptg'd tree
3569 function _parenthesizedExpression()
3571 $result = $this->_createTree('ptgParen', $this->_expression(), '');
3576 * It parses a term. It assumes the following rule:
3577 * Term -> Fact [("*" | "/") Fact]
3580 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3584 $result = $this->_fact();
3585 while (($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) or
3586 ($this->_current_token == SPREADSHEET_EXCEL_WRITER_DIV)) {
3588 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_MUL) {
3590 $result2 = $this->_fact();
3591 $result = $this->_createTree('ptgMul', $result, $result2);
3594 $result2 = $this->_fact();
3595 $result = $this->_createTree('ptgDiv', $result, $result2);
3602 * It parses a factor. It assumes the following rule:
3610 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3614 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_OPEN) {
3615 $this->_advance(); // eat the "("
3616 $result = $this->_parenthesizedExpression();
3617 if ($this->_current_token != SPREADSHEET_EXCEL_WRITER_CLOSE) {
3618 die("')' token expected.");
3620 $this->_advance(); // eat the ")"
3623 // if it's a reference
3624 if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))
3626 $result = $this->_createTree($this->_current_token, '', '');
3630 // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1)
3631 elseif (preg_match("/^\w+(\:\w+)?\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3633 $result = $this->_createTree($this->_current_token, '', '');
3637 // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1)
3638 elseif (preg_match("/^'[\w -]+(\:[\w -]+)?'\![A-Ia-i]?[A-Za-z][0-9]+$/u",$this->_current_token))
3640 $result = $this->_createTree($this->_current_token, '', '');
3645 elseif (preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token) or
3646 preg_match("/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/",$this->_current_token))
3648 $result = $this->_current_token;
3652 // If it's an external range (Sheet1!A1 or Sheet1!A1:B2)
3653 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))
3655 $result = $this->_current_token;
3659 // If it's an external range ('Sheet1'!A1 or 'Sheet1'!A1:B2)
3660 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))
3662 $result = $this->_current_token;
3666 elseif (is_numeric($this->_current_token))
3668 $result = $this->_createTree($this->_current_token, '', '');
3672 // if it's a function call
3673 elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))
3675 $result = $this->_func();
3678 die("Syntax error: ".$this->_current_token.
3679 ", lookahead: ".$this->_lookahead.
3680 ", current char: ".$this->_current_char);
3684 * It parses a function call. It assumes the following rule:
3685 * Func -> ( Expr [,Expr]* )
3688 * @return mixed The parsed ptg'd tree on success, PEAR_Error on failure
3692 $num_args = 0; // number of arguments received
3693 $function = strtoupper($this->_current_token);
3694 $result = ''; // initialize result
3696 $this->_advance(); // eat the "("
3697 while ($this->_current_token != ')') {
3699 if ($num_args > 0) {
3700 if ($this->_current_token == SPREADSHEET_EXCEL_WRITER_COMA or
3701 $this->_current_token == SPREADSHEET_EXCEL_WRITER_SEMICOLON)
3703 $this->_advance(); // eat the "," or ";"
3705 die("Syntax error: comma expected in ".
3706 "function $function, arg #{$num_args}");
3708 $result2 = $this->_condition();
3709 $result = $this->_createTree('arg', $result, $result2);
3710 } else { // first argument
3711 $result2 = $this->_condition();
3712 $result = $this->_createTree('arg', '', $result2);
3716 if (!isset($this->_functions[$function])) {
3717 die("Function $function() doesn't exist");
3719 $args = $this->_functions[$function][1];
3720 // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.
3721 if (($args >= 0) and ($args != $num_args)) {
3722 die("Incorrect number of arguments in function $function() ");
3725 $result = $this->_createTree($function, $result, $num_args);
3726 $this->_advance(); // eat the ")"
3731 * Creates a tree. In fact an array which may have one or two arrays (sub-trees)
3735 * @param mixed $value The value of this node.
3736 * @param mixed $left The left array (sub-tree) or a final node.
3737 * @param mixed $right The right array (sub-tree) or a final node.
3738 * @return array A tree
3740 function _createTree($value, $left, $right)
3742 return array('value' => $value, 'left' => $left, 'right' => $right);
3746 * Builds a string containing the tree in reverse polish notation (What you
3747 * would use in a HP calculator stack).
3748 * The following tree:
3756 * The following tree:
3764 * produces: "36A1*+"
3766 * In fact all operands, functions, references, etc... are written as ptg's
3769 * @param array $tree The optional tree to convert.
3770 * @return string The tree in reverse polish notation
3772 function toReversePolish($tree = array())
3774 $polish = ""; // the string we are going to return
3775 if (empty($tree)) { // If it's the first call use _parse_tree
3776 $tree = $this->_parse_tree;
3778 if (is_array($tree['left'])) {
3779 $converted_tree = $this->toReversePolish($tree['left']);
3780 $polish .= $converted_tree;
3781 } elseif ($tree['left'] != '') { // It's a final node
3782 $converted_tree = $this->_convert($tree['left']);
3783 $polish .= $converted_tree;
3785 if (is_array($tree['right'])) {
3786 $converted_tree = $this->toReversePolish($tree['right']);
3787 $polish .= $converted_tree;
3788 } elseif ($tree['right'] != '') { // It's a final node
3789 $converted_tree = $this->_convert($tree['right']);
3790 $polish .= $converted_tree;
3792 // if it's a function convert it here (so we can set it's arguments)
3793 if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and
3794 !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and
3795 !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and
3796 !is_numeric($tree['value']) and
3797 !isset($this->ptg[$tree['value']]))
3799 // left subtree for a function is always an array.
3800 if ($tree['left'] != '') {
3801 $left_tree = $this->toReversePolish($tree['left']);
3805 // add it's left subtree and return.
3806 return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);
3808 $converted_tree = $this->_convert($tree['value']);
3810 $polish .= $converted_tree;
3816 * Class for generating Excel Spreadsheets
3818 * @author Xavier Noguer <xnoguer@rezebra.com>
3819 * @category FileFormats
3820 * @package Spreadsheet_Excel_Writer
3823 class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter
3826 * Name of the Worksheet
3832 * Index for the Worksheet
3838 * Reference to the (default) Format object for URLs
3839 * @var object Format
3844 * Reference to the parser used for parsing formulas
3845 * @var object Format
3850 * Filehandle to the temporary file for storing data
3856 * Boolean indicating if we are using a temporary file for storing data
3859 var $_using_tmpfile;
3862 * Maximum number of rows for an Excel spreadsheet (BIFF5)
3868 * Maximum number of columns for an Excel spreadsheet (BIFF5)
3874 * Maximum number of characters for a string (LABEL record in BIFF5)
3880 * First row for the DIMENSIONS record
3882 * @see _storeDimensions()
3887 * Last row for the DIMENSIONS record
3889 * @see _storeDimensions()
3894 * First column for the DIMENSIONS record
3896 * @see _storeDimensions()
3901 * Last column for the DIMENSIONS record
3903 * @see _storeDimensions()
3908 * Array containing format information for columns
3914 * Array containing the selected area for the worksheet
3920 * Array containing the panes for the worksheet
3926 * The active pane for the worksheet
3932 * Bit specifying if panes are frozen
3938 * Bit specifying if the worksheet is selected
3944 * The paper size (for printing) (DOCUMENT!!!)
3950 * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait
3956 * The page header caption
3962 * The page footer caption
3968 * The horizontal centering value for the page
3974 * The vertical centering value for the page
3980 * The margin for the header
3986 * The margin for the footer
3992 * The left margin for the worksheet in inches
3998 * The right margin for the worksheet in inches
4004 * The top margin for the worksheet in inches
4010 * The bottom margin for the worksheet in inches
4013 var $_margin_bottom;
4016 * First row to reapeat on each printed page
4022 * Last row to reapeat on each printed page
4028 * First column to reapeat on each printed page
4034 * First row of the area to print
4040 * Last row to of the area to print
4046 * First column of the area to print
4052 * Last column of the area to print
4058 * Whether to use outline.
4064 * Auto outline styles.
4067 var $_outline_style;
4070 * Whether to have outline summary below.
4073 var $_outline_below;
4076 * Whether to have outline summary at the right.
4079 var $_outline_right;
4082 * Outline row level.
4085 var $_outline_row_level;
4088 * Whether to fit to page when printing or not.
4094 * Number of pages to fit wide
4100 * Number of pages to fit high
4106 * Reference to the total number of strings in the workbook
4112 * Reference to the number of unique strings in the workbook
4118 * Reference to the array containing all the unique strings in the workbook
4124 * Merged cell ranges
4127 var $_merged_ranges;
4130 * Charset encoding currently used when calling writeString()
4133 var $_input_encoding;
4138 * @param string $name The name of the new worksheet
4139 * @param integer $index The index of the new worksheet
4140 * @param mixed &$activesheet The current activesheet of the workbook we belong to
4141 * @param mixed &$firstsheet The first worksheet in the workbook we belong to
4142 * @param mixed &$url_format The default format for hyperlinks
4143 * @param mixed &$parser The formula parser created for the Workbook
4146 function __construct($BIFF_version, $name,
4147 $index, &$activesheet,
4148 &$firstsheet, &$str_total,
4149 &$str_unique, &$str_table,
4150 &$url_format, &$parser)
4152 // It needs to call its parent's constructor explicitly
4153 parent::__construct();
4154 $this->_BIFF_version = $BIFF_version;
4155 $rowmax = 65536; // 16384 in Excel 5
4158 $this->name = $name;
4159 $this->index = $index;
4160 $this->activesheet = &$activesheet;
4161 $this->firstsheet = &$firstsheet;
4162 $this->_str_total = &$str_total;
4163 $this->_str_unique = &$str_unique;
4164 $this->_str_table = &$str_table;
4165 $this->_url_format = &$url_format;
4166 $this->_parser = &$parser;
4168 //$this->ext_sheets = array();
4169 $this->_filehandle = '';
4170 $this->_using_tmpfile = true;
4171 //$this->fileclosed = 0;
4172 //$this->offset = 0;
4173 $this->_xls_rowmax = $rowmax;
4174 $this->_xls_colmax = $colmax;
4175 $this->_xls_strmax = 255;
4176 $this->_dim_rowmin = $rowmax + 1;
4177 $this->_dim_rowmax = 0;
4178 $this->_dim_colmin = $colmax + 1;
4179 $this->_dim_colmax = 0;
4180 $this->_colinfo = array();
4181 $this->_selection = array(0,0,0,0);
4182 $this->_panes = array();
4183 $this->_active_pane = 3;
4185 $this->selected = 0;
4187 $this->_paper_size = 0x0;
4188 $this->_orientation = 0x1;
4189 $this->_header = '';
4190 $this->_footer = '';
4191 $this->_hcenter = 0;
4192 $this->_vcenter = 0;
4193 $this->_margin_head = 0.50;
4194 $this->_margin_foot = 0.50;
4195 $this->_margin_left = 0.75;
4196 $this->_margin_right = 0.75;
4197 $this->_margin_top = 1.00;
4198 $this->_margin_bottom = 1.00;
4200 $this->title_rowmin = null;
4201 $this->title_rowmax = null;
4202 $this->title_colmin = null;
4203 $this->title_colmax = null;
4204 $this->print_rowmin = null;
4205 $this->print_rowmax = null;
4206 $this->print_colmin = null;
4207 $this->print_colmax = null;
4209 $this->_print_gridlines = 1;
4210 $this->_screen_gridlines = 1;
4211 $this->_print_headers = 0;
4213 $this->_fit_page = 0;
4214 $this->_fit_width = 0;
4215 $this->_fit_height = 0;
4217 $this->_hbreaks = array();
4218 $this->_vbreaks = array();
4220 $this->_protect = 0;
4221 $this->_password = null;
4223 $this->col_sizes = array();
4224 $this->_row_sizes = array();
4227 $this->_print_scale = 100;
4229 $this->_outline_row_level = 0;
4230 $this->_outline_style = 0;
4231 $this->_outline_below = 1;
4232 $this->_outline_right = 1;
4233 $this->_outline_on = 1;
4235 $this->_merged_ranges = array();
4237 $this->_rtl = 0; // Added by Joe Hunt 2009-03-05 for arabic languages
4238 $this->_input_encoding = '';
4240 $this->_dv = array();
4242 $this->_initialize();
4246 * Open a tmp file to store the majority of the Worksheet data. If this fails,
4247 * for example due to write permissions, store the data in memory. This can be
4248 * slow for large files.
4252 function _initialize()
4254 // Open tmp file for storing Worksheet data
4258 $this->_filehandle = $fh;
4260 // If tmpfile() fails store data in memory
4261 $this->_using_tmpfile = false;
4266 * Add data to the beginning of the workbook (note the reverse order)
4267 * and to the end of the workbook.
4270 * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
4271 * @param array $sheetnames The array of sheetnames from the Workbook this
4272 * worksheet belongs to
4274 function close($sheetnames)
4276 $num_sheets = count($sheetnames);
4278 /***********************************************
4279 * Prepend in reverse order!!
4282 // Prepend the sheet dimensions
4283 $this->_storeDimensions();
4285 // Prepend the sheet password
4286 $this->_storePassword();
4288 // Prepend the sheet protection
4289 $this->_storeProtect();
4291 // Prepend the page setup
4292 $this->_storeSetup();
4294 /* FIXME: margins are actually appended */
4295 // Prepend the bottom margin
4296 $this->_storeMarginBottom();
4298 // Prepend the top margin
4299 $this->_storeMarginTop();
4301 // Prepend the right margin
4302 $this->_storeMarginRight();
4304 // Prepend the left margin
4305 $this->_storeMarginLeft();
4307 // Prepend the page vertical centering
4308 $this->_storeVcenter();
4310 // Prepend the page horizontal centering
4311 $this->_storeHcenter();
4313 // Prepend the page footer
4314 $this->_storeFooter();
4316 // Prepend the page header
4317 $this->_storeHeader();
4319 // Prepend the vertical page breaks
4320 $this->_storeVbreak();
4322 // Prepend the horizontal page breaks
4323 $this->_storeHbreak();
4326 $this->_storeWsbool();
4329 $this->_storeGridset();
4332 if ($this->_BIFF_version == 0x0500) {
4333 $this->_storeGuts();
4336 // Prepend PRINTGRIDLINES
4337 $this->_storePrintGridlines();
4339 // Prepend PRINTHEADERS
4340 $this->_storePrintHeaders();
4342 // Prepend EXTERNSHEET references
4343 if ($this->_BIFF_version == 0x0500) {
4344 for ($i = $num_sheets; $i > 0; $i--) {
4345 $sheetname = $sheetnames[$i-1];
4346 $this->_storeExternsheet($sheetname);
4350 // Prepend the EXTERNCOUNT of external references.
4351 if ($this->_BIFF_version == 0x0500) {
4352 $this->_storeExterncount($num_sheets);
4355 // Prepend the COLINFO records if they exist
4356 if (!empty($this->_colinfo)) {
4357 $colcount = count($this->_colinfo);
4358 for ($i = 0; $i < $colcount; $i++) {
4359 $this->_storeColinfo($this->_colinfo[$i]);
4361 $this->_storeDefcol();
4364 // Prepend the BOF record
4365 $this->_storeBof(0x0010);
4368 * End of prepend. Read upwards from here.
4369 ***********************************************/
4372 $this->_storeWindow2();
4373 $this->_storeZoom();
4374 if (!empty($this->_panes)) {
4375 $this->_storePanes($this->_panes);
4377 $this->_storeSelection($this->_selection);
4378 $this->_storeMergedCells();
4379 /* TODO: add data validity */
4380 /*if ($this->_BIFF_version == 0x0600) {
4381 $this->_storeDataValidity();
4387 * Retrieve the worksheet name.
4388 * This is usefull when creating worksheets without a name.
4391 * @return string The worksheet's name
4399 * Retrieves data from memory in one chunk, or from disk in $buffer
4402 * @return string The data
4408 // Return data stored in memory
4409 if (isset($this->_data)) {
4410 $tmp = $this->_data;
4411 unset($this->_data);
4412 $fh = $this->_filehandle;
4413 if ($this->_using_tmpfile) {
4418 // Return data stored on disk
4419 if ($this->_using_tmpfile) {
4420 if ($tmp = fread($this->_filehandle, $buffer)) {
4425 // No data to return
4430 * Sets a merged cell range
4433 * @param integer $first_row First row of the area to merge
4434 * @param integer $first_col First column of the area to merge
4435 * @param integer $last_row Last row of the area to merge
4436 * @param integer $last_col Last column of the area to merge
4438 function setMerge($first_row, $first_col, $last_row, $last_col)
4440 if (($last_row < $first_row) || ($last_col < $first_col)) {
4443 // don't check rowmin, rowmax, etc... because we don't know when this
4444 // is going to be called
4445 $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
4449 * Set this worksheet as a selected worksheet,
4450 * i.e. the worksheet has its tab highlighted.
4456 $this->selected = 1;
4460 * Set this worksheet as the active worksheet,
4461 * i.e. the worksheet that is displayed when the workbook is opened.
4462 * Also set it as selected.
4468 $this->selected = 1;
4469 $this->activesheet = $this->index;
4473 * Set this worksheet as the first visible sheet.
4474 * This is necessary when there are a large number of worksheets and the
4475 * activated worksheet is not visible on the screen.
4479 function setFirstSheet()
4481 $this->firstsheet = $this->index;
4485 * Set the worksheet protection flag
4486 * to prevent accidental modification and to
4487 * hide formulas if the locked and hidden format properties have been set.
4490 * @param string $password The password to use for protecting the sheet.
4492 function protect($password)
4494 $this->_protect = 1;
4495 $this->_password = $this->_encodePassword($password);
4499 * Set the width of a single column or a range of columns.
4502 * @param integer $firstcol first column on the range
4503 * @param integer $lastcol last column on the range
4504 * @param integer $width width to set
4505 * @param mixed $format The optional XF format to apply to the columns
4506 * @param integer $hidden The optional hidden atribute
4507 * @param integer $level The optional outline level
4509 function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
4511 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
4513 // Set width to zero if column is hidden
4514 $width = ($hidden) ? 0 : $width;
4516 for ($col = $firstcol; $col <= $lastcol; $col++) {
4517 $this->col_sizes[$col] = $width;
4522 * Set which cell or cells are selected in a worksheet
4525 * @param integer $first_row first row in the selected quadrant
4526 * @param integer $first_column first column in the selected quadrant
4527 * @param integer $last_row last row in the selected quadrant
4528 * @param integer $last_column last column in the selected quadrant
4530 function setSelection($first_row,$first_column,$last_row,$last_column)
4532 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
4536 * Set panes and mark them as frozen.
4539 * @param array $panes This is the only parameter received and is composed of the following:
4540 * 0 => Vertical split position,
4541 * 1 => Horizontal split position
4542 * 2 => Top row visible
4543 * 3 => Leftmost column visible
4546 function freezePanes($panes)
4549 $this->_panes = $panes;
4553 * Set panes and mark them as unfrozen.
4556 * @param array $panes This is the only parameter received and is composed of the following:
4557 * 0 => Vertical split position,
4558 * 1 => Horizontal split position
4559 * 2 => Top row visible
4560 * 3 => Leftmost column visible
4563 function thawPanes($panes)
4566 $this->_panes = $panes;
4570 * Set the page orientation as portrait.
4574 function setPortrait()
4576 $this->_orientation = 1;
4580 * Set the page orientation as landscape.
4584 function setLandscape()
4586 $this->_orientation = 0;
4590 * Set the paper type. Ex. 1 = US Letter, 9 = A4
4593 * @param integer $size The type of paper size to use
4595 function setPaper($size = 0)
4597 $this->_paper_size = $size;
4602 * Set the page header caption and optional margin.
4605 * @param string $string The header text
4606 * @param float $margin optional head margin in inches.
4608 function setHeader($string,$margin = 0.50)
4610 if (strlen($string) >= 255) {
4611 //carp 'Header string must be less than 255 characters';
4614 $this->_header = $string;
4615 $this->_margin_head = $margin;
4619 * Set the page footer caption and optional margin.
4622 * @param string $string The footer text
4623 * @param float $margin optional foot margin in inches.
4625 function setFooter($string,$margin = 0.50)
4627 if (strlen($string) >= 255) {
4628 //carp 'Footer string must be less than 255 characters';
4631 $this->_footer = $string;
4632 $this->_margin_foot = $margin;
4636 * Center the page horinzontally.
4639 * @param integer $center the optional value for centering. Defaults to 1 (center).
4641 function centerHorizontally($center = 1)
4643 $this->_hcenter = $center;
4647 * Center the page vertically.
4650 * @param integer $center the optional value for centering. Defaults to 1 (center).
4652 function centerVertically($center = 1)
4654 $this->_vcenter = $center;
4658 * Set all the page margins to the same value in inches.
4661 * @param float $margin The margin to set in inches
4663 function setMargins($margin)
4665 $this->setMarginLeft($margin);
4666 $this->setMarginRight($margin);
4667 $this->setMarginTop($margin);
4668 $this->setMarginBottom($margin);
4672 * Set the left and right margins to the same value in inches.
4675 * @param float $margin The margin to set in inches
4677 function setMargins_LR($margin)
4679 $this->setMarginLeft($margin);
4680 $this->setMarginRight($margin);
4684 * Set the top and bottom margins to the same value in inches.
4687 * @param float $margin The margin to set in inches
4689 function setMargins_TB($margin)
4691 $this->setMarginTop($margin);
4692 $this->setMarginBottom($margin);
4696 * Set the left margin in inches.
4699 * @param float $margin The margin to set in inches
4701 function setMarginLeft($margin = 0.75)
4703 $this->_margin_left = $margin;
4707 * Set the right margin in inches.
4710 * @param float $margin The margin to set in inches
4712 function setMarginRight($margin = 0.75)
4714 $this->_margin_right = $margin;
4718 * Set the top margin in inches.
4721 * @param float $margin The margin to set in inches
4723 function setMarginTop($margin = 1.00)
4725 $this->_margin_top = $margin;
4729 * Set the bottom margin in inches.
4732 * @param float $margin The margin to set in inches
4734 function setMarginBottom($margin = 1.00)
4736 $this->_margin_bottom = $margin;
4740 * Set the rows to repeat at the top of each printed page.
4743 * @param integer $first_row First row to repeat
4744 * @param integer $last_row Last row to repeat. Optional.
4746 function repeatRows($first_row, $last_row = null)
4748 $this->title_rowmin = $first_row;
4749 if (isset($last_row)) { //Second row is optional
4750 $this->title_rowmax = $last_row;
4752 $this->title_rowmax = $first_row;
4757 * Set the columns to repeat at the left hand side of each printed page.
4760 * @param integer $first_col First column to repeat
4761 * @param integer $last_col Last column to repeat. Optional.
4763 function repeatColumns($first_col, $last_col = null)
4765 $this->title_colmin = $first_col;
4766 if (isset($last_col)) { // Second col is optional
4767 $this->title_colmax = $last_col;
4769 $this->title_colmax = $first_col;
4774 * Set the area of each worksheet that will be printed.
4777 * @param integer $first_row First row of the area to print
4778 * @param integer $first_col First column of the area to print
4779 * @param integer $last_row Last row of the area to print
4780 * @param integer $last_col Last column of the area to print
4782 function printArea($first_row, $first_col, $last_row, $last_col)
4784 $this->print_rowmin = $first_row;
4785 $this->print_colmin = $first_col;
4786 $this->print_rowmax = $last_row;
4787 $this->print_colmax = $last_col;
4792 * Set the option to hide gridlines on the printed page.
4796 function hideGridlines()
4798 $this->_print_gridlines = 0;
4802 * Set the option to hide gridlines on the worksheet (as seen on the screen).
4806 function hideScreenGridlines()
4808 $this->_screen_gridlines = 0;
4812 * Set the option to print the row and column headers on the printed page.
4815 * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
4817 function printRowColHeaders($print = 1)
4819 $this->_print_headers = $print;
4823 * Set the vertical and horizontal number of pages that will define the maximum area printed.
4824 * It doesn't seem to work with OpenOffice.
4827 * @param integer $width Maximun width of printed area in pages
4828 * @param integer $height Maximun heigth of printed area in pages
4829 * @see setPrintScale()
4831 function fitToPages($width, $height)
4833 $this->_fit_page = 1;
4834 $this->_fit_width = $width;
4835 $this->_fit_height = $height;
4839 * Store the horizontal page breaks on a worksheet (for printing).
4840 * The breaks represent the row after which the break is inserted.
4843 * @param array $breaks Array containing the horizontal page breaks
4845 function setHPagebreaks($breaks)
4847 foreach ($breaks as $break) {
4848 array_push($this->_hbreaks, $break);
4853 * Store the vertical page breaks on a worksheet (for printing).
4854 * The breaks represent the column after which the break is inserted.
4857 * @param array $breaks Array containing the vertical page breaks
4859 function setVPagebreaks($breaks)
4861 foreach ($breaks as $break) {
4862 array_push($this->_vbreaks, $break);
4868 * Set the worksheet zoom factor.
4871 * @param integer $scale The zoom factor
4873 function setZoom($scale = 100)
4875 // Confine the scale to Excel's range
4876 if ($scale < 10 || $scale > 400) {
4880 $this->_zoom = floor($scale);
4884 * Set the scale factor for the printed page.
4885 * It turns off the "fit to page" option
4888 * @param integer $scale The optional scale factor. Defaults to 100
4890 function setPrintScale($scale = 100)
4892 // Confine the scale to Excel's range
4893 if ($scale < 10 || $scale > 400) {
4897 // Turn off "fit to page" option
4898 $this->_fit_page = 0;
4900 $this->_print_scale = floor($scale);
4904 * Map to the appropriate write method acording to the token recieved.
4907 * @param integer $row The row of the cell we are writing to
4908 * @param integer $col The column of the cell we are writing to
4909 * @param mixed $token What we are writing
4910 * @param mixed $format The optional format to apply to the cell
4912 function write($row, $col, $token, $format = null)
4914 // Check for a cell reference in A1 notation and substitute row and column
4915 /*if ($_[0] =~ /^\D/) {
4916 @_ = $this->_substituteCellref(@_);
4919 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
4921 return $this->writeNumber($row, $col, $token, $format);
4922 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
4923 // Match http or ftp URL
4924 return $this->writeUrl($row, $col, $token, '', $format);
4925 } elseif (preg_match("/^mailto:/", $token)) {
4927 return $this->writeUrl($row, $col, $token, '', $format);
4928 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
4929 // Match internal or external sheet link
4930 return $this->writeUrl($row, $col, $token, '', $format);
4931 } elseif (preg_match("/^=/", $token)) {
4933 return $this->writeFormula($row, $col, $token, $format);
4934 } elseif (preg_match("/^@/", $token)) {
4936 return $this->writeFormula($row, $col, $token, $format);
4937 } elseif ($token == '') {
4939 return $this->writeBlank($row, $col, $format);
4941 // Default: match string
4942 return $this->writeString($row, $col, $token, $format);
4947 * Write an array of values as a row
4950 * @param integer $row The row we are writing to
4951 * @param integer $col The first col (leftmost col) we are writing to
4952 * @param array $val The array of values to write
4953 * @param mixed $format The optional format to apply to the cell
4954 * @return mixed PEAR_Error on failure
4957 function writeRow($row, $col, $val, $format = null)
4960 if (is_array($val)) {
4961 foreach ($val as $v) {
4963 $this->writeCol($row, $col, $v, $format);
4965 $this->write($row, $col, $v, $format);
4970 die('$val needs to be an array');
4976 * Write an array of values as a column
4979 * @param integer $row The first row (uppermost row) we are writing to
4980 * @param integer $col The col we are writing to
4981 * @param array $val The array of values to write
4982 * @param mixed $format The optional format to apply to the cell
4983 * @return mixed PEAR_Error on failure
4986 function writeCol($row, $col, $val, $format = null)
4989 if (is_array($val)) {
4990 foreach ($val as $v) {
4991 $this->write($row, $col, $v, $format);
4995 die('$val needs to be an array');
5001 * Returns an index to the XF record in the workbook
5004 * @param mixed &$format The optional XF format
5005 * @return integer The XF record index
5007 function _XF(&$format)
5010 return($format->getXfIndex());
5017 /******************************************************************************
5018 *******************************************************************************
5025 * Store Worksheet data in memory using the parent's class append() or to a
5026 * temporary file, the default.
5029 * @param string $data The binary data to append
5031 function _append($data)
5033 if ($this->_using_tmpfile) {
5034 // Add CONTINUE records if necessary
5035 if (strlen($data) > $this->_limit) {
5036 $data = $this->_addContinue($data);
5038 fwrite($this->_filehandle, $data);
5039 $this->_datasize += strlen($data);
5041 parent::_append($data);
5046 * Substitute an Excel cell reference in A1 notation for zero based row and
5047 * column values in an argument list.
5049 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
5052 * @param string $cell The cell reference. Or range of cells.
5055 function _substituteCellref($cell)
5057 $cell = strtoupper($cell);
5059 // Convert a column range: 'A:A' or 'B:G'
5060 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
5061 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
5062 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
5063 return(array($col1, $col2));
5066 // Convert a cell range: 'A1:B7'
5067 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
5068 list($row1, $col1) = $this->_cellToRowcol($match[1]);
5069 list($row2, $col2) = $this->_cellToRowcol($match[2]);
5070 return(array($row1, $col1, $row2, $col2));
5073 // Convert a cell reference: 'A1' or 'AD2000'
5074 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
5075 list($row1, $col1) = $this->_cellToRowcol($match[1]);
5076 return(array($row1, $col1));
5079 // TODO use real error codes
5080 die("Unknown cell reference $cell");
5084 * Convert an Excel cell reference in A1 notation to a zero based row and column
5085 * reference; converts C1 to (0, 2).
5088 * @param string $cell The cell reference.
5089 * @return array containing (row, column)
5091 function _cellToRowcol($cell)
5093 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
5097 // Convert base26 column string to number
5098 $chars = preg_split('//', $col);
5103 $char = array_pop($chars); // LS char first
5104 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
5108 // Convert 1-index to zero-index
5112 return(array($row, $col));
5116 * Based on the algorithm provided by Daniel Rentz of OpenOffice.
5119 * @param string $plaintext The password to be encoded in plaintext.
5120 * @return string The encoded password
5122 function _encodePassword($plaintext)
5125 $i = 1; // char position
5127 // split the plain text password in its component characters
5128 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
5129 foreach ($chars as $char) {
5130 $value = ord($char) << $i; // shifted ASCII value
5131 $rotated_bits = $value >> 15; // rotated bits beyond bit 15
5132 $value &= 0x7fff; // first 15 bits
5133 $password ^= ($value | $rotated_bits);
5137 $password ^= strlen($plaintext);
5138 $password ^= 0xCE4B;
5144 * This method sets the properties for outlining and grouping. The defaults
5145 * correspond to Excel's defaults.
5147 * @param bool $visible
5148 * @param bool $symbols_below
5149 * @param bool $symbols_right
5150 * @param bool $auto_style
5152 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
5154 $this->_outline_on = $visible;
5155 $this->_outline_below = $symbols_below;
5156 $this->_outline_right = $symbols_right;
5157 $this->_outline_style = $auto_style;
5159 // Ensure this is a boolean vale for Window2
5160 if ($this->_outline_on) {
5161 $this->_outline_on = 1;
5165 /******************************************************************************
5166 *******************************************************************************
5173 * Write a double to the specified row and column (zero indexed).
5174 * An integer can be written as a double. Excel will display an
5175 * integer. $format is optional.
5177 * Returns 0 : normal termination
5178 * -2 : row or column out of range
5181 * @param integer $row Zero indexed row
5182 * @param integer $col Zero indexed column
5183 * @param float $num The number to write
5184 * @param mixed $format The optional XF format
5187 function writeNumber($row, $col, $num, $format = null)
5189 $record = 0x0203; // Record identifier
5190 $length = 0x000E; // Number of bytes to follow
5192 $xf = $this->_XF($format); // The cell format
5194 // Check that row and col are valid and store max and min values
5195 if ($row >= $this->_xls_rowmax) {
5198 if ($col >= $this->_xls_colmax) {
5201 if ($row < $this->_dim_rowmin) {
5202 $this->_dim_rowmin = $row;
5204 if ($row > $this->_dim_rowmax) {
5205 $this->_dim_rowmax = $row;
5207 if ($col < $this->_dim_colmin) {
5208 $this->_dim_colmin = $col;
5210 if ($col > $this->_dim_colmax) {
5211 $this->_dim_colmax = $col;
5214 $header = pack("vv", $record, $length);
5215 $data = pack("vvv", $row, $col, $xf);
5216 $xl_double = pack("d", $num);
5217 if ($this->_byte_order) { // if it's Big Endian
5218 $xl_double = strrev($xl_double);
5221 $this->_append($header.$data.$xl_double);
5226 * Write a string to the specified row and column (zero indexed).
5227 * NOTE: there is an Excel 5 defined limit of 255 characters.
5228 * $format is optional.
5229 * Returns 0 : normal termination
5230 * -2 : row or column out of range
5231 * -3 : long string truncated to 255 chars
5234 * @param integer $row Zero indexed row
5235 * @param integer $col Zero indexed column
5236 * @param string $str The string to write
5237 * @param mixed $format The XF format for the cell
5240 function writeString($row, $col, $str, $format = null)
5242 if ($this->_BIFF_version == 0x0600) {
5243 return $this->writeStringBIFF8($row, $col, $str, $format);
5245 $strlen = strlen($str);
5246 $record = 0x0204; // Record identifier
5247 $length = 0x0008 + $strlen; // Bytes to follow
5248 $xf = $this->_XF($format); // The cell format
5252 // Check that row and col are valid and store max and min values
5253 if ($row >= $this->_xls_rowmax) {
5256 if ($col >= $this->_xls_colmax) {
5259 if ($row < $this->_dim_rowmin) {
5260 $this->_dim_rowmin = $row;
5262 if ($row > $this->_dim_rowmax) {
5263 $this->_dim_rowmax = $row;
5265 if ($col < $this->_dim_colmin) {
5266 $this->_dim_colmin = $col;
5268 if ($col > $this->_dim_colmax) {
5269 $this->_dim_colmax = $col;
5272 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
5273 $str = substr($str, 0, $this->_xls_strmax);
5274 $length = 0x0008 + $this->_xls_strmax;
5275 $strlen = $this->_xls_strmax;
5279 $header = pack("vv", $record, $length);
5280 $data = pack("vvvv", $row, $col, $xf, $strlen);
5281 $this->_append($header . $data . $str);
5286 * Sets Input Encoding for writing strings
5289 * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
5291 function setInputEncoding($encoding)
5293 if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
5294 die("Using an input encoding other than UTF-16LE requires PHP support for iconv");
5296 $this->_input_encoding = $encoding;
5299 /** added 2009-03-05 by Joe Hunt, FA for arabic languages */
5306 * Write a string to the specified row and column (zero indexed).
5307 * This is the BIFF8 version (no 255 chars limit).
5308 * $format is optional.
5309 * Returns 0 : normal termination
5310 * -2 : row or column out of range
5311 * -3 : long string truncated to 255 chars
5314 * @param integer $row Zero indexed row
5315 * @param integer $col Zero indexed column
5316 * @param string $str The string to write
5317 * @param mixed $format The XF format for the cell
5320 function writeStringBIFF8($row, $col, $str, $format = null)
5322 if ($this->_input_encoding == 'UTF-16LE')
5324 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
5327 elseif ($this->_input_encoding != '')
5329 $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
5330 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
5335 $strlen = strlen($str);
5338 $record = 0x00FD; // Record identifier
5339 $length = 0x000A; // Bytes to follow
5340 $xf = $this->_XF($format); // The cell format
5344 // Check that row and col are valid and store max and min values
5345 if ($this->_checkRowCol($row, $col) == false) {
5349 $str = pack('vC', $strlen, $encoding).$str;
5351 /* check if string is already present */
5352 if (!isset($this->_str_table[$str])) {
5353 $this->_str_table[$str] = $this->_str_unique++;
5355 $this->_str_total++;
5357 $header = pack('vv', $record, $length);
5358 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
5359 $this->_append($header.$data);
5364 * Check row and col before writing to a cell, and update the sheet's
5365 * dimensions accordingly
5368 * @param integer $row Zero indexed row
5369 * @param integer $col Zero indexed column
5370 * @return boolean true for success, false if row and/or col are grester
5371 * then maximums allowed.
5373 function _checkRowCol($row, $col)
5375 if ($row >= $this->_xls_rowmax) {
5378 if ($col >= $this->_xls_colmax) {
5381 if ($row < $this->_dim_rowmin) {
5382 $this->_dim_rowmin = $row;
5384 if ($row > $this->_dim_rowmax) {
5385 $this->_dim_rowmax = $row;
5387 if ($col < $this->_dim_colmin) {
5388 $this->_dim_colmin = $col;
5390 if ($col > $this->_dim_colmax) {
5391 $this->_dim_colmax = $col;
5397 * Writes a note associated with the cell given by the row and column.
5398 * NOTE records don't have a length limit.
5401 * @param integer $row Zero indexed row
5402 * @param integer $col Zero indexed column
5403 * @param string $note The note to write
5405 function writeNote($row, $col, $note)
5407 $note_length = strlen($note);
5408 $record = 0x001C; // Record identifier
5409 $max_length = 2048; // Maximun length for a NOTE record
5410 //$length = 0x0006 + $note_length; // Bytes to follow
5412 // Check that row and col are valid and store max and min values
5413 if ($row >= $this->_xls_rowmax) {
5416 if ($col >= $this->_xls_colmax) {
5419 if ($row < $this->_dim_rowmin) {
5420 $this->_dim_rowmin = $row;
5422 if ($row > $this->_dim_rowmax) {
5423 $this->_dim_rowmax = $row;
5425 if ($col < $this->_dim_colmin) {
5426 $this->_dim_colmin = $col;
5428 if ($col > $this->_dim_colmax) {
5429 $this->_dim_colmax = $col;
5432 // Length for this record is no more than 2048 + 6
5433 $length = 0x0006 + min($note_length, 2048);
5434 $header = pack("vv", $record, $length);
5435 $data = pack("vvv", $row, $col, $note_length);
5436 $this->_append($header . $data . substr($note, 0, 2048));
5438 for ($i = $max_length; $i < $note_length; $i += $max_length) {
5439 $chunk = substr($note, $i, $max_length);
5440 $length = 0x0006 + strlen($chunk);
5441 $header = pack("vv", $record, $length);
5442 $data = pack("vvv", -1, 0, strlen($chunk));
5443 $this->_append($header.$data.$chunk);
5449 * Write a blank cell to the specified row and column (zero indexed).
5450 * A blank cell is used to specify formatting without adding a string
5453 * A blank cell without a format serves no purpose. Therefore, we don't write
5454 * a BLANK record unless a format is specified.
5456 * Returns 0 : normal termination (including no format)
5457 * -1 : insufficient number of arguments
5458 * -2 : row or column out of range
5461 * @param integer $row Zero indexed row
5462 * @param integer $col Zero indexed column
5463 * @param mixed $format The XF format
5465 function writeBlank($row, $col, $format)
5467 // Don't write a blank cell unless it has a format
5472 $record = 0x0201; // Record identifier
5473 $length = 0x0006; // Number of bytes to follow
5474 $xf = $this->_XF($format); // The cell format
5476 // Check that row and col are valid and store max and min values
5477 if ($row >= $this->_xls_rowmax) {
5480 if ($col >= $this->_xls_colmax) {
5483 if ($row < $this->_dim_rowmin) {
5484 $this->_dim_rowmin = $row;
5486 if ($row > $this->_dim_rowmax) {
5487 $this->_dim_rowmax = $row;
5489 if ($col < $this->_dim_colmin) {
5490 $this->_dim_colmin = $col;
5492 if ($col > $this->_dim_colmax) {
5493 $this->_dim_colmax = $col;
5496 $header = pack("vv", $record, $length);
5497 $data = pack("vvv", $row, $col, $xf);
5498 $this->_append($header . $data);
5503 * Write a formula to the specified row and column (zero indexed).
5504 * The textual representation of the formula is passed to the parser in
5505 * Parser.php which returns a packed binary string.
5507 * Returns 0 : normal termination
5508 * -1 : formula errors (bad formula)
5509 * -2 : row or column out of range
5512 * @param integer $row Zero indexed row
5513 * @param integer $col Zero indexed column
5514 * @param string $formula The formula text string
5515 * @param mixed $format The optional XF format
5518 function writeFormula($row, $col, $formula, $format = null)
5520 $record = 0x0006; // Record identifier
5522 // Excel normally stores the last calculated value of the formula in $num.
5523 // Clearly we are not in a position to calculate this a priori. Instead
5524 // we set $num to zero and set the option flags in $grbit to ensure
5525 // automatic calculation of the formula when the file is opened.
5527 $xf = $this->_XF($format); // The cell format
5528 $num = 0x00; // Current value of formula
5529 $grbit = 0x03; // Option flags
5530 $unknown = 0x0000; // Must be zero
5533 // Check that row and col are valid and store max and min values
5534 if ($this->_checkRowCol($row, $col) == false) {
5538 // Strip the '=' or '@' sign at the beginning of the formula string
5539 if (preg_match("/^=/", $formula)) {
5540 $formula = preg_replace("/(^=)/", "", $formula);
5541 } elseif (preg_match("/^@/", $formula)) {
5542 $formula = preg_replace("/(^@)/", "", $formula);
5545 $this->writeString($row, $col, 'Unrecognised character for formula');
5549 // Parse the formula using the parser in Parser.php
5550 $this->_parser->parse($formula);
5552 $formula = $this->_parser->toReversePolish();
5554 $formlen = strlen($formula); // Length of the binary string
5555 $length = 0x16 + $formlen; // Length of the record data
5557 $header = pack("vv", $record, $length);
5558 $data = pack("vvvdvVv", $row, $col, $xf, $num,
5559 $grbit, $unknown, $formlen);
5561 $this->_append($header . $data . $formula);
5566 * Write a hyperlink.
5567 * This is comprised of two elements: the visible label and
5568 * the invisible link. The visible label is the same as the link unless an
5569 * alternative string is specified. The label is written using the
5570 * writeString() method. Therefore the 255 characters string limit applies.
5571 * $string and $format are optional.
5573 * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
5576 * Returns 0 : normal termination
5577 * -2 : row or column out of range
5578 * -3 : long string truncated to 255 chars
5581 * @param integer $row Row
5582 * @param integer $col Column
5583 * @param string $url URL string
5584 * @param string $string Alternative label
5585 * @param mixed $format The cell format
5588 function writeUrl($row, $col, $url, $string = '', $format = null)
5590 // Add start row and col to arg list
5591 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
5595 * This is the more general form of writeUrl(). It allows a hyperlink to be
5596 * written to a range of cells. This function also decides the type of hyperlink
5597 * to be written. These are either, Web (http, ftp, mailto), Internal
5598 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
5602 * @param integer $row1 Start row
5603 * @param integer $col1 Start column
5604 * @param integer $row2 End row
5605 * @param integer $col2 End column
5606 * @param string $url URL string
5607 * @param string $string Alternative label
5608 * @param mixed $format The cell format
5612 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
5615 // Check for internal/external sheet links or default to web link
5616 if (preg_match('[^internal:]', $url)) {
5617 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
5619 if (preg_match('[^external:]', $url)) {
5620 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
5622 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
5627 * Used to write http, ftp and mailto hyperlinks.
5628 * The link type ($options) is 0x03 is the same as absolute dir ref without
5629 * sheet. However it is differentiated by the $unknown2 data stream.
5633 * @param integer $row1 Start row
5634 * @param integer $col1 Start column
5635 * @param integer $row2 End row
5636 * @param integer $col2 End column
5637 * @param string $url URL string
5638 * @param string $str Alternative label
5639 * @param mixed $format The cell format
5642 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
5644 $record = 0x01B8; // Record identifier
5645 $length = 0x00000; // Bytes to follow
5648 $format = $this->_url_format;
5651 // Write the visible label using the writeString() method.
5655 $str_error = $this->writeString($row1, $col1, $str, $format);
5656 if (($str_error == -2) || ($str_error == -3)) {
5660 // Pack the undocumented parts of the hyperlink stream
5661 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5662 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
5664 // Pack the option flags
5665 $options = pack("V", 0x03);
5667 // Convert URL to a null terminated wchar string
5668 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5669 $url = $url . "\0\0\0";
5671 // Pack the length of the URL
5672 $url_len = pack("V", strlen($url));
5674 // Calculate the data length
5675 $length = 0x34 + strlen($url);
5677 // Pack the header data
5678 $header = pack("vv", $record, $length);
5679 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5681 // Write the packed data
5682 $this->_append($header . $data .
5683 $unknown1 . $options .
5684 $unknown2 . $url_len . $url);
5689 * Used to write internal reference hyperlinks such as "Sheet1!A1".
5693 * @param integer $row1 Start row
5694 * @param integer $col1 Start column
5695 * @param integer $row2 End row
5696 * @param integer $col2 End column
5697 * @param string $url URL string
5698 * @param string $str Alternative label
5699 * @param mixed $format The cell format
5702 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5704 $record = 0x01B8; // Record identifier
5705 $length = 0x00000; // Bytes to follow
5708 $format = $this->_url_format;
5712 $url = preg_replace('/^internal:/', '', $url);
5714 // Write the visible label
5718 $str_error = $this->writeString($row1, $col1, $str, $format);
5719 if (($str_error == -2) || ($str_error == -3)) {
5723 // Pack the undocumented parts of the hyperlink stream
5724 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
5726 // Pack the option flags
5727 $options = pack("V", 0x08);
5729 // Convert the URL type and to a null terminated wchar string
5730 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
5731 $url = $url . "\0\0\0";
5733 // Pack the length of the URL as chars (not wchars)
5734 $url_len = pack("V", floor(strlen($url)/2));
5736 // Calculate the data length
5737 $length = 0x24 + strlen($url);
5739 // Pack the header data
5740 $header = pack("vv", $record, $length);
5741 $data = pack("vvvv", $row1, $row2, $col1, $col2);
5743 // Write the packed data
5744 $this->_append($header . $data .
5745 $unknown1 . $options .
5751 * Write links to external directory names such as 'c:\foo.xls',
5752 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
5754 * Note: Excel writes some relative links with the $dir_long string. We ignore
5755 * these cases for the sake of simpler code.
5759 * @param integer $row1 Start row
5760 * @param integer $col1 Start column
5761 * @param integer $row2 End row
5762 * @param integer $col2 End column
5763 * @param string $url URL string
5764 * @param string $str Alternative label
5765 * @param mixed $format The cell format
5768 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
5770 // Network drives are different. We will handle them separately
5771 // MS/Novell network drives and shares start with \\
5772 if (preg_match('[^external:\\\\]', $url)) {
5773 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
5776 $record = 0x01B8; // Record identifier
5777 $length = 0x00000; // Bytes to follow
5780 $format = $this->_url_format;
5783 // Strip URL type and change Unix dir separator to Dos style (if needed)
5785 $url = preg_replace('/^external:/', '', $url);
5786 $url = preg_replace('/\//', "\\", $url);
5788 // Write the visible label
5790 $str = preg_replace('/\#/', ' - ', $url);
5792 $str_error = $this->writeString($row1, $col1, $str, $format);
5793 if (($str_error == -2) or ($str_error == -3)) {
5797 // Determine if the link is relative or absolute:
5798 // relative if link contains no dir separator, "somefile.xls"
5799 // relative if link starts with up-dir, "..\..\somefile.xls"
5800 // otherwise, absolute
5802 $absolute = 0x02; // Bit mask
5803 if (!preg_match("/\\\/", $url)) {
5806 if (preg_match("/^\.\.\\\/", $url)) {
5809 $link_type = 0x01 | $absolute;
5811 // Determine if the link contains a sheet reference and change some of the
5812 // parameters accordingly.
5813 // Split the dir name and sheet name (if it exists)
5814 /*if (preg_match("/\#/", $url)) {
5815 list($dir_long, $sheet) = preg_split("/\#/", $url);
5820 if (isset($sheet)) {
5822 $sheet_len = pack("V", strlen($sheet) + 0x01);
5823 $sheet = join("\0", preg_split('//', $sheet));
5830 if (preg_match("/\#/", $url)) {
5836 // Pack the link type
5837 $link_type = pack("V", $link_type);
5839 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
5840 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
5841 $up_count = pack("v", $up_count);
5843 // Store the short dos dir name (null terminated)
5844 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
5846 // Store the long dir name as a wchar string (non-null terminated)
5847 //$dir_long = join("\0", preg_split('//', $dir_long));
5848 $dir_long = $dir_long . "\0";
5850 // Pack the lengths of the dir strings
5851 $dir_short_len = pack("V", strlen($dir_short) );
5852 $dir_long_len = pack("V", strlen($dir_long) );
5853 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
5855 // Pack the undocumented parts of the hyperlink stream
5856 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
5857 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
5858 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
5859 $unknown4 = pack("v", 0x03 );
5861 // Pack the main data stream
5862 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
5877 // Pack the header data
5878 $length = strlen($data);
5879 $header = pack("vv", $record, $length);
5881 // Write the packed data
5882 $this->_append($header. $data);
5888 * This method is used to set the height and format for a row.
5891 * @param integer $row The row to set
5892 * @param integer $height Height we are giving to the row.
5893 * Use null to set XF without setting height
5894 * @param mixed $format XF format we are giving to the row
5895 * @param bool $hidden The optional hidden attribute
5896 * @param integer $level The optional outline level for row, in range [0,7]
5898 function setRow($row, $height, $format = null, $hidden = false, $level = 0)
5900 $record = 0x0208; // Record identifier
5901 $length = 0x0010; // Number of bytes to follow
5903 $colMic = 0x0000; // First defined column
5904 $colMac = 0x0000; // Last defined column
5905 $irwMac = 0x0000; // Used by Excel to optimise loading
5906 $reserved = 0x0000; // Reserved
5907 $grbit = 0x0000; // Option flags
5908 $ixfe = $this->_XF($format); // XF index
5910 // set _row_sizes so _sizeRow() can use it
5911 $this->_row_sizes[$row] = $height;
5913 // Use setRow($row, null, $XF) to set XF format without setting height
5914 if ($height != null) {
5915 $miyRw = $height * 20; // row height
5917 $miyRw = 0xff; // default row height is 256
5920 $level = max(0, min($level, 7)); // level should be between 0 and 7
5921 $this->_outline_row_level = max($level, $this->_outline_row_level);
5924 // Set the options flags. fUnsynced is used to show that the font and row
5925 // heights are not compatible. This is usually the case for WriteExcel.
5926 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
5927 // is collapsed. Instead it is used to indicate that the previous row is
5928 // collapsed. The zero height flag, 0x20, is used to collapse a row.
5934 $grbit |= 0x0040; // fUnsynced
5940 $header = pack("vv", $record, $length);
5941 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
5942 $irwMac,$reserved, $grbit, $ixfe);
5943 $this->_append($header.$data);
5947 * Writes Excel DIMENSIONS to define the area in which there is data.
5951 function _storeDimensions()
5953 $record = 0x0200; // Record identifier
5954 $row_min = $this->_dim_rowmin; // First row
5955 $row_max = $this->_dim_rowmax + 1; // Last row plus 1
5956 $col_min = $this->_dim_colmin; // First column
5957 $col_max = $this->_dim_colmax + 1; // Last column plus 1
5958 $reserved = 0x0000; // Reserved by Excel
5960 if ($this->_BIFF_version == 0x0500) {
5961 $length = 0x000A; // Number of bytes to follow
5962 $data = pack("vvvvv", $row_min, $row_max,
5963 $col_min, $col_max, $reserved);
5964 } elseif ($this->_BIFF_version == 0x0600) {
5966 $data = pack("VVvvv", $row_min, $row_max,
5967 $col_min, $col_max, $reserved);
5969 $header = pack("vv", $record, $length);
5970 $this->_prepend($header.$data);
5974 * Write BIFF record Window2.
5978 function _storeWindow2()
5980 $record = 0x023E; // Record identifier
5981 if ($this->_BIFF_version == 0x0500) {
5982 $length = 0x000A; // Number of bytes to follow
5983 } elseif ($this->_BIFF_version == 0x0600) {
5987 $grbit = 0x00B6; // Option flags
5988 $rwTop = 0x0000; // Top row visible in window
5989 $colLeft = 0x0000; // Leftmost column visible in window
5992 // The options flags that comprise $grbit
5993 $fDspFmla = 0; // 0 - bit
5994 $fDspGrid = $this->_screen_gridlines; // 1
5995 $fDspRwCol = 1; // 2
5996 $fFrozen = $this->_frozen; // 3
5997 $fDspZeros = 1; // 4
5998 $fDefaultHdr = 1; // 5
5999 $fArabic = $this->_rtl; // 6
6000 $fDspGuts = $this->_outline_on; // 7
6001 $fFrozenNoSplit = 0; // 0 - bit
6002 $fSelected = $this->selected; // 1
6006 $grbit |= $fDspGrid << 1;
6007 $grbit |= $fDspRwCol << 2;
6008 $grbit |= $fFrozen << 3;
6009 $grbit |= $fDspZeros << 4;
6010 $grbit |= $fDefaultHdr << 5;
6011 $grbit |= $fArabic << 6;
6012 $grbit |= $fDspGuts << 7;
6013 $grbit |= $fFrozenNoSplit << 8;
6014 $grbit |= $fSelected << 9;
6015 $grbit |= $fPaged << 10;
6017 $header = pack("vv", $record, $length);
6018 $data = pack("vvv", $grbit, $rwTop, $colLeft);
6020 if ($this->_BIFF_version == 0x0500) {
6021 $rgbHdr = 0x00000000; // Row/column heading and gridline color
6022 $data .= pack("V", $rgbHdr);
6023 } elseif ($this->_BIFF_version == 0x0600) {
6024 $rgbHdr = 0x0040; // Row/column heading and gridline color index
6025 $zoom_factor_page_break = 0x0000;
6026 $zoom_factor_normal = 0x0000;
6027 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
6029 $this->_append($header.$data);
6033 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
6037 function _storeDefcol()
6039 $record = 0x0055; // Record identifier
6040 $length = 0x0002; // Number of bytes to follow
6041 $colwidth = 0x0008; // Default column width
6043 $header = pack("vv", $record, $length);
6044 $data = pack("v", $colwidth);
6045 $this->_prepend($header . $data);
6049 * Write BIFF record COLINFO to define column widths
6051 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
6055 * @param array $col_array This is the only parameter received and is composed of the following:
6056 * 0 => First formatted column,
6057 * 1 => Last formatted column,
6058 * 2 => Col width (8.43 is Excel default),
6059 * 3 => The optional XF format of the column,
6060 * 4 => Option flags.
6061 * 5 => Optional outline level
6063 function _storeColinfo($col_array)
6065 if (isset($col_array[0])) {
6066 $colFirst = $col_array[0];
6068 if (isset($col_array[1])) {
6069 $colLast = $col_array[1];
6071 if (isset($col_array[2])) {
6072 $coldx = $col_array[2];
6076 if (isset($col_array[3])) {
6077 $format = $col_array[3];
6081 if (isset($col_array[4])) {
6082 $grbit = $col_array[4];
6086 if (isset($col_array[5])) {
6087 $level = $col_array[5];
6091 $record = 0x007D; // Record identifier
6092 $length = 0x000B; // Number of bytes to follow
6094 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
6095 $coldx *= 256; // Convert to units of 1/256 of a char
6097 $ixfe = $this->_XF($format);
6098 $reserved = 0x00; // Reserved
6100 $level = max(0, min($level, 7));
6101 $grbit |= $level << 8;
6103 $header = pack("vv", $record, $length);
6104 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
6105 $ixfe, $grbit, $reserved);
6106 $this->_prepend($header.$data);
6110 * Write BIFF record SELECTION.
6113 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
6114 * @see setSelection()
6116 function _storeSelection($array)
6118 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
6119 $record = 0x001D; // Record identifier
6120 $length = 0x000F; // Number of bytes to follow
6122 $pnn = $this->_active_pane; // Pane position
6123 $rwAct = $rwFirst; // Active row
6124 $colAct = $colFirst; // Active column
6125 $irefAct = 0; // Active cell ref
6126 $cref = 1; // Number of refs
6128 if (!isset($rwLast)) {
6129 $rwLast = $rwFirst; // Last row in reference
6131 if (!isset($colLast)) {
6132 $colLast = $colFirst; // Last col in reference
6135 // Swap last row/col for first row/col as necessary
6136 if ($rwFirst > $rwLast) {
6137 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
6140 if ($colFirst > $colLast) {
6141 list($colFirst, $colLast) = array($colLast, $colFirst);
6144 $header = pack("vv", $record, $length);
6145 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
6148 $colFirst, $colLast);
6149 $this->_append($header . $data);
6153 * Store the MERGEDCELLS record for all ranges of merged cells
6157 function _storeMergedCells()
6159 // if there are no merged cell ranges set, return
6160 if (count($this->_merged_ranges) == 0) {
6164 $length = 2 + count($this->_merged_ranges) * 8;
6166 $header = pack('vv', $record, $length);
6167 $data = pack('v', count($this->_merged_ranges));
6168 foreach ($this->_merged_ranges as $range) {
6169 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
6171 $this->_append($header . $data);
6175 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
6176 * references in a worksheet.
6178 * Excel only stores references to external sheets that are used in formulas.
6179 * For simplicity we store references to all the sheets in the workbook
6180 * regardless of whether they are used or not. This reduces the overall
6181 * complexity and eliminates the need for a two way dialogue between the formula
6182 * parser the worksheet objects.
6185 * @param integer $count The number of external sheet references in this worksheet
6187 function _storeExterncount($count)
6189 $record = 0x0016; // Record identifier
6190 $length = 0x0002; // Number of bytes to follow
6192 $header = pack("vv", $record, $length);
6193 $data = pack("v", $count);
6194 $this->_prepend($header . $data);
6198 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
6199 * formulas. A formula references a sheet name via an index. Since we store a
6200 * reference to all of the external worksheets the EXTERNSHEET index is the same
6201 * as the worksheet index.
6204 * @param string $sheetname The name of a external worksheet
6206 function _storeExternsheet($sheetname)
6208 $record = 0x0017; // Record identifier
6210 // References to the current sheet are encoded differently to references to
6213 if ($this->name == $sheetname) {
6215 $length = 0x02; // The following 2 bytes
6216 $cch = 1; // The following byte
6217 $rgch = 0x02; // Self reference
6219 $length = 0x02 + strlen($sheetname);
6220 $cch = strlen($sheetname);
6221 $rgch = 0x03; // Reference to a sheet in the current workbook
6224 $header = pack("vv", $record, $length);
6225 $data = pack("CC", $cch, $rgch);
6226 $this->_prepend($header . $data . $sheetname);
6230 * Writes the Excel BIFF PANE record.
6231 * The panes can either be frozen or thawed (unfrozen).
6232 * Frozen panes are specified in terms of an integer number of rows and columns.
6233 * Thawed panes are specified in terms of Excel's units for rows and columns.
6236 * @param array $panes This is the only parameter received and is composed of the following:
6237 * 0 => Vertical split position,
6238 * 1 => Horizontal split position
6239 * 2 => Top row visible
6240 * 3 => Leftmost column visible
6243 function _storePanes($panes)
6248 $colLeft = $panes[3];
6249 if (count($panes) > 4) { // if Active pane was received
6250 $pnnAct = $panes[4];
6254 $record = 0x0041; // Record identifier
6255 $length = 0x000A; // Number of bytes to follow
6257 // Code specific to frozen or thawed panes.
6258 if ($this->_frozen) {
6259 // Set default values for $rwTop and $colLeft
6260 if (!isset($rwTop)) {
6263 if (!isset($colLeft)) {
6267 // Set default values for $rwTop and $colLeft
6268 if (!isset($rwTop)) {
6271 if (!isset($colLeft)) {
6275 // Convert Excel's row and column units to the internal units.
6276 // The default row height is 12.75
6277 // The default column width is 8.43
6278 // The following slope and intersection values were interpolated.
6281 $x = 113.879*$x + 390;
6285 // Determine which pane should be active. There is also the undocumented
6286 // option to override this should it be necessary: may be removed later.
6288 if (!isset($pnnAct)) {
6289 if ($x != 0 && $y != 0) {
6290 $pnnAct = 0; // Bottom right
6292 if ($x != 0 && $y == 0) {
6293 $pnnAct = 1; // Top right
6295 if ($x == 0 && $y != 0) {
6296 $pnnAct = 2; // Bottom left
6298 if ($x == 0 && $y == 0) {
6299 $pnnAct = 3; // Top left
6303 $this->_active_pane = $pnnAct; // Used in _storeSelection
6305 $header = pack("vv", $record, $length);
6306 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
6307 $this->_append($header . $data);
6311 * Store the page setup SETUP BIFF record.
6315 function _storeSetup()
6317 $record = 0x00A1; // Record identifier
6318 $length = 0x0022; // Number of bytes to follow
6320 $iPaperSize = $this->_paper_size; // Paper size
6321 $iScale = $this->_print_scale; // Print scaling factor
6322 $iPageStart = 0x01; // Starting page number
6323 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
6324 $iFitHeight = $this->_fit_height; // Fit to number of pages high
6325 $grbit = 0x00; // Option flags
6326 $iRes = 0x0258; // Print resolution
6327 $iVRes = 0x0258; // Vertical print resolution
6328 $numHdr = $this->_margin_head; // Header Margin
6329 $numFtr = $this->_margin_foot; // Footer Margin
6330 $iCopies = 0x01; // Number of copies
6332 $fLeftToRight = 0x0; // Print over then down
6333 $fLandscape = $this->_orientation; // Page orientation
6334 $fNoPls = 0x0; // Setup not read from printer
6335 $fNoColor = 0x0; // Print black and white
6336 $fDraft = 0x0; // Print draft quality
6337 $fNotes = 0x0; // Print notes
6338 $fNoOrient = 0x0; // Orientation not set
6339 $fUsePage = 0x0; // Use custom starting page
6341 $grbit = $fLeftToRight;
6342 $grbit |= $fLandscape << 1;
6343 $grbit |= $fNoPls << 2;
6344 $grbit |= $fNoColor << 3;
6345 $grbit |= $fDraft << 4;
6346 $grbit |= $fNotes << 5;
6347 $grbit |= $fNoOrient << 6;
6348 $grbit |= $fUsePage << 7;
6350 $numHdr = pack("d", $numHdr);
6351 $numFtr = pack("d", $numFtr);
6352 if ($this->_byte_order) { // if it's Big Endian
6353 $numHdr = strrev($numHdr);
6354 $numFtr = strrev($numFtr);
6357 $header = pack("vv", $record, $length);
6358 $data1 = pack("vvvvvvvv", $iPaperSize,
6366 $data2 = $numHdr.$numFtr;
6367 $data3 = pack("v", $iCopies);
6368 $this->_prepend($header . $data1 . $data2 . $data3);
6372 * Store the header caption BIFF record.
6376 function _storeHeader()
6378 $record = 0x0014; // Record identifier
6380 $str = $this->_header; // header string
6381 $cch = strlen($str); // Length of header string
6382 if ($this->_BIFF_version == 0x0600) {
6383 $encoding = 0x0; // TODO: Unicode support
6384 $length = 3 + $cch; // Bytes to follow
6386 $length = 1 + $cch; // Bytes to follow
6389 $header = pack("vv", $record, $length);
6390 if ($this->_BIFF_version == 0x0600) {
6391 $data = pack("vC", $cch, $encoding);
6393 $data = pack("C", $cch);
6396 $this->_prepend($header.$data.$str);
6400 * Store the footer caption BIFF record.
6404 function _storeFooter()
6406 $record = 0x0015; // Record identifier
6408 $str = $this->_footer; // Footer string
6409 $cch = strlen($str); // Length of footer string
6410 if ($this->_BIFF_version == 0x0600) {
6411 $encoding = 0x0; // TODO: Unicode support
6412 $length = 3 + $cch; // Bytes to follow
6417 $header = pack("vv", $record, $length);
6418 if ($this->_BIFF_version == 0x0600) {
6419 $data = pack("vC", $cch, $encoding);
6421 $data = pack("C", $cch);
6424 $this->_prepend($header . $data . $str);
6428 * Store the horizontal centering HCENTER BIFF record.
6432 function _storeHcenter()
6434 $record = 0x0083; // Record identifier
6435 $length = 0x0002; // Bytes to follow
6437 $fHCenter = $this->_hcenter; // Horizontal centering
6439 $header = pack("vv", $record, $length);
6440 $data = pack("v", $fHCenter);
6442 $this->_prepend($header.$data);
6446 * Store the vertical centering VCENTER BIFF record.
6450 function _storeVcenter()
6452 $record = 0x0084; // Record identifier
6453 $length = 0x0002; // Bytes to follow
6455 $fVCenter = $this->_vcenter; // Horizontal centering
6457 $header = pack("vv", $record, $length);
6458 $data = pack("v", $fVCenter);
6459 $this->_prepend($header . $data);
6463 * Store the LEFTMARGIN BIFF record.
6467 function _storeMarginLeft()
6469 $record = 0x0026; // Record identifier
6470 $length = 0x0008; // Bytes to follow
6472 $margin = $this->_margin_left; // Margin in inches
6474 $header = pack("vv", $record, $length);
6475 $data = pack("d", $margin);
6476 if ($this->_byte_order) { // if it's Big Endian
6477 $data = strrev($data);
6480 $this->_prepend($header . $data);
6484 * Store the RIGHTMARGIN BIFF record.
6488 function _storeMarginRight()
6490 $record = 0x0027; // Record identifier
6491 $length = 0x0008; // Bytes to follow
6493 $margin = $this->_margin_right; // Margin in inches
6495 $header = pack("vv", $record, $length);
6496 $data = pack("d", $margin);
6497 if ($this->_byte_order) { // if it's Big Endian
6498 $data = strrev($data);
6501 $this->_prepend($header . $data);
6505 * Store the TOPMARGIN BIFF record.
6509 function _storeMarginTop()
6511 $record = 0x0028; // Record identifier
6512 $length = 0x0008; // Bytes to follow
6514 $margin = $this->_margin_top; // Margin in inches
6516 $header = pack("vv", $record, $length);
6517 $data = pack("d", $margin);
6518 if ($this->_byte_order) { // if it's Big Endian
6519 $data = strrev($data);
6522 $this->_prepend($header . $data);
6526 * Store the BOTTOMMARGIN BIFF record.
6530 function _storeMarginBottom()
6532 $record = 0x0029; // Record identifier
6533 $length = 0x0008; // Bytes to follow
6535 $margin = $this->_margin_bottom; // Margin in inches
6537 $header = pack("vv", $record, $length);
6538 $data = pack("d", $margin);
6539 if ($this->_byte_order) { // if it's Big Endian
6540 $data = strrev($data);
6543 $this->_prepend($header . $data);
6547 * Merges the area given by its arguments.
6548 * This is an Excel97/2000 method. It is required to perform more complicated
6549 * merging than the normal setAlign('merge').
6552 * @param integer $first_row First row of the area to merge
6553 * @param integer $first_col First column of the area to merge
6554 * @param integer $last_row Last row of the area to merge
6555 * @param integer $last_col Last column of the area to merge
6557 function mergeCells($first_row, $first_col, $last_row, $last_col)
6559 $record = 0x00E5; // Record identifier
6560 $length = 0x000A; // Bytes to follow
6561 $cref = 1; // Number of refs
6563 // Swap last row/col for first row/col as necessary
6564 if ($first_row > $last_row) {
6565 list($first_row, $last_row) = array($last_row, $first_row);
6568 if ($first_col > $last_col) {
6569 list($first_col, $last_col) = array($last_col, $first_col);
6572 $header = pack("vv", $record, $length);
6573 $data = pack("vvvvv", $cref, $first_row, $last_row,
6574 $first_col, $last_col);
6576 $this->_append($header.$data);
6580 * Write the PRINTHEADERS BIFF record.
6584 function _storePrintHeaders()
6586 $record = 0x002a; // Record identifier
6587 $length = 0x0002; // Bytes to follow
6589 $fPrintRwCol = $this->_print_headers; // Boolean flag
6591 $header = pack("vv", $record, $length);
6592 $data = pack("v", $fPrintRwCol);
6593 $this->_prepend($header . $data);
6597 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
6602 function _storePrintGridlines()
6604 $record = 0x002b; // Record identifier
6605 $length = 0x0002; // Bytes to follow
6607 $fPrintGrid = $this->_print_gridlines; // Boolean flag
6609 $header = pack("vv", $record, $length);
6610 $data = pack("v", $fPrintGrid);
6611 $this->_prepend($header . $data);
6615 * Write the GRIDSET BIFF record. Must be used in conjunction with the
6616 * PRINTGRIDLINES record.
6620 function _storeGridset()
6622 $record = 0x0082; // Record identifier
6623 $length = 0x0002; // Bytes to follow
6625 $fGridSet = !($this->_print_gridlines); // Boolean flag
6627 $header = pack("vv", $record, $length);
6628 $data = pack("v", $fGridSet);
6629 $this->_prepend($header . $data);
6633 * Write the GUTS BIFF record. This is used to configure the gutter margins
6634 * where Excel outline symbols are displayed. The visibility of the gutters is
6635 * controlled by a flag in WSBOOL.
6637 * @see _storeWsbool()
6640 function _storeGuts()
6642 $record = 0x0080; // Record identifier
6643 $length = 0x0008; // Bytes to follow
6645 $dxRwGut = 0x0000; // Size of row gutter
6646 $dxColGut = 0x0000; // Size of col gutter
6648 $row_level = $this->_outline_row_level;
6651 // Calculate the maximum column outline level. The equivalent calculation
6652 // for the row outline level is carried out in setRow().
6653 $colcount = count($this->_colinfo);
6654 for ($i = 0; $i < $colcount; $i++) {
6655 // Skip cols without outline level info.
6656 if (count_array($col_level) >= 6) {
6657 $col_level = max($this->_colinfo[$i][5], $col_level);
6661 // Set the limits for the outline levels (0 <= x <= 7).
6662 $col_level = max(0, min($col_level, 7));
6664 // The displayed level is one greater than the max outline levels
6672 $header = pack("vv", $record, $length);
6673 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
6675 $this->_prepend($header.$data);
6680 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
6681 * with the SETUP record.
6685 function _storeWsbool()
6687 $record = 0x0081; // Record identifier
6688 $length = 0x0002; // Bytes to follow
6691 // The only option that is of interest is the flag for fit to page. So we
6692 // set all the options in one go.
6694 /*if ($this->_fit_page) {
6699 // Set the option flags
6700 $grbit |= 0x0001; // Auto page breaks visible
6701 if ($this->_outline_style) {
6702 $grbit |= 0x0020; // Auto outline styles
6704 if ($this->_outline_below) {
6705 $grbit |= 0x0040; // Outline summary below
6707 if ($this->_outline_right) {
6708 $grbit |= 0x0080; // Outline summary right
6710 if ($this->_fit_page) {
6711 $grbit |= 0x0100; // Page setup fit to page
6713 if ($this->_outline_on) {
6714 $grbit |= 0x0400; // Outline symbols displayed
6717 $header = pack("vv", $record, $length);
6718 $data = pack("v", $grbit);
6719 $this->_prepend($header . $data);
6723 * Write the HORIZONTALPAGEBREAKS BIFF record.
6727 function _storeHbreak()
6729 // Return if the user hasn't specified pagebreaks
6730 if (empty($this->_hbreaks)) {
6734 // Sort and filter array of page breaks
6735 $breaks = $this->_hbreaks;
6736 sort($breaks, SORT_NUMERIC);
6737 if ($breaks[0] == 0) { // don't use first break if it's 0
6738 array_shift($breaks);
6741 $record = 0x001b; // Record identifier
6742 $cbrk = count($breaks); // Number of page breaks
6743 if ($this->_BIFF_version == 0x0600) {
6744 $length = 2 + 6*$cbrk; // Bytes to follow
6746 $length = 2 + 2*$cbrk; // Bytes to follow
6749 $header = pack("vv", $record, $length);
6750 $data = pack("v", $cbrk);
6752 // Append each page break
6753 foreach ($breaks as $break) {
6754 if ($this->_BIFF_version == 0x0600) {
6755 $data .= pack("vvv", $break, 0x0000, 0x00ff);
6757 $data .= pack("v", $break);
6761 $this->_prepend($header.$data);
6766 * Write the VERTICALPAGEBREAKS BIFF record.
6770 function _storeVbreak()
6772 // Return if the user hasn't specified pagebreaks
6773 if (empty($this->_vbreaks)) {
6777 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
6778 // It is slightly higher in Excel 97/200, approx. 1026
6779 $breaks = array_slice($this->_vbreaks,0,1000);
6781 // Sort and filter array of page breaks
6782 sort($breaks, SORT_NUMERIC);
6783 if ($breaks[0] == 0) { // don't use first break if it's 0
6784 array_shift($breaks);
6787 $record = 0x001a; // Record identifier
6788 $cbrk = count($breaks); // Number of page breaks
6789 if ($this->_BIFF_version == 0x0600) {
6790 $length = 2 + 6*$cbrk; // Bytes to follow
6792 $length = 2 + 2*$cbrk; // Bytes to follow
6795 $header = pack("vv", $record, $length);
6796 $data = pack("v", $cbrk);
6798 // Append each page break
6799 foreach ($breaks as $break) {
6800 if ($this->_BIFF_version == 0x0600) {
6801 $data .= pack("vvv", $break, 0x0000, 0xffff);
6803 $data .= pack("v", $break);
6807 $this->_prepend($header . $data);
6811 * Set the Biff PROTECT record to indicate that the worksheet is protected.
6815 function _storeProtect()
6817 // Exit unless sheet protection has been specified
6818 if ($this->_protect == 0) {
6822 $record = 0x0012; // Record identifier
6823 $length = 0x0002; // Bytes to follow
6825 $fLock = $this->_protect; // Worksheet is protected
6827 $header = pack("vv", $record, $length);
6828 $data = pack("v", $fLock);
6830 $this->_prepend($header.$data);
6834 * Write the worksheet PASSWORD record.
6838 function _storePassword()
6840 // Exit unless sheet protection and password have been specified
6841 if (($this->_protect == 0) || (!isset($this->_password))) {
6845 $record = 0x0013; // Record identifier
6846 $length = 0x0002; // Bytes to follow
6848 $wPassword = $this->_password; // Encoded password
6850 $header = pack("vv", $record, $length);
6851 $data = pack("v", $wPassword);
6853 $this->_prepend($header . $data);
6858 * Insert a 24bit bitmap image in a worksheet.
6861 * @param integer $row The row we are going to insert the bitmap into
6862 * @param integer $col The column we are going to insert the bitmap into
6863 * @param string $bitmap The bitmap filename
6864 * @param integer $x The horizontal position (offset) of the image inside the cell.
6865 * @param integer $y The vertical position (offset) of the image inside the cell.
6866 * @param integer $scale_x The horizontal scale
6867 * @param integer $scale_y The vertical scale
6869 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
6871 $bitmap_array = $this->_processBitmap($bitmap);
6872 if ($this->isError($bitmap_array)) {
6873 $this->writeString($row, $col, $bitmap_array->getMessage());
6876 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
6878 // Scale the frame of the image.
6880 $height *= $scale_y;
6882 // Calculate the vertices of the image and write the OBJ record
6883 $this->_positionImage($col, $row, $x, $y, $width, $height);
6885 // Write the IMDATA record to store the bitmap data
6887 $length = 8 + $size;
6892 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
6893 $this->_append($header.$data);
6897 * Calculate the vertices that define the position of the image as required by
6900 * +------------+------------+
6902 * +-----+------------+------------+
6904 * | 1 |(A1)._______|______ |
6907 * +-----+----| BITMAP |-----+
6909 * | 2 | |______________. |
6912 * +---- +------------+------------+
6914 * Example of a bitmap that covers some of the area from cell A1 to cell B2.
6916 * Based on the width and height of the bitmap we need to calculate 8 vars:
6917 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
6918 * The width and height of the cells are also variable and have to be taken into
6920 * The values of $col_start and $row_start are passed in from the calling
6921 * function. The values of $col_end and $row_end are calculated by subtracting
6922 * the width and height of the bitmap from the width and height of the
6924 * The vertices are expressed as a percentage of the underlying cell width as
6925 * follows (rhs values are in pixels):
6929 * x2 = (X-1) / W *1024
6930 * y2 = (Y-1) / H *256
6932 * Where: X is distance from the left side of the underlying cell
6933 * Y is distance from the top of the underlying cell
6934 * W is the width of the cell
6935 * H is the height of the cell
6938 * @note the SDK incorrectly states that the height should be expressed as a
6939 * percentage of 1024.
6940 * @param integer $col_start Col containing upper left corner of object
6941 * @param integer $row_start Row containing top left corner of object
6942 * @param integer $x1 Distance to left side of object
6943 * @param integer $y1 Distance to top of object
6944 * @param integer $width Width of image frame
6945 * @param integer $height Height of image frame
6947 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
6949 // Initialise end cell to the same as the start cell
6950 $col_end = $col_start; // Col containing lower right corner of object
6951 $row_end = $row_start; // Row containing bottom right corner of object
6953 // Zero the specified offset if greater than the cell dimensions
6954 if ($x1 >= $this->_sizeCol($col_start)) {
6957 if ($y1 >= $this->_sizeRow($row_start)) {
6961 $width = $width + $x1 -1;
6962 $height = $height + $y1 -1;
6964 // Subtract the underlying cell widths to find the end cell of the image
6965 while ($width >= $this->_sizeCol($col_end)) {
6966 $width -= $this->_sizeCol($col_end);
6970 // Subtract the underlying cell heights to find the end cell of the image
6971 while ($height >= $this->_sizeRow($row_end)) {
6972 $height -= $this->_sizeRow($row_end);
6976 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
6977 // with zero eight or width.
6979 if ($this->_sizeCol($col_start) == 0) {
6982 if ($this->_sizeCol($col_end) == 0) {
6985 if ($this->_sizeRow($row_start) == 0) {
6988 if ($this->_sizeRow($row_end) == 0) {
6992 // Convert the pixel values to the percentage value expected by Excel
6993 $x1 = $x1 / $this->_sizeCol($col_start) * 1024;
6994 $y1 = $y1 / $this->_sizeRow($row_start) * 256;
6995 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object
6996 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object
6998 $this->_storeObjPicture($col_start, $x1,
7005 * Convert the width of a cell from user's units to pixels. By interpolation
7006 * the relationship is: y = 7x +5. If the width hasn't been set by the user we
7007 * use the default value. If the col is hidden we use a value of zero.
7010 * @param integer $col The column
7011 * @return integer The width in pixels
7013 function _sizeCol($col)
7015 // Look up the cell value to see if it has been changed
7016 if (isset($this->col_sizes[$col])) {
7017 if ($this->col_sizes[$col] == 0) {
7020 return(floor(7 * $this->col_sizes[$col] + 5));
7028 * Convert the height of a cell from user's units to pixels. By interpolation
7029 * the relationship is: y = 4/3x. If the height hasn't been set by the user we
7030 * use the default value. If the row is hidden we use a value of zero. (Not
7031 * possible to hide row yet).
7034 * @param integer $row The row
7035 * @return integer The width in pixels
7037 function _sizeRow($row)
7039 // Look up the cell value to see if it has been changed
7040 if (isset($this->_row_sizes[$row])) {
7041 if ($this->_row_sizes[$row] == 0) {
7044 return(floor(4/3 * $this->_row_sizes[$row]));
7052 * Store the OBJ record that precedes an IMDATA record. This could be generalise
7053 * to support other Excel objects.
7056 * @param integer $colL Column containing upper left corner of object
7057 * @param integer $dxL Distance from left side of cell
7058 * @param integer $rwT Row containing top left corner of object
7059 * @param integer $dyT Distance from top of cell
7060 * @param integer $colR Column containing lower right corner of object
7061 * @param integer $dxR Distance from right of cell
7062 * @param integer $rwB Row containing bottom right corner of object
7063 * @param integer $dyB Distance from bottom of cell
7065 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
7067 $record = 0x005d; // Record identifier
7068 $length = 0x003c; // Bytes to follow
7070 $cObj = 0x0001; // Count of objects in file (set to 1)
7071 $OT = 0x0008; // Object type. 8 = Picture
7072 $id = 0x0001; // Object ID
7073 $grbit = 0x0614; // Option flags
7075 $cbMacro = 0x0000; // Length of FMLA structure
7076 $Reserved1 = 0x0000; // Reserved
7077 $Reserved2 = 0x0000; // Reserved
7079 $icvBack = 0x09; // Background colour
7080 $icvFore = 0x09; // Foreground colour
7081 $fls = 0x00; // Fill pattern
7082 $fAuto = 0x00; // Automatic fill
7083 $icv = 0x08; // Line colour
7084 $lns = 0xff; // Line style
7085 $lnw = 0x01; // Line weight
7086 $fAutoB = 0x00; // Automatic border
7087 $frs = 0x0000; // Frame style
7088 $cf = 0x0009; // Image format, 9 = bitmap
7089 $Reserved3 = 0x0000; // Reserved
7090 $cbPictFmla = 0x0000; // Length of FMLA structure
7091 $Reserved4 = 0x0000; // Reserved
7092 $grbit2 = 0x0001; // Option flags
7093 $Reserved5 = 0x0000; // Reserved
7096 $header = pack("vv", $record, $length);
7097 $data = pack("V", $cObj);
7098 $data .= pack("v", $OT);
7099 $data .= pack("v", $id);
7100 $data .= pack("v", $grbit);
7101 $data .= pack("v", $colL);
7102 $data .= pack("v", $dxL);
7103 $data .= pack("v", $rwT);
7104 $data .= pack("v", $dyT);
7105 $data .= pack("v", $colR);
7106 $data .= pack("v", $dxR);
7107 $data .= pack("v", $rwB);
7108 $data .= pack("v", $dyB);
7109 $data .= pack("v", $cbMacro);
7110 $data .= pack("V", $Reserved1);
7111 $data .= pack("v", $Reserved2);
7112 $data .= pack("C", $icvBack);
7113 $data .= pack("C", $icvFore);
7114 $data .= pack("C", $fls);
7115 $data .= pack("C", $fAuto);
7116 $data .= pack("C", $icv);
7117 $data .= pack("C", $lns);
7118 $data .= pack("C", $lnw);
7119 $data .= pack("C", $fAutoB);
7120 $data .= pack("v", $frs);
7121 $data .= pack("V", $cf);
7122 $data .= pack("v", $Reserved3);
7123 $data .= pack("v", $cbPictFmla);
7124 $data .= pack("v", $Reserved4);
7125 $data .= pack("v", $grbit2);
7126 $data .= pack("V", $Reserved5);
7128 $this->_append($header . $data);
7132 * Convert a 24 bit bitmap into the modified internal format used by Windows.
7133 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
7137 * @param string $bitmap The bitmap to process
7138 * @return array Array with data and properties of the bitmap
7140 function _processBitmap($bitmap)
7143 $bmp_fd = @fopen($bitmap,"rb");
7145 die("Couldn't import $bitmap");
7148 // Slurp the file into a string.
7149 $data = fread($bmp_fd, filesize($bitmap));
7151 // Check that the file is big enough to be a bitmap.
7152 if (strlen($data) <= 0x36) {
7153 die("$bitmap doesn't contain enough data.\n");
7156 // The first 2 bytes are used to identify the bitmap.
7157 $identity = unpack("A2ident", $data);
7158 if ($identity['ident'] != "BM") {
7159 die("$bitmap doesn't appear to be a valid bitmap image.\n");
7162 // Remove bitmap data: ID.
7163 $data = substr($data, 2);
7165 // Read and remove the bitmap size. This is more reliable than reading
7166 // the data size at offset 0x22.
7168 $size_array = unpack("Vsa", substr($data, 0, 4));
7169 $size = $size_array['sa'];
7170 $data = substr($data, 4);
7171 $size -= 0x36; // Subtract size of bitmap header.
7172 $size += 0x0C; // Add size of BIFF header.
7174 // Remove bitmap data: reserved, offset, header length.
7175 $data = substr($data, 12);
7177 // Read and remove the bitmap width and height. Verify the sizes.
7178 $width_and_height = unpack("V2", substr($data, 0, 8));
7179 $width = $width_and_height[1];
7180 $height = $width_and_height[2];
7181 $data = substr($data, 8);
7182 if ($width > 0xFFFF) {
7183 die("$bitmap: largest image width supported is 65k.\n");
7185 if ($height > 0xFFFF) {
7186 die("$bitmap: largest image height supported is 65k.\n");
7189 // Read and remove the bitmap planes and bpp data. Verify them.
7190 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
7191 $data = substr($data, 4);
7192 if ($planes_and_bitcount[2] != 24) { // Bitcount
7193 die("$bitmap isn't a 24bit true color bitmap.\n");
7195 if ($planes_and_bitcount[1] != 1) {
7196 die("$bitmap: only 1 plane supported in bitmap image.\n");
7199 // Read and remove the bitmap compression. Verify compression.
7200 $compression = unpack("Vcomp", substr($data, 0, 4));
7201 $data = substr($data, 4);
7204 if ($compression['comp'] != 0) {
7205 die("$bitmap: compression not supported in bitmap image.\n");
7208 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
7209 $data = substr($data, 20);
7211 // Add the BITMAPCOREHEADER data
7212 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
7213 $data = $header . $data;
7215 return (array($width, $height, $size, $data));
7219 * Store the window zoom factor. This should be a reduced fraction but for
7220 * simplicity we will store all fractions with a numerator of 100.
7224 function _storeZoom()
7226 // If scale is 100 we don't need to write a record
7227 if ($this->_zoom == 100) {
7231 $record = 0x00A0; // Record identifier
7232 $length = 0x0004; // Bytes to follow
7234 $header = pack("vv", $record, $length);
7235 $data = pack("vv", $this->_zoom, 100);
7236 $this->_append($header . $data);
7240 * FIXME: add comments
7242 function setValidation($row1, $col1, $row2, $col2, &$validator)
7244 $this->_dv[] = $validator->_getData() .
7245 pack("vvvvv", 1, $row1, $row2, $col1, $col2);
7249 * Store the DVAL and DV records.
7253 function _storeDataValidity()
7255 $record = 0x01b2; // Record identifier
7256 $length = 0x0012; // Bytes to follow
7258 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
7259 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
7260 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
7261 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible
7263 $header = pack('vv', $record, $length);
7264 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
7266 $this->_append($header.$data);
7268 $record = 0x01be; // Record identifier
7269 foreach ($this->_dv as $dv) {
7270 $length = strlen($dv); // Bytes to follow
7271 $header = pack("vv", $record, $length);
7272 $this->_append($header . $dv);
7278 * Class for generating Excel Spreadsheets
7280 * @author Xavier Noguer <xnoguer@rezebra.com>
7281 * @category FileFormats
7282 * @package Spreadsheet_Excel_Writer
7285 class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter
7288 * Filename for the Workbook
7295 * @var object Parser
7300 * Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
7306 * The active worksheet of the workbook (0 indexed)
7312 * 1st displayed worksheet in the workbook (0 indexed)
7318 * Number of workbook tabs selected
7324 * Index for creating adding new formats to the workbook
7330 * Flag for preventing close from being called twice.
7337 * The BIFF file size for the workbook.
7339 * @see _calcSheetOffsets()
7344 * The default sheetname for all sheets created.
7350 * The default XF format.
7351 * @var object Format
7356 * Array containing references to all of this workbook's worksheets
7362 * Array of sheetnames for creating the EXTERNSHEET records
7368 * Array containing references to all of this workbook's formats
7374 * Array containing the colour palette
7380 * The default format for URLs.
7381 * @var object Format
7386 * The codepage indicates the text encoding used for strings
7392 * The country code used for localization
7398 * The temporary dir for storing the OLE file
7404 * number of bytes for sizeinfo of strings
7407 var $_string_sizeinfo_size;
7412 * @param string filename for storing the workbook. "-" for writing to stdout.
7415 function __construct($filename)
7417 // It needs to call its parent's constructor explicitly
7418 parent::__construct();
7420 $this->_filename = $filename;
7421 $this->_parser = new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);
7423 $this->_activesheet = 0;
7424 $this->_firstsheet = 0;
7425 $this->_selected = 0;
7426 $this->_xf_index = 16; // 15 style XF's and 1 cell XF.
7427 $this->_fileclosed = 0;
7428 $this->_biffsize = 0;
7429 $this->_sheetname = 'Sheet';
7430 $this->_tmp_format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);
7431 $this->_worksheets = array();
7432 $this->_sheetnames = array();
7433 $this->_formats = array();
7434 $this->_palette = array();
7435 $this->_codepage = 0x04E4; // FIXME: should change for BIFF8
7436 $this->_country_code = -1;
7437 $this->_string_sizeinfo = 3;
7439 // Add the default format for hyperlinks
7440 $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
7441 $this->_str_total = 0;
7442 $this->_str_unique = 0;
7443 $this->_str_table = array();
7444 $this->_setPaletteXl97();
7445 $this->_tmp_dir = '';
7449 * Calls finalization methods.
7450 * This method should always be the last one to be called on every workbook
7453 * @return mixed true on success. PEAR_Error on failure
7457 if ($this->_fileclosed) { // Prevent close() from being called twice.
7460 $this->_storeWorkbook();
7461 $this->_fileclosed = 1;
7466 * An accessor for the _worksheets[] array
7467 * Returns an array of the worksheet objects in a workbook
7468 * It actually calls to worksheets()
7476 return $this->worksheets();
7480 * An accessor for the _worksheets[] array.
7481 * Returns an array of the worksheet objects in a workbook
7486 function worksheets()
7488 return $this->_worksheets;
7492 * Sets the BIFF version.
7493 * This method exists just to access experimental functionality
7494 * from BIFF8. It will be deprecated !
7495 * Only possible value is 8 (Excel 97/2000).
7496 * For any other value it fails silently.
7499 * @param integer $version The BIFF version
7501 function setVersion($version)
7503 if ($version == 8) { // only accept version 8
7505 $this->_BIFF_version = $version;
7506 // change BIFFwriter limit for CONTINUE records
7507 $this->_limit = 8228;
7508 $this->_tmp_format->_BIFF_version = $version;
7509 $this->_url_format->_BIFF_version = $version;
7510 $this->_parser->_BIFF_version = $version;
7512 $total_worksheets = count($this->_worksheets);
7513 // change version for all worksheets too
7514 for ($i = 0; $i < $total_worksheets; $i++) {
7515 $this->_worksheets[$i]->_BIFF_version = $version;
7518 $total_formats = count($this->_formats);
7519 // change version for all formats too
7520 for ($i = 0; $i < $total_formats; $i++) {
7521 $this->_formats[$i]->_BIFF_version = $version;
7527 * Set the country identifier for the workbook
7530 * @param integer $code Is the international calling country code for the
7533 function setCountry($code)
7535 $this->_country_code = $code;
7539 * Add a new worksheet to the Excel workbook.
7540 * If no name is given the name of the worksheet will be Sheeti$i, with
7544 * @param string $name the optional name of the worksheet
7545 * @return mixed reference to a worksheet object on success, PEAR_Error
7548 function &addWorksheet($name = '')
7550 $index = count($this->_worksheets);
7551 $sheetname = $this->_sheetname;
7554 $name = $sheetname.($index+1);
7557 // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
7558 if ($this->_BIFF_version != 0x0600)
7560 if (strlen($name) > 31) {
7561 die("Sheetname $name must be <= 31 chars");
7565 // Check that the worksheet name doesn't already exist: a fatal Excel error.
7566 $total_worksheets = count($this->_worksheets);
7567 for ($i = 0; $i < $total_worksheets; $i++) {
7568 if ($this->_worksheets[$i]->getName() == $name) {
7569 die("Worksheet '$name' already exists");
7573 $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
7575 $this->_activesheet, $this->_firstsheet,
7576 $this->_str_total, $this->_str_unique,
7577 $this->_str_table, $this->_url_format,
7580 $this->_worksheets[$index] = &$worksheet; // Store ref for iterator
7581 $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names
7582 $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser
7587 * Add a new format to the Excel workbook.
7588 * Also, pass any properties to the Format constructor.
7591 * @param array $properties array with properties for initializing the format.
7592 * @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format
7594 function &addFormat($properties = array())
7596 $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
7597 $this->_xf_index += 1;
7598 $this->_formats[] = &$format;
7603 * Create new validator.
7606 * @return &Spreadsheet_Excel_Writer_Validator reference to a Validator
7608 function &addValidator()
7610 include_once 'Spreadsheet/Excel/Writer/Validator.php';
7611 /* FIXME: check for successful inclusion*/
7612 $valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);
7617 * Change the RGB components of the elements in the colour palette.
7620 * @param integer $index colour index
7621 * @param integer $red red RGB value [0-255]
7622 * @param integer $green green RGB value [0-255]
7623 * @param integer $blue blue RGB value [0-255]
7624 * @return integer The palette index for the custom color
7626 function setCustomColor($index, $red, $green, $blue)
7628 // Match a HTML #xxyyzz style parameter
7629 /*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
7630 @_ = ($_[0], hex $1, hex $2, hex $3);
7633 // Check that the colour index is the right range
7634 if ($index < 8 or $index > 64) {
7635 // TODO: assign real error codes
7636 die("Color index $index outside range: 8 <= index <= 64");
7639 // Check that the colour components are in the right range
7640 if (($red < 0 or $red > 255) ||
7641 ($green < 0 or $green > 255) ||
7642 ($blue < 0 or $blue > 255))
7644 die("Color component outside range: 0 <= color <= 255");
7647 $index -= 8; // Adjust colour index (wingless dragonfly)
7649 // Set the RGB value
7650 $this->_palette[$index] = array($red, $green, $blue, 0);
7655 * Sets the colour palette to the Excel 97+ default.
7659 function _setPaletteXl97()
7661 $this->_palette = array(
7662 array(0x00, 0x00, 0x00, 0x00), // 8
7663 array(0xff, 0xff, 0xff, 0x00), // 9
7664 array(0xff, 0x00, 0x00, 0x00), // 10
7665 array(0x00, 0xff, 0x00, 0x00), // 11
7666 array(0x00, 0x00, 0xff, 0x00), // 12
7667 array(0xff, 0xff, 0x00, 0x00), // 13
7668 array(0xff, 0x00, 0xff, 0x00), // 14
7669 array(0x00, 0xff, 0xff, 0x00), // 15
7670 array(0x80, 0x00, 0x00, 0x00), // 16
7671 array(0x00, 0x80, 0x00, 0x00), // 17
7672 array(0x00, 0x00, 0x80, 0x00), // 18
7673 array(0x80, 0x80, 0x00, 0x00), // 19
7674 array(0x80, 0x00, 0x80, 0x00), // 20
7675 array(0x00, 0x80, 0x80, 0x00), // 21
7676 array(0xc0, 0xc0, 0xc0, 0x00), // 22
7677 array(0x80, 0x80, 0x80, 0x00), // 23
7678 array(0x99, 0x99, 0xff, 0x00), // 24
7679 array(0x99, 0x33, 0x66, 0x00), // 25
7680 array(0xff, 0xff, 0xcc, 0x00), // 26
7681 array(0xcc, 0xff, 0xff, 0x00), // 27
7682 array(0x66, 0x00, 0x66, 0x00), // 28
7683 array(0xff, 0x80, 0x80, 0x00), // 29
7684 array(0x00, 0x66, 0xcc, 0x00), // 30
7685 array(0xcc, 0xcc, 0xff, 0x00), // 31
7686 array(0x00, 0x00, 0x80, 0x00), // 32
7687 array(0xff, 0x00, 0xff, 0x00), // 33
7688 array(0xff, 0xff, 0x00, 0x00), // 34
7689 array(0x00, 0xff, 0xff, 0x00), // 35
7690 array(0x80, 0x00, 0x80, 0x00), // 36
7691 array(0x80, 0x00, 0x00, 0x00), // 37
7692 array(0x00, 0x80, 0x80, 0x00), // 38
7693 array(0x00, 0x00, 0xff, 0x00), // 39
7694 array(0x00, 0xcc, 0xff, 0x00), // 40
7695 array(0xcc, 0xff, 0xff, 0x00), // 41
7696 array(0xcc, 0xff, 0xcc, 0x00), // 42
7697 array(0xff, 0xff, 0x99, 0x00), // 43
7698 array(0x99, 0xcc, 0xff, 0x00), // 44
7699 array(0xff, 0x99, 0xcc, 0x00), // 45
7700 array(0xcc, 0x99, 0xff, 0x00), // 46
7701 array(0xff, 0xcc, 0x99, 0x00), // 47
7702 array(0x33, 0x66, 0xff, 0x00), // 48
7703 array(0x33, 0xcc, 0xcc, 0x00), // 49
7704 array(0x99, 0xcc, 0x00, 0x00), // 50
7705 array(0xff, 0xcc, 0x00, 0x00), // 51
7706 array(0xff, 0x99, 0x00, 0x00), // 52
7707 array(0xff, 0x66, 0x00, 0x00), // 53
7708 array(0x66, 0x66, 0x99, 0x00), // 54
7709 array(0x96, 0x96, 0x96, 0x00), // 55
7710 array(0x00, 0x33, 0x66, 0x00), // 56
7711 array(0x33, 0x99, 0x66, 0x00), // 57
7712 array(0x00, 0x33, 0x00, 0x00), // 58
7713 array(0x33, 0x33, 0x00, 0x00), // 59
7714 array(0x99, 0x33, 0x00, 0x00), // 60
7715 array(0x99, 0x33, 0x66, 0x00), // 61
7716 array(0x33, 0x33, 0x99, 0x00), // 62
7717 array(0x33, 0x33, 0x33, 0x00), // 63
7722 * Assemble worksheets into a workbook and send the BIFF data to an OLE
7726 * @return mixed true on success. PEAR_Error on failure
7728 function _storeWorkbook()
7730 // Ensure that at least one worksheet has been selected.
7731 if ($this->_activesheet == 0) {
7732 $this->_worksheets[0]->selected = 1;
7735 // Calculate the number of selected worksheet tabs and call the finalization
7736 // methods for each worksheet
7737 $total_worksheets = count($this->_worksheets);
7738 for ($i = 0; $i < $total_worksheets; $i++) {
7739 if ($this->_worksheets[$i]->selected) {
7742 $this->_worksheets[$i]->close($this->_sheetnames);
7745 // Add Workbook globals
7746 $this->_storeBof(0x0005);
7747 $this->_storeCodepage();
7748 if ($this->_BIFF_version == 0x0600) {
7749 $this->_storeWindow1();
7751 if ($this->_BIFF_version == 0x0500) {
7752 $this->_storeExterns(); // For print area and repeat rows
7754 $this->_storeNames(); // For print area and repeat rows
7755 if ($this->_BIFF_version == 0x0500) {
7756 $this->_storeWindow1();
7758 $this->_storeDatemode();
7759 $this->_storeAllFonts();
7760 $this->_storeAllNumFormats();
7761 $this->_storeAllXfs();
7762 $this->_storeAllStyles();
7763 $this->_storePalette();
7764 $this->_calcSheetOffsets();
7766 // Add BOUNDSHEET records
7767 for ($i = 0; $i < $total_worksheets; $i++) {
7768 $this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);
7771 if ($this->_country_code != -1) {
7772 $this->_storeCountry();
7775 if ($this->_BIFF_version == 0x0600) {
7776 //$this->_storeSupbookInternal();
7777 /* TODO: store external SUPBOOK records and XCT and CRN records
7778 in case of external references for BIFF8 */
7779 //$this->_storeExternsheetBiff8();
7780 $this->_storeSharedStringsTable();
7783 // End Workbook globals
7786 // Store the workbook in an OLE container
7787 $res = $this->_storeOLEFile();
7792 * Sets the temp dir used for storing the OLE file
7795 * @param string $dir The dir to be used as temp dir
7796 * @return true if given dir is valid, false otherwise
7798 function setTempDir($dir)
7801 $this->_tmp_dir = $dir;
7808 * Store the workbook in an OLE container
7811 * @return mixed true on success. PEAR_Error on failure
7813 function _storeOLEFile()
7815 if($this->_BIFF_version == 0x0600) {
7816 $OLE = new ole_pps_file(Asc2Ucs('Workbook'));
7818 $OLE = new ole_pps_file(Asc2Ucs('Book'));
7821 $OLE->append($this->_data);
7823 $total_worksheets = count($this->_worksheets);
7824 for ($i = 0; $i < $total_worksheets; $i++) {
7825 while ($tmp = $this->_worksheets[$i]->getData()) {
7830 $root = new ole_pps_root(false, false, array($OLE));
7832 $root->save($this->_filename);
7836 * Calculate offsets for Worksheet BOF records.
7840 function _calcSheetOffsets()
7842 if ($this->_BIFF_version == 0x0600) {
7843 $boundsheet_length = 12; // fixed length for a BOUNDSHEET record
7845 $boundsheet_length = 11;
7848 $offset = $this->_datasize;
7850 if ($this->_BIFF_version == 0x0600) {
7851 // add the length of the SST
7852 /* TODO: check this works for a lot of strings (> 8224 bytes) */
7853 $offset += $this->_calculateSharedStringsSizes();
7854 if ($this->_country_code != -1) {
7855 $offset += 8; // adding COUNTRY record
7857 // add the lenght of SUPBOOK, EXTERNSHEET and NAME records
7858 //$offset += 8; // FIXME: calculate real value when storing the records
7860 $total_worksheets = count($this->_worksheets);
7861 // add the length of the BOUNDSHEET records
7862 for ($i = 0; $i < $total_worksheets; $i++) {
7863 $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
7867 for ($i = 0; $i < $total_worksheets; $i++) {
7868 $this->_worksheets[$i]->offset = $offset;
7869 $offset += $this->_worksheets[$i]->_datasize;
7871 $this->_biffsize = $offset;
7875 * Store the Excel FONT records.
7879 function _storeAllFonts()
7881 // tmp_format is added by the constructor. We use this to write the default XF's
7882 $format = $this->_tmp_format;
7883 $font = $format->getFont();
7885 // Note: Fonts are 0-indexed. According to the SDK there is no index 4,
7886 // so the following fonts are 0, 1, 2, 3, 5
7888 for ($i = 1; $i <= 5; $i++){
7889 $this->_append($font);
7892 // Iterate through the XF objects and write a FONT record if it isn't the
7893 // same as the default FONT and if it hasn't already been used.
7896 $index = 6; // The first user defined FONT
7898 $key = $format->getFontKey(); // The default font from _tmp_format
7899 $fonts[$key] = 0; // Index of the default font
7901 $total_formats = count($this->_formats);
7902 for ($i = 0; $i < $total_formats; $i++) {
7903 $key = $this->_formats[$i]->getFontKey();
7904 if (isset($fonts[$key])) {
7905 // FONT has already been used
7906 $this->_formats[$i]->font_index = $fonts[$key];
7908 // Add a new FONT record
7909 $fonts[$key] = $index;
7910 $this->_formats[$i]->font_index = $index;
7912 $font = $this->_formats[$i]->getFont();
7913 $this->_append($font);
7919 * Store user defined numerical formats i.e. FORMAT records
7923 function _storeAllNumFormats()
7925 // Leaning num_format syndrome
7926 $hash_num_formats = array();
7927 $num_formats = array();
7930 // Iterate through the XF objects and write a FORMAT record if it isn't a
7931 // built-in format type and if the FORMAT string hasn't already been used.
7932 $total_formats = count($this->_formats);
7933 for ($i = 0; $i < $total_formats; $i++) {
7934 $num_format = $this->_formats[$i]->_num_format;
7936 // Check if $num_format is an index to a built-in format.
7937 // Also check for a string of zeros, which is a valid format string
7938 // but would evaluate to zero.
7940 if (!preg_match("/^0+\d/", $num_format)) {
7941 if (preg_match("/^\d+$/", $num_format)) { // built-in format
7946 if (isset($hash_num_formats[$num_format])) {
7947 // FORMAT has already been used
7948 $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
7951 $hash_num_formats[$num_format] = $index;
7952 $this->_formats[$i]->_num_format = $index;
7953 array_push($num_formats,$num_format);
7958 // Write the new FORMAT records starting from 0xA4
7960 foreach ($num_formats as $num_format) {
7961 $this->_storeNumFormat($num_format,$index);
7967 * Write all XF records.
7971 function _storeAllXfs()
7973 // _tmp_format is added by the constructor. We use this to write the default XF's
7974 // The default font index is 0
7976 $format = $this->_tmp_format;
7977 for ($i = 0; $i <= 14; $i++) {
7978 $xf = $format->getXf('style'); // Style XF
7979 $this->_append($xf);
7982 $xf = $format->getXf('cell'); // Cell XF
7983 $this->_append($xf);
7986 $total_formats = count($this->_formats);
7987 for ($i = 0; $i < $total_formats; $i++) {
7988 $xf = $this->_formats[$i]->getXf('cell');
7989 $this->_append($xf);
7994 * Write all STYLE records.
7998 function _storeAllStyles()
8000 $this->_storeStyle();
8004 * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
8009 function _storeExterns()
8011 // Create EXTERNCOUNT with number of worksheets
8012 $this->_storeExterncount(count($this->_worksheets));
8014 // Create EXTERNSHEET for each worksheet
8015 foreach ($this->_sheetnames as $sheetname) {
8016 $this->_storeExternsheet($sheetname);
8021 * Write the NAME record to define the print area and the repeat rows and cols.
8025 function _storeNames()
8027 // Create the print area NAME records
8028 $total_worksheets = count($this->_worksheets);
8029 for ($i = 0; $i < $total_worksheets; $i++) {
8030 // Write a Name record if the print area has been defined
8031 if (isset($this->_worksheets[$i]->print_rowmin)) {
8032 $this->_storeNameShort(
8033 $this->_worksheets[$i]->index,
8035 $this->_worksheets[$i]->print_rowmin,
8036 $this->_worksheets[$i]->print_rowmax,
8037 $this->_worksheets[$i]->print_colmin,
8038 $this->_worksheets[$i]->print_colmax
8043 // Create the print title NAME records
8044 $total_worksheets = count($this->_worksheets);
8045 for ($i = 0; $i < $total_worksheets; $i++) {
8046 $rowmin = $this->_worksheets[$i]->title_rowmin;
8047 $rowmax = $this->_worksheets[$i]->title_rowmax;
8048 $colmin = $this->_worksheets[$i]->title_colmin;
8049 $colmax = $this->_worksheets[$i]->title_colmax;
8051 // Determine if row + col, row, col or nothing has been defined
8052 // and write the appropriate record
8054 if (isset($rowmin) && isset($colmin)) {
8055 // Row and column titles have been defined.
8056 // Row title has been defined.
8057 $this->_storeNameLong(
8058 $this->_worksheets[$i]->index,
8065 } elseif (isset($rowmin)) {
8066 // Row title has been defined.
8067 $this->_storeNameShort(
8068 $this->_worksheets[$i]->index,
8075 } elseif (isset($colmin)) {
8076 // Column title has been defined.
8077 $this->_storeNameShort(
8078 $this->_worksheets[$i]->index,
8086 // Print title hasn't been defined.
8094 /******************************************************************************
8101 * Stores the CODEPAGE biff record.
8105 function _storeCodepage()
8107 $record = 0x0042; // Record identifier
8108 $length = 0x0002; // Number of bytes to follow
8109 $cv = $this->_codepage; // The code page
8111 $header = pack('vv', $record, $length);
8112 $data = pack('v', $cv);
8114 $this->_append($header . $data);
8118 * Write Excel BIFF WINDOW1 record.
8122 function _storeWindow1()
8124 $record = 0x003D; // Record identifier
8125 $length = 0x0012; // Number of bytes to follow
8127 $xWn = 0x0000; // Horizontal position of window
8128 $yWn = 0x0000; // Vertical position of window
8129 $dxWn = 0x25BC; // Width of window
8130 $dyWn = 0x1572; // Height of window
8132 $grbit = 0x0038; // Option flags
8133 $ctabsel = $this->_selected; // Number of workbook tabs selected
8134 $wTabRatio = 0x0258; // Tab to scrollbar ratio
8136 $itabFirst = $this->_firstsheet; // 1st displayed worksheet
8137 $itabCur = $this->_activesheet; // Active worksheet
8139 $header = pack("vv", $record, $length);
8140 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
8142 $itabCur, $itabFirst,
8143 $ctabsel, $wTabRatio);
8144 $this->_append($header . $data);
8148 * Writes Excel BIFF BOUNDSHEET record.
8149 * FIXME: inconsistent with BIFF documentation
8151 * @param string $sheetname Worksheet name
8152 * @param integer $offset Location of worksheet BOF
8155 function _storeBoundsheet($sheetname,$offset)
8157 $record = 0x0085; // Record identifier
8159 if ($this->_BIFF_version == 0x0600) // Tried to fix the correct handling here, with the
8160 { // corrected specification from M$ - Joe Hunt 2009-03-08
8161 $encoding_string = $this->_input_encoding;
8162 if ($encoding_string == 'UTF-16LE')
8164 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
8167 else if ($encoding_string != '')
8169 $sheetname = iconv($encoding_string, 'UTF-16LE', $sheetname);
8170 $strlen = function_exists('mb_strlen') ? mb_strlen($sheetname, 'UTF-16LE') : (strlen($sheetname) / 2);
8173 if ($strlen % 2 != 0)
8177 //$strlen = strlen($sheetname);
8178 $length = 0x08 + $strlen; // Number of bytes to follow
8180 $strlen = strlen($sheetname);
8181 $length = 0x07 + $strlen; // Number of bytes to follow
8184 $grbit = 0x0000; // Visibility and sheet type
8185 $cch = $strlen; // Length of sheet name
8187 $header = pack("vv", $record, $length);
8188 if ($this->_BIFF_version == 0x0600) {
8189 $data = pack("VvCC", $offset, $grbit, $cch, $encoding);
8191 $data = pack("VvC", $offset, $grbit, $cch);
8194 if ($this->_BIFF_version == 0x0600)
8196 $strlen = strlen($sheetname);
8197 $length = 0x08 + $strlen; // Number of bytes to follow
8199 $strlen = strlen($sheetname);
8200 $length = 0x07 + $strlen; // Number of bytes to follow
8203 $grbit = 0x0000; // Visibility and sheet type
8204 $cch = $strlen; // Length of sheet name
8206 $header = pack("vv", $record, $length);
8207 if ($this->_BIFF_version == 0x0600) {
8208 $data = pack("Vvv", $offset, $grbit, $cch);
8210 $data = pack("VvC", $offset, $grbit, $cch);
8212 $this->_append($header.$data.$sheetname);
8216 * Write Internal SUPBOOK record
8220 function _storeSupbookInternal()
8222 $record = 0x01AE; // Record identifier
8223 $length = 0x0004; // Bytes to follow
8225 $header = pack("vv", $record, $length);
8226 $data = pack("vv", count($this->_worksheets), 0x0104);
8227 $this->_append($header . $data);
8231 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
8234 * @param string $sheetname Worksheet name
8237 function _storeExternsheetBiff8()
8239 $total_references = count($this->_parser->_references);
8240 $record = 0x0017; // Record identifier
8241 $length = 2 + 6 * $total_references; // Number of bytes to follow
8243 $supbook_index = 0; // FIXME: only using internal SUPBOOK record
8244 $header = pack("vv", $record, $length);
8245 $data = pack('v', $total_references);
8246 for ($i = 0; $i < $total_references; $i++) {
8247 $data .= $this->_parser->_references[$i];
8249 $this->_append($header . $data);
8253 * Write Excel BIFF STYLE records.
8257 function _storeStyle()
8259 $record = 0x0293; // Record identifier
8260 $length = 0x0004; // Bytes to follow
8262 $ixfe = 0x8000; // Index to style XF
8263 $BuiltIn = 0x00; // Built-in style
8264 $iLevel = 0xff; // Outline style level
8266 $header = pack("vv", $record, $length);
8267 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
8268 $this->_append($header . $data);
8273 * Writes Excel FORMAT record for non "built-in" numerical formats.
8275 * @param string $format Custom format string
8276 * @param integer $ifmt Format index code
8279 function _storeNumFormat($format, $ifmt)
8281 $record = 0x041E; // Record identifier
8283 if ($this->_BIFF_version == 0x0600) {
8284 $length = 5 + strlen($format); // Number of bytes to follow
8286 } elseif ($this->_BIFF_version == 0x0500) {
8287 $length = 3 + strlen($format); // Number of bytes to follow
8290 $cch = strlen($format); // Length of format string
8292 $header = pack("vv", $record, $length);
8293 if ($this->_BIFF_version == 0x0600) {
8294 $data = pack("vvC", $ifmt, $cch, $encoding);
8295 } elseif ($this->_BIFF_version == 0x0500) {
8296 $data = pack("vC", $ifmt, $cch);
8298 $this->_append($header . $data . $format);
8302 * Write DATEMODE record to indicate the date system in use (1904 or 1900).
8306 function _storeDatemode()
8308 $record = 0x0022; // Record identifier
8309 $length = 0x0002; // Bytes to follow
8311 $f1904 = $this->_1904; // Flag for 1904 date system
8313 $header = pack("vv", $record, $length);
8314 $data = pack("v", $f1904);
8315 $this->_append($header . $data);
8320 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
8321 * references in the workbook.
8323 * Excel only stores references to external sheets that are used in NAME.
8324 * The workbook NAME record is required to define the print area and the repeat
8327 * A similar method is used in Worksheet.php for a slightly different purpose.
8329 * @param integer $cxals Number of external references
8332 function _storeExterncount($cxals)
8334 $record = 0x0016; // Record identifier
8335 $length = 0x0002; // Number of bytes to follow
8337 $header = pack("vv", $record, $length);
8338 $data = pack("v", $cxals);
8339 $this->_append($header . $data);
8344 * Writes the Excel BIFF EXTERNSHEET record. These references are used by
8345 * formulas. NAME record is required to define the print area and the repeat
8348 * A similar method is used in Worksheet.php for a slightly different purpose.
8350 * @param string $sheetname Worksheet name
8353 function _storeExternsheet($sheetname)
8355 $record = 0x0017; // Record identifier
8356 $length = 0x02 + strlen($sheetname); // Number of bytes to follow
8358 $cch = strlen($sheetname); // Length of sheet name
8359 $rgch = 0x03; // Filename encoding
8361 $header = pack("vv", $record, $length);
8362 $data = pack("CC", $cch, $rgch);
8363 $this->_append($header . $data . $sheetname);
8368 * Store the NAME record in the short format that is used for storing the print
8369 * area, repeat rows only and repeat columns only.
8371 * @param integer $index Sheet index
8372 * @param integer $type Built-in name type
8373 * @param integer $rowmin Start row
8374 * @param integer $rowmax End row
8375 * @param integer $colmin Start colum
8376 * @param integer $colmax End column
8379 function _storeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
8381 $record = 0x0018; // Record identifier
8382 $length = 0x0024; // Number of bytes to follow
8384 $grbit = 0x0020; // Option flags
8385 $chKey = 0x00; // Keyboard shortcut
8386 $cch = 0x01; // Length of text name
8387 $cce = 0x0015; // Length of text definition
8388 $ixals = $index + 1; // Sheet index
8389 $itab = $ixals; // Equal to ixals
8390 $cchCustMenu = 0x00; // Length of cust menu text
8391 $cchDescription = 0x00; // Length of description text
8392 $cchHelptopic = 0x00; // Length of help topic text
8393 $cchStatustext = 0x00; // Length of status bar text
8394 $rgch = $type; // Built-in name type
8397 $unknown04 = 0xffff-$index;
8398 $unknown05 = 0x0000;
8399 $unknown06 = 0x0000;
8400 $unknown07 = 0x1087;
8401 $unknown08 = 0x8005;
8403 $header = pack("vv", $record, $length);
8404 $data = pack("v", $grbit);
8405 $data .= pack("C", $chKey);
8406 $data .= pack("C", $cch);
8407 $data .= pack("v", $cce);
8408 $data .= pack("v", $ixals);
8409 $data .= pack("v", $itab);
8410 $data .= pack("C", $cchCustMenu);
8411 $data .= pack("C", $cchDescription);
8412 $data .= pack("C", $cchHelptopic);
8413 $data .= pack("C", $cchStatustext);
8414 $data .= pack("C", $rgch);
8415 $data .= pack("C", $unknown03);
8416 $data .= pack("v", $unknown04);
8417 $data .= pack("v", $unknown05);
8418 $data .= pack("v", $unknown06);
8419 $data .= pack("v", $unknown07);
8420 $data .= pack("v", $unknown08);
8421 $data .= pack("v", $index);
8422 $data .= pack("v", $index);
8423 $data .= pack("v", $rowmin);
8424 $data .= pack("v", $rowmax);
8425 $data .= pack("C", $colmin);
8426 $data .= pack("C", $colmax);
8427 $this->_append($header . $data);
8432 * Store the NAME record in the long format that is used for storing the repeat
8433 * rows and columns when both are specified. This shares a lot of code with
8434 * _storeNameShort() but we use a separate method to keep the code clean.
8435 * Code abstraction for reuse can be carried too far, and I should know. ;-)
8437 * @param integer $index Sheet index
8438 * @param integer $type Built-in name type
8439 * @param integer $rowmin Start row
8440 * @param integer $rowmax End row
8441 * @param integer $colmin Start colum
8442 * @param integer $colmax End column
8445 function _storeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
8447 $record = 0x0018; // Record identifier
8448 $length = 0x003d; // Number of bytes to follow
8449 $grbit = 0x0020; // Option flags
8450 $chKey = 0x00; // Keyboard shortcut
8451 $cch = 0x01; // Length of text name
8452 $cce = 0x002e; // Length of text definition
8453 $ixals = $index + 1; // Sheet index
8454 $itab = $ixals; // Equal to ixals
8455 $cchCustMenu = 0x00; // Length of cust menu text
8456 $cchDescription = 0x00; // Length of description text
8457 $cchHelptopic = 0x00; // Length of help topic text
8458 $cchStatustext = 0x00; // Length of status bar text
8459 $rgch = $type; // Built-in name type
8462 $unknown02 = 0x002b;
8464 $unknown04 = 0xffff-$index;
8465 $unknown05 = 0x0000;
8466 $unknown06 = 0x0000;
8467 $unknown07 = 0x1087;
8468 $unknown08 = 0x8008;
8470 $header = pack("vv", $record, $length);
8471 $data = pack("v", $grbit);
8472 $data .= pack("C", $chKey);
8473 $data .= pack("C", $cch);
8474 $data .= pack("v", $cce);
8475 $data .= pack("v", $ixals);
8476 $data .= pack("v", $itab);
8477 $data .= pack("C", $cchCustMenu);
8478 $data .= pack("C", $cchDescription);
8479 $data .= pack("C", $cchHelptopic);
8480 $data .= pack("C", $cchStatustext);
8481 $data .= pack("C", $rgch);
8482 $data .= pack("C", $unknown01);
8483 $data .= pack("v", $unknown02);
8484 // Column definition
8485 $data .= pack("C", $unknown03);
8486 $data .= pack("v", $unknown04);
8487 $data .= pack("v", $unknown05);
8488 $data .= pack("v", $unknown06);
8489 $data .= pack("v", $unknown07);
8490 $data .= pack("v", $unknown08);
8491 $data .= pack("v", $index);
8492 $data .= pack("v", $index);
8493 $data .= pack("v", 0x0000);
8494 $data .= pack("v", 0x3fff);
8495 $data .= pack("C", $colmin);
8496 $data .= pack("C", $colmax);
8498 $data .= pack("C", $unknown03);
8499 $data .= pack("v", $unknown04);
8500 $data .= pack("v", $unknown05);
8501 $data .= pack("v", $unknown06);
8502 $data .= pack("v", $unknown07);
8503 $data .= pack("v", $unknown08);
8504 $data .= pack("v", $index);
8505 $data .= pack("v", $index);
8506 $data .= pack("v", $rowmin);
8507 $data .= pack("v", $rowmax);
8508 $data .= pack("C", 0x00);
8509 $data .= pack("C", 0xff);
8511 $data .= pack("C", 0x10);
8512 $this->_append($header . $data);
8516 * Stores the COUNTRY record for localization
8520 function _storeCountry()
8522 $record = 0x008C; // Record identifier
8523 $length = 4; // Number of bytes to follow
8525 $header = pack('vv', $record, $length);
8526 /* using the same country code always for simplicity */
8527 $data = pack('vv', $this->_country_code, $this->_country_code);
8528 $this->_append($header . $data);
8532 * Stores the PALETTE biff record.
8536 function _storePalette()
8538 $aref = $this->_palette;
8540 $record = 0x0092; // Record identifier
8541 $length = 2 + 4 * count($aref); // Number of bytes to follow
8542 $ccv = count($aref); // Number of RGB values to follow
8543 $data = ''; // The RGB data
8545 // Pack the RGB data
8546 foreach ($aref as $color) {
8547 foreach ($color as $byte) {
8548 $data .= pack("C",$byte);
8552 $header = pack("vvv", $record, $length, $ccv);
8553 $this->_append($header . $data);
8558 * Handling of the SST continue blocks is complicated by the need to include an
8559 * additional continuation byte depending on whether the string is split between
8560 * blocks or whether it starts at the beginning of the block. (There are also
8561 * additional complications that will arise later when/if Rich Strings are
8566 function _calculateSharedStringsSizes()
8568 /* Iterate through the strings to calculate the CONTINUE block sizes.
8569 For simplicity we use the same size for the SST and CONTINUE records:
8570 8228 : Maximum Excel97 block size
8571 -4 : Length of block header
8572 -8 : Length of additional SST header information
8573 -8 : Arbitrary number to keep within _add_continue() limit = 8208
8575 $continue_limit = 8208;
8578 $this->_block_sizes = array();
8581 foreach (array_keys($this->_str_table) as $string) {
8582 $string_length = strlen($string);
8583 $headerinfo = unpack("vlength/Cencoding", $string);
8584 $encoding = $headerinfo["encoding"];
8587 // Block length is the total length of the strings that will be
8588 // written out in a single SST or CONTINUE block.
8589 $block_length += $string_length;
8591 // We can write the string if it doesn't cross a CONTINUE boundary
8592 if ($block_length < $continue_limit) {
8593 $written += $string_length;
8597 // Deal with the cases where the next string to be written will exceed
8598 // the CONTINUE boundary. If the string is very long it may need to be
8599 // written in more than one CONTINUE record.
8600 while ($block_length >= $continue_limit) {
8602 // We need to avoid the case where a string is continued in the first
8603 // n bytes that contain the string header information.
8604 $header_length = 3; // Min string + header size -1
8605 $space_remaining = $continue_limit - $written - $continue;
8608 /* TODO: Unicode data should only be split on char (2 byte)
8609 boundaries. Therefore, in some cases we need to reduce the
8614 // Only applies to Unicode strings
8615 if ($encoding == 1) {
8616 // Min string + header size -1
8619 if ($space_remaining > $header_length) {
8620 // String contains 3 byte header => split on odd boundary
8621 if (!$split_string && $space_remaining % 2 != 1) {
8625 // Split section without header => split on even boundary
8626 else if ($split_string && $space_remaining % 2 == 1) {
8636 if ($space_remaining > $header_length) {
8637 // Write as much as possible of the string in the current block
8638 $written += $space_remaining;
8640 // Reduce the current block length by the amount written
8641 $block_length -= $continue_limit - $continue - $align;
8643 // Store the max size for this block
8644 $this->_block_sizes[] = $continue_limit - $align;
8646 // If the current string was split then the next CONTINUE block
8647 // should have the string continue flag (grbit) set unless the
8648 // split string fits exactly into the remaining space.
8649 if ($block_length > 0) {
8655 // Store the max size for this block
8656 $this->_block_sizes[] = $written + $continue;
8658 // Not enough space to start the string in the current block
8659 $block_length -= $continue_limit - $space_remaining - $continue;
8664 // If the string (or substr) is small enough we can write it in the
8665 // new CONTINUE block. Else, go through the loop again to write it in
8666 // one or more CONTINUE blocks
8667 if ($block_length < $continue_limit) {
8668 $written = $block_length;
8675 // Store the max size for the last block unless it is empty
8676 if ($written + $continue) {
8677 $this->_block_sizes[] = $written + $continue;
8681 /* Calculate the total length of the SST and associated CONTINUEs (if any).
8682 The SST record will have a length even if it contains no strings.
8683 This length is required to set the offsets in the BOUNDSHEET records since
8684 they must be written before the SST records
8687 $tmp_block_sizes = array();
8688 $tmp_block_sizes = $this->_block_sizes;
8691 if (!empty($tmp_block_sizes)) {
8692 $length += array_shift($tmp_block_sizes); // SST
8694 while (!empty($tmp_block_sizes)) {
8695 $length += 4 + array_shift($tmp_block_sizes); // CONTINUEs
8702 * Write all of the workbooks strings into an indexed array.
8703 * See the comments in _calculate_shared_string_sizes() for more information.
8705 * The Excel documentation says that the SST record should be followed by an
8706 * EXTSST record. The EXTSST record is a hash table that is used to optimise
8707 * access to SST. However, despite the documentation it doesn't seem to be
8708 * required so we will ignore it.
8712 function _storeSharedStringsTable()
8714 $record = 0x00fc; // Record identifier
8715 $length = 0x0008; // Number of bytes to follow
8718 // Iterate through the strings to calculate the CONTINUE block sizes
8719 $continue_limit = 8208;
8724 // sizes are upside down
8725 $tmp_block_sizes = $this->_block_sizes;
8726 // $tmp_block_sizes = array_reverse($this->_block_sizes);
8728 // The SST record is required even if it contains no strings. Thus we will
8729 // always have a length
8731 if (!empty($tmp_block_sizes)) {
8732 $length = 8 + array_shift($tmp_block_sizes);
8739 // Write the SST block header information
8740 $header = pack("vv", $record, $length);
8741 $data = pack("VV", $this->_str_total, $this->_str_unique);
8742 $this->_append($header . $data);
8744 /* TODO: not good for performance */
8745 foreach (array_keys($this->_str_table) as $string) {
8747 $string_length = strlen($string);
8748 $headerinfo = unpack("vlength/Cencoding", $string);
8749 $encoding = $headerinfo["encoding"];
8752 // Block length is the total length of the strings that will be
8753 // written out in a single SST or CONTINUE block.
8755 $block_length += $string_length;
8758 // We can write the string if it doesn't cross a CONTINUE boundary
8759 if ($block_length < $continue_limit) {
8760 $this->_append($string);
8761 $written += $string_length;
8765 // Deal with the cases where the next string to be written will exceed
8766 // the CONTINUE boundary. If the string is very long it may need to be
8767 // written in more than one CONTINUE record.
8769 while ($block_length >= $continue_limit) {
8771 // We need to avoid the case where a string is continued in the first
8772 // n bytes that contain the string header information.
8774 $header_length = 3; // Min string + header size -1
8775 $space_remaining = $continue_limit - $written - $continue;
8778 // Unicode data should only be split on char (2 byte) boundaries.
8779 // Therefore, in some cases we need to reduce the amount of available
8780 // space by 1 byte to ensure the correct alignment.
8783 // Only applies to Unicode strings
8784 if ($encoding == 1) {
8785 // Min string + header size -1
8788 if ($space_remaining > $header_length) {
8789 // String contains 3 byte header => split on odd boundary
8790 if (!$split_string && $space_remaining % 2 != 1) {
8794 // Split section without header => split on even boundary
8795 else if ($split_string && $space_remaining % 2 == 1) {
8805 if ($space_remaining > $header_length) {
8806 // Write as much as possible of the string in the current block
8807 $tmp = substr($string, 0, $space_remaining);
8808 $this->_append($tmp);
8810 // The remainder will be written in the next block(s)
8811 $string = substr($string, $space_remaining);
8813 // Reduce the current block length by the amount written
8814 $block_length -= $continue_limit - $continue - $align;
8816 // If the current string was split then the next CONTINUE block
8817 // should have the string continue flag (grbit) set unless the
8818 // split string fits exactly into the remaining space.
8820 if ($block_length > 0) {
8826 // Not enough space to start the string in the current block
8827 $block_length -= $continue_limit - $space_remaining - $continue;
8831 // Write the CONTINUE block header
8832 if (!empty($this->_block_sizes)) {
8834 $length = array_shift($tmp_block_sizes);
8836 $header = pack('vv', $record, $length);
8838 $header .= pack('C', $encoding);
8840 $this->_append($header);
8843 // If the string (or substr) is small enough we can write it in the
8844 // new CONTINUE block. Else, go through the loop again to write it in
8845 // one or more CONTINUE blocks
8847 if ($block_length < $continue_limit) {
8848 $this->_append($string);
8849 $written = $block_length;