nvalid character found in sheet code name'); } // Maximum 31 characters allowed for sheet title if ($CharCount > 31) { throw new PHPExcel_Exception('Maximum 31 characters allowed in sheet code name.'); } return $pValue; } /** * Check sheet title for valid Excel syntax * * @param string $pValue The string to check * @return string The valid string * @throws PHPExcel_Exception */ private static function _checkSheetTitle($pValue) { // Some of the printable ASCII characters are invalid: * : / \ ? [ ] if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) { throw new PHPExcel_Exception('Invalid character found in sheet title'); } // Maximum 31 characters allowed for sheet title if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) { throw new PHPExcel_Exception('Maximum 31 characters allowed in sheet title.'); } return $pValue; } /** * Get collection of cells * * @param boolean $pSorted Also sort the cell collection? * @return PHPExcel_Cell[] */ public function getCellCollection($pSorted = true) { if ($pSorted) { // Re-order cell collection return $this->sortCellCollection(); } if ($this->_cellCollection !== NULL) { return $this->_cellCollection->getCellList(); } return array(); } /** * Sort collection of cells * * @return PHPExcel_Worksheet */ public function sortCellCollection() { if ($this->_cellCollection !== NULL) { return $this->_cellCollection->getSortedCellList(); } return array(); } /** * Get collection of row dimensions * * @return PHPExcel_Worksheet_RowDimension[] */ public function getRowDimensions() { return $this->_rowDimensions; } /** * Get default row dimension * * @return PHPExcel_Worksheet_RowDimension */ public function getDefaultRowDimension() { return $this->_defaultRowDimension; } /** * Get collection of column dimensions * * @return PHPExcel_Worksheet_ColumnDimension[] */ public function getColumnDimensions() { return $this->_columnDimensions; } /** * Get default column dimension * * @return PHPExcel_Worksheet_ColumnDimension */ public function getDefaultColumnDimension() { return $this->_defaultColumnDimension; } /** * Get collection of drawings * * @return PHPExcel_Worksheet_BaseDrawing[] */ public function getDrawingCollection() { return $this->_drawingCollection; } /** * Get collection of charts * * @return PHPExcel_Chart[] */ public function getChartCollection() { return $this->_chartCollection; } /** * Add chart * * @param PHPExcel_Chart $pChart * @param int|null $iChartIndex Index where chart should go (0,1,..., or null for last) * @return PHPExcel_Chart */ public function addChart(PHPExcel_Chart $pChart = null, $iChartIndex = null) { $pChart->setWorksheet($this); if (is_null($iChartIndex)) { $this->_chartCollection[] = $pChart; } else { // Insert the chart at the requested index array_splice($this->_chartCollection, $iChartIndex, 0, array($pChart)); } return $pChart; } /** * Return the count of charts on this worksheet * * @return int The number of charts */ public function getChartCount() { return count($this->_chartCollection); } /** * Get a chart by its index position * * @param string $index Chart index position * @return false|PHPExcel_Chart * @throws PHPExcel_Exception */ public function getChartByIndex($index = null) { $chartCount = count($this->_chartCollection); if ($chartCount == 0) { return false; } if (is_null($index)) { $index = --$chartCount; } if (!isset($this->_chartCollection[$index])) { return false; } return $this->_chartCollection[$index]; } /** * Return an array of the names of charts on this worksheet * * @return string[] The names of charts * @throws PHPExcel_Exception */ public function getChartNames() { $chartNames = array(); foreach($this->_chartCollection as $chart) { $chartNames[] = $chart->getName(); } return $chartNames; } /** * Get a chart by name * * @param string $chartName Chart name * @return false|PHPExcel_Chart * @throws PHPExcel_Exception */ public function getChartByName($chartName = '') { $chartCount = count($this->_chartCollection); if ($chartCount == 0) { return false; } foreach($this->_chartCollection as $index => $chart) { if ($chart->getName() == $chartName) { return $this->_chartCollection[$index]; } } return false; } /** * Refresh column dimensions * * @return PHPExcel_Worksheet */ public function refreshColumnDimensions() { $currentColumnDimensions = $this->getColumnDimensions(); $newColumnDimensions = array(); foreach ($currentColumnDimensions as $objColumnDimension) { $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension; } $this->_columnDimensions = $newColumnDimensions; return $this; } /** * Refresh row dimensions * * @return PHPExcel_Worksheet */ public function refreshRowDimensions() { $currentRowDimensions = $this->getRowDimensions(); $newRowDimensions = array(); foreach ($currentRowDimensions as $objRowDimension) { $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension; } $this->_rowDimensions = $newRowDimensions; return $this; } /** * Calculate worksheet dimension * * @return string String containing the dimension of this worksheet */ public function calculateWorksheetDimension() { // Return return 'A1' . ':' . $this->getHighestColumn() . $this->getHighestRow(); } /** * Calculate worksheet data dimension * * @return string String containing the dimension of this worksheet that actually contain data */ public function calculateWorksheetDataDimension() { // Return return 'A1' . ':' . $this->getHighestDataColumn() . $this->getHighestDataRow(); } /** * Calculate widths for auto-size columns * * @param boolean $calculateMergeCells Calculate merge cell width * @return PHPExcel_Worksheet; */ public function calculateColumnWidths($calculateMergeCells = false) { // initialize $autoSizes array $autoSizes = array(); foreach ($this->getColumnDimensions() as $colDimension) { if ($colDimension->getAutoSize()) { $autoSizes[$colDimension->getColumnIndex()] = -1; } } // There is only something to do if there are some auto-size columns if (!empty($autoSizes)) { // build list of cells references that participate in a merge $isMergeCell = array(); foreach ($this->getMergeCells() as $cells) { foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) { $isMergeCell[$cellReference] = true; } } // loop through all cells in the worksheet foreach ($this->getCellCollection(false) as $cellID) { $cell = $this->getCell($cellID); if (isset($autoSizes[$this->_cellCollection->getCurrentColumn()])) { // Determine width if cell does not participate in a merge if (!isset($isMergeCell[$this->_cellCollection->getCurrentAddress()])) { // Calculated value // To formatted string $cellValue = PHPExcel_Style_NumberFormat::toFormattedString( $cell->getCalculatedValue(), $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode() ); $autoSizes[$this->_cellCollection->getCurrentColumn()] = max( (float) $autoSizes[$this->_cellCollection->getCurrentColumn()], (float)PHPExcel_Shared_Font::calculateColumnWidth( $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(), $cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(), $this->getDefaultStyle()->getFont() ) ); } } } // adjust column widths foreach ($autoSizes as $columnIndex => $width) { if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth(); $this->getColumnDimension($columnIndex)->setWidth($width); } } return $this; } /** * Get parent * * @return PHPExcel */ public function getParent() { return $this->_parent; } /** * Re-bind parent * * @param PHPExcel $parent * @return PHPExcel_Worksheet */ public function rebindParent(PHPExcel $parent) { if ($this->_parent !== null) { $namedRanges = $this->_parent->getNamedRanges(); foreach ($namedRanges as $namedRange) { $parent->addNamedRange($namedRange); } $this->_parent->removeSheetByIndex( $this->_parent->getIndex($this) ); } $this->_parent = $parent; return $this; } /** * Get title * * @return string */ public function getTitle() { return $this->_title; } /** * Set title * * @param string $pValue String containing the dimension of this worksheet * @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should * be updated to reflect the new sheet name. * This should be left as the default true, unless you are * certain that no formula cells on any worksheet contain * references to this worksheet * @return PHPExcel_Worksheet */ public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true) { // Is this a 'rename' or not? if ($this->getTitle() == $pValue) { return $this; } // Syntax check self::_checkSheetTitle($pValue); // Old title $oldTitle = $this->getTitle(); if ($this->_parent) { // Is there already such sheet name? if ($this->_parent->sheetNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) { $pValue = PHPExcel_Shared_String::Substring($pValue,0,29); } $i = 1; while ($this->_parent->sheetNameExists($pValue . ' ' . $i)) { ++$i; if ($i == 10) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) { $pValue = PHPExcel_Shared_String::Substring($pValue,0,28); } } elseif ($i == 100) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) { $pValue = PHPExcel_Shared_String::Substring($pValue,0,27); } } } $altTitle = $pValue . ' ' . $i; return $this->setTitle($altTitle,$updateFormulaCellReferences); } } // Set title $this->_title = $pValue; $this->_dirty = true; if ($this->_parent) { // New title $newTitle = $this->getTitle(); PHPExcel_Calculation::getInstance($this->_parent) ->renameCalculationCacheForWorksheet($oldTitle, $newTitle); if ($updateFormulaCellReferences) PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->_parent, $oldTitle, $newTitle); } return $this; } /** * Get sheet state * * @return string Sheet state (visible, hidden, veryHidden) */ public function getSheetState() { return $this->_sheetState; } /** * Set sheet state * * @param string $value Sheet state (visible, hidden, veryHidden) * @return PHPExcel_Worksheet */ public function setSheetState($value = PHPExcel_Worksheet::SHEETSTATE_VISIBLE) { $this->_sheetState = $value; return $this; } /** * Get page setup * * @return PHPExcel_Worksheet_PageSetup */ public function getPageSetup() { return $this->_pageSetup; } /** * Set page setup * * @param PHPExcel_Worksheet_PageSetup $pValue * @return PHPExcel_Worksheet */ public function setPageSetup(PHPExcel_Worksheet_PageSetup $pValue) { $this->_pageSetup = $pValue; return $this; } /** * Get page margins * * @return PHPExcel_Worksheet_PageMargins */ public function getPageMargins() { return $this->_pageMargins; } /** * Set page margins * * @param PHPExcel_Worksheet_PageMargins $pValue * @return PHPExcel_Worksheet */ public function setPageMargins(PHPExcel_Worksheet_PageMargins $pValue) { $this->_pageMargins = $pValue; return $this; } /** * Get page header/footer * * @return PHPExcel_Worksheet_HeaderFooter */ public function getHeaderFooter() { return $this->_headerFooter; } /** * Set page header/footer * * @param PHPExcel_Worksheet_HeaderFooter $pValue * @return PHPExcel_Worksheet */ public function setHeaderFooter(PHPExcel_Worksheet_HeaderFooter $pValue) { $this->_headerFooter = $pValue; return $this; } /** * Get sheet view * * @return PHPExcel_Worksheet_SheetView */ public function getSheetView() { return $this->_sheetView; } /** * Set sheet view * * @param PHPExcel_Worksheet_SheetView $pValue * @return PHPExcel_Worksheet */ public function setSheetView(PHPExcel_Worksheet_SheetView $pValue) { $this->_sheetView = $pValue; return $this; } /** * Get Protection * * @return PHPExcel_Worksheet_Protection */ public function getProtection() { return $this->_protection; } /** * Set Protection * * @param PHPExcel_Worksheet_Protection $pValue * @return PHPExcel_Worksheet */ public function setProtection(PHPExcel_Worksheet_Protection $pValue) { $this->_protection = $pValue; $this->_dirty = true; return $this; } /** * Get highest worksheet column * * @param string $row Return the data highest column for the specified row, * or the highest column of any row if no row number is passed * @return string Highest column name */ public function getHighestColumn($row = null) { if ($row == null) { return $this->_cachedHighestColumn; } return $this->getHighestDataColumn($row); } /** * Get highest worksheet column that contains data * * @param string $row Return the highest data column for the specified row, * or the highest data column of any row if no row number is passed * @return string Highest column name that contains data */ public function getHighestDataColumn($row = null) { return $this->_cellCollection->getHighestColumn($row); } /** * Get highest worksheet row * * @param string $column Return the highest data row for the specified column, * or the highest row of any column if no column letter is passed * @return int Highest row number */ public function getHighestRow($column = null) { if ($column == null) { return $this->_cachedHighestRow; } return $this->getHighestDataRow($column); } /** * Get highest worksheet row that contains data * * @param string $column Return the highest data row for the specified column, * or the highest data row of any column if no column letter is passed * @return string Highest row number that contains data */ public function getHighestDataRow($column = null) { return $this->_cellCollection->getHighestRow($column); } /** * Get highest worksheet column and highest row that have cell records * * @return array Highest column name and highest row number */ public function getHighestRowAndColumn() { return $this->_cellCollection->getHighestRowAndColumn(); } /** * Set a cell value * * @param string $pCoordinate Coordinate of the cell * @param mixed $pValue Value of the cell * @param bool $returnCell Return the worksheet (false, default) or the cell (true) * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified */ public function setCellValue($pCoordinate = 'A1', $pValue = null, $returnCell = false) { $cell = $this->getCell(strtoupper($pCoordinate))->setValue($pValue); return ($returnCell) ? $cell : $this; } /** * Set a cell value by using numeric cell coordinates * * @param string $pColumn Numeric column coordinate of the cell (A = 0) * @param string $pRow Numeric row coordinate of the cell * @param mixed $pValue Value of the cell * @param bool $returnCell Return the worksheet (false, default) or the cell (true) * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified */ public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $returnCell = false) { $cell = $this->getCellByColumnAndRow($pColumn, $pRow)->setValue($pValue); return ($returnCell) ? $cell : $this; } /** * Set a cell value * * @param string $pCoordinate Coordinate of the cell * @param mixed $pValue Value of the cell * @param string $pDataType Explicit data type * @param bool $returnCell Return the worksheet (false, default) or the cell (true) * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified */ public function setCellValueExplicit($pCoordinate = 'A1', $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false) { // Set value $cell = $this->getCell(strtoupper($pCoordinate))->setValueExplicit($pValue, $pDataType); return ($returnCell) ? $cell : $this; } /** * Set a cell value by using numeric cell coordinates * * @param string $pColumn Numeric column coordinate of the cell * @param string $pRow Numeric row coordinate of the cell * @param mixed $pValue Value of the cell * @param string $pDataType Explicit data type * @param bool $returnCell Return the worksheet (false, default) or the cell (true) * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified */ public function setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false) { $cell = $this->getCellByColumnAndRow($pColumn, $pRow)->setValueExplicit($pValue, $pDataType); return ($returnCell) ? $cell : $this; } /** * Get cell at a specific coordinate * * @param string $pCoordinate Coordinate of the cell * @throws PHPExcel_Exception * @return PHPExcel_Cell Cell that was found */ public function getCell($pCoordinate = 'A1') { $pCoordinate = strtoupper($pCoordinate); // Check cell collection if ($this->_cellCollection->isDataSet($pCoordinate)) { return $this->_cellCollection->getCacheData($pCoordinate); } // Worksheet reference? if (strpos($pCoordinate, '!') !== false) { $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true); return $this->_parent->getSheetByName($worksheetReference[0])->getCell($worksheetReference[1]); } // Named range? if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) && (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) { $namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this); if ($namedRange !== NULL) { $pCoordinate = $namedRange->getRange(); return $namedRange->getWorksheet()->getCell($pCoordinate); } } // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { throw new PHPExcel_Exception('Cell coordinate can not be a range of cells.'); } elseif (strpos($pCoordinate, '$') !== false) { throw new PHPExcel_Exception('Cell coordinate must not be absolute.'); } // Create new cell object return $this->_createNewCell($pCoordinate); } /** * Get cell at a specific coordinate by using numeric cell coordinates * * @param string $pColumn Numeric column coordinate of the cell * @param string $pRow Numeric row coordinate of the cell * @return PHPExcel_Cell Cell that was found */ public function getCellByColumnAndRow($pColumn = 0, $pRow = 1) { $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn); $coordinate = $columnLetter . $pRow; if ($this->_cellCollection->isDataSet($coordinate)) { return $this->_cellCollection->getCacheData($coordinate); } return $this->_createNewCell($coordinate); } /** * Create a new cell at the specified coordinate * * @param string $pCoordinate Coordinate of the cell * @return PHPExcel_Cell Cell that was created */ private function _createNewCell($pCoordinate) { $cell = $this->_cellCollection->addCacheData( $pCoordinate, new PHPExcel_Cell( NULL, PHPExcel_Cell_DataType::TYPE_NULL, $this ) ); $this->_cellCollectionIsSorted = false; // Coordinates $aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate); if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($aCoordinates[0])) $this->_cachedHighestColumn = $aCoordinates[0]; $this->_cachedHighestRow = max($this->_cachedHighestRow, $aCoordinates[1]); // Cell needs appropriate xfIndex from dimensions records // but don't create dimension records if they don't already exist $rowDimension = $this->getRowDimension($aCoordinates[1], FALSE); $columnDimension = $this->getColumnDimension($aCoordinates[0], FALSE); if ($rowDimension !== NULL && $rowDimension->getXfIndex() > 0) { // then there is a row dimension with explicit style, assign it to the cell $cell->setXfIndex($rowDimension->getXfIndex()); } elseif ($columnDimension !== NULL && $columnDimension->getXfIndex() > 0) { // then there is a column dimension, assign it to the cell $cell->setXfIndex($columnDimension->getXfIndex()); } return $cell; } /** * Does the cell at a specific coordinate exist? * * @param string $pCoordinate Coordinate of the cell * @throws PHPExcel_Exception * @return boolean */ public function cellExists($pCoordinate = 'A1') { // Worksheet reference? if (strpos($pCoordinate, '!') !== false) { $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true); return $this->_parent->getSheetByName($worksheetReference[0])->cellExists(strtoupper($worksheetReference[1])); } // Named range? if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) && (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) { $namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this); if ($namedRange !== NULL) { $pCoordinate = $namedRange->getRange(); if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) { if (!$namedRange->getLocalOnly()) { return $namedRange->getWorksheet()->cellExists($pCoordinate); } else { throw new PHPExcel_Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle()); } } } else { return false; } } // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) { throw new PHPExcel_Exception('Cell coordinate can not be a range of cells.'); } elseif (strpos($pCoordinate,'$') !== false) { throw new PHPExcel_Exception('Cell coordinate must not be absolute.'); } else { // Coordinates $aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate); // Cell exists? return $this->_cellCollection->isDataSet($pCoordinate); } } /** * Cell at a specific coordinate by using numeric cell coordinates exists? * * @param string $pColumn Numeric column coordinate of the cell * @param string $pRow Numeric row coordinate of the cell * @return boolean */ public function cellExistsByColumnAndRow($pColumn = 0, $pRow = 1) { return $this->cellExists(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow); } /** * Get row dimension at a specific row * * @param int $pRow Numeric index of the row * @return PHPExcel_Worksheet_RowDimension */ public function getRowDimension($pRow = 1, $create = TRUE) { // Found $found = null; // Get row dimension if (!isset($this->_rowDimensions[$pRow])) { if (!$create) return NULL; $this->_rowDimensions[$pRow] = new PHPExcel_Worksheet_RowDimension($pRow); $this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow); } return $this->_rowDimensions[$pRow]; } /** * Get column dimension at a specific column * * @param string $pColumn String index of the column * @return PHPExcel_Worksheet_ColumnDimension */ public function getColumnDimension($pColumn = 'A', $create = TRUE) { // Uppercase coordinate $pColumn = strtoupper($pColumn); // Fetch dimensions if (!isset($this->_columnDimensions[$pColumn])) { if (!$create) return NULL; $this->_columnDimensions[$pColumn] = new PHPExcel_Worksheet_ColumnDimension($pColumn); if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($pColumn)) $this->_cachedHighestColumn = $pColumn; } return $this->_columnDimensions[$pColumn]; } /** * Get column dimension at a specific column by using numeric cell coordinates * * @param string $pColumn Numeric column coordinate of the cell * @return PHPExcel_Worksheet_ColumnDimension */ public function getColumnDimensionByColumn($pColumn = 0) { return $this->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($pColumn)); } /** * Get styles * * @return PHPExcel_Style[] */ public function getStyles() { return $this->_styles; } /** * Get default style of workbook. * * @deprecated * @return PHPExcel_Style * @throws PHPExcel_Exception */ public function getDefaultStyle() { return $this->_parent->getDefaultStyle(); } /** * Set default style - should only be used by PHPExcel_IReader implementations! * * @deprecated * @param PHPExcel_Style $pValue * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setDefaultStyle(PHPExcel_Style $pValue) { $this->_parent->getDefaultStyle()->applyFromArray(array( 'font' => array( 'name' => $pValue->getFont()->getName(), 'size' => $pValue->getFont()->getSize(), ), )); return $this; } /** * Get style for cell * * @param string $pCellCoordinate Cell coordinate (or range) to get style for * @return PHPExcel_Style * @throws PHPExcel_Exception */ public function getStyle($pCellCoordinate = 'A1') { // set this sheet as active $this->_parent->setActiveSheetIndex($this->_parent->getIndex($this)); // set cell coordinate as active $this->setSelectedCells(strtoupper($pCellCoordinate)); return $this->_parent->getCellXfSupervisor(); } /** * Get conditional styles for a cell * * @param string $pCoordinate * @return PHPExcel_Style_Conditional[] */ public function getConditionalStyles($pCoordinate = 'A1') { $pCoordinate = strtoupper($pCoordinate); if (!isset($this->_conditionalStylesCollection[$pCoordinate])) { $this->_conditionalStylesCollection[$pCoordinate] = array(); } return $this->_conditionalStylesCollection[$pCoordinate]; } /** * Do conditional styles exist for this cell? * * @param string $pCoordinate * @return boolean */ public function conditionalStylesExists($pCoordinate = 'A1') { if (isset($this->_conditionalStylesCollection[strtoupper($pCoordinate)])) { return true; } return false; } /** * Removes conditional styles for a cell * * @param string $pCoordinate * @return PHPExcel_Worksheet */ public function removeConditionalStyles($pCoordinate = 'A1') { unset($this->_conditionalStylesCollection[strtoupper($pCoordinate)]); return $this; } /** * Get collection of conditional styles * * @return array */ public function getConditionalStylesCollection() { return $this->_conditionalStylesCollection; } /** * Set conditional styles * * @param $pCoordinate string E.g. 'A1' * @param $pValue PHPExcel_Style_Conditional[] * @return PHPExcel_Worksheet */ public function setConditionalStyles($pCoordinate = 'A1', $pValue) { $this->_conditionalStylesCollection[strtoupper($pCoordinate)] = $pValue; return $this; } /** * Get style for cell by using numeric cell coordinates * * @param int $pColumn Numeric column coordinate of the cell * @param int $pRow Numeric row coordinate of the cell * @param int pColumn2 Numeric column coordinate of the range cell * @param int pRow2 Numeric row coordinate of the range cell * @return PHPExcel_Style */ public function getStyleByColumnAndRow($pColumn = 0, $pRow = 1, $pColumn2 = null, $pRow2 = null) { if (!is_null($pColumn2) && !is_null($pRow2)) { $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2; return $this->getStyle($cellRange); } return $this->getStyle(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow); } /** * Set shared cell style to a range of cells * * Please note that this will overwrite existing cell styles for cells in range! * * @deprecated * @param PHPExcel_Style $pSharedCellStyle Cell style to share * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setSharedStyle(PHPExcel_Style $pSharedCellStyle = null, $pRange = '') { $this->duplicateStyle($pSharedCellStyle, $pRange); return $this; } /** * Duplicate cell style to a range of cells * * Please note that this will overwrite existing cell styles for cells in range! * * @param PHPExcel_Style $pCellStyle Cell style to duplicate * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '') { // make sure we have a real style and not supervisor $style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle; // Add the style to the workbook if necessary $workbook = $this->_parent; if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) { // there is already such cell Xf in our collection $xfIndex = $existingStyle->getIndex(); } else { // we don't have such a cell Xf, need to add $workbook->addCellXf($pCellStyle); $xfIndex = $pCellStyle->getIndex(); } // Calculate range outer borders list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange . ':' . $pRange); // Make sure we can loop upwards on rows and columns if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) { $tmp = $rangeStart; $rangeStart = $rangeEnd; $rangeEnd = $tmp; } // Loop through cells and apply styles for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) { for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) { $this->getCell(PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row)->setXfIndex($xfIndex); } } return $this; } /** * Duplicate conditional style to a range of cells * * Please note that this will overwrite existing cell styles for cells in range! * * @param array of PHPExcel_Style_Conditional $pCellStyle Cell style to duplicate * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function duplicateConditionalStyle(array $pCellStyle = null, $pRange = '') { foreach($pCellStyle as $cellStyle) { if (!($cellStyle instanceof PHPExcel_Style_Conditional)) { throw new PHPExcel_Exception('Style is not a conditional style'); } } // Calculate range outer borders list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange . ':' . $pRange); // Make sure we can loop upwards on rows and columns if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) { $tmp = $rangeStart; $rangeStart = $rangeEnd; $rangeEnd = $tmp; } // Loop through cells and apply styles for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) { for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) { $this->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row, $pCellStyle); } } return $this; } /** * Duplicate cell style array to a range of cells * * Please note that this will overwrite existing cell styles for cells in range, * if they are in the styles array. For example, if you decide to set a range of * cells to font bold, only include font bold in the styles array. * * @deprecated * @param array $pStyles Array containing style information * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @param boolean $pAdvanced Advanced mode for setting borders. * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function duplicateStyleArray($pStyles = null, $pRange = '', $pAdvanced = true) { $this->getStyle($pRange)->applyFromArray($pStyles, $pAdvanced); return $this; } /** * Set break on a cell * * @param string $pCell Cell coordinate (e.g. A1) * @param int $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*) * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setBreak($pCell = 'A1', $pBreak = PHPExcel_Worksheet::BREAK_NONE) { // Uppercase coordinate $pCell = strtoupper($pCell); if ($pCell != '') { if ($pBreak == PHPExcel_Worksheet::BREAK_NONE) { if (isset($this->_breaks[$pCell])) { unset($this->_breaks[$pCell]); } } else { $this->_breaks[$pCell] = $pBreak; } } else { throw new PHPExcel_Exception('No cell coordinate specified.'); } return $this; } /** * Set break on a cell by using numeric cell coordinates * * @param integer $pColumn Numeric column coordinate of the cell * @param integer $pRow Numeric row coordinate of the cell * @param integer $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*) * @return PHPExcel_Worksheet */ public function setBreakByColumnAndRow($pColumn = 0, $pRow = 1, $pBreak = PHPExcel_Worksheet::BREAK_NONE) { return $this->setBreak(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow, $pBreak); } /** * Get breaks * * @return array[] */ public function getBreaks() { return $this->_breaks; } /** * Set merge on a cell range * * @param string $pRange Cell range (e.g. A1:E1) * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function mergeCells($pRange = 'A1:A1') { // Uppercase coordinate $pRange = strtoupper($pRange); if (strpos($pRange,':') !== false) { $this->_mergeCells[$pRange] = $pRange; // make sure cells are created // get the cells in the range $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange); // create upper left cell if it does not already exist $upperLeft = $aReferences[0]; if (!$this->cellExists($upperLeft)) { $this->getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL); } // create or blank out the rest of the cells in the range $count = count($aReferences); for ($i = 1; $i < $count; $i++) { $this->getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL); } } else { throw new PHPExcel_Exception('Merge must be set on a range of cells.'); } return $this; } /** * Set merge on a cell range by using numeric cell coordinates * * @param int $pColumn1 Numeric column coordinate of the first cell * @param int $pRow1 Numeric row coordinate of the first cell * @param int $pColumn2 Numeric column coordinate of the last cell * @param int $pRow2 Numeric row coordinate of the last cell * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function mergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1) { $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2; return $this->mergeCells($cellRange); } /** * Remove merge on a cell range * * @param string $pRange Cell range (e.g. A1:E1) * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function unmergeCells($pRange = 'A1:A1') { // Uppercase coordinate $pRange = strtoupper($pRange); if (strpos($pRange,':') !== false) { if (isset($this->_mergeCells[$pRange])) { unset($this->_mergeCells[$pRange]); } else { throw new PHPExcel_Exception('Cell range ' . $pRange . ' not known as merged.'); } } else { throw new PHPExcel_Exception('Merge can only be removed from a range of cells.'); } return $this; } /** * Remove merge on a cell range by using numeric cell coordinates * * @param int $pColumn1 Numeric column coordinate of the first cell * @param int $pRow1 Numeric row coordinate of the first cell * @param int $pColumn2 Numeric column coordinate of the last cell * @param int $pRow2 Numeric row coordinate of the last cell * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function unmergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1) { $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2; return $this->unmergeCells($cellRange); } /** * Get merge cells array. * * @return array[] */ public function getMergeCells() { return $this->_mergeCells; } /** * Set merge cells array for the entire sheet. Use instead mergeCells() to merge * a single cell range. * * @param array */ public function setMergeCells($pValue = array()) { $this->_mergeCells = $pValue; return $this; } /** * Set protection on a cell range * * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1) * @param string $pPassword Password to unlock the protection * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function protectCells($pRange = 'A1', $pPassword = '', $pAlreadyHashed = false) { // Uppercase coordinate $pRange = strtoupper($pRange); if (!$pAlreadyHashed) { $pPassword = PHPExcel_Shared_PasswordHasher::hashPassword($pPassword); } $this->_protectedCells[$pRange] = $pPassword; return $this; } /** * Set protection on a cell range by using numeric cell coordinates * * @param int $pColumn1 Numeric column coordinate of the first cell * @param int $pRow1 Numeric row coordinate of the first cell * @param int $pColumn2 Numeric column coordinate of the last cell * @param int $pRow2 Numeric row coordinate of the last cell * @param string $pPassword Password to unlock the protection * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function protectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false) { $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2; return $this->protectCells($cellRange, $pPassword, $pAlreadyHashed); } /** * Remove protection on a cell range * * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1) * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function unprotectCells($pRange = 'A1') { // Uppercase coordinate $pRange = strtoupper($pRange); if (isset($this->_protectedCells[$pRange])) { unset($this->_protectedCells[$pRange]); } else { throw new PHPExcel_Exception('Cell range ' . $pRange . ' not known as protected.'); } return $this; } /** * Remove protection on a cell range by using numeric cell coordinates * * @param int $pColumn1 Numeric column coordinate of the first cell * @param int $pRow1 Numeric row coordinate of the first cell * @param int $pColumn2 Numeric column coordinate of the last cell * @param int $pRow2 Numeric row coordinate of the last cell * @param string $pPassword Password to unlock the protection * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function unprotectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false) { $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2; return $this->unprotectCells($cellRange, $pPassword, $pAlreadyHashed); } /** * Get protected cells * * @return array[] */ public function getProtectedCells() { return $this->_protectedCells; } /** * Get Autofilter * * @return PHPExcel_Worksheet_AutoFilter */ public function getAutoFilter() { return $this->_autoFilter; } /** * Set AutoFilter * * @param PHPExcel_Worksheet_AutoFilter|string $pValue * A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setAutoFilter($pValue) { $pRange = strtoupper($pValue); if (is_string($pValue)) { $this->_autoFilter->setRange($pValue); } elseif(is_object($pValue) && ($pValue instanceof PHPExcel_Worksheet_AutoFilter)) { $this->_autoFilter = $pValue; } return $this; } /** * Set Autofilter Range by using numeric cell coordinates * * @param integer $pColumn1 Numeric column coordinate of the first cell * @param integer $pRow1 Numeric row coordinate of the first cell * @param integer $pColumn2 Numeric column coordinate of the second cell * @param integer $pRow2 Numeric row coordinate of the second cell * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setAutoFilterByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1) { return $this->setAutoFilter( PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2 ); } /** * Remove autofilter * * @return PHPExcel_Worksheet */ public function removeAutoFilter() { $this->_autoFilter->setRange(NULL); return $this; } /** * Get Freeze Pane * * @return string */ public function getFreezePane() { return $this->_freezePane; } /** * Freeze Pane * * @param string $pCell Cell (i.e. A2) * Examples: * A2 will freeze the rows above cell A2 (i.e row 1) * B1 will freeze the columns to the left of cell B1 (i.e column A) * B2 will freeze the rows above and to the left of cell A2 * (i.e row 1 and column A) * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function freezePane($pCell = '') { // Uppercase coordinate $pCell = strtoupper($pCell); if (strpos($pCell,':') === false && strpos($pCell,',') === false) { $this->_freezePane = $pCell; } else { throw new PHPExcel_Exception('Freeze pane can not be set on a range of cells.'); } return $this; } /** * Freeze Pane by using numeric cell coordinates * * @param int $pColumn Numeric column coordinate of the cell * @param int $pRow Numeric row coordinate of the cell * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 1) { return $this->freezePane(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow); } /** * Unfreeze Pane * * @return PHPExcel_Worksheet */ public function unfreezePane() { return $this->freezePane(''); } /** * Insert a new row, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumRows Number of rows to insert * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function insertNewRowBefore($pBefore = 1, $pNumRows = 1) { if ($pBefore >= 1) { $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance(); $objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this); } else { throw new PHPExcel_Exception("Rows can only be inserted before at least row 1."); } return $this; } /** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function insertNewColumnBefore($pBefore = 'A', $pNumCols = 1) { if (!is_numeric($pBefore)) { $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance(); $objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this); } else { throw new PHPExcel_Exception("Column references should not be numeric."); } return $this; } /** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one (numeric column coordinate of the cell) * @param int $pNumCols Number of columns to insert * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function insertNewColumnBeforeByIndex($pBefore = 0, $pNumCols = 1) { if ($pBefore >= 0) { return $this->insertNewColumnBefore(PHPExcel_Cell::stringFromColumnIndex($pBefore), $pNumCols); } else { throw new PHPExcel_Exception("Columns can only be inserted before at least column A (0)."); } } /** * Delete a row, updating all possible related data * * @param int $pRow Remove starting with this one * @param int $pNumRows Number of rows to remove * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function removeRow($pRow = 1, $pNumRows = 1) { if ($pRow >= 1) { $highestRow = $this->getHighestDataRow(); $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance(); $objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this); for($r = 0; $r < $pNumRows; ++$r) { $this->getCellCacheController()->removeRow($highestRow); --$highestRow; } } else { throw new PHPExcel_Exception("Rows to be deleted should at least start from row 1."); } return $this; } /** * Remove a column, updating all possible related data * * @param string $pColumn Remove starting with this one * @param int $pNumCols Number of columns to remove * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function removeColumn($pColumn = 'A', $pNumCols = 1) { if (!is_numeric($pColumn)) { $highestColumn = $this->getHighestDataColumn(); $pColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($pColumn) - 1 + $pNumCols); $objReferenceHelper = PHPExcel_ReferenceHelper::getInstance(); $objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this); for($c = 0; $c < $pNumCols; ++$c) { $this->getCellCacheController()->removeColumn($highestColumn); $highestColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($highestColumn) - 2); } } else { throw new PHPExcel_Exception("Column references should not be numeric."); } return $this; } /** * Remove a column, updating all possible related data * * @param int $pColumn Remove starting with this one (numeric column coordinate of the cell) * @param int $pNumCols Number of columns to remove * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function removeColumnByIndex($pColumn = 0, $pNumCols = 1) { if ($pColumn >= 0) { return $this->removeColumn(PHPExcel_Cell::stringFromColumnIndex($pColumn), $pNumCols); } else { throw new PHPExcel_Exception("Columns to be deleted should at least start from column 0"); } } /** * Show gridlines? * * @return boolean */ public function getShowGridlines() { return $this->_showGridlines; } /** * Set show gridlines * * @param boolean $pValue Show gridlines (true/false) * @return PHPExcel_Worksheet */ public function setShowGridlines($pValue = false) { $this->_showGridlines = $pValue; return $this; } /** * Print gridlines? * * @return boolean */ public function getPrintGridlines() { return $this->_printGridlines; } /** * Set print gridlines * * @param boolean $pValue Print gridlines (true/false) * @return PHPExcel_Worksheet */ public function setPrintGridlines($pValue = false) { $this->_printGridlines = $pValue; return $this; } /** * Show row and column headers? * * @return boolean */ public function getShowRowColHeaders() { return $this->_showRowColHeaders; } /** * Set show row and column headers * * @param boolean $pValue Show row and column headers (true/false) * @return PHPExcel_Worksheet */ public function setShowRowColHeaders($pValue = false) { $this->_showRowColHeaders = $pValue; return $this; } /** * Show summary below? (Row/Column outlining) * * @return boolean */ public function getShowSummaryBelow() { return $this->_showSummaryBelow; } /** * Set show summary below * * @param boolean $pValue Show summary below (true/false) * @return PHPExcel_Worksheet */ public function setShowSummaryBelow($pValue = true) { $this->_showSummaryBelow = $pValue; return $this; } /** * Show summary right? (Row/Column outlining) * * @return boolean */ public function getShowSummaryRight() { return $this->_showSummaryRight; } /** * Set show summary right * * @param boolean $pValue Show summary right (true/false) * @return PHPExcel_Worksheet */ public function setShowSummaryRight($pValue = true) { $this->_showSummaryRight = $pValue; return $this; } /** * Get comments * * @return PHPExcel_Comment[] */ public function getComments() { return $this->_comments; } /** * Set comments array for the entire sheet. * * @param array of PHPExcel_Comment * @return PHPExcel_Worksheet */ public function setComments($pValue = array()) { $this->_comments = $pValue; return $this; } /** * Get comment for cell * * @param string $pCellCoordinate Cell coordinate to get comment for * @return PHPExcel_Comment * @throws PHPExcel_Exception */ public function getComment($pCellCoordinate = 'A1') { // Uppercase coordinate $pCellCoordinate = strtoupper($pCellCoordinate); if (strpos($pCellCoordinate,':') !== false || strpos($pCellCoordinate,',') !== false) { throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells.'); } else if (strpos($pCellCoordinate,'$') !== false) { throw new PHPExcel_Exception('Cell coordinate string must not be absolute.'); } else if ($pCellCoordinate == '') { throw new PHPExcel_Exception('Cell coordinate can not be zero-length string.'); } else { // Check if we already have a comment for this cell. // If not, create a new comment. if (isset($this->_comments[$pCellCoordinate])) { return $this->_comments[$pCellCoordinate]; } else { $newComment = new PHPExcel_Comment(); $this->_comments[$pCellCoordinate] = $newComment; return $newComment; } } } /** * Get comment for cell by using numeric cell coordinates * * @param int $pColumn Numeric column coordinate of the cell * @param int $pRow Numeric row coordinate of the cell * @return PHPExcel_Comment */ public function getCommentByColumnAndRow($pColumn = 0, $pRow = 1) { return $this->getComment(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow); } /** * Get selected cell * * @deprecated * @return string */ public function getSelectedCell() { return $this->getSelectedCells(); } /** * Get active cell * * @return string Example: 'A1' */ public function getActiveCell() { return $this->_activeCell; } /** * Get selected cells * * @return string */ public function getSelectedCells() { return $this->_selectedCells; } /** * Selected cell * * @param string $pCoordinate Cell (i.e. A1) * @return PHPExcel_Worksheet */ public function setSelectedCell($pCoordinate = 'A1') { return $this->setSelectedCells($pCoordinate); } /** * Select a range of cells. * * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6' * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setSelectedCells($pCoordinate = 'A1') { // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); // Convert 'A' to 'A:A' $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate); // Convert '1' to '1:1' $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate); // Convert 'A:C' to 'A1:C1048576' $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate); // Convert '1:3' to 'A1:XFD3' $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate); if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) { list($first, ) = PHPExcel_Cell::splitRange($pCoordinate); $this->_activeCell = $first[0]; } else { $this->_activeCell = $pCoordinate; } $this->_selectedCells = $pCoordinate; return $this; } /** * Selected cell by using numeric cell coordinates * * @param int $pColumn Numeric column coordinate of the cell * @param int $pRow Numeric row coordinate of the cell * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setSelectedCellByColumnAndRow($pColumn = 0, $pRow = 1) { return $this->setSelectedCells(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow); } /** * Get right-to-left * * @return boolean */ public function getRightToLeft() { return $this->_rightToLeft; } /** * Set right-to-left * * @param boolean $value Right-to-left true/false * @return PHPExcel_Worksheet */ public function setRightToLeft($value = false) { $this->_rightToLeft = $value; return $this; } /** * Fill worksheet from values in array * * @param array $source Source array * @param mixed $nullValue Value in source array that stands for blank cell * @param string $startCell Insert array starting from this cell address as the top left coordinate * @param boolean $strictNullComparison Apply strict comparison when testing for null values in the array * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function fromArray($source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false) { if (is_array($source)) { // Convert a 1-D array to 2-D (for ease of looping) if (!is_array(end($source))) { $source = array($source); } // start coordinate list ($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($startCell); // Loop through $source foreach ($source as $rowData) { $currentColumn = $startColumn; foreach($rowData as $cellValue) { if ($strictNullComparison) { if ($cellValue !== $nullValue) { // Set cell value $this->getCell($currentColumn . $startRow)->setValue($cellValue); } } else { if ($cellValue != $nullValue) { // Set cell value $this->getCell($currentColumn . $startRow)->setValue($cellValue); } } ++$currentColumn; } ++$startRow; } } else { throw new PHPExcel_Exception("Parameter \$source should be an array."); } return $this; } /** * 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 a range of cells * * @param string $pNamedRange Name of the Named Range * @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 * @throws PHPExcel_Exception */ public function namedRangeToArray($pNamedRange = '', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) { $namedRange = PHPExcel_NamedRange::resolveRange($pNamedRange, $this); if ($namedRange !== NULL) { $pWorkSheet = $namedRange->getWorksheet(); $pCellRange = $namedRange->getRange(); return $pWorkSheet->rangeToArray( $pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef); } throw new PHPExcel_Exception('Named Range '.$pNamedRange.' does not exist.'); } /** * 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) { // Garbage collect... $this->garbageCollect(); // Identify the range that we need to extract from the worksheet $maxCol = $this->getHighestColumn(); $maxRow = $this->getHighestRow(); // Return return $this->rangeToArray( 'A1:'.$maxCol.$maxRow, $nullValue, $calculateFormulas, $formatData, $returnCellRef); } /** * Get row iterator * * @param integer $startRow The row number at which to start iterating * @param integer $endRow The row number at which to stop iterating * * @return PHPExcel_Worksheet_RowIterator */ public function getRowIterator($startRow = 1, $endRow = null) { return new PHPExcel_Worksheet_RowIterator($this, $startRow, $endRow); } /** * Get column iterator * * @param string $startColumn The column address at which to start iterating * @param string $endColumn The column address at which to stop iterating * * @return PHPExcel_Worksheet_ColumnIterator */ public function getColumnIterator($startColumn = 'A', $endColumn = null) { return new PHPExcel_Worksheet_ColumnIterator($this, $startColumn, $endColumn); } /** * Run PHPExcel garabage collector. * * @return PHPExcel_Worksheet */ public function garbageCollect() { // Flush cache $this->_cellCollection->getCacheData('A1'); // Build a reference table from images // $imageCoordinates = array(); // $iterator = $this->getDrawingCollection()->getIterator(); // while ($iterator->valid()) { // $imageCoordinates[$iterator->current()->getCoordinates()] = true; // // $iterator->next(); // } // // Lookup highest column and highest row if cells are cleaned $colRow = $this->_cellCollection->getHighestRowAndColumn(); $highestRow = $colRow['row']; $highestColumn = PHPExcel_Cell::columnIndexFromString($colRow['column']); // Loop through column dimensions foreach ($this->_columnDimensions as $dimension) { $highestColumn = max($highestColumn,PHPExcel_Cell::columnIndexFromString($dimension->getColumnIndex())); } // Loop through row dimensions foreach ($this->_rowDimensions as $dimension) { $highestRow = max($highestRow,$dimension->getRowIndex()); } // Cache values if ($highestColumn < 0) { $this->_cachedHighestColumn = 'A'; } else { $this->_cachedHighestColumn = PHPExcel_Cell::stringFromColumnIndex(--$highestColumn); } $this->_cachedHighestRow = $highestRow; // Return return $this; } /** * Get hash code * * @return string Hash code */ public function getHashCode() { if ($this->_dirty) { $this->_hash = md5( $this->_title . $this->_autoFilter . ($this->_protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__ ); $this->_dirty = false; } return $this->_hash; } /** * Extract worksheet title from range. * * Example: extractSheetTitle("testSheet!A1") ==> 'A1' * Example: extractSheetTitle("'testSheet 1'!A1", true) ==> array('testSheet 1', 'A1'); * * @param string $pRange Range to extract title from * @param bool $returnRange Return range? (see example) * @return mixed */ public static function extractSheetTitle($pRange, $returnRange = false) { // Sheet title included? if (($sep = strpos($pRange, '!')) === false) { return ''; } if ($returnRange) { return array( trim(substr($pRange, 0, $sep),"'"), substr($pRange, $sep + 1) ); } return substr($pRange, $sep + 1); } /** * Get hyperlink * * @param string $pCellCoordinate Cell coordinate to get hyperlink for */ public function getHyperlink($pCellCoordinate = 'A1') { // return hyperlink if we already have one if (isset($this->_hyperlinkCollection[$pCellCoordinate])) { return $this->_hyperlinkCollection[$pCellCoordinate]; } // else create hyperlink $this->_hyperlinkCollection[$pCellCoordinate] = new PHPExcel_Cell_Hyperlink(); return $this->_hyperlinkCollection[$pCellCoordinate]; } /** * Set hyperlnk * * @param string $pCellCoordinate Cell coordinate to insert hyperlink * @param PHPExcel_Cell_Hyperlink $pHyperlink * @return PHPExcel_Worksheet */ public function setHyperlink($pCellCoordinate = 'A1', PHPExcel_Cell_Hyperlink $pHyperlink = null) { if ($pHyperlink === null) { unset($this->_hyperlinkCollection[$pCellCoordinate]); } else { $this->_hyperlinkCollection[$pCellCoordinate] = $pHyperlink; } return $this; } /** * Hyperlink at a specific coordinate exists? * * @param string $pCoordinate * @return boolean */ public function hyperlinkExists($pCoordinate = 'A1') { return isset($this->_hyperlinkCollection[$pCoordinate]); } /** * Get collection of hyperlinks * * @return PHPExcel_Cell_Hyperlink[] */ public function getHyperlinkCollection() { return $this->_hyperlinkCollection; } /** * Get data validation * * @param string $pCellCoordinate Cell coordinate to get data validation for */ public function getDataValidation($pCellCoordinate = 'A1') { // return data validation if we already have one if (isset($this->_dataValidationCollection[$pCellCoordinate])) { return $this->_dataValidationCollection[$pCellCoordinate]; } // else create data validation $this->_dataValidationCollection[$pCellCoordinate] = new PHPExcel_Cell_DataValidation(); return $this->_dataValidationCollection[$pCellCoordinate]; } /** * Set data validation * * @param string $pCellCoordinate Cell coordinate to insert data validation * @param PHPExcel_Cell_DataValidation $pDataValidation * @return PHPExcel_Worksheet */ public function setDataValidation($pCellCoordinate = 'A1', PHPExcel_Cell_DataValidation $pDataValidation = null) { if ($pDataValidation === null) { unset($this->_dataValidationCollection[$pCellCoordinate]); } else { $this->_dataValidationCollection[$pCellCoordinate] = $pDataValidation; } return $this; } /** * Data validation at a specific coordinate exists? * * @param string $pCoordinate * @return boolean */ public function dataValidationExists($pCoordinate = 'A1') { return isset($this->_dataValidationCollection[$pCoordinate]); } /** * Get collection of data validations * * @return PHPExcel_Cell_DataValidation[] */ public function getDataValidationCollection() { return $this->_dataValidationCollection; } /** * Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet * * @param string $range * @return string Adjusted range value */ public function shrinkRangeToFit($range) { $maxCol = $this->getHighestColumn(); $maxRow = $this->getHighestRow(); $maxCol = PHPExcel_Cell::columnIndexFromString($maxCol); $rangeBlocks = explode(' ',$range); foreach ($rangeBlocks as &$rangeSet) { $rangeBoundaries = PHPExcel_Cell::getRangeBoundaries($rangeSet); if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) { $rangeBoundaries[0][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol); } if ($rangeBoundaries[0][1] > $maxRow) { $rangeBoundaries[0][1] = $maxRow; } if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) { $rangeBoundaries[1][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol); } if ($rangeBoundaries[1][1] > $maxRow) { $rangeBoundaries[1][1] = $maxRow; } $rangeSet = $rangeBoundaries[0][0].$rangeBoundaries[0][1].':'.$rangeBoundaries[1][0].$rangeBoundaries[1][1]; } unset($rangeSet); $stRange = implode(' ',$rangeBlocks); return $stRange; } /** * Get tab color * * @return PHPExcel_Style_Color */ public function getTabColor() { if ($this->_tabColor === NULL) $this->_tabColor = new PHPExcel_Style_Color(); return $this->_tabColor; } /** * Reset tab color * * @return PHPExcel_Worksheet */ public function resetTabColor() { $this->_tabColor = null; unset($this->_tabColor); return $this; } /** * Tab color set? * * @return boolean */ public function isTabColorSet() { return ($this->_tabColor !== NULL); } /** * Copy worksheet (!= clone!) * * @return PHPExcel_Worksheet */ public function copy() { $copied = clone $this; return $copied; } /** * Implement PHP __clone to create a deep clone, not just a shallow copy. */ public function __clone() { foreach ($this as $key => $val) { if ($key == '_parent') { continue; } if (is_object($val) || (is_array($val))) { if ($key == '_cellCollection') { $newCollection = clone $this->_cellCollection; $newCollection->copyCellCollection($this); $this->_cellCollection = $newCollection; } elseif ($key == '_drawingCollection') { $newCollection = clone $this->_drawingCollection; $this->_drawingCollection = $newCollection; } elseif (($key == '_autoFilter') && ($this->_autoFilter instanceof PHPExcel_Worksheet_AutoFilter)) { $newAutoFilter = clone $this->_autoFilter; $this->_autoFilter = $newAutoFilter; $this->_autoFilter->setParent($this); } else { $this->{$key} = unserialize(serialize($val)); } } } } /** * Define the code name of the sheet * * @param null|string Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore) * @return objWorksheet * @throws PHPExcel_Exception */ public function setCodeName($pValue=null){ // Is this a 'rename' or not? if ($this->getCodeName() == $pValue) { return $this; } $pValue = str_replace(' ', '_', $pValue);//Excel does this automatically without flinching, we are doing the same // Syntax check // throw an exception if not valid self::_checkSheetCodeName($pValue); // We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_' if ($this->getParent()) { // Is there already such sheet name? if ($this->getParent()->sheetCodeNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) { $pValue = PHPExcel_Shared_String::Substring($pValue,0,29); } $i = 1; while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) { ++$i; if ($i == 10) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) { $pValue = PHPExcel_Shared_String::Substring($pValue,0,28); } } elseif ($i == 100) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) { $pValue = PHPExcel_Shared_String::Substring($pValue,0,27); } } } $pValue = $pValue . '_' . $i;// ok, we have a valid name //codeName is'nt used in formula : no need to call for an update //return $this->setTitle($altTitle,$updateFormulaCellReferences); } } $this->_codeName=$pValue; return $this; } /** * Return the code name of the sheet * * @return null|string */ public function getCodeName(){ return $this->_codeName; } /** * Sheet has a code name ? * @return boolean */ public function hasCodeName(){ return !(is_null($this->_codeName)); } } Escher.php 2238 1487008958 plugins/wp-all-import-pro/classes/PHPExcel/Shared _dggContainer; } /** * Set Drawing Group Container * * @param PHPExcel_Shared_Escher_DggContainer $dggContainer */ public function setDggContainer($dggContainer) { return $this->_dggContainer = $dggContainer; } /** * Get Drawing Container * * @return PHPExcel_Shared_Escher_DgContainer */ public function getDgContainer() { return $this->_dgContainer; } /** * Set Drawing Container * * @param PHPExcel_Shared_Escher_DgContainer $dgContainer */ public function setDgContainer($dgContainer) { return $this->_dgContainer = $dgContainer; } } File.php 5230 1487008959 plugins/wp-all-import-pro/classes/PHPExcel/Shared open($zipFile) === true) { $returnValue = ($zip->getFromName($archiveFile) !== false); $zip->close(); return $returnValue; } else { return false; } } else { // Regular file_exists return file_exists($pFilename); } } /** * Returns canonicalized absolute pathname, also for ZIP archives * * @param string $pFilename * @return string */ public static function realpath($pFilename) { // Returnvalue $returnValue = ''; // Try using realpath() if (file_exists($pFilename)) { $returnValue = realpath($pFilename); } // Found something? if ($returnValue == '' || ($returnValue === NULL)) { $pathArray = explode('/' , $pFilename); while(in_array('..', $pathArray) && $pathArray[0] != '..') { for ($i = 0; $i < count($pathArray); ++$i) { if ($pathArray[$i] == '..' && $i > 0) { unset($pathArray[$i]); unset($pathArray[$i - 1]); break; } } } $returnValue = implode('/', $pathArray); } // Return return $returnValue; } /** * Get the systems temporary directory. * * @return string */ public static function sys_get_temp_dir() { if (self::$_useUploadTempDirectory) { // use upload-directory when defined to allow running on environments having very restricted // open_basedir configs if (ini_get('upload_tmp_dir') !== FALSE) { if ($temp = ini_get('upload_tmp_dir')) { if (file_exists($temp)) return realpath($temp); } } } // sys_get_temp_dir is only available since PHP 5.2.1 // http://php.net/manual/en/function.sys-get-temp-dir.php#94119 if ( !function_exists('sys_get_temp_dir')) { if ($temp = getenv('TMP') ) { if ((!empty($temp)) && (file_exists($temp))) { return realpath($temp); } } if ($temp = getenv('TEMP') ) { if ((!empty($temp)) && (file_exists($temp))) { return realpath($temp); } } if ($temp = getenv('TMPDIR') ) { if ((!empty($temp)) && (file_exists($temp))) { return realpath($temp); } } // trick for creating a file in system's temporary dir // without knowing the path of the system's temporary dir $temp = tempnam(__FILE__, ''); if (file_exists($temp)) { unlink($temp); return realpath(dirname($temp)); } return null; } // use ordinary built-in PHP function // There should be no problem with the 5.2.4 Suhosin realpath() bug, because this line should only // be called if we're running 5.2.1 or earlier return realpath(sys_get_temp_dir()); } } CholeskyDecomposition.php 3358 1487009049 plugins/wp-all-import-pro/classes/PHPExcel/Shared/JAMA L = $A->getArray(); $this->m = $A->getRowDimension(); for($i = 0; $i < $this->m; ++$i) { for($j = $i; $j < $this->m; ++$j) { for($sum = $this->L[$i][$j], $k = $i - 1; $k >= 0; --$k) { $sum -= $this->L[$i][$k] * $this->L[$j][$k]; } if ($i == $j) { if ($sum >= 0) { $this->L[$i][$i] = sqrt($sum); } else { $this->isspd = false; } } else { if ($this->L[$i][$i] != 0) { $this->L[$j][$i] = $sum / $this->L[$i][$i]; } } } for ($k = $i+1; $k < $this->m; ++$k) { $this->L[$i][$k] = 0.0; } } } else { throw new PHPExcel_Calculation_Exception(JAMAError(ArgumentTypeException)); } } // function __construct() /** * Is the matrix symmetric and positive definite? * * @return boolean */ public function isSPD() { return $this->isspd; } // function isSPD() /** * getL * * Return triangular factor. * @return Matrix Lower triangular matrix */ public function getL() { return new Matrix($this->L); } // function getL() /** * Solve A*X = B * * @param $B Row-equal matrix * @return Matrix L * L' * X = B */ public function solve($B = null) { if ($B instanceof Matrix) { if ($B->getRowDimension() == $this->m) { if ($this->isspd) { $X = $B->getArrayCopy(); $nx = $B->getColumnDimension(); for ($k = 0; $k < $this->m; ++$k) { for ($i = $k + 1; $i < $this->m; ++$i) { for ($j = 0; $j < $nx; ++$j) { $X[$i][$j] -= $X[$k][$j] * $this->L[$i][$k]; } } for ($j = 0; $j < $nx; ++$j) { $X[$k][$j] /= $this->L[$k][$k]; } } for ($k = $this->m - 1; $k >= 0; --$k) { for ($j = 0; $j < $nx; ++$j) { $X[$k][$j] /= $this->L[$k][$k]; } for ($i = 0; $i < $k; ++$i) { for ($j = 0; $j < $nx; ++$j) { $X[$i][$j] -= $X[$k][$j] * $this->L[$k][$i]; } } } return new Matrix($X, $this->m, $nx); } else { throw new PHPExcel_Calculation_Exception(JAMAError(MatrixSPDException)); } } else { throw new PHPExcel_Calculation_Exception(JAMAError(MatrixDimensionException)); } } else { throw new PHPExcel_Calculation_Exception(JAMAError(ArgumentTypeException)); } } // function solve() } // class CholeskyDecomposition EigenvalueDecomposition.php 22708 1487009050 plugins/wp-all-import-pro/classes/PHPExcel/Shared/JAMA d = $this->V[$this->n-1]; // Householder reduction to tridiagonal form. for ($i = $this->n-1; $i > 0; --$i) { $i_ = $i -1; // Scale to avoid under/overflow. $h = $scale = 0.0; $scale += array_sum(array_map(abs, $this->d)); if ($scale == 0.0) { $this->e[$i] = $this->d[$i_]; $this->d = array_slice($this->V[$i_], 0, $i_); for ($j = 0; $j < $i; ++$j) { $this->V[$j][$i] = $this->V[$i][$j] = 0.0; } } else { // Generate Householder vector. for ($k = 0; $k < $i; ++$k) { $this->d[$k] /= $scale; $h += pow($this->d[$k], 2); } $f = $this->d[$i_]; $g = sqrt($h); if ($f > 0) { $g = -$g; } $this->e[$i] = $scale * $g; $h = $h - $f * $g; $this->d[$i_] = $f - $g; for ($j = 0; $j < $i; ++$j) { $this->e[$j] = 0.0; } // Apply similarity transformation to remaining columns. for ($j = 0; $j < $i; ++$j) { $f = $this->d[$j]; $this->V[$j][$i] = $f; $g = $this->e[$j] + $this->V[$j][$j] * $f; for ($k = $j+1; $k <= $i_; ++$k) { $g += $this->V[$k][$j] * $this->d[$k]; $this->e[$k] += $this->V[$k][$j] * $f; } $this->e[$j] = $g; } $f = 0.0; for ($j = 0; $j < $i; ++$j) { $this->e[$j] /= $h; $f += $this->e[$j] * $this->d[$j]; } $hh = $f / (2 * $h); for ($j=0; $j < $i; ++$j) { $this->e[$j] -= $hh * $this->d[$j]; } for ($j = 0; $j < $i; ++$j) { $f = $this->d[$j]; $g = $this->e[$j]; for ($k = $j; $k <= $i_; ++$k) { $this->V[$k][$j] -= ($f * $this->e[$k] + $g * $this->d[$k]); } $this->d[$j] = $this->V[$i-1][$j]; $this->V[$i][$j] = 0.0; } } $this->d[$i] = $h; } // Accumulate transformations. for ($i = 0; $i < $this->n-1; ++$i) { $this->V[$this->n-1][$i] = $this->V[$i][$i]; $this->V[$i][$i] = 1.0; $h = $this->d[$i+1]; if ($h != 0.0) { for ($k = 0; $k <= $i; ++$k) { $this->d[$k] = $this->V[$k][$i+1] / $h; } for ($j = 0; $j <= $i; ++$j) { $g = 0.0; for ($k = 0; $k <= $i; ++$k) { $g += $this->V[$k][$i+1] * $this->V[$k][$j]; } for ($k = 0; $k <= $i; ++$k) { $this->V[$k][$j] -= $g * $this->d[$k]; } } } for ($k = 0; $k <= $i; ++$k) { $this->V[$k][$i+1] = 0.0; } } $this->d = $this->V[$this->n-1]; $this->V[$this->n-1] = array_fill(0, $j, 0.0); $this->V[$this->n-1][$this->n-1] = 1.0; $this->e[0] = 0.0; } /** * Symmetric tridiagonal QL algorithm. * * This is derived from the Algol procedures tql2, by * Bowdler, Martin, Reinsch, and Wilkinson, Handbook for * Auto. Comp., Vol.ii-Linear Algebra, and the corresponding * Fortran subroutine in EISPACK. * * @access private */ private function tql2() { for ($i = 1; $i < $this->n; ++$i) { $this->e[$i-1] = $this->e[$i]; } $this->e[$this->n-1] = 0.0; $f = 0.0; $tst1 = 0.0; $eps = pow(2.0,-52.0); for ($l = 0; $l < $this->n; ++$l) { // Find small subdiagonal element $tst1 = max($tst1, abs($this->d[$l]) + abs($this->e[$l])); $m = $l; while ($m < $this->n) { if (abs($this->e[$m]) <= $eps * $tst1) break; ++$m; } // If m == l, $this->d[l] is an eigenvalue, // otherwise, iterate. if ($m > $l) { $iter = 0; do { // Could check iteration count here. $iter += 1; // Compute implicit shift $g = $this->d[$l]; $p = ($this->d[$l+1] - $g) / (2.0 * $this->e[$l]); $r = hypo($p, 1.0); if ($p < 0) $r *= -1; $this->d[$l] = $this->e[$l] / ($p + $r); $this->d[$l+1] = $this->e[$l] * ($p + $r); $dl1 = $this->d[$l+1]; $h = $g - $this->d[$l]; for ($i = $l + 2; $i < $this->n; ++$i) $this->d[$i] -= $h; $f += $h; // Implicit QL transformation. $p = $this->d[$m]; $c = 1.0; $c2 = $c3 = $c; $el1 = $this->e[$l + 1]; $s = $s2 = 0.0; for ($i = $m-1; $i >= $l; --$i) { $c3 = $c2; $c2 = $c; $s2 = $s; $g = $c * $this->e[$i]; $h = $c * $p; $r = hypo($p, $this->e[$i]); $this->e[$i+1] = $s * $r; $s = $this->e[$i] / $r; $c = $p / $r; $p = $c * $this->d[$i] - $s * $g; $this->d[$i+1] = $h + $s * ($c * $g + $s * $this->d[$i]); // Accumulate transformation. for ($k = 0; $k < $this->n; ++$k) { $h = $this->V[$k][$i+1]; $this->V[$k][$i+1] = $s * $this->V[$k][$i] + $c * $h; $this->V[$k][$i] = $c * $this->V[$k][$i] - $s * $h; } } $p = -$s * $s2 * $c3 * $el1 * $this->e[$l] / $dl1; $this->e[$l] = $s * $p; $this->d[$l] = $c * $p; // Check for convergence. } while (abs($this->e[$l]) > $eps * $tst1); } $this->d[$l] = $this->d[$l] + $f; $this->e[$l] = 0.0; } // Sort eigenvalues and corresponding vectors. for ($i = 0; $i < $this->n - 1; ++$i) { $k = $i; $p = $this->d[$i]; for ($j = $i+1; $j < $this->n; ++$j) { if ($this->d[$j] < $p) { $k = $j; $p = $this->d[$j]; } } if ($k != $i) { $this->d[$k] = $this->d[$i]; $this->d[$i] = $p; for ($j = 0; $j < $this->n; ++$j) { $p = $this->V[$j][$i]; $this->V[$j][$i] = $this->V[$j][$k]; $this->V[$j][$k] = $p; } } } } /** * Nonsymmetric reduction to Hessenberg form. * * This is derived from the Algol procedures orthes and ortran, * by Martin and Wilkinson, Handbook for Auto. Comp., * Vol.ii-Linear Algebra, and the corresponding * Fortran subroutines in EISPACK. * * @access private */ private function orthes () { $low = 0; $high = $this->n-1; for ($m = $low+1; $m <= $high-1; ++$m) { // Scale column. $scale = 0.0; for ($i = $m; $i <= $high; ++$i) { $scale = $scale + abs($this->H[$i][$m-1]); } if ($scale != 0.0) { // Compute Householder transformation. $h = 0.0; for ($i = $high; $i >= $m; --$i) { $this->ort[$i] = $this->H[$i][$m-1] / $scale; $h += $this->ort[$i] * $this->ort[$i]; } $g = sqrt($h); if ($this->ort[$m] > 0) { $g *= -1; } $h -= $this->ort[$m] * $g; $this->ort[$m] -= $g; // Apply Householder similarity transformation // H = (I -u * u' / h) * H * (I -u * u') / h) for ($j = $m; $j < $this->n; ++$j) { $f = 0.0; for ($i = $high; $i >= $m; --$i) { $f += $this->ort[$i] * $this->H[$i][$j]; } $f /= $h; for ($i = $m; $i <= $high; ++$i) { $this->H[$i][$j] -= $f * $this->ort[$i]; } } for ($i = 0; $i <= $high; ++$i) { $f = 0.0; for ($j = $high; $j >= $m; --$j) { $f += $this->ort[$j] * $this->H[$i][$j]; } $f = $f / $h; for ($j = $m; $j <= $high; ++$j) { $this->H[$i][$j] -= $f * $this->ort[$j]; } } $this->ort[$m] = $scale * $this->ort[$m]; $this->H[$m][$m-1] = $scale * $g; } } // Accumulate transformations (Algol's ortran). for ($i = 0; $i < $this->n; ++$i) { for ($j = 0; $j < $this->n; ++$j) { $this->V[$i][$j] = ($i == $j ? 1.0 : 0.0); } } for ($m = $high-1; $m >= $low+1; --$m) { if ($this->H[$m][$m-1] != 0.0) { for ($i = $m+1; $i <= $high; ++$i) { $this->ort[$i] = $this->H[$i][$m-1]; } for ($j = $m; $j <= $high; ++$j) { $g = 0.0; for ($i = $m; $i <= $high; ++$i) { $g += $this->ort[$i] * $this->V[$i][$j]; } // Double division avoids possible underflow $g = ($g / $this->ort[$m]) / $this->H[$m][$m-1]; for ($i = $m; $i <= $high; ++$i) { $this->V[$i][$j] += $g * $this->ort[$i]; } } } } } /** * Performs complex division. * * @access private */ private function cdiv($xr, $xi, $yr, $yi) { if (abs($yr) > abs($yi)) { $r = $yi / $yr; $d = $yr + $r * $yi; $this->cdivr = ($xr + $r * $xi) / $d; $this->cdivi = ($xi - $r * $xr) / $d; } else { $r = $yr / $yi; $d = $yi + $r * $yr; $this->cdivr = ($r * $xr + $xi) / $d; $this->cdivi = ($r * $xi - $xr) / $d; } } /** * Nonsymmetric reduction from Hessenberg to real Schur form. * * Code is derived from the Algol procedure hqr2, * by Martin and Wilkinson, Handbook for Auto. Comp., * Vol.ii-Linear Algebra, and the corresponding * Fortran subroutine in EISPACK. * * @access private */ private function hqr2 () { // Initialize $nn = $this->n; $n = $nn - 1; $low = 0; $high = $nn - 1; $eps = pow(2.0, -52.0); $exshift = 0.0; $p = $q = $r = $s = $z = 0; // Store roots isolated by balanc and compute matrix norm $norm = 0.0; for ($i = 0; $i < $nn; ++$i) { if (($i < $low) OR ($i > $high)) { $this->d[$i] = $this->H[$i][$i]; $this->e[$i] = 0.0; } for ($j = max($i-1, 0); $j < $nn; ++$j) { $norm = $norm + abs($this->H[$i][$j]); } } // Outer loop over eigenvalue index $iter = 0; while ($n >= $low) { // Look for single small sub-diagonal element $l = $n; while ($l > $low) { $s = abs($this->H[$l-1][$l-1]) + abs($this->H[$l][$l]); if ($s == 0.0) { $s = $norm; } if (abs($this->H[$l][$l-1]) < $eps * $s) { break; } --$l; } // Check for convergence // One root found if ($l == $n) { $this->H[$n][$n] = $this->H[$n][$n] + $exshift; $this->d[$n] = $this->H[$n][$n]; $this->e[$n] = 0.0; --$n; $iter = 0; // Two roots found } else if ($l == $n-1) { $w = $this->H[$n][$n-1] * $this->H[$n-1][$n]; $p = ($this->H[$n-1][$n-1] - $this->H[$n][$n]) / 2.0; $q = $p * $p + $w; $z = sqrt(abs($q)); $this->H[$n][$n] = $this->H[$n][$n] + $exshift; $this->H[$n-1][$n-1] = $this->H[$n-1][$n-1] + $exshift; $x = $this->H[$n][$n]; // Real pair if ($q >= 0) { if ($p >= 0) { $z = $p + $z; } else { $z = $p - $z; } $this->d[$n-1] = $x + $z; $this->d[$n] = $this->d[$n-1]; if ($z != 0.0) { $this->d[$n] = $x - $w / $z; } $this->e[$n-1] = 0.0; $this->e[$n] = 0.0; $x = $this->H[$n][$n-1]; $s = abs($x) + abs($z); $p = $x / $s; $q = $z / $s; $r = sqrt($p * $p + $q * $q); $p = $p / $r; $q = $q / $r; // Row modification for ($j = $n-1; $j < $nn; ++$j) { $z = $this->H[$n-1][$j]; $this->H[$n-1][$j] = $q * $z + $p * $this->H[$n][$j]; $this->H[$n][$j] = $q * $this->H[$n][$j] - $p * $z; } // Column modification for ($i = 0; $i <= n; ++$i) { $z = $this->H[$i][$n-1]; $this->H[$i][$n-1] = $q * $z + $p * $this->H[$i][$n]; $this->H[$i][$n] = $q * $this->H[$i][$n] - $p * $z; } // Accumulate transformations for ($i = $low; $i <= $high; ++$i) { $z = $this->V[$i][$n-1]; $this->V[$i][$n-1] = $q * $z + $p * $this->V[$i][$n]; $this->V[$i][$n] = $q * $this->V[$i][$n] - $p * $z; } // Complex pair } else { $this->d[$n-1] = $x + $p; $this->d[$n] = $x + $p; $this->e[$n-1] = $z; $this->e[$n] = -$z; } $n = $n - 2; $iter = 0; // No convergence yet } else { // Form shift $x = $this->H[$n][$n]; $y = 0.0; $w = 0.0; if ($l < $n) { $y = $this->H[$n-1][$n-1]; $w = $this->H[$n][$n-1] * $this->H[$n-1][$n]; } // Wilkinson's original ad hoc shift if ($iter == 10) { $exshift += $x; for ($i = $low; $i <= $n; ++$i) { $this->H[$i][$i] -= $x; } $s = abs($this->H[$n][$n-1]) + abs($this->H[$n-1][$n-2]); $x = $y = 0.75 * $s; $w = -0.4375 * $s * $s; } // MATLAB's new ad hoc shift if ($iter == 30) { $s = ($y - $x) / 2.0; $s = $s * $s + $w; if ($s > 0) { $s = sqrt($s); if ($y < $x) { $s = -$s; } $s = $x - $w / (($y - $x) / 2.0 + $s); for ($i = $low; $i <= $n; ++$i) { $this->H[$i][$i] -= $s; } $exshift += $s; $x = $y = $w = 0.964; } } // Could check iteration count here. $iter = $iter + 1; // Look for two consecutive small sub-diagonal elements $m = $n - 2; while ($m >= $l) { $z = $this->H[$m][$m]; $r = $x - $z; $s = $y - $z; $p = ($r * $s - $w) / $this->H[$m+1][$m] + $this->H[$m][$m+1]; $q = $this->H[$m+1][$m+1] - $z - $r - $s; $r = $this->H[$m+2][$m+1]; $s = abs($p) + abs($q) + abs($r); $p = $p / $s; $q = $q / $s; $r = $r / $s; if ($m == $l) { break; } if (abs($this->H[$m][$m-1]) * (abs($q) + abs($r)) < $eps * (abs($p) * (abs($this->H[$m-1][$m-1]) + abs($z) + abs($this->H[$m+1][$m+1])))) { break; } --$m; } for ($i = $m + 2; $i <= $n; ++$i) { $this->H[$i][$i-2] = 0.0; if ($i > $m+2) { $this->H[$i][$i-3] = 0.0; } } // Double QR step involving rows l:n and columns m:n for ($k = $m; $k <= $n-1; ++$k) { $notlast = ($k != $n-1); if ($k != $m) { $p = $this->H[$k][$k-1]; $q = $this->H[$k+1][$k-1]; $r = ($notlast ? $this->H[$k+2][$k-1] : 0.0); $x = abs($p) + abs($q) + abs($r); if ($x != 0.0) { $p = $p / $x; $q = $q / $x; $r = $r / $x; } } if ($x == 0.0) { break; } $s = sqrt($p * $p + $q * $q + $r * $r); if ($p < 0) { $s = -$s; } if ($s != 0) { if ($k != $m) { $this->H[$k][$k-1] = -$s * $x; } elseif ($l != $m) { $this->H[$k][$k-1] = -$this->H[$k][$k-1]; } $p = $p + $s; $x = $p / $s; $y = $q / $s; $z = $r / $s; $q = $q / $p; $r = $r / $p; // Row modification for ($j = $k; $j < $nn; ++$j) { $p = $this->H[$k][$j] + $q * $this->H[$k+1][$j]; if ($notlast) { $p = $p + $r * $this->H[$k+2][$j]; $this->H[$k+2][$j] = $this->H[$k+2][$j] - $p * $z; } $this->H[$k][$j] = $this->H[$k][$j] - $p * $x; $this->H[$k+1][$j] = $this->H[$k+1][$j] - $p * $y; } // Column modification for ($i = 0; $i <= min($n, $k+3); ++$i) { $p = $x * $this->H[$i][$k] + $y * $this->H[$i][$k+1]; if ($notlast) { $p = $p + $z * $this->H[$i][$k+2]; $this->H[$i][$k+2] = $this->H[$i][$k+2] - $p * $r; } $this->H[$i][$k] = $this->H[$i][$k] - $p; $this->H[$i][$k+1] = $this->H[$i][$k+1] - $p * $q; } // Accumulate transformations for ($i = $low; $i <= $high; ++$i) { $p = $x * $this->V[$i][$k] + $y * $this->V[$i][$k+1]; if ($notlast) { $p = $p + $z * $this->V[$i][$k+2]; $this->V[$i][$k+2] = $this->V[$i][$k+2] - $p * $r; } $this->V[$i][$k] = $this->V[$i][$k] - $p; $this->V[$i][$k+1] = $this->V[$i][$k+1] - $p * $q; } } // ($s != 0) } // k loop } // check convergence } // while ($n >= $low) // Backsubstitute to find vectors of upper triangular form if ($norm == 0.0) { return; } for ($n = $nn-1; $n >= 0; --$n) { $p = $this->d[$n]; $q = $this->e[$n]; // Real vector if ($q == 0) { $l = $n; $this->H[$n][$n] = 1.0; for ($i = $n-1; $i >= 0; --$i) { $w = $this->H[$i][$i] - $p; $r = 0.0; for ($j = $l; $j <= $n; ++$j) { $r = $r + $this->H[$i][$j] * $this->H[$j][$n]; } if ($this->e[$i] < 0.0) { $z = $w; $s = $r; } else { $l = $i; if ($this->e[$i] == 0.0) { if ($w != 0.0) { $this->H[$i][$n] = -$r / $w; } else { $this->H[$i][$n] = -$r / ($eps * $norm); } // Solve real equations } else { $x = $this->H[$i][$i+1]; $y = $this->H[$i+1][$i]; $q = ($this->d[$i] - $p) * ($this->d[$i] - $p) + $this->e[$i] * $this->e[$i]; $t = ($x * $s - $z * $r) / $q; $this->H[$i][$n] = $t; if (abs($x) > abs($z)) { $this->H[$i+1][$n] = (-$r - $w * $t) / $x; } else { $this->H[$i+1][$n] = (-$s - $y * $t) / $z; } } // Overflow control $t = abs($this->H[$i][$n]); if (($eps * $t) * $t > 1) { for ($j = $i; $j <= $n; ++$j) { $this->H[$j][$n] = $this->H[$j][$n] / $t; } } } } // Complex vector } else if ($q < 0) { $l = $n-1; // Last vector component imaginary so matrix is triangular if (abs($this->H[$n][$n-1]) > abs($this->H[$n-1][$n])) { $this->H[$n-1][$n-1] = $q / $this->H[$n][$n-1]; $this->H[$n-1][$n] = -($this->H[$n][$n] - $p) / $this->H[$n][$n-1]; } else { $this->cdiv(0.0, -$this->H[$n-1][$n], $this->H[$n-1][$n-1] - $p, $q); $this->H[$n-1][$n-1] = $this->cdivr; $this->H[$n-1][$n] = $this->cdivi; } $this->H[$n][$n-1] = 0.0; $this->H[$n][$n] = 1.0; for ($i = $n-2; $i >= 0; --$i) { // double ra,sa,vr,vi; $ra = 0.0; $sa = 0.0; for ($j = $l; $j <= $n; ++$j) { $ra = $ra + $this->H[$i][$j] * $this->H[$j][$n-1]; $sa = $sa + $this->H[$i][$j] * $this->H[$j][$n]; } $w = $this->H[$i][$i] - $p; if ($this->e[$i] < 0.0) { $z = $w; $r = $ra; $s = $sa; } else { $l = $i; if ($this->e[$i] == 0) { $this->cdiv(-$ra, -$sa, $w, $q); $this->H[$i][$n-1] = $this->cdivr; $this->H[$i][$n] = $this->cdivi; } else { // Solve complex equations $x = $this->H[$i][$i+1]; $y = $this->H[$i+1][$i]; $vr = ($this->d[$i] - $p) * ($this->d[$i] - $p) + $this->e[$i] * $this->e[$i] - $q * $q; $vi = ($this->d[$i] - $p) * 2.0 * $q; if ($vr == 0.0 & $vi == 0.0) { $vr = $eps * $norm * (abs($w) + abs($q) + abs($x) + abs($y) + abs($z)); } $this->cdiv($x * $r - $z * $ra + $q * $sa, $x * $s - $z * $sa - $q * $ra, $vr, $vi); $this->H[$i][$n-1] = $this->cdivr; $this->H[$i][$n] = $this->cdivi; if (abs($x) > (abs($z) + abs($q))) { $this->H[$i+1][$n-1] = (-$ra - $w * $this->H[$i][$n-1] + $q * $this->H[$i][$n]) / $x; $this->H[$i+1][$n] = (-$sa - $w * $this->H[$i][$n] - $q * $this->H[$i][$n-1]) / $x; } else { $this->cdiv(-$r - $y * $this->H[$i][$n-1], -$s - $y * $this->H[$i][$n], $z, $q); $this->H[$i+1][$n-1] = $this->cdivr; $this->H[$i+1][$n] = $this->cdivi; } } // Overflow control $t = max(abs($this->H[$i][$n-1]),abs($this->H[$i][$n])); if (($eps * $t) * $t > 1) { for ($j = $i; $j <= $n; ++$j) { $this->H[$j][$n-1] = $this->H[$j][$n-1] / $t; $this->H[$j][$n] = $this->H[$j][$n] / $t; } } } // end else } // end for } // end else for complex case } // end for // Vectors of isolated roots for ($i = 0; $i < $nn; ++$i) { if ($i < $low | $i > $high) { for ($j = $i; $j < $nn; ++$j) { $this->V[$i][$j] = $this->H[$i][$j]; } } } // Back transformation to get eigenvectors of original matrix for ($j = $nn-1; $j >= $low; --$j) { for ($i = $low; $i <= $high; ++$i) { $z = 0.0; for ($k = $low; $k <= min($j,$high); ++$k) { $z = $z + $this->V[$i][$k] * $this->H[$k][$j]; } $this->V[$i][$j] = $z; } } } // end hqr2 /** * Constructor: Check for symmetry, then construct the eigenvalue decomposition * * @access public * @param A Square matrix * @return Structure to access D and V. */ public function __construct($Arg) { $this->A = $Arg->getArray(); $this->n = $Arg->getColumnDimension(); $issymmetric = true; for ($j = 0; ($j < $this->n) & $issymmetric; ++$j) { for ($i = 0; ($i < $this->n) & $issymmetric; ++$i) { $issymmetric = ($this->A[$i][$j] == $this->A[$j][$i]); } } if ($issymmetric) { $this->V = $this->A; // Tridiagonalize. $this->tred2(); // Diagonalize. $this->tql2(); } else { $this->H = $this->A; $this->ort = array(); // Reduce to Hessenberg form. $this->orthes(); // Reduce Hessenberg to real Schur form. $this->hqr2(); } } /** * Return the eigenvector matrix * * @access public * @return V */ public function getV() { return new Matrix($this->V, $this->n, $this->n); } /** * Return the real parts of the eigenvalues * * @access public * @return real(diag(D)) */ public function getRealEigenvalues() { return $this->d; } /** * Return the imaginary parts of the eigenvalues * * @access public * @return imag(diag(D)) */ public function getImagEigenvalues() { return $this->e; } /** * Return the block diagonal eigenvalue matrix * * @access public * @return D */ public function getD() { for ($i = 0; $i < $this->n; ++$i) { $D[$i] = array_fill(0, $this->n, 0.0); $D[$i][$i] = $this->d[$i]; if ($this->e[$i] == 0) { continue; } $o = ($this->e[$i] > 0) ? $i + 1 : $i - 1; $D[$i][$o] = $this->e[$i]; } return new Matrix($D); } } // class EigenvalueDecomposition SingularValueDecomposition.php 13005 1487009052 plugins/wp-all-import-pro/classes/PHPExcel/Shared/JAMA = n, the singular value decomposition is * an m-by-n orthogonal matrix U, an n-by-n diagonal matrix S, and * an n-by-n orthogonal matrix V so that A = U*S*V'. * * The singular values, sigma[$k] = S[$k][$k], are ordered so that * sigma[0] >= sigma[1] >= ... >= sigma[n-1]. * * The singular value decompostion always exists, so the constructor will * never fail. The matrix condition number and the effective numerical * rank can be computed from this decomposition. * * @author Paul Meagher * @license PHP v3.0 * @version 1.1 */ class SingularValueDecomposition { /** * Internal storage of U. * @var array */ private $U = array(); /** * Internal storage of V. * @var array */ private $V = array(); /** * Internal storage of singular values. * @var array */ private $s = array(); /** * Row dimension. * @var int */ private $m; /** * Column dimension. * @var int */ private $n; /** * Construct the singular value decomposition * * Derived from LINPACK code. * * @param $A Rectangular matrix * @return Structure to access U, S and V. */ public function __construct($Arg) { // Initialize. $A = $Arg->getArrayCopy(); $this->m = $Arg->getRowDimension(); $this->n = $Arg->getColumnDimension(); $nu = min($this->m, $this->n); $e = array(); $work = array(); $wantu = true; $wantv = true; $nct = min($this->m - 1, $this->n); $nrt = max(0, min($this->n - 2, $this->m)); // Reduce A to bidiagonal form, storing the diagonal elements // in s and the super-diagonal elements in e. for ($k = 0; $k < max($nct,$nrt); ++$k) { if ($k < $nct) { // Compute the transformation for the k-th column and // place the k-th diagonal in s[$k]. // Compute 2-norm of k-th column without under/overflow. $this->s[$k] = 0; for ($i = $k; $i < $this->m; ++$i) { $this->s[$k] = hypo($this->s[$k], $A[$i][$k]); } if ($this->s[$k] != 0.0) { if ($A[$k][$k] < 0.0) { $this->s[$k] = -$this->s[$k]; } for ($i = $k; $i < $this->m; ++$i) { $A[$i][$k] /= $this->s[$k]; } $A[$k][$k] += 1.0; } $this->s[$k] = -$this->s[$k]; } for ($j = $k + 1; $j < $this->n; ++$j) { if (($k < $nct) & ($this->s[$k] != 0.0)) { // Apply the transformation. $t = 0; for ($i = $k; $i < $this->m; ++$i) { $t += $A[$i][$k] * $A[$i][$j]; } $t = -$t / $A[$k][$k]; for ($i = $k; $i < $this->m; ++$i) { $A[$i][$j] += $t * $A[$i][$k]; } // Place the k-th row of A into e for the // subsequent calculation of the row transformation. $e[$j] = $A[$k][$j]; } } if ($wantu AND ($k < $nct)) { // Place the transformation in U for subsequent back // multiplication. for ($i = $k; $i < $this->m; ++$i) { $this->U[$i][$k] = $A[$i][$k]; } } if ($k < $nrt) { // Compute the k-th row transformation and place the // k-th super-diagonal in e[$k]. // Compute 2-norm without under/overflow. $e[$k] = 0; for ($i = $k + 1; $i < $this->n; ++$i) { $e[$k] = hypo($e[$k], $e[$i]); } if ($e[$k] != 0.0) { if ($e[$k+1] < 0.0) { $e[$k] = -$e[$k]; } for ($i = $k + 1; $i < $this->n; ++$i) { $e[$i] /= $e[$k]; } $e[$k+1] += 1.0; } $e[$k] = -$e[$k]; if (($k+1 < $this->m) AND ($e[$k] != 0.0)) { // Apply the transformation. for ($i = $k+1; $i < $this->m; ++$i) { $work[$i] = 0.0; } for ($j = $k+1; $j < $this->n; ++$j) { for ($i = $k+1; $i < $this->m; ++$i) { $work[$i] += $e[$j] * $A[$i][$j]; } } for ($j = $k + 1; $j < $this->n; ++$j) { $t = -$e[$j] / $e[$k+1]; for ($i = $k + 1; $i < $this->m; ++$i) { $A[$i][$j] += $t * $work[$i]; } } } if ($wantv) { // Place the transformation in V for subsequent // back multiplication. for ($i = $k + 1; $i < $this->n; ++$i) { $this->V[$i][$k] = $e[$i]; } } } } // Set up the final bidiagonal matrix or order p. $p = min($this->n, $this->m + 1); if ($nct < $this->n) { $this->s[$nct] = $A[$nct][$nct]; } if ($this->m < $p) { $this->s[$p-1] = 0.0; } if ($nrt + 1 < $p) { $e[$nrt] = $A[$nrt][$p-1]; } $e[$p-1] = 0.0; // If required, generate U. if ($wantu) { for ($j = $nct; $j < $nu; ++$j) { for ($i = 0; $i < $this->m; ++$i) { $this->U[$i][$j] = 0.0; } $this->U[$j][$j] = 1.0; } for ($k = $nct - 1; $k >= 0; --$k) { if ($this->s[$k] != 0.0) { for ($j = $k + 1; $j < $nu; ++$j) { $t = 0; for ($i = $k; $i < $this->m; ++$i) { $t += $this->U[$i][$k] * $this->U[$i][$j]; } $t = -$t / $this->U[$k][$k]; for ($i = $k; $i < $this->m; ++$i) { $this->U[$i][$j] += $t * $this->U[$i][$k]; } } for ($i = $k; $i < $this->m; ++$i ) { $this->U[$i][$k] = -$this->U[$i][$k]; } $this->U[$k][$k] = 1.0 + $this->U[$k][$k]; for ($i = 0; $i < $k - 1; ++$i) { $this->U[$i][$k] = 0.0; } } else { for ($i = 0; $i < $this->m; ++$i) { $this->U[$i][$k] = 0.0; } $this->U[$k][$k] = 1.0; } } } // If required, generate V. if ($wantv) { for ($k = $this->n - 1; $k >= 0; --$k) { if (($k < $nrt) AND ($e[$k] != 0.0)) { for ($j = $k + 1; $j < $nu; ++$j) { $t = 0; for ($i = $k + 1; $i < $this->n; ++$i) { $t += $this->V[$i][$k]* $this->V[$i][$j]; } $t = -$t / $this->V[$k+1][$k]; for ($i = $k + 1; $i < $this->n; ++$i) { $this->V[$i][$j] += $t * $this->V[$i][$k]; } } } for ($i = 0; $i < $this->n; ++$i) { $this->V[$i][$k] = 0.0; } $this->V[$k][$k] = 1.0; } } // Main iteration loop for the singular values. $pp = $p - 1; $iter = 0; $eps = pow(2.0, -52.0); while ($p > 0) { // Here is where a test for too many iterations would go. // This section of the program inspects for negligible // elements in the s and e arrays. On completion the // variables kase and k are set as follows: // kase = 1 if s(p) and e[k-1] are negligible and k
= -1; --$k) { if ($k == -1) { break; } if (abs($e[$k]) <= $eps * (abs($this->s[$k]) + abs($this->s[$k+1]))) { $e[$k] = 0.0; break; } } if ($k == $p - 2) { $kase = 4; } else { for ($ks = $p - 1; $ks >= $k; --$ks) { if ($ks == $k) { break; } $t = ($ks != $p ? abs($e[$ks]) : 0.) + ($ks != $k + 1 ? abs($e[$ks-1]) : 0.); if (abs($this->s[$ks]) <= $eps * $t) { $this->s[$ks] = 0.0; break; } } if ($ks == $k) { $kase = 3; } else if ($ks == $p-1) { $kase = 1; } else { $kase = 2; $k = $ks; } } ++$k; // Perform the task indicated by kase. switch ($kase) { // Deflate negligible s(p). case 1: $f = $e[$p-2]; $e[$p-2] = 0.0; for ($j = $p - 2; $j >= $k; --$j) { $t = hypo($this->s[$j],$f); $cs = $this->s[$j] / $t; $sn = $f / $t; $this->s[$j] = $t; if ($j != $k) { $f = -$sn * $e[$j-1]; $e[$j-1] = $cs * $e[$j-1]; } if ($wantv) { for ($i = 0; $i < $this->n; ++$i) { $t = $cs * $this->V[$i][$j] + $sn * $this->V[$i][$p-1]; $this->V[$i][$p-1] = -$sn * $this->V[$i][$j] + $cs * $this->V[$i][$p-1]; $this->V[$i][$j] = $t; } } } break; // Split at negligible s(k). case 2: $f = $e[$k-1]; $e[$k-1] = 0.0; for ($j = $k; $j < $p; ++$j) { $t = hypo($this->s[$j], $f); $cs = $this->s[$j] / $t; $sn = $f / $t; $this->s[$j] = $t; $f = -$sn * $e[$j]; $e[$j] = $cs * $e[$j]; if ($wantu) { for ($i = 0; $i < $this->m; ++$i) { $t = $cs * $this->U[$i][$j] + $sn * $this->U[$i][$k-1]; $this->U[$i][$k-1] = -$sn * $this->U[$i][$j] + $cs * $this->U[$i][$k-1]; $this->U[$i][$j] = $t; } } } break; // Perform one qr step. case 3: // Calculate the shift. $scale = max(max(max(max( abs($this->s[$p-1]),abs($this->s[$p-2])),abs($e[$p-2])), abs($this->s[$k])), abs($e[$k])); $sp = $this->s[$p-1] / $scale; $spm1 = $this->s[$p-2] / $scale; $epm1 = $e[$p-2] / $scale; $sk = $this->s[$k] / $scale; $ek = $e[$k] / $scale; $b = (($spm1 + $sp) * ($spm1 - $sp) + $epm1 * $epm1) / 2.0; $c = ($sp * $epm1) * ($sp * $epm1); $shift = 0.0; if (($b != 0.0) || ($c != 0.0)) { $shift = sqrt($b * $b + $c); if ($b < 0.0) { $shift = -$shift; } $shift = $c / ($b + $shift); } $f = ($sk + $sp) * ($sk - $sp) + $shift; $g = $sk * $ek; // Chase zeros. for ($j = $k; $j < $p-1; ++$j) { $t = hypo($f,$g); $cs = $f/$t; $sn = $g/$t; if ($j != $k) { $e[$j-1] = $t; } $f = $cs * $this->s[$j] + $sn * $e[$j]; $e[$j] = $cs * $e[$j] - $sn * $this->s[$j]; $g = $sn * $this->s[$j+1]; $this->s[$j+1] = $cs * $this->s[$j+1]; if ($wantv) { for ($i = 0; $i < $this->n; ++$i) { $t = $cs * $this->V[$i][$j] + $sn * $this->V[$i][$j+1]; $this->V[$i][$j+1] = -$sn * $this->V[$i][$j] + $cs * $this->V[$i][$j+1]; $this->V[$i][$j] = $t; } } $t = hypo($f,$g); $cs = $f/$t; $sn = $g/$t; $this->s[$j] = $t; $f = $cs * $e[$j] + $sn * $this->s[$j+1]; $this->s[$j+1] = -$sn * $e[$j] + $cs * $this->s[$j+1]; $g = $sn * $e[$j+1]; $e[$j+1] = $cs * $e[$j+1]; if ($wantu && ($j < $this->m - 1)) { for ($i = 0; $i < $this->m; ++$i) { $t = $cs * $this->U[$i][$j] + $sn * $this->U[$i][$j+1]; $this->U[$i][$j+1] = -$sn * $this->U[$i][$j] + $cs * $this->U[$i][$j+1]; $this->U[$i][$j] = $t; } } } $e[$p-2] = $f; $iter = $iter + 1; break; // Convergence. case 4: // Make the singular values positive. if ($this->s[$k] <= 0.0) { $this->s[$k] = ($this->s[$k] < 0.0 ? -$this->s[$k] : 0.0); if ($wantv) { for ($i = 0; $i <= $pp; ++$i) { $this->V[$i][$k] = -$this->V[$i][$k]; } } } // Order the singular values. while ($k < $pp) { if ($this->s[$k] >= $this->s[$k+1]) { break; } $t = $this->s[$k]; $this->s[$k] = $this->s[$k+1]; $this->s[$k+1] = $t; if ($wantv AND ($k < $this->n - 1)) { for ($i = 0; $i < $this->n; ++$i) { $t = $this->V[$i][$k+1]; $this->V[$i][$k+1] = $this->V[$i][$k]; $this->V[$i][$k] = $t; } } if ($wantu AND ($k < $this->m-1)) { for ($i = 0; $i < $this->m; ++$i) { $t = $this->U[$i][$k+1]; $this->U[$i][$k+1] = $this->U[$i][$k]; $this->U[$i][$k] = $t; } } ++$k; } $iter = 0; --$p; break; } // end switch } // end while } // end constructor /** * Return the left singular vectors * * @access public * @return U */ public function getU() { return new Matrix($this->U, $this->m, min($this->m + 1, $this->n)); } /** * Return the right singular vectors * * @access public * @return V */ public function getV() { return new Matrix($this->V); } /** * Return the one-dimensional array of singular values * * @access public * @return diagonal of S. */ public function getSingularValues() { return $this->s; } /** * Return the diagonal matrix of singular values * * @access public * @return S */ public function getS() { for ($i = 0; $i < $this->n; ++$i) { for ($j = 0; $j < $this->n; ++$j) { $S[$i][$j] = 0.0; } $S[$i][$i] = $this->s[$i]; } return new Matrix($S); } /** * Two norm * * @access public * @return max(S) */ public function norm2() { return $this->s[0]; } /** * Two norm condition number * * @access public * @return max(S)/min(S) */ public function cond() { return $this->s[0] / $this->s[min($this->m, $this->n) - 1]; } /** * Effective numerical matrix rank * * @access public * @return Number of nonnegligible singular values. */ public function rank() { $eps = pow(2.0, -52.0); $tol = max($this->m, $this->n) * $this->s[0] * $eps; $r = 0; for ($i = 0; $i < count($this->s); ++$i) { if ($this->s[$i] > $tol) { ++$r; } } return $r; } } // class SingularValueDecomposition CHANGELOG.TXT 500 1487009049 plugins/wp-all-import-pro/classes/PHPExcel/Shared/JAMA Mar 1, 2005 11:15 AST by PM + For consistency, renamed Math.php to Maths.java, utils to util, tests to test, docs to doc - + Removed conditional logic from top of Matrix class. + Switched to using hypo function in Maths.php for all php-hypot calls. NOTE TO SELF: Need to make sure that all decompositions have been switched over to using the bundled hypo. Feb 25, 2005 at 10:00 AST by PM + Recommend using simpler Error.php instead of JAMA_Error.php but can be persuaded otherwise. Maths.php 711 1487009075 plugins/wp-all-import-pro/classes/PHPExcel/Shared/JAMA/utils abs($b)) { $r = $b / $a; $r = abs($a) * sqrt(1 + $r * $r); } elseif ($b != 0) { $r = $a / $b; $r = abs($b) * sqrt(1 + $r * $r); } else { $r = 0.0; } return $r; } // function hypo() /** * Mike Bommarito's version. * Compute n-dimensional hyotheneuse. * function hypot() { $s = 0; foreach (func_get_args() as $d) { if (is_numeric($d)) { $s += pow($d, 2); } else { throw new PHPExcel_Calculation_Exception(JAMAError(ArgumentTypeException)); } } return sqrt($s); } */ Error.php 3031 1487009075 plugins/wp-all-import-pro/classes/PHPExcel/Shared/JAMA/utils