Example #1
0
    /**
     * Create array from a range of cells
     *
     * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
     * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
     * @param boolean $calculateFormulas Should formulas be calculated?
     * @param boolean $formatData Should formatting be applied to cell values?
     * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
     *                               True - Return rows and columns indexed by their actual row and column IDs
     * @return array
     */
	public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
        // Returnvalue
        $returnValue = array();
        //    Identify the range that we need to extract from the worksheet
        list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
        $minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] -1);
        $minRow = $rangeStart[1];
        $maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] -1);
        $maxRow = $rangeEnd[1];

        $maxCol++;
        // Loop through rows
        $r = -1;
        for ($row = $minRow; $row <= $maxRow; ++$row) {
            $rRef = ($returnCellRef) ? $row : ++$r;
            $c = -1;
            // Loop through columns in the current row
            for ($col = $minCol; $col != $maxCol; ++$col) {
                $cRef = ($returnCellRef) ? $col : ++$c;
                //    Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
                //        so we test and retrieve directly against _cellCollection
                if ($this->_cellCollection->isDataSet($col.$row)) {
                    // Cell exists
                    $cell = $this->_cellCollection->getCacheData($col.$row);
                    if ($cell->getValue() !== null) {
                        if ($cell->getValue() instanceof PHPExcel_RichText) {
                            $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
                        } else {
                            if ($calculateFormulas) {
                                $returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
                            } else {
                                $returnValue[$rRef][$cRef] = $cell->getValue();
                            }
                        }

                        if ($formatData) {
                            $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
                            $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString(
                            	$returnValue[$rRef][$cRef],
								($style && $style->getNumberFormat()) ?
									$style->getNumberFormat()->getFormatCode() :
									PHPExcel_Style_NumberFormat::FORMAT_GENERAL
                            );
                        }
                    } else {
                        // Cell holds a NULL
                        $returnValue[$rRef][$cRef] = $nullValue;
                    }
                } else {
                    // Cell doesn't exist
                    $returnValue[$rRef][$cRef] = $nullValue;
                }
            }
        }

        // Return
        return $returnValue;
    }
Example #2
0
 /**
  * Create array from worksheet
  *
  * @param	mixed	$nullValue				Value returned in the array entry if a cell doesn't exist
  * @param	boolean	$calculateFormulas		Should formulas be calculated?
  * @param	boolean	$formatData				Should formatting be applied to cell values?
  * @param	boolean	$returnCellRef			False - Return a simple array of rows and columns indexed by number counting from zero
  *											True - Return rows and columns indexed by their actual row and column IDs
  * @return array
  */
 public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
 {
     // Returnvalue
     $returnValue = array();
     // Garbage collect...
     $this->garbageCollect();
     // Loop through rows
     $r = -1;
     $rowIterator = $this->getRowIterator();
     foreach ($rowIterator as $row) {
         ++$r;
         $cellIterator = $row->getCellIterator();
         $cellIterator->setIterateOnlyExistingCells(true);
         // Loop through each cell in the current row
         $c = -1;
         foreach ($cellIterator as $cell) {
             ++$c;
             $rRef = $returnCellRef ? $cell->getRow() : $r;
             $cRef = $returnCellRef ? $cell->getColumn() : $c;
             if (!is_null($cell)) {
                 // Cell exists?
                 if ($cell->getValue() instanceof PHPExcel_RichText) {
                     $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
                 } else {
                     if ($calculateFormulas) {
                         $returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
                     } else {
                         $returnValue[$rRef][$cRef] = $cell->getValue();
                     }
                 }
                 if ($formatData) {
                     $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
                     $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
                 }
             } else {
                 $returnValue[$rRef][$cRef] = $nullValue;
             }
         }
     }
     // Return
     return $returnValue;
 }
Example #3
0
 /**
  * Create array from worksheet
  *
  * @param mixed $nullValue Value treated as "null"
  * @param boolean $calculateFormulas Should formulas be calculated?
  * @return array
  */
 public function toArray($nullValue = null, $calculateFormulas = true)
 {
     // Returnvalue
     $returnValue = array();
     // Garbage collect...
     $this->garbageCollect();
     // Get worksheet dimension
     $dimension = explode(':', $this->calculateWorksheetDimension());
     $dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
     $dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
     $dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
     $dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
     // Loop through cells
     for ($row = $dimension[0][1]; $row <= $dimension[1][1]; ++$row) {
         for ($column = $dimension[0][0]; $column <= $dimension[1][0]; ++$column) {
             // Cell exists?
             if ($this->cellExistsByColumnAndRow($column, $row)) {
                 $cell = $this->getCellByColumnAndRow($column, $row);
                 if ($cell->getValue() instanceof PHPExcel_RichText) {
                     $returnValue[$row][$column] = $cell->getValue()->getPlainText();
                 } else {
                     if ($calculateFormulas) {
                         $returnValue[$row][$column] = $cell->getCalculatedValue();
                     } else {
                         $returnValue[$row][$column] = $cell->getValue();
                     }
                 }
                 $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
                 $returnValue[$row][$column] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$row][$column], $style->getNumberFormat()->getFormatCode());
             } else {
                 $returnValue[$row][$column] = $nullValue;
             }
         }
     }
     // Return
     return $returnValue;
 }
Example #4
0
                    $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
                    break;
                case 0x2:
                    $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLE);
                    break;
                case 0x21:
                    $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING);
                    break;
                case 0x22:
                    $objFont->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING);
                    break;
            }
            // offset: 11; size: 1; font family
            // offset: 12; size: 1; character set
            // offset: 13; size: 1; not used
            // offset: 14; size: var; font name
            if ($this->_version == self::XLS_BIFF8) {
                $string = self::_readUnicodeStringShort(substr($recordData, 14));
            } else {
                $string = $this->_readByteStringShort(substr($recordData, 14));
            }
            $objFont->setName($string['value']);
            $this->_objFonts[] = $objFont;
        }
    }
    /**
	 * FORMAT
	 *
	 * This record contains information about a number format.
	 * All FORMAT records occur together in a sequential list.
	 *
	 * In BIFF2-BIFF4 other records referencing a FORMAT record
	 * contain a zero-based index into this list. From BIFF5 on
	 * the FORMAT record contains the index itself that will be
	 * used by other records.
	 *
	 * --	"OpenOffice.org's Documentation of the Microsoft
	 * 		Excel File Format"
	 */
    private function _readFormat()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        if (!$this->_readDataOnly) {
            $indexCode = self::_GetInt2d($recordData, 0);
            if ($this->_version == self::XLS_BIFF8) {
                $string = self::_readUnicodeStringLong(substr($recordData, 2));
            } else {
                // BIFF7
                $string = $this->_readByteStringShort(substr($recordData, 2));
            }
            $formatString = $string['value'];
            $this->_formats[$indexCode] = $formatString;
        }
    }
    /**
	 * XF - Extended Format
	 *
	 * This record contains formatting information for cells, rows, columns or styles.
	 * According to http://support.microsoft.com/kb/147732 there are always at least 15 cell style XF
	 * and 1 cell XF.
	 * Inspection of Excel files generated by MS Office Excel shows that XF records 0-14 are cell style XF
	 * and XF record 15 is a cell XF
	 * We only read the first cell style XF and skip the remaining cell style XF records
	 * We read all cell XF records.
	 *
	 * --	"OpenOffice.org's Documentation of the Microsoft
	 * 		Excel File Format"
	 */
    private function _readXf()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        $objStyle = new PHPExcel_Style();
        if (!$this->_readDataOnly) {
            // offset:  0; size: 2; Index to FONT record
            if (self::_GetInt2d($recordData, 0) < 4) {
                $fontIndex = self::_GetInt2d($recordData, 0);
            } else {
                // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
                // check the OpenOffice documentation of the FONT record
                $fontIndex = self::_GetInt2d($recordData, 0) - 1;
            }
            $objStyle->setFont($this->_objFonts[$fontIndex]);
            // offset:  2; size: 2; Index to FORMAT record
            $numberFormatIndex = self::_GetInt2d($recordData, 2);
            if (isset($this->_formats[$numberFormatIndex])) {
                // then we have user-defined format code
                $numberformat = array('code' => $this->_formats[$numberFormatIndex]);
            } elseif (($code = PHPExcel_Style_NumberFormat::builtInFormatCode($numberFormatIndex)) !== '') {
                // then we have built-in format code
                $numberformat = array('code' => $code);
            } else {
                // we set the general format code
                $numberformat = array('code' => 'General');
            }
            $objStyle->getNumberFormat()->setFormatCode($numberformat['code']);
            // offset:  4; size: 2; XF type, cell protection, and parent style XF
            // bit 2-0; mask 0x0007; XF_TYPE_PROT
            $xfTypeProt = self::_GetInt2d($recordData, 4);
            // bit 0; mask 0x01; 1 = cell is locked
            $isLocked = (0x1 & $xfTypeProt) >> 0;
            $objStyle->getProtection()->setLocked($isLocked ? PHPExcel_Style_Protection::PROTECTION_INHERIT : PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
            // bit 1; mask 0x02; 1 = Formula is hidden
            $isHidden = (0x2 & $xfTypeProt) >> 1;
            $objStyle->getProtection()->setHidden($isHidden ? PHPExcel_Style_Protection::PROTECTION_PROTECTED : PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
            // bit 2; mask 0x04; 0 = Cell XF, 1 = Cell Style XF
            $isCellStyleXf = (0x4 & $xfTypeProt) >> 2;
            // offset:  6; size: 1; Alignment and text break
            // bit 2-0, mask 0x07; horizontal alignment
            $horAlign = (0x7 & ord($recordData[6])) >> 0;
            switch ($horAlign) {
                case 0:
                    $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL);
                    break;
                case 1:
                    $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                    break;
                case 2:
                    $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    break;
                case 3:
                    $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    break;
                case 5:
                    $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
                    break;
                case 6:
                    $objStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS);
                    break;
            }
            // bit 3, mask 0x08; wrap text
            $wrapText = (0x8 & ord($recordData[6])) >> 3;
            switch ($wrapText) {
                case 0:
                    $objStyle->getAlignment()->setWrapText(false);
                    break;
                case 1:
                    $objStyle->getAlignment()->setWrapText(true);
                    break;
            }
            // bit 6-4, mask 0x70; vertical alignment
            $vertAlign = (0x70 & ord($recordData[6])) >> 4;
            switch ($vertAlign) {
                case 0:
                    $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
                    break;
                case 1:
                    $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    break;
                case 2:
                    $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
                    break;
                case 3:
                    $objStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
                    break;
            }
            if ($this->_version == self::XLS_BIFF8) {
                // offset:  7; size: 1; XF_ROTATION: Text rotation angle
                $angle = ord($recordData[7]);
                $rotation = 0;
                if ($angle <= 90) {
                    $rotation = $angle;
                } else {
Example #5
0
 /**
  * Get the shared style component for the currently active cell in currently active sheet.
  * Only used for style supervisor
  *
  * @return PHPExcel_Style
  */
 public function getSharedComponent()
 {
     $activeSheet = $this->getActiveSheet();
     $selectedCell = $this->getActiveCell();
     // e.g. 'A1'
     if ($activeSheet->cellExists($selectedCell)) {
         $xfIndex = $activeSheet->getCell($selectedCell)->getXfIndex();
     } else {
         $xfIndex = 0;
     }
     return $this->_parent->getCellXfByIndex($xfIndex);
 }