From f1ab2f022fdc780aca0944d90e9a0e844a0820d7 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Anton=20Luka=20=C5=A0ijanec?= Date: Mon, 27 May 2024 13:12:17 +0200 Subject: =?UTF-8?q?2024-02-19:=20popravljen=20(prej=C5=A1nji=20commit=20je?= =?UTF-8?q?=20napa=C4=8Den)?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../excel/PHPExcel/Writer/Excel5/BIFFwriter.php | 255 -- .../survey/excel/PHPExcel/Writer/Excel5/Escher.php | 537 ---- admin/survey/excel/PHPExcel/Writer/Excel5/Font.php | 165 -- .../survey/excel/PHPExcel/Writer/Excel5/Parser.php | 1583 ----------- .../excel/PHPExcel/Writer/Excel5/Workbook.php | 1450 ---------- .../excel/PHPExcel/Writer/Excel5/Worksheet.php | 2954 -------------------- admin/survey/excel/PHPExcel/Writer/Excel5/Xf.php | 546 ---- 7 files changed, 7490 deletions(-) delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/BIFFwriter.php delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/Escher.php delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/Font.php delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/Parser.php delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/Workbook.php delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/Worksheet.php delete mode 100644 admin/survey/excel/PHPExcel/Writer/Excel5/Xf.php (limited to 'admin/survey/excel/PHPExcel/Writer/Excel5') diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/BIFFwriter.php b/admin/survey/excel/PHPExcel/Writer/Excel5/BIFFwriter.php deleted file mode 100644 index 2c930c3..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/BIFFwriter.php +++ /dev/null @@ -1,255 +0,0 @@ - -// * -// * The majority of this is _NOT_ my code. I simply ported it from the -// * PERL Spreadsheet::WriteExcel module. -// * -// * The author of the Spreadsheet::WriteExcel module is John McNamara -// * -// * -// * I _DO_ maintain this code, and John McNamara has nothing to do with the -// * porting of this code to PHP. Any questions directly related to this -// * class library should be directed to me. -// * -// * License Information: -// * -// * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets -// * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com -// * -// * This library is free software; you can redistribute it and/or -// * modify it under the terms of the GNU Lesser General Public -// * License as published by the Free Software Foundation; either -// * version 2.1 of the License, or (at your option) any later version. -// * -// * This library is distributed in the hope that it will be useful, -// * but WITHOUT ANY WARRANTY; without even the implied warranty of -// * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -// * Lesser General Public License for more details. -// * -// * You should have received a copy of the GNU Lesser General Public -// * License along with this library; if not, write to the Free Software -// * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA -// */ - - -/** - * PHPExcel_Writer_Excel5_BIFFwriter - * - * @category PHPExcel - * @package PHPExcel_Writer_Excel5 - * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel) - */ -class PHPExcel_Writer_Excel5_BIFFwriter -{ - /** - * The byte order of this architecture. 0 => little endian, 1 => big endian - * @var integer - */ - private static $_byte_order; - - /** - * The string containing the data of the BIFF stream - * @var string - */ - public $_data; - - /** - * The size of the data in bytes. Should be the same as strlen($this->_data) - * @var integer - */ - public $_datasize; - - /** - * The maximum length for a BIFF record (excluding record header and length field). See _addContinue() - * @var integer - * @see _addContinue() - */ - public $_limit = 8224; - - /** - * Constructor - */ - public function __construct() - { - $this->_data = ''; - $this->_datasize = 0; -// $this->_limit = 8224; - } - - /** - * Determine the byte order and store it as class data to avoid - * recalculating it for each call to new(). - * - * @return int - */ - public static function getByteOrder() - { - if (!isset(self::$_byte_order)) { - // Check if "pack" gives the required IEEE 64bit float - $teststr = pack("d", 1.2345); - $number = pack("C8", 0x8D, 0x97, 0x6E, 0x12, 0x83, 0xC0, 0xF3, 0x3F); - if ($number == $teststr) { - $byte_order = 0; // Little Endian - } elseif ($number == strrev($teststr)){ - $byte_order = 1; // Big Endian - } else { - // Give up. I'll fix this in a later version. - throw new Exception("Required floating point format not supported on this platform."); - } - self::$_byte_order = $byte_order; - } - - return self::$_byte_order; - } - - /** - * General storage function - * - * @param string $data binary data to append - * @access private - */ - function _append($data) - { - if (strlen($data) - 4 > $this->_limit) { - $data = $this->_addContinue($data); - } - $this->_data .= $data; - $this->_datasize += strlen($data); - } - - /** - * General storage function like _append, but returns string instead of modifying $this->_data - * - * @param string $data binary data to write - * @return string - */ - public function writeData($data) - { - if (strlen($data) - 4 > $this->_limit) { - $data = $this->_addContinue($data); - } - $this->_datasize += strlen($data); - - return $data; - } - - /** - * Writes Excel BOF record to indicate the beginning of a stream or - * sub-stream in the BIFF file. - * - * @param integer $type Type of BIFF file to write: 0x0005 Workbook, - * 0x0010 Worksheet. - * @access private - */ - function _storeBof($type) - { - $record = 0x0809; // Record identifier (BIFF5-BIFF8) - $length = 0x0010; - - // by inspection of real files, MS Office Excel 2007 writes the following - $unknown = pack("VV", 0x000100D1, 0x00000406); - - $build = 0x0DBB; // Excel 97 - $year = 0x07CC; // Excel 97 - - $version = 0x0600; // BIFF8 - - $header = pack("vv", $record, $length); - $data = pack("vvvv", $version, $type, $build, $year); - $this->_append($header . $data . $unknown); - } - - /** - * Writes Excel EOF record to indicate the end of a BIFF stream. - * - * @access private - */ - function _storeEof() - { - $record = 0x000A; // Record identifier - $length = 0x0000; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $this->_append($header); - } - - /** - * Writes Excel EOF record to indicate the end of a BIFF stream. - * - * @access private - */ - public function writeEof() - { - $record = 0x000A; // Record identifier - $length = 0x0000; // Number of bytes to follow - $header = pack("vv", $record, $length); - return $this->writeData($header); - } - - /** - * Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In - * Excel 97 the limit is 8228 bytes. Records that are longer than these limits - * must be split up into CONTINUE blocks. - * - * This function takes a long BIFF record and inserts CONTINUE records as - * necessary. - * - * @param string $data The original binary data to be written - * @return string A very convenient string of continue blocks - * @access private - */ - function _addContinue($data) - { - $limit = $this->_limit; - $record = 0x003C; // Record identifier - - // The first 2080/8224 bytes remain intact. However, we have to change - // the length field of the record. - $tmp = substr($data, 0, 2) . pack("v", $limit) . substr($data, 4, $limit); - - $header = pack("vv", $record, $limit); // Headers for continue records - - // Retrieve chunks of 2080/8224 bytes +4 for the header. - $data_length = strlen($data); - for ($i = $limit + 4; $i < ($data_length - $limit); $i += $limit) { - $tmp .= $header; - $tmp .= substr($data, $i, $limit); - } - - // Retrieve the last chunk of data - $header = pack("vv", $record, strlen($data) - $i); - $tmp .= $header; - $tmp .= substr($data, $i, strlen($data) - $i); - - return $tmp; - } - -} diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/Escher.php b/admin/survey/excel/PHPExcel/Writer/Excel5/Escher.php deleted file mode 100644 index b0e85a2..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/Escher.php +++ /dev/null @@ -1,537 +0,0 @@ -_object = $object; - } - - /** - * Process the object to be written - */ - public function close() - { - // initialize - $this->_data = ''; - - switch (get_class($this->_object)) { - - case 'PHPExcel_Shared_Escher': - if ($dggContainer = $this->_object->getDggContainer()) { - $writer = new PHPExcel_Writer_Excel5_Escher($dggContainer); - $this->_data = $writer->close(); - } else if ($dgContainer = $this->_object->getDgContainer()) { - $writer = new PHPExcel_Writer_Excel5_Escher($dgContainer); - $this->_data = $writer->close(); - $this->_spOffsets = $writer->getSpOffsets(); - $this->_spTypes = $writer->getSpTypes(); - } - break; - - case 'PHPExcel_Shared_Escher_DggContainer': - // this is a container record - - // initialize - $innerData = ''; - - // write the dgg - $recVer = 0x0; - $recInstance = 0x0000; - $recType = 0xF006; - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - // dgg data - $dggData = - pack('VVVV' - , $this->_object->getSpIdMax() // maximum shape identifier increased by one - , $this->_object->getCDgSaved() + 1 // number of file identifier clusters increased by one - , $this->_object->getCSpSaved() - , $this->_object->getCDgSaved() // count total number of drawings saved - ); - - // add file identifier clusters (one per drawing) - $IDCLs = $this->_object->getIDCLs(); - - foreach ($IDCLs as $dgId => $maxReducedSpId) { - $dggData .= pack('VV', $dgId, $maxReducedSpId + 1); - } - - $header = pack('vvV', $recVerInstance, $recType, strlen($dggData)); - $innerData .= $header . $dggData; - - // write the bstoreContainer - if ($bstoreContainer = $this->_object->getBstoreContainer()) { - $writer = new PHPExcel_Writer_Excel5_Escher($bstoreContainer); - $innerData .= $writer->close(); - } - - // write the record - $recVer = 0xF; - $recInstance = 0x0000; - $recType = 0xF000; - $length = strlen($innerData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header . $innerData; - break; - - case 'PHPExcel_Shared_Escher_DggContainer_BstoreContainer': - // this is a container record - - // initialize - $innerData = ''; - - // treat the inner data - if ($BSECollection = $this->_object->getBSECollection()) { - foreach ($BSECollection as $BSE) { - $writer = new PHPExcel_Writer_Excel5_Escher($BSE); - $innerData .= $writer->close(); - } - } - - // write the record - $recVer = 0xF; - $recInstance = count($this->_object->getBSECollection()); - $recType = 0xF001; - $length = strlen($innerData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header . $innerData; - break; - - case 'PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE': - // this is a semi-container record - - // initialize - $innerData = ''; - - // here we treat the inner data - if ($blip = $this->_object->getBlip()) { - $writer = new PHPExcel_Writer_Excel5_Escher($blip); - $innerData .= $writer->close(); - } - - // initialize - $data = ''; - - $btWin32 = $this->_object->getBlipType(); - $btMacOS = $this->_object->getBlipType(); - $data .= pack('CC', $btWin32, $btMacOS); - - $rgbUid = pack('VVVV', 0,0,0,0); // todo - $data .= $rgbUid; - - $tag = 0; - $size = strlen($innerData); - $cRef = 1; - $foDelay = 0; //todo - $unused1 = 0x0; - $cbName = 0x0; - $unused2 = 0x0; - $unused3 = 0x0; - $data .= pack('vVVVCCCC', $tag, $size, $cRef, $foDelay, $unused1, $cbName, $unused2, $unused3); - - $data .= $innerData; - - // write the record - $recVer = 0x2; - $recInstance = $this->_object->getBlipType(); - $recType = 0xF007; - $length = strlen($data); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header; - - $this->_data .= $data; - break; - - case 'PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE_Blip': - // this is an atom record - - // write the record - switch ($this->_object->getParent()->getBlipType()) { - - case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG: - // initialize - $innerData = ''; - - $rgbUid1 = pack('VVVV', 0,0,0,0); // todo - $innerData .= $rgbUid1; - - $tag = 0xFF; // todo - $innerData .= pack('C', $tag); - - $innerData .= $this->_object->getData(); - - $recVer = 0x0; - $recInstance = 0x46A; - $recType = 0xF01D; - $length = strlen($innerData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header; - - $this->_data .= $innerData; - break; - - case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG: - // initialize - $innerData = ''; - - $rgbUid1 = pack('VVVV', 0,0,0,0); // todo - $innerData .= $rgbUid1; - - $tag = 0xFF; // todo - $innerData .= pack('C', $tag); - - $innerData .= $this->_object->getData(); - - $recVer = 0x0; - $recInstance = 0x6E0; - $recType = 0xF01E; - $length = strlen($innerData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header; - - $this->_data .= $innerData; - break; - - } - break; - - case 'PHPExcel_Shared_Escher_DgContainer': - // this is a container record - - // initialize - $innerData = ''; - - // write the dg - $recVer = 0x0; - $recInstance = $this->_object->getDgId(); - $recType = 0xF008; - $length = 8; - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - // number of shapes in this drawing (including group shape) - $countShapes = count($this->_object->getSpgrContainer()->getChildren()); - $innerData .= $header . pack('VV', $countShapes, $this->_object->getLastSpId()); - //$innerData .= $header . pack('VV', 0, 0); - - // write the spgrContainer - if ($spgrContainer = $this->_object->getSpgrContainer()) { - $writer = new PHPExcel_Writer_Excel5_Escher($spgrContainer); - $innerData .= $writer->close(); - - // get the shape offsets relative to the spgrContainer record - $spOffsets = $writer->getSpOffsets(); - $spTypes = $writer->getSpTypes(); - - // save the shape offsets relative to dgContainer - foreach ($spOffsets as & $spOffset) { - $spOffset += 24; // add length of dgContainer header data (8 bytes) plus dg data (16 bytes) - } - - $this->_spOffsets = $spOffsets; - $this->_spTypes = $spTypes; - } - - // write the record - $recVer = 0xF; - $recInstance = 0x0000; - $recType = 0xF002; - $length = strlen($innerData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header . $innerData; - break; - - case 'PHPExcel_Shared_Escher_DgContainer_SpgrContainer': - // this is a container record - - // initialize - $innerData = ''; - - // initialize spape offsets - $totalSize = 8; - $spOffsets = array(); - $spTypes = array(); - - // treat the inner data - foreach ($this->_object->getChildren() as $spContainer) { - $writer = new PHPExcel_Writer_Excel5_Escher($spContainer); - $spData = $writer->close(); - $innerData .= $spData; - - // save the shape offsets (where new shape records begin) - $totalSize += strlen($spData); - $spOffsets[] = $totalSize; - - $spTypes = array_merge($spTypes, $writer->getSpTypes()); - } - - // write the record - $recVer = 0xF; - $recInstance = 0x0000; - $recType = 0xF003; - $length = strlen($innerData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header . $innerData; - $this->_spOffsets = $spOffsets; - $this->_spTypes = $spTypes; - break; - - case 'PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer': - // initialize - $data = ''; - - // build the data - - // write group shape record, if necessary? - if ($this->_object->getSpgr()) { - $recVer = 0x1; - $recInstance = 0x0000; - $recType = 0xF009; - $length = 0x00000010; - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $data .= $header . pack('VVVV', 0,0,0,0); - } - $this->_spTypes[] = ($this->_object->getSpType()); - - // write the shape record - $recVer = 0x2; - $recInstance = $this->_object->getSpType(); // shape type - $recType = 0xF00A; - $length = 0x00000008; - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $data .= $header . pack('VV', $this->_object->getSpId(), $this->_object->getSpgr() ? 0x0005 : 0x0A00); - - - // the options - if ($this->_object->getOPTCollection()) { - $optData = ''; - - $recVer = 0x3; - $recInstance = count($this->_object->getOPTCollection()); - $recType = 0xF00B; - foreach ($this->_object->getOPTCollection() as $property => $value) { - $optData .= pack('vV', $property, $value); - } - $length = strlen($optData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - $data .= $header . $optData; - } - - // the client anchor - if ($this->_object->getStartCoordinates()) { - $clientAnchorData = ''; - - $recVer = 0x0; - $recInstance = 0x0; - $recType = 0xF010; - - // start coordinates - list($column, $row) = PHPExcel_Cell::coordinateFromString($this->_object->getStartCoordinates()); - $c1 = PHPExcel_Cell::columnIndexFromString($column) - 1; - $r1 = $row - 1; - - // start offsetX - $startOffsetX = $this->_object->getStartOffsetX(); - - // start offsetY - $startOffsetY = $this->_object->getStartOffsetY(); - - // end coordinates - list($column, $row) = PHPExcel_Cell::coordinateFromString($this->_object->getEndCoordinates()); - $c2 = PHPExcel_Cell::columnIndexFromString($column) - 1; - $r2 = $row - 1; - - // end offsetX - $endOffsetX = $this->_object->getEndOffsetX(); - - // end offsetY - $endOffsetY = $this->_object->getEndOffsetY(); - - $clientAnchorData = pack('vvvvvvvvv', $this->_object->getSpFlag(), - $c1, $startOffsetX, $r1, $startOffsetY, - $c2, $endOffsetX, $r2, $endOffsetY); - - $length = strlen($clientAnchorData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - $data .= $header . $clientAnchorData; - } - - // the client data, just empty for now - if (!$this->_object->getSpgr()) { - $clientDataData = ''; - - $recVer = 0x0; - $recInstance = 0x0; - $recType = 0xF011; - - $length = strlen($clientDataData); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - $data .= $header . $clientDataData; - } - - // write the record - $recVer = 0xF; - $recInstance = 0x0000; - $recType = 0xF004; - $length = strlen($data); - - $recVerInstance = $recVer; - $recVerInstance |= $recInstance << 4; - - $header = pack('vvV', $recVerInstance, $recType, $length); - - $this->_data = $header . $data; - break; - - } - - return $this->_data; - } - - /** - * Gets the shape offsets - * - * @return array - */ - public function getSpOffsets() - { - return $this->_spOffsets; - } - - /** - * Gets the shape types - * - * @return array - */ - public function getSpTypes() - { - return $this->_spTypes; - } - - -} diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/Font.php b/admin/survey/excel/PHPExcel/Writer/Excel5/Font.php deleted file mode 100644 index a891117..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/Font.php +++ /dev/null @@ -1,165 +0,0 @@ -_colorIndex = 0x7FFF; - $this->_font = $font; - } - - /** - * Set the color index - * - * @param int $colorIndex - */ - public function setColorIndex($colorIndex) - { - $this->_colorIndex = $colorIndex; - } - - /** - * Get font record data - * - * @return string - */ - public function writeFont() - { - $font_outline = 0; - $font_shadow = 0; - - $icv = $this->_colorIndex; // Index to color palette - if ($this->_font->getSuperScript()) { - $sss = 1; - } else if ($this->_font->getSubScript()) { - $sss = 2; - } else { - $sss = 0; - } - $bFamily = 0; // Font family - $bCharSet = PHPExcel_Shared_Font::getCharsetFromFontName($this->_font->getName()); // Character set - - $record = 0x31; // Record identifier - $reserved = 0x00; // Reserved - $grbit = 0x00; // Font attributes - if ($this->_font->getItalic()) { - $grbit |= 0x02; - } - if ($this->_font->getStrikethrough()) { - $grbit |= 0x08; - } - if ($font_outline) { - $grbit |= 0x10; - } - if ($font_shadow) { - $grbit |= 0x20; - } - - $data = pack("vvvvvCCCC", - $this->_font->getSize() * 20, // Fontsize (in twips) - $grbit, - $icv, // Colour - self::_mapBold($this->_font->getBold()), // Font weight - $sss, // Superscript/Subscript - self::_mapUnderline($this->_font->getUnderline()), - $bFamily, - $bCharSet, - $reserved - ); - $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($this->_font->getName()); - - $length = strlen($data); - $header = pack("vv", $record, $length); - - return($header . $data); - } - - /** - * Map to BIFF5-BIFF8 codes for bold - * - * @param boolean $bold - * @return int - */ - private static function _mapBold($bold) { - if ($bold) { - return 0x2BC; // 700 = Bold font weight - } - return 0x190; // 400 = Normal font weight - } - - /** - * Map of BIFF2-BIFF8 codes for underline styles - * @static array of int - * - */ - private static $_mapUnderline = array( PHPExcel_Style_Font::UNDERLINE_NONE => 0x00, - PHPExcel_Style_Font::UNDERLINE_SINGLE => 0x01, - PHPExcel_Style_Font::UNDERLINE_DOUBLE => 0x02, - PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING => 0x21, - PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING => 0x22, - ); - /** - * Map underline - * - * @param string - * @return int - */ - private static function _mapUnderline($underline) { - if (isset(self::$_mapUnderline[$underline])) - return self::$_mapUnderline[$underline]; - return 0x00; - } - -} diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/Parser.php b/admin/survey/excel/PHPExcel/Writer/Excel5/Parser.php deleted file mode 100644 index f986055..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/Parser.php +++ /dev/null @@ -1,1583 +0,0 @@ -=,;#()"{} - const REGEX_SHEET_TITLE_UNQUOTED = '[^\*\:\/\\\\\?\[\]\+\-\% \\\'\^\&\<\>\=\,\;\#\(\)\"\{\}]+'; - - // Sheet title in quoted form (without surrounding quotes) - // Invalid sheet title characters cannot occur in the sheet title: - // *:/\?[] (usual invalid sheet title characters) - // Single quote is represented as a pair '' - const REGEX_SHEET_TITLE_QUOTED = '(([^\*\:\/\\\\\?\[\]\\\'])+|(\\\'\\\')+)+'; - - /** - * The index of the character we are currently looking at - * @var integer - */ - public $_current_char; - - /** - * The token we are working on. - * @var string - */ - public $_current_token; - - /** - * The formula to parse - * @var string - */ - public $_formula; - - /** - * The character ahead of the current char - * @var string - */ - public $_lookahead; - - /** - * The parse tree to be generated - * @var string - */ - public $_parse_tree; - - /** - * Array of external sheets - * @var array - */ - public $_ext_sheets; - - /** - * Array of sheet references in the form of REF structures - * @var array - */ - public $_references; - - /** - * The class constructor - * - */ - public function __construct() - { - $this->_current_char = 0; - $this->_current_token = ''; // The token we are working on. - $this->_formula = ''; // The formula to parse. - $this->_lookahead = ''; // The character ahead of the current char. - $this->_parse_tree = ''; // The parse tree to be generated. - $this->_initializeHashes(); // Initialize the hashes: ptg's and function's ptg's - $this->_ext_sheets = array(); - $this->_references = array(); - } - - /** - * Initialize the ptg and function hashes. - * - * @access private - */ - function _initializeHashes() - { - // The Excel ptg indices - $this->ptg = array( - 'ptgExp' => 0x01, - 'ptgTbl' => 0x02, - 'ptgAdd' => 0x03, - 'ptgSub' => 0x04, - 'ptgMul' => 0x05, - 'ptgDiv' => 0x06, - 'ptgPower' => 0x07, - 'ptgConcat' => 0x08, - 'ptgLT' => 0x09, - 'ptgLE' => 0x0A, - 'ptgEQ' => 0x0B, - 'ptgGE' => 0x0C, - 'ptgGT' => 0x0D, - 'ptgNE' => 0x0E, - 'ptgIsect' => 0x0F, - 'ptgUnion' => 0x10, - 'ptgRange' => 0x11, - 'ptgUplus' => 0x12, - 'ptgUminus' => 0x13, - 'ptgPercent' => 0x14, - 'ptgParen' => 0x15, - 'ptgMissArg' => 0x16, - 'ptgStr' => 0x17, - 'ptgAttr' => 0x19, - 'ptgSheet' => 0x1A, - 'ptgEndSheet' => 0x1B, - 'ptgErr' => 0x1C, - 'ptgBool' => 0x1D, - 'ptgInt' => 0x1E, - 'ptgNum' => 0x1F, - 'ptgArray' => 0x20, - 'ptgFunc' => 0x21, - 'ptgFuncVar' => 0x22, - 'ptgName' => 0x23, - 'ptgRef' => 0x24, - 'ptgArea' => 0x25, - 'ptgMemArea' => 0x26, - 'ptgMemErr' => 0x27, - 'ptgMemNoMem' => 0x28, - 'ptgMemFunc' => 0x29, - 'ptgRefErr' => 0x2A, - 'ptgAreaErr' => 0x2B, - 'ptgRefN' => 0x2C, - 'ptgAreaN' => 0x2D, - 'ptgMemAreaN' => 0x2E, - 'ptgMemNoMemN' => 0x2F, - 'ptgNameX' => 0x39, - 'ptgRef3d' => 0x3A, - 'ptgArea3d' => 0x3B, - 'ptgRefErr3d' => 0x3C, - 'ptgAreaErr3d' => 0x3D, - 'ptgArrayV' => 0x40, - 'ptgFuncV' => 0x41, - 'ptgFuncVarV' => 0x42, - 'ptgNameV' => 0x43, - 'ptgRefV' => 0x44, - 'ptgAreaV' => 0x45, - 'ptgMemAreaV' => 0x46, - 'ptgMemErrV' => 0x47, - 'ptgMemNoMemV' => 0x48, - 'ptgMemFuncV' => 0x49, - 'ptgRefErrV' => 0x4A, - 'ptgAreaErrV' => 0x4B, - 'ptgRefNV' => 0x4C, - 'ptgAreaNV' => 0x4D, - 'ptgMemAreaNV' => 0x4E, - 'ptgMemNoMemN' => 0x4F, - 'ptgFuncCEV' => 0x58, - 'ptgNameXV' => 0x59, - 'ptgRef3dV' => 0x5A, - 'ptgArea3dV' => 0x5B, - 'ptgRefErr3dV' => 0x5C, - 'ptgAreaErr3d' => 0x5D, - 'ptgArrayA' => 0x60, - 'ptgFuncA' => 0x61, - 'ptgFuncVarA' => 0x62, - 'ptgNameA' => 0x63, - 'ptgRefA' => 0x64, - 'ptgAreaA' => 0x65, - 'ptgMemAreaA' => 0x66, - 'ptgMemErrA' => 0x67, - 'ptgMemNoMemA' => 0x68, - 'ptgMemFuncA' => 0x69, - 'ptgRefErrA' => 0x6A, - 'ptgAreaErrA' => 0x6B, - 'ptgRefNA' => 0x6C, - 'ptgAreaNA' => 0x6D, - 'ptgMemAreaNA' => 0x6E, - 'ptgMemNoMemN' => 0x6F, - 'ptgFuncCEA' => 0x78, - 'ptgNameXA' => 0x79, - 'ptgRef3dA' => 0x7A, - 'ptgArea3dA' => 0x7B, - 'ptgRefErr3dA' => 0x7C, - 'ptgAreaErr3d' => 0x7D - ); - - // Thanks to Michael Meeks and Gnumeric for the initial arg values. - // - // The following hash was generated by "function_locale.pl" in the distro. - // Refer to function_locale.pl for non-English function names. - // - // The array elements are as follow: - // ptg: The Excel function ptg code. - // args: The number of arguments that the function takes: - // >=0 is a fixed number of arguments. - // -1 is a variable number of arguments. - // class: The reference, value or array class of the function args. - // vol: The function is volatile. - // - $this->_functions = array( - // function ptg args class vol - 'COUNT' => array( 0, -1, 0, 0 ), - 'IF' => array( 1, -1, 1, 0 ), - 'ISNA' => array( 2, 1, 1, 0 ), - 'ISERROR' => array( 3, 1, 1, 0 ), - 'SUM' => array( 4, -1, 0, 0 ), - 'AVERAGE' => array( 5, -1, 0, 0 ), - 'MIN' => array( 6, -1, 0, 0 ), - 'MAX' => array( 7, -1, 0, 0 ), - 'ROW' => array( 8, -1, 0, 0 ), - 'COLUMN' => array( 9, -1, 0, 0 ), - 'NA' => array( 10, 0, 0, 0 ), - 'NPV' => array( 11, -1, 1, 0 ), - 'STDEV' => array( 12, -1, 0, 0 ), - 'DOLLAR' => array( 13, -1, 1, 0 ), - 'FIXED' => array( 14, -1, 1, 0 ), - 'SIN' => array( 15, 1, 1, 0 ), - 'COS' => array( 16, 1, 1, 0 ), - 'TAN' => array( 17, 1, 1, 0 ), - 'ATAN' => array( 18, 1, 1, 0 ), - 'PI' => array( 19, 0, 1, 0 ), - 'SQRT' => array( 20, 1, 1, 0 ), - 'EXP' => array( 21, 1, 1, 0 ), - 'LN' => array( 22, 1, 1, 0 ), - 'LOG10' => array( 23, 1, 1, 0 ), - 'ABS' => array( 24, 1, 1, 0 ), - 'INT' => array( 25, 1, 1, 0 ), - 'SIGN' => array( 26, 1, 1, 0 ), - 'ROUND' => array( 27, 2, 1, 0 ), - 'LOOKUP' => array( 28, -1, 0, 0 ), - 'INDEX' => array( 29, -1, 0, 1 ), - 'REPT' => array( 30, 2, 1, 0 ), - 'MID' => array( 31, 3, 1, 0 ), - 'LEN' => array( 32, 1, 1, 0 ), - 'VALUE' => array( 33, 1, 1, 0 ), - 'TRUE' => array( 34, 0, 1, 0 ), - 'FALSE' => array( 35, 0, 1, 0 ), - 'AND' => array( 36, -1, 0, 0 ), - 'OR' => array( 37, -1, 0, 0 ), - 'NOT' => array( 38, 1, 1, 0 ), - 'MOD' => array( 39, 2, 1, 0 ), - 'DCOUNT' => array( 40, 3, 0, 0 ), - 'DSUM' => array( 41, 3, 0, 0 ), - 'DAVERAGE' => array( 42, 3, 0, 0 ), - 'DMIN' => array( 43, 3, 0, 0 ), - 'DMAX' => array( 44, 3, 0, 0 ), - 'DSTDEV' => array( 45, 3, 0, 0 ), - 'VAR' => array( 46, -1, 0, 0 ), - 'DVAR' => array( 47, 3, 0, 0 ), - 'TEXT' => array( 48, 2, 1, 0 ), - 'LINEST' => array( 49, -1, 0, 0 ), - 'TREND' => array( 50, -1, 0, 0 ), - 'LOGEST' => array( 51, -1, 0, 0 ), - 'GROWTH' => array( 52, -1, 0, 0 ), - 'PV' => array( 56, -1, 1, 0 ), - 'FV' => array( 57, -1, 1, 0 ), - 'NPER' => array( 58, -1, 1, 0 ), - 'PMT' => array( 59, -1, 1, 0 ), - 'RATE' => array( 60, -1, 1, 0 ), - 'MIRR' => array( 61, 3, 0, 0 ), - 'IRR' => array( 62, -1, 0, 0 ), - 'RAND' => array( 63, 0, 1, 1 ), - 'MATCH' => array( 64, -1, 0, 0 ), - 'DATE' => array( 65, 3, 1, 0 ), - 'TIME' => array( 66, 3, 1, 0 ), - 'DAY' => array( 67, 1, 1, 0 ), - 'MONTH' => array( 68, 1, 1, 0 ), - 'YEAR' => array( 69, 1, 1, 0 ), - 'WEEKDAY' => array( 70, -1, 1, 0 ), - 'HOUR' => array( 71, 1, 1, 0 ), - 'MINUTE' => array( 72, 1, 1, 0 ), - 'SECOND' => array( 73, 1, 1, 0 ), - 'NOW' => array( 74, 0, 1, 1 ), - 'AREAS' => array( 75, 1, 0, 1 ), - 'ROWS' => array( 76, 1, 0, 1 ), - 'COLUMNS' => array( 77, 1, 0, 1 ), - 'OFFSET' => array( 78, -1, 0, 1 ), - 'SEARCH' => array( 82, -1, 1, 0 ), - 'TRANSPOSE' => array( 83, 1, 1, 0 ), - 'TYPE' => array( 86, 1, 1, 0 ), - 'ATAN2' => array( 97, 2, 1, 0 ), - 'ASIN' => array( 98, 1, 1, 0 ), - 'ACOS' => array( 99, 1, 1, 0 ), - 'CHOOSE' => array( 100, -1, 1, 0 ), - 'HLOOKUP' => array( 101, -1, 0, 0 ), - 'VLOOKUP' => array( 102, -1, 0, 0 ), - 'ISREF' => array( 105, 1, 0, 0 ), - 'LOG' => array( 109, -1, 1, 0 ), - 'CHAR' => array( 111, 1, 1, 0 ), - 'LOWER' => array( 112, 1, 1, 0 ), - 'UPPER' => array( 113, 1, 1, 0 ), - 'PROPER' => array( 114, 1, 1, 0 ), - 'LEFT' => array( 115, -1, 1, 0 ), - 'RIGHT' => array( 116, -1, 1, 0 ), - 'EXACT' => array( 117, 2, 1, 0 ), - 'TRIM' => array( 118, 1, 1, 0 ), - 'REPLACE' => array( 119, 4, 1, 0 ), - 'SUBSTITUTE' => array( 120, -1, 1, 0 ), - 'CODE' => array( 121, 1, 1, 0 ), - 'FIND' => array( 124, -1, 1, 0 ), - 'CELL' => array( 125, -1, 0, 1 ), - 'ISERR' => array( 126, 1, 1, 0 ), - 'ISTEXT' => array( 127, 1, 1, 0 ), - 'ISNUMBER' => array( 128, 1, 1, 0 ), - 'ISBLANK' => array( 129, 1, 1, 0 ), - 'T' => array( 130, 1, 0, 0 ), - 'N' => array( 131, 1, 0, 0 ), - 'DATEVALUE' => array( 140, 1, 1, 0 ), - 'TIMEVALUE' => array( 141, 1, 1, 0 ), - 'SLN' => array( 142, 3, 1, 0 ), - 'SYD' => array( 143, 4, 1, 0 ), - 'DDB' => array( 144, -1, 1, 0 ), - 'INDIRECT' => array( 148, -1, 1, 1 ), - 'CALL' => array( 150, -1, 1, 0 ), - 'CLEAN' => array( 162, 1, 1, 0 ), - 'MDETERM' => array( 163, 1, 2, 0 ), - 'MINVERSE' => array( 164, 1, 2, 0 ), - 'MMULT' => array( 165, 2, 2, 0 ), - 'IPMT' => array( 167, -1, 1, 0 ), - 'PPMT' => array( 168, -1, 1, 0 ), - 'COUNTA' => array( 169, -1, 0, 0 ), - 'PRODUCT' => array( 183, -1, 0, 0 ), - 'FACT' => array( 184, 1, 1, 0 ), - 'DPRODUCT' => array( 189, 3, 0, 0 ), - 'ISNONTEXT' => array( 190, 1, 1, 0 ), - 'STDEVP' => array( 193, -1, 0, 0 ), - 'VARP' => array( 194, -1, 0, 0 ), - 'DSTDEVP' => array( 195, 3, 0, 0 ), - 'DVARP' => array( 196, 3, 0, 0 ), - 'TRUNC' => array( 197, -1, 1, 0 ), - 'ISLOGICAL' => array( 198, 1, 1, 0 ), - 'DCOUNTA' => array( 199, 3, 0, 0 ), - 'USDOLLAR' => array( 204, -1, 1, 0 ), - 'FINDB' => array( 205, -1, 1, 0 ), - 'SEARCHB' => array( 206, -1, 1, 0 ), - 'REPLACEB' => array( 207, 4, 1, 0 ), - 'LEFTB' => array( 208, -1, 1, 0 ), - 'RIGHTB' => array( 209, -1, 1, 0 ), - 'MIDB' => array( 210, 3, 1, 0 ), - 'LENB' => array( 211, 1, 1, 0 ), - 'ROUNDUP' => array( 212, 2, 1, 0 ), - 'ROUNDDOWN' => array( 213, 2, 1, 0 ), - 'ASC' => array( 214, 1, 1, 0 ), - 'DBCS' => array( 215, 1, 1, 0 ), - 'RANK' => array( 216, -1, 0, 0 ), - 'ADDRESS' => array( 219, -1, 1, 0 ), - 'DAYS360' => array( 220, -1, 1, 0 ), - 'TODAY' => array( 221, 0, 1, 1 ), - 'VDB' => array( 222, -1, 1, 0 ), - 'MEDIAN' => array( 227, -1, 0, 0 ), - 'SUMPRODUCT' => array( 228, -1, 2, 0 ), - 'SINH' => array( 229, 1, 1, 0 ), - 'COSH' => array( 230, 1, 1, 0 ), - 'TANH' => array( 231, 1, 1, 0 ), - 'ASINH' => array( 232, 1, 1, 0 ), - 'ACOSH' => array( 233, 1, 1, 0 ), - 'ATANH' => array( 234, 1, 1, 0 ), - 'DGET' => array( 235, 3, 0, 0 ), - 'INFO' => array( 244, 1, 1, 1 ), - 'DB' => array( 247, -1, 1, 0 ), - 'FREQUENCY' => array( 252, 2, 0, 0 ), - 'ERROR.TYPE' => array( 261, 1, 1, 0 ), - 'REGISTER.ID' => array( 267, -1, 1, 0 ), - 'AVEDEV' => array( 269, -1, 0, 0 ), - 'BETADIST' => array( 270, -1, 1, 0 ), - 'GAMMALN' => array( 271, 1, 1, 0 ), - 'BETAINV' => array( 272, -1, 1, 0 ), - 'BINOMDIST' => array( 273, 4, 1, 0 ), - 'CHIDIST' => array( 274, 2, 1, 0 ), - 'CHIINV' => array( 275, 2, 1, 0 ), - 'COMBIN' => array( 276, 2, 1, 0 ), - 'CONFIDENCE' => array( 277, 3, 1, 0 ), - 'CRITBINOM' => array( 278, 3, 1, 0 ), - 'EVEN' => array( 279, 1, 1, 0 ), - 'EXPONDIST' => array( 280, 3, 1, 0 ), - 'FDIST' => array( 281, 3, 1, 0 ), - 'FINV' => array( 282, 3, 1, 0 ), - 'FISHER' => array( 283, 1, 1, 0 ), - 'FISHERINV' => array( 284, 1, 1, 0 ), - 'FLOOR' => array( 285, 2, 1, 0 ), - 'GAMMADIST' => array( 286, 4, 1, 0 ), - 'GAMMAINV' => array( 287, 3, 1, 0 ), - 'CEILING' => array( 288, 2, 1, 0 ), - 'HYPGEOMDIST' => array( 289, 4, 1, 0 ), - 'LOGNORMDIST' => array( 290, 3, 1, 0 ), - 'LOGINV' => array( 291, 3, 1, 0 ), - 'NEGBINOMDIST' => array( 292, 3, 1, 0 ), - 'NORMDIST' => array( 293, 4, 1, 0 ), - 'NORMSDIST' => array( 294, 1, 1, 0 ), - 'NORMINV' => array( 295, 3, 1, 0 ), - 'NORMSINV' => array( 296, 1, 1, 0 ), - 'STANDARDIZE' => array( 297, 3, 1, 0 ), - 'ODD' => array( 298, 1, 1, 0 ), - 'PERMUT' => array( 299, 2, 1, 0 ), - 'POISSON' => array( 300, 3, 1, 0 ), - 'TDIST' => array( 301, 3, 1, 0 ), - 'WEIBULL' => array( 302, 4, 1, 0 ), - 'SUMXMY2' => array( 303, 2, 2, 0 ), - 'SUMX2MY2' => array( 304, 2, 2, 0 ), - 'SUMX2PY2' => array( 305, 2, 2, 0 ), - 'CHITEST' => array( 306, 2, 2, 0 ), - 'CORREL' => array( 307, 2, 2, 0 ), - 'COVAR' => array( 308, 2, 2, 0 ), - 'FORECAST' => array( 309, 3, 2, 0 ), - 'FTEST' => array( 310, 2, 2, 0 ), - 'INTERCEPT' => array( 311, 2, 2, 0 ), - 'PEARSON' => array( 312, 2, 2, 0 ), - 'RSQ' => array( 313, 2, 2, 0 ), - 'STEYX' => array( 314, 2, 2, 0 ), - 'SLOPE' => array( 315, 2, 2, 0 ), - 'TTEST' => array( 316, 4, 2, 0 ), - 'PROB' => array( 317, -1, 2, 0 ), - 'DEVSQ' => array( 318, -1, 0, 0 ), - 'GEOMEAN' => array( 319, -1, 0, 0 ), - 'HARMEAN' => array( 320, -1, 0, 0 ), - 'SUMSQ' => array( 321, -1, 0, 0 ), - 'KURT' => array( 322, -1, 0, 0 ), - 'SKEW' => array( 323, -1, 0, 0 ), - 'ZTEST' => array( 324, -1, 0, 0 ), - 'LARGE' => array( 325, 2, 0, 0 ), - 'SMALL' => array( 326, 2, 0, 0 ), - 'QUARTILE' => array( 327, 2, 0, 0 ), - 'PERCENTILE' => array( 328, 2, 0, 0 ), - 'PERCENTRANK' => array( 329, -1, 0, 0 ), - 'MODE' => array( 330, -1, 2, 0 ), - 'TRIMMEAN' => array( 331, 2, 0, 0 ), - 'TINV' => array( 332, 2, 1, 0 ), - 'CONCATENATE' => array( 336, -1, 1, 0 ), - 'POWER' => array( 337, 2, 1, 0 ), - 'RADIANS' => array( 342, 1, 1, 0 ), - 'DEGREES' => array( 343, 1, 1, 0 ), - 'SUBTOTAL' => array( 344, -1, 0, 0 ), - 'SUMIF' => array( 345, -1, 0, 0 ), - 'COUNTIF' => array( 346, 2, 0, 0 ), - 'COUNTBLANK' => array( 347, 1, 0, 0 ), - 'ISPMT' => array( 350, 4, 1, 0 ), - 'DATEDIF' => array( 351, 3, 1, 0 ), - 'DATESTRING' => array( 352, 1, 1, 0 ), - 'NUMBERSTRING' => array( 353, 2, 1, 0 ), - 'ROMAN' => array( 354, -1, 1, 0 ), - 'GETPIVOTDATA' => array( 358, -1, 0, 0 ), - 'HYPERLINK' => array( 359, -1, 1, 0 ), - 'PHONETIC' => array( 360, 1, 0, 0 ), - 'AVERAGEA' => array( 361, -1, 0, 0 ), - 'MAXA' => array( 362, -1, 0, 0 ), - 'MINA' => array( 363, -1, 0, 0 ), - 'STDEVPA' => array( 364, -1, 0, 0 ), - 'VARPA' => array( 365, -1, 0, 0 ), - 'STDEVA' => array( 366, -1, 0, 0 ), - 'VARA' => array( 367, -1, 0, 0 ), - 'BAHTTEXT' => array( 368, 1, 0, 0 ), - ); - } - - /** - * Convert a token to the proper ptg value. - * - * @access private - * @param mixed $token The token to convert. - * @return mixed the converted token on success - */ - function _convert($token) - { - if (preg_match("/\"([^\"]|\"\"){0,255}\"/", $token)) { - return $this->_convertString($token); - - } elseif (is_numeric($token)) { - return $this->_convertNumber($token); - - // match references like A1 or $A$1 - } elseif (preg_match('/^\$?([A-Ia-i]?[A-Za-z])\$?(\d+)$/',$token)) { - return $this->_convertRef2d($token); - - // match external references like Sheet1!A1 or Sheet1:Sheet2!A1 or Sheet1!$A$1 or Sheet1:Sheet2!$A$1 - } elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?[A-Ia-i]?[A-Za-z]\\$?(\d+)$/u",$token)) { - return $this->_convertRef3d($token); - - // match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1 or 'Sheet1'!$A$1 or 'Sheet1:Sheet2'!$A$1 - } elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?[A-Ia-i]?[A-Za-z]\\$?(\d+)$/u",$token)) { - return $this->_convertRef3d($token); - - // match ranges like A1:B2 or $A$1:$B$2 - } elseif (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/', $token)) { - return $this->_convertRange2d($token); - - // match external ranges like Sheet1!A1:B2 or Sheet1:Sheet2!A1:B2 or Sheet1!$A$1:$B$2 or Sheet1:Sheet2!$A$1:$B$2 - } elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)\:\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)$/u",$token)) { - return $this->_convertRange3d($token); - - // match external ranges like 'Sheet1'!A1:B2 or 'Sheet1:Sheet2'!A1:B2 or 'Sheet1'!$A$1:$B$2 or 'Sheet1:Sheet2'!$A$1:$B$2 - } elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)\:\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)$/u",$token)) { - return $this->_convertRange3d($token); - - // operators (including parentheses) - } elseif (isset($this->ptg[$token])) { - return pack("C", $this->ptg[$token]); - - // match error codes - } elseif (preg_match("/^#[A-Z0\/]{3,5}[!?]{1}$/", $token) or $token == '#N/A') { - return $this->_convertError($token); - - // commented so argument number can be processed correctly. See toReversePolish(). - /*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token)) - { - return($this->_convertFunction($token,$this->_func_args)); - }*/ - - // if it's an argument, ignore the token (the argument remains) - } elseif ($token == 'arg') { - return ''; - } - - // TODO: use real error codes - throw new Exception("Unknown token $token"); - } - - /** - * Convert a number token to ptgInt or ptgNum - * - * @access private - * @param mixed $num an integer or double for conversion to its ptg value - */ - function _convertNumber($num) - { - // Integer in the range 0..2**16-1 - if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) { - return pack("Cv", $this->ptg['ptgInt'], $num); - } else { // A float - if (PHPExcel_Writer_Excel5_BIFFwriter::getByteOrder()) { // if it's Big Endian - $num = strrev($num); - } - return pack("Cd", $this->ptg['ptgNum'], $num); - } - } - - /** - * Convert a string token to ptgStr - * - * @access private - * @param string $string A string for conversion to its ptg value. - * @return mixed the converted token on success - */ - function _convertString($string) - { - // chop away beggining and ending quotes - $string = substr($string, 1, strlen($string) - 2); - if (strlen($string) > 255) { - throw new Exception("String is too long"); - } - - return pack('C', $this->ptg['ptgStr']) . PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($string); - } - - /** - * Convert a function to a ptgFunc or ptgFuncVarV depending on the number of - * args that it takes. - * - * @access private - * @param string $token The name of the function for convertion to ptg value. - * @param integer $num_args The number of arguments the function receives. - * @return string The packed ptg for the function - */ - function _convertFunction($token, $num_args) - { - $args = $this->_functions[$token][1]; -// $volatile = $this->_functions[$token][3]; - - // Fixed number of args eg. TIME($i,$j,$k). - if ($args >= 0) { - return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]); - } - // Variable number of args eg. SUM($i,$j,$k, ..). - if ($args == -1) { - return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]); - } - } - - /** - * Convert an Excel range such as A1:D4 to a ptgRefV. - * - * @access private - * @param string $range An Excel range in the A1:A2 - * @param int $class - */ - function _convertRange2d($range, $class=0) - { - - // TODO: possible class value 0,1,2 check Formula.pm - // Split the range into 2 cell refs - if (preg_match('/^(\$)?([A-Ia-i]?[A-Za-z])(\$)?(\d+)\:(\$)?([A-Ia-i]?[A-Za-z])(\$)?(\d+)$/', $range)) { - list($cell1, $cell2) = explode(':', $range); - } else { - // TODO: use real error codes - throw new Exception("Unknown range separator"); - } - - // Convert the cell references - list($row1, $col1) = $this->_cellToPackedRowcol($cell1); - list($row2, $col2) = $this->_cellToPackedRowcol($cell2); - - // The ptg value depends on the class of the ptg. - if ($class == 0) { - $ptgArea = pack("C", $this->ptg['ptgArea']); - } elseif ($class == 1) { - $ptgArea = pack("C", $this->ptg['ptgAreaV']); - } elseif ($class == 2) { - $ptgArea = pack("C", $this->ptg['ptgAreaA']); - } else { - // TODO: use real error codes - throw new Exception("Unknown class $class"); - } - return $ptgArea . $row1 . $row2 . $col1. $col2; - } - - /** - * Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to - * a ptgArea3d. - * - * @access private - * @param string $token An Excel range in the Sheet1!A1:A2 format. - * @return mixed The packed ptgArea3d token on success. - */ - function _convertRange3d($token) - { -// $class = 0; // formulas like Sheet1!$A$1:$A$2 in list type data validation need this class (0x3B) - - // Split the ref at the ! symbol - list($ext_ref, $range) = explode('!', $token); - - // Convert the external reference part (different for BIFF8) - $ext_ref = $this->_getRefIndex($ext_ref); - - // Split the range into 2 cell refs - list($cell1, $cell2) = explode(':', $range); - - // Convert the cell references - if (preg_match("/^(\\$)?[A-Ia-i]?[A-Za-z](\\$)?(\d+)$/", $cell1)) { - list($row1, $col1) = $this->_cellToPackedRowcol($cell1); - list($row2, $col2) = $this->_cellToPackedRowcol($cell2); - } else { // It's a rows range (like 26:27) - list($row1, $col1, $row2, $col2) = $this->_rangeToPackedRange($cell1.':'.$cell2); - } - - // The ptg value depends on the class of the ptg. -// if ($class == 0) { - $ptgArea = pack("C", $this->ptg['ptgArea3d']); -// } elseif ($class == 1) { -// $ptgArea = pack("C", $this->ptg['ptgArea3dV']); -// } elseif ($class == 2) { -// $ptgArea = pack("C", $this->ptg['ptgArea3dA']); -// } else { -// throw new Exception("Unknown class $class"); -// } - - return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2; - } - - /** - * Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV. - * - * @access private - * @param string $cell An Excel cell reference - * @return string The cell in packed() format with the corresponding ptg - */ - function _convertRef2d($cell) - { -// $class = 2; // as far as I know, this is magick. - - // Convert the cell reference - $cell_array = $this->_cellToPackedRowcol($cell); - list($row, $col) = $cell_array; - - // The ptg value depends on the class of the ptg. -// if ($class == 0) { -// $ptgRef = pack("C", $this->ptg['ptgRef']); -// } elseif ($class == 1) { -// $ptgRef = pack("C", $this->ptg['ptgRefV']); -// } elseif ($class == 2) { - $ptgRef = pack("C", $this->ptg['ptgRefA']); -// } else { -// // TODO: use real error codes -// throw new Exception("Unknown class $class"); -// } - return $ptgRef.$row.$col; - } - - /** - * Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a - * ptgRef3d. - * - * @access private - * @param string $cell An Excel cell reference - * @return mixed The packed ptgRef3d token on success. - */ - function _convertRef3d($cell) - { -// $class = 2; // as far as I know, this is magick. - - // Split the ref at the ! symbol - list($ext_ref, $cell) = explode('!', $cell); - - // Convert the external reference part (different for BIFF8) - $ext_ref = $this->_getRefIndex($ext_ref); - - // Convert the cell reference part - list($row, $col) = $this->_cellToPackedRowcol($cell); - - // The ptg value depends on the class of the ptg. -// if ($class == 0) { -// $ptgRef = pack("C", $this->ptg['ptgRef3d']); -// } elseif ($class == 1) { -// $ptgRef = pack("C", $this->ptg['ptgRef3dV']); -// } elseif ($class == 2) { - $ptgRef = pack("C", $this->ptg['ptgRef3dA']); -// } else { -// throw new Exception("Unknown class $class"); -// } - - return $ptgRef . $ext_ref. $row . $col; - } - - /** - * Convert an error code to a ptgErr - * - * @access private - * @param string $errorCode The error code for conversion to its ptg value - * @return string The error code ptgErr - */ - function _convertError($errorCode) - { - switch ($errorCode) { - case '#NULL!': return pack("C", 0x00); - case '#DIV/0!': return pack("C", 0x07); - case '#VALUE!': return pack("C", 0x0F); - case '#REF!': return pack("C", 0x17); - case '#NAME?': return pack("C", 0x1D); - case '#NUM!': return pack("C", 0x24); - case '#N/A': return pack("C", 0x2A); - } - return pack("C", 0xFF); - } - - /** - * Convert the sheet name part of an external reference, for example "Sheet1" or - * "Sheet1:Sheet2", to a packed structure. - * - * @access private - * @param string $ext_ref The name of the external reference - * @return string The reference index in packed() format - */ - function _packExtRef($ext_ref) - { - $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any. - $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any. - - // Check if there is a sheet range eg., Sheet1:Sheet2. - if (preg_match("/:/", $ext_ref)) { - list($sheet_name1, $sheet_name2) = explode(':', $ext_ref); - - $sheet1 = $this->_getSheetIndex($sheet_name1); - if ($sheet1 == -1) { - throw new Exception("Unknown sheet name $sheet_name1 in formula"); - } - $sheet2 = $this->_getSheetIndex($sheet_name2); - if ($sheet2 == -1) { - throw new Exception("Unknown sheet name $sheet_name2 in formula"); - } - - // Reverse max and min sheet numbers if necessary - if ($sheet1 > $sheet2) { - list($sheet1, $sheet2) = array($sheet2, $sheet1); - } - } else { // Single sheet name only. - $sheet1 = $this->_getSheetIndex($ext_ref); - if ($sheet1 == -1) { - throw new Exception("Unknown sheet name $ext_ref in formula"); - } - $sheet2 = $sheet1; - } - - // References are stored relative to 0xFFFF. - $offset = -1 - $sheet1; - - return pack('vdvv', $offset, 0x00, $sheet1, $sheet2); - } - - /** - * Look up the REF index that corresponds to an external sheet name - * (or range). If it doesn't exist yet add it to the workbook's references - * array. It assumes all sheet names given must exist. - * - * @access private - * @param string $ext_ref The name of the external reference - * @return mixed The reference index in packed() format on success - */ - function _getRefIndex($ext_ref) - { - $ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any. - $ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any. - $ext_ref = str_replace('\'\'', '\'', $ext_ref); // Replace escaped '' with ' - - // Check if there is a sheet range eg., Sheet1:Sheet2. - if (preg_match("/:/", $ext_ref)) { - list($sheet_name1, $sheet_name2) = explode(':', $ext_ref); - - $sheet1 = $this->_getSheetIndex($sheet_name1); - if ($sheet1 == -1) { - throw new Exception("Unknown sheet name $sheet_name1 in formula"); - } - $sheet2 = $this->_getSheetIndex($sheet_name2); - if ($sheet2 == -1) { - throw new Exception("Unknown sheet name $sheet_name2 in formula"); - } - - // Reverse max and min sheet numbers if necessary - if ($sheet1 > $sheet2) { - list($sheet1, $sheet2) = array($sheet2, $sheet1); - } - } else { // Single sheet name only. - $sheet1 = $this->_getSheetIndex($ext_ref); - if ($sheet1 == -1) { - throw new Exception("Unknown sheet name $ext_ref in formula"); - } - $sheet2 = $sheet1; - } - - // assume all references belong to this document - $supbook_index = 0x00; - $ref = pack('vvv', $supbook_index, $sheet1, $sheet2); - $total_references = count($this->_references); - $index = -1; - for ($i = 0; $i < $total_references; ++$i) { - if ($ref == $this->_references[$i]) { - $index = $i; - break; - } - } - // if REF was not found add it to references array - if ($index == -1) { - $this->_references[$total_references] = $ref; - $index = $total_references; - } - - return pack('v', $index); - } - - /** - * Look up the index that corresponds to an external sheet name. The hash of - * sheet names is updated by the addworksheet() method of the - * PHPExcel_Writer_Excel5_Workbook class. - * - * @access private - * @param string $sheet_name Sheet name - * @return integer The sheet index, -1 if the sheet was not found - */ - function _getSheetIndex($sheet_name) - { - if (!isset($this->_ext_sheets[$sheet_name])) { - return -1; - } else { - return $this->_ext_sheets[$sheet_name]; - } - } - - /** - * This method is used to update the array of sheet names. It is - * called by the addWorksheet() method of the - * PHPExcel_Writer_Excel5_Workbook class. - * - * @access public - * @see PHPExcel_Writer_Excel5_Workbook::addWorksheet() - * @param string $name The name of the worksheet being added - * @param integer $index The index of the worksheet being added - */ - function setExtSheet($name, $index) - { - $this->_ext_sheets[$name] = $index; - } - - /** - * pack() row and column into the required 3 or 4 byte format. - * - * @access private - * @param string $cell The Excel cell reference to be packed - * @return array Array containing the row and column in packed() format - */ - function _cellToPackedRowcol($cell) - { - $cell = strtoupper($cell); - list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell); - if ($col >= 256) { - throw new Exception("Column in: $cell greater than 255"); - } - // FIXME: change for BIFF8 - if ($row >= 16384) { - throw new Exception("Row in: $cell greater than 16384 "); - } - - // Set the high bits to indicate if row or col are relative. - $col |= $col_rel << 14; - $col |= $row_rel << 15; - $col = pack('v', $col); - - $row = pack('v', $row); - - return array($row, $col); - } - - /** - * pack() row range into the required 3 or 4 byte format. - * Just using maximum col/rows, which is probably not the correct solution - * - * @access private - * @param string $range The Excel range to be packed - * @return array Array containing (row1,col1,row2,col2) in packed() format - */ - function _rangeToPackedRange($range) - { - preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match); - // return absolute rows if there is a $ in the ref - $row1_rel = empty($match[1]) ? 1 : 0; - $row1 = $match[2]; - $row2_rel = empty($match[3]) ? 1 : 0; - $row2 = $match[4]; - // Convert 1-index to zero-index - --$row1; - --$row2; - // Trick poor inocent Excel - $col1 = 0; - $col2 = 16383; // FIXME: maximum possible value for Excel 5 (change this!!!) - - // FIXME: this changes for BIFF8 - if (($row1 >= 16384) or ($row2 >= 16384)) { - throw new Exception("Row in: $range greater than 16384 "); - } - - // Set the high bits to indicate if rows are relative. - $col1 |= $row1_rel << 15; - $col2 |= $row2_rel << 15; - $col1 = pack('v', $col1); - $col2 = pack('v', $col2); - - $row1 = pack('v', $row1); - $row2 = pack('v', $row2); - - return array($row1, $col1, $row2, $col2); - } - - /** - * Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero - * indexed row and column number. Also returns two (0,1) values to indicate - * whether the row or column are relative references. - * - * @access private - * @param string $cell The Excel cell reference in A1 format. - * @return array - */ - function _cellToRowcol($cell) - { - preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match); - // return absolute column if there is a $ in the ref - $col_rel = empty($match[1]) ? 1 : 0; - $col_ref = $match[2]; - $row_rel = empty($match[3]) ? 1 : 0; - $row = $match[4]; - - // Convert base26 column string to a number. - $expn = strlen($col_ref) - 1; - $col = 0; - $col_ref_length = strlen($col_ref); - for ($i = 0; $i < $col_ref_length; ++$i) { - $col += (ord($col_ref{$i}) - 64) * pow(26, $expn); - --$expn; - } - - // Convert 1-index to zero-index - --$row; - --$col; - - return array($row, $col, $row_rel, $col_rel); - } - - /** - * Advance to the next valid token. - * - * @access private - */ - function _advance() - { - $i = $this->_current_char; - $formula_length = strlen($this->_formula); - // eat up white spaces - if ($i < $formula_length) { - while ($this->_formula{$i} == " ") { - ++$i; - } - - if ($i < ($formula_length - 1)) { - $this->_lookahead = $this->_formula{$i+1}; - } - $token = ''; - } - - while ($i < $formula_length) { - $token .= $this->_formula{$i}; - - if ($i < ($formula_length - 1)) { - $this->_lookahead = $this->_formula{$i+1}; - } else { - $this->_lookahead = ''; - } - - if ($this->_match($token) != '') { - //if ($i < strlen($this->_formula) - 1) { - // $this->_lookahead = $this->_formula{$i+1}; - //} - $this->_current_char = $i + 1; - $this->_current_token = $token; - return 1; - } - - if ($i < ($formula_length - 2)) { - $this->_lookahead = $this->_formula{$i+2}; - } else { // if we run out of characters _lookahead becomes empty - $this->_lookahead = ''; - } - ++$i; - } - //die("Lexical error ".$this->_current_char); - } - - /** - * Checks if it's a valid token. - * - * @access private - * @param mixed $token The token to check. - * @return mixed The checked token or false on failure - */ - function _match($token) - { - switch($token) { - case "+": - case "-": - case "*": - case "/": - case "(": - case ")": - case ",": - case ";": - case ">=": - case "<=": - case "=": - case "<>": - case "^": - case "&": - case "%": - return $token; - break; - case ">": - if ($this->_lookahead == '=') { // it's a GE token - break; - } - return $token; - break; - case "<": - // it's a LE or a NE token - if (($this->_lookahead == '=') or ($this->_lookahead == '>')) { - break; - } - return $token; - break; - default: - // if it's a reference A1 or $A$1 or $A1 or A$1 - if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and - !preg_match("/[0-9]/",$this->_lookahead) and - ($this->_lookahead != ':') and ($this->_lookahead != '.') and - ($this->_lookahead != '!')) - { - return $token; - } - // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1 or Sheet1!$A$1 or Sheet1:Sheet2!$A$1) - elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$token) and - !preg_match("/[0-9]/",$this->_lookahead) and - ($this->_lookahead != ':') and ($this->_lookahead != '.')) - { - return $token; - } - // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1 or 'Sheet1'!$A$1 or 'Sheet1:Sheet2'!$A$1) - elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$token) and - !preg_match("/[0-9]/",$this->_lookahead) and - ($this->_lookahead != ':') and ($this->_lookahead != '.')) - { - return $token; - } - // if it's a range A1:A2 or $A$1:$A$2 - elseif (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/', $token) and - !preg_match("/[0-9]/",$this->_lookahead)) - { - return $token; - } - // If it's an external range like Sheet1!A1:B2 or Sheet1:Sheet2!A1:B2 or Sheet1!$A$1:$B$2 or Sheet1:Sheet2!$A$1:$B$2 - elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$token) and - !preg_match("/[0-9]/",$this->_lookahead)) - { - return $token; - } - // If it's an external range like 'Sheet1'!A1:B2 or 'Sheet1:Sheet2'!A1:B2 or 'Sheet1'!$A$1:$B$2 or 'Sheet1:Sheet2'!$A$1:$B$2 - elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$token) and - !preg_match("/[0-9]/",$this->_lookahead)) - { - return $token; - } - // If it's a number (check that it's not a sheet name or range) - elseif (is_numeric($token) and - (!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and - ($this->_lookahead != '!') and ($this->_lookahead != ':')) - { - return $token; - } - // If it's a string (of maximum 255 characters) - elseif (preg_match("/\"([^\"]|\"\"){0,255}\"/",$token) and $this->_lookahead != '"' and (substr_count($token, '"')%2 == 0)) - { - return $token; - } - // If it's an error code - elseif (preg_match("/^#[A-Z0\/]{3,5}[!?]{1}$/", $token) or $token == '#N/A') - { - return $token; - } - // if it's a function call - elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "(")) - { - return $token; - } - // It's an argument of some description (e.g. a named range), - // precise nature yet to be determined - elseif(substr($token,-1) == ')') { - return $token; - } - return ''; - } - } - - /** - * The parsing method. It parses a formula. - * - * @access public - * @param string $formula The formula to parse, without the initial equal - * sign (=). - * @return mixed true on success - */ - function parse($formula) - { - $this->_current_char = 0; - $this->_formula = $formula; - $this->_lookahead = isset($formula{1}) ? $formula{1} : ''; - $this->_advance(); - $this->_parse_tree = $this->_condition(); - return true; - } - - /** - * It parses a condition. It assumes the following rule: - * Cond -> Expr [(">" | "<") Expr] - * - * @access private - * @return mixed The parsed ptg'd tree on success - */ - function _condition() - { - $result = $this->_expression(); - if ($this->_current_token == "<") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgLT', $result, $result2); - } elseif ($this->_current_token == ">") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgGT', $result, $result2); - } elseif ($this->_current_token == "<=") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgLE', $result, $result2); - } elseif ($this->_current_token == ">=") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgGE', $result, $result2); - } elseif ($this->_current_token == "=") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgEQ', $result, $result2); - } elseif ($this->_current_token == "<>") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgNE', $result, $result2); - } elseif ($this->_current_token == "&") { - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgConcat', $result, $result2); - } - return $result; - } - - /** - * It parses a expression. It assumes the following rule: - * Expr -> Term [("+" | "-") Term] - * -> "string" - * -> "-" Term : Negative value - * -> "+" Term : Positive value - * -> Error code - * - * @access private - * @return mixed The parsed ptg'd tree on success - */ - function _expression() - { - // If it's a string return a string node - if (preg_match("/\"([^\"]|\"\"){0,255}\"/", $this->_current_token)) { - $tmp = str_replace('""', '"', $this->_current_token); - if (($tmp == '"') || ($tmp == '')) $tmp = '""'; // Trap for "" that has been used for an empty string - $result = $this->_createTree($tmp, '', ''); - $this->_advance(); - return $result; - // If it's an error code - } elseif (preg_match("/^#[A-Z0\/]{3,5}[!?]{1}$/", $this->_current_token) or $this->_current_token == '#N/A'){ - $result = $this->_createTree($this->_current_token, 'ptgErr', ''); - $this->_advance(); - return $result; - // If it's a negative value - } elseif ($this->_current_token == "-") { - // catch "-" Term - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgUminus', $result2, ''); - return $result; - // If it's a positive value - } elseif ($this->_current_token == "+") { - // catch "+" Term - $this->_advance(); - $result2 = $this->_expression(); - $result = $this->_createTree('ptgUplus', $result2, ''); - return $result; - } - $result = $this->_term(); - while (($this->_current_token == "+") or - ($this->_current_token == "-") or - ($this->_current_token == "^")) { - /**/ - if ($this->_current_token == "+") { - $this->_advance(); - $result2 = $this->_term(); - $result = $this->_createTree('ptgAdd', $result, $result2); - } elseif ($this->_current_token == "-") { - $this->_advance(); - $result2 = $this->_term(); - $result = $this->_createTree('ptgSub', $result, $result2); - } else { - $this->_advance(); - $result2 = $this->_term(); - $result = $this->_createTree('ptgPower', $result, $result2); - } - } - return $result; - } - - /** - * This function just introduces a ptgParen element in the tree, so that Excel - * doesn't get confused when working with a parenthesized formula afterwards. - * - * @access private - * @see _fact() - * @return array The parsed ptg'd tree - */ - function _parenthesizedExpression() - { - $result = $this->_createTree('ptgParen', $this->_expression(), ''); - return $result; - } - - /** - * It parses a term. It assumes the following rule: - * Term -> Fact [("*" | "/") Fact] - * - * @access private - * @return mixed The parsed ptg'd tree on success - */ - function _term() - { - $result = $this->_fact(); - while (($this->_current_token == "*") or - ($this->_current_token == "/")) { - /**/ - if ($this->_current_token == "*") { - $this->_advance(); - $result2 = $this->_fact(); - $result = $this->_createTree('ptgMul', $result, $result2); - } else { - $this->_advance(); - $result2 = $this->_fact(); - $result = $this->_createTree('ptgDiv', $result, $result2); - } - } - return $result; - } - - /** - * It parses a factor. It assumes the following rule: - * Fact -> ( Expr ) - * | CellRef - * | CellRange - * | Number - * | Function - * - * @access private - * @return mixed The parsed ptg'd tree on success - */ - function _fact() - { - if ($this->_current_token == "(") { - $this->_advance(); // eat the "(" - $result = $this->_parenthesizedExpression(); - if ($this->_current_token != ")") { - throw new Exception("')' token expected."); - } - $this->_advance(); // eat the ")" - return $result; - } - // if it's a reference - if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token)) - { - $result = $this->_createTree($this->_current_token, '', ''); - $this->_advance(); - return $result; - } - // If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1 or Sheet1!$A$1 or Sheet1:Sheet2!$A$1) - elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$this->_current_token)) - { - $result = $this->_createTree($this->_current_token, '', ''); - $this->_advance(); - return $result; - } - // If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1 or 'Sheet1'!$A$1 or 'Sheet1:Sheet2'!$A$1) - elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$this->_current_token)) - { - $result = $this->_createTree($this->_current_token, '', ''); - $this->_advance(); - return $result; - } - // if it's a range A1:B2 or $A$1:$B$2 - elseif (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/',$this->_current_token) or - preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/',$this->_current_token)) - { - // must be an error? - $result = $this->_createTree($this->_current_token, '', ''); - $this->_advance(); - return $result; - } - // If it's an external range (Sheet1!A1:B2 or Sheet1:Sheet2!A1:B2 or Sheet1!$A$1:$B$2 or Sheet1:Sheet2!$A$1:$B$2) - elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$this->_current_token)) - { - // must be an error? - //$result = $this->_current_token; - $result = $this->_createTree($this->_current_token, '', ''); - $this->_advance(); - return $result; - } - // If it's an external range ('Sheet1'!A1:B2 or 'Sheet1'!A1:B2 or 'Sheet1'!$A$1:$B$2 or 'Sheet1'!$A$1:$B$2) - elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$this->_current_token)) - { - // must be an error? - //$result = $this->_current_token; - $result = $this->_createTree($this->_current_token, '', ''); - $this->_advance(); - return $result; - } - // If it's a number or a percent - elseif (is_numeric($this->_current_token)) - { - if($this->_lookahead == '%'){ - $result = $this->_createTree('ptgPercent', $this->_current_token, ''); - } else { - $result = $this->_createTree($this->_current_token, '', ''); - } - $this->_advance(); - return $result; - } - // if it's a function call - elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token)) - { - $result = $this->_func(); - return $result; - } - throw new Exception("Syntax error: ".$this->_current_token. - ", lookahead: ".$this->_lookahead. - ", current char: ".$this->_current_char); - } - - /** - * It parses a function call. It assumes the following rule: - * Func -> ( Expr [,Expr]* ) - * - * @access private - * @return mixed The parsed ptg'd tree on success - */ - function _func() - { - $num_args = 0; // number of arguments received - $function = strtoupper($this->_current_token); - $result = ''; // initialize result - $this->_advance(); - $this->_advance(); // eat the "(" - while ($this->_current_token != ')') { - /**/ - if ($num_args > 0) { - if ($this->_current_token == "," or - $this->_current_token == ";") - { - $this->_advance(); // eat the "," or ";" - } else { - throw new Exception("Syntax error: comma expected in ". - "function $function, arg #{$num_args}"); - } - $result2 = $this->_condition(); - $result = $this->_createTree('arg', $result, $result2); - } else { // first argument - $result2 = $this->_condition(); - $result = $this->_createTree('arg', '', $result2); - } - ++$num_args; - } - if (!isset($this->_functions[$function])) { - throw new Exception("Function $function() doesn't exist"); - } - $args = $this->_functions[$function][1]; - // If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid. - if (($args >= 0) and ($args != $num_args)) { - throw new Exception("Incorrect number of arguments in function $function() "); - } - - $result = $this->_createTree($function, $result, $num_args); - $this->_advance(); // eat the ")" - return $result; - } - - /** - * Creates a tree. In fact an array which may have one or two arrays (sub-trees) - * as elements. - * - * @access private - * @param mixed $value The value of this node. - * @param mixed $left The left array (sub-tree) or a final node. - * @param mixed $right The right array (sub-tree) or a final node. - * @return array A tree - */ - function _createTree($value, $left, $right) - { - return array('value' => $value, 'left' => $left, 'right' => $right); - } - - /** - * Builds a string containing the tree in reverse polish notation (What you - * would use in a HP calculator stack). - * The following tree: - * - * + - * / \ - * 2 3 - * - * produces: "23+" - * - * The following tree: - * - * + - * / \ - * 3 * - * / \ - * 6 A1 - * - * produces: "36A1*+" - * - * In fact all operands, functions, references, etc... are written as ptg's - * - * @access public - * @param array $tree The optional tree to convert. - * @return string The tree in reverse polish notation - */ - function toReversePolish($tree = array()) - { - $polish = ""; // the string we are going to return - if (empty($tree)) { // If it's the first call use _parse_tree - $tree = $this->_parse_tree; - } - - if (is_array($tree['left'])) { - $converted_tree = $this->toReversePolish($tree['left']); - $polish .= $converted_tree; - } elseif ($tree['left'] != '') { // It's a final node - $converted_tree = $this->_convert($tree['left']); - $polish .= $converted_tree; - } - if (is_array($tree['right'])) { - $converted_tree = $this->toReversePolish($tree['right']); - $polish .= $converted_tree; - } elseif ($tree['right'] != '') { // It's a final node - $converted_tree = $this->_convert($tree['right']); - $polish .= $converted_tree; - } - // if it's a function convert it here (so we can set it's arguments) - if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and - !preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and - !preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and - !is_numeric($tree['value']) and - !isset($this->ptg[$tree['value']])) - { - // left subtree for a function is always an array. - if ($tree['left'] != '') { - $left_tree = $this->toReversePolish($tree['left']); - } else { - $left_tree = ''; - } - // add it's left subtree and return. - return $left_tree.$this->_convertFunction($tree['value'], $tree['right']); - } else { - $converted_tree = $this->_convert($tree['value']); - } - $polish .= $converted_tree; - return $polish; - } - -} diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/Workbook.php b/admin/survey/excel/PHPExcel/Writer/Excel5/Workbook.php deleted file mode 100644 index c9df493..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/Workbook.php +++ /dev/null @@ -1,1450 +0,0 @@ - -// * -// * The majority of this is _NOT_ my code. I simply ported it from the -// * PERL Spreadsheet::WriteExcel module. -// * -// * The author of the Spreadsheet::WriteExcel module is John McNamara -// * -// * -// * I _DO_ maintain this code, and John McNamara has nothing to do with the -// * porting of this code to PHP. Any questions directly related to this -// * class library should be directed to me. -// * -// * License Information: -// * -// * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets -// * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com -// * -// * This library is free software; you can redistribute it and/or -// * modify it under the terms of the GNU Lesser General Public -// * License as published by the Free Software Foundation; either -// * version 2.1 of the License, or (at your option) any later version. -// * -// * This library is distributed in the hope that it will be useful, -// * but WITHOUT ANY WARRANTY; without even the implied warranty of -// * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -// * Lesser General Public License for more details. -// * -// * You should have received a copy of the GNU Lesser General Public -// * License along with this library; if not, write to the Free Software -// * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA -// */ - - -/** - * PHPExcel_Writer_Excel5_Workbook - * - * @category PHPExcel - * @package PHPExcel_Writer_Excel5 - * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel) - */ -class PHPExcel_Writer_Excel5_Workbook extends PHPExcel_Writer_Excel5_BIFFwriter -{ - /** - * Formula parser - * - * @var PHPExcel_Writer_Excel5_Parser - */ - private $_parser; - - /** - * The BIFF file size for the workbook. - * @var integer - * @see _calcSheetOffsets() - */ - public $_biffsize; - - /** - * XF Writers - * @var PHPExcel_Writer_Excel5_Xf[] - */ - private $_xfWriters = array(); - - /** - * Array containing the colour palette - * @var array - */ - public $_palette; - - /** - * The codepage indicates the text encoding used for strings - * @var integer - */ - public $_codepage; - - /** - * The country code used for localization - * @var integer - */ - public $_country_code; - - /** - * Workbook - * @var PHPExcel - */ - private $_phpExcel; - - /** - * Fonts writers - * - * @var PHPExcel_Writer_Excel5_Font[] - */ - private $_fontWriters = array(); - - /** - * Added fonts. Maps from font's hash => index in workbook - * - * @var array - */ - private $_addedFonts = array(); - - /** - * Shared number formats - * - * @var array - */ - private $_numberFormats = array(); - - /** - * Added number formats. Maps from numberFormat's hash => index in workbook - * - * @var array - */ - private $_addedNumberFormats = array(); - - /** - * Sizes of the binary worksheet streams - * - * @var array - */ - private $_worksheetSizes = array(); - - /** - * Offsets of the binary worksheet streams relative to the start of the global workbook stream - * - * @var array - */ - private $_worksheetOffsets = array(); - - /** - * Total number of shared strings in workbook - * - * @var int - */ - private $_str_total; - - /** - * Number of unique shared strings in workbook - * - * @var int - */ - private $_str_unique; - - /** - * Array of unique shared strings in workbook - * - * @var array - */ - private $_str_table; - - /** - * Color cache - */ - private $_colors; - - /** - * Escher object corresponding to MSODRAWINGGROUP - * - * @var PHPExcel_Shared_Escher - */ - private $_escher; - - - /** - * Class constructor - * - * @param PHPExcel $phpExcel The Workbook - * @param int &$str_total Total number of strings - * @param int &$str_unique Total number of unique strings - * @param array &$str_table String Table - * @param array &$colors Colour Table - * @param mixed $parser The formula parser created for the Workbook - */ - public function __construct(PHPExcel $phpExcel = null, - &$str_total, &$str_unique, &$str_table, &$colors, - $parser ) - { - // It needs to call its parent's constructor explicitly - parent::__construct(); - - $this->_parser = $parser; - $this->_biffsize = 0; - $this->_palette = array(); - $this->_country_code = -1; - - $this->_str_total = &$str_total; - $this->_str_unique = &$str_unique; - $this->_str_table = &$str_table; - $this->_colors = &$colors; - $this->_setPaletteXl97(); - - $this->_phpExcel = $phpExcel; - - // set BIFFwriter limit for CONTINUE records - // $this->_limit = 8224; - $this->_codepage = 0x04B0; - - // Add empty sheets and Build color cache - $countSheets = $phpExcel->getSheetCount(); - for ($i = 0; $i < $countSheets; ++$i) { - $phpSheet = $phpExcel->getSheet($i); - - $this->_parser->setExtSheet($phpSheet->getTitle(), $i); // Register worksheet name with parser - - $supbook_index = 0x00; - $ref = pack('vvv', $supbook_index, $i, $i); - $this->_parser->_references[] = $ref; // Register reference with parser - - // Sheet tab colors? - if ($phpSheet->isTabColorSet()) { - $this->_addColor($phpSheet->getTabColor()->getRGB()); - } - } - - } - - /** - * Add a new XF writer - * - * @param PHPExcel_Style - * @param boolean Is it a style XF? - * @return int Index to XF record - */ - public function addXfWriter($style, $isStyleXf = false) - { - $xfWriter = new PHPExcel_Writer_Excel5_Xf($style); - $xfWriter->setIsStyleXf($isStyleXf); - - // Add the font if not already added - $fontIndex = $this->_addFont($style->getFont()); - - // Assign the font index to the xf record - $xfWriter->setFontIndex($fontIndex); - - // Background colors, best to treat these after the font so black will come after white in custom palette - $xfWriter->setFgColor($this->_addColor($style->getFill()->getStartColor()->getRGB())); - $xfWriter->setBgColor($this->_addColor($style->getFill()->getEndColor()->getRGB())); - $xfWriter->setBottomColor($this->_addColor($style->getBorders()->getBottom()->getColor()->getRGB())); - $xfWriter->setTopColor($this->_addColor($style->getBorders()->getTop()->getColor()->getRGB())); - $xfWriter->setRightColor($this->_addColor($style->getBorders()->getRight()->getColor()->getRGB())); - $xfWriter->setLeftColor($this->_addColor($style->getBorders()->getLeft()->getColor()->getRGB())); - $xfWriter->setDiagColor($this->_addColor($style->getBorders()->getDiagonal()->getColor()->getRGB())); - - // Add the number format if it is not a built-in one and not already added - if ($style->getNumberFormat()->getBuiltInFormatCode() === false) { - $numberFormatHashCode = $style->getNumberFormat()->getHashCode(); - - if (isset($this->_addedNumberFormats[$numberFormatHashCode])) { - $numberFormatIndex = $this->_addedNumberFormats[$numberFormatHashCode]; - } else { - $numberFormatIndex = 164 + count($this->_numberFormats); - $this->_numberFormats[$numberFormatIndex] = $style->getNumberFormat(); - $this->_addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex; - } - } else { - $numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode(); - } - - // Assign the number format index to xf record - $xfWriter->setNumberFormatIndex($numberFormatIndex); - - $this->_xfWriters[] = $xfWriter; - - $xfIndex = count($this->_xfWriters) - 1; - return $xfIndex; - } - - /** - * Add a font to added fonts - * - * @param PHPExcel_Style_Font $font - * @return int Index to FONT record - */ - public function _addFont(PHPExcel_Style_Font $font) - { - $fontHashCode = $font->getHashCode(); - if(isset($this->_addedFonts[$fontHashCode])){ - $fontIndex = $this->_addedFonts[$fontHashCode]; - } else { - $countFonts = count($this->_fontWriters); - $fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1; - - $fontWriter = new PHPExcel_Writer_Excel5_Font($font); - $fontWriter->setColorIndex($this->_addColor($font->getColor()->getRGB())); - $this->_fontWriters[] = $fontWriter; - - $this->_addedFonts[$fontHashCode] = $fontIndex; - } - return $fontIndex; - } - /** - * Alter color palette adding a custom color - * - * @param string $rgb E.g. 'FF00AA' - * @return int Color index - */ - private function _addColor($rgb) { - if (!isset($this->_colors[$rgb])) { - if (count($this->_colors) < 57) { - // then we add a custom color altering the palette - $colorIndex = 8 + count($this->_colors); - $this->_palette[$colorIndex] = - array( - hexdec(substr($rgb, 0, 2)), - hexdec(substr($rgb, 2, 2)), - hexdec(substr($rgb, 4)), - 0 - ); - $this->_colors[$rgb] = $colorIndex; - } else { - // no room for more custom colors, just map to black - $colorIndex = 0; - } - } else { - // fetch already added custom color - $colorIndex = $this->_colors[$rgb]; - } - - return $colorIndex; - } - - /** - * Sets the colour palette to the Excel 97+ default. - * - * @access private - */ - function _setPaletteXl97() - { - $this->_palette = array( - 0x08 => array(0x00, 0x00, 0x00, 0x00), - 0x09 => array(0xff, 0xff, 0xff, 0x00), - 0x0A => array(0xff, 0x00, 0x00, 0x00), - 0x0B => array(0x00, 0xff, 0x00, 0x00), - 0x0C => array(0x00, 0x00, 0xff, 0x00), - 0x0D => array(0xff, 0xff, 0x00, 0x00), - 0x0E => array(0xff, 0x00, 0xff, 0x00), - 0x0F => array(0x00, 0xff, 0xff, 0x00), - 0x10 => array(0x80, 0x00, 0x00, 0x00), - 0x11 => array(0x00, 0x80, 0x00, 0x00), - 0x12 => array(0x00, 0x00, 0x80, 0x00), - 0x13 => array(0x80, 0x80, 0x00, 0x00), - 0x14 => array(0x80, 0x00, 0x80, 0x00), - 0x15 => array(0x00, 0x80, 0x80, 0x00), - 0x16 => array(0xc0, 0xc0, 0xc0, 0x00), - 0x17 => array(0x80, 0x80, 0x80, 0x00), - 0x18 => array(0x99, 0x99, 0xff, 0x00), - 0x19 => array(0x99, 0x33, 0x66, 0x00), - 0x1A => array(0xff, 0xff, 0xcc, 0x00), - 0x1B => array(0xcc, 0xff, 0xff, 0x00), - 0x1C => array(0x66, 0x00, 0x66, 0x00), - 0x1D => array(0xff, 0x80, 0x80, 0x00), - 0x1E => array(0x00, 0x66, 0xcc, 0x00), - 0x1F => array(0xcc, 0xcc, 0xff, 0x00), - 0x20 => array(0x00, 0x00, 0x80, 0x00), - 0x21 => array(0xff, 0x00, 0xff, 0x00), - 0x22 => array(0xff, 0xff, 0x00, 0x00), - 0x23 => array(0x00, 0xff, 0xff, 0x00), - 0x24 => array(0x80, 0x00, 0x80, 0x00), - 0x25 => array(0x80, 0x00, 0x00, 0x00), - 0x26 => array(0x00, 0x80, 0x80, 0x00), - 0x27 => array(0x00, 0x00, 0xff, 0x00), - 0x28 => array(0x00, 0xcc, 0xff, 0x00), - 0x29 => array(0xcc, 0xff, 0xff, 0x00), - 0x2A => array(0xcc, 0xff, 0xcc, 0x00), - 0x2B => array(0xff, 0xff, 0x99, 0x00), - 0x2C => array(0x99, 0xcc, 0xff, 0x00), - 0x2D => array(0xff, 0x99, 0xcc, 0x00), - 0x2E => array(0xcc, 0x99, 0xff, 0x00), - 0x2F => array(0xff, 0xcc, 0x99, 0x00), - 0x30 => array(0x33, 0x66, 0xff, 0x00), - 0x31 => array(0x33, 0xcc, 0xcc, 0x00), - 0x32 => array(0x99, 0xcc, 0x00, 0x00), - 0x33 => array(0xff, 0xcc, 0x00, 0x00), - 0x34 => array(0xff, 0x99, 0x00, 0x00), - 0x35 => array(0xff, 0x66, 0x00, 0x00), - 0x36 => array(0x66, 0x66, 0x99, 0x00), - 0x37 => array(0x96, 0x96, 0x96, 0x00), - 0x38 => array(0x00, 0x33, 0x66, 0x00), - 0x39 => array(0x33, 0x99, 0x66, 0x00), - 0x3A => array(0x00, 0x33, 0x00, 0x00), - 0x3B => array(0x33, 0x33, 0x00, 0x00), - 0x3C => array(0x99, 0x33, 0x00, 0x00), - 0x3D => array(0x99, 0x33, 0x66, 0x00), - 0x3E => array(0x33, 0x33, 0x99, 0x00), - 0x3F => array(0x33, 0x33, 0x33, 0x00), - ); - } - - /** - * Assemble worksheets into a workbook and send the BIFF data to an OLE - * storage. - * - * @param array $pWorksheetSizes The sizes in bytes of the binary worksheet streams - * @return string Binary data for workbook stream - */ - public function writeWorkbook($pWorksheetSizes = null) - { - $this->_worksheetSizes = $pWorksheetSizes; - - // Calculate the number of selected worksheet tabs and call the finalization - // methods for each worksheet - $total_worksheets = $this->_phpExcel->getSheetCount(); - - // Add part 1 of the Workbook globals, what goes before the SHEET records - $this->_storeBof(0x0005); - $this->_writeCodepage(); - $this->_writeWindow1(); - - $this->_writeDatemode(); - $this->_writeAllFonts(); - $this->_writeAllNumFormats(); - $this->_writeAllXfs(); - $this->_writeAllStyles(); - $this->_writePalette(); - - // Prepare part 3 of the workbook global stream, what goes after the SHEET records - $part3 = ''; - if ($this->_country_code != -1) { - $part3 .= $this->_writeCountry(); - } - $part3 .= $this->_writeRecalcId(); - - $part3 .= $this->_writeSupbookInternal(); - /* TODO: store external SUPBOOK records and XCT and CRN records - in case of external references for BIFF8 */ - $part3 .= $this->_writeExternsheetBiff8(); - $part3 .= $this->_writeAllDefinedNamesBiff8(); - $part3 .= $this->_writeMsoDrawingGroup(); - $part3 .= $this->_writeSharedStringsTable(); - - $part3 .= $this->writeEof(); - - // Add part 2 of the Workbook globals, the SHEET records - $this->_calcSheetOffsets(); - for ($i = 0; $i < $total_worksheets; ++$i) { - $this->_writeBoundsheet($this->_phpExcel->getSheet($i), $this->_worksheetOffsets[$i]); - } - - // Add part 3 of the Workbook globals - $this->_data .= $part3; - - return $this->_data; - } - - /** - * Calculate offsets for Worksheet BOF records. - * - * @access private - */ - function _calcSheetOffsets() - { - $boundsheet_length = 10; // fixed length for a BOUNDSHEET record - - // size of Workbook globals part 1 + 3 - $offset = $this->_datasize; - - // add size of Workbook globals part 2, the length of the SHEET records - $total_worksheets = count($this->_phpExcel->getAllSheets()); - foreach ($this->_phpExcel->getWorksheetIterator() as $sheet) { - $offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle())); - } - - // add the sizes of each of the Sheet substreams, respectively - for ($i = 0; $i < $total_worksheets; ++$i) { - $this->_worksheetOffsets[$i] = $offset; - $offset += $this->_worksheetSizes[$i]; - } - $this->_biffsize = $offset; - } - - /** - * Store the Excel FONT records. - */ - private function _writeAllFonts() - { - foreach ($this->_fontWriters as $fontWriter) { - $this->_append($fontWriter->writeFont()); - } - } - - /** - * Store user defined numerical formats i.e. FORMAT records - */ - private function _writeAllNumFormats() - { - foreach ($this->_numberFormats as $numberFormatIndex => $numberFormat) { - $this->_writeNumFormat($numberFormat->getFormatCode(), $numberFormatIndex); - } - } - - /** - * Write all XF records. - */ - private function _writeAllXfs() - { - foreach ($this->_xfWriters as $xfWriter) { - $this->_append($xfWriter->writeXf()); - } - } - - /** - * Write all STYLE records. - */ - private function _writeAllStyles() - { - $this->_writeStyle(); - } - - /** - * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for - * the NAME records. - */ - private function _writeExterns() - { - $countSheets = $this->_phpExcel->getSheetCount(); - // Create EXTERNCOUNT with number of worksheets - $this->_writeExterncount($countSheets); - - // Create EXTERNSHEET for each worksheet - for ($i = 0; $i < $countSheets; ++$i) { - $this->_writeExternsheet($this->_phpExcel->getSheet($i)->getTitle()); - } - } - - /** - * Write the NAME record to define the print area and the repeat rows and cols. - */ - private function _writeNames() - { - // total number of sheets - $total_worksheets = $this->_phpExcel->getSheetCount(); - - // Create the print area NAME records - for ($i = 0; $i < $total_worksheets; ++$i) { - $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); - // Write a Name record if the print area has been defined - if ($sheetSetup->isPrintAreaSet()) { - // Print area - $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea()); - $printArea = $printArea[0]; - $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]); - $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]); - - $print_rowmin = $printArea[0][1] - 1; - $print_rowmax = $printArea[1][1] - 1; - $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1; - $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1; - - $this->_writeNameShort( - $i, // sheet index - 0x06, // NAME type - $print_rowmin, - $print_rowmax, - $print_colmin, - $print_colmax - ); - } - } - - // Create the print title NAME records - for ($i = 0; $i < $total_worksheets; ++$i) { - $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); - - // simultaneous repeatColumns repeatRows - if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { - $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); - $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; - $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; - - $repeat = $sheetSetup->getRowsToRepeatAtTop(); - $rowmin = $repeat[0] - 1; - $rowmax = $repeat[1] - 1; - - $this->_writeNameLong( - $i, // sheet index - 0x07, // NAME type - $rowmin, - $rowmax, - $colmin, - $colmax - ); - - // (exclusive) either repeatColumns or repeatRows - } else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { - - // Columns to repeat - if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { - $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); - $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; - $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; - } else { - $colmin = 0; - $colmax = 255; - } - - // Rows to repeat - if ($sheetSetup->isRowsToRepeatAtTopSet()) { - $repeat = $sheetSetup->getRowsToRepeatAtTop(); - $rowmin = $repeat[0] - 1; - $rowmax = $repeat[1] - 1; - } else { - $rowmin = 0; - $rowmax = 16383; - } - - $this->_writeNameShort( - $i, // sheet index - 0x07, // NAME type - $rowmin, - $rowmax, - $colmin, - $colmax - ); - } - } - } - - /** - * Writes all the DEFINEDNAME records (BIFF8). - * So far this is only used for repeating rows/columns (print titles) and print areas - */ - private function _writeAllDefinedNamesBiff8() - { - $chunk = ''; - - // Named ranges - if (count($this->_phpExcel->getNamedRanges()) > 0) { - // Loop named ranges - $namedRanges = $this->_phpExcel->getNamedRanges(); - foreach ($namedRanges as $namedRange) { - - // Create absolute coordinate - $range = PHPExcel_Cell::splitRange($namedRange->getRange()); - for ($i = 0; $i < count($range); $i++) { - $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]); - if (isset($range[$i][1])) { - $range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]); - } - } - $range = PHPExcel_Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2 - - // parse formula - try { - $error = $this->_parser->parse($range); - $formulaData = $this->_parser->toReversePolish(); - - // make sure tRef3d is of type tRef3dR (0x3A) - if (isset($formulaData{0}) and ($formulaData{0} == "\x7A" or $formulaData{0} == "\x5A")) { - $formulaData = "\x3A" . substr($formulaData, 1); - } - - if ($namedRange->getLocalOnly()) { - // local scope - $scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1; - } else { - // global scope - $scope = 0; - } - $chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false)); - - } catch(Exception $e) { - // do nothing - } - } - } - - // total number of sheets - $total_worksheets = $this->_phpExcel->getSheetCount(); - - // write the print titles (repeating rows, columns), if any - for ($i = 0; $i < $total_worksheets; ++$i) { - $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); - // simultaneous repeatColumns repeatRows - if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { - $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); - $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; - $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; - - $repeat = $sheetSetup->getRowsToRepeatAtTop(); - $rowmin = $repeat[0] - 1; - $rowmax = $repeat[1] - 1; - - // construct formula data manually - $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc - $formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d - $formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d - $formulaData .= pack('C', 0x10); // tList - - // store the DEFINEDNAME record - $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true)); - - // (exclusive) either repeatColumns or repeatRows - } else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { - - // Columns to repeat - if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { - $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); - $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; - $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; - } else { - $colmin = 0; - $colmax = 255; - } - // Rows to repeat - if ($sheetSetup->isRowsToRepeatAtTopSet()) { - $repeat = $sheetSetup->getRowsToRepeatAtTop(); - $rowmin = $repeat[0] - 1; - $rowmax = $repeat[1] - 1; - } else { - $rowmin = 0; - $rowmax = 65535; - } - - // construct formula data manually because parser does not recognize absolute 3d cell references - $formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax); - - // store the DEFINEDNAME record - $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true)); - } - } - - // write the print areas, if any - for ($i = 0; $i < $total_worksheets; ++$i) { - $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); - if ($sheetSetup->isPrintAreaSet()) { - // Print area, e.g. A3:J6,H1:X20 - $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea()); - $countPrintArea = count($printArea); - - $formulaData = ''; - for ($j = 0; $j < $countPrintArea; ++$j) { - $printAreaRect = $printArea[$j]; // e.g. A3:J6 - $printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]); - $printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]); - - $print_rowmin = $printAreaRect[0][1] - 1; - $print_rowmax = $printAreaRect[1][1] - 1; - $print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1; - $print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1; - - // construct formula data manually because parser does not recognize absolute 3d cell references - $formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); - - if ($j > 0) { - $formulaData .= pack('C', 0x10); // list operator token ',' - } - } - - // store the DEFINEDNAME record - $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true)); - } - } - - // write autofilters, if any - for ($i = 0; $i < $total_worksheets; ++$i) { - $sheetAutoFilter = $this->_phpExcel->getSheet($i)->getAutoFilter(); - $autoFilterRange = $sheetAutoFilter->getRange(); - if(!empty($autoFilterRange)) { - $rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange); - - //Autofilter built in name - $name = pack('C', 0x0D); - - $chunk .= $this->writeData($this->_writeShortNameBiff8($name, $i + 1, $rangeBounds, true)); - } - } - - return $chunk; - } - - /** - * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data - * - * @param string $name The name in UTF-8 - * @param string $formulaData The binary formula data - * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global - * @param boolean $isBuiltIn Built-in name? - * @return string Complete binary record data - */ - private function _writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false) - { - $record = 0x0018; - - // option flags - $options = $isBuiltIn ? 0x20 : 0x00; - - // length of the name, character count - $nlen = PHPExcel_Shared_String::CountCharacters($name); - - // name with stripped length field - $name = substr(PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($name), 2); - - // size of the formula (in bytes) - $sz = strlen($formulaData); - - // combine the parts - $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0) - . $name . $formulaData; - $length = strlen($data); - - $header = pack('vv', $record, $length); - - return $header . $data; - } - - /** - * Write a short NAME record - * - * @param string $name - * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global - * @param int[][] $range rangeboundaries - * @param bool $isHidden - * @return string Complete binary record data - * */ - private function _writeShortNameBiff8($name, $sheetIndex = 0, $rangeBounds, $isHidden = false){ - $record = 0x0018; - - // option flags - $options = ($isHidden ? 0x21 : 0x00); - - $extra = pack('Cvvvvv', - 0x3B, - $sheetIndex - 1, - $rangeBounds[0][1] - 1, - $rangeBounds[1][1] - 1, - $rangeBounds[0][0] - 1, - $rangeBounds[1][0] - 1); - - // size of the formula (in bytes) - $sz = strlen($extra); - - // combine the parts - $data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0) - . $name . $extra; - $length = strlen($data); - - $header = pack('vv', $record, $length); - - return $header . $data; - } - - /** - * Stores the CODEPAGE biff record. - */ - private function _writeCodepage() - { - $record = 0x0042; // Record identifier - $length = 0x0002; // Number of bytes to follow - $cv = $this->_codepage; // The code page - - $header = pack('vv', $record, $length); - $data = pack('v', $cv); - - $this->_append($header . $data); - } - - /** - * Write Excel BIFF WINDOW1 record. - */ - private function _writeWindow1() - { - $record = 0x003D; // Record identifier - $length = 0x0012; // Number of bytes to follow - - $xWn = 0x0000; // Horizontal position of window - $yWn = 0x0000; // Vertical position of window - $dxWn = 0x25BC; // Width of window - $dyWn = 0x1572; // Height of window - - $grbit = 0x0038; // Option flags - - // not supported by PHPExcel, so there is only one selected sheet, the active - $ctabsel = 1; // Number of workbook tabs selected - - $wTabRatio = 0x0258; // Tab to scrollbar ratio - - // not supported by PHPExcel, set to 0 - $itabFirst = 0; // 1st displayed worksheet - $itabCur = $this->_phpExcel->getActiveSheetIndex(); // Active worksheet - - $header = pack("vv", $record, $length); - $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn, - $grbit, - $itabCur, $itabFirst, - $ctabsel, $wTabRatio); - $this->_append($header . $data); - } - - /** - * Writes Excel BIFF BOUNDSHEET record. - * - * @param PHPExcel_Worksheet $sheet Worksheet name - * @param integer $offset Location of worksheet BOF - */ - private function _writeBoundsheet($sheet, $offset) - { - $sheetname = $sheet->getTitle(); - $record = 0x0085; // Record identifier - - // sheet state - switch ($sheet->getSheetState()) { - case PHPExcel_Worksheet::SHEETSTATE_VISIBLE: $ss = 0x00; break; - case PHPExcel_Worksheet::SHEETSTATE_HIDDEN: $ss = 0x01; break; - case PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN: $ss = 0x02; break; - default: $ss = 0x00; break; - } - - // sheet type - $st = 0x00; - - $grbit = 0x0000; // Visibility and sheet type - - $data = pack("VCC", $offset, $ss, $st); - $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheetname); - - $length = strlen($data); - $header = pack("vv", $record, $length); - $this->_append($header . $data); - } - - /** - * Write Internal SUPBOOK record - */ - private function _writeSupbookInternal() - { - $record = 0x01AE; // Record identifier - $length = 0x0004; // Bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("vv", $this->_phpExcel->getSheetCount(), 0x0401); - return $this->writeData($header . $data); - } - - /** - * Writes the Excel BIFF EXTERNSHEET record. These references are used by - * formulas. - * - */ - private function _writeExternsheetBiff8() - { - $total_references = count($this->_parser->_references); - $record = 0x0017; // Record identifier - $length = 2 + 6 * $total_references; // Number of bytes to follow - - $supbook_index = 0; // FIXME: only using internal SUPBOOK record - $header = pack("vv", $record, $length); - $data = pack('v', $total_references); - for ($i = 0; $i < $total_references; ++$i) { - $data .= $this->_parser->_references[$i]; - } - return $this->writeData($header . $data); - } - - /** - * Write Excel BIFF STYLE records. - */ - private function _writeStyle() - { - $record = 0x0293; // Record identifier - $length = 0x0004; // Bytes to follow - - $ixfe = 0x8000; // Index to cell style XF - $BuiltIn = 0x00; // Built-in style - $iLevel = 0xff; // Outline style level - - $header = pack("vv", $record, $length); - $data = pack("vCC", $ixfe, $BuiltIn, $iLevel); - $this->_append($header . $data); - } - - /** - * Writes Excel FORMAT record for non "built-in" numerical formats. - * - * @param string $format Custom format string - * @param integer $ifmt Format index code - */ - private function _writeNumFormat($format, $ifmt) - { - $record = 0x041E; // Record identifier - - $numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format); - $length = 2 + strlen($numberFormatString); // Number of bytes to follow - - - $header = pack("vv", $record, $length); - $data = pack("v", $ifmt) . $numberFormatString; - $this->_append($header . $data); - } - - /** - * Write DATEMODE record to indicate the date system in use (1904 or 1900). - */ - private function _writeDatemode() - { - $record = 0x0022; // Record identifier - $length = 0x0002; // Bytes to follow - - $f1904 = (PHPExcel_Shared_Date::getExcelCalendar() == PHPExcel_Shared_Date::CALENDAR_MAC_1904) ? - 1 : 0; // Flag for 1904 date system - - $header = pack("vv", $record, $length); - $data = pack("v", $f1904); - $this->_append($header . $data); - } - - /** - * Write BIFF record EXTERNCOUNT to indicate the number of external sheet - * references in the workbook. - * - * Excel only stores references to external sheets that are used in NAME. - * The workbook NAME record is required to define the print area and the repeat - * rows and columns. - * - * A similar method is used in Worksheet.php for a slightly different purpose. - * - * @param integer $cxals Number of external references - */ - private function _writeExterncount($cxals) - { - $record = 0x0016; // Record identifier - $length = 0x0002; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("v", $cxals); - $this->_append($header . $data); - } - - /** - * Writes the Excel BIFF EXTERNSHEET record. These references are used by - * formulas. NAME record is required to define the print area and the repeat - * rows and columns. - * - * A similar method is used in Worksheet.php for a slightly different purpose. - * - * @param string $sheetname Worksheet name - */ - private function _writeExternsheet($sheetname) - { - $record = 0x0017; // Record identifier - $length = 0x02 + strlen($sheetname); // Number of bytes to follow - - $cch = strlen($sheetname); // Length of sheet name - $rgch = 0x03; // Filename encoding - - $header = pack("vv", $record, $length); - $data = pack("CC", $cch, $rgch); - $this->_append($header . $data . $sheetname); - } - - /** - * Store the NAME record in the short format that is used for storing the print - * area, repeat rows only and repeat columns only. - * - * @param integer $index Sheet index - * @param integer $type Built-in name type - * @param integer $rowmin Start row - * @param integer $rowmax End row - * @param integer $colmin Start colum - * @param integer $colmax End column - */ - private function _writeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax) - { - $record = 0x0018; // Record identifier - $length = 0x0024; // Number of bytes to follow - - $grbit = 0x0020; // Option flags - $chKey = 0x00; // Keyboard shortcut - $cch = 0x01; // Length of text name - $cce = 0x0015; // Length of text definition - $ixals = $index + 1; // Sheet index - $itab = $ixals; // Equal to ixals - $cchCustMenu = 0x00; // Length of cust menu text - $cchDescription = 0x00; // Length of description text - $cchHelptopic = 0x00; // Length of help topic text - $cchStatustext = 0x00; // Length of status bar text - $rgch = $type; // Built-in name type - - $unknown03 = 0x3b; - $unknown04 = 0xffff-$index; - $unknown05 = 0x0000; - $unknown06 = 0x0000; - $unknown07 = 0x1087; - $unknown08 = 0x8005; - - $header = pack("vv", $record, $length); - $data = pack("v", $grbit); - $data .= pack("C", $chKey); - $data .= pack("C", $cch); - $data .= pack("v", $cce); - $data .= pack("v", $ixals); - $data .= pack("v", $itab); - $data .= pack("C", $cchCustMenu); - $data .= pack("C", $cchDescription); - $data .= pack("C", $cchHelptopic); - $data .= pack("C", $cchStatustext); - $data .= pack("C", $rgch); - $data .= pack("C", $unknown03); - $data .= pack("v", $unknown04); - $data .= pack("v", $unknown05); - $data .= pack("v", $unknown06); - $data .= pack("v", $unknown07); - $data .= pack("v", $unknown08); - $data .= pack("v", $index); - $data .= pack("v", $index); - $data .= pack("v", $rowmin); - $data .= pack("v", $rowmax); - $data .= pack("C", $colmin); - $data .= pack("C", $colmax); - $this->_append($header . $data); - } - - /** - * Store the NAME record in the long format that is used for storing the repeat - * rows and columns when both are specified. This shares a lot of code with - * _writeNameShort() but we use a separate method to keep the code clean. - * Code abstraction for reuse can be carried too far, and I should know. ;-) - * - * @param integer $index Sheet index - * @param integer $type Built-in name type - * @param integer $rowmin Start row - * @param integer $rowmax End row - * @param integer $colmin Start colum - * @param integer $colmax End column - */ - private function _writeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax) - { - $record = 0x0018; // Record identifier - $length = 0x003d; // Number of bytes to follow - $grbit = 0x0020; // Option flags - $chKey = 0x00; // Keyboard shortcut - $cch = 0x01; // Length of text name - $cce = 0x002e; // Length of text definition - $ixals = $index + 1; // Sheet index - $itab = $ixals; // Equal to ixals - $cchCustMenu = 0x00; // Length of cust menu text - $cchDescription = 0x00; // Length of description text - $cchHelptopic = 0x00; // Length of help topic text - $cchStatustext = 0x00; // Length of status bar text - $rgch = $type; // Built-in name type - - $unknown01 = 0x29; - $unknown02 = 0x002b; - $unknown03 = 0x3b; - $unknown04 = 0xffff-$index; - $unknown05 = 0x0000; - $unknown06 = 0x0000; - $unknown07 = 0x1087; - $unknown08 = 0x8008; - - $header = pack("vv", $record, $length); - $data = pack("v", $grbit); - $data .= pack("C", $chKey); - $data .= pack("C", $cch); - $data .= pack("v", $cce); - $data .= pack("v", $ixals); - $data .= pack("v", $itab); - $data .= pack("C", $cchCustMenu); - $data .= pack("C", $cchDescription); - $data .= pack("C", $cchHelptopic); - $data .= pack("C", $cchStatustext); - $data .= pack("C", $rgch); - $data .= pack("C", $unknown01); - $data .= pack("v", $unknown02); - // Column definition - $data .= pack("C", $unknown03); - $data .= pack("v", $unknown04); - $data .= pack("v", $unknown05); - $data .= pack("v", $unknown06); - $data .= pack("v", $unknown07); - $data .= pack("v", $unknown08); - $data .= pack("v", $index); - $data .= pack("v", $index); - $data .= pack("v", 0x0000); - $data .= pack("v", 0x3fff); - $data .= pack("C", $colmin); - $data .= pack("C", $colmax); - // Row definition - $data .= pack("C", $unknown03); - $data .= pack("v", $unknown04); - $data .= pack("v", $unknown05); - $data .= pack("v", $unknown06); - $data .= pack("v", $unknown07); - $data .= pack("v", $unknown08); - $data .= pack("v", $index); - $data .= pack("v", $index); - $data .= pack("v", $rowmin); - $data .= pack("v", $rowmax); - $data .= pack("C", 0x00); - $data .= pack("C", 0xff); - // End of data - $data .= pack("C", 0x10); - $this->_append($header . $data); - } - - /** - * Stores the COUNTRY record for localization - * - * @return string - */ - private function _writeCountry() - { - $record = 0x008C; // Record identifier - $length = 4; // Number of bytes to follow - - $header = pack('vv', $record, $length); - /* using the same country code always for simplicity */ - $data = pack('vv', $this->_country_code, $this->_country_code); - //$this->_append($header . $data); - return $this->writeData($header . $data); - } - - /** - * Write the RECALCID record - * - * @return string - */ - private function _writeRecalcId() - { - $record = 0x01C1; // Record identifier - $length = 8; // Number of bytes to follow - - $header = pack('vv', $record, $length); - - // by inspection of real Excel files, MS Office Excel 2007 writes this - $data = pack('VV', 0x000001C1, 0x00001E667); - - return $this->writeData($header . $data); - } - - /** - * Stores the PALETTE biff record. - */ - private function _writePalette() - { - $aref = $this->_palette; - - $record = 0x0092; // Record identifier - $length = 2 + 4 * count($aref); // Number of bytes to follow - $ccv = count($aref); // Number of RGB values to follow - $data = ''; // The RGB data - - // Pack the RGB data - foreach ($aref as $color) { - foreach ($color as $byte) { - $data .= pack("C",$byte); - } - } - - $header = pack("vvv", $record, $length, $ccv); - $this->_append($header . $data); - } - - /** - * Handling of the SST continue blocks is complicated by the need to include an - * additional continuation byte depending on whether the string is split between - * blocks or whether it starts at the beginning of the block. (There are also - * additional complications that will arise later when/if Rich Strings are - * supported). - * - * The Excel documentation says that the SST record should be followed by an - * EXTSST record. The EXTSST record is a hash table that is used to optimise - * access to SST. However, despite the documentation it doesn't seem to be - * required so we will ignore it. - * - * @return string Binary data - */ - private function _writeSharedStringsTable() - { - // maximum size of record data (excluding record header) - $continue_limit = 8224; - - // initialize array of record data blocks - $recordDatas = array(); - - // start SST record data block with total number of strings, total number of unique strings - $recordData = pack("VV", $this->_str_total, $this->_str_unique); - - // loop through all (unique) strings in shared strings table - foreach (array_keys($this->_str_table) as $string) { - - // here $string is a BIFF8 encoded string - - // length = character count - $headerinfo = unpack("vlength/Cencoding", $string); - - // currently, this is always 1 = uncompressed - $encoding = $headerinfo["encoding"]; - - // initialize finished writing current $string - $finished = false; - - while ($finished === false) { - - // normally, there will be only one cycle, but if string cannot immediately be written as is - // there will be need for more than one cylcle, if string longer than one record data block, there - // may be need for even more cycles - - if (strlen($recordData) + strlen($string) <= $continue_limit) { - // then we can write the string (or remainder of string) without any problems - $recordData .= $string; - - if (strlen($recordData) + strlen($string) == $continue_limit) { - // we close the record data block, and initialize a new one - $recordDatas[] = $recordData; - $recordData = ''; - } - - // we are finished writing this string - $finished = true; - } else { - // special treatment writing the string (or remainder of the string) - // If the string is very long it may need to be written in more than one CONTINUE record. - - // check how many bytes more there is room for in the current record - $space_remaining = $continue_limit - strlen($recordData); - - // minimum space needed - // uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character - // compressed: 2 byte string length length field + 1 byte option flags + 1 byte character - $min_space_needed = ($encoding == 1) ? 5 : 4; - - // We have two cases - // 1. space remaining is less than minimum space needed - // here we must waste the space remaining and move to next record data block - // 2. space remaining is greater than or equal to minimum space needed - // here we write as much as we can in the current block, then move to next record data block - - // 1. space remaining is less than minimum space needed - if ($space_remaining < $min_space_needed) { - // we close the block, store the block data - $recordDatas[] = $recordData; - - // and start new record data block where we start writing the string - $recordData = ''; - - // 2. space remaining is greater than or equal to minimum space needed - } else { - // initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below - $effective_space_remaining = $space_remaining; - - // for uncompressed strings, sometimes effective space remaining is reduced by 1 - if ( $encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1 ) { - --$effective_space_remaining; - } - - // one block fininshed, store the block data - $recordData .= substr($string, 0, $effective_space_remaining); - - $string = substr($string, $effective_space_remaining); // for next cycle in while loop - $recordDatas[] = $recordData; - - // start new record data block with the repeated option flags - $recordData = pack('C', $encoding); - } - } - } - } - - // Store the last record data block unless it is empty - // if there was no need for any continue records, this will be the for SST record data block itself - if (strlen($recordData) > 0) { - $recordDatas[] = $recordData; - } - - // combine into one chunk with all the blocks SST, CONTINUE,... - $chunk = ''; - foreach ($recordDatas as $i => $recordData) { - // first block should have the SST record header, remaing should have CONTINUE header - $record = ($i == 0) ? 0x00FC : 0x003C; - - $header = pack("vv", $record, strlen($recordData)); - $data = $header . $recordData; - - $chunk .= $this->writeData($data); - } - - return $chunk; - } - - /** - * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records. - */ - private function _writeMsoDrawingGroup() - { - // write the Escher stream if necessary - if (isset($this->_escher)) { - $writer = new PHPExcel_Writer_Excel5_Escher($this->_escher); - $data = $writer->close(); - - $record = 0x00EB; - $length = strlen($data); - $header = pack("vv", $record, $length); - - return $this->writeData($header . $data); - - } else { - return ''; - } - } - - /** - * Get Escher object - * - * @return PHPExcel_Shared_Escher - */ - public function getEscher() - { - return $this->_escher; - } - - /** - * Set Escher object - * - * @param PHPExcel_Shared_Escher $pValue - */ - public function setEscher(PHPExcel_Shared_Escher $pValue = null) - { - $this->_escher = $pValue; - } - -} diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/Worksheet.php b/admin/survey/excel/PHPExcel/Writer/Excel5/Worksheet.php deleted file mode 100644 index 7f5053e..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/Worksheet.php +++ /dev/null @@ -1,2954 +0,0 @@ - -// * -// * The majority of this is _NOT_ my code. I simply ported it from the -// * PERL Spreadsheet::WriteExcel module. -// * -// * The author of the Spreadsheet::WriteExcel module is John McNamara -// * -// * -// * I _DO_ maintain this code, and John McNamara has nothing to do with the -// * porting of this code to PHP. Any questions directly related to this -// * class library should be directed to me. -// * -// * License Information: -// * -// * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets -// * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com -// * -// * This library is free software; you can redistribute it and/or -// * modify it under the terms of the GNU Lesser General Public -// * License as published by the Free Software Foundation; either -// * version 2.1 of the License, or (at your option) any later version. -// * -// * This library is distributed in the hope that it will be useful, -// * but WITHOUT ANY WARRANTY; without even the implied warranty of -// * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -// * Lesser General Public License for more details. -// * -// * You should have received a copy of the GNU Lesser General Public -// * License along with this library; if not, write to the Free Software -// * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA -// */ - - -/** - * PHPExcel_Writer_Excel5_Worksheet - * - * @category PHPExcel - * @package PHPExcel_Writer_Excel5 - * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel) - */ -class PHPExcel_Writer_Excel5_Worksheet extends PHPExcel_Writer_Excel5_BIFFwriter -{ - /** - * Formula parser - * - * @var PHPExcel_Writer_Excel5_Parser - */ - private $_parser; - - /** - * Maximum number of characters for a string (LABEL record in BIFF5) - * @var integer - */ - public $_xls_strmax; - - /** - * Array containing format information for columns - * @var array - */ - public $_colinfo; - - /** - * Array containing the selected area for the worksheet - * @var array - */ - public $_selection; - - /** - * The active pane for the worksheet - * @var integer - */ - public $_active_pane; - - /** - * Whether to use outline. - * @var integer - */ - public $_outline_on; - - /** - * Auto outline styles. - * @var bool - */ - public $_outline_style; - - /** - * Whether to have outline summary below. - * @var bool - */ - public $_outline_below; - - /** - * Whether to have outline summary at the right. - * @var bool - */ - public $_outline_right; - - /** - * Reference to the total number of strings in the workbook - * @var integer - */ - public $_str_total; - - /** - * Reference to the number of unique strings in the workbook - * @var integer - */ - public $_str_unique; - - /** - * Reference to the array containing all the unique strings in the workbook - * @var array - */ - public $_str_table; - - /** - * Color cache - */ - private $_colors; - - /** - * Index of first used row (at least 0) - * @var int - */ - private $_firstRowIndex; - - /** - * Index of last used row. (no used rows means -1) - * @var int - */ - private $_lastRowIndex; - - /** - * Index of first used column (at least 0) - * @var int - */ - private $_firstColumnIndex; - - /** - * Index of last used column (no used columns means -1) - * @var int - */ - private $_lastColumnIndex; - - /** - * Sheet object - * @var PHPExcel_Worksheet - */ - public $_phpSheet; - - /** - * Count cell style Xfs - * - * @var int - */ - private $_countCellStyleXfs; - - /** - * Escher object corresponding to MSODRAWING - * - * @var PHPExcel_Shared_Escher - */ - private $_escher; - - /** - * Array of font hashes associated to FONT records index - * - * @var array - */ - public $_fntHashIndex; - - /** - * Constructor - * - * @param int &$str_total Total number of strings - * @param int &$str_unique Total number of unique strings - * @param array &$str_table String Table - * @param array &$colors Colour Table - * @param mixed $parser The formula parser created for the Workbook - * @param boolean $preCalculateFormulas Flag indicating whether formulas should be calculated or just written - * @param string $phpSheet The worksheet to write - * @param PHPExcel_Worksheet $phpSheet - */ - public function __construct(&$str_total, &$str_unique, &$str_table, &$colors, - $parser, $preCalculateFormulas, $phpSheet) - { - // It needs to call its parent's constructor explicitly - parent::__construct(); - - // change BIFFwriter limit for CONTINUE records -// $this->_limit = 8224; - - - $this->_preCalculateFormulas = $preCalculateFormulas; - $this->_str_total = &$str_total; - $this->_str_unique = &$str_unique; - $this->_str_table = &$str_table; - $this->_colors = &$colors; - $this->_parser = $parser; - - $this->_phpSheet = $phpSheet; - - //$this->ext_sheets = array(); - //$this->offset = 0; - $this->_xls_strmax = 255; - $this->_colinfo = array(); - $this->_selection = array(0,0,0,0); - $this->_active_pane = 3; - - $this->_print_headers = 0; - - $this->_outline_style = 0; - $this->_outline_below = 1; - $this->_outline_right = 1; - $this->_outline_on = 1; - - $this->_fntHashIndex = array(); - - // calculate values for DIMENSIONS record - $minR = 1; - $minC = 'A'; - - $maxR = $this->_phpSheet->getHighestRow(); - $maxC = $this->_phpSheet->getHighestColumn(); - - // Determine lowest and highest column and row -// $this->_firstRowIndex = ($minR > 65535) ? 65535 : $minR; - $this->_lastRowIndex = ($maxR > 65535) ? 65535 : $maxR ; - - $this->_firstColumnIndex = PHPExcel_Cell::columnIndexFromString($minC); - $this->_lastColumnIndex = PHPExcel_Cell::columnIndexFromString($maxC); - -// if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255; - if ($this->_lastColumnIndex > 255) $this->_lastColumnIndex = 255; - - $this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection()); - } - - /** - * Add data to the beginning of the workbook (note the reverse order) - * and to the end of the workbook. - * - * @access public - * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook() - */ - function close() - { - $_phpSheet = $this->_phpSheet; - - $num_sheets = $_phpSheet->getParent()->getSheetCount(); - - // Write BOF record - $this->_storeBof(0x0010); - - // Write PRINTHEADERS - $this->_writePrintHeaders(); - - // Write PRINTGRIDLINES - $this->_writePrintGridlines(); - - // Write GRIDSET - $this->_writeGridset(); - - // Calculate column widths - $_phpSheet->calculateColumnWidths(); - - // Column dimensions - if (($defaultWidth = $_phpSheet->getDefaultColumnDimension()->getWidth()) < 0) { - $defaultWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($_phpSheet->getParent()->getDefaultStyle()->getFont()); - } - - $columnDimensions = $_phpSheet->getColumnDimensions(); - $maxCol = $this->_lastColumnIndex -1; - for ($i = 0; $i <= $maxCol; ++$i) { - $hidden = 0; - $level = 0; - $xfIndex = 15; // there are 15 cell style Xfs - - $width = $defaultWidth; - - $columnLetter = PHPExcel_Cell::stringFromColumnIndex($i); - if (isset($columnDimensions[$columnLetter])) { - $columnDimension = $columnDimensions[$columnLetter]; - if ($columnDimension->getWidth() >= 0) { - $width = $columnDimension->getWidth(); - } - $hidden = $columnDimension->getVisible() ? 0 : 1; - $level = $columnDimension->getOutlineLevel(); - $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs - } - - // Components of _colinfo: - // $firstcol first column on the range - // $lastcol last column on the range - // $width width to set - // $xfIndex The optional cell style Xf index to apply to the columns - // $hidden The optional hidden atribute - // $level The optional outline level - $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level); - } - - // Write GUTS - $this->_writeGuts(); - - // Write DEFAULTROWHEIGHT - $this->_writeDefaultRowHeight(); - - // Write WSBOOL - $this->_writeWsbool(); - - // Write horizontal and vertical page breaks - $this->_writeBreaks(); - - // Write page header - $this->_writeHeader(); - - // Write page footer - $this->_writeFooter(); - - // Write page horizontal centering - $this->_writeHcenter(); - - // Write page vertical centering - $this->_writeVcenter(); - - // Write left margin - $this->_writeMarginLeft(); - - // Write right margin - $this->_writeMarginRight(); - - // Write top margin - $this->_writeMarginTop(); - - // Write bottom margin - $this->_writeMarginBottom(); - - // Write page setup - $this->_writeSetup(); - - // Write sheet protection - $this->_writeProtect(); - - // Write SCENPROTECT - $this->_writeScenProtect(); - - // Write OBJECTPROTECT - $this->_writeObjectProtect(); - - // Write sheet password - $this->_writePassword(); - - // Write DEFCOLWIDTH record - $this->_writeDefcol(); - - // Write the COLINFO records if they exist - if (!empty($this->_colinfo)) { - $colcount = count($this->_colinfo); - for ($i = 0; $i < $colcount; ++$i) { - $this->_writeColinfo($this->_colinfo[$i]); - } - } - $autoFilterRange = $_phpSheet->getAutoFilter()->getRange(); - if (!empty($autoFilterRange)) { - // Write AUTOFILTERINFO - $this->_writeAutoFilterInfo(); - } - - // Write sheet dimensions - $this->_writeDimensions(); - - // Row dimensions - foreach ($_phpSheet->getRowDimensions() as $rowDimension) { - $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs - $this->_writeRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() ); - } - - // Write Cells - foreach ($_phpSheet->getCellCollection() as $cellID) { - $cell = $_phpSheet->getCell($cellID); - $row = $cell->getRow() - 1; - $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; - - // Don't break Excel! -// if ($row + 1 > 65536 or $column + 1 > 256) { - if ($row > 65535 || $column > 255) { - break; - } - - // Write cell value - $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs - - $cVal = $cell->getValue(); - if ($cVal instanceof PHPExcel_RichText) { - // $this->_writeString($row, $column, $cVal->getPlainText(), $xfIndex); - $arrcRun = array(); - $str_len = strlen($cVal->getPlainText()); - $str_pos = 0; - $elements = $cVal->getRichTextElements(); - foreach ($elements as $element) { - // FONT Index - if ($element instanceof PHPExcel_RichText_Run) { - $str_fontidx = $this->_fntHashIndex[$element->getFont()->getHashCode()]; - } - else { - $str_fontidx = 0; - } - $arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx); - // Position FROM - $str_pos += strlen($element->getText()); - } - $this->_writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun); - } else { - switch ($cell->getDatatype()) { - case PHPExcel_Cell_DataType::TYPE_STRING: - case PHPExcel_Cell_DataType::TYPE_NULL: - if ($cVal === '' || $cVal === null) { - $this->_writeBlank($row, $column, $xfIndex); - } else { - $this->_writeString($row, $column, $cVal, $xfIndex); - } - break; - - case PHPExcel_Cell_DataType::TYPE_NUMERIC: - $this->_writeNumber($row, $column, $cVal, $xfIndex); - break; - - case PHPExcel_Cell_DataType::TYPE_FORMULA: - $calculatedValue = $this->_preCalculateFormulas ? - $cell->getCalculatedValue() : null; - $this->_writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue); - break; - - case PHPExcel_Cell_DataType::TYPE_BOOL: - $this->_writeBoolErr($row, $column, $cVal, 0, $xfIndex); - break; - - case PHPExcel_Cell_DataType::TYPE_ERROR: - $this->_writeBoolErr($row, $column, self::_mapErrorCode($cVal), 1, $xfIndex); - break; - - } - } - } - - // Append - $this->_writeMsoDrawing(); - - $this->_writeWindow2(); - $this->_writeZoom(); - if ($_phpSheet->getFreezePane()) { - $this->_writePanes(); - } - $this->_writeSelection(); - $this->_writeMergedCells(); - - // Hyperlinks - foreach ($_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) { - list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinate); - - $url = $hyperlink->getUrl(); - - if ( strpos($url, 'sheet://') !== false ) { - // internal to current workbook - $url = str_replace('sheet://', 'internal:', $url); - - } else if ( preg_match('/^(http:|https:|ftp:|mailto:)/', $url) ) { - // URL - // $url = $url; - - } else { - // external (local file) - $url = 'external:' . $url; - } - - $this->_writeUrl($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $url); - } - - $this->_writeDataValidity(); - $this->_writeSheetLayout(); - $this->_writeSheetProtection(); - $this->_writeRangeProtection(); - - $this->_storeEof(); - } - - /** - * Write a cell range address in BIFF8 - * always fixed range - * See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format - * - * @param string $range E.g. 'A1' or 'A1:B6' - * @return string Binary data - */ - private function _writeBIFF8CellRangeAddressFixed($range = 'A1') - { - $explodes = explode(':', $range); - - // extract first cell, e.g. 'A1' - $firstCell = $explodes[0]; - - // extract last cell, e.g. 'B6' - if (count($explodes) == 1) { - $lastCell = $firstCell; - } else { - $lastCell = $explodes[1]; - } - - $firstCellCoordinates = PHPExcel_Cell::coordinateFromString($firstCell); // e.g. array(0, 1) - $lastCellCoordinates = PHPExcel_Cell::coordinateFromString($lastCell); // e.g. array(1, 6) - - return(pack('vvvv', - $firstCellCoordinates[1] - 1, - $lastCellCoordinates[1] - 1, - PHPExcel_Cell::columnIndexFromString($firstCellCoordinates[0]) - 1, - PHPExcel_Cell::columnIndexFromString($lastCellCoordinates[0]) - 1 - )); - } - - /** - * Retrieves data from memory in one chunk, or from disk in $buffer - * sized chunks. - * - * @return string The data - */ - function getData() - { - $buffer = 4096; - - // Return data stored in memory - if (isset($this->_data)) { - $tmp = $this->_data; - unset($this->_data); - return $tmp; - } - // No data to return - return false; - } - - /** - * Set the option to print the row and column headers on the printed page. - * - * @access public - * @param integer $print Whether to print the headers or not. Defaults to 1 (print). - */ - function printRowColHeaders($print = 1) - { - $this->_print_headers = $print; - } - - /** - * This method sets the properties for outlining and grouping. The defaults - * correspond to Excel's defaults. - * - * @param bool $visible - * @param bool $symbols_below - * @param bool $symbols_right - * @param bool $auto_style - */ - function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false) - { - $this->_outline_on = $visible; - $this->_outline_below = $symbols_below; - $this->_outline_right = $symbols_right; - $this->_outline_style = $auto_style; - - // Ensure this is a boolean vale for Window2 - if ($this->_outline_on) { - $this->_outline_on = 1; - } - } - - /** - * Write a double to the specified row and column (zero indexed). - * An integer can be written as a double. Excel will display an - * integer. $format is optional. - * - * Returns 0 : normal termination - * -2 : row or column out of range - * - * @param integer $row Zero indexed row - * @param integer $col Zero indexed column - * @param float $num The number to write - * @param mixed $xfIndex The optional XF format - * @return integer - */ - private function _writeNumber($row, $col, $num, $xfIndex) - { - $record = 0x0203; // Record identifier - $length = 0x000E; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("vvv", $row, $col, $xfIndex); - $xl_double = pack("d", $num); - if (self::getByteOrder()) { // if it's Big Endian - $xl_double = strrev($xl_double); - } - - $this->_append($header.$data.$xl_double); - return(0); - } - - /** - * Write a LABELSST record or a LABEL record. Which one depends on BIFF version - * - * @param int $row Row index (0-based) - * @param int $col Column index (0-based) - * @param string $str The string - * @param int $xfIndex Index to XF record - */ - private function _writeString($row, $col, $str, $xfIndex) - { - $this->_writeLabelSst($row, $col, $str, $xfIndex); - } - /** - * Write a LABELSST record or a LABEL record. Which one depends on BIFF version - * It differs from _writeString by the writing of rich text strings. - * @param int $row Row index (0-based) - * @param int $col Column index (0-based) - * @param string $str The string - * @param mixed $xfIndex The XF format index for the cell - * @param array $arrcRun Index to Font record and characters beginning - */ - private function _writeRichTextString($row, $col, $str, $xfIndex, $arrcRun){ - $record = 0x00FD; // Record identifier - $length = 0x000A; // Bytes to follow - - $str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($str, $arrcRun); - - /* check if string is already present */ - if (!isset($this->_str_table[$str])) { - $this->_str_table[$str] = $this->_str_unique++; - } - $this->_str_total++; - - $header = pack('vv', $record, $length); - $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]); - $this->_append($header.$data); - } - - /** - * Write a string to the specified row and column (zero indexed). - * NOTE: there is an Excel 5 defined limit of 255 characters. - * $format is optional. - * Returns 0 : normal termination - * -2 : row or column out of range - * -3 : long string truncated to 255 chars - * - * @access public - * @param integer $row Zero indexed row - * @param integer $col Zero indexed column - * @param string $str The string to write - * @param mixed $xfIndex The XF format index for the cell - * @return integer - */ - private function _writeLabel($row, $col, $str, $xfIndex) - { - $strlen = strlen($str); - $record = 0x0204; // Record identifier - $length = 0x0008 + $strlen; // Bytes to follow - - $str_error = 0; - - if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars - $str = substr($str, 0, $this->_xls_strmax); - $length = 0x0008 + $this->_xls_strmax; - $strlen = $this->_xls_strmax; - $str_error = -3; - } - - $header = pack("vv", $record, $length); - $data = pack("vvvv", $row, $col, $xfIndex, $strlen); - $this->_append($header . $data . $str); - return($str_error); - } - - /** - * Write a string to the specified row and column (zero indexed). - * This is the BIFF8 version (no 255 chars limit). - * $format is optional. - * Returns 0 : normal termination - * -2 : row or column out of range - * -3 : long string truncated to 255 chars - * - * @access public - * @param integer $row Zero indexed row - * @param integer $col Zero indexed column - * @param string $str The string to write - * @param mixed $xfIndex The XF format index for the cell - * @return integer - */ - private function _writeLabelSst($row, $col, $str, $xfIndex) - { - $record = 0x00FD; // Record identifier - $length = 0x000A; // Bytes to follow - - $str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($str); - - /* check if string is already present */ - if (!isset($this->_str_table[$str])) { - $this->_str_table[$str] = $this->_str_unique++; - } - $this->_str_total++; - - $header = pack('vv', $record, $length); - $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]); - $this->_append($header.$data); - } - - /** - * Writes a note associated with the cell given by the row and column. - * NOTE records don't have a length limit. - * - * @param integer $row Zero indexed row - * @param integer $col Zero indexed column - * @param string $note The note to write - */ - private function _writeNote($row, $col, $note) - { - $note_length = strlen($note); - $record = 0x001C; // Record identifier - $max_length = 2048; // Maximun length for a NOTE record - - // Length for this record is no more than 2048 + 6 - $length = 0x0006 + min($note_length, 2048); - $header = pack("vv", $record, $length); - $data = pack("vvv", $row, $col, $note_length); - $this->_append($header . $data . substr($note, 0, 2048)); - - for ($i = $max_length; $i < $note_length; $i += $max_length) { - $chunk = substr($note, $i, $max_length); - $length = 0x0006 + strlen($chunk); - $header = pack("vv", $record, $length); - $data = pack("vvv", -1, 0, strlen($chunk)); - $this->_append($header.$data.$chunk); - } - return(0); - } - - /** - * Write a blank cell to the specified row and column (zero indexed). - * A blank cell is used to specify formatting without adding a string - * or a number. - * - * A blank cell without a format serves no purpose. Therefore, we don't write - * a BLANK record unless a format is specified. - * - * Returns 0 : normal termination (including no format) - * -1 : insufficient number of arguments - * -2 : row or column out of range - * - * @param integer $row Zero indexed row - * @param integer $col Zero indexed column - * @param mixed $xfIndex The XF format index - */ - function _writeBlank($row, $col, $xfIndex) - { - $record = 0x0201; // Record identifier - $length = 0x0006; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("vvv", $row, $col, $xfIndex); - $this->_append($header . $data); - return 0; - } - - /** - * Write a boolean or an error type to the specified row and column (zero indexed) - * - * @param int $row Row index (0-based) - * @param int $col Column index (0-based) - * @param int $value - * @param boolean $isError Error or Boolean? - * @param int $xfIndex - */ - private function _writeBoolErr($row, $col, $value, $isError, $xfIndex) - { - $record = 0x0205; - $length = 8; - - $header = pack("vv", $record, $length); - $data = pack("vvvCC", $row, $col, $xfIndex, $value, $isError); - $this->_append($header . $data); - return 0; - } - - /** - * Write a formula to the specified row and column (zero indexed). - * The textual representation of the formula is passed to the parser in - * Parser.php which returns a packed binary string. - * - * Returns 0 : normal termination - * -1 : formula errors (bad formula) - * -2 : row or column out of range - * - * @param integer $row Zero indexed row - * @param integer $col Zero indexed column - * @param string $formula The formula text string - * @param mixed $xfIndex The XF format index - * @param mixed $calculatedValue Calculated value - * @return integer - */ - private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue) - { - $record = 0x0006; // Record identifier - - // Initialize possible additional value for STRING record that should be written after the FORMULA record? - $stringValue = null; - - // calculated value - if (isset($calculatedValue)) { - // Since we can't yet get the data type of the calculated value, - // we use best effort to determine data type - if (is_bool($calculatedValue)) { - // Boolean value - $num = pack('CCCvCv', 0x01, 0x00, (int)$calculatedValue, 0x00, 0x00, 0xFFFF); - } elseif (is_int($calculatedValue) || is_float($calculatedValue)) { - // Numeric value - $num = pack('d', $calculatedValue); - } elseif (is_string($calculatedValue)) { - if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) { - // Error value - $num = pack('CCCvCv', 0x02, 0x00, self::_mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF); - } elseif ($calculatedValue === '') { - // Empty string (and BIFF8) - $num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF); - } else { - // Non-empty string value (or empty string BIFF5) - $stringValue = $calculatedValue; - $num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF); - } - } else { - // We are really not supposed to reach here - $num = pack('d', 0x00); - } - } else { - $num = pack('d', 0x00); - } - - $grbit = 0x03; // Option flags - $unknown = 0x0000; // Must be zero - - // Strip the '=' or '@' sign at the beginning of the formula string - if ($formula{0} == '=') { - $formula = substr($formula,1); - } else { - // Error handling - $this->_writeString($row, $col, 'Unrecognised character for formula'); - return -1; - } - - // Parse the formula using the parser in Parser.php - try { - $error = $this->_parser->parse($formula); - $formula = $this->_parser->toReversePolish(); - - $formlen = strlen($formula); // Length of the binary string - $length = 0x16 + $formlen; // Length of the record data - - $header = pack("vv", $record, $length); - - $data = pack("vvv", $row, $col, $xfIndex) - . $num - . pack("vVv", $grbit, $unknown, $formlen); - $this->_append($header . $data . $formula); - - // Append also a STRING record if necessary - if ($stringValue !== null) { - $this->_writeStringRecord($stringValue); - } - - return 0; - - } catch (Exception $e) { - // do nothing - } - - } - - /** - * Write a STRING record. This - * - * @param string $stringValue - */ - private function _writeStringRecord($stringValue) - { - $record = 0x0207; // Record identifier - $data = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($stringValue); - - $length = strlen($data); - $header = pack('vv', $record, $length); - - $this->_append($header . $data); - } - - /** - * Write a hyperlink. - * This is comprised of two elements: the visible label and - * the invisible link. The visible label is the same as the link unless an - * alternative string is specified. The label is written using the - * _writeString() method. Therefore the 255 characters string limit applies. - * $string and $format are optional. - * - * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external - * directory url. - * - * Returns 0 : normal termination - * -2 : row or column out of range - * -3 : long string truncated to 255 chars - * - * @param integer $row Row - * @param integer $col Column - * @param string $url URL string - * @return integer - */ - private function _writeUrl($row, $col, $url) - { - // Add start row and col to arg list - return($this->_writeUrlRange($row, $col, $row, $col, $url)); - } - - /** - * This is the more general form of _writeUrl(). It allows a hyperlink to be - * written to a range of cells. This function also decides the type of hyperlink - * to be written. These are either, Web (http, ftp, mailto), Internal - * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1'). - * - * @access private - * @see _writeUrl() - * @param integer $row1 Start row - * @param integer $col1 Start column - * @param integer $row2 End row - * @param integer $col2 End column - * @param string $url URL string - * @return integer - */ - function _writeUrlRange($row1, $col1, $row2, $col2, $url) - { - // Check for internal/external sheet links or default to web link - if (preg_match('[^internal:]', $url)) { - return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url)); - } - if (preg_match('[^external:]', $url)) { - return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url)); - } - return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url)); - } - - /** - * Used to write http, ftp and mailto hyperlinks. - * The link type ($options) is 0x03 is the same as absolute dir ref without - * sheet. However it is differentiated by the $unknown2 data stream. - * - * @access private - * @see _writeUrl() - * @param integer $row1 Start row - * @param integer $col1 Start column - * @param integer $row2 End row - * @param integer $col2 End column - * @param string $url URL string - * @return integer - */ - function _writeUrlWeb($row1, $col1, $row2, $col2, $url) - { - $record = 0x01B8; // Record identifier - $length = 0x00000; // Bytes to follow - - // Pack the undocumented parts of the hyperlink stream - $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); - $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B"); - - // Pack the option flags - $options = pack("V", 0x03); - - // Convert URL to a null terminated wchar string - $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); - $url = $url . "\0\0\0"; - - // Pack the length of the URL - $url_len = pack("V", strlen($url)); - - // Calculate the data length - $length = 0x34 + strlen($url); - - // Pack the header data - $header = pack("vv", $record, $length); - $data = pack("vvvv", $row1, $row2, $col1, $col2); - - // Write the packed data - $this->_append($header . $data . - $unknown1 . $options . - $unknown2 . $url_len . $url); - return 0; - } - - /** - * Used to write internal reference hyperlinks such as "Sheet1!A1". - * - * @access private - * @see _writeUrl() - * @param integer $row1 Start row - * @param integer $col1 Start column - * @param integer $row2 End row - * @param integer $col2 End column - * @param string $url URL string - * @return integer - */ - function _writeUrlInternal($row1, $col1, $row2, $col2, $url) - { - $record = 0x01B8; // Record identifier - $length = 0x00000; // Bytes to follow - - // Strip URL type - $url = preg_replace('/^internal:/', '', $url); - - // Pack the undocumented parts of the hyperlink stream - $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); - - // Pack the option flags - $options = pack("V", 0x08); - - // Convert the URL type and to a null terminated wchar string - $url .= "\0"; - - // character count - $url_len = PHPExcel_Shared_String::CountCharacters($url); - $url_len = pack('V', $url_len); - - $url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8'); - - // Calculate the data length - $length = 0x24 + strlen($url); - - // Pack the header data - $header = pack("vv", $record, $length); - $data = pack("vvvv", $row1, $row2, $col1, $col2); - - // Write the packed data - $this->_append($header . $data . - $unknown1 . $options . - $url_len . $url); - return 0; - } - - /** - * Write links to external directory names such as 'c:\foo.xls', - * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'. - * - * Note: Excel writes some relative links with the $dir_long string. We ignore - * these cases for the sake of simpler code. - * - * @access private - * @see _writeUrl() - * @param integer $row1 Start row - * @param integer $col1 Start column - * @param integer $row2 End row - * @param integer $col2 End column - * @param string $url URL string - * @return integer - */ - function _writeUrlExternal($row1, $col1, $row2, $col2, $url) - { - // Network drives are different. We will handle them separately - // MS/Novell network drives and shares start with \\ - if (preg_match('[^external:\\\\]', $url)) { - return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format)); - } - - $record = 0x01B8; // Record identifier - $length = 0x00000; // Bytes to follow - - // Strip URL type and change Unix dir separator to Dos style (if needed) - // - $url = preg_replace('/^external:/', '', $url); - $url = preg_replace('/\//', "\\", $url); - - // Determine if the link is relative or absolute: - // relative if link contains no dir separator, "somefile.xls" - // relative if link starts with up-dir, "..\..\somefile.xls" - // otherwise, absolute - - $absolute = 0x00; // relative path - if ( preg_match('/^[A-Z]:/', $url) ) { - $absolute = 0x02; // absolute path on Windows, e.g. C:\... - } - $link_type = 0x01 | $absolute; - - // Determine if the link contains a sheet reference and change some of the - // parameters accordingly. - // Split the dir name and sheet name (if it exists) - $dir_long = $url; - if (preg_match("/\#/", $url)) { - $link_type |= 0x08; - } - - - // Pack the link type - $link_type = pack("V", $link_type); - - // Calculate the up-level dir count e.g.. (..\..\..\ == 3) - $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless); - $up_count = pack("v", $up_count); - - // Store the short dos dir name (null terminated) - $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0"; - - // Store the long dir name as a wchar string (non-null terminated) - $dir_long = $dir_long . "\0"; - - // Pack the lengths of the dir strings - $dir_short_len = pack("V", strlen($dir_short) ); - $dir_long_len = pack("V", strlen($dir_long) ); - $stream_len = pack("V", 0);//strlen($dir_long) + 0x06); - - // Pack the undocumented parts of the hyperlink stream - $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' ); - $unknown2 = pack("H*",'0303000000000000C000000000000046' ); - $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000'); - $unknown4 = pack("v", 0x03 ); - - // Pack the main data stream - $data = pack("vvvv", $row1, $row2, $col1, $col2) . - $unknown1 . - $link_type . - $unknown2 . - $up_count . - $dir_short_len. - $dir_short . - $unknown3 . - $stream_len ;/*. - $dir_long_len . - $unknown4 . - $dir_long . - $sheet_len . - $sheet ;*/ - - // Pack the header data - $length = strlen($data); - $header = pack("vv", $record, $length); - - // Write the packed data - $this->_append($header. $data); - return 0; - } - - /** - * This method is used to set the height and format for a row. - * - * @param integer $row The row to set - * @param integer $height Height we are giving to the row. - * Use null to set XF without setting height - * @param integer $xfIndex The optional cell style Xf index to apply to the columns - * @param bool $hidden The optional hidden attribute - * @param integer $level The optional outline level for row, in range [0,7] - */ - private function _writeRow($row, $height, $xfIndex, $hidden = false, $level = 0) - { - $record = 0x0208; // Record identifier - $length = 0x0010; // Number of bytes to follow - - $colMic = 0x0000; // First defined column - $colMac = 0x0000; // Last defined column - $irwMac = 0x0000; // Used by Excel to optimise loading - $reserved = 0x0000; // Reserved - $grbit = 0x0000; // Option flags - $ixfe = $xfIndex; - - if ( $height < 0 ){ - $height = null; - } - - // Use _writeRow($row, null, $XF) to set XF format without setting height - if ($height != null) { - $miyRw = $height * 20; // row height - } else { - $miyRw = 0xff; // default row height is 256 - } - - // Set the options flags. fUnsynced is used to show that the font and row - // heights are not compatible. This is usually the case for WriteExcel. - // The collapsed flag 0x10 doesn't seem to be used to indicate that a row - // is collapsed. Instead it is used to indicate that the previous row is - // collapsed. The zero height flag, 0x20, is used to collapse a row. - - $grbit |= $level; - if ($hidden) { - $grbit |= 0x0020; - } - if ($height !== null) { - $grbit |= 0x0040; // fUnsynced - } - if ($xfIndex !== 0xF) { - $grbit |= 0x0080; - } - $grbit |= 0x0100; - - $header = pack("vv", $record, $length); - $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw, - $irwMac,$reserved, $grbit, $ixfe); - $this->_append($header.$data); - } - - /** - * Writes Excel DIMENSIONS to define the area in which there is data. - */ - private function _writeDimensions() - { - $record = 0x0200; // Record identifier - - $length = 0x000E; - $data = pack('VVvvv' - , $this->_firstRowIndex - , $this->_lastRowIndex + 1 - , $this->_firstColumnIndex - , $this->_lastColumnIndex + 1 - , 0x0000 // reserved - ); - - $header = pack("vv", $record, $length); - $this->_append($header.$data); - } - - /** - * Write BIFF record Window2. - */ - private function _writeWindow2() - { - $record = 0x023E; // Record identifier - $length = 0x0012; - - $grbit = 0x00B6; // Option flags - $rwTop = 0x0000; // Top row visible in window - $colLeft = 0x0000; // Leftmost column visible in window - - - // The options flags that comprise $grbit - $fDspFmla = 0; // 0 - bit - $fDspGrid = $this->_phpSheet->getShowGridlines() ? 1 : 0; // 1 - $fDspRwCol = $this->_phpSheet->getShowRowColHeaders() ? 1 : 0; // 2 - $fFrozen = $this->_phpSheet->getFreezePane() ? 1 : 0; // 3 - $fDspZeros = 1; // 4 - $fDefaultHdr = 1; // 5 - $fArabic = $this->_phpSheet->getRightToLeft() ? 1 : 0; // 6 - $fDspGuts = $this->_outline_on; // 7 - $fFrozenNoSplit = 0; // 0 - bit - // no support in PHPExcel for selected sheet, therefore sheet is only selected if it is the active sheet - $fSelected = ($this->_phpSheet === $this->_phpSheet->getParent()->getActiveSheet()) ? 1 : 0; - $fPaged = 1; // 2 - - $grbit = $fDspFmla; - $grbit |= $fDspGrid << 1; - $grbit |= $fDspRwCol << 2; - $grbit |= $fFrozen << 3; - $grbit |= $fDspZeros << 4; - $grbit |= $fDefaultHdr << 5; - $grbit |= $fArabic << 6; - $grbit |= $fDspGuts << 7; - $grbit |= $fFrozenNoSplit << 8; - $grbit |= $fSelected << 9; - $grbit |= $fPaged << 10; - - $header = pack("vv", $record, $length); - $data = pack("vvv", $grbit, $rwTop, $colLeft); - - // FIXME !!! - $rgbHdr = 0x0040; // Row/column heading and gridline color index - $zoom_factor_page_break = 0x0000; - $zoom_factor_normal = 0x0000; - $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000); - - $this->_append($header.$data); - } - - /** - * Write BIFF record DEFAULTROWHEIGHT. - */ - private function _writeDefaultRowHeight() - { - $defaultRowHeight = $this->_phpSheet->getDefaultRowDimension()->getRowHeight(); - - if ($defaultRowHeight < 0) { - return; - } - - // convert to twips - $defaultRowHeight = (int) 20 * $defaultRowHeight; - - $record = 0x0225; // Record identifier - $length = 0x0004; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("vv", 1, $defaultRowHeight); - $this->_append($header . $data); - } - - /** - * Write BIFF record DEFCOLWIDTH if COLINFO records are in use. - */ - private function _writeDefcol() - { - $defaultColWidth = 8; - - $record = 0x0055; // Record identifier - $length = 0x0002; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("v", $defaultColWidth); - $this->_append($header . $data); - } - - /** - * Write BIFF record COLINFO to define column widths - * - * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C - * length record. - * - * @param array $col_array This is the only parameter received and is composed of the following: - * 0 => First formatted column, - * 1 => Last formatted column, - * 2 => Col width (8.43 is Excel default), - * 3 => The optional XF format of the column, - * 4 => Option flags. - * 5 => Optional outline level - */ - private function _writeColinfo($col_array) - { - if (isset($col_array[0])) { - $colFirst = $col_array[0]; - } - if (isset($col_array[1])) { - $colLast = $col_array[1]; - } - if (isset($col_array[2])) { - $coldx = $col_array[2]; - } else { - $coldx = 8.43; - } - if (isset($col_array[3])) { - $xfIndex = $col_array[3]; - } else { - $xfIndex = 15; - } - if (isset($col_array[4])) { - $grbit = $col_array[4]; - } else { - $grbit = 0; - } - if (isset($col_array[5])) { - $level = $col_array[5]; - } else { - $level = 0; - } - $record = 0x007D; // Record identifier - $length = 0x000C; // Number of bytes to follow - - $coldx *= 256; // Convert to units of 1/256 of a char - - $ixfe = $xfIndex; - $reserved = 0x0000; // Reserved - - $level = max(0, min($level, 7)); - $grbit |= $level << 8; - - $header = pack("vv", $record, $length); - $data = pack("vvvvvv", $colFirst, $colLast, $coldx, - $ixfe, $grbit, $reserved); - $this->_append($header.$data); - } - - /** - * Write BIFF record SELECTION. - */ - private function _writeSelection() - { - // look up the selected cell range - $selectedCells = $this->_phpSheet->getSelectedCells(); - $selectedCells = PHPExcel_Cell::splitRange($this->_phpSheet->getSelectedCells()); - $selectedCells = $selectedCells[0]; - if (count($selectedCells) == 2) { - list($first, $last) = $selectedCells; - } else { - $first = $selectedCells[0]; - $last = $selectedCells[0]; - } - - list($colFirst, $rwFirst) = PHPExcel_Cell::coordinateFromString($first); - $colFirst = PHPExcel_Cell::columnIndexFromString($colFirst) - 1; // base 0 column index - --$rwFirst; // base 0 row index - - list($colLast, $rwLast) = PHPExcel_Cell::coordinateFromString($last); - $colLast = PHPExcel_Cell::columnIndexFromString($colLast) - 1; // base 0 column index - --$rwLast; // base 0 row index - - // make sure we are not out of bounds - $colFirst = min($colFirst, 255); - $colLast = min($colLast, 255); - - $rwFirst = min($rwFirst, 65535); - $rwLast = min($rwLast, 65535); - - $record = 0x001D; // Record identifier - $length = 0x000F; // Number of bytes to follow - - $pnn = $this->_active_pane; // Pane position - $rwAct = $rwFirst; // Active row - $colAct = $colFirst; // Active column - $irefAct = 0; // Active cell ref - $cref = 1; // Number of refs - - if (!isset($rwLast)) { - $rwLast = $rwFirst; // Last row in reference - } - if (!isset($colLast)) { - $colLast = $colFirst; // Last col in reference - } - - // Swap last row/col for first row/col as necessary - if ($rwFirst > $rwLast) { - list($rwFirst, $rwLast) = array($rwLast, $rwFirst); - } - - if ($colFirst > $colLast) { - list($colFirst, $colLast) = array($colLast, $colFirst); - } - - $header = pack("vv", $record, $length); - $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct, - $irefAct, $cref, - $rwFirst, $rwLast, - $colFirst, $colLast); - $this->_append($header . $data); - } - - /** - * Store the MERGEDCELLS records for all ranges of merged cells - */ - private function _writeMergedCells() - { - $mergeCells = $this->_phpSheet->getMergeCells(); - $countMergeCells = count($mergeCells); - - if ($countMergeCells == 0) { - return; - } - - // maximum allowed number of merged cells per record - $maxCountMergeCellsPerRecord = 1027; - - // record identifier - $record = 0x00E5; - - // counter for total number of merged cells treated so far by the writer - $i = 0; - - // counter for number of merged cells written in record currently being written - $j = 0; - - // initialize record data - $recordData = ''; - - // loop through the merged cells - foreach ($mergeCells as $mergeCell) { - ++$i; - ++$j; - - // extract the row and column indexes - $range = PHPExcel_Cell::splitRange($mergeCell); - list($first, $last) = $range[0]; - list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first); - list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last); - - $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1); - - // flush record if we have reached limit for number of merged cells, or reached final merged cell - if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) { - $recordData = pack('v', $j) . $recordData; - $length = strlen($recordData); - $header = pack('vv', $record, $length); - $this->_append($header . $recordData); - - // initialize for next record, if any - $recordData = ''; - $j = 0; - } - } - } - - /** - * Write SHEETLAYOUT record - */ - private function _writeSheetLayout() - { - if (!$this->_phpSheet->isTabColorSet()) { - return; - } - - $recordData = pack( - 'vvVVVvv' - , 0x0862 - , 0x0000 // unused - , 0x00000000 // unused - , 0x00000000 // unused - , 0x00000014 // size of record data - , $this->_colors[$this->_phpSheet->getTabColor()->getRGB()] // color index - , 0x0000 // unused - ); - - $length = strlen($recordData); - - $record = 0x0862; // Record identifier - $header = pack('vv', $record, $length); - $this->_append($header . $recordData); - } - - /** - * Write SHEETPROTECTION - */ - private function _writeSheetProtection() - { - // record identifier - $record = 0x0867; - - // prepare options - $options = (int) !$this->_phpSheet->getProtection()->getObjects() - | (int) !$this->_phpSheet->getProtection()->getScenarios() << 1 - | (int) !$this->_phpSheet->getProtection()->getFormatCells() << 2 - | (int) !$this->_phpSheet->getProtection()->getFormatColumns() << 3 - | (int) !$this->_phpSheet->getProtection()->getFormatRows() << 4 - | (int) !$this->_phpSheet->getProtection()->getInsertColumns() << 5 - | (int) !$this->_phpSheet->getProtection()->getInsertRows() << 6 - | (int) !$this->_phpSheet->getProtection()->getInsertHyperlinks() << 7 - | (int) !$this->_phpSheet->getProtection()->getDeleteColumns() << 8 - | (int) !$this->_phpSheet->getProtection()->getDeleteRows() << 9 - | (int) !$this->_phpSheet->getProtection()->getSelectLockedCells() << 10 - | (int) !$this->_phpSheet->getProtection()->getSort() << 11 - | (int) !$this->_phpSheet->getProtection()->getAutoFilter() << 12 - | (int) !$this->_phpSheet->getProtection()->getPivotTables() << 13 - | (int) !$this->_phpSheet->getProtection()->getSelectUnlockedCells() << 14 ; - - // record data - $recordData = pack( - 'vVVCVVvv' - , 0x0867 // repeated record identifier - , 0x0000 // not used - , 0x0000 // not used - , 0x00 // not used - , 0x01000200 // unknown data - , 0xFFFFFFFF // unknown data - , $options // options - , 0x0000 // not used - ); - - $length = strlen($recordData); - $header = pack('vv', $record, $length); - - $this->_append($header . $recordData); - } - - /** - * Write BIFF record RANGEPROTECTION - * - * Openoffice.org's Documentaion of the Microsoft Excel File Format uses term RANGEPROTECTION for these records - * Microsoft Office Excel 97-2007 Binary File Format Specification uses term FEAT for these records - */ - private function _writeRangeProtection() - { - foreach ($this->_phpSheet->getProtectedCells() as $range => $password) { - // number of ranges, e.g. 'A1:B3 C20:D25' - $cellRanges = explode(' ', $range); - $cref = count($cellRanges); - - $recordData = pack( - 'vvVVvCVvVv', - 0x0868, - 0x00, - 0x0000, - 0x0000, - 0x02, - 0x0, - 0x0000, - $cref, - 0x0000, - 0x00 - ); - - foreach ($cellRanges as $cellRange) { - $recordData .= $this->_writeBIFF8CellRangeAddressFixed($cellRange); - } - - // the rgbFeat structure - $recordData .= pack( - 'VV', - 0x0000, - hexdec($password) - ); - - $recordData .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong('p' . md5($recordData)); - - $length = strlen($recordData); - - $record = 0x0868; // Record identifier - $header = pack("vv", $record, $length); - $this->_append($header . $recordData); - } - } - - /** - * Write BIFF record EXTERNCOUNT to indicate the number of external sheet - * references in a worksheet. - * - * Excel only stores references to external sheets that are used in formulas. - * For simplicity we store references to all the sheets in the workbook - * regardless of whether they are used or not. This reduces the overall - * complexity and eliminates the need for a two way dialogue between the formula - * parser the worksheet objects. - * - * @param integer $count The number of external sheet references in this worksheet - */ - private function _writeExterncount($count) - { - $record = 0x0016; // Record identifier - $length = 0x0002; // Number of bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("v", $count); - $this->_append($header . $data); - } - - /** - * Writes the Excel BIFF EXTERNSHEET record. These references are used by - * formulas. A formula references a sheet name via an index. Since we store a - * reference to all of the external worksheets the EXTERNSHEET index is the same - * as the worksheet index. - * - * @param string $sheetname The name of a external worksheet - */ - private function _writeExternsheet($sheetname) - { - $record = 0x0017; // Record identifier - - // References to the current sheet are encoded differently to references to - // external sheets. - // - if ($this->_phpSheet->getTitle() == $sheetname) { - $sheetname = ''; - $length = 0x02; // The following 2 bytes - $cch = 1; // The following byte - $rgch = 0x02; // Self reference - } else { - $length = 0x02 + strlen($sheetname); - $cch = strlen($sheetname); - $rgch = 0x03; // Reference to a sheet in the current workbook - } - - $header = pack("vv", $record, $length); - $data = pack("CC", $cch, $rgch); - $this->_append($header . $data . $sheetname); - } - - /** - * Writes the Excel BIFF PANE record. - * The panes can either be frozen or thawed (unfrozen). - * Frozen panes are specified in terms of an integer number of rows and columns. - * Thawed panes are specified in terms of Excel's units for rows and columns. - */ - private function _writePanes() - { - $panes = array(); - if ($freezePane = $this->_phpSheet->getFreezePane()) { - list($column, $row) = PHPExcel_Cell::coordinateFromString($freezePane); - $panes[0] = $row - 1; - $panes[1] = PHPExcel_Cell::columnIndexFromString($column) - 1; - } else { - // thaw panes - return; - } - - $y = isset($panes[0]) ? $panes[0] : null; - $x = isset($panes[1]) ? $panes[1] : null; - $rwTop = isset($panes[2]) ? $panes[2] : null; - $colLeft = isset($panes[3]) ? $panes[3] : null; - if (count($panes) > 4) { // if Active pane was received - $pnnAct = $panes[4]; - } else { - $pnnAct = null; - } - $record = 0x0041; // Record identifier - $length = 0x000A; // Number of bytes to follow - - // Code specific to frozen or thawed panes. - if ($this->_phpSheet->getFreezePane()) { - // Set default values for $rwTop and $colLeft - if (!isset($rwTop)) { - $rwTop = $y; - } - if (!isset($colLeft)) { - $colLeft = $x; - } - } else { - // Set default values for $rwTop and $colLeft - if (!isset($rwTop)) { - $rwTop = 0; - } - if (!isset($colLeft)) { - $colLeft = 0; - } - - // Convert Excel's row and column units to the internal units. - // The default row height is 12.75 - // The default column width is 8.43 - // The following slope and intersection values were interpolated. - // - $y = 20*$y + 255; - $x = 113.879*$x + 390; - } - - - // Determine which pane should be active. There is also the undocumented - // option to override this should it be necessary: may be removed later. - // - if (!isset($pnnAct)) { - if ($x != 0 && $y != 0) { - $pnnAct = 0; // Bottom right - } - if ($x != 0 && $y == 0) { - $pnnAct = 1; // Top right - } - if ($x == 0 && $y != 0) { - $pnnAct = 2; // Bottom left - } - if ($x == 0 && $y == 0) { - $pnnAct = 3; // Top left - } - } - - $this->_active_pane = $pnnAct; // Used in _writeSelection - - $header = pack("vv", $record, $length); - $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct); - $this->_append($header . $data); - } - - /** - * Store the page setup SETUP BIFF record. - */ - private function _writeSetup() - { - $record = 0x00A1; // Record identifier - $length = 0x0022; // Number of bytes to follow - - $iPaperSize = $this->_phpSheet->getPageSetup()->getPaperSize(); // Paper size - - $iScale = $this->_phpSheet->getPageSetup()->getScale() ? - $this->_phpSheet->getPageSetup()->getScale() : 100; // Print scaling factor - - $iPageStart = 0x01; // Starting page number - $iFitWidth = (int) $this->_phpSheet->getPageSetup()->getFitToWidth(); // Fit to number of pages wide - $iFitHeight = (int) $this->_phpSheet->getPageSetup()->getFitToHeight(); // Fit to number of pages high - $grbit = 0x00; // Option flags - $iRes = 0x0258; // Print resolution - $iVRes = 0x0258; // Vertical print resolution - - $numHdr = $this->_phpSheet->getPageMargins()->getHeader(); // Header Margin - - $numFtr = $this->_phpSheet->getPageMargins()->getFooter(); // Footer Margin - $iCopies = 0x01; // Number of copies - - $fLeftToRight = 0x0; // Print over then down - - // Page orientation - $fLandscape = ($this->_phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) ? - 0x0 : 0x1; - - $fNoPls = 0x0; // Setup not read from printer - $fNoColor = 0x0; // Print black and white - $fDraft = 0x0; // Print draft quality - $fNotes = 0x0; // Print notes - $fNoOrient = 0x0; // Orientation not set - $fUsePage = 0x0; // Use custom starting page - - $grbit = $fLeftToRight; - $grbit |= $fLandscape << 1; - $grbit |= $fNoPls << 2; - $grbit |= $fNoColor << 3; - $grbit |= $fDraft << 4; - $grbit |= $fNotes << 5; - $grbit |= $fNoOrient << 6; - $grbit |= $fUsePage << 7; - - $numHdr = pack("d", $numHdr); - $numFtr = pack("d", $numFtr); - if (self::getByteOrder()) { // if it's Big Endian - $numHdr = strrev($numHdr); - $numFtr = strrev($numFtr); - } - - $header = pack("vv", $record, $length); - $data1 = pack("vvvvvvvv", $iPaperSize, - $iScale, - $iPageStart, - $iFitWidth, - $iFitHeight, - $grbit, - $iRes, - $iVRes); - $data2 = $numHdr.$numFtr; - $data3 = pack("v", $iCopies); - $this->_append($header . $data1 . $data2 . $data3); - } - - /** - * Store the header caption BIFF record. - */ - private function _writeHeader() - { - $record = 0x0014; // Record identifier - - /* removing for now - // need to fix character count (multibyte!) - if (strlen($this->_phpSheet->getHeaderFooter()->getOddHeader()) <= 255) { - $str = $this->_phpSheet->getHeaderFooter()->getOddHeader(); // header string - } else { - $str = ''; - } - */ - - $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddHeader()); - $length = strlen($recordData); - - $header = pack("vv", $record, $length); - - $this->_append($header . $recordData); - } - - /** - * Store the footer caption BIFF record. - */ - private function _writeFooter() - { - $record = 0x0015; // Record identifier - - /* removing for now - // need to fix character count (multibyte!) - if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) { - $str = $this->_phpSheet->getHeaderFooter()->getOddFooter(); - } else { - $str = ''; - } - */ - - $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter()); - $length = strlen($recordData); - - $header = pack("vv", $record, $length); - - $this->_append($header . $recordData); - } - - /** - * Store the horizontal centering HCENTER BIFF record. - * - * @access private - */ - private function _writeHcenter() - { - $record = 0x0083; // Record identifier - $length = 0x0002; // Bytes to follow - - $fHCenter = $this->_phpSheet->getPageSetup()->getHorizontalCentered() ? 1 : 0; // Horizontal centering - - $header = pack("vv", $record, $length); - $data = pack("v", $fHCenter); - - $this->_append($header.$data); - } - - /** - * Store the vertical centering VCENTER BIFF record. - */ - private function _writeVcenter() - { - $record = 0x0084; // Record identifier - $length = 0x0002; // Bytes to follow - - $fVCenter = $this->_phpSheet->getPageSetup()->getVerticalCentered() ? 1 : 0; // Horizontal centering - - $header = pack("vv", $record, $length); - $data = pack("v", $fVCenter); - $this->_append($header . $data); - } - - /** - * Store the LEFTMARGIN BIFF record. - */ - private function _writeMarginLeft() - { - $record = 0x0026; // Record identifier - $length = 0x0008; // Bytes to follow - - $margin = $this->_phpSheet->getPageMargins()->getLeft(); // Margin in inches - - $header = pack("vv", $record, $length); - $data = pack("d", $margin); - if (self::getByteOrder()) { // if it's Big Endian - $data = strrev($data); - } - - $this->_append($header . $data); - } - - /** - * Store the RIGHTMARGIN BIFF record. - */ - private function _writeMarginRight() - { - $record = 0x0027; // Record identifier - $length = 0x0008; // Bytes to follow - - $margin = $this->_phpSheet->getPageMargins()->getRight(); // Margin in inches - - $header = pack("vv", $record, $length); - $data = pack("d", $margin); - if (self::getByteOrder()) { // if it's Big Endian - $data = strrev($data); - } - - $this->_append($header . $data); - } - - /** - * Store the TOPMARGIN BIFF record. - */ - private function _writeMarginTop() - { - $record = 0x0028; // Record identifier - $length = 0x0008; // Bytes to follow - - $margin = $this->_phpSheet->getPageMargins()->getTop(); // Margin in inches - - $header = pack("vv", $record, $length); - $data = pack("d", $margin); - if (self::getByteOrder()) { // if it's Big Endian - $data = strrev($data); - } - - $this->_append($header . $data); - } - - /** - * Store the BOTTOMMARGIN BIFF record. - */ - private function _writeMarginBottom() - { - $record = 0x0029; // Record identifier - $length = 0x0008; // Bytes to follow - - $margin = $this->_phpSheet->getPageMargins()->getBottom(); // Margin in inches - - $header = pack("vv", $record, $length); - $data = pack("d", $margin); - if (self::getByteOrder()) { // if it's Big Endian - $data = strrev($data); - } - - $this->_append($header . $data); - } - - /** - * Write the PRINTHEADERS BIFF record. - */ - private function _writePrintHeaders() - { - $record = 0x002a; // Record identifier - $length = 0x0002; // Bytes to follow - - $fPrintRwCol = $this->_print_headers; // Boolean flag - - $header = pack("vv", $record, $length); - $data = pack("v", $fPrintRwCol); - $this->_append($header . $data); - } - - /** - * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the - * GRIDSET record. - */ - private function _writePrintGridlines() - { - $record = 0x002b; // Record identifier - $length = 0x0002; // Bytes to follow - - $fPrintGrid = $this->_phpSheet->getPrintGridlines() ? 1 : 0; // Boolean flag - - $header = pack("vv", $record, $length); - $data = pack("v", $fPrintGrid); - $this->_append($header . $data); - } - - /** - * Write the GRIDSET BIFF record. Must be used in conjunction with the - * PRINTGRIDLINES record. - */ - private function _writeGridset() - { - $record = 0x0082; // Record identifier - $length = 0x0002; // Bytes to follow - - $fGridSet = !$this->_phpSheet->getPrintGridlines(); // Boolean flag - - $header = pack("vv", $record, $length); - $data = pack("v", $fGridSet); - $this->_append($header . $data); - } - - /** - * Write the AUTOFILTERINFO BIFF record. This is used to configure the number of autofilter select used in the sheet. - */ - private function _writeAutoFilterInfo(){ - $record = 0x009D; // Record identifier - $length = 0x0002; // Bytes to follow - - $rangeBounds = PHPExcel_Cell::rangeBoundaries($this->_phpSheet->getAutoFilter()->getRange()); - $iNumFilters = 1 + $rangeBounds[1][0] - $rangeBounds[0][0]; - - $header = pack("vv", $record, $length); - $data = pack("v", $iNumFilters); - $this->_append($header . $data); - } - - /** - * Write the GUTS BIFF record. This is used to configure the gutter margins - * where Excel outline symbols are displayed. The visibility of the gutters is - * controlled by a flag in WSBOOL. - * - * @see _writeWsbool() - */ - private function _writeGuts() - { - $record = 0x0080; // Record identifier - $length = 0x0008; // Bytes to follow - - $dxRwGut = 0x0000; // Size of row gutter - $dxColGut = 0x0000; // Size of col gutter - - // determine maximum row outline level - $maxRowOutlineLevel = 0; - foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) { - $maxRowOutlineLevel = max($maxRowOutlineLevel, $rowDimension->getOutlineLevel()); - } - - $col_level = 0; - - // Calculate the maximum column outline level. The equivalent calculation - // for the row outline level is carried out in _writeRow(). - $colcount = count($this->_colinfo); - for ($i = 0; $i < $colcount; ++$i) { - $col_level = max($this->_colinfo[$i][5], $col_level); - } - - // Set the limits for the outline levels (0 <= x <= 7). - $col_level = max(0, min($col_level, 7)); - - // The displayed level is one greater than the max outline levels - if ($maxRowOutlineLevel) { - ++$maxRowOutlineLevel; - } - if ($col_level) { - ++$col_level; - } - - $header = pack("vv", $record, $length); - $data = pack("vvvv", $dxRwGut, $dxColGut, $maxRowOutlineLevel, $col_level); - - $this->_append($header.$data); - } - - /** - * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction - * with the SETUP record. - */ - private function _writeWsbool() - { - $record = 0x0081; // Record identifier - $length = 0x0002; // Bytes to follow - $grbit = 0x0000; - - // The only option that is of interest is the flag for fit to page. So we - // set all the options in one go. - // - // Set the option flags - $grbit |= 0x0001; // Auto page breaks visible - if ($this->_outline_style) { - $grbit |= 0x0020; // Auto outline styles - } - if ($this->_phpSheet->getShowSummaryBelow()) { - $grbit |= 0x0040; // Outline summary below - } - if ($this->_phpSheet->getShowSummaryRight()) { - $grbit |= 0x0080; // Outline summary right - } - if ($this->_phpSheet->getPageSetup()->getFitToPage()) { - $grbit |= 0x0100; // Page setup fit to page - } - if ($this->_outline_on) { - $grbit |= 0x0400; // Outline symbols displayed - } - - $header = pack("vv", $record, $length); - $data = pack("v", $grbit); - $this->_append($header . $data); - } - - /** - * Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records. - */ - private function _writeBreaks() - { - // initialize - $vbreaks = array(); - $hbreaks = array(); - - foreach ($this->_phpSheet->getBreaks() as $cell => $breakType) { - // Fetch coordinates - $coordinates = PHPExcel_Cell::coordinateFromString($cell); - - // Decide what to do by the type of break - switch ($breakType) { - case PHPExcel_Worksheet::BREAK_COLUMN: - // Add to list of vertical breaks - $vbreaks[] = PHPExcel_Cell::columnIndexFromString($coordinates[0]) - 1; - break; - - case PHPExcel_Worksheet::BREAK_ROW: - // Add to list of horizontal breaks - $hbreaks[] = $coordinates[1]; - break; - - case PHPExcel_Worksheet::BREAK_NONE: - default: - // Nothing to do - break; - } - } - - //horizontal page breaks - if (!empty($hbreaks)) { - - // Sort and filter array of page breaks - sort($hbreaks, SORT_NUMERIC); - if ($hbreaks[0] == 0) { // don't use first break if it's 0 - array_shift($hbreaks); - } - - $record = 0x001b; // Record identifier - $cbrk = count($hbreaks); // Number of page breaks - $length = 2 + 6 * $cbrk; // Bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("v", $cbrk); - - // Append each page break - foreach ($hbreaks as $hbreak) { - $data .= pack("vvv", $hbreak, 0x0000, 0x00ff); - } - - $this->_append($header . $data); - } - - // vertical page breaks - if (!empty($vbreaks)) { - - // 1000 vertical pagebreaks appears to be an internal Excel 5 limit. - // It is slightly higher in Excel 97/200, approx. 1026 - $vbreaks = array_slice($vbreaks, 0, 1000); - - // Sort and filter array of page breaks - sort($vbreaks, SORT_NUMERIC); - if ($vbreaks[0] == 0) { // don't use first break if it's 0 - array_shift($vbreaks); - } - - $record = 0x001a; // Record identifier - $cbrk = count($vbreaks); // Number of page breaks - $length = 2 + 6 * $cbrk; // Bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("v", $cbrk); - - // Append each page break - foreach ($vbreaks as $vbreak) { - $data .= pack("vvv", $vbreak, 0x0000, 0xffff); - } - - $this->_append($header . $data); - } - } - - /** - * Set the Biff PROTECT record to indicate that the worksheet is protected. - */ - private function _writeProtect() - { - // Exit unless sheet protection has been specified - if (!$this->_phpSheet->getProtection()->getSheet()) { - return; - } - - $record = 0x0012; // Record identifier - $length = 0x0002; // Bytes to follow - - $fLock = 1; // Worksheet is protected - - $header = pack("vv", $record, $length); - $data = pack("v", $fLock); - - $this->_append($header.$data); - } - - /** - * Write SCENPROTECT - */ - private function _writeScenProtect() - { - // Exit if sheet protection is not active - if (!$this->_phpSheet->getProtection()->getSheet()) { - return; - } - - // Exit if scenarios are not protected - if (!$this->_phpSheet->getProtection()->getScenarios()) { - return; - } - - $record = 0x00DD; // Record identifier - $length = 0x0002; // Bytes to follow - - $header = pack('vv', $record, $length); - $data = pack('v', 1); - - $this->_append($header . $data); - } - - /** - * Write OBJECTPROTECT - */ - private function _writeObjectProtect() - { - // Exit if sheet protection is not active - if (!$this->_phpSheet->getProtection()->getSheet()) { - return; - } - - // Exit if objects are not protected - if (!$this->_phpSheet->getProtection()->getObjects()) { - return; - } - - $record = 0x0063; // Record identifier - $length = 0x0002; // Bytes to follow - - $header = pack('vv', $record, $length); - $data = pack('v', 1); - - $this->_append($header . $data); - } - - /** - * Write the worksheet PASSWORD record. - */ - private function _writePassword() - { - // Exit unless sheet protection and password have been specified - if (!$this->_phpSheet->getProtection()->getSheet() || !$this->_phpSheet->getProtection()->getPassword()) { - return; - } - - $record = 0x0013; // Record identifier - $length = 0x0002; // Bytes to follow - - $wPassword = hexdec($this->_phpSheet->getProtection()->getPassword()); // Encoded password - - $header = pack("vv", $record, $length); - $data = pack("v", $wPassword); - - $this->_append($header . $data); - } - - /** - * Insert a 24bit bitmap image in a worksheet. - * - * @access public - * @param integer $row The row we are going to insert the bitmap into - * @param integer $col The column we are going to insert the bitmap into - * @param mixed $bitmap The bitmap filename or GD-image resource - * @param integer $x The horizontal position (offset) of the image inside the cell. - * @param integer $y The vertical position (offset) of the image inside the cell. - * @param float $scale_x The horizontal scale - * @param float $scale_y The vertical scale - */ - function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) - { - $bitmap_array = (is_resource($bitmap) ? $this->_processBitmapGd($bitmap) : $this->_processBitmap($bitmap)); - list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap); - - // Scale the frame of the image. - $width *= $scale_x; - $height *= $scale_y; - - // Calculate the vertices of the image and write the OBJ record - $this->_positionImage($col, $row, $x, $y, $width, $height); - - // Write the IMDATA record to store the bitmap data - $record = 0x007f; - $length = 8 + $size; - $cf = 0x09; - $env = 0x01; - $lcb = $size; - - $header = pack("vvvvV", $record, $length, $cf, $env, $lcb); - $this->_append($header.$data); - } - - /** - * Calculate the vertices that define the position of the image as required by - * the OBJ record. - * - * +------------+------------+ - * | A | B | - * +-----+------------+------------+ - * | |(x1,y1) | | - * | 1 |(A1)._______|______ | - * | | | | | - * | | | | | - * +-----+----| BITMAP |-----+ - * | | | | | - * | 2 | |______________. | - * | | | (B2)| - * | | | (x2,y2)| - * +---- +------------+------------+ - * - * Example of a bitmap that covers some of the area from cell A1 to cell B2. - * - * Based on the width and height of the bitmap we need to calculate 8 vars: - * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. - * The width and height of the cells are also variable and have to be taken into - * account. - * The values of $col_start and $row_start are passed in from the calling - * function. The values of $col_end and $row_end are calculated by subtracting - * the width and height of the bitmap from the width and height of the - * underlying cells. - * The vertices are expressed as a percentage of the underlying cell width as - * follows (rhs values are in pixels): - * - * x1 = X / W *1024 - * y1 = Y / H *256 - * x2 = (X-1) / W *1024 - * y2 = (Y-1) / H *256 - * - * Where: X is distance from the left side of the underlying cell - * Y is distance from the top of the underlying cell - * W is the width of the cell - * H is the height of the cell - * The SDK incorrectly states that the height should be expressed as a - * percentage of 1024. - * - * @access private - * @param integer $col_start Col containing upper left corner of object - * @param integer $row_start Row containing top left corner of object - * @param integer $x1 Distance to left side of object - * @param integer $y1 Distance to top of object - * @param integer $width Width of image frame - * @param integer $height Height of image frame - */ - function _positionImage($col_start, $row_start, $x1, $y1, $width, $height) - { - // Initialise end cell to the same as the start cell - $col_end = $col_start; // Col containing lower right corner of object - $row_end = $row_start; // Row containing bottom right corner of object - - // Zero the specified offset if greater than the cell dimensions - if ($x1 >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) { - $x1 = 0; - } - if ($y1 >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1)) { - $y1 = 0; - } - - $width = $width + $x1 -1; - $height = $height + $y1 -1; - - // Subtract the underlying cell widths to find the end cell of the image - while ($width >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) { - $width -= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)); - ++$col_end; - } - - // Subtract the underlying cell heights to find the end cell of the image - while ($height >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1)) { - $height -= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1); - ++$row_end; - } - - // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell - // with zero eight or width. - // - if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) { - return; - } - if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) { - return; - } - if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) == 0) { - return; - } - if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) == 0) { - return; - } - - // Convert the pixel values to the percentage value expected by Excel - $x1 = $x1 / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024; - $y1 = $y1 / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) * 256; - $x2 = $width / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object - $y2 = $height / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) * 256; // Distance to bottom of object - - $this->_writeObjPicture($col_start, $x1, - $row_start, $y1, - $col_end, $x2, - $row_end, $y2); - } - - /** - * Store the OBJ record that precedes an IMDATA record. This could be generalise - * to support other Excel objects. - * - * @param integer $colL Column containing upper left corner of object - * @param integer $dxL Distance from left side of cell - * @param integer $rwT Row containing top left corner of object - * @param integer $dyT Distance from top of cell - * @param integer $colR Column containing lower right corner of object - * @param integer $dxR Distance from right of cell - * @param integer $rwB Row containing bottom right corner of object - * @param integer $dyB Distance from bottom of cell - */ - private function _writeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB) - { - $record = 0x005d; // Record identifier - $length = 0x003c; // Bytes to follow - - $cObj = 0x0001; // Count of objects in file (set to 1) - $OT = 0x0008; // Object type. 8 = Picture - $id = 0x0001; // Object ID - $grbit = 0x0614; // Option flags - - $cbMacro = 0x0000; // Length of FMLA structure - $Reserved1 = 0x0000; // Reserved - $Reserved2 = 0x0000; // Reserved - - $icvBack = 0x09; // Background colour - $icvFore = 0x09; // Foreground colour - $fls = 0x00; // Fill pattern - $fAuto = 0x00; // Automatic fill - $icv = 0x08; // Line colour - $lns = 0xff; // Line style - $lnw = 0x01; // Line weight - $fAutoB = 0x00; // Automatic border - $frs = 0x0000; // Frame style - $cf = 0x0009; // Image format, 9 = bitmap - $Reserved3 = 0x0000; // Reserved - $cbPictFmla = 0x0000; // Length of FMLA structure - $Reserved4 = 0x0000; // Reserved - $grbit2 = 0x0001; // Option flags - $Reserved5 = 0x0000; // Reserved - - - $header = pack("vv", $record, $length); - $data = pack("V", $cObj); - $data .= pack("v", $OT); - $data .= pack("v", $id); - $data .= pack("v", $grbit); - $data .= pack("v", $colL); - $data .= pack("v", $dxL); - $data .= pack("v", $rwT); - $data .= pack("v", $dyT); - $data .= pack("v", $colR); - $data .= pack("v", $dxR); - $data .= pack("v", $rwB); - $data .= pack("v", $dyB); - $data .= pack("v", $cbMacro); - $data .= pack("V", $Reserved1); - $data .= pack("v", $Reserved2); - $data .= pack("C", $icvBack); - $data .= pack("C", $icvFore); - $data .= pack("C", $fls); - $data .= pack("C", $fAuto); - $data .= pack("C", $icv); - $data .= pack("C", $lns); - $data .= pack("C", $lnw); - $data .= pack("C", $fAutoB); - $data .= pack("v", $frs); - $data .= pack("V", $cf); - $data .= pack("v", $Reserved3); - $data .= pack("v", $cbPictFmla); - $data .= pack("v", $Reserved4); - $data .= pack("v", $grbit2); - $data .= pack("V", $Reserved5); - - $this->_append($header . $data); - } - - /** - * Convert a GD-image into the internal format. - * - * @access private - * @param resource $image The image to process - * @return array Array with data and properties of the bitmap - */ - function _processBitmapGd($image) { - $width = imagesx($image); - $height = imagesy($image); - - $data = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18); - for ($j=$height; $j--; ) { - for ($i=0; $i < $width; ++$i) { - $color = imagecolorsforindex($image, imagecolorat($image, $i, $j)); - foreach (array("red", "green", "blue") as $key) { - $color[$key] = $color[$key] + round((255 - $color[$key]) * $color["alpha"] / 127); - } - $data .= chr($color["blue"]) . chr($color["green"]) . chr($color["red"]); - } - if (3*$width % 4) { - $data .= str_repeat("\x00", 4 - 3*$width % 4); - } - } - - return array($width, $height, strlen($data), $data); - } - - /** - * Convert a 24 bit bitmap into the modified internal format used by Windows. - * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the - * MSDN library. - * - * @access private - * @param string $bitmap The bitmap to process - * @return array Array with data and properties of the bitmap - */ - function _processBitmap($bitmap) - { - // Open file. - $bmp_fd = @fopen($bitmap,"rb"); - if (!$bmp_fd) { - throw new Exception("Couldn't import $bitmap"); - } - - // Slurp the file into a string. - $data = fread($bmp_fd, filesize($bitmap)); - - // Check that the file is big enough to be a bitmap. - if (strlen($data) <= 0x36) { - throw new Exception("$bitmap doesn't contain enough data.\n"); - } - - // The first 2 bytes are used to identify the bitmap. - $identity = unpack("A2ident", $data); - if ($identity['ident'] != "BM") { - throw new Exception("$bitmap doesn't appear to be a valid bitmap image.\n"); - } - - // Remove bitmap data: ID. - $data = substr($data, 2); - - // Read and remove the bitmap size. This is more reliable than reading - // the data size at offset 0x22. - // - $size_array = unpack("Vsa", substr($data, 0, 4)); - $size = $size_array['sa']; - $data = substr($data, 4); - $size -= 0x36; // Subtract size of bitmap header. - $size += 0x0C; // Add size of BIFF header. - - // Remove bitmap data: reserved, offset, header length. - $data = substr($data, 12); - - // Read and remove the bitmap width and height. Verify the sizes. - $width_and_height = unpack("V2", substr($data, 0, 8)); - $width = $width_and_height[1]; - $height = $width_and_height[2]; - $data = substr($data, 8); - if ($width > 0xFFFF) { - throw new Exception("$bitmap: largest image width supported is 65k.\n"); - } - if ($height > 0xFFFF) { - throw new Exception("$bitmap: largest image height supported is 65k.\n"); - } - - // Read and remove the bitmap planes and bpp data. Verify them. - $planes_and_bitcount = unpack("v2", substr($data, 0, 4)); - $data = substr($data, 4); - if ($planes_and_bitcount[2] != 24) { // Bitcount - throw new Exception("$bitmap isn't a 24bit true color bitmap.\n"); - } - if ($planes_and_bitcount[1] != 1) { - throw new Exception("$bitmap: only 1 plane supported in bitmap image.\n"); - } - - // Read and remove the bitmap compression. Verify compression. - $compression = unpack("Vcomp", substr($data, 0, 4)); - $data = substr($data, 4); - - //$compression = 0; - if ($compression['comp'] != 0) { - throw new Exception("$bitmap: compression not supported in bitmap image.\n"); - } - - // Remove bitmap data: data size, hres, vres, colours, imp. colours. - $data = substr($data, 20); - - // Add the BITMAPCOREHEADER data - $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18); - $data = $header . $data; - - return (array($width, $height, $size, $data)); - } - - /** - * Store the window zoom factor. This should be a reduced fraction but for - * simplicity we will store all fractions with a numerator of 100. - */ - private function _writeZoom() - { - // If scale is 100 we don't need to write a record - if ($this->_phpSheet->getSheetView()->getZoomScale() == 100) { - return; - } - - $record = 0x00A0; // Record identifier - $length = 0x0004; // Bytes to follow - - $header = pack("vv", $record, $length); - $data = pack("vv", $this->_phpSheet->getSheetView()->getZoomScale(), 100); - $this->_append($header . $data); - } - - /** - * Get Escher object - * - * @return PHPExcel_Shared_Escher - */ - public function getEscher() - { - return $this->_escher; - } - - /** - * Set Escher object - * - * @param PHPExcel_Shared_Escher $pValue - */ - public function setEscher(PHPExcel_Shared_Escher $pValue = null) - { - $this->_escher = $pValue; - } - - /** - * Write MSODRAWING record - */ - private function _writeMsoDrawing() - { - // write the Escher stream if necessary - if (isset($this->_escher)) { - $writer = new PHPExcel_Writer_Excel5_Escher($this->_escher); - $data = $writer->close(); - $spOffsets = $writer->getSpOffsets(); - $spTypes = $writer->getSpTypes(); - // write the neccesary MSODRAWING, OBJ records - - // split the Escher stream - $spOffsets[0] = 0; - $nm = count($spOffsets) - 1; // number of shapes excluding first shape - for ($i = 1; $i <= $nm; ++$i) { - // MSODRAWING record - $record = 0x00EC; // Record identifier - - // chunk of Escher stream for one shape - $dataChunk = substr($data, $spOffsets[$i -1], $spOffsets[$i] - $spOffsets[$i - 1]); - - $length = strlen($dataChunk); - $header = pack("vv", $record, $length); - - $this->_append($header . $dataChunk); - - // OBJ record - $record = 0x005D; // record identifier - $objData = ''; - - // ftCmo - if($spTypes[$i] == 0x00C9){ - // Add ftCmo (common object data) subobject - $objData .= - pack('vvvvvVVV' - , 0x0015 // 0x0015 = ftCmo - , 0x0012 // length of ftCmo data - , 0x0014 // object type, 0x0014 = filter - , $i // object id number, Excel seems to use 1-based index, local for the sheet - , 0x2101 // option flags, 0x2001 is what OpenOffice.org uses - , 0 // reserved - , 0 // reserved - , 0 // reserved - ); - - // Add ftSbs Scroll bar subobject - $objData .= pack('vv', 0x00C, 0x0014); - $objData .= pack('H*', '0000000000000000640001000A00000010000100'); - // Add ftLbsData (List box data) subobject - $objData .= pack('vv', 0x0013, 0x1FEE); - $objData .= pack('H*', '00000000010001030000020008005700'); - } - else { - // Add ftCmo (common object data) subobject - $objData .= - pack('vvvvvVVV' - , 0x0015 // 0x0015 = ftCmo - , 0x0012 // length of ftCmo data - , 0x0008 // object type, 0x0008 = picture - , $i // object id number, Excel seems to use 1-based index, local for the sheet - , 0x6011 // option flags, 0x6011 is what OpenOffice.org uses - , 0 // reserved - , 0 // reserved - , 0 // reserved - ); - } - - // ftEnd - $objData .= - pack('vv' - , 0x0000 // 0x0000 = ftEnd - , 0x0000 // length of ftEnd data - ); - - $length = strlen($objData); - $header = pack('vv', $record, $length); - $this->_append($header . $objData); - } - } - } - - /** - * Store the DATAVALIDATIONS and DATAVALIDATION records. - */ - private function _writeDataValidity() - { - // Datavalidation collection - $dataValidationCollection = $this->_phpSheet->getDataValidationCollection(); - - // Write data validations? - if (!empty($dataValidationCollection)) { - - // DATAVALIDATIONS record - $record = 0x01B2; // Record identifier - $length = 0x0012; // Bytes to follow - - $grbit = 0x0000; // Prompt box at cell, no cached validity data at DV records - $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position - $verPos = 0x00000000; // Vertical position of prompt box, if fixed position - $objId = 0xFFFFFFFF; // Object identifier of drop down arrow object, or -1 if not visible - - $header = pack('vv', $record, $length); - $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, - count($dataValidationCollection)); - $this->_append($header.$data); - - // DATAVALIDATION records - $record = 0x01BE; // Record identifier - - foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) { - // initialize record data - $data = ''; - - // options - $options = 0x00000000; - - // data type - $type = $dataValidation->getType(); - switch ($type) { - case PHPExcel_Cell_DataValidation::TYPE_NONE: $type = 0x00; break; - case PHPExcel_Cell_DataValidation::TYPE_WHOLE: $type = 0x01; break; - case PHPExcel_Cell_DataValidation::TYPE_DECIMAL: $type = 0x02; break; - case PHPExcel_Cell_DataValidation::TYPE_LIST: $type = 0x03; break; - case PHPExcel_Cell_DataValidation::TYPE_DATE: $type = 0x04; break; - case PHPExcel_Cell_DataValidation::TYPE_TIME: $type = 0x05; break; - case PHPExcel_Cell_DataValidation::TYPE_TEXTLENGTH: $type = 0x06; break; - case PHPExcel_Cell_DataValidation::TYPE_CUSTOM: $type = 0x07; break; - } - $options |= $type << 0; - - // error style - $errorStyle = $dataValidation->getType(); - switch ($errorStyle) { - case PHPExcel_Cell_DataValidation::STYLE_STOP: $errorStyle = 0x00; break; - case PHPExcel_Cell_DataValidation::STYLE_WARNING: $errorStyle = 0x01; break; - case PHPExcel_Cell_DataValidation::STYLE_INFORMATION: $errorStyle = 0x02; break; - } - $options |= $errorStyle << 4; - - // explicit formula? - if ($type == 0x03 && preg_match('/^\".*\"$/', $dataValidation->getFormula1())) { - $options |= 0x01 << 7; - } - - // empty cells allowed - $options |= $dataValidation->getAllowBlank() << 8; - - // show drop down - $options |= (!$dataValidation->getShowDropDown()) << 9; - - // show input message - $options |= $dataValidation->getShowInputMessage() << 18; - - // show error message - $options |= $dataValidation->getShowErrorMessage() << 19; - - // condition operator - $operator = $dataValidation->getOperator(); - switch ($operator) { - case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN: $operator = 0x00 ; break; - case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN: $operator = 0x01 ; break; - case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL: $operator = 0x02 ; break; - case PHPExcel_Cell_DataValidation::OPERATOR_NOTEQUAL: $operator = 0x03 ; break; - case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN: $operator = 0x04 ; break; - case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHAN: $operator = 0x05 ; break; - case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL: $operator = 0x06; break; - case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL: $operator = 0x07 ; break; - } - $options |= $operator << 20; - - $data = pack('V', $options); - - // prompt title - $promptTitle = $dataValidation->getPromptTitle() !== '' ? - $dataValidation->getPromptTitle() : chr(0); - $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($promptTitle); - - // error title - $errorTitle = $dataValidation->getErrorTitle() !== '' ? - $dataValidation->getErrorTitle() : chr(0); - $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($errorTitle); - - // prompt text - $prompt = $dataValidation->getPrompt() !== '' ? - $dataValidation->getPrompt() : chr(0); - $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($prompt); - - // error text - $error = $dataValidation->getError() !== '' ? - $dataValidation->getError() : chr(0); - $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($error); - - // formula 1 - try { - $formula1 = $dataValidation->getFormula1(); - if ($type == 0x03) { // list type - $formula1 = str_replace(',', chr(0), $formula1); - } - $this->_parser->parse($formula1); - $formula1 = $this->_parser->toReversePolish(); - $sz1 = strlen($formula1); - - } catch(Exception $e) { - $sz1 = 0; - $formula1 = ''; - } - $data .= pack('vv', $sz1, 0x0000); - $data .= $formula1; - - // formula 2 - try { - $formula2 = $dataValidation->getFormula2(); - if ($formula2 === '') { - throw new Exception('No formula2'); - } - $this->_parser->parse($formula2); - $formula2 = $this->_parser->toReversePolish(); - $sz2 = strlen($formula2); - - } catch(Exception $e) { - $sz2 = 0; - $formula2 = ''; - } - $data .= pack('vv', $sz2, 0x0000); - $data .= $formula2; - - // cell range address list - $data .= pack('v', 0x0001); - $data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate); - - $length = strlen($data); - $header = pack("vv", $record, $length); - - $this->_append($header . $data); - } - } - } - - /** - * Map Error code - * - * @param string $errorCode - * @return int - */ - private static function _mapErrorCode($errorCode) { - switch ($errorCode) { - case '#NULL!': return 0x00; - case '#DIV/0!': return 0x07; - case '#VALUE!': return 0x0F; - case '#REF!': return 0x17; - case '#NAME?': return 0x1D; - case '#NUM!': return 0x24; - case '#N/A': return 0x2A; - } - - return 0; - } - -} \ No newline at end of file diff --git a/admin/survey/excel/PHPExcel/Writer/Excel5/Xf.php b/admin/survey/excel/PHPExcel/Writer/Excel5/Xf.php deleted file mode 100644 index 60835c5..0000000 --- a/admin/survey/excel/PHPExcel/Writer/Excel5/Xf.php +++ /dev/null @@ -1,546 +0,0 @@ - -// * -// * The majority of this is _NOT_ my code. I simply ported it from the -// * PERL Spreadsheet::WriteExcel module. -// * -// * The author of the Spreadsheet::WriteExcel module is John McNamara -// * -// * -// * I _DO_ maintain this code, and John McNamara has nothing to do with the -// * porting of this code to PHP. Any questions directly related to this -// * class library should be directed to me. -// * -// * License Information: -// * -// * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets -// * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com -// * -// * This library is free software; you can redistribute it and/or -// * modify it under the terms of the GNU Lesser General Public -// * License as published by the Free Software Foundation; either -// * version 2.1 of the License, or (at your option) any later version. -// * -// * This library is distributed in the hope that it will be useful, -// * but WITHOUT ANY WARRANTY; without even the implied warranty of -// * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -// * Lesser General Public License for more details. -// * -// * You should have received a copy of the GNU Lesser General Public -// * License along with this library; if not, write to the Free Software -// * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA -// */ - - -/** - * PHPExcel_Writer_Excel5_Xf - * - * @category PHPExcel - * @package PHPExcel_Writer_Excel5 - * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel) - */ -class PHPExcel_Writer_Excel5_Xf -{ - /** - * Style XF or a cell XF ? - * - * @var boolean - */ - private $_isStyleXf; - - /** - * Index to the FONT record. Index 4 does not exist - * @var integer - */ - private $_fontIndex; - - /** - * An index (2 bytes) to a FORMAT record (number format). - * @var integer - */ - public $_numberFormatIndex; - - /** - * 1 bit, apparently not used. - * @var integer - */ - public $_text_justlast; - - /** - * The cell's foreground color. - * @var integer - */ - public $_fg_color; - - /** - * The cell's background color. - * @var integer - */ - public $_bg_color; - - /** - * Color of the bottom border of the cell. - * @var integer - */ - public $_bottom_color; - - /** - * Color of the top border of the cell. - * @var integer - */ - public $_top_color; - - /** - * Color of the left border of the cell. - * @var integer - */ - public $_left_color; - - /** - * Color of the right border of the cell. - * @var integer - */ - public $_right_color; - - /** - * Constructor - * - * @access public - * @param PHPExcel_Style The XF format - */ - public function __construct(PHPExcel_Style $style = null) - { - $this->_isStyleXf = false; - $this->_fontIndex = 0; - - $this->_numberFormatIndex = 0; - - $this->_text_justlast = 0; - - $this->_fg_color = 0x40; - $this->_bg_color = 0x41; - - $this->_diag = 0; - - $this->_bottom_color = 0x40; - $this->_top_color = 0x40; - $this->_left_color = 0x40; - $this->_right_color = 0x40; - $this->_diag_color = 0x40; - $this->_style = $style; - - } - - - /** - * Generate an Excel BIFF XF record (style or cell). - * - * @return string The XF record - */ - function writeXf() - { - // Set the type of the XF record and some of the attributes. - if ($this->_isStyleXf) { - $style = 0xFFF5; - } else { - $style = self::_mapLocked($this->_style->getProtection()->getLocked()); - $style |= self::_mapHidden($this->_style->getProtection()->getHidden()) << 1; - } - - // Flags to indicate if attributes have been set. - $atr_num = ($this->_numberFormatIndex != 0)?1:0; - $atr_fnt = ($this->_fontIndex != 0)?1:0; - $atr_alc = ((int) $this->_style->getAlignment()->getWrapText())?1:0; - $atr_bdr = (self::_mapBorderStyle($this->_style->getBorders()->getBottom()->getBorderStyle()) || - self::_mapBorderStyle($this->_style->getBorders()->getTop()->getBorderStyle()) || - self::_mapBorderStyle($this->_style->getBorders()->getLeft()->getBorderStyle()) || - self::_mapBorderStyle($this->_style->getBorders()->getRight()->getBorderStyle()))?1:0; - $atr_pat = (($this->_fg_color != 0x40) || - ($this->_bg_color != 0x41) || - self::_mapFillType($this->_style->getFill()->getFillType()))?1:0; - $atr_prot = self::_mapLocked($this->_style->getProtection()->getLocked()) - | self::_mapHidden($this->_style->getProtection()->getHidden()); - - // Zero the default border colour if the border has not been set. - if (self::_mapBorderStyle($this->_style->getBorders()->getBottom()->getBorderStyle()) == 0) { - $this->_bottom_color = 0; - } - if (self::_mapBorderStyle($this->_style->getBorders()->getTop()->getBorderStyle()) == 0) { - $this->_top_color = 0; - } - if (self::_mapBorderStyle($this->_style->getBorders()->getRight()->getBorderStyle()) == 0) { - $this->_right_color = 0; - } - if (self::_mapBorderStyle($this->_style->getBorders()->getLeft()->getBorderStyle()) == 0) { - $this->_left_color = 0; - } - if (self::_mapBorderStyle($this->_style->getBorders()->getDiagonal()->getBorderStyle()) == 0) { - $this->_diag_color = 0; - } - - $record = 0x00E0; // Record identifier - $length = 0x0014; // Number of bytes to follow - - $ifnt = $this->_fontIndex; // Index to FONT record - $ifmt = $this->_numberFormatIndex; // Index to FORMAT record - - $align = $this->_mapHAlign($this->_style->getAlignment()->getHorizontal()); // Alignment - $align |= (int) $this->_style->getAlignment()->getWrapText() << 3; - $align |= self::_mapVAlign($this->_style->getAlignment()->getVertical()) << 4; - $align |= $this->_text_justlast << 7; - - $used_attrib = $atr_num << 2; - $used_attrib |= $atr_fnt << 3; - $used_attrib |= $atr_alc << 4; - $used_attrib |= $atr_bdr << 5; - $used_attrib |= $atr_pat << 6; - $used_attrib |= $atr_prot << 7; - - $icv = $this->_fg_color; // fg and bg pattern colors - $icv |= $this->_bg_color << 7; - - $border1 = self::_mapBorderStyle($this->_style->getBorders()->getLeft()->getBorderStyle()); // Border line style and color - $border1 |= self::_mapBorderStyle($this->_style->getBorders()->getRight()->getBorderStyle()) << 4; - $border1 |= self::_mapBorderStyle($this->_style->getBorders()->getTop()->getBorderStyle()) << 8; - $border1 |= self::_mapBorderStyle($this->_style->getBorders()->getBottom()->getBorderStyle()) << 12; - $border1 |= $this->_left_color << 16; - $border1 |= $this->_right_color << 23; - - $diagonalDirection = $this->_style->getBorders()->getDiagonalDirection(); - $diag_tl_to_rb = $diagonalDirection == PHPExcel_Style_Borders::DIAGONAL_BOTH - || $diagonalDirection == PHPExcel_Style_Borders::DIAGONAL_DOWN; - $diag_tr_to_lb = $diagonalDirection == PHPExcel_Style_Borders::DIAGONAL_BOTH - || $diagonalDirection == PHPExcel_Style_Borders::DIAGONAL_UP; - $border1 |= $diag_tl_to_rb << 30; - $border1 |= $diag_tr_to_lb << 31; - - $border2 = $this->_top_color; // Border color - $border2 |= $this->_bottom_color << 7; - $border2 |= $this->_diag_color << 14; - $border2 |= self::_mapBorderStyle($this->_style->getBorders()->getDiagonal()->getBorderStyle()) << 21; - $border2 |= self::_mapFillType($this->_style->getFill()->getFillType()) << 26; - - $header = pack("vv", $record, $length); - - //BIFF8 options: identation, shrinkToFit and text direction - $biff8_options = $this->_style->getAlignment()->getIndent(); - $biff8_options |= (int) $this->_style->getAlignment()->getShrinkToFit() << 4; - - $data = pack("vvvC", $ifnt, $ifmt, $style, $align); - $data .= pack("CCC" - , self::_mapTextRotation($this->_style->getAlignment()->getTextRotation()) - , $biff8_options - , $used_attrib - ); - $data .= pack("VVv", $border1, $border2, $icv); - - return($header . $data); - } - - /** - * Is this a style XF ? - * - * @param boolean $value - */ - public function setIsStyleXf($value) - { - $this->_isStyleXf = $value; - } - - /** - * Sets the cell's bottom border color - * - * @access public - * @param int $colorIndex Color index - */ - function setBottomColor($colorIndex) - { - $this->_bottom_color = $colorIndex; - } - - /** - * Sets the cell's top border color - * - * @access public - * @param int $colorIndex Color index - */ - function setTopColor($colorIndex) - { - $this->_top_color = $colorIndex; - } - - /** - * Sets the cell's left border color - * - * @access public - * @param int $colorIndex Color index - */ - function setLeftColor($colorIndex) - { - $this->_left_color = $colorIndex; - } - - /** - * Sets the cell's right border color - * - * @access public - * @param int $colorIndex Color index - */ - function setRightColor($colorIndex) - { - $this->_right_color = $colorIndex; - } - - /** - * Sets the cell's diagonal border color - * - * @access public - * @param int $colorIndex Color index - */ - function setDiagColor($colorIndex) - { - $this->_diag_color = $colorIndex; - } - - - /** - * Sets the cell's foreground color - * - * @access public - * @param int $colorIndex Color index - */ - function setFgColor($colorIndex) - { - $this->_fg_color = $colorIndex; - } - - /** - * Sets the cell's background color - * - * @access public - * @param int $colorIndex Color index - */ - function setBgColor($colorIndex) - { - $this->_bg_color = $colorIndex; - } - - /** - * Sets the index to the number format record - * It can be date, time, currency, etc... - * - * @access public - * @param integer $numberFormatIndex Index to format record - */ - function setNumberFormatIndex($numberFormatIndex) - { - $this->_numberFormatIndex = $numberFormatIndex; - } - - /** - * Set the font index. - * - * @param int $value Font index, note that value 4 does not exist - */ - public function setFontIndex($value) - { - $this->_fontIndex = $value; - } - - /** - * Map of BIFF2-BIFF8 codes for border styles - * @static array of int - * - */ - private static $_mapBorderStyle = array ( PHPExcel_Style_Border::BORDER_NONE => 0x00, - PHPExcel_Style_Border::BORDER_THIN => 0x01, - PHPExcel_Style_Border::BORDER_MEDIUM => 0x02, - PHPExcel_Style_Border::BORDER_DASHED => 0x03, - PHPExcel_Style_Border::BORDER_DOTTED => 0x04, - PHPExcel_Style_Border::BORDER_THICK => 0x05, - PHPExcel_Style_Border::BORDER_DOUBLE => 0x06, - PHPExcel_Style_Border::BORDER_HAIR => 0x07, - PHPExcel_Style_Border::BORDER_MEDIUMDASHED => 0x08, - PHPExcel_Style_Border::BORDER_DASHDOT => 0x09, - PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT => 0x0A, - PHPExcel_Style_Border::BORDER_DASHDOTDOT => 0x0B, - PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT => 0x0C, - PHPExcel_Style_Border::BORDER_SLANTDASHDOT => 0x0D, - ); - - /** - * Map border style - * - * @param string $borderStyle - * @return int - */ - private static function _mapBorderStyle($borderStyle) { - if (isset(self::$_mapBorderStyle[$borderStyle])) - return self::$_mapBorderStyle[$borderStyle]; - return 0x00; - } - - /** - * Map of BIFF2-BIFF8 codes for fill types - * @static array of int - * - */ - private static $_mapFillType = array( PHPExcel_Style_Fill::FILL_NONE => 0x00, - PHPExcel_Style_Fill::FILL_SOLID => 0x01, - PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY => 0x02, - PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY => 0x03, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY => 0x04, - PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL => 0x05, - PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL => 0x06, - PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN => 0x07, - PHPExcel_Style_Fill::FILL_PATTERN_DARKUP => 0x08, - PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID => 0x09, - PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS => 0x0A, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL => 0x0B, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL => 0x0C, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN => 0x0D, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP => 0x0E, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID => 0x0F, - PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS => 0x10, - PHPExcel_Style_Fill::FILL_PATTERN_GRAY125 => 0x11, - PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625 => 0x12, - PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR => 0x00, // does not exist in BIFF8 - PHPExcel_Style_Fill::FILL_GRADIENT_PATH => 0x00, // does not exist in BIFF8 - ); - /** - * Map fill type - * - * @param string $fillType - * @return int - */ - private static function _mapFillType($fillType) { - if (isset(self::$_mapFillType[$fillType])) - return self::$_mapFillType[$fillType]; - return 0x00; - } - - /** - * Map of BIFF2-BIFF8 codes for horizontal alignment - * @static array of int - * - */ - private static $_mapHAlign = array( PHPExcel_Style_Alignment::HORIZONTAL_GENERAL => 0, - PHPExcel_Style_Alignment::HORIZONTAL_LEFT => 1, - PHPExcel_Style_Alignment::HORIZONTAL_CENTER => 2, - PHPExcel_Style_Alignment::HORIZONTAL_RIGHT => 3, - PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY => 5, - PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS => 6, - ); - /** - * Map to BIFF2-BIFF8 codes for horizontal alignment - * - * @param string $hAlign - * @return int - */ - private function _mapHAlign($hAlign) - { - if (isset(self::$_mapHAlign[$hAlign])) - return self::$_mapHAlign[$hAlign]; - return 0; - } - - /** - * Map of BIFF2-BIFF8 codes for vertical alignment - * @static array of int - * - */ - private static $_mapVAlign = array( PHPExcel_Style_Alignment::VERTICAL_TOP => 0, - PHPExcel_Style_Alignment::VERTICAL_CENTER => 1, - PHPExcel_Style_Alignment::VERTICAL_BOTTOM => 2, - PHPExcel_Style_Alignment::VERTICAL_JUSTIFY => 3, - ); - /** - * Map to BIFF2-BIFF8 codes for vertical alignment - * - * @param string $vAlign - * @return int - */ - private static function _mapVAlign($vAlign) { - if (isset(self::$_mapVAlign[$vAlign])) - return self::$_mapVAlign[$vAlign]; - return 2; - } - - /** - * Map to BIFF8 codes for text rotation angle - * - * @param int $textRotation - * @return int - */ - private static function _mapTextRotation($textRotation) { - if ($textRotation >= 0) { - return $textRotation; - } - if ($textRotation == -165) { - return 255; - } - if ($textRotation < 0) { - return 90 - $textRotation; - } - } - - /** - * Map locked - * - * @param string - * @return int - */ - private static function _mapLocked($locked) { - switch ($locked) { - case PHPExcel_Style_Protection::PROTECTION_INHERIT: return 1; - case PHPExcel_Style_Protection::PROTECTION_PROTECTED: return 1; - case PHPExcel_Style_Protection::PROTECTION_UNPROTECTED: return 0; - default: return 1; - } - } - - /** - * Map hidden - * - * @param string - * @return int - */ - private static function _mapHidden($hidden) { - switch ($hidden) { - case PHPExcel_Style_Protection::PROTECTION_INHERIT: return 0; - case PHPExcel_Style_Protection::PROTECTION_PROTECTED: return 1; - case PHPExcel_Style_Protection::PROTECTION_UNPROTECTED: return 0; - default: return 0; - } - } - -} -- cgit v1.2.3