PHP PHPExcel_Worksheet::getHighestColumn方法代码示例

 /**
  * 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();
//.........这里部分代码省略.........

 /**
  * @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);
     }
 }

 /**
  * 
  */
 protected function _styleWorkSheet()
 {
     $highestColumn = $this->_mainSheet->getHighestColumn();
     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
     for ($column = $this->_startCol; $column < $highestColumnIndex; $column++) {
         $this->_mainSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
     }
     $this->_mainSheet->getColumnDimension('B')->setWidth(3);
 }

 /**
  * Are there any more PHPExcel_Cell instances available?
  *
  * @return boolean
  */
 public function valid()
 {
     // columnIndexFromString() returns an index based at one,
     // treat it as a count when comparing it to the base zero
     // position.
     $columnCount = PHPExcel_Cell::columnIndexFromString($this->_subject->getHighestColumn());
     if ($this->_onlyExistingCells) {
         // If we aren't looking at an existing cell, either
         // because the first column doesn't exist or next() has
         // been called onto a nonexistent cell, then loop until we
         // find one, or pass the last column.
         while ($this->_position < $columnCount && !$this->_subject->cellExistsByColumnAndRow($this->_position, $this->_rowIndex)) {
             ++$this->_position;
         }
     }
     return $this->_position < $columnCount;
 }

 /**
  * Разобрать накладную
  *
  * @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;
         }
     }
 }

 /**
  * 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.");
     }
 }

 /**
  * 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);
                     }
                 }
             }
//.........这里部分代码省略.........

 /**
  * (Re)Set the end column
  *
  * @param string	$endColumn	The column address at which to stop iterating
  * @return PHPExcel_Worksheet_ColumnIterator
  */
 public function resetEnd($endColumn = null)
 {
     $endColumn = $endColumn ? $endColumn : $this->_subject->getHighestColumn();
     $this->_endColumn = PHPExcel_Cell::columnIndexFromString($endColumn) - 1;
     return $this;
 }

    /**
     * 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)
				);

			}
		}
//.........这里部分代码省略.........

 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;
 }

 /**
  * 
  * @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();
 }

	/**
	 * Add data to the beginning of the workbook (note the reverse order)
	 * and to the end of the workbook.
	 *
	 * @access public
	 * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
	 */
	function close() {
		$num_sheets = $this->_phpSheet->getParent ()->getSheetCount ();
		
		// Write BOF record
		$this->_storeBof ( 0x0010 );
		
		// Write PRINTHEADERS
		$this->_writePrintHeaders ();
		
		// Write PRINTGRIDLINES
		$this->_writePrintGridlines ();
		
		// Write GRIDSET
		$this->_writeGridset ();
		
		// Calculate column widths
		$this->_phpSheet->calculateColumnWidths ();
		
		// Column dimensions
		$maxCol = PHPExcel_Cell::columnIndexFromString ( $this->_phpSheet->getHighestColumn () ) - 1;
		$columnDimensions = $this->_phpSheet->getColumnDimensions ();
		for($i = 0; $i <= $maxCol; ++ $i) {
			$hidden = 0;
			$level = 0;
			$xfIndex = 15; // there are 15 cell style Xfs
			
			if ($this->_phpSheet->getDefaultColumnDimension ()->getWidth () >= 0) {
				$width = $this->_phpSheet->getDefaultColumnDimension ()->getWidth ();
			} else {
				$width = PHPExcel_Shared_Font::getDefaultColumnWidthByFont ( $this->_phpSheet->getParent ()->getDefaultStyle ()->getFont () );
			}
			
			$columnLetter = PHPExcel_Cell::stringFromColumnIndex ( $i );
			if (isset ( $columnDimensions [$columnLetter] )) {
				$columnDimension = $columnDimensions [$columnLetter];
				if ($columnDimension->getWidth () >= 0) {
					$width = $columnDimension->getWidth ();
				}
				$hidden = $columnDimension->getVisible () ? 0 : 1;
				$level = $columnDimension->getOutlineLevel ();
				$xfIndex = $columnDimension->getXfIndex () + 15; // there are 15 cell
				                                                // style Xfs
			}
			
			// Components of _colinfo:
			// $firstcol first column on the range
			// $lastcol last column on the range
			// $width width to set
			// $xfIndex The optional cell style Xf index to apply to the columns
			// $hidden The optional hidden atribute
			// $level The optional outline level
			$this->_colinfo [] = array (
					$i,
					$i,
					$width,
					$xfIndex,
					$hidden,
					$level 
			);
		}
		
		// Write GUTS
		$this->_writeGuts ();
		
		// Write DEFAULTROWHEIGHT
		if ($this->_BIFF_version == 0x0600) {
			$this->_writeDefaultRowHeight ();
		}
		
		// Write WSBOOL
		$this->_writeWsbool ();
		
		// Write horizontal and vertical page breaks
		$this->_writeBreaks ();
		
		// Write page header
		$this->_writeHeader ();
		
		// Write page footer
		$this->_writeFooter ();
		
		// Write page horizontal centering
		$this->_writeHcenter ();
		
		// Write page vertical centering
		$this->_writeVcenter ();
		
		// Write left margin
		$this->_writeMarginLeft ();
		
		// Write right margin
		$this->_writeMarginRight ();
		
//.........这里部分代码省略.........

 /**
  * Generate table header
  *
  * @param 	PHPExcel_Worksheet	$pSheet		The worksheet for the table we are writing
  * @return	string
  * @throws	Exception
  */
 private function _generateTableHeader($pSheet)
 {
     $identifier = $pSheet->getHashCode();
     // Construct HTML
     $html = '';
     if (!$this->_useInlineCss) {
         $html .= '    <table border="0" cellpadding="0" cellspacing="0" class="sheet' . $identifier . '">' . "\r\n";
     } else {
         $style = isset($this->_cssStyles['table.sheet' . $identifier]) ? $this->_cssStyles['table.sheet' . $identifier] : '';
         $html .= '    <table border="0" cellpadding="0" cellspacing="0" style="' . $style . '">' . "\r\n";
     }
     // Write <col> elements
     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($pSheet->getHighestColumn()) - 1;
     for ($i = 0; $i <= $highestColumnIndex; ++$i) {
         if (!$this->_useInlineCss) {
             $html .= '        <col class="col' . $i . '">' . "\r\n";
         } else {
             $style = isset($this->_cssStyles['table.sheet' . $identifier]) ? $this->_cssStyles['table.sheet' . $identifier . ' col.col' . $i] : '';
             $html .= '        <col style="' . $style . '">' . "\r\n";
         }
     }
     // Return
     return $html;
 }

 /**
  * 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);
 }

 /**
  * Функция преобразования листа 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
//.........这里部分代码省略.........