/** * 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; }
/** * 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; }
/** * 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; }
$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 {
/** * 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); }