Rev 1605 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
<?php/** Module written/ported by Xavier Noguer <xnoguer@rezebra.com>** 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* <jmcnamara@cpan.org>** 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*/require_once 'Spreadsheet/Excel/Writer/Format.php';require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';require_once 'Spreadsheet/Excel/Writer/Worksheet.php';require_once 'Spreadsheet/Excel/Writer/Parser.php';require_once 'OLE/PPS/Root.php';require_once 'OLE/PPS/File.php';/*** Class for generating Excel Spreadsheets** @author Xavier Noguer <xnoguer@rezebra.com>* @category FileFormats* @package Spreadsheet_Excel_Writer*/class Spreadsheet_Excel_Writer_Workbook extends Spreadsheet_Excel_Writer_BIFFwriter{/*** Filename for the Workbook* @var string*/var $_filename;/*** Formula parser* @var object Parser*/var $_parser;/*** Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)* @var integer*/var $_1904;/*** The active worksheet of the workbook (0 indexed)* @var integer*/var $_activesheet;/*** 1st displayed worksheet in the workbook (0 indexed)* @var integer*/var $_firstsheet;/*** Number of workbook tabs selected* @var integer*/var $_selected;/*** Index for creating adding new formats to the workbook* @var integer*/var $_xf_index;/*** Flag for preventing close from being called twice.* @var integer* @see close()*/var $_fileclosed;/*** The BIFF file size for the workbook.* @var integer* @see _calcSheetOffsets()*/var $_biffsize;/*** The default sheetname for all sheets created.* @var string*/var $_sheetname;/*** The default XF format.* @var object Format*/var $_tmp_format;/*** Array containing references to all of this workbook's worksheets* @var array*/var $_worksheets;/*** Array of sheetnames for creating the EXTERNSHEET records* @var array*/var $_sheetnames;/*** Array containing references to all of this workbook's formats* @var array*/var $_formats;/*** Array containing the colour palette* @var array*/var $_palette;/*** The default format for URLs.* @var object Format*/var $_url_format;/*** The codepage indicates the text encoding used for strings* @var integer*/var $_codepage;/*** The country code used for localization* @var integer*/var $_country_code;/*** number of bytes for sizeinfo of strings* @var integer*/var $_string_sizeinfo_size;/*** Class constructor** @param string filename for storing the workbook. "-" for writing to stdout.* @access public*/function Spreadsheet_Excel_Writer_Workbook($filename){// It needs to call its parent's constructor explicitly$this->Spreadsheet_Excel_Writer_BIFFwriter();$this->_filename = $filename;$this->_parser = new Spreadsheet_Excel_Writer_Parser($this->_byte_order, $this->_BIFF_version);$this->_1904 = 0;$this->_activesheet = 0;$this->_firstsheet = 0;$this->_selected = 0;$this->_xf_index = 16; // 15 style XF's and 1 cell XF.$this->_fileclosed = 0;$this->_biffsize = 0;$this->_sheetname = 'Sheet';$this->_tmp_format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version);$this->_worksheets = array();$this->_sheetnames = array();$this->_formats = array();$this->_palette = array();$this->_codepage = 0x04E4; // FIXME: should change for BIFF8$this->_country_code = -1;$this->_string_sizeinfo = 3;// Add the default format for hyperlinks$this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));$this->_str_total = 0;$this->_str_unique = 0;$this->_str_table = array();$this->_setPaletteXl97();}/*** Calls finalization methods.* This method should always be the last one to be called on every workbook** @access public* @return mixed true on success. PEAR_Error on failure*/function close(){if ($this->_fileclosed) { // Prevent close() from being called twice.return true;}$res = $this->_storeWorkbook();if ($this->isError($res)) {return $this->raiseError($res->getMessage());}$this->_fileclosed = 1;return true;}/*** An accessor for the _worksheets[] array* Returns an array of the worksheet objects in a workbook* It actually calls to worksheets()** @access public* @see worksheets()* @return array*/function sheets(){return $this->worksheets();}/*** An accessor for the _worksheets[] array.* Returns an array of the worksheet objects in a workbook** @access public* @return array*/function worksheets(){return $this->_worksheets;}/*** Sets the BIFF version.* This method exists just to access experimental functionality* from BIFF8. It will be deprecated !* Only possible value is 8 (Excel 97/2000).* For any other value it fails silently.** @access public* @param integer $version The BIFF version*/function setVersion($version){if ($version == 8) { // only accept version 8$version = 0x0600;$this->_BIFF_version = $version;// change BIFFwriter limit for CONTINUE records$this->_limit = 8228;$this->_tmp_format->_BIFF_version = $version;$this->_url_format->_BIFF_version = $version;$this->_parser->_BIFF_version = $version;$this->_codepage = 0x04B0;$total_worksheets = count($this->_worksheets);// change version for all worksheets toofor ($i = 0; $i < $total_worksheets; $i++) {$this->_worksheets[$i]->_BIFF_version = $version;}$total_formats = count($this->_formats);// change version for all formats toofor ($i = 0; $i < $total_formats; $i++) {$this->_formats[$i]->_BIFF_version = $version;}}}/*** Set the country identifier for the workbook** @access public* @param integer $code Is the international calling country code for the* chosen country.*/function setCountry($code){$this->_country_code = $code;}/*** Add a new worksheet to the Excel workbook.* If no name is given the name of the worksheet will be Sheeti$i, with* $i in [1..].** @access public* @param string $name the optional name of the worksheet* @return mixed reference to a worksheet object on success, PEAR_Error* on failure*/function &addWorksheet($name = ''){$index = count($this->_worksheets);$sheetname = $this->_sheetname;if ($name == '') {$name = $sheetname.($index+1);}// Check that sheetname is <= 31 chars (Excel limit before BIFF8).if ($this->_BIFF_version != 0x0600){if (strlen($name) > 31) {return $this->raiseError("Sheetname $name must be <= 31 chars");}} else {if(function_exists('iconv')) {$name = iconv('UTF-8','UTF-16LE',$name);}}// Check that the worksheet name doesn't already exist: a fatal Excel error.$total_worksheets = count($this->_worksheets);for ($i = 0; $i < $total_worksheets; $i++) {if ($this->_worksheets[$i]->getName() == $name) {return $this->raiseError("Worksheet '$name' already exists");}}$worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,$name, $index,$this->_activesheet, $this->_firstsheet,$this->_str_total, $this->_str_unique,$this->_str_table, $this->_url_format,$this->_parser, $this->_tmp_dir);$this->_worksheets[$index] = &$worksheet; // Store ref for iterator$this->_sheetnames[$index] = $name; // Store EXTERNSHEET names$this->_parser->setExtSheet($name, $index); // Register worksheet name with parserreturn $worksheet;}/*** Add a new format to the Excel workbook.* Also, pass any properties to the Format constructor.** @access public* @param array $properties array with properties for initializing the format.* @return &Spreadsheet_Excel_Writer_Format reference to an Excel Format*/function &addFormat($properties = array()){$format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);$this->_xf_index += 1;$this->_formats[] = &$format;return $format;}/*** Create new validator.** @access public* @return &Spreadsheet_Excel_Writer_Validator reference to a Validator*/function &addValidator(){include_once 'Spreadsheet/Excel/Writer/Validator.php';/* FIXME: check for successful inclusion*/$valid = new Spreadsheet_Excel_Writer_Validator($this->_parser);return $valid;}/*** Change the RGB components of the elements in the colour palette.** @access public* @param integer $index colour index* @param integer $red red RGB value [0-255]* @param integer $green green RGB value [0-255]* @param integer $blue blue RGB value [0-255]* @return integer The palette index for the custom color*/function setCustomColor($index, $red, $green, $blue){// Match a HTML #xxyyzz style parameter/*if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {@_ = ($_[0], hex $1, hex $2, hex $3);}*/// Check that the colour index is the right rangeif ($index < 8 or $index > 64) {// TODO: assign real error codesreturn $this->raiseError("Color index $index outside range: 8 <= index <= 64");}// Check that the colour components are in the right rangeif (($red < 0 or $red > 255) ||($green < 0 or $green > 255) ||($blue < 0 or $blue > 255)){return $this->raiseError("Color component outside range: 0 <= color <= 255");}$index -= 8; // Adjust colour index (wingless dragonfly)// Set the RGB value$this->_palette[$index] = array($red, $green, $blue, 0);return($index + 8);}/*** Sets the colour palette to the Excel 97+ default.** @access private*/function _setPaletteXl97(){$this->_palette = array(array(0x00, 0x00, 0x00, 0x00), // 8array(0xff, 0xff, 0xff, 0x00), // 9array(0xff, 0x00, 0x00, 0x00), // 10array(0x00, 0xff, 0x00, 0x00), // 11array(0x00, 0x00, 0xff, 0x00), // 12array(0xff, 0xff, 0x00, 0x00), // 13array(0xff, 0x00, 0xff, 0x00), // 14array(0x00, 0xff, 0xff, 0x00), // 15array(0x80, 0x00, 0x00, 0x00), // 16array(0x00, 0x80, 0x00, 0x00), // 17array(0x00, 0x00, 0x80, 0x00), // 18array(0x80, 0x80, 0x00, 0x00), // 19array(0x80, 0x00, 0x80, 0x00), // 20array(0x00, 0x80, 0x80, 0x00), // 21array(0xc0, 0xc0, 0xc0, 0x00), // 22array(0x80, 0x80, 0x80, 0x00), // 23array(0x99, 0x99, 0xff, 0x00), // 24array(0x99, 0x33, 0x66, 0x00), // 25array(0xff, 0xff, 0xcc, 0x00), // 26array(0xcc, 0xff, 0xff, 0x00), // 27array(0x66, 0x00, 0x66, 0x00), // 28array(0xff, 0x80, 0x80, 0x00), // 29array(0x00, 0x66, 0xcc, 0x00), // 30array(0xcc, 0xcc, 0xff, 0x00), // 31array(0x00, 0x00, 0x80, 0x00), // 32array(0xff, 0x00, 0xff, 0x00), // 33array(0xff, 0xff, 0x00, 0x00), // 34array(0x00, 0xff, 0xff, 0x00), // 35array(0x80, 0x00, 0x80, 0x00), // 36array(0x80, 0x00, 0x00, 0x00), // 37array(0x00, 0x80, 0x80, 0x00), // 38array(0x00, 0x00, 0xff, 0x00), // 39array(0x00, 0xcc, 0xff, 0x00), // 40array(0xcc, 0xff, 0xff, 0x00), // 41array(0xcc, 0xff, 0xcc, 0x00), // 42array(0xff, 0xff, 0x99, 0x00), // 43array(0x99, 0xcc, 0xff, 0x00), // 44array(0xff, 0x99, 0xcc, 0x00), // 45array(0xcc, 0x99, 0xff, 0x00), // 46array(0xff, 0xcc, 0x99, 0x00), // 47array(0x33, 0x66, 0xff, 0x00), // 48array(0x33, 0xcc, 0xcc, 0x00), // 49array(0x99, 0xcc, 0x00, 0x00), // 50array(0xff, 0xcc, 0x00, 0x00), // 51array(0xff, 0x99, 0x00, 0x00), // 52array(0xff, 0x66, 0x00, 0x00), // 53array(0x66, 0x66, 0x99, 0x00), // 54array(0x96, 0x96, 0x96, 0x00), // 55array(0x00, 0x33, 0x66, 0x00), // 56array(0x33, 0x99, 0x66, 0x00), // 57array(0x00, 0x33, 0x00, 0x00), // 58array(0x33, 0x33, 0x00, 0x00), // 59array(0x99, 0x33, 0x00, 0x00), // 60array(0x99, 0x33, 0x66, 0x00), // 61array(0x33, 0x33, 0x99, 0x00), // 62array(0x33, 0x33, 0x33, 0x00), // 63);}/*** Assemble worksheets into a workbook and send the BIFF data to an OLE* storage.** @access private* @return mixed true on success. PEAR_Error on failure*/function _storeWorkbook(){if (count($this->_worksheets) == 0) {return true;}// Ensure that at least one worksheet has been selected.if ($this->_activesheet == 0) {$this->_worksheets[0]->selected = 1;}// Calculate the number of selected worksheet tabs and call the finalization// methods for each worksheet$total_worksheets = count($this->_worksheets);for ($i = 0; $i < $total_worksheets; $i++) {if ($this->_worksheets[$i]->selected) {$this->_selected++;}$this->_worksheets[$i]->close($this->_sheetnames);}// Add Workbook globals$this->_storeBof(0x0005);$this->_storeCodepage();if ($this->_BIFF_version == 0x0600) {$this->_storeWindow1();}if ($this->_BIFF_version == 0x0500) {$this->_storeExterns(); // For print area and repeat rows}$this->_storeNames(); // For print area and repeat rowsif ($this->_BIFF_version == 0x0500) {$this->_storeWindow1();}$this->_storeDatemode();$this->_storeAllFonts();$this->_storeAllNumFormats();$this->_storeAllXfs();$this->_storeAllStyles();$this->_storePalette();$this->_calcSheetOffsets();// Add BOUNDSHEET recordsfor ($i = 0; $i < $total_worksheets; $i++) {$this->_storeBoundsheet($this->_worksheets[$i]->name,$this->_worksheets[$i]->offset);}if ($this->_country_code != -1) {$this->_storeCountry();}if ($this->_BIFF_version == 0x0600) {//$this->_storeSupbookInternal();/* TODO: store external SUPBOOK records and XCT and CRN recordsin case of external references for BIFF8 *///$this->_storeExternsheetBiff8();$this->_storeSharedStringsTable();}// End Workbook globals$this->_storeEof();// Store the workbook in an OLE container$res = $this->_storeOLEFile();if ($this->isError($res)) {return $this->raiseError($res->getMessage());}return true;}/*** Store the workbook in an OLE container** @access private* @return mixed true on success. PEAR_Error on failure*/function _storeOLEFile(){if($this->_BIFF_version == 0x0600) {$OLE = new OLE_PPS_File(OLE::Asc2Ucs('Workbook'));} else {$OLE = new OLE_PPS_File(OLE::Asc2Ucs('Book'));}if ($this->_tmp_dir != '') {$OLE->setTempDir($this->_tmp_dir);}$res = $OLE->init();if ($this->isError($res)) {return $this->raiseError("OLE Error: ".$res->getMessage());}$OLE->append($this->_data);$total_worksheets = count($this->_worksheets);for ($i = 0; $i < $total_worksheets; $i++) {while ($tmp = $this->_worksheets[$i]->getData()) {$OLE->append($tmp);}}$root = new OLE_PPS_Root(time(), time(), array($OLE));if ($this->_tmp_dir != '') {$root->setTempDir($this->_tmp_dir);}$res = $root->save($this->_filename);if ($this->isError($res)) {return $this->raiseError("OLE Error: ".$res->getMessage());}return true;}/*** Calculate offsets for Worksheet BOF records.** @access private*/function _calcSheetOffsets(){if ($this->_BIFF_version == 0x0600) {$boundsheet_length = 12; // fixed length for a BOUNDSHEET record} else {$boundsheet_length = 11;}$EOF = 4;$offset = $this->_datasize;if ($this->_BIFF_version == 0x0600) {// add the length of the SST/* TODO: check this works for a lot of strings (> 8224 bytes) */$offset += $this->_calculateSharedStringsSizes();if ($this->_country_code != -1) {$offset += 8; // adding COUNTRY record}// add the lenght of SUPBOOK, EXTERNSHEET and NAME records//$offset += 8; // FIXME: calculate real value when storing the records}$total_worksheets = count($this->_worksheets);// add the length of the BOUNDSHEET recordsfor ($i = 0; $i < $total_worksheets; $i++) {$offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);}$offset += $EOF;for ($i = 0; $i < $total_worksheets; $i++) {$this->_worksheets[$i]->offset = $offset;$offset += $this->_worksheets[$i]->_datasize;}$this->_biffsize = $offset;}/*** Store the Excel FONT records.** @access private*/function _storeAllFonts(){// tmp_format is added by the constructor. We use this to write the default XF's$format = $this->_tmp_format;$font = $format->getFont();// Note: Fonts are 0-indexed. According to the SDK there is no index 4,// so the following fonts are 0, 1, 2, 3, 5//for ($i = 1; $i <= 5; $i++){$this->_append($font);}// Iterate through the XF objects and write a FONT record if it isn't the// same as the default FONT and if it hasn't already been used.//$fonts = array();$index = 6; // The first user defined FONT$key = $format->getFontKey(); // The default font from _tmp_format$fonts[$key] = 0; // Index of the default font$total_formats = count($this->_formats);for ($i = 0; $i < $total_formats; $i++) {$key = $this->_formats[$i]->getFontKey();if (isset($fonts[$key])) {// FONT has already been used$this->_formats[$i]->font_index = $fonts[$key];} else {// Add a new FONT record$fonts[$key] = $index;$this->_formats[$i]->font_index = $index;$index++;$font = $this->_formats[$i]->getFont();$this->_append($font);}}}/*** Store user defined numerical formats i.e. FORMAT records** @access private*/function _storeAllNumFormats(){// Leaning num_format syndrome$hash_num_formats = array();$num_formats = array();$index = 164;// Iterate through the XF objects and write a FORMAT record if it isn't a// built-in format type and if the FORMAT string hasn't already been used.$total_formats = count($this->_formats);for ($i = 0; $i < $total_formats; $i++) {$num_format = $this->_formats[$i]->_num_format;// Check if $num_format is an index to a built-in format.// Also check for a string of zeros, which is a valid format string// but would evaluate to zero.//if (!preg_match("/^0+\d/", $num_format)) {if (preg_match("/^\d+$/", $num_format)) { // built-in formatcontinue;}}if (isset($hash_num_formats[$num_format])) {// FORMAT has already been used$this->_formats[$i]->_num_format = $hash_num_formats[$num_format];} else{// Add a new FORMAT$hash_num_formats[$num_format] = $index;$this->_formats[$i]->_num_format = $index;array_push($num_formats,$num_format);$index++;}}// Write the new FORMAT records starting from 0xA4$index = 164;foreach ($num_formats as $num_format) {$this->_storeNumFormat($num_format,$index);$index++;}}/*** Write all XF records.** @access private*/function _storeAllXfs(){// _tmp_format is added by the constructor. We use this to write the default XF's// The default font index is 0//$format = $this->_tmp_format;for ($i = 0; $i <= 14; $i++) {$xf = $format->getXf('style'); // Style XF$this->_append($xf);}$xf = $format->getXf('cell'); // Cell XF$this->_append($xf);// User defined XFs$total_formats = count($this->_formats);for ($i = 0; $i < $total_formats; $i++) {$xf = $this->_formats[$i]->getXf('cell');$this->_append($xf);}}/*** Write all STYLE records.** @access private*/function _storeAllStyles(){$this->_storeStyle();}/*** Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for* the NAME records.** @access private*/function _storeExterns(){// Create EXTERNCOUNT with number of worksheets$this->_storeExterncount(count($this->_worksheets));// Create EXTERNSHEET for each worksheetforeach ($this->_sheetnames as $sheetname) {$this->_storeExternsheet($sheetname);}}/*** Write the NAME record to define the print area and the repeat rows and cols.** @access private*/function _storeNames(){// Create the print area NAME records$total_worksheets = count($this->_worksheets);for ($i = 0; $i < $total_worksheets; $i++) {// Write a Name record if the print area has been definedif (isset($this->_worksheets[$i]->print_rowmin)) {$this->_storeNameShort($this->_worksheets[$i]->index,0x06, // NAME type$this->_worksheets[$i]->print_rowmin,$this->_worksheets[$i]->print_rowmax,$this->_worksheets[$i]->print_colmin,$this->_worksheets[$i]->print_colmax);}}// Create the print title NAME records$total_worksheets = count($this->_worksheets);for ($i = 0; $i < $total_worksheets; $i++) {$rowmin = $this->_worksheets[$i]->title_rowmin;$rowmax = $this->_worksheets[$i]->title_rowmax;$colmin = $this->_worksheets[$i]->title_colmin;$colmax = $this->_worksheets[$i]->title_colmax;// Determine if row + col, row, col or nothing has been defined// and write the appropriate record//if (isset($rowmin) && isset($colmin)) {// Row and column titles have been defined.// Row title has been defined.$this->_storeNameLong($this->_worksheets[$i]->index,0x07, // NAME type$rowmin,$rowmax,$colmin,$colmax);} elseif (isset($rowmin)) {// Row title has been defined.$this->_storeNameShort($this->_worksheets[$i]->index,0x07, // NAME type$rowmin,$rowmax,0x00,0xff);} elseif (isset($colmin)) {// Column title has been defined.$this->_storeNameShort($this->_worksheets[$i]->index,0x07, // NAME type0x0000,0x3fff,$colmin,$colmax);} else {// Print title hasn't been defined.}}}/******************************************************************************** BIFF RECORDS**//*** Stores the CODEPAGE biff record.** @access private*/function _storeCodepage(){$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);$data = $header . $data;$this->_append($data);}/*** Write Excel BIFF WINDOW1 record.** @access private*/function _storeWindow1(){$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$ctabsel = $this->_selected; // Number of workbook tabs selected$wTabRatio = 0x0258; // Tab to scrollbar ratio$itabFirst = $this->_firstsheet; // 1st displayed worksheet$itabCur = $this->_activesheet; // Active worksheet$header = pack("vv", $record, $length);$data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,$grbit,$itabCur, $itabFirst,$ctabsel, $wTabRatio);$data = $header . $data;$this->_append($data);}/*** Writes Excel BIFF BOUNDSHEET record.* FIXME: inconsistent with BIFF documentation** @param string $sheetname Worksheet name* @param integer $offset Location of worksheet BOF* @access private*/function _storeBoundsheet($sheetname,$offset){$record = 0x0085; // Record identifierif ($this->_BIFF_version == 0x0600) {$length = 0x08 + strlen($sheetname); // Number of bytes to follow} else {$length = 0x07 + strlen($sheetname); // Number of bytes to follow}$grbit = 0x0000; // Visibility and sheet typeif ($this->_BIFF_version == 0x0600) {$cch = mb_strlen($sheetname,'UTF-16LE'); // Length of sheet name} else {$cch = strlen($sheetname); // Length of sheet name}$header = pack("vv", $record, $length);if ($this->_BIFF_version == 0x0600) {$data = pack("VvCC", $offset, $grbit, $cch, 0x1);} else {$data = pack("VvC", $offset, $grbit, $cch);}$data = $header.$data.$sheetname;$this->_append($data);}/*** Write Internal SUPBOOK record** @access private*/function _storeSupbookInternal(){$record = 0x01AE; // Record identifier$length = 0x0004; // Bytes to follow$header = pack("vv", $record, $length);$data = pack("vv", count($this->_worksheets), 0x0104);$data = $header . $data;$this->_append($data);}/*** Writes the Excel BIFF EXTERNSHEET record. These references are used by* formulas.** @param string $sheetname Worksheet name* @access private*/function _storeExternsheetBiff8(){$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];}$data = $header . $data;$this->_append($data);}/*** Write Excel BIFF STYLE records.** @access private*/function _storeStyle(){$record = 0x0293; // Record identifier$length = 0x0004; // Bytes to follow$ixfe = 0x8000; // Index to style XF$BuiltIn = 0x00; // Built-in style$iLevel = 0xff; // Outline style level$header = pack("vv", $record, $length);$data = pack("vCC", $ixfe, $BuiltIn, $iLevel);$data = $header . $data;$this->_append($data);}/*** Writes Excel FORMAT record for non "built-in" numerical formats.** @param string $format Custom format string* @param integer $ifmt Format index code* @access private*/function _storeNumFormat($format, $ifmt){$record = 0x041E; // Record identifierif ($this->_BIFF_version == 0x0600) {$length = 5 + strlen($format); // Number of bytes to follow$encoding = 0x0;} elseif ($this->_BIFF_version == 0x0500) {$length = 3 + strlen($format); // Number of bytes to follow}if ( $this->_BIFF_version == 0x0600 && function_exists('iconv') ) { // Encode format Stringif (mb_detect_encoding($format, 'auto') !== 'UTF-16LE'){$format = iconv(mb_detect_encoding($format, 'auto'),'UTF-16LE',$format);}$encoding = 1;$cch = function_exists('mb_strlen') ? mb_strlen($format, 'UTF-16LE') : (strlen($format) / 2);} else {$encoding = 0;$cch = strlen($format); // Length of format string}$length = strlen($format);if ($this->_BIFF_version == 0x0600) {$header = pack("vv", $record, 5 + $length);$data = pack("vvC", $ifmt, $cch, $encoding);} elseif ($this->_BIFF_version == 0x0500) {$header = pack("vv", $record, 3 + $length);$data = pack("vC", $ifmt, $cch);}$data = $header . $data . $format;$this->_append($data);}/*** Write DATEMODE record to indicate the date system in use (1904 or 1900).** @access private*/function _storeDatemode(){$record = 0x0022; // Record identifier$length = 0x0002; // Bytes to follow$f1904 = $this->_1904; // Flag for 1904 date system$header = pack("vv", $record, $length);$data = pack("v", $f1904);$data = $header . $data;$this->_append($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* @access private*/function _storeExterncount($cxals){$record = 0x0016; // Record identifier$length = 0x0002; // Number of bytes to follow$header = pack("vv", $record, $length);$data = pack("v", $cxals);$data = $header . $data;$this->_append($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* @access private*/function _storeExternsheet($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);$data = $header . $data . $sheetname;$this->_append($data);}/*** 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* @access private*/function _storeNameShort($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);$data = $header . $data;$this->_append($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* _storeNameShort() 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* @access private*/function _storeNameLong($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);$data = $header . $data;$this->_append($data);}/*** Stores the COUNTRY record for localization** @access private*/function _storeCountry(){$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);$data = $header . $data;$this->_append($data);}/*** Stores the PALETTE biff record.** @access private*/function _storePalette(){$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 dataforeach ($aref as $color) {foreach ($color as $byte) {$data .= pack("C",$byte);}}$header = pack("vvv", $record, $length, $ccv);$data = $header . $data;$this->_append($data);}/*** Calculate* 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).** @access private*/function _calculateSharedStringsSizes(){/* Iterate through the strings to calculate the CONTINUE block sizes.For simplicity we use the same size for the SST and CONTINUE records:8228 : Maximum Excel97 block size-4 : Length of block header-8 : Length of additional SST header information-8 : Arbitrary number to keep within _add_continue() limit = 8208*/$continue_limit = 8208;$block_length = 0;$written = 0;$this->_block_sizes = array();$continue = 0;foreach (array_keys($this->_str_table) as $string) {$string_length = strlen($string);$headerinfo = unpack("vlength/Cencoding", $string);$encoding = $headerinfo["encoding"];$split_string = 0;// Block length is the total length of the strings that will be// written out in a single SST or CONTINUE block.$block_length += $string_length;// We can write the string if it doesn't cross a CONTINUE boundaryif ($block_length < $continue_limit) {$written += $string_length;continue;}// Deal with the cases where the next string to be written will exceed// the CONTINUE boundary. If the string is very long it may need to be// written in more than one CONTINUE record.while ($block_length >= $continue_limit) {// We need to avoid the case where a string is continued in the first// n bytes that contain the string header information.$header_length = 3; // Min string + header size -1$space_remaining = $continue_limit - $written - $continue;/* TODO: Unicode data should only be split on char (2 byte)boundaries. Therefore, in some cases we need to reduce theamount of available*/$align = 0;// Only applies to Unicode stringsif ($encoding == 1) {// Min string + header size -1$header_length = 4;if ($space_remaining > $header_length) {// String contains 3 byte header => split on odd boundaryif (!$split_string && $space_remaining % 2 != 1) {$space_remaining--;$align = 1;}// Split section without header => split on even boundaryelse if ($split_string && $space_remaining % 2 == 1) {$space_remaining--;$align = 1;}$split_string = 1;}}if ($space_remaining > $header_length) {// Write as much as possible of the string in the current block$written += $space_remaining;// Reduce the current block length by the amount written$block_length -= $continue_limit - $continue - $align;// Store the max size for this block$this->_block_sizes[] = $continue_limit - $align;// If the current string was split then the next CONTINUE block// should have the string continue flag (grbit) set unless the// split string fits exactly into the remaining space.if ($block_length > 0) {$continue = 1;} else {$continue = 0;}} else {// Store the max size for this block$this->_block_sizes[] = $written + $continue;// Not enough space to start the string in the current block$block_length -= $continue_limit - $space_remaining - $continue;$continue = 0;}// If the string (or substr) is small enough we can write it in the// new CONTINUE block. Else, go through the loop again to write it in// one or more CONTINUE blocksif ($block_length < $continue_limit) {$written = $block_length;} else {$written = 0;}}}// Store the max size for the last block unless it is emptyif ($written + $continue) {$this->_block_sizes[] = $written + $continue;}/* Calculate the total length of the SST and associated CONTINUEs (if any).The SST record will have a length even if it contains no strings.This length is required to set the offsets in the BOUNDSHEET records sincethey must be written before the SST records*/$tmp_block_sizes = array();$tmp_block_sizes = $this->_block_sizes;$length = 12;if (!empty($tmp_block_sizes)) {$length += array_shift($tmp_block_sizes); // SST}while (!empty($tmp_block_sizes)) {$length += 4 + array_shift($tmp_block_sizes); // CONTINUEs}return $length;}/*** Write all of the workbooks strings into an indexed array.* See the comments in _calculate_shared_string_sizes() for more information.** 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.** @access private*/function _storeSharedStringsTable(){$record = 0x00fc; // Record identifier$length = 0x0008; // Number of bytes to follow$total = 0x0000;// Iterate through the strings to calculate the CONTINUE block sizes$continue_limit = 8208;$block_length = 0;$written = 0;$continue = 0;// sizes are upside down$tmp_block_sizes = $this->_block_sizes;// $tmp_block_sizes = array_reverse($this->_block_sizes);// The SST record is required even if it contains no strings. Thus we will// always have a length//if (!empty($tmp_block_sizes)) {$length = 8 + array_shift($tmp_block_sizes);}else {// No strings$length = 8;}// Write the SST block header information$header = pack("vv", $record, $length);$data = pack("VV", $this->_str_total, $this->_str_unique);$data = $header . $data;$this->_append($data);/* TODO: not good for performance */foreach (array_keys($this->_str_table) as $string) {$string_length = strlen($string);$headerinfo = unpack("vlength/Cencoding", $string);$encoding = $headerinfo["encoding"];$split_string = 0;// Block length is the total length of the strings that will be// written out in a single SST or CONTINUE block.//$block_length += $string_length;// We can write the string if it doesn't cross a CONTINUE boundaryif ($block_length < $continue_limit) {$this->_append($string);$written += $string_length;continue;}// Deal with the cases where the next string to be written will exceed// the CONTINUE boundary. If the string is very long it may need to be// written in more than one CONTINUE record.//while ($block_length >= $continue_limit) {// We need to avoid the case where a string is continued in the first// n bytes that contain the string header information.//$header_length = 3; // Min string + header size -1$space_remaining = $continue_limit - $written - $continue;// Unicode data should only be split on char (2 byte) boundaries.// Therefore, in some cases we need to reduce the amount of available// space by 1 byte to ensure the correct alignment.$align = 0;// Only applies to Unicode stringsif ($encoding == 1) {// Min string + header size -1$header_length = 4;if ($space_remaining > $header_length) {// String contains 3 byte header => split on odd boundaryif (!$split_string && $space_remaining % 2 != 1) {$space_remaining--;$align = 1;}// Split section without header => split on even boundaryelse if ($split_string && $space_remaining % 2 == 1) {$space_remaining--;$align = 1;}$split_string = 1;}}if ($space_remaining > $header_length) {// Write as much as possible of the string in the current block$tmp = substr($string, 0, $space_remaining);$this->_append($tmp);// The remainder will be written in the next block(s)$string = substr($string, $space_remaining);// Reduce the current block length by the amount written$block_length -= $continue_limit - $continue - $align;// If the current string was split then the next CONTINUE block// should have the string continue flag (grbit) set unless the// split string fits exactly into the remaining space.//if ($block_length > 0) {$continue = 1;} else {$continue = 0;}} else {// Not enough space to start the string in the current block$block_length -= $continue_limit - $space_remaining - $continue;$continue = 0;}// Write the CONTINUE block headerif (!empty($this->_block_sizes)) {$record = 0x003C;$length = array_shift($tmp_block_sizes);$header = pack('vv', $record, $length);if ($continue) {$header .= pack('C', $encoding);}$this->_append($header);}// If the string (or substr) is small enough we can write it in the// new CONTINUE block. Else, go through the loop again to write it in// one or more CONTINUE blocks//if ($block_length < $continue_limit) {$this->_append($string);$written = $block_length;} else {$written = 0;}}}}}