/** * 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(); }
/** * @param \SplFileObject $file Excel file * @param integer $headerRowNumber Optional number of header row * @param integer $activeSheet Index of active sheet to read from * @param boolean $readOnly If set to false, the reader take care of the excel formatting (slow) */ public function __construct(\SplFileObject $file, $headerRowNumber = null, $activeSheet = null, $readOnly = true) { $reader = \PHPExcel_IOFactory::createReaderForFile($file->getPathName()); $reader->setReadDataOnly($readOnly); /** @var \PHPExcel $excel */ $excel = $reader->load($file->getPathname()); if (null !== $activeSheet) { $excel->setActiveSheetIndex($activeSheet); } $this->worksheet = $excel->getActiveSheet(); $this->maxColumn = $this->worksheet->getHighestColumn(); $this->maxRow = $this->worksheet->getHighestRow(); if (null !== $headerRowNumber) { $this->setHeaderRowNumber($headerRowNumber); } }
/** * * @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)); } }
/** * Разобрать накладную * * @throws ParseTorg12Exception */ public function parse() { if (!file_exists($this->filePath)) { throw new ParseTorg12Exception('Указан некорректный путь к файлу накладной'); } // читаем файл в формате Excel по форме ТОРГ12 try { $objPHPExcel = \PHPExcel_IOFactory::load($this->filePath); } catch (\Exception $e) { $errorMsg = 'Невозможно прочитать загруженный файл: ' . $e->getMessage(); throw new ParseTorg12Exception($errorMsg); } // создаем накладную $this->invoice = new models\Invoice(); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $this->setWorksheet($worksheet); // очищаем список критических ошибок, т.к. накладная может быть не на первом листе $this->invoice->errors = []; // определяем последнюю строку документа $this->highestRow = $this->worksheet->getHighestRow(); // определяем последний столбец документа $this->highestColumn = \PHPExcel_Cell::columnIndexFromString($this->worksheet->getHighestColumn()); // разбираем заголовок накладной $this->parseHeader(); // разбираем заголовок строк накладной $this->parseRowsHeader(); // разбираем строки накладной, выкидываем дубли заголовка и т.п. $this->parseRows(); // обрабатываем строки накладной $this->processRows(); // если в накладной есть строки, то не обрабатываем остальные листы if (count($this->invoice->rows)) { // проверяем, что обработаны все строки накладной $lastRow = end($this->invoice->rows); if ($lastRow->num != count($this->invoice->rows)) { $this->invoice->errors['count_rows'] = 'Порядковый номер последней строки накладной не совпадает с количеством обработанных строк'; } break; } } }
/** * 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 ); // Remove cell styles? $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols ) . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ); } } if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow + $pNumRows)) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ); } } // Loop trough 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? if ( (PHPExcel_Cell::columnIndexFromString( $cell->getColumn() ) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) && ($cell->getRow() >= $beforeRow) ) { // Update cell styles $pSheet->duplicateStyle( $pSheet->getStyle($cell->getCoordinate()), $newCoordinates . ':' . $newCoordinates ); $pSheet->duplicateStyle( $pSheet->getDefaultStyle(), $cell->getCoordinate() . ':' . $cell->getCoordinate() ); // 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 $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ), ($beforeColumn . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols ) . $i) ); } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ), (PHPExcel_Cell::stringFromColumnIndex($i) . $beforeRow) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1 + $pNumRows)) ); } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); 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); 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: merge cells $aMergeCells = array_reverse($pSheet->getMergeCells(), true); foreach ($aMergeCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->mergeCells( $newReference ); $pSheet->unmergeCells( $key ); } } // 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->setFreezePane( $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(); }
/** * (Re)Set the end row * * @param integer $endRow The row number at which to stop iterating * @return PHPExcel_Worksheet_RowIterator */ public function resetEnd($endRow = null) { $this->_endRow = $endRow ? $endRow : $this->_subject->getHighestRow(); return $this; }
function getDayTest(PHPExcel $objPHPExcel, PHPExcel_Worksheet $sheet, $col = 'A', $row = 1, $day = 'ПОНЕДЕЛЬНИК') { $b = false; $index_sheet = $objPHPExcel->getActiveSheetIndex(); $rowHiestIndex = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn($row)); for ($i = 0; $i <= $rowHiestIndex; $i++) { $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row); if ($cell_value == $day) { return $b = true; } } if (!$b) { $colHiestIndex = $sheet->getHighestRow($col); for ($i = 0; $i <= $colHiestIndex; $i++) { $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $col, $i); if ($cell_value == $day) { return $b = true; } } } return $b; }
/** * Функция преобразования листа 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; }
/** * * @param PHPExcel_Worksheet $sheet */ private function loadSheet($sheet) { $name = $sheet->getTitle(); $entity_name = "Entity_{$name}"; if (!class_exists($entity_name, true)) { print "entity class not found. skip. {$entity_name}"; return; } // ヘッダ $rowIterator = $sheet->getRowIterator(); if (!$rowIterator->valid()) { print "no data. skip. {$entity_name}"; return; } $col = array(); $row = $rowIterator->current(); $cellIterator = $row->getCellIterator(); while ($cellIterator->valid()) { $cell = $cellIterator->current(); $col[] = trim($cell->getValue()); $cellIterator->next(); } $rowIterator->next(); print "load {$entity_name} .... "; // データを登録 while ($rowIterator->valid()) { $row = $rowIterator->current(); $entity = $this->c->getEntity($entity_name); $cellIterator = $row->getCellIterator(); $i = 0; while ($cellIterator->valid()) { $cell = $cellIterator->current(); $prop = $col[$i]; $entity->{$prop} = trim($cell->getValue()); $cellIterator->next(); $i++; } $entity->insert(); $rowIterator->next(); } print $sheet->getHighestRow() - 1 . " rows loaded.\n"; return; }
/** * 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); }
/** * 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 $cell) { $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."); } }
/** * * @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(); }
public function count() { return $this->_subject->getHighestRow(); }
function lastRow(PHPExcel_Worksheet $sheet, $indexCellDAY) { //получаем индекс последней строки в ЛИСТЕ $higestRow = $sheet->getHighestRow(); for ($i = $indexCellDAY; $i <= $higestRow; $i++) { $tempRow = $sheet->getCellByColumnAndRow(0, $i)->getFormattedValue(); if (mb_strtoupper(trim($tempRow)) == "СУББОТА") { return ++$i; } } }
/** * Indicate if more rows exist in the worksheet * * @return boolean */ public function valid() { return $this->_position <= $this->_subject->getHighestRow(); }
/** * 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(); }
/** * Format the columns of the worksheet according to the attribute definition * * @param PHPExcel_Worksheet $worksheet * @param array $columns */ function formatWSColumns($worksheet, $columns) { $fmt_code = array('T' => PHPExcel_Style_NumberFormat::FORMAT_TEXT, 'D' => 'yyyy-mm-dd', 'N' => PHPExcel_Style_NumberFormat::FORMAT_NUMBER, 'F' => '0.0', 'P' => '0.0%', '' => null); $highestRow = $worksheet->getHighestRow(); $colFmt = $columns[0][1]; $colidx = 0; $start = 0; $idx = 0; foreach ($columns as $col) { if ($col[2]) { // set the column width if a specific value is defined $colLetter = PHPExcel_Cell::stringFromColumnIndex($colidx); $worksheet->getColumnDimension($colLetter)->setWidth($col[2]); } if ($col[3] and $col[3] == 'wrap') { // set text wrapping attribute if requested $colLetter = PHPExcel_Cell::stringFromColumnIndex($colidx); $range = $colLetter . '2:' . $colLetter . $highestRow; $worksheet->getStyle($range)->getAlignment()->setWrapText(true); } if ($colFmt != $col[1]) { // assign the format to the range if a format is explicitly required $start_col = PHPExcel_Cell::stringFromColumnIndex($start); $end_col = PHPExcel_Cell::stringFromColumnIndex($start + $idx - 1); $range = $start_col . '2:' . $end_col . $highestRow; $colFmtSpec = $fmt_code[$colFmt]; if ($colFmtSpec) { $worksheet->getStyle($range)->getNumberFormat()->setFormatCode($colFmtSpec); } $colFmt = $col[1]; $start = $start + $idx; $idx = 1; } else { // still in the same format range $idx += 1; } $colidx++; } $start_col = PHPExcel_Cell::stringFromColumnIndex($start); $end_col = PHPExcel_Cell::stringFromColumnIndex($start + $idx - 1); $range = $start_col . '2:' . $end_col . $highestRow; $colFmtSpec = $fmt_code[$colFmt]; if ($colFmtSpec) { // assign up to the end the format if explicitly required $worksheet->getStyle($range)->getNumberFormat()->setFormatCode($colFmtSpec); } }
/** * Constructor * * @param int &$str_total Total number of strings * @param int &$str_unique Total number of unique strings * @param array &$str_table String Table * @param array &$colors Colour Table * @param mixed $parser The formula parser created for the Workbook * @param boolean $preCalculateFormulas Flag indicating whether formulas should be calculated or just written * @param string $phpSheet The worksheet to write * @param PHPExcel_Worksheet $phpSheet */ public function __construct(&$str_total, &$str_unique, &$str_table, &$colors, $parser, $preCalculateFormulas, $phpSheet) { // It needs to call its parent's constructor explicitly parent::__construct(); // change BIFFwriter limit for CONTINUE records // $this->_limit = 8224; $this->_preCalculateFormulas = $preCalculateFormulas; $this->_str_total =& $str_total; $this->_str_unique =& $str_unique; $this->_str_table =& $str_table; $this->_colors =& $colors; $this->_parser = $parser; $this->_phpSheet = $phpSheet; //$this->ext_sheets = array(); //$this->offset = 0; $this->_xls_strmax = 255; $this->_colinfo = array(); $this->_selection = array(0, 0, 0, 0); $this->_active_pane = 3; $this->_print_headers = 0; $this->_outline_style = 0; $this->_outline_below = 1; $this->_outline_right = 1; $this->_outline_on = 1; $this->_fntHashIndex = array(); // calculate values for DIMENSIONS record $minR = 1; $minC = 'A'; $maxR = $this->_phpSheet->getHighestRow(); $maxC = $this->_phpSheet->getHighestColumn(); // Determine lowest and highest column and row // $this->_firstRowIndex = ($minR > 65535) ? 65535 : $minR; $this->_lastRowIndex = $maxR > 65535 ? 65535 : $maxR; $this->_firstColumnIndex = PHPExcel_Cell::columnIndexFromString($minC); $this->_lastColumnIndex = PHPExcel_Cell::columnIndexFromString($maxC); // if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255; if ($this->_lastColumnIndex > 255) { $this->_lastColumnIndex = 255; } $this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection()); }
/** * Add a new row to a {@link PHPExcel_Worksheet} based of a * {@link DataObjectInterface} * @param PHPExcel_Worksheet $sheet * @param DataObjectInterface $item * @param array $fields List of fields to include * @return PHPExcel_Worksheet */ protected function addRow(PHPExcel_Worksheet &$sheet, DataObjectInterface $item, array $fields) { $row = $sheet->getHighestRow() + 1; $col = 0; foreach ($fields as $field => $type) { $sheet->setCellValueByColumnAndRow($col, $row, $item->{$field}); $col++; } return $sheet; }
public function initializeObject() { $this->objPHPExcel = PHPExcel_IOFactory::load(GeneralUtility::getFileAbsFileName($this->data)); $this->worksheet = $this->objPHPExcel->getSheet(0); $highestColumn = $this->worksheet->getHighestColumn(); // e.g 'F' $this->highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $this->totalRowCount = $this->worksheet->getHighestRow(); $this->header = array(); for ($col = 0; $col < $this->highestColumnIndex; ++$col) { $cell = $this->worksheet->getCellByColumnAndRow($col, 1); $this->header[$col] = $cell->getValue(); } $this->rowPointer++; }