WebSVN – eFlore/Applications.cel – Diff – /trunk/jrest/lib/Spreadsheet/Excel/Writer/Workbook.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.
 *    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('Format.php');
+require_once 'Spreadsheet/Excel/Writer/Format.php';
+require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';
-require_once('OLEwriter.php');
+require_once 'Spreadsheet/Excel/Writer/Worksheet.php';
-require_once('BIFFwriter.php');
+require_once 'Spreadsheet/Excel/Writer/Parser.php';
-require_once('Worksheet.php');
+require_once 'OLE/PPS/Root.php';
-require_once('Parser.php');
+require_once 'OLE/PPS/File.php';
 /**
 * Class for generating Excel Spreadsheets
     * @var object Parser
     */
     var $_parser;
     /**
-    * Flag for 1904 date system
+    * Flag for 1904 date system (0 => base date is 1900, 1 => base date is 1904)
     * @var integer
     */
     var $_1904;
     * @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->_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->_sheetname        = 'Sheet';
-        $this->_tmp_format       = new Spreadsheet_Excel_Writer_Format();
+        $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->_palette          = array();
+        $this->_string_sizeinfo  = 3;
         // Add the default format for hyperlinks
-        $this->_url_format =& $this->addFormat(array('color' => 'blue', 'underline' => 1));
+        $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
-    * @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;
+            return $this->raiseError($res->getMessage());
-        }
         $this->_storeWorkbook();
+        $this->_fileclosed = 1;
-        $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()
     */
     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);
+        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 too
+            for ($i = 0; $i < $total_worksheets; $i++) {
+                $this->_worksheets[$i]->_BIFF_version = $version;
+            }
+            $total_formats = count($this->_formats);
+            // change version for all formats too
+            for ($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 &Spreadsheet_Excel_Writer_Worksheet reference to a worksheet object
+    * @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 == '') {
+        if ($name == '') {
             $name = $sheetname.($index+1);
-        }
-        // Check that sheetname is <= 31 chars (Excel limit).
-        if(strlen($name) > 31) {
-            $this->raiseError("Sheetname $name must be <= 31 chars");
+        }
-        // Check that the worksheet name doesn't already exist: a fatal Excel error.
+        // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
-        for($i=0; $i < count($this->_worksheets); $i++)
+        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($name == $this->_worksheets[$i]->getName()) {
+            if ($this->_worksheets[$i]->getName() == $name) {
-                $this->raiseError("Worksheet '$name' already exists");
+                return $this->raiseError("Worksheet '$name' already exists");
+            }
+        }
-        $worksheet = new Spreadsheet_Excel_Writer_Worksheet($name,$index,$this->_activesheet,
+        $worksheet = new Spreadsheet_Excel_Writer_Worksheet($this->_BIFF_version,
+                                   $name, $index,
+                                   $this->_activesheet, $this->_firstsheet,
+                                   $this->_str_total, $this->_str_unique,
-                                   $this->_firstsheet,$this->_url_format,
+                                   $this->_str_table, $this->_url_format,
-                                   $this->_parser);
+                                   $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 parser
-        return($worksheet);
+        return $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->_xf_index,$properties);
+        $format = new Spreadsheet_Excel_Writer_Format($this->_BIFF_version, $this->_xf_index, $properties);
         $this->_xf_index += 1;
         $this->_formats[] = &$format;
-        return($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)
+    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 range
         if ($index < 8 or $index > 64) {
             // TODO: assign real error codes
-            $this->raiseError("Color index $index outside range: 8 <= index <= 64",0,PEAR_ERROR_DIE);
+            return $this->raiseError("Color index $index outside range: 8 <= index <= 64");
         }
         // Check that the colour components are in the right range
-        if ( ($red   < 0 or $red   > 255) or
+        if (($red   < 0 or $red   > 255) ||
-             ($green < 0 or $green > 255) or
+            ($green < 0 or $green > 255) ||
-             ($blue  < 0 or $blue  > 255) )
+            ($blue  < 0 or $blue  > 255))
         {
-            $this->raiseError("Color component outside range: 0 <= color <= 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
                            array(0x99, 0x33, 0x66, 0x00),   // 61
                            array(0x33, 0x33, 0x99, 0x00),   // 62
                            array(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)
+        if ($this->_activesheet == 0) {
-        {
             $this->_worksheets[0]->selected = 1;
+        }
         // Calculate the number of selected worksheet tabs and call the finalization
         // methods for each worksheet
-        for($i=0; $i < count($this->_worksheets); $i++)
+        $total_worksheets = count($this->_worksheets);
-        {
+        for ($i = 0; $i < $total_worksheets; $i++) {
-            if($this->_worksheets[$i]->selected) {
+            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->_storeExterns();    // For print area and repeat rows
+        }
         $this->_storeNames();      // For print area and repeat rows
+        if ($this->_BIFF_version == 0x0500) {
-        $this->_storeWindow1();
+            $this->_storeWindow1();
+        }
-        $this->_store1904();
+        $this->_storeDatemode();
         $this->_storeAllFonts();
         $this->_storeAllNumFormats();
         $this->_storeAllXfs();
         $this->_storeAllStyles();
         $this->_storePalette();
         $this->_calcSheetOffsets();
         // Add BOUNDSHEET records
-        for($i=0; $i < count($this->_worksheets); $i++) {
+        for ($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 records
+            in case of external references for BIFF8 */
+            //$this->_storeExternsheetBiff8();
+            $this->_storeSharedStringsTable();
+        }
         // End Workbook globals
         $this->_storeEof();
         // Store the workbook in an OLE container
-        $this->_storeOLEFile();
+        $res = $this->_storeOLEFile();
+        if ($this->isError($res)) {
+            return $this->raiseError($res->getMessage());
+        }
+        return true;
+    }
     /**
-    * Store the workbook in an OLE container if the total size of the workbook data
+    * Store the workbook in an OLE container
-    * is less than ~ 7MB.
+    *
     * @access private
+    * @return mixed true on success. PEAR_Error on failure
     */
     function _storeOLEFile()
+    {
+        if($this->_BIFF_version == 0x0600) {
-        $OLE  = new Spreadsheet_Excel_Writer_OLEwriter($this->_filename);
+            $OLE = new OLE_PPS_File(OLE::Asc2Ucs('Workbook'));
+        } else {
-        $this->_tmp_filename = $OLE->_tmp_filename;
+            $OLE = new OLE_PPS_File(OLE::Asc2Ucs('Book'));
+        }
-        // Write Worksheet data if data <~ 7MB
+        if ($this->_tmp_dir != '') {
-        if ($OLE->setSize($this->_biffsize))
+            $OLE->setTempDir($this->_tmp_dir);
-        {
+        }
-            $OLE->writeHeader();
+        $res = $OLE->init();
-            $OLE->write($this->_data);
+        if ($this->isError($res)) {
-            foreach($this->_worksheets as $sheet)
+            return $this->raiseError("OLE Error: ".$res->getMessage());
-            {
+        }
+        $OLE->append($this->_data);
+        $total_worksheets = count($this->_worksheets);
-                while ($tmp = $sheet->getData()) {
+        for ($i = 0; $i < $total_worksheets; $i++) {
-                    $OLE->write($tmp);
+            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());
+        }
-        $OLE->close();
+        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
-        $BOF     = 11;
+        } else {
+            $boundsheet_length = 11;
+        }
-        $EOF     = 4;
+        $EOF               = 4;
-        $offset  = $this->_datasize;
+        $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 records
-        for($i=0; $i < count($this->_worksheets); $i++) {
+        for ($i = 0; $i < $total_worksheets; $i++) {
-            $offset += $BOF + strlen($this->_worksheets[$i]->name);
+            $offset += $boundsheet_length + strlen($this->_worksheets[$i]->name);
+        }
         $offset += $EOF;
-        for($i=0; $i < count($this->_worksheets); $i++) {
+        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++){
+        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
+        $fonts[$key] = 0;             // Index of the default font
+        $total_formats = count($this->_formats);
-        for($i=0; $i < count($this->_formats); $i++) {
+        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 {
+            } 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
     */
+    {
         // 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 < count($this->_formats); $i++)
+        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("/^0+\d/", $num_format)) {
-            {
-                if (preg_match("/^\d+$/",$num_format)) { // built-in format
+                if (preg_match("/^\d+$/", $num_format)) { // built-in format
                     continue;
+                }
+            }
             if (isset($hash_num_formats[$num_format])) {
                 // FORMAT has already been used
                 $this->_formats[$i]->_num_format = $hash_num_formats[$num_format];
-            }
-            else{
+            } 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
     */
+    {
         // _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++) {
+        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 < count($this->_formats); $i++) {
+        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 worksheet
         foreach ($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);
-        foreach ($this->_worksheets as $worksheet) {
+        for ($i = 0; $i < $total_worksheets; $i++) {
             // Write a Name record if the print area has been defined
-            if (isset($worksheet->print_rowmin))
+            if (isset($this->_worksheets[$i]->print_rowmin)) {
-            {
                 $this->_storeNameShort(
-                    $worksheet->index,
+                    $this->_worksheets[$i]->index,
 x06, // NAME type
-                    $worksheet->print_rowmin,
+                    $this->_worksheets[$i]->print_rowmin,
-                    $worksheet->print_rowmax,
+                    $this->_worksheets[$i]->print_rowmax,
-                    $worksheet->print_colmin,
+                    $this->_worksheets[$i]->print_colmin,
-                    $worksheet->print_colmax
+                    $this->_worksheets[$i]->print_colmax
                     );
+            }
+        }
         // Create the print title NAME records
-        foreach ($this->_worksheets as $worksheet)
+        $total_worksheets = count($this->_worksheets);
-        {
+        for ($i = 0; $i < $total_worksheets; $i++) {
-            $rowmin = $worksheet->title_rowmin;
+            $rowmin = $this->_worksheets[$i]->title_rowmin;
-            $rowmax = $worksheet->title_rowmax;
+            $rowmax = $this->_worksheets[$i]->title_rowmax;
-            $colmin = $worksheet->title_colmin;
+            $colmin = $this->_worksheets[$i]->title_colmin;
-            $colmax = $worksheet->title_colmax;
+            $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) and isset($colmin)) {
+            if (isset($rowmin) && isset($colmin)) {
                 // Row and column titles have been defined.
                 // Row title has been defined.
                 $this->_storeNameLong(
-                    $worksheet->index,
+                    $this->_worksheets[$i]->index,
 x07, // NAME type
                     $rowmin,
                     $rowmax,
                     $colmin,
                     $colmax
                     );
-            }
-            elseif (isset($rowmin)) {
+            } elseif (isset($rowmin)) {
                 // Row title has been defined.
                 $this->_storeNameShort(
-                    $worksheet->index,
+                    $this->_worksheets[$i]->index,
 x07, // NAME type
                     $rowmin,
                     $rowmax,
 x00,
 xff
                     );
-            }
-            elseif (isset($colmin)) {
+            } elseif (isset($colmin)) {
                 // Column title has been defined.
                 $this->_storeNameShort(
-                    $worksheet->index,
+                    $this->_worksheets[$i]->index,
 x07, // NAME type
 x0000,
 x3fff,
                     $colmin,
                     $colmax
                     );
-            }
-            else {
+            } 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);
+        $this->_append($header . $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);
-        $this->_append($header.$data);
+        $this->_append($header . $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 identifier
+        if ($this->_BIFF_version == 0x0600) {
-        $length    = 0x07 + strlen($sheetname); // Number of bytes to follow
+            $length    = 0x08 + strlen($sheetname); // Number of bytes to follow
+        } else {
+            $length = 0x07 + strlen($sheetname); // Number of bytes to follow
+        }
-        $grbit     = 0x0000;                    // Sheet identifier
+        $grbit     = 0x0000;                    // Visibility and sheet type
+        if ($this->_BIFF_version == 0x0600) {
+            $cch       = mb_strlen($sheetname,'UTF-16LE'); // Length of sheet name
+        } else {
-        $cch       = strlen($sheetname);        // Length of sheet name
+            $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      = pack("VvC", $offset, $grbit, $cch);
+        }
         $this->_append($header.$data.$sheetname);
+    }
+    /**
+    * 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);
+        $this->_append($header . $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];
+        }
+        $this->_append($header . $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);
-        $this->_append($header.$data);
+        $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
     * @access private
     */
-    function _storeNumFormat($format,$ifmt)
+    function _storeNumFormat($format, $ifmt)
+    {
         $record    = 0x041E;                      // Record identifier
+        if ($this->_BIFF_version == 0x0600) {
+            $length    = 5 + strlen($format);      // Number of bytes to follow
+            $encoding = 0x0;
+        } elseif ($this->_BIFF_version == 0x0500) {
-        $length    = 0x03 + strlen($format);      // Number of bytes to follow
+            $length    = 3 + strlen($format);      // Number of bytes to follow
+        }
+        if ( $this->_BIFF_version == 0x0600 && function_exists('iconv') ) {     // Encode format String
+            if (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
+            $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, $length);
+            $header    = pack("vv", $record, 3 + $length);
-        $data      = pack("vC", $ifmt, $cch);
+            $data      = pack("vC", $ifmt, $cch);
+        }
-        $this->_append($header.$data.$format);
+        $this->_append($header . $data . $format);
+    }
     /**
-    * Write Excel 1904 record to indicate the date system in use.
+    * Write DATEMODE record to indicate the date system in use (1904 or 1900).
+    *
     * @access private
     */
-    function _store1904()
+    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);
-        $this->_append($header.$data);
+        $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.
     */
     function _storeExterncount($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);
+        $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.
+    *
     */
     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);
-        $this->_append($header.$data.$sheetname);
+        $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 $rowmax End row
     * @param integer $colmin Start colum
     * @param integer $colmax End column
     * @access private
     */
-    function _storeNameShort($index,$type,$rowmin,$rowmax,$colmin,$colmax)
+    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
         $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", $index);
         $data              .= pack("v", $rowmin);
         $data              .= pack("v", $rowmax);
         $data              .= pack("C", $colmin);
         $data              .= pack("C", $colmax);
-        $this->_append($header.$data);
+        $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
     * _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 $rowmax End row
     * @param integer $colmin Start colum
     * @param integer $colmax End column
     * @access private
     */
-    function _storeNameLong($index,$type,$rowmin,$rowmax,$colmin,$colmax)
+    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
         $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", $rowmax);
         $data              .= pack("C", 0x00);
         $data              .= pack("C", 0xff);
         // End of data
         $data              .= pack("C", 0x10);
-        $this->_append($header.$data);
+        $this->_append($header . $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);
+        $this->_append($header . $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 data
-        foreach($aref as $color)
+        foreach ($aref as $color) {
-        {
-            foreach($color as $byte) {
+            foreach ($color as $byte) {
                 $data .= pack("C",$byte);
+            }
+        }
         $header = pack("vvv",  $record, $length, $ccv);
-        $this->_append($header.$data);
+        $this->_append($header . $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:
+: 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 boundary
+            if ($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 the
+                amount of available
+                */
+                $align = 0;
+                // Only applies to Unicode strings
+                if ($encoding == 1) {
+                    // Min string + header size -1
+                    $header_length = 4;
+                    if ($space_remaining > $header_length) {
+                        // String contains 3 byte header => split on odd boundary
+                        if (!$split_string && $space_remaining % 2 != 1) {
+                            $space_remaining--;
+                            $align = 1;
+                        }
+                        // Split section without header => split on even boundary
+                        else 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 blocks
+                if ($block_length < $continue_limit) {
+                    $written = $block_length;
+                } else {
+                    $written = 0;
+                }
+            }
+        }
+        // Store the max size for the last block unless it is empty
+        if ($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 since
+         they 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);
+        $this->_append($header . $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 boundary
+            if ($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 strings
+                if ($encoding == 1) {
+                    // Min string + header size -1
+                    $header_length = 4;
+                    if ($space_remaining > $header_length) {
+                        // String contains 3 byte header => split on odd boundary
+                        if (!$split_string && $space_remaining % 2 != 1) {
+                            $space_remaining--;
+                            $align = 1;
+                        }
+                        // Split section without header => split on even boundary
+                        else 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 header
+                if (!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;
+                }
+            }
+        }
+    }
+}
-?>

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/lib/Spreadsheet/Excel/Writer/Workbook.php – Rev 1603 → 1604