PHP PHPExcel_Writer_Excel5_Parser类代码示例

 /**
  * Store the DATAVALIDATIONS and DATAVALIDATION records.
  */
 private function _writeDataValidity()
 {
     // Datavalidation collection
     $dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
     // Write data validations?
     if (!empty($dataValidationCollection)) {
         // DATAVALIDATIONS record
         $record = 0x1b2;
         // Record identifier
         $length = 0x12;
         // Bytes to follow
         $grbit = 0x0;
         // Prompt box at cell, no cached validity data at DV records
         $horPos = 0x0;
         // Horizontal position of prompt box, if fixed position
         $verPos = 0x0;
         // Vertical position of prompt box, if fixed position
         $objId = 0xffffffff;
         // Object identifier of drop down arrow object, or -1 if not visible
         $header = pack('vv', $record, $length);
         $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection));
         $this->_append($header . $data);
         // DATAVALIDATION records
         $record = 0x1be;
         // Record identifier
         foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
             // initialize record data
             $data = '';
             // options
             $options = 0x0;
             // data type
             $type = $dataValidation->getType();
             switch ($type) {
                 case PHPExcel_Cell_DataValidation::TYPE_NONE:
                     $type = 0x0;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_WHOLE:
                     $type = 0x1;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_DECIMAL:
                     $type = 0x2;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_LIST:
                     $type = 0x3;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_DATE:
                     $type = 0x4;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_TIME:
                     $type = 0x5;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_TEXTLENGTH:
                     $type = 0x6;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_CUSTOM:
                     $type = 0x7;
                     break;
             }
             $options |= $type << 0;
             // error style
             $errorStyle = $dataValidation->getType();
             switch ($errorStyle) {
                 case PHPExcel_Cell_DataValidation::STYLE_STOP:
                     $errorStyle = 0x0;
                     break;
                 case PHPExcel_Cell_DataValidation::STYLE_WARNING:
                     $errorStyle = 0x1;
                     break;
                 case PHPExcel_Cell_DataValidation::STYLE_INFORMATION:
                     $errorStyle = 0x2;
                     break;
             }
             $options |= $errorStyle << 4;
             // explicit formula?
             if ($type == 0x3 && preg_match('/^\\".*\\"$/', $dataValidation->getFormula1())) {
                 $options |= 0x1 << 7;
             }
             // empty cells allowed
             $options |= $dataValidation->getAllowBlank() << 8;
             // show drop down
             $options |= !$dataValidation->getShowDropDown() << 9;
             // show input message
             $options |= $dataValidation->getShowInputMessage() << 18;
             // show error message
             $options |= $dataValidation->getShowErrorMessage() << 19;
             // condition operator
             $operator = $dataValidation->getOperator();
             switch ($operator) {
                 case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN:
                     $operator = 0x0;
                     break;
                 case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN:
                     $operator = 0x1;
                     break;
                 case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL:
                     $operator = 0x2;
                     break;
//.........这里部分代码省略.........

 /**
  * Writes all the DEFINEDNAME records (BIFF8).
  * So far this is only used for repeating rows/columns (print titles) and print areas
  */
 private function writeAllDefinedNamesBiff8()
 {
     $chunk = '';
     // Named ranges
     if (count($this->phpExcel->getNamedRanges()) > 0) {
         // Loop named ranges
         $namedRanges = $this->phpExcel->getNamedRanges();
         foreach ($namedRanges as $namedRange) {
             // Create absolute coordinate
             $range = PHPExcel_Cell::splitRange($namedRange->getRange());
             for ($i = 0; $i < count($range); $i++) {
                 $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]);
                 if (isset($range[$i][1])) {
                     $range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]);
                 }
             }
             $range = PHPExcel_Cell::buildRange($range);
             // e.g. Sheet1!$A$1:$B$2
             // parse formula
             try {
                 $error = $this->parser->parse($range);
                 $formulaData = $this->parser->toReversePolish();
                 // make sure tRef3d is of type tRef3dR (0x3A)
                 if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) {
                     $formulaData = ":" . substr($formulaData, 1);
                 }
                 if ($namedRange->getLocalOnly()) {
                     // local scope
                     $scope = $this->phpExcel->getIndex($namedRange->getScope()) + 1;
                 } else {
                     // global scope
                     $scope = 0;
                 }
                 $chunk .= $this->writeData($this->writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
             } catch (PHPExcel_Exception $e) {
                 // do nothing
             }
         }
     }
     // total number of sheets
     $total_worksheets = $this->phpExcel->getSheetCount();
     // write the print titles (repeating rows, columns), if any
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup();
         // simultaneous repeatColumns repeatRows
         if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
             $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
             $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
             $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
             $repeat = $sheetSetup->getRowsToRepeatAtTop();
             $rowmin = $repeat[0] - 1;
             $rowmax = $repeat[1] - 1;
             // construct formula data manually
             $formulaData = pack('Cv', 0x29, 0x17);
             // tMemFunc
             $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);
             // tArea3d
             $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);
             // tArea3d
             $formulaData .= pack('C', 0x10);
             // tList
             // store the DEFINEDNAME record
             $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
             // (exclusive) either repeatColumns or repeatRows
         } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
             // Columns to repeat
             if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
                 $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
                 $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
                 $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
             } else {
                 $colmin = 0;
                 $colmax = 255;
             }
             // Rows to repeat
             if ($sheetSetup->isRowsToRepeatAtTopSet()) {
                 $repeat = $sheetSetup->getRowsToRepeatAtTop();
                 $rowmin = $repeat[0] - 1;
                 $rowmax = $repeat[1] - 1;
             } else {
                 $rowmin = 0;
                 $rowmax = 65535;
             }
             // construct formula data manually because parser does not recognize absolute 3d cell references
             $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
             // store the DEFINEDNAME record
             $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
         }
     }
     // write the print areas, if any
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup();
         if ($sheetSetup->isPrintAreaSet()) {
             // Print area, e.g. A3:J6,H1:X20
             $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
             $countPrintArea = count($printArea);
//.........这里部分代码省略.........

 /**
  * Write a formula to the specified row and column (zero indexed).
  * The textual representation of the formula is passed to the parser in
  * Parser.php which returns a packed binary string.
  *
  * Returns  0 : normal termination
  *		 -1 : formula errors (bad formula)
  *		 -2 : row or column out of range
  *
  * @param integer $row	 Zero indexed row
  * @param integer $col	 Zero indexed column
  * @param string  $formula The formula text string
  * @param mixed   $format  The optional XF format
  * @return integer
  */
 private function _writeFormula($row, $col, $formula, $xfIndex)
 {
     $record = 0x6;
     // Record identifier
     // Excel normally stores the last calculated value of the formula in $num.
     // Clearly we are not in a position to calculate this a priori. Instead
     // we set $num to zero and set the option flags in $grbit to ensure
     // automatic calculation of the formula when the file is opened.
     //
     $num = 0x0;
     // Current value of formula
     $grbit = 0x3;
     // Option flags
     $unknown = 0x0;
     // Must be zero
     // Strip the '=' or '@' sign at the beginning of the formula string
     if (preg_match("/^=/", $formula)) {
         $formula = preg_replace("/(^=)/", "", $formula);
     } elseif (preg_match("/^@/", $formula)) {
         $formula = preg_replace("/(^@)/", "", $formula);
     } else {
         // Error handling
         $this->_writeString($row, $col, 'Unrecognised character for formula');
         return -1;
     }
     // Parse the formula using the parser in Parser.php
     try {
         $error = $this->_parser->parse($formula);
         $formula = $this->_parser->toReversePolish();
         $formlen = strlen($formula);
         // Length of the binary string
         $length = 0x16 + $formlen;
         // Length of the record data
         $header = pack("vv", $record, $length);
         $data = pack("vvvdvVv", $row, $col, $xfIndex, $num, $grbit, $unknown, $formlen);
         $this->_append($header . $data . $formula);
         return 0;
     } catch (Exception $e) {
         // do nothing
     }
 }

 /**
  * Class constructor
  *
  * @param PHPExcel $phpExcel The Workbook
  * @param int $BIFF_verions BIFF version
  * @param int  $str_total		Total number of strings
  * @param int  $str_unique		Total number of unique strings
  * @param array  $str_table
  * @param mixed   $parser	  The formula parser created for the Workbook
  */
 public function __construct(PHPExcel $phpExcel = null, $BIFF_version = 0x600, &$str_total, &$str_unique, &$str_table, $parser, $tempDir = '')
 {
     // It needs to call its parent's constructor explicitly
     parent::__construct();
     $this->_parser = $parser;
     $this->_biffsize = 0;
     $this->_palette = array();
     $this->_codepage = 0x4e4;
     // FIXME: should change for BIFF8
     $this->_country_code = -1;
     $this->_str_total =& $str_total;
     $this->_str_unique =& $str_unique;
     $this->_str_table =& $str_table;
     $this->_setPaletteXl97();
     $this->_tmp_dir = $tempDir;
     $this->_phpExcel = $phpExcel;
     if ($BIFF_version == 0x600) {
         $this->_BIFF_version = 0x600;
         // change BIFFwriter limit for CONTINUE records
         $this->_limit = 8224;
         $this->_codepage = 0x4b0;
     }
     // Add empty sheets
     $countSheets = count($phpExcel->getAllSheets());
     for ($i = 0; $i < $countSheets; ++$i) {
         $phpSheet = $phpExcel->getSheet($i);
         $this->_parser->setExtSheet($phpSheet->getTitle(), $i);
         // Register worksheet name with parser
         // for BIFF8
         if ($this->_BIFF_version == 0x600) {
             $supbook_index = 0x0;
             $ref = pack('vvv', $supbook_index, $i, $i);
             $this->_parser->_references[] = $ref;
             // Register reference with parser
         }
     }
 }

 /**
  * Write a formula to the specified row and column (zero indexed).
  * The textual representation of the formula is passed to the parser in
  * Parser.php which returns a packed binary string.
  *
  * Returns  0 : normal termination
  *		 -1 : formula errors (bad formula)
  *		 -2 : row or column out of range
  *
  * @param integer $row	 Zero indexed row
  * @param integer $col	 Zero indexed column
  * @param string  $formula The formula text string
  * @param mixed   $format  The optional XF format
  * @param mixed   $calculatedValue  Calculated value
  * @return integer
  */
 private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
 {
     $record = 0x6;
     // Record identifier
     // Initialize possible additional value for STRING record that should be written after the FORMULA record?
     $stringValue = null;
     // calculated value
     if (isset($calculatedValue)) {
         // Since we can't yet get the data type of the calculated value,
         // we use best effort to determine data type
         if (is_bool($calculatedValue)) {
             // Boolean value
             $num = pack('CCCvCv', 0x1, 0x0, (int) $calculatedValue, 0x0, 0x0, 0xffff);
         } elseif (is_int($calculatedValue) || is_float($calculatedValue)) {
             // Numeric value
             $num = pack('d', $calculatedValue);
         } elseif (is_string($calculatedValue)) {
             if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) {
                 // Error value
                 $num = pack('CCCvCv', 0x2, 0x0, $this->_mapErrorCode($calculatedValue), 0x0, 0x0, 0xffff);
             } elseif ($calculatedValue === '' && $this->_BIFF_version == 0x600) {
                 // Empty string (and BIFF8)
                 $num = pack('CCCvCv', 0x3, 0x0, 0x0, 0x0, 0x0, 0xffff);
             } else {
                 // Non-empty string value (or empty string BIFF5)
                 $stringValue = $calculatedValue;
                 $num = pack('CCCvCv', 0x0, 0x0, 0x0, 0x0, 0x0, 0xffff);
             }
         } else {
             // We are really not supposed to reach here
             $num = pack('d', 0x0);
         }
     } else {
         $num = pack('d', 0x0);
     }
     $grbit = 0x3;
     // Option flags
     $unknown = 0x0;
     // Must be zero
     // Strip the '=' or '@' sign at the beginning of the formula string
     if (preg_match("/^=/", $formula)) {
         $formula = preg_replace("/(^=)/", "", $formula);
     } elseif (preg_match("/^@/", $formula)) {
         $formula = preg_replace("/(^@)/", "", $formula);
     } else {
         // Error handling
         $this->_writeString($row, $col, 'Unrecognised character for formula');
         return -1;
     }
     // Parse the formula using the parser in Parser.php
     try {
         $error = $this->_parser->parse($formula);
         $formula = $this->_parser->toReversePolish();
         $formlen = strlen($formula);
         // Length of the binary string
         $length = 0x16 + $formlen;
         // Length of the record data
         $header = pack("vv", $record, $length);
         $data = pack("vvv", $row, $col, $xfIndex) . $num . pack("vVv", $grbit, $unknown, $formlen);
         $this->_append($header . $data . $formula);
         // Append also a STRING record if necessary
         if ($stringValue !== null) {
             $this->_writeStringRecord($stringValue);
         }
         return 0;
     } catch (Exception $e) {
         // do nothing
     }
 }