Example #1
5
 /**
  * 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();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
     if (count($aColumnDimensions) > 0) {
         foreach ($aColumnDimensions as $objColumnDimension) {
             $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
             list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objColumnDimension->getColumnIndex() != $newReference) {
                 $objColumnDimension->setColumnIndex($newReference);
             }
         }
         $pSheet->refreshColumnDimensions();
     }
     // Update worksheet: row dimensions
     $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
     if (count($aRowDimensions) > 0) {
         foreach ($aRowDimensions as $objRowDimension) {
             $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
             list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objRowDimension->getRowIndex() != $newReference) {
                 $objRowDimension->setRowIndex($newReference);
             }
         }
         $pSheet->refreshRowDimensions();
         $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
         for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
             $newDimension = $pSheet->getRowDimension($i);
             $newDimension->setRowHeight($copyDimension->getRowHeight());
             $newDimension->setVisible($copyDimension->getVisible());
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
     // Update worksheet: merge cells
     $aMergeCells = $pSheet->getMergeCells();
     $aNewMergeCells = array();
     // the new array of all merge cells
     foreach ($aMergeCells as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewMergeCells[$newReference] = $newReference;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // Update worksheet: protected cells
     $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
     foreach ($aProtectedCells as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->protectCells($newReference, $value, true);
             $pSheet->unprotectCells($key);
         }
     }
     // Update worksheet: autofilter
     if ($pSheet->getAutoFilter() != '') {
         $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
 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;
 }
Example #3
0
 /**
  * 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;
 }
Example #4
0
 /**
  *
  * @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.");
     }
 }
Example #7
0
 /**
  * 
  */
 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');
 }
Example #8
0
 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;
             }
         }
     }
 }
Example #9
0
 /**
  * Функция преобразования листа 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
             if ($this->mysql_connect->query($query_string)) {
                 // Коллекция значений уникального столбца для удаления несуществующих строк в файле импорта (используется при обновлении)
                 $id_list_in_import = array();
                 // Количество строк на листе Excel
                 $rows_count = $worksheet->getHighestRow();
                 // Получаем массив всех объединенных ячеек
                 $all_merged_cells = $worksheet->getMergeCells();
                 // Перебираем строки листа Excel
                 for ($row = $start_row_index ? $start_row_index : (is_array($columns_names) ? 1 : $columns_names + 1); $row <= $rows_count; $row++) {
                     // Строка со значениями всех столбцов в строке листа Excel
                     $values = array();
                     // Перебираем столбцы листа Excel
                     for ($column = 0; $column < $columns_count; $column++) {
                         if (in_array($column, $ignore_columns)) {
                             continue;
                         }
                         // Строка со значением объединенных ячеек листа Excel
                         $merged_value = null;
                         // Ячейка листа Excel
                         $cell = $worksheet->getCellByColumnAndRow($column, $row);
                         // Перебираем массив объединенных ячеек листа Excel
                         foreach ($all_merged_cells as $merged_cells) {
                             // Если текущая ячейка - объединенная,
                             if ($cell->isInRange($merged_cells)) {
                                 // то вычисляем значение первой объединенной ячейки, и используем её в качестве значения текущей ячейки
                                 $merged_value = explode(":", $merged_cells);
                                 $merged_value = $worksheet->getCell($merged_value[0])->getValue();
                                 break;
                             }
                         }
                         // Проверяем, что ячейка не объединенная: если нет, то берем ее значение, иначе значение первой объединенной ячейки
                         $value = strlen($merged_value) == 0 ? $cell->getValue() : $merged_value;
                         // Если задан массив функций с условиями
                         if ($condition_functions) {
                             if (isset($condition_functions[$columns_names[$column]])) {
                                 // Проверяем условие
                                 if (!$condition_functions[$columns_names[$column]]($value)) {
                                     break;
                                 }
                             }
                         }
                         $value = $transform_functions ? isset($transform_functions[$columns_names[$column]]) ? $transform_functions[$columns_names[$column]]($value) : $value : $value;
                         $values[] = "'{$this->mysql_connect->real_escape_string($value)}'";
                     }
                     // Если количество столбцов не равно количеству значений, значит строка не прошла проверку
                     if ($columns_count - count($ignore_columns) != count($values)) {
                         continue;
                     }
                     // Добавляем или проверяем обновлять ли значение
                     $add_to_table = $unique_column_for_update ? false : true;
                     // Если обновляем
                     if ($unique_column_for_update) {
                         // Объединяем массивы для простоты работы
                         $columns_values = array_combine($columns, $values);
                         // Сохраняем уникальное значение
                         $id_list_in_import[] = $columns_values[$unique_column_for_update];
                         // Создаем условие выборки
                         $where = " WHERE {$unique_column_for_update} = {$columns_values[$unique_column_for_update]}";
                         // Удаляем столбец выборки
                         unset($columns_values[$unique_column_for_update]);
                         $query_string = "SELECT COUNT(*) AS count FROM {$table_name}{$where}";
                         if (defined("EXCEL_MYSQL_DEBUG")) {
                             if (EXCEL_MYSQL_DEBUG) {
                                 var_dump($query_string);
                             }
                         }
                         // Проверяем есть ли запись в таблице
                         $count = $this->mysql_connect->query($query_string);
                         $count = $count->fetch_assoc();
                         // Если есть, то создаем запрос и обновляем
                         if (intval($count['count']) != 0) {
                             $set = array();
                             foreach ($columns_values as $column => $value) {
                                 $set[] = "{$column} = {$value}";
                             }
                             $set_list = implode(", ", $set);
                             $query_string = "UPDATE {$table_name} SET {$set_list}{$where}";
                             if (defined("EXCEL_MYSQL_DEBUG")) {
                                 if (EXCEL_MYSQL_DEBUG) {
                                     var_dump($query_string);
                                 }
                             }
                             if (!$this->mysql_connect->query($query_string)) {
                                 return false;
                             }
                         } else {
                             $add_to_table = true;
                         }
                     }
                     // Добавляем строку в таблицу MySQL
                     if ($add_to_table) {
                         $columns_list = implode(", ", $columns);
                         $values_list = implode(", ", $values);
                         $query_string = "INSERT INTO {$table_name} ({$columns_list}) VALUES ({$values_list})";
                         if (defined("EXCEL_MYSQL_DEBUG")) {
                             if (EXCEL_MYSQL_DEBUG) {
                                 var_dump($query_string);
                             }
                         }
                         if (!$this->mysql_connect->query($query_string)) {
                             return false;
                         }
                     }
                 }
                 if (!empty($id_list_in_import)) {
                     $id_list = implode(", ", $id_list_in_import);
                     $query_string = "DELETE FROM {$table_name} WHERE {$unique_column_for_update} NOT IN ({$id_list})";
                     if (defined("EXCEL_MYSQL_DEBUG")) {
                         if (EXCEL_MYSQL_DEBUG) {
                             var_dump($query_string);
                         }
                     }
                     $this->mysql_connect->query($query_string);
                 }
                 return true;
             }
         }
     }
     return false;
 }
Example #10
0
 /**
  * Add data to the beginning of the workbook (note the reverse order)
  * and to the end of the workbook.
  *
  * @access public
  * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
  */
 function close()
 {
     $num_sheets = count($this->_phpSheet->getParent()->getAllSheets());
     // Write BOF record
     $this->_storeBof(0x10);
     // Write PRINTHEADERS
     $this->_writePrintHeaders();
     // Write PRINTGRIDLINES
     $this->_writePrintGridlines();
     // Write GRIDSET
     $this->_writeGridset();
     // Calculate column widths
     $this->_phpSheet->calculateColumnWidths();
     // Column dimensions
     $columnDimensions = $this->_phpSheet->getColumnDimensions();
     for ($i = 0; $i < 256; ++$i) {
         $hidden = 0;
         $level = 0;
         $xfIndex = 15;
         // there are 15 cell style Xfs
         if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
             $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth();
         } else {
             $width = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont());
         }
         $columnLetter = PHPExcel_Cell::stringFromColumnIndex($i);
         if (isset($columnDimensions[$columnLetter])) {
             $columnDimension = $columnDimensions[$columnLetter];
             if ($columnDimension->getWidth() >= 0) {
                 $width = $columnDimension->getWidth();
             }
             $hidden = $columnDimension->getVisible() ? 0 : 1;
             $level = $columnDimension->getOutlineLevel();
             $xfIndex = $columnDimension->getXfIndex() + 15;
             // there are 15 cell style Xfs
         }
         // Components of _colinfo:
         // $firstcol first column on the range
         // $lastcol  last column on the range
         // $width	width to set
         // $xfIndex  The optional cell style Xf index to apply to the columns
         // $hidden   The optional hidden atribute
         // $level	The optional outline level
         $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
     }
     // Write GUTS
     $this->_writeGuts();
     // Write DEFAULTROWHEIGHT
     if ($this->_BIFF_version == 0x600) {
         $this->_writeDefaultRowHeight();
     }
     // Write WSBOOL
     $this->_writeWsbool();
     // Write horizontal and vertical page breaks
     $this->_writeBreaks();
     // Write page header
     $this->_writeHeader();
     // Write page footer
     $this->_writeFooter();
     // Write page horizontal centering
     $this->_writeHcenter();
     // Write page vertical centering
     $this->_writeVcenter();
     // Write left margin
     $this->_writeMarginLeft();
     // Write right margin
     $this->_writeMarginRight();
     // Write top margin
     $this->_writeMarginTop();
     // Write bottom margin
     $this->_writeMarginBottom();
     // Write page setup
     $this->_writeSetup();
     // Write sheet protection
     $this->_writeProtect();
     // Write SCENPROTECT
     $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:
                     $this->_writeNumber($row, $column, $cell->getValue(), $xfIndex);
                     break;
             }
         }
     }
     // Append
     if ($this->_BIFF_version == 0x600) {
         $this->_writeMsoDrawing();
     }
     $this->_writeWindow2();
     $this->_writeZoom();
     if ($this->_phpSheet->getFreezePane()) {
         $this->_writePanes();
     }
     $this->_writeSelection();
     $this->_writeMergedCells();
     // Hyperlinks
     if ($this->_BIFF_version == 0x600) {
         foreach ($this->_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
             list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinate);
             $url = $hyperlink->getUrl();
             if (strpos($url, 'sheet://') !== false) {
                 // internal to current workbook
                 $url = str_replace('sheet://', 'internal:', $url);
             } else {
                 if (preg_match('/^(http:|https:|ftp:|mailto:)/', $url)) {
                     // URL
                     // $url = $url;
                 } else {
                     // external (local file)
                     $url = 'external:' . $url;
                 }
             }
             $this->_writeUrl($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $url);
         }
     }
     if ($this->_BIFF_version == 0x600) {
         $this->_writeDataValidity();
         $this->_writeSheetLayout();
         $this->_writeSheetProtection();
         $this->_writeRangeProtection();
     }
     $this->_storeEof();
 }
Example #11
0
 /**
  *	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;
                         $ruleValues[] = array('operator' => $operator, 'value' => $average);
                         $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR));
                     } else {
                         //	Date based
                         if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') {
                             //	Month or Quarter
                             sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period);
                             if ($periodType == 'M') {
                                 $ruleValues = array($period);
                             } else {
                                 --$period;
                                 $periodEnd = (1 + $period) * 3;
                                 $periodStart = 1 + $period * 3;
                                 $ruleValues = range($periodStart, periodEnd);
                             }
                             $columnFilterTests[$columnID] = array('method' => '_filterTestInPeriodDateSet', 'arguments' => $ruleValues);
                             $filterColumn->setAttributes(array());
                         } else {
                             //	Date Range
                             $columnFilterTests[$columnID] = $this->_dynamicFilterDateRange($dynamicRuleType, $filterColumn);
                             break;
                         }
                     }
                 }
                 break;
             case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
                 $ruleValues = array();
                 $dataRowCount = $rangeEnd[1] - $rangeStart[1];
                 foreach ($rules as $rule) {
                     //	We should only ever have one Dynamic Filter Rule anyway
                     $toptenRuleType = $rule->getGrouping();
                     $ruleValue = $rule->getValue();
                     $ruleOperator = $rule->getOperator();
                 }
                 if ($ruleOperator === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
                     $ruleValue = floor($ruleValue * ($dataRowCount / 100));
                 }
                 if ($ruleValue < 1) {
                     $ruleValue = 1;
                 }
                 if ($ruleValue > 500) {
                     $ruleValue = 500;
                 }
                 $maxVal = $this->_calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue);
                 $operator = $toptenRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
                 $ruleValues[] = array('operator' => $operator, 'value' => $maxVal);
                 $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR));
                 $filterColumn->setAttributes(array('maxVal' => $maxVal));
                 break;
         }
     }
     //		echo 'Column Filter Test CRITERIA',PHP_EOL;
     //		var_dump($columnFilterTests);
     //
     //	Execute the column tests for each row in the autoFilter range to determine show/hide,
     for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
         //			echo 'Testing Row = ',$row,PHP_EOL;
         $result = TRUE;
         foreach ($columnFilterTests as $columnID => $columnFilterTest) {
             //				echo 'Testing cell ',$columnID.$row,PHP_EOL;
             $cellValue = $this->_workSheet->getCell($columnID . $row)->getCalculatedValue();
             //				echo 'Value is ',$cellValue,PHP_EOL;
             //	Execute the filter test
             $result = $result && call_user_func_array(array('PHPExcel_Worksheet_AutoFilter', $columnFilterTest['method']), array($cellValue, $columnFilterTest['arguments']));
             //				echo (($result) ? 'VALID' : 'INVALID'),PHP_EOL;
             //	If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
             if (!$result) {
                 break;
             }
         }
         //	Set show/hide for the row based on the result of the autoFilter result
         //			echo (($result) ? 'SHOW' : 'HIDE'),PHP_EOL;
         $this->_workSheet->getRowDimension($row)->setVisible($result);
     }
     return $this;
 }
Example #12
0
 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;
 }
Example #13
0
 /**
  * Read HYPERLINK record
  */
 private function _readHyperLink()
 {
     $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
     $recordData = substr($this->_data, $this->_pos + 4, $length);
     // move stream pointer forward to next record
     $this->_pos += 4 + $length;
     if (!$this->_readDataOnly) {
         // offset: 0; size: 8; cell range address of all cells containing this hyperlink
         try {
             $cellRange = $this->_readBIFF8CellRangeAddressFixed($recordData, 0, 8);
         } catch (Exception $e) {
             return;
         }
         // offset: 8, size: 16; GUID of StdLink
         // offset: 24, size: 4; unknown value
         // offset: 28, size: 4; option flags
         // bit: 0; mask: 0x00000001; 0 = no link or extant, 1 = file link or URL
         $isFileLinkOrUrl = (0x1 & $this->_GetInt2d($recordData, 28)) >> 0;
         // bit: 1; mask: 0x00000002; 0 = relative path, 1 = absolute path or URL
         $isAbsPathOrUrl = (0x1 & $this->_GetInt2d($recordData, 28)) >> 1;
         // bit: 2 (and 4); mask: 0x00000014; 0 = no description
         $hasDesc = (0x14 & $this->_GetInt2d($recordData, 28)) >> 2;
         // bit: 3; mask: 0x00000008; 0 = no text, 1 = has text
         $hasText = (0x8 & $this->_GetInt2d($recordData, 28)) >> 3;
         // bit: 7; mask: 0x00000080; 0 = no target frame, 1 = has target frame
         $hasFrame = (0x80 & $this->_GetInt2d($recordData, 28)) >> 7;
         // bit: 8; mask: 0x00000100; 0 = file link or URL, 1 = UNC path (inc. server name)
         $isUNC = (0x100 & $this->_GetInt2d($recordData, 28)) >> 8;
         // offset within record data
         $offset = 32;
         if ($hasDesc) {
             // offset: 32; size: var; character count of description text
             $dl = $this->_GetInt4d($recordData, 32);
             // offset: 36; size: var; character array of description text, no Unicode string header, always 16-bit characters, zero terminated
             $desc = $this->_encodeUTF16(substr($recordData, 36, 2 * ($dl - 1)), false);
             $offset += 4 + 2 * $dl;
         }
         if ($hasFrame) {
             $fl = $this->_GetInt4d($recordData, $offset);
             $offset += 4 + 2 * $fl;
         }
         // 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);
     }
 }
Example #16
0
 /**
  * Checks to see if the sheet is empty.
  * 
  * @param \PHPExcel_Worksheet $sheet The PHPExcel sheet to check.
  * 
  * @return bool Returns TRUE if the sheet is empty, else FALSE.
  */
 private static function sheetIsEmpty(\PHPExcel_Worksheet $sheet) : bool
 {
     return $sheet->getHighestDataRow() === 1 && $sheet->getHighestDataColumn() === 'A' && $sheet->getCell('A1')->getValue() === null;
 }
Example #17
0
 /**
  * Read HYPERLINK record
  */
 private function _readHyperLink()
 {
     $spos = $this->_pos;
     $length = $this->_GetInt2d($this->_data, $spos + 2);
     $recordData = substr($this->_data, $spos + 4, $length);
     $spos += 4;
     // move stream pointer forward to next record
     $this->_pos += 4 + $length;
     if (!$this->_readDataOnly) {
         // offset: 0; size: 8; cell range address of all cells containing this hyperlink
         $cellRange = $this->_readBIFF8CellRangeAddressFixed($recordData, 0, 8);
         // offset: 8, size: 16; GUID of StdLink
         // offset: 24, size: 4; unknown value
         // offset: 28, size: 4; option flags
         // bit: 0; mask: 0x00000001; 0 = no link or extant, 1 = file link or URL
         $isFileLinkOrUrl = (0x1 & $this->_GetInt2d($recordData, 28)) >> 0;
         // bit: 1; mask: 0x00000002; 0 = relative path, 1 = absolute path or URL
         $isAbsPathOrUrl = (0x1 & $this->_GetInt2d($recordData, 28)) >> 1;
         // bit: 2 (and 4); mask: 0x00000014; 0 = no description
         $hasDesc = (0x14 & $this->_GetInt2d($recordData, 28)) >> 2;
         // bit: 3; mask: 0x00000008; 0 = no text, 1 = has text
         $hasText = (0x8 & $this->_GetInt2d($recordData, 28)) >> 3;
         // bit: 7; mask: 0x00000080; 0 = no target frame, 1 = has target frame
         $hasFrame = (0x80 & $this->_GetInt2d($recordData, 28)) >> 7;
         // bit: 8; mask: 0x00000100; 0 = file link or URL, 1 = UNC path (inc. server name)
         $isUNC = (0x100 & $this->_GetInt2d($recordData, 28)) >> 8;
         $offset = 32;
         if ($hasDesc) {
             // offset: 32; size: var; character count of description text
             $dl = $this->_GetInt4d($recordData, 32);
             // offset: 36; size: var; character array of description text, no Unicode string header, always 16-bit characters, zero terminated
             $desc = $this->_encodeUTF16(substr($recordData, 36, 2 * ($dl - 1)), false);
             $offset += 4 + 2 * $dl;
         }
         if ($hasFrame) {
             $fl = $this->_GetInt4d($recordData, $offset);
             $offset += 4 + 2 * $fl;
         }
         // 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':
                 // 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 - 1), false);
                 $url .= $hasText ? '#' : '';
                 $offset += $us;
                 break;
             case 'workbook':
                 // section 5.58.5: Hyperlink to the Current Workbook
                 // e.g. Sheet2!B1:C2, stored in text mark field
                 $url = 'sheet://';
                 break;
             case 'local':
                 // section 5.58.2: Hyperlink containing a URL
                 // e.g. http://example.org/index.php
                 // todo: implement
             // section 5.58.2: Hyperlink containing a URL
             // e.g. http://example.org/index.php
             // todo: implement
             case 'UNC':
                 // section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
                 // todo: implement
             // section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
             // todo: implement
             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);
         }
     }
 }
Example #18
0
 /**
  * Insert a new column or row, updating all possible related data
  *
  * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
  * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
  * @param   PHPExcel_Worksheet  $pSheet     The worksheet that we're editing
  * @throws  PHPExcel_Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = NULL)
 {
     $remove = $pNumCols < 0 || $pNumRows < 0;
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
     // 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 && $beforeColumnIndex - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 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 = $beforeColumnIndex - 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 ($cellID = $remove ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
         if ($cellIndex - 1 + $pNumCols < 0) {
             continue;
         }
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             } else {
                 // Formula should not be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
             }
             // Clear the original cell
             $pSheet->getCellCacheController()->deleteCacheData($cellID);
         } else {
             /*	We don't need to update styles for rows/columns before our insertion position,
             			but we do still need to adjust any formulae	in those cells					*/
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             }
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 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 = $beforeColumnIndex - 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();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: row dimensions
     $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: page breaks
     $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: comments
     $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: hyperlinks
     $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: data validations
     $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: merge cells
     $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: protected cells
     $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: autofilter
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());
             if (count($autoFilterColumns) > 0) {
                 sscanf($pBefore, '%[A-Z]%d', $column, $row);
                 $columnIndex = PHPExcel_Cell::columnIndexFromString($column);
                 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
                 if ($columnIndex <= $rangeEnd[0]) {
                     if ($pNumCols < 0) {
                         //	If we're actually deleting any columns that fall within the autofilter range,
                         //		then we delete any rules for those columns
                         $deleteColumn = $columnIndex + $pNumCols - 1;
                         $deleteCount = abs($pNumCols);
                         for ($i = 1; $i <= $deleteCount; ++$i) {
                             if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) {
                                 $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
                             }
                             ++$deleteColumn;
                         }
                     }
                     $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0];
                     //	Shuffle columns in autofilter range
                     if ($pNumCols > 0) {
                         //	For insert, we shuffle from end to beginning to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         $startColRef = $startCol;
                         $endColRef = $rangeEnd[0];
                         $toColRef = $rangeEnd[0] + $pNumCols;
                         do {
                             $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef - 1), PHPExcel_Cell::stringFromColumnIndex($toColRef - 1));
                             --$endColRef;
                             --$toColRef;
                         } while ($startColRef <= $endColRef);
                     } else {
                         //	For delete, we shuffle from beginning to end to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         do {
                             $autoFilter->shiftColumn($startColID, $toColID);
                             ++$startColID;
                             ++$toColID;
                         } while ($startColID != $endColID);
                     }
                 }
             }
         }
         $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
Example #19
0
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        if ($this->_version == self::XLS_BIFF8) {
            $string = self::_readUnicodeStringLong($recordData);
            $value = $string['value'];
        } else {
            $string = $this->_readByteStringLong($recordData);
            $value = $string['value'];
        }
        return $value;
    }
    /**
	 * Read BOOLERR record
	 * This record represents a Boolean value or error value
	 * cell.
	 *
	 * --	"OpenOffice.org's Documentation of the Microsoft
	 * 		Excel File Format"
	 */
    private function _readBoolErr()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        // offset: 0; size: 2; row index
        $row = self::_GetInt2d($recordData, 0);
        // offset: 2; size: 2; column index
        $column = self::_GetInt2d($recordData, 2);
        $columnString = PHPExcel_Cell::stringFromColumnIndex($column);
        // Read cell?
        if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
            // offset: 4; size: 2; index to XF record
            $xfIndex = self::_GetInt2d($recordData, 4);
            // offset: 6; size: 1; the boolean value or error value
            $boolErr = ord($recordData[6]);
            // offset: 7; size: 1; 0=boolean; 1=error
            $isError = ord($recordData[7]);
            $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
            switch ($isError) {
                case 0:
                    // boolean
                    $value = (bool) $boolErr;
                    // add cell value
                    $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_BOOL);
                    break;
                case 1:
                    // error type
                    $value = self::_mapErrorCode($boolErr);
                    // add cell value
                    $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_ERROR);
                    break;
            }
            if (!$this->_readDataOnly) {
                // add cell style
                $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
            }
        }
    }
    /**
	 * Read MULBLANK record
	 * This record represents a cell range of empty cells. All
	 * cells are located in the same row
	 *
	 * --	"OpenOffice.org's Documentation of the Microsoft
	 * 		Excel File Format"
	 */
    private function _readMulBlank()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        // offset: 0; size: 2; index to row
        $row = self::_GetInt2d($recordData, 0);
        // offset: 2; size: 2; index to first column
        $fc = self::_GetInt2d($recordData, 2);
        // offset: 4; size: 2 x nc; list of indexes to XF records
        // add style information
        if (!$this->_readDataOnly) {
            for ($i = 0; $i < $length / 2 - 3; ++$i) {
                $columnString = PHPExcel_Cell::stringFromColumnIndex($fc + $i);
                // Read cell?
                if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
                    $xfIndex = self::_GetInt2d($recordData, 4 + 2 * $i);
                    $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
                }
            }
        }
        // offset: 6; size 2; index to last column (not needed)
    }
    /**
	 * Read LABEL record
	 * This record represents a cell that contains a string. In
	 * BIFF8 it is usually replaced by the LABELSST record.
	 * Excel still uses this record, if it copies unformatted
	 * text cells to the clipboard.
	 *
	 * --	"OpenOffice.org's Documentation of the Microsoft
	 * 		Excel File Format"
	 */
    private function _readLabel()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        // offset: 0; size: 2; index to row
        $row = self::_GetInt2d($recordData, 0);
        // offset: 2; size: 2; index to column
        $column = self::_GetInt2d($recordData, 2);
        $columnString = PHPExcel_Cell::stringFromColumnIndex($column);
        // Read cell?
        if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
            // offset: 4; size: 2; XF index
            $xfIndex = self::_GetInt2d($recordData, 4);
            // add cell value
            // todo: what if string is very long? continue record
            if ($this->_version == self::XLS_BIFF8) {
                $string = self::_readUnicodeStringLong(substr($recordData, 6));
                $value = $string['value'];
            } else {
                $string = $this->_readByteStringLong(substr($recordData, 6));
                $value = $string['value'];
            }
            $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
            if (!$this->_readDataOnly) {
                // add cell style
                $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
            }
        }
    }
    /**
	 * Read BLANK record
	 */
    private function _readBlank()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        $recordData = substr($this->_data, $this->_pos + 4, $length);
        // move stream pointer to next record
        $this->_pos += 4 + $length;
        // offset: 0; size: 2; row index
        $row = self::_GetInt2d($recordData, 0);
        // offset: 2; size: 2; col index
        $col = self::_GetInt2d($recordData, 2);
        $columnString = PHPExcel_Cell::stringFromColumnIndex($col);
        // Read cell?
        if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
            // offset: 4; size: 2; XF index
            $xfIndex = self::_GetInt2d($recordData, 4);
            // add style information
            if (!$this->_readDataOnly) {
                $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
            }
        }
    }
    /**
	 * Read MSODRAWING record
	 */
    private function _readMsoDrawing()
    {
        $length = self::_GetInt2d($this->_data, $this->_pos + 2);
        // get spliced record data
Example #20
0
 /**
  * Write SheetData
  *
  * @param	PHPExcel_Shared_XMLWriter		$objWriter		XML Writer
  * @param	PHPExcel_Worksheet				$pSheet			Worksheet
  * @param	string[]						$pStringTable	String table
  * @throws	Exception
  */
 private function _writeSheetData(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pStringTable = null)
 {
     if (is_array($pStringTable)) {
         // Flipped stringtable, for faster index searching
         $aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
         // sheetData
         $objWriter->startElement('sheetData');
         // Get column count
         $colCount = PHPExcel_Cell::columnIndexFromString($pSheet->getHighestColumn());
         // Highest row number
         $highestRow = $pSheet->getHighestRow();
         // Loop through cells
         $cellCollection = $pSheet->getCellCollection();
         $cellsByRow = array();
         foreach ($cellCollection as $cellID) {
             $cell = $pSheet->getCell($cellID);
             $cellsByRow[$cell->getRow()][] = $cell;
         }
         for ($currentRow = 1; $currentRow <= $highestRow; ++$currentRow) {
             // Get row dimension
             $rowDimension = $pSheet->getRowDimension($currentRow);
             // Write current row?
             $writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null;
             if ($writeCurrentRow) {
                 // Start a new row
                 $objWriter->startElement('row');
                 $objWriter->writeAttribute('r', $currentRow);
                 $objWriter->writeAttribute('spans', '1:' . $colCount);
                 // Row dimensions
                 if ($rowDimension->getRowHeight() >= 0) {
                     $objWriter->writeAttribute('customHeight', '1');
                     $objWriter->writeAttribute('ht', PHPExcel_Shared_String::FormatNumber($rowDimension->getRowHeight()));
                 }
                 // Row visibility
                 if ($rowDimension->getVisible() == false) {
                     $objWriter->writeAttribute('hidden', 'true');
                 }
                 // Collapsed
                 if ($rowDimension->getCollapsed() == true) {
                     $objWriter->writeAttribute('collapsed', 'true');
                 }
                 // Outline level
                 if ($rowDimension->getOutlineLevel() > 0) {
                     $objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
                 }
                 // Style
                 if ($rowDimension->getXfIndex() !== null) {
                     $objWriter->writeAttribute('s', $rowDimension->getXfIndex());
                     $objWriter->writeAttribute('customFormat', '1');
                 }
                 // Write cells
                 if (isset($cellsByRow[$currentRow])) {
                     foreach ($cellsByRow[$currentRow] as $cell) {
                         // Write cell
                         $this->_writeCell($objWriter, $pSheet, $cell, $pStringTable, $aFlippedStringTable);
                     }
                 }
                 // End row
                 $objWriter->endElement();
             }
         }
         $objWriter->endElement();
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }
Example #21
0
 public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null)
 {
     if ($this->sheetNameExists($pSheet->getTitle())) {
         throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
     }
     $countCellXfs = count($this->_cellXfCollection);
     foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
         $this->addCellXf(clone $cellXf);
     }
     $pSheet->rebindParent($this);
     foreach ($pSheet->getCellCollection(false) as $cellID) {
         $cell = $pSheet->getCell($cellID);
         $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
     }
     return $this->addSheet($pSheet, $iSheetIndex);
 }
 /**
  * creates another worksheet
  *
  * @return PHPExcel_Worksheet objWorksheet
  */
 public function createProductArray(&$objPHPExcel)
 {
     $produkteAufgaben = new PHPExcel_Worksheet($objPHPExcel);
     $produkteAufgaben->setTitle('Aufgaben Produkte');
     $firstSheet = $objPHPExcel->getSheet(0);
     $anzahlTeilnehmer = $firstSheet->getCell('C4')->getValue();
     $endzeile = $anzahlTeilnehmer + 6;
     $lastColumnRawData = $firstSheet->getHighestColumn();
     $aufgabenwerte = $firstSheet->rangeToArray('G7:' . $lastColumnRawData . $endzeile, 0, true, false);
     //$produkteAufgaben->fromArray($aufgabenwerte, NULL, 'A1', true);
     $transponierteAufgabenwerte = PHPExcel_Calculation_LookupRef::TRANSPOSE($aufgabenwerte);
     //$produkteAufgaben->fromArray($transponierteAufgabenwerte, NULL, 'A150', true);
     $endmatrix = PHPExcel_Calculation_MathTrig::MMULT($transponierteAufgabenwerte, $aufgabenwerte);
     $produkteAufgaben->fromArray($endmatrix, NULL, 'A1', true);
     $lastColumnMMULTData = $produkteAufgaben->getHighestColumn();
     $lastRowMMULTData = $produkteAufgaben->getHighestRow();
     $maxColumn = $lastColumnMMULTData;
     $maxColumn++;
     $writeRow = $lastRowMMULTData + 2;
     for ($column = 'A'; $column != $maxColumn; $column++) {
         $cell = $produkteAufgaben->getCell($column . $writeRow);
         $cell->setValue('=SUM(' . $column . '1:' . $column . $lastRowMMULTData . ')');
     }
     $objPHPExcel->addSheet($produkteAufgaben);
 }
Example #23
0
 /**
  * 
  * @param PHPExcel_Worksheet $worksheet
  * @return void
  */
 private function ParseWorksheet($worksheet)
 {
     // Format is as follows:
     // (gray bg)    [ <description of data> ], <relation1>,    <relationN>
     //              <srcConcept>,              <tgtConcept1>,  <tgtConceptN>
     //              <srcAtomA>,                <tgtAtom1A>,    <tgtAtomNA>
     //              <srcAtomB>,                <tgtAtom1B>,    <tgtAtomNB>
     //              <srcAtomC>,                <tgtAtom1C>,    <tgtAtomNC>
     // Loop through all rows
     $highestrow = $worksheet->getHighestRow();
     $highestcolumn = $worksheet->getHighestColumn();
     $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
     $row = 1;
     // Go to the first row where a table starts.
     for ($i = $row; $i <= $highestrow; $i++) {
         $row = $i;
         $cellvalue = $worksheet->getCell('A' . $row)->getValue();
         if (substr(trim($cellvalue), 0, 1) === '[') {
             break;
         }
     }
     // We are now at the beginning of a table or at the end of the file.
     $lines = array();
     // Line is a buffer of one or more related (subsequent) excel rows
     while ($row <= $highestrow) {
         // Read this line as an array of values
         $line = array();
         // values is a buffer containing the cells in a single excel row
         for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
             $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
             $cell = $worksheet->getCell($columnletter . $row);
             $cellvalue = (string) $cell->getCalculatedValue();
             // overwrite $cellvalue in case of datetime
             // the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
             if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
                 $cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
             }
             $line[] = $cellvalue;
         }
         $lines[] = $line;
         // add line (array of values) to the line buffer
         $row++;
         // Is this relation table done? Then we parse the current values into function calls and reset it
         $firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
         if (substr(trim($firstCellInRow), 0, 1) === '[') {
             // Relation table is complete, so it can be processed.
             $this->ParseLines($lines);
             $lines = array();
         }
     }
     // Last relation table remains to be processed.
     $this->ParseLines($lines);
     $lines = array();
 }
Example #24
0
 protected function getCell($char)
 {
     return $this->sheet->getCell($char . $this->currentRow->getRowIndex());
 }
 /**
  * Write Cell
  *
  * @param	PHPExcel_Shared_XMLWriter	$objWriter				XML Writer
  * @param	PHPExcel_Worksheet			$pSheet					Worksheet
  * @param	PHPExcel_Cell				$pCellAddress			Cell Address
  * @param	string[]					$pStringTable			String table
  * @param	string[]					$pFlippedStringTable	String table (flipped), for faster index searching
  * @throws	PHPExcel_Writer_Exception
  */
 private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null)
 {
     if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
         // Cell
         $pCell = $pSheet->getCell($pCellAddress);
         $objWriter->startElement('c');
         $objWriter->writeAttribute('r', $pCellAddress);
         // Sheet styles
         if ($pCell->getXfIndex() != '') {
             $objWriter->writeAttribute('s', $pCell->getXfIndex());
         }
         // If cell value is supplied, write cell value
         $cellValue = $pCell->getValue();
         if (is_object($cellValue) || $cellValue !== '') {
             // Map type
             $mappedType = $pCell->getDataType();
             // Write data type depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                 // Inline string
                 case 's':
                     // String
                 // String
                 case 'b':
                     // Boolean
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'f':
                     // Formula
                     $calculatedValue = $this->getParentWriter()->getPreCalculateFormulas() ? $pCell->getCalculatedValue() : $cellValue;
                     if (is_string($calculatedValue)) {
                         $objWriter->writeAttribute('t', 'str');
                     }
                     break;
                 case 'e':
                     // Error
                     $objWriter->writeAttribute('t', $mappedType);
             }
             // Write data depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     if (!$cellValue instanceof PHPExcel_RichText) {
                         $objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML(htmlspecialchars($cellValue)));
                     } else {
                         if ($cellValue instanceof PHPExcel_RichText) {
                             $objWriter->startElement('is');
                             $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $cellValue);
                             $objWriter->endElement();
                         }
                     }
                     break;
                 case 's':
                     // String
                     if (!$cellValue instanceof PHPExcel_RichText) {
                         if (isset($pFlippedStringTable[$cellValue])) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$cellValue]);
                         }
                     } else {
                         if ($cellValue instanceof PHPExcel_RichText) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$cellValue->getHashCode()]);
                         }
                     }
                     break;
                 case 'f':
                     // Formula
                     $attributes = $pCell->getFormulaAttributes();
                     if ($attributes['t'] == 'array') {
                         $objWriter->startElement('f');
                         $objWriter->writeAttribute('t', 'array');
                         $objWriter->writeAttribute('ref', $pCellAddress);
                         $objWriter->writeAttribute('aca', '1');
                         $objWriter->writeAttribute('ca', '1');
                         $objWriter->text(substr($cellValue, 1));
                         $objWriter->endElement();
                     } else {
                         $objWriter->writeElement('f', substr($cellValue, 1));
                     }
                     if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
                         if ($this->getParentWriter()->getPreCalculateFormulas()) {
                             //								$calculatedValue = $pCell->getCalculatedValue();
                             if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
                                 $objWriter->writeElement('v', PHPExcel_Shared_String::FormatNumber($calculatedValue));
                             } else {
                                 $objWriter->writeElement('v', '0');
                             }
                         } else {
                             $objWriter->writeElement('v', '0');
                         }
                     }
                     break;
                 case 'n':
                     // Numeric
                     // force point as decimal separator in case current locale uses comma
                     $objWriter->writeElement('v', str_replace(',', '.', $cellValue));
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeElement('v', $cellValue ? '1' : '0');
                     break;
                 case 'e':
                     // Error
                     if (substr($cellValue, 0, 1) == '=') {
                         $objWriter->writeElement('f', substr($cellValue, 1));
                         $objWriter->writeElement('v', substr($cellValue, 1));
                     } else {
                         $objWriter->writeElement('v', $cellValue);
                     }
                     break;
             }
         }
         $objWriter->endElement();
     } else {
         throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
     }
 }
Example #26
0
 /**
  * Add external sheet
  *
  * @param  PHPExcel_Worksheet $pSheet External sheet to add
  * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
  * @throws PHPExcel_Exception
  * @return PHPExcel_Worksheet
  */
 public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null)
 {
     if ($this->sheetNameExists($pSheet->getTitle())) {
         throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
     }
     // count how many cellXfs there are in this workbook currently, we will need this below
     $countCellXfs = count($this->cellXfCollection);
     // copy all the shared cellXfs from the external workbook and append them to the current
     foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
         $this->addCellXf(clone $cellXf);
     }
     // move sheet to this workbook
     $pSheet->rebindParent($this);
     // update the cellXfs
     foreach ($pSheet->getCellCollection(false) as $cellID) {
         $cell = $pSheet->getCell($cellID);
         $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
     }
     return $this->addSheet($pSheet, $iSheetIndex);
 }
Example #27
0
 /**
  *	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();
                     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:
                 $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);
                     }
                     $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue);
                 }
                 $join = $filterColumn->getAndOr();
                 $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join));
                 break;
             case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
                 $ruleValues = array();
                 //var_dump($rules);
                 foreach ($rules as $rule) {
                     //	We should only ever have one Dynamic Filter Rule anyway
                     $dynamicRuleType = $rule->getGrouping();
                     echo '$dynamicRuleType is ', $dynamicRuleType, PHP_EOL;
                     if ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) {
                         //	Number based
                         $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
                         echo 'Average Formula Result is ', $averageFormula, PHP_EOL;
                         $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, NULL, $this->_workSheet->getCell('A1'));
                         $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;
                         $ruleValues[] = array('operator' => $operator, 'value' => $average);
                         $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_ANDOR_OR));
                     } else {
                         //	Date based
                         $columnFilterTests[$columnID] = array('method' => '_filterTypeDynamicFilters', 'arguments' => $ruleValues);
                     }
                 }
                 break;
             case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
                 $ruleValues = array();
                 var_dump($rules);
                 foreach ($rules as $rule) {
                     //	We should only ever have one Dynamic Filter Rule anyway
                 }
                 $columnFilterTests[$columnID] = array('method' => '_filterTypeTopTenFilters', 'arguments' => $ruleValues);
                 break;
         }
     }
     echo 'Column Filter Test CRITERIA', PHP_EOL;
     var_dump($columnFilterTests);
     for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
         echo 'Testing Row = ', $row, PHP_EOL;
         $result = TRUE;
         foreach ($columnFilterTests as $columnID => $columnFilterTest) {
             echo 'Testing cell ', $columnID . $row, PHP_EOL;
             $cellValue = $this->_workSheet->getCell($columnID . $row)->getCalculatedValue();
             echo 'Value is ', $cellValue, PHP_EOL;
             //	Execute the filter test
             $result = $result && call_user_func_array(array('PHPExcel_Worksheet_AutoFilter', $columnFilterTest['method']), array($cellValue, $columnFilterTest['arguments']));
             echo $result ? 'VALID' : 'INVALID', PHP_EOL;
             //	If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
             if (!$result) {
                 break;
             }
         }
         echo $result ? 'SHOW' : 'HIDE', PHP_EOL;
         $this->_workSheet->getRowDimension($row)->setVisible($result);
     }
     return $this;
 }
Example #28
0
 /**
  *
  * @param integer $col
  * @param integer $row 
  * @return \PHPExcel_Cell
  */
 protected function getCell($col, $row)
 {
     $coodrinate = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
     return $this->sheet->getCell($coodrinate);
 }
Example #29
0
 public function refresh(PHPExcel_Worksheet $worksheet)
 {
     if ($this->_dataSource !== NULL) {
         $calcEngine = PHPExcel_Calculation::getInstance();
         $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
         $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
     }
 }
Example #30
0
 /**
  * Sets cells of a single row
  * 
  * @param int $row
  * @param mixed $cell_values
  * @param boolean $header
  * @return Worksheet
  */
 private function _set_row($row, &$data, $header = FALSE)
 {
     $column = 0;
     $type = PHPExcel_Cell_DataType::TYPE_STRING;
     foreach (array_keys($this->columns) as $key) {
         $value = NULL;
         if (is_array($data)) {
             $value = isset($data[$key]) ? $data[$key] : '';
         } elseif (is_object($data)) {
             if (method_exists($data, $key)) {
                 $value = $data->{$key}();
             } elseif (isset($data->{$key})) {
                 $value = $data->{$key};
             }
         }
         // Determine cell type and format
         if ($header === FALSE) {
             $type = Arr::get($this->types, $key);
         }
         $coordinates = PHPExcel_Cell::stringFromColumnIndex($column) . $row;
         // Options
         if (is_array($value)) {
             $options = array_slice($value, 1);
             $validation = $this->_worksheet->getCell($coordinates)->getDataValidation();
             $validation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
             $validation->setAllowBlank(TRUE);
             $validation->setShowDropDown(TRUE);
             $validation->setFormula1('"' . join(',', $options) . '"');
             $value = $value[0];
         }
         // Set cell value
         if ($type !== NULL) {
             $this->_worksheet->setCellValueExplicit($coordinates, $value, $type);
         } else {
             $this->_worksheet->setCellValue($coordinates, $value);
         }
         $column++;
     }
     return $this;
 }