Example #1
5
 /**
  * Insert a new column, updating all possible related data
  *
  * @param	int	$pBefore	Insert before this one
  * @param	int	$pNumCols	Number of columns to insert
  * @param	int	$pNumRows	Number of rows to insert
  * @throws	Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
 {
     // Get a copy of the cell collection
     /*$aTemp = $pSheet->getCellCollection();
     		$aCellCollection = array();
     		foreach ($aTemp as $key => $value) {
     			$aCellCollection[$key] = clone $value;
     		}*/
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     while ($cell = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             $cell->setXfIndex(0);
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->setCellValue($newCoordinates, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows));
             } else {
                 // Formula should not be adjusted
                 $pSheet->setCellValue($newCoordinates, $cell->getValue());
             }
             // Clear the original cell
             $pSheet->setCellValue($cell->getCoordinate(), '');
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
     if (count($aColumnDimensions) > 0) {
         foreach ($aColumnDimensions as $objColumnDimension) {
             $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
             list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objColumnDimension->getColumnIndex() != $newReference) {
                 $objColumnDimension->setColumnIndex($newReference);
             }
         }
         $pSheet->refreshColumnDimensions();
     }
     // Update worksheet: row dimensions
     $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
     if (count($aRowDimensions) > 0) {
         foreach ($aRowDimensions as $objRowDimension) {
             $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
             list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objRowDimension->getRowIndex() != $newReference) {
                 $objRowDimension->setRowIndex($newReference);
             }
         }
         $pSheet->refreshRowDimensions();
         $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
         for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
             $newDimension = $pSheet->getRowDimension($i);
             $newDimension->setRowHeight($copyDimension->getRowHeight());
             $newDimension->setVisible($copyDimension->getVisible());
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
     // Update worksheet: merge cells
     $aMergeCells = $pSheet->getMergeCells();
     $aNewMergeCells = array();
     // the new array of all merge cells
     foreach ($aMergeCells as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewMergeCells[$newReference] = $newReference;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // Update worksheet: protected cells
     $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
     foreach ($aProtectedCells as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->protectCells($newReference, $value, true);
             $pSheet->unprotectCells($key);
         }
     }
     // Update worksheet: autofilter
     if ($pSheet->getAutoFilter() != '') {
         $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
Example #2
0
 /**
  * @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);
     }
 }
Example #3
0
 /**
  *
  * @throws PhpExcelException
  */
 private function mapHeaders()
 {
     $keys = array_keys($this->loweredFields);
     $columns = array_fill_keys($keys, null);
     $this->loweredFieldNameToExcelColumnMap = [];
     $lastRow = $this->activeSheet->getHighestRow();
     for ($i = $this->headerRow; $i <= $lastRow; $i++) {
         foreach ($this->excelColumnsRange as $columnIndex) {
             $value = $this->activeSheet->getCell($columnIndex . $i)->getCalculatedValue();
             $text = $this->lowerHeaderCellText($value);
             if (array_key_exists($text, $columns)) {
                 $columns[$text] = $columnIndex;
             }
         }
         $this->loweredFieldNameToExcelColumnMap = array_filter($columns);
         if (count($this->loweredFieldNameToExcelColumnMap) > 0) {
             $this->firstDataRow = $i + 1;
             break;
         }
     }
     $missingColumns = array_diff_key($this->loweredToOriginalKeysMap, $this->loweredFieldNameToExcelColumnMap);
     if (count($missingColumns) > 0) {
         throw new PhpExcelException('Missing columns: ' . implode(', ', $missingColumns));
     }
 }
 /**
  * Разобрать накладную
  *
  * @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;
         }
     }
 }
Example #5
0
    /**
     * 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;
 }
Example #7
0
 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;
 }
Example #8
0
 /**
  * Функция преобразования листа Excel в таблицу MySQL, с учетом объединенных строк и столбцов. Значения берутся уже вычисленными
  *
  * @param PHPExcel_Worksheet $worksheet                - Лист Excel
  * @param string             $table_name               - Имя таблицы MySQL
  * @param int|array          $columns_names            - Строка или массив с именами столбцов таблицы MySQL (0 - имена типа column + n). Если указано больше столбцов, чем на листе Excel, будут использованы значения по умолчанию указанных типов столбцов. Если указано ложное значение (null, false, "", 0, -1...) столбец игнорируется
  * @param bool|int           $start_row_index          - Номер строки, с которой начинается обработка данных (например, если 1 строка шапка таблицы). Нумерация начинается с 1, как в Excel
  * @param bool|array         $condition_functions      - Массив функций с условиями добавления строки по значению столбца (столбец => функция)
  * @param bool|array         $transform_functions      - Массив функций для изменения значения столбца (столбец => функция)
  * @param bool|int           $unique_column_for_update - Номер столбца с уникальным значением для обновления таблицы. Работает если $columns_names - массив (название столбца берется из него по [$unique_column_for_update - 1])
  * @param bool|array         $table_types              - Типы столбцов таблицы (используется при создании таблицы), в SQL формате - "INT(11) NOT NULL". Если не указаны, то используется "TEXT NOT NULL"
  * @param bool|array         $table_keys               - Ключевые поля таблицы (тип => столбец)
  * @param string             $table_encoding           - Кодировка таблицы MySQL
  * @param string             $table_engine             - Тип таблицы MySQL
  *
  * @return bool - Флаг, удалось ли выполнить функцию в полном объеме
  */
 private function excel_to_mysql($worksheet, $table_name, $columns_names, $start_row_index, $condition_functions, $transform_functions, $unique_column_for_update, $table_types, $table_keys, $table_encoding, $table_engine)
 {
     // Проверяем соединение с MySQL
     if (!$this->mysql_connect->connect_error) {
         // Строка для названий столбцов таблицы MySQL
         $columns = array();
         // Количество столбцов на листе Excel
         $columns_count = \PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());
         // Если в качестве имен столбцов передан массив, то проверяем соответствие его длинны с количеством столбцов
         if ($columns_names) {
             if (is_array($columns_names)) {
                 $columns_names_count = count($columns_names);
                 if ($columns_names_count < $columns_count) {
                     return false;
                 } elseif ($columns_names_count > $columns_count) {
                     $columns_count = $columns_names_count;
                 }
             } else {
                 return false;
             }
         }
         // Если указаны типы столбцов
         if ($table_types) {
             if (is_array($table_types)) {
                 // Проверяем количество столбцов и типов
                 if (count($table_types) != count($columns_names)) {
                     return false;
                 }
             } else {
                 return false;
             }
         }
         $table_name = "`{$table_name}`";
         // Проверяем, что $columns_names - массив и $unique_column_for_update находиться в его пределах
         if ($unique_column_for_update) {
             $unique_column_for_update = is_array($columns_names) ? $unique_column_for_update <= count($columns_names) ? "`{$columns_names[$unique_column_for_update - 1]}`" : false : false;
         }
         // Перебираем столбцы листа Excel и генерируем строку с именами через запятую
         for ($column = 0; $column < $columns_count; $column++) {
             $column_name = is_array($columns_names) ? $columns_names[$column] : ($columns_names == 0 ? "column{$column}" : $worksheet->getCellByColumnAndRow($column, $columns_names)->getValue());
             $columns[] = $column_name ? "`{$column_name}`" : null;
         }
         $query_string = "DROP TABLE IF EXISTS {$table_name}";
         if (defined("EXCEL_MYSQL_DEBUG")) {
             if (EXCEL_MYSQL_DEBUG) {
                 var_dump($query_string);
             }
         }
         // Удаляем таблицу MySQL, если она существовала (если не указан столбец с уникальным значением для обновления)
         if ($unique_column_for_update ? true : $this->mysql_connect->query($query_string)) {
             $columns_types = $ignore_columns = array();
             // Обходим столбцы и присваиваем типы
             foreach ($columns as $index => $value) {
                 if ($value == null) {
                     $ignore_columns[] = $index;
                     unset($columns[$index]);
                 } else {
                     if ($table_types) {
                         $columns_types[] = "{$value} {$table_types[$index]}";
                     } else {
                         $columns_types[] = "{$value} TEXT NOT NULL";
                     }
                 }
             }
             // Если указаны ключевые поля, то создаем массив ключей
             if ($table_keys) {
                 $columns_keys = array();
                 foreach ($table_keys as $key => $value) {
                     $columns_keys[] = "{$value} (`{$key}`)";
                 }
                 $columns_keys_list = implode(", ", $columns_keys);
                 $columns_keys = ", {$columns_keys_list}";
             } else {
                 $columns_keys = null;
             }
             $columns_types_list = implode(", ", $columns_types);
             $query_string = "CREATE TABLE IF NOT EXISTS {$table_name} ({$columns_types_list}{$columns_keys}) COLLATE = '{$table_encoding}' ENGINE = {$table_engine}";
             if (defined("EXCEL_MYSQL_DEBUG")) {
                 if (EXCEL_MYSQL_DEBUG) {
                     var_dump($query_string);
                 }
             }
             // Создаем таблицу MySQL
             if ($this->mysql_connect->query($query_string)) {
                 // Коллекция значений уникального столбца для удаления несуществующих строк в файле импорта (используется при обновлении)
                 $id_list_in_import = array();
                 // Количество строк на листе Excel
                 $rows_count = $worksheet->getHighestRow();
                 // Получаем массив всех объединенных ячеек
                 $all_merged_cells = $worksheet->getMergeCells();
                 // Перебираем строки листа Excel
                 for ($row = $start_row_index ? $start_row_index : (is_array($columns_names) ? 1 : $columns_names + 1); $row <= $rows_count; $row++) {
                     // Строка со значениями всех столбцов в строке листа Excel
                     $values = array();
                     // Перебираем столбцы листа Excel
                     for ($column = 0; $column < $columns_count; $column++) {
                         if (in_array($column, $ignore_columns)) {
                             continue;
                         }
                         // Строка со значением объединенных ячеек листа Excel
                         $merged_value = null;
                         // Ячейка листа Excel
                         $cell = $worksheet->getCellByColumnAndRow($column, $row);
                         // Перебираем массив объединенных ячеек листа Excel
                         foreach ($all_merged_cells as $merged_cells) {
                             // Если текущая ячейка - объединенная,
                             if ($cell->isInRange($merged_cells)) {
                                 // то вычисляем значение первой объединенной ячейки, и используем её в качестве значения текущей ячейки
                                 $merged_value = explode(":", $merged_cells);
                                 $merged_value = $worksheet->getCell($merged_value[0])->getValue();
                                 break;
                             }
                         }
                         // Проверяем, что ячейка не объединенная: если нет, то берем ее значение, иначе значение первой объединенной ячейки
                         $value = strlen($merged_value) == 0 ? $cell->getValue() : $merged_value;
                         // Если задан массив функций с условиями
                         if ($condition_functions) {
                             if (isset($condition_functions[$columns_names[$column]])) {
                                 // Проверяем условие
                                 if (!$condition_functions[$columns_names[$column]]($value)) {
                                     break;
                                 }
                             }
                         }
                         $value = $transform_functions ? isset($transform_functions[$columns_names[$column]]) ? $transform_functions[$columns_names[$column]]($value) : $value : $value;
                         $values[] = "'{$this->mysql_connect->real_escape_string($value)}'";
                     }
                     // Если количество столбцов не равно количеству значений, значит строка не прошла проверку
                     if ($columns_count - count($ignore_columns) != count($values)) {
                         continue;
                     }
                     // Добавляем или проверяем обновлять ли значение
                     $add_to_table = $unique_column_for_update ? false : true;
                     // Если обновляем
                     if ($unique_column_for_update) {
                         // Объединяем массивы для простоты работы
                         $columns_values = array_combine($columns, $values);
                         // Сохраняем уникальное значение
                         $id_list_in_import[] = $columns_values[$unique_column_for_update];
                         // Создаем условие выборки
                         $where = " WHERE {$unique_column_for_update} = {$columns_values[$unique_column_for_update]}";
                         // Удаляем столбец выборки
                         unset($columns_values[$unique_column_for_update]);
                         $query_string = "SELECT COUNT(*) AS count FROM {$table_name}{$where}";
                         if (defined("EXCEL_MYSQL_DEBUG")) {
                             if (EXCEL_MYSQL_DEBUG) {
                                 var_dump($query_string);
                             }
                         }
                         // Проверяем есть ли запись в таблице
                         $count = $this->mysql_connect->query($query_string);
                         $count = $count->fetch_assoc();
                         // Если есть, то создаем запрос и обновляем
                         if (intval($count['count']) != 0) {
                             $set = array();
                             foreach ($columns_values as $column => $value) {
                                 $set[] = "{$column} = {$value}";
                             }
                             $set_list = implode(", ", $set);
                             $query_string = "UPDATE {$table_name} SET {$set_list}{$where}";
                             if (defined("EXCEL_MYSQL_DEBUG")) {
                                 if (EXCEL_MYSQL_DEBUG) {
                                     var_dump($query_string);
                                 }
                             }
                             if (!$this->mysql_connect->query($query_string)) {
                                 return false;
                             }
                         } else {
                             $add_to_table = true;
                         }
                     }
                     // Добавляем строку в таблицу MySQL
                     if ($add_to_table) {
                         $columns_list = implode(", ", $columns);
                         $values_list = implode(", ", $values);
                         $query_string = "INSERT INTO {$table_name} ({$columns_list}) VALUES ({$values_list})";
                         if (defined("EXCEL_MYSQL_DEBUG")) {
                             if (EXCEL_MYSQL_DEBUG) {
                                 var_dump($query_string);
                             }
                         }
                         if (!$this->mysql_connect->query($query_string)) {
                             return false;
                         }
                     }
                 }
                 if (!empty($id_list_in_import)) {
                     $id_list = implode(", ", $id_list_in_import);
                     $query_string = "DELETE FROM {$table_name} WHERE {$unique_column_for_update} NOT IN ({$id_list})";
                     if (defined("EXCEL_MYSQL_DEBUG")) {
                         if (EXCEL_MYSQL_DEBUG) {
                             var_dump($query_string);
                         }
                     }
                     $this->mysql_connect->query($query_string);
                 }
                 return true;
             }
         }
     }
     return false;
 }
Example #9
0
 /**
  * 
  * @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);
 }
Example #11
0
 /**
  * Write SheetData
  *
  * @param	PHPExcel_Shared_XMLWriter		$objWriter		XML Writer
  * @param	PHPExcel_Worksheet				$pSheet			Worksheet
  * @param	string[]						$pStringTable	String table
  * @throws	Exception
  */
 private function _writeSheetData(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pStringTable = null)
 {
     if (is_array($pStringTable)) {
         // Flipped stringtable, for faster index searching
         $aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
         // sheetData
         $objWriter->startElement('sheetData');
         // Get column count
         $colCount = PHPExcel_Cell::columnIndexFromString($pSheet->getHighestColumn());
         // Highest row number
         $highestRow = $pSheet->getHighestRow();
         // Loop through cells
         $cellCollection = $pSheet->getCellCollection();
         $cellsByRow = array();
         foreach ($cellCollection as $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.");
     }
 }
Example #12
0
 /**
  * 
  * @param PHPExcel_Worksheet $worksheet
  * @return void
  */
 private function ParseWorksheet($worksheet)
 {
     // Format is as follows:
     // (gray bg)    [ <description of data> ], <relation1>,    <relationN>
     //              <srcConcept>,              <tgtConcept1>,  <tgtConceptN>
     //              <srcAtomA>,                <tgtAtom1A>,    <tgtAtomNA>
     //              <srcAtomB>,                <tgtAtom1B>,    <tgtAtomNB>
     //              <srcAtomC>,                <tgtAtom1C>,    <tgtAtomNC>
     // Loop through all rows
     $highestrow = $worksheet->getHighestRow();
     $highestcolumn = $worksheet->getHighestColumn();
     $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
     $row = 1;
     // Go to the first row where a table starts.
     for ($i = $row; $i <= $highestrow; $i++) {
         $row = $i;
         $cellvalue = $worksheet->getCell('A' . $row)->getValue();
         if (substr(trim($cellvalue), 0, 1) === '[') {
             break;
         }
     }
     // We are now at the beginning of a table or at the end of the file.
     $lines = array();
     // Line is a buffer of one or more related (subsequent) excel rows
     while ($row <= $highestrow) {
         // Read this line as an array of values
         $line = array();
         // values is a buffer containing the cells in a single excel row
         for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
             $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
             $cell = $worksheet->getCell($columnletter . $row);
             $cellvalue = (string) $cell->getCalculatedValue();
             // overwrite $cellvalue in case of datetime
             // the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
             if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
                 $cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
             }
             $line[] = $cellvalue;
         }
         $lines[] = $line;
         // add line (array of values) to the line buffer
         $row++;
         // Is this relation table done? Then we parse the current values into function calls and reset it
         $firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
         if (substr(trim($firstCellInRow), 0, 1) === '[') {
             // Relation table is complete, so it can be processed.
             $this->ParseLines($lines);
             $lines = array();
         }
     }
     // Last relation table remains to be processed.
     $this->ParseLines($lines);
     $lines = array();
 }
 public function count()
 {
     return $this->_subject->getHighestRow();
 }
Example #14
0
 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;
         }
     }
 }
Example #15
0
 /**
  * Indicate if more rows exist in the worksheet
  *
  * @return boolean
  */
 public function valid()
 {
     return $this->_position <= $this->_subject->getHighestRow();
 }
Example #16
0
 /**
  * Insert a new column or row, updating all possible related data
  *
  * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
  * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
  * @param   PHPExcel_Worksheet  $pSheet     The worksheet that we're editing
  * @throws  PHPExcel_Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = NULL)
 {
     $remove = $pNumCols < 0 || $pNumRows < 0;
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     while ($cellID = $remove ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
         if ($cellIndex - 1 + $pNumCols < 0) {
             continue;
         }
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             } else {
                 // Formula should not be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
             }
             // Clear the original cell
             $pSheet->getCellCacheController()->deleteCacheData($cellID);
         } else {
             /*	We don't need to update styles for rows/columns before our insertion position,
             			but we do still need to adjust any formulae	in those cells					*/
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             }
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: row dimensions
     $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: page breaks
     $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: comments
     $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: hyperlinks
     $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: data validations
     $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: merge cells
     $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: protected cells
     $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: autofilter
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());
             if (count($autoFilterColumns) > 0) {
                 sscanf($pBefore, '%[A-Z]%d', $column, $row);
                 $columnIndex = PHPExcel_Cell::columnIndexFromString($column);
                 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
                 if ($columnIndex <= $rangeEnd[0]) {
                     if ($pNumCols < 0) {
                         //	If we're actually deleting any columns that fall within the autofilter range,
                         //		then we delete any rules for those columns
                         $deleteColumn = $columnIndex + $pNumCols - 1;
                         $deleteCount = abs($pNumCols);
                         for ($i = 1; $i <= $deleteCount; ++$i) {
                             if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) {
                                 $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
                             }
                             ++$deleteColumn;
                         }
                     }
                     $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0];
                     //	Shuffle columns in autofilter range
                     if ($pNumCols > 0) {
                         //	For insert, we shuffle from end to beginning to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         $startColRef = $startCol;
                         $endColRef = $rangeEnd[0];
                         $toColRef = $rangeEnd[0] + $pNumCols;
                         do {
                             $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef - 1), PHPExcel_Cell::stringFromColumnIndex($toColRef - 1));
                             --$endColRef;
                             --$toColRef;
                         } while ($startColRef <= $endColRef);
                     } else {
                         //	For delete, we shuffle from beginning to end to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         do {
                             $autoFilter->shiftColumn($startColID, $toColID);
                             ++$startColID;
                             ++$toColID;
                         } while ($startColID != $endColID);
                     }
                 }
             }
         }
         $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
/**
 * 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);
    }
}
Example #18
0
 /**
  * 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++;
 }