/** * 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; }
/** * Set Worksheet * * @param PHPExcel_Worksheet $pValue * @param bool $pOverrideOld * Worksheet has already been assigned, overwrite it and remove image from old Worksheet? * @throws PHPExcel_Exception * @return PHPExcel_Worksheet_BaseDrawing */ public function setWorksheet(PHPExcel_Worksheet $pValue = null, $pOverrideOld = false) { if (is_null($this->_worksheet)) { // Add drawing to PHPExcel_Worksheet $this->_worksheet = $pValue; $this->_worksheet->getCell($this->_coordinates); $this->_worksheet->getDrawingCollection()->append($this); } else { if ($pOverrideOld) { // Remove drawing from old PHPExcel_Worksheet $iterator = $this->_worksheet->getDrawingCollection()->getIterator(); while ($iterator->valid()) { if ($iterator->current()->getHashCode() == $this->getHashCode()) { $this->_worksheet->getDrawingCollection()->offsetUnset($iterator->key()); $this->_worksheet = null; break; } } // Set new PHPExcel_Worksheet $this->setWorksheet($pValue); } else { throw new PHPExcel_Exception("A PHPExcel_Worksheet has already been assigned. Drawings can only exist on one PHPExcel_Worksheet."); } } return $this; }
/** * * @throws PhpExcelException */ private function mapHeaders() { $keys = array_keys($this->loweredFields); $columns = array_fill_keys($keys, null); $this->loweredFieldNameToExcelColumnMap = []; $lastRow = $this->activeSheet->getHighestRow(); for ($i = $this->headerRow; $i <= $lastRow; $i++) { foreach ($this->excelColumnsRange as $columnIndex) { $value = $this->activeSheet->getCell($columnIndex . $i)->getCalculatedValue(); $text = $this->lowerHeaderCellText($value); if (array_key_exists($text, $columns)) { $columns[$text] = $columnIndex; } } $this->loweredFieldNameToExcelColumnMap = array_filter($columns); if (count($this->loweredFieldNameToExcelColumnMap) > 0) { $this->firstDataRow = $i + 1; break; } } $missingColumns = array_diff_key($this->loweredToOriginalKeysMap, $this->loweredFieldNameToExcelColumnMap); if (count($missingColumns) > 0) { throw new PhpExcelException('Missing columns: ' . implode(', ', $missingColumns)); } }
protected function getCellValue(\PHPExcel_Worksheet $sheet, $coord) { $cell = $sheet->getCell($coord); if ($cell) { return $cell->getValue(); } }
/** * Create worksheet stringtable * * @param PHPExcel_Worksheet $pSheet Worksheet * @param string[] $pExistingTable Existing table to eventually merge with * @return string[] String table for worksheet * @throws PHPExcel_Writer_Exception */ public function createStringTable($pSheet = null, $pExistingTable = null) { if ($pSheet !== null) { // Create string lookup table $aStringTable = array(); $cellCollection = null; $aFlippedStringTable = null; // For faster lookup // Is an existing table given? if ($pExistingTable !== null && is_array($pExistingTable)) { $aStringTable = $pExistingTable; } // Fill index array $aFlippedStringTable = $this->flipStringTable($aStringTable); // Loop through cells foreach ($pSheet->getCellCollection() as $cellID) { $cell = $pSheet->getCell($cellID); $cellValue = $cell->getValue(); if (!is_object($cellValue) && $cellValue !== null && $cellValue !== '' && !isset($aFlippedStringTable[$cellValue]) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING || $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING2 || $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NULL)) { $aStringTable[] = $cellValue; $aFlippedStringTable[$cellValue] = true; } elseif ($cellValue instanceof PHPExcel_RichText && $cellValue !== null && !isset($aFlippedStringTable[$cellValue->getHashCode()])) { $aStringTable[] = $cellValue; $aFlippedStringTable[$cellValue->getHashCode()] = true; } } return $aStringTable; } else { throw new PHPExcel_Writer_Exception("Invalid PHPExcel_Worksheet object passed."); } }
/** * */ protected function _addFilters() { $pFilters = $this->_content->getElementsByTagName('p'); if (empty($pFilters->length)) { return false; } $filters = array(); foreach ($pFilters->item(0)->childNodes as $child) { $nodeValue = trim($child->nodeValue); if (!empty($nodeValue)) { $filters[] = $nodeValue; } } $this->_mainSheet->getCell('C6')->setValue(implode(' ', $filters))->getStyle()->getAlignment()->setWrapText(true); $this->_mainSheet->mergeCells('C6:T6'); }
protected function writeData(\PHPExcel_Worksheet $worksheet) { foreach ($this->activeDataProvider->getModels() as $row => $model) { foreach ($this->columns as $col => $column) { $columnIndex = \PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 2); switch ($column->format) { case Column::FormatRaw: $worksheet->setCellValue($columnIndex, $column->getValue($model)); break; case Column::FormatUri: $worksheet->setCellValue($columnIndex, $column->getValue($model)); $worksheet->getCell($columnIndex)->getHyperlink()->setUrl('"' . $column->getValue($model) . '"'); break; } } } }
/** * Функция преобразования листа Excel в таблицу MySQL, с учетом объединенных строк и столбцов. Значения берутся уже вычисленными * * @param PHPExcel_Worksheet $worksheet - Лист Excel * @param string $table_name - Имя таблицы MySQL * @param int|array $columns_names - Строка или массив с именами столбцов таблицы MySQL (0 - имена типа column + n). Если указано больше столбцов, чем на листе Excel, будут использованы значения по умолчанию указанных типов столбцов. Если указано ложное значение (null, false, "", 0, -1...) столбец игнорируется * @param bool|int $start_row_index - Номер строки, с которой начинается обработка данных (например, если 1 строка шапка таблицы). Нумерация начинается с 1, как в Excel * @param bool|array $condition_functions - Массив функций с условиями добавления строки по значению столбца (столбец => функция) * @param bool|array $transform_functions - Массив функций для изменения значения столбца (столбец => функция) * @param bool|int $unique_column_for_update - Номер столбца с уникальным значением для обновления таблицы. Работает если $columns_names - массив (название столбца берется из него по [$unique_column_for_update - 1]) * @param bool|array $table_types - Типы столбцов таблицы (используется при создании таблицы), в SQL формате - "INT(11) NOT NULL". Если не указаны, то используется "TEXT NOT NULL" * @param bool|array $table_keys - Ключевые поля таблицы (тип => столбец) * @param string $table_encoding - Кодировка таблицы MySQL * @param string $table_engine - Тип таблицы MySQL * * @return bool - Флаг, удалось ли выполнить функцию в полном объеме */ private function excel_to_mysql($worksheet, $table_name, $columns_names, $start_row_index, $condition_functions, $transform_functions, $unique_column_for_update, $table_types, $table_keys, $table_encoding, $table_engine) { // Проверяем соединение с MySQL if (!$this->mysql_connect->connect_error) { // Строка для названий столбцов таблицы MySQL $columns = array(); // Количество столбцов на листе Excel $columns_count = \PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn()); // Если в качестве имен столбцов передан массив, то проверяем соответствие его длинны с количеством столбцов if ($columns_names) { if (is_array($columns_names)) { $columns_names_count = count($columns_names); if ($columns_names_count < $columns_count) { return false; } elseif ($columns_names_count > $columns_count) { $columns_count = $columns_names_count; } } else { return false; } } // Если указаны типы столбцов if ($table_types) { if (is_array($table_types)) { // Проверяем количество столбцов и типов if (count($table_types) != count($columns_names)) { return false; } } else { return false; } } $table_name = "`{$table_name}`"; // Проверяем, что $columns_names - массив и $unique_column_for_update находиться в его пределах if ($unique_column_for_update) { $unique_column_for_update = is_array($columns_names) ? $unique_column_for_update <= count($columns_names) ? "`{$columns_names[$unique_column_for_update - 1]}`" : false : false; } // Перебираем столбцы листа Excel и генерируем строку с именами через запятую for ($column = 0; $column < $columns_count; $column++) { $column_name = is_array($columns_names) ? $columns_names[$column] : ($columns_names == 0 ? "column{$column}" : $worksheet->getCellByColumnAndRow($column, $columns_names)->getValue()); $columns[] = $column_name ? "`{$column_name}`" : null; } $query_string = "DROP TABLE IF EXISTS {$table_name}"; if (defined("EXCEL_MYSQL_DEBUG")) { if (EXCEL_MYSQL_DEBUG) { var_dump($query_string); } } // Удаляем таблицу MySQL, если она существовала (если не указан столбец с уникальным значением для обновления) if ($unique_column_for_update ? true : $this->mysql_connect->query($query_string)) { $columns_types = $ignore_columns = array(); // Обходим столбцы и присваиваем типы foreach ($columns as $index => $value) { if ($value == null) { $ignore_columns[] = $index; unset($columns[$index]); } else { if ($table_types) { $columns_types[] = "{$value} {$table_types[$index]}"; } else { $columns_types[] = "{$value} TEXT NOT NULL"; } } } // Если указаны ключевые поля, то создаем массив ключей if ($table_keys) { $columns_keys = array(); foreach ($table_keys as $key => $value) { $columns_keys[] = "{$value} (`{$key}`)"; } $columns_keys_list = implode(", ", $columns_keys); $columns_keys = ", {$columns_keys_list}"; } else { $columns_keys = null; } $columns_types_list = implode(", ", $columns_types); $query_string = "CREATE TABLE IF NOT EXISTS {$table_name} ({$columns_types_list}{$columns_keys}) COLLATE = '{$table_encoding}' ENGINE = {$table_engine}"; if (defined("EXCEL_MYSQL_DEBUG")) { if (EXCEL_MYSQL_DEBUG) { var_dump($query_string); } } // Создаем таблицу MySQL 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; }
/** * 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(); }
/** * 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; }
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; }
/** * 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); } }
/** * 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; }
/** * 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); } } }
/** * 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(); }
{ $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
/** * 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."); } }
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); }
/** * * @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(); }
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."); } }
/** * 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); }
/** * 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; }
/** * * @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); }
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); } }
/** * 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; }