PHP PHPExcel_Worksheet::getCell方法代码示例

 /**
  * Insert a new column, updating all possible related data
  *
  * @param	int	$pBefore	Insert before this one
  * @param	int	$pNumCols	Number of columns to insert
  * @param	int	$pNumRows	Number of rows to insert
  * @throws	Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
 {
     // Get a copy of the cell collection
     /*$aTemp = $pSheet->getCellCollection();
     		$aCellCollection = array();
     		foreach ($aTemp as $key => $value) {
     			$aCellCollection[$key] = clone $value;
     		}*/
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     while ($cell = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             $cell->setXfIndex(0);
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->setCellValue($newCoordinates, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows));
             } else {
                 // Formula should not be adjusted
                 $pSheet->setCellValue($newCoordinates, $cell->getValue());
             }
             // Clear the original cell
             $pSheet->setCellValue($cell->getCoordinate(), '');
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
//.........这里部分代码省略.........

 public function parse(\PHPExcel_Worksheet $sheet)
 {
     $rates = [];
     foreach ($sheet->getRowIterator() as $row) {
         $rowIndex = $row->getRowIndex();
         $currencyCode = $sheet->getCell('B' . $rowIndex)->getCalculatedValue();
         if (!$this->isCurrencyCode($currencyCode)) {
             continue;
         }
         $date = new \DateTime();
         $rate = ['type' => 'cash', 'date' => $date->format('Y-m-d'), 'curr' => $currencyCode, 'count' => (int) $sheet->getCell('C' . $rowIndex)->getValue(), 'buy' => number_format((double) $sheet->getCell('D' . $rowIndex)->getValue(), $this->getDecimals($currencyCode), '.', ''), 'sale' => number_format((double) $sheet->getCell('E' . $rowIndex)->getValue(), $this->getDecimals($currencyCode), '.', ''), 'nbu' => number_format((double) $sheet->getCell('F' . $rowIndex)->getValue(), $this->getDecimals($currencyCode, 'nbu'), '.', '')];
         $rates[] = $rate;
     }
     return $rates;
 }

 /**
  * Set Worksheet
  *
  * @param PHPExcel_Worksheet $pValue            
  * @param bool $pOverrideOld
  *            Worksheet has already been assigned, overwrite it and remove image from old Worksheet?
  * @throws PHPExcel_Exception
  * @return PHPExcel_Worksheet_BaseDrawing
  */
 public function setWorksheet(PHPExcel_Worksheet $pValue = null, $pOverrideOld = false)
 {
     if (is_null($this->_worksheet)) {
         // Add drawing to PHPExcel_Worksheet
         $this->_worksheet = $pValue;
         $this->_worksheet->getCell($this->_coordinates);
         $this->_worksheet->getDrawingCollection()->append($this);
     } else {
         if ($pOverrideOld) {
             // Remove drawing from old PHPExcel_Worksheet
             $iterator = $this->_worksheet->getDrawingCollection()->getIterator();
             
             while ($iterator->valid()) {
                 if ($iterator->current()->getHashCode() == $this->getHashCode()) {
                     $this->_worksheet->getDrawingCollection()->offsetUnset($iterator->key());
                     $this->_worksheet = null;
                     break;
                 }
             }
             
             // Set new PHPExcel_Worksheet
             $this->setWorksheet($pValue);
         } else {
             throw new PHPExcel_Exception("A PHPExcel_Worksheet has already been assigned. Drawings can only exist on one PHPExcel_Worksheet.");
         }
     }
     return $this;
 }

 /**
  *
  * @throws PhpExcelException
  */
 private function mapHeaders()
 {
     $keys = array_keys($this->loweredFields);
     $columns = array_fill_keys($keys, null);
     $this->loweredFieldNameToExcelColumnMap = [];
     $lastRow = $this->activeSheet->getHighestRow();
     for ($i = $this->headerRow; $i <= $lastRow; $i++) {
         foreach ($this->excelColumnsRange as $columnIndex) {
             $value = $this->activeSheet->getCell($columnIndex . $i)->getCalculatedValue();
             $text = $this->lowerHeaderCellText($value);
             if (array_key_exists($text, $columns)) {
                 $columns[$text] = $columnIndex;
             }
         }
         $this->loweredFieldNameToExcelColumnMap = array_filter($columns);
         if (count($this->loweredFieldNameToExcelColumnMap) > 0) {
             $this->firstDataRow = $i + 1;
             break;
         }
     }
     $missingColumns = array_diff_key($this->loweredToOriginalKeysMap, $this->loweredFieldNameToExcelColumnMap);
     if (count($missingColumns) > 0) {
         throw new PhpExcelException('Missing columns: ' . implode(', ', $missingColumns));
     }
 }

 protected function getCellValue(\PHPExcel_Worksheet $sheet, $coord)
 {
     $cell = $sheet->getCell($coord);
     if ($cell) {
         return $cell->getValue();
     }
 }

 /**
  * Create worksheet stringtable
  *
  * @param     PHPExcel_Worksheet     $pSheet                Worksheet
  * @param     string[]                 $pExistingTable     Existing table to eventually merge with
  * @return     string[]                 String table for worksheet
  * @throws     PHPExcel_Writer_Exception
  */
 public function createStringTable($pSheet = null, $pExistingTable = null)
 {
     if ($pSheet !== null) {
         // Create string lookup table
         $aStringTable = array();
         $cellCollection = null;
         $aFlippedStringTable = null;
         // For faster lookup
         // Is an existing table given?
         if ($pExistingTable !== null && is_array($pExistingTable)) {
             $aStringTable = $pExistingTable;
         }
         // Fill index array
         $aFlippedStringTable = $this->flipStringTable($aStringTable);
         // Loop through cells
         foreach ($pSheet->getCellCollection() as $cellID) {
             $cell = $pSheet->getCell($cellID);
             $cellValue = $cell->getValue();
             if (!is_object($cellValue) && $cellValue !== null && $cellValue !== '' && !isset($aFlippedStringTable[$cellValue]) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING || $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING2 || $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NULL)) {
                 $aStringTable[] = $cellValue;
                 $aFlippedStringTable[$cellValue] = true;
             } elseif ($cellValue instanceof PHPExcel_RichText && $cellValue !== null && !isset($aFlippedStringTable[$cellValue->getHashCode()])) {
                 $aStringTable[] = $cellValue;
                 $aFlippedStringTable[$cellValue->getHashCode()] = true;
             }
         }
         return $aStringTable;
     } else {
         throw new PHPExcel_Writer_Exception("Invalid PHPExcel_Worksheet object passed.");
     }
 }

 /**
  * 
  */
 protected function _addFilters()
 {
     $pFilters = $this->_content->getElementsByTagName('p');
     if (empty($pFilters->length)) {
         return false;
     }
     $filters = array();
     foreach ($pFilters->item(0)->childNodes as $child) {
         $nodeValue = trim($child->nodeValue);
         if (!empty($nodeValue)) {
             $filters[] = $nodeValue;
         }
     }
     $this->_mainSheet->getCell('C6')->setValue(implode(' ', $filters))->getStyle()->getAlignment()->setWrapText(true);
     $this->_mainSheet->mergeCells('C6:T6');
 }

 protected function writeData(\PHPExcel_Worksheet $worksheet)
 {
     foreach ($this->activeDataProvider->getModels() as $row => $model) {
         foreach ($this->columns as $col => $column) {
             $columnIndex = \PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 2);
             switch ($column->format) {
                 case Column::FormatRaw:
                     $worksheet->setCellValue($columnIndex, $column->getValue($model));
                     break;
                 case Column::FormatUri:
                     $worksheet->setCellValue($columnIndex, $column->getValue($model));
                     $worksheet->getCell($columnIndex)->getHyperlink()->setUrl('"' . $column->getValue($model) . '"');
                     break;
             }
         }
     }
 }

 /**
  * Функция преобразования листа Excel в таблицу MySQL, с учетом объединенных строк и столбцов. Значения берутся уже вычисленными
  *
  * @param PHPExcel_Worksheet $worksheet                - Лист Excel
  * @param string             $table_name               - Имя таблицы MySQL
  * @param int|array          $columns_names            - Строка или массив с именами столбцов таблицы MySQL (0 - имена типа column + n). Если указано больше столбцов, чем на листе Excel, будут использованы значения по умолчанию указанных типов столбцов. Если указано ложное значение (null, false, "", 0, -1...) столбец игнорируется
  * @param bool|int           $start_row_index          - Номер строки, с которой начинается обработка данных (например, если 1 строка шапка таблицы). Нумерация начинается с 1, как в Excel
  * @param bool|array         $condition_functions      - Массив функций с условиями добавления строки по значению столбца (столбец => функция)
  * @param bool|array         $transform_functions      - Массив функций для изменения значения столбца (столбец => функция)
  * @param bool|int           $unique_column_for_update - Номер столбца с уникальным значением для обновления таблицы. Работает если $columns_names - массив (название столбца берется из него по [$unique_column_for_update - 1])
  * @param bool|array         $table_types              - Типы столбцов таблицы (используется при создании таблицы), в SQL формате - "INT(11) NOT NULL". Если не указаны, то используется "TEXT NOT NULL"
  * @param bool|array         $table_keys               - Ключевые поля таблицы (тип => столбец)
  * @param string             $table_encoding           - Кодировка таблицы MySQL
  * @param string             $table_engine             - Тип таблицы MySQL
  *
  * @return bool - Флаг, удалось ли выполнить функцию в полном объеме
  */
 private function excel_to_mysql($worksheet, $table_name, $columns_names, $start_row_index, $condition_functions, $transform_functions, $unique_column_for_update, $table_types, $table_keys, $table_encoding, $table_engine)
 {
     // Проверяем соединение с MySQL
     if (!$this->mysql_connect->connect_error) {
         // Строка для названий столбцов таблицы MySQL
         $columns = array();
         // Количество столбцов на листе Excel
         $columns_count = \PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());
         // Если в качестве имен столбцов передан массив, то проверяем соответствие его длинны с количеством столбцов
         if ($columns_names) {
             if (is_array($columns_names)) {
                 $columns_names_count = count($columns_names);
                 if ($columns_names_count < $columns_count) {
                     return false;
                 } elseif ($columns_names_count > $columns_count) {
                     $columns_count = $columns_names_count;
                 }
             } else {
                 return false;
             }
         }
         // Если указаны типы столбцов
         if ($table_types) {
             if (is_array($table_types)) {
                 // Проверяем количество столбцов и типов
                 if (count($table_types) != count($columns_names)) {
                     return false;
                 }
             } else {
                 return false;
             }
         }
         $table_name = "`{$table_name}`";
         // Проверяем, что $columns_names - массив и $unique_column_for_update находиться в его пределах
         if ($unique_column_for_update) {
             $unique_column_for_update = is_array($columns_names) ? $unique_column_for_update <= count($columns_names) ? "`{$columns_names[$unique_column_for_update - 1]}`" : false : false;
         }
         // Перебираем столбцы листа Excel и генерируем строку с именами через запятую
         for ($column = 0; $column < $columns_count; $column++) {
             $column_name = is_array($columns_names) ? $columns_names[$column] : ($columns_names == 0 ? "column{$column}" : $worksheet->getCellByColumnAndRow($column, $columns_names)->getValue());
             $columns[] = $column_name ? "`{$column_name}`" : null;
         }
         $query_string = "DROP TABLE IF EXISTS {$table_name}";
         if (defined("EXCEL_MYSQL_DEBUG")) {
             if (EXCEL_MYSQL_DEBUG) {
                 var_dump($query_string);
             }
         }
         // Удаляем таблицу MySQL, если она существовала (если не указан столбец с уникальным значением для обновления)
         if ($unique_column_for_update ? true : $this->mysql_connect->query($query_string)) {
             $columns_types = $ignore_columns = array();
             // Обходим столбцы и присваиваем типы
             foreach ($columns as $index => $value) {
                 if ($value == null) {
                     $ignore_columns[] = $index;
                     unset($columns[$index]);
                 } else {
                     if ($table_types) {
                         $columns_types[] = "{$value} {$table_types[$index]}";
                     } else {
                         $columns_types[] = "{$value} TEXT NOT NULL";
                     }
                 }
             }
             // Если указаны ключевые поля, то создаем массив ключей
             if ($table_keys) {
                 $columns_keys = array();
                 foreach ($table_keys as $key => $value) {
                     $columns_keys[] = "{$value} (`{$key}`)";
                 }
                 $columns_keys_list = implode(", ", $columns_keys);
                 $columns_keys = ", {$columns_keys_list}";
             } else {
                 $columns_keys = null;
             }
             $columns_types_list = implode(", ", $columns_types);
             $query_string = "CREATE TABLE IF NOT EXISTS {$table_name} ({$columns_types_list}{$columns_keys}) COLLATE = '{$table_encoding}' ENGINE = {$table_engine}";
             if (defined("EXCEL_MYSQL_DEBUG")) {
                 if (EXCEL_MYSQL_DEBUG) {
                     var_dump($query_string);
                 }
             }
             // Создаем таблицу MySQL
//.........这里部分代码省略.........

//.........这里部分代码省略.........
     $this->_writeScenProtect();
     // Write OBJECTPROTECT
     $this->_writeObjectProtect();
     // Write sheet password
     $this->_writePassword();
     // Write DEFCOLWIDTH record
     $this->_writeDefcol();
     // Write the COLINFO records if they exist
     if (!empty($this->_colinfo)) {
         $colcount = count($this->_colinfo);
         for ($i = 0; $i < $colcount; ++$i) {
             $this->_writeColinfo($this->_colinfo[$i]);
         }
     }
     // Write EXTERNCOUNT of external references
     if ($this->_BIFF_version == 0x500) {
         $this->_writeExterncount($num_sheets);
     }
     // Write EXTERNSHEET references
     if ($this->_BIFF_version == 0x500) {
         for ($i = 0; $i < $num_sheets; ++$i) {
             $this->_writeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle());
         }
     }
     // Write sheet dimensions
     $this->_writeDimensions();
     // Row dimensions
     foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
         $xfIndex = $rowDimension->getXfIndex() + 15;
         // there are 15 cellXfs
         $this->_writeRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel());
     }
     // Write Cells
     foreach ($this->_phpSheet->getCellCollection() as $cellID) {
         $cell = $this->_phpSheet->getCell($cellID);
         $row = $cell->getRow() - 1;
         $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
         // Don't break Excel!
         if ($row + 1 > 65536 or $column + 1 > 256) {
             break;
         }
         // Write cell value
         $xfIndex = $cell->getXfIndex() + 15;
         // there are 15 cell style Xfs
         if ($cell->getValue() instanceof PHPExcel_RichText) {
             $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex);
         } else {
             switch ($cell->getDatatype()) {
                 case PHPExcel_Cell_DataType::TYPE_STRING:
                     if ($cell->getValue() === '' or $cell->getValue() === null) {
                         $this->_writeBlank($row, $column, $xfIndex);
                     } else {
                         $this->_writeString($row, $column, $cell->getValue(), $xfIndex);
                     }
                     break;
                 case PHPExcel_Cell_DataType::TYPE_FORMULA:
                     $calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null;
                     $this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue);
                     break;
                 case PHPExcel_Cell_DataType::TYPE_BOOL:
                     $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex);
                     break;
                 case PHPExcel_Cell_DataType::TYPE_ERROR:
                     $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex);
                     break;
                 case PHPExcel_Cell_DataType::TYPE_NUMERIC:

 /**
  *	Apply the AutoFilter rules to the AutoFilter Range
  *
  *	@throws	PHPExcel_Exception
  *	@return PHPExcel_Worksheet_AutoFilter
  */
 public function showHideRows()
 {
     list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range);
     //	The heading row should always be visible
     //		echo 'AutoFilter Heading Row ',$rangeStart[1],' is always SHOWN',PHP_EOL;
     $this->_workSheet->getRowDimension($rangeStart[1])->setVisible(TRUE);
     $columnFilterTests = array();
     foreach ($this->_columns as $columnID => $filterColumn) {
         $rules = $filterColumn->getRules();
         switch ($filterColumn->getFilterType()) {
             case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER:
                 $ruleValues = array();
                 //	Build a list of the filter value selections
                 foreach ($rules as $rule) {
                     $ruleType = $rule->getRuleType();
                     $ruleValues[] = $rule->getValue();
                 }
                 //	Test if we want to include blanks in our filter criteria
                 $blanks = FALSE;
                 $ruleDataSet = array_filter($ruleValues);
                 if (count($ruleValues) != count($ruleDataSet)) {
                     $blanks = TRUE;
                 }
                 if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER) {
                     //	Filter on absolute values
                     $columnFilterTests[$columnID] = array('method' => '_filterTestInSimpleDataSet', 'arguments' => array('filterValues' => $ruleDataSet, 'blanks' => $blanks));
                 } else {
                     //	Filter on date group values
                     $arguments = array('date' => array(), 'time' => array(), 'dateTime' => array());
                     foreach ($ruleDataSet as $ruleValue) {
                         $date = $time = '';
                         if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') {
                             $date .= sprintf('%04d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
                         }
                         if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') {
                             $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
                         }
                         if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') {
                             $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
                         }
                         if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') {
                             $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
                         }
                         if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') {
                             $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
                         }
                         if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') {
                             $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
                         }
                         $dateTime = $date . $time;
                         $arguments['date'][] = $date;
                         $arguments['time'][] = $time;
                         $arguments['dateTime'][] = $dateTime;
                     }
                     //	Remove empty elements
                     $arguments['date'] = array_filter($arguments['date']);
                     $arguments['time'] = array_filter($arguments['time']);
                     $arguments['dateTime'] = array_filter($arguments['dateTime']);
                     $columnFilterTests[$columnID] = array('method' => '_filterTestInDateGroupSet', 'arguments' => array('filterValues' => $arguments, 'blanks' => $blanks));
                 }
                 break;
             case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER:
                 $customRuleForBlanks = FALSE;
                 $ruleValues = array();
                 //	Build a list of the filter value selections
                 foreach ($rules as $rule) {
                     $ruleType = $rule->getRuleType();
                     $ruleValue = $rule->getValue();
                     if (!is_numeric($ruleValue)) {
                         //	Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards
                         $ruleValue = preg_quote($ruleValue);
                         $ruleValue = str_replace(self::$_fromReplace, self::$_toReplace, $ruleValue);
                         if (trim($ruleValue) == '') {
                             $customRuleForBlanks = TRUE;
                             $ruleValue = trim($ruleValue);
                         }
                     }
                     $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue);
                 }
                 $join = $filterColumn->getJoin();
                 $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks));
                 break;
             case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
                 $ruleValues = array();
                 foreach ($rules as $rule) {
                     //	We should only ever have one Dynamic Filter Rule anyway
                     $dynamicRuleType = $rule->getGrouping();
                     if ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) {
                         //	Number (Average) based
                         //	Calculate the average
                         $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
                         $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, NULL, $this->_workSheet->getCell('A1'));
                         //	Set above/below rule based on greaterThan or LessTan
                         $operator = $dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
//.........这里部分代码省略.........

 function findLessonS(PHPExcel_Worksheet $sheet, $arr_Time, $arr_Group)
 {
     $b = null;
     $arr_temp = null;
     if (isset($arr_Time) && isset($arr_Group)) {
         foreach ($arr_Group as $temp) {
             foreach ($temp as $groupName => $value) {
                 foreach ($arr_Time as $temp1) {
                     foreach ($temp1 as $key_time => $value_time) {
                         $result = $sheet->getCell($value['x'] . $value_time['row'])->getFormattedValue();
                         if ($result == '') {
                         } else {
                             $arr_temp[] = array('group' => $groupName, 'time' => $key_time, 'day' => $value_time['day'], 'value' => $result);
                         }
                     }
                 }
             }
         }
     } else {
         //вывод сообщения о том что нет на странице нашего расписания
         $arr_temp = false;
     }
     return $arr_temp;
 }

//.........这里部分代码省略.........
         // detect type of hyperlink (there are 4 types)
         $hyperlinkType = null;
         if ($isUNC) {
             $hyperlinkType = 'UNC';
         } else {
             if (!$isFileLinkOrUrl) {
                 $hyperlinkType = 'workbook';
             } else {
                 if (ord($recordData[$offset]) == 0x3) {
                     $hyperlinkType = 'local';
                 } else {
                     if (ord($recordData[$offset]) == 0xe0) {
                         $hyperlinkType = 'URL';
                     }
                 }
             }
         }
         switch ($hyperlinkType) {
             case 'URL':
                 // section 5.58.2: Hyperlink containing a URL
                 // e.g. http://example.org/index.php
                 // offset: var; size: 16; GUID of URL Moniker
                 $offset += 16;
                 // offset: var; size: 4; size (in bytes) of character array of the URL including trailing zero word
                 $us = $this->_GetInt4d($recordData, $offset);
                 $offset += 4;
                 // offset: var; size: $us; character array of the URL, no Unicode string header, always 16-bit characters, zero-terminated
                 $url = $this->_encodeUTF16(substr($recordData, $offset, $us - 2), false);
                 $url .= $hasText ? '#' : '';
                 $offset += $us;
                 break;
             case 'local':
                 // section 5.58.3: Hyperlink to local file
                 // examples:
                 //   mydoc.txt
                 //   ../../somedoc.xls#Sheet!A1
                 // offset: var; size: 16; GUI of File Moniker
                 $offset += 16;
                 // offset: var; size: 2; directory up-level count.
                 $upLevelCount = $this->_GetInt2d($recordData, $offset);
                 $offset += 2;
                 // offset: var; size: 4; character count of the shortened file path and name, including trailing zero word
                 $sl = $this->_GetInt4d($recordData, $offset);
                 $offset += 4;
                 // offset: var; size: sl; character array of the shortened file path and name in 8.3-DOS-format (compressed Unicode string)
                 $shortenedFilePath = substr($recordData, $offset, $sl);
                 $shortenedFilePath = $this->_encodeUTF16($shortenedFilePath, true);
                 $shortenedFilePath = substr($shortenedFilePath, 0, -1);
                 // remove trailing zero
                 $offset += $sl;
                 // offset: var; size: 24; unknown sequence
                 $offset += 24;
                 // extended file path
                 // offset: var; size: 4; size of the following file link field including string lenth mark
                 $sz = $this->_GetInt4d($recordData, $offset);
                 $offset += 4;
                 // only present if $sz > 0
                 if ($sz > 0) {
                     // offset: var; size: 4; size of the character array of the extended file path and name
                     $xl = $this->_GetInt4d($recordData, $offset);
                     $offset += 4;
                     // offset: var; size 2; unknown
                     $offset += 2;
                     // offset: var; size $xl; character array of the extended file path and name.
                     $extendedFilePath = substr($recordData, $offset, $xl);
                     $extendedFilePath = $this->_encodeUTF16($extendedFilePath, false);
                     $offset += $xl;
                 }
                 // construct the path
                 $url = str_repeat('..\\', $upLevelCount);
                 $url .= $sz > 0 ? $extendedFilePath : $shortenedFilePath;
                 // use extended path if available
                 $url .= $hasText ? '#' : '';
                 break;
             case 'UNC':
                 // section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
                 // todo: implement
                 return;
             case 'workbook':
                 // section 5.58.5: Hyperlink to the Current Workbook
                 // e.g. Sheet2!B1:C2, stored in text mark field
                 $url = 'sheet://';
                 break;
             default:
                 return;
         }
         if ($hasText) {
             // offset: var; size: 4; character count of text mark including trailing zero word
             $tl = $this->_GetInt4d($recordData, $offset);
             $offset += 4;
             // offset: var; size: var; character array of the text mark without the # sign, no Unicode header, always 16-bit characters, zero-terminated
             $text = $this->_encodeUTF16(substr($recordData, $offset, 2 * ($tl - 1)), false);
             $url .= $text;
         }
         // apply the hyperlink to all the relevant cells
         foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cellRange) as $coordinate) {
             $this->_phpSheet->getCell($coordinate)->getHyperLink()->setUrl($url);
         }
     }
 }

 public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE)
 {
     if ($this->_dataSource !== NULL) {
         $calcEngine = PHPExcel_Calculation::getInstance($worksheet->getParent());
         $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
         if ($flatten) {
             $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
             foreach ($this->_dataValues as &$dataValue) {
                 if (!empty($dataValue) && $dataValue[0] == '#') {
                     $dataValue = 0.0;
                 }
             }
             unset($dataValue);
         } else {
             $cellRange = explode('!', $this->_dataSource);
             if (count($cellRange) > 1) {
                 list(, $cellRange) = $cellRange;
             }
             $dimensions = PHPExcel_Cell::rangeDimension(str_replace('$', '', $cellRange));
             if ($dimensions[0] == 1 || $dimensions[1] == 1) {
                 $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
             } else {
                 $newArray = array_values(array_shift($newDataValues));
                 foreach ($newArray as $i => $newDataSet) {
                     $newArray[$i] = array($newDataSet);
                 }
                 foreach ($newDataValues as $newDataSet) {
                     $i = 0;
                     foreach ($newDataSet as $newDataVal) {
                         array_unshift($newArray[$i++], $newDataVal);
                     }
                 }
                 $this->_dataValues = $newArray;
             }
         }
         $this->_pointCount = count($this->_dataValues);
     }
 }

 public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE)
 {
     if ($this->_dataSource !== NULL) {
         $calcEngine = PHPExcel_Calculation::getInstance();
         $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
         if ($flatten) {
             $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
         } else {
             $newArray = array_values(array_shift($newDataValues));
             foreach ($newArray as $i => $newDataSet) {
                 $newArray[$i] = array($newDataSet);
             }
             foreach ($newDataValues as $newDataSet) {
                 $i = 0;
                 foreach ($newDataSet as $newDataVal) {
                     array_unshift($newArray[$i++], $newDataVal);
                 }
             }
             $this->_dataValues = $newArray;
         }
         $this->_pointCount = count($this->_dataValues);
     }
 }