public function __construct(\PHPExcel_Worksheet $worksheet, $parent_filename)
 {
     $this->name = $this->genName($parent_filename, $worksheet->getTitle());
     $row_it = $worksheet->getRowIterator();
     $this->header = new \excel2sql\SqlHeader($row_it->current());
     $this->entries = array();
     for ($row_it->next(); $row_it->valid(); $row_it->next()) {
         $rowindex = $row_it->current()->getRowIndex();
         $this->entries[] = new \excel2sql\SqlEntry($rowindex, $this->header, $worksheet);
     }
 }
Example #2
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;
 }
Example #3
0
 /**
  * Read BLANK record
  */
 private function _readBlank()
 {
     $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
     $recordData = substr($this->_data, $this->_pos + 4, $length);
     // move stream pointer to next record
     $this->_pos += 4 + $length;
     // offset: 0; size: 2; row index
     $row = $this->_GetInt2d($recordData, 0);
     // offset: 2; size: 2; col index
     $col = $this->_GetInt2d($recordData, 2);
     $columnString = PHPExcel_Cell::stringFromColumnIndex($col);
     // Read cell?
     if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
         // offset: 4; size: 2; XF index
         $xfIndex = $this->_GetInt2d($recordData, 4);
         // add style information
         if (!$this->_readDataOnly) {
             $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
         }
     }
 }
Example #4
0
 /**
  * Add external sheet
  *
  * @param PHPExcel_Worksheet $pSheet External sheet to add
  * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
  * @throws Exception
  * @return PHPExcel_Worksheet
  */
 public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null)
 {
     if (!is_null($this->getSheetByName($pSheet->getTitle()))) {
         throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
     }
     // count how many cellXfs there are in this workbook currently, we will need this below
     $countCellXfs = count($this->_cellXfCollection);
     // copy all the shared cellXfs from the external workbook and append them to the current
     foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
         $this->addCellXf(clone $cellXf);
     }
     // move sheet to this workbook
     $pSheet->rebindParent($this);
     // update the cellXfs
     foreach ($pSheet->getCellCollection(false) as $cell) {
         $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
     }
     return $this->addSheet($pSheet, $iSheetIndex);
 }
Example #5
0
 /**
  * Remove named range
  *
  * @param  string  $namedRange
  * @param  PHPExcel_Worksheet|null  $pSheet  Scope: use null for global scope.
  * @return PHPExcel
  */
 public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null)
 {
     if ($pSheet === null) {
         if (isset($this->namedRanges[$namedRange])) {
             unset($this->namedRanges[$namedRange]);
         }
     } else {
         if (isset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
             unset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
         }
     }
     return $this;
 }
Example #6
0
                $xfIndex = self::_GetInt2d($recordData, $offset);
                // offset: var; size: 4; RK value
                $numValue = self::_GetIEEE754(self::_GetInt4d($recordData, $offset + 2));
                $cell = $this->_phpSheet->getCell($columnString . ($row + 1));
                if (!$this->_readDataOnly) {
                    // add style
                    $cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
                }
                // add cell value
                $cell->setValueExplicit($numValue, PHPExcel_Cell_DataType::TYPE_NUMERIC);
            }
            $offset += 6;
        }
    }
    /**
	 * Read NUMBER record
	 * This record represents a cell that contains a
	 * floating-point value.
Example #7
0
 /**
  * Extract range values
  *
  * @param	string				&$pRange	String based range representation
  * @param	PHPExcel_Worksheet	$pSheet		Worksheet
  * @return  mixed				Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  * @param	boolean				$resetLog	Flag indicating whether calculation log should be reset or not
  * @throws	PHPExcel_Calculation_Exception
  */
 public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE)
 {
     // Return value
     $returnValue = array();
     //		echo 'extractNamedRange('.$pRange.')<br />';
     if ($pSheet !== NULL) {
         $pSheetName = $pSheet->getTitle();
         //			echo 'Current sheet name is '.$pSheetName.'<br />';
         //			echo 'Range reference is '.$pRange.'<br />';
         if (strpos($pRange, '!') !== false) {
             //				echo '$pRange reference includes sheet reference',PHP_EOL;
             list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             //				echo 'New sheet name is '.$pSheetName,PHP_EOL;
             //				echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
             $pSheet = $this->_workbook->getSheetByName($pSheetName);
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if ($namedRange !== NULL) {
             $pSheet = $namedRange->getWorksheet();
             //				echo 'Named Range '.$pRange.' (';
             $pRange = $namedRange->getRange();
             $splitRange = PHPExcel_Cell::splitRange($pRange);
             //	Convert row and column references
             if (ctype_alpha($splitRange[0][0])) {
                 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
             } elseif (ctype_digit($splitRange[0][0])) {
                 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
             }
             //				echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
             //				if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
             //					if (!$namedRange->getLocalOnly()) {
             //						$pSheet = $namedRange->getWorksheet();
             //					} else {
             //						return $returnValue;
             //					}
             //				}
         } else {
             return PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         //			var_dump($aReferences);
         if (!isset($aReferences[1])) {
             //	Single cell (or single column or row) in range
             list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
             $cellValue = NULL;
             if ($pSheet->cellExists($aReferences[0])) {
                 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
             } else {
                 $returnValue[$currentRow][$currentCol] = NULL;
             }
         } else {
             // Extract cell data for all cells in the range
             foreach ($aReferences as $reference) {
                 // Extract range
                 list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
                 //					echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
                 $cellValue = NULL;
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = NULL;
                 }
             }
         }
         //				print_r($returnValue);
         //			echo '<br />';
     }
     // Return
     return $returnValue;
 }
Example #8
0
 /**
  * Write SheetPr
  *
  * @param	PHPExcel_Shared_XMLWriter		$objWriter		XML Writer
  * @param	PHPExcel_Worksheet				$pSheet			Worksheet
  * @throws	PHPExcel_Writer_Exception
  */
 private function _writeSheetPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // sheetPr
     $objWriter->startElement('sheetPr');
     //$objWriter->writeAttribute('codeName',		$pSheet->getTitle());
     if ($pSheet->getParent()->hasMacros()) {
         //if the workbook have macros, we need to have codeName for the sheet
         if ($pSheet->hasCodeName() == false) {
             $pSheet->setCodeName($pSheet->getTitle());
         }
         $objWriter->writeAttribute('codeName', $pSheet->getCodeName());
     }
     $autoFilterRange = $pSheet->getAutoFilter()->getRange();
     if (!empty($autoFilterRange)) {
         $objWriter->writeAttribute('filterMode', 1);
         $pSheet->getAutoFilter()->showHideRows();
     }
     // tabColor
     if ($pSheet->isTabColorSet()) {
         $objWriter->startElement('tabColor');
         $objWriter->writeAttribute('rgb', $pSheet->getTabColor()->getARGB());
         $objWriter->endElement();
     }
     // outlinePr
     $objWriter->startElement('outlinePr');
     $objWriter->writeAttribute('summaryBelow', $pSheet->getShowSummaryBelow() ? '1' : '0');
     $objWriter->writeAttribute('summaryRight', $pSheet->getShowSummaryRight() ? '1' : '0');
     $objWriter->endElement();
     // pageSetUpPr
     if ($pSheet->getPageSetup()->getFitToPage()) {
         $objWriter->startElement('pageSetUpPr');
         $objWriter->writeAttribute('fitToPage', '1');
         $objWriter->endElement();
     }
     $objWriter->endElement();
 }
 /**
  * Writes a row of values
  *
  * @param \PHPExcel_Worksheet $worksheet
  * @param array               $data      An array of values with column indexes as keys
  */
 protected function writeValues(\PHPExcel_Worksheet $worksheet, array $data)
 {
     $worksheetName = $worksheet->getTitle();
     $row = $this->rowIndexes[$worksheetName];
     foreach ($this->labels[$worksheet->getTitle()] as $column => $label) {
         if (isset($data[$label])) {
             $worksheet->setCellValueByColumnAndRow($column, $row, $data[$label]);
         }
     }
     $this->rowIndexes[$worksheetName]++;
 }
Example #10
0
 /**
  * Add a new worksheet to the Excel workbook.
  * If no name is given the name of the worksheet will be Sheeti$i, with
  * $i in [1..].
  *
  * @access public
  * @param PHPExcel_Worksheet $phpSheet
  * @param array $xfIndexes
  * @return mixed reference to a worksheet object on success
  */
 function &addWorksheet($phpSheet = null, &$xfIndexes)
 {
     $name = $phpSheet->getTitle();
     $index = count($this->_worksheets);
     // Check that sheetname is <= 31 chars (Excel limit before BIFF8).
     if ($this->_BIFF_version != 0x600) {
         if (strlen($name) > 31) {
             throw new Exception("Sheetname {$name} must be <= 31 chars");
         }
     }
     $total_worksheets = count($this->_worksheets);
     $worksheet = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_parser, $this->_tmp_dir, $phpSheet, $xfIndexes);
     $this->_worksheets[$index] =& $worksheet;
     // Store ref for iterator
     $this->_parser->setExtSheet($name, $index);
     // Register worksheet name with parser
     // for BIFF8
     if ($this->_BIFF_version == 0x600) {
         $supbook_index = 0x0;
         $ref = pack('vvv', $supbook_index, $total_worksheets, $total_worksheets);
         $this->_parser->_references[] = $ref;
         // Register reference with parser
     }
     return $worksheet;
 }
Example #11
0
 /**
  * 
  * @param PHPExcel_Worksheet $worksheet
  * @param InterfaceObject $ifc
  * @return void
  */
 private function ParseWorksheetWithIfc($worksheet, $ifc)
 {
     /* Use interface name as worksheet name. Format for content is as follows:
        #1 <srcConcept> | <ifc label x> | <ifc label y> | <etc>
        #2 <srcAtomA>   | <tgtAtom1>    | <tgtAtom2>    | <etc>
        #3 <srcAtomB>   | <tgtAtom3>    | <tgtAtom4>    | <etc>
        */
     $highestrow = $worksheet->getHighestRow();
     $highestcolumn = $worksheet->getHighestColumn();
     $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
     $leftConcept = Concept::getConceptByLabel((string) $worksheet->getCell('A1'));
     if ($leftConcept != $ifc->tgtConcept) {
         throw new Exception("Target concept of interface '{$ifc->path}' does not match concept specified in cell {$worksheet->getTitle()}:A1", 500);
     }
     // Parse other columns of first row
     $header = array();
     for ($columnnr = 1; $columnnr < $highestcolumnnr; $columnnr++) {
         $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
         $cell = $worksheet->getCell($columnletter . '1');
         $cellvalue = (string) $cell->getCalculatedValue();
         if ($cellvalue == '') {
             $header[$columnletter] = null;
         } else {
             $subIfc = $ifc->getSubinterfaceByLabel($cellvalue);
             if (!$subIfc->crudU || !$subIfc->relation) {
                 throw new Exception("Update not allowed/possible for {$subIfc->label} as specified in cell {$columnletter}1", 403);
             }
             $header[$columnletter] = $subIfc;
         }
     }
     for ($row = 2; $row <= $highestrow; $row++) {
         $firstCol = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
         if ($firstCol == '') {
             continue;
         } elseif ($firstCol == '_NEW') {
             if (!$ifc->crudC) {
                 throw new Exception("Trying to create new atom in cell A{$row}. This is not allowed.", 403);
             }
             $leftAtom = $leftConcept->createNewAtom()->addAtom();
         } else {
             $leftAtom = new Atom($firstCol, $leftConcept);
             if (!$leftAtom->atomExists() && !$ifc->crudC) {
                 throw new Exception("Trying to create new {$leftConcept} in cell A{$row}. This is not allowed.", 403);
             }
             $leftAtom->addAtom();
         }
         for ($columnnr = 1; $columnnr < $highestcolumnnr; $columnnr++) {
             $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
             if (is_null($header[$columnletter])) {
                 continue;
             }
             // skip this column.
             $cell = $worksheet->getCell($columnletter . $row);
             $cellvalue = (string) $cell->getCalculatedValue();
             if ($cellvalue == '') {
                 continue;
             }
             // skip if not value provided
             // 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);
             }
             $rightAtom = new Atom($cellvalue, $header[$columnletter]->tgtConcept);
             if (!$rightAtom->atomExists() && !$header[$columnletter]->crudC) {
                 throw new Exception("Trying to create new {$header[$columnletter]->tgtConcept} in cell {$columnletter}{$row}. This is not allowed.", 403);
             }
             $header[$columnletter]->relation()->addLink($leftAtom, $rightAtom, $header[$columnletter]->relationIsFlipped, 'ExcelImport');
         }
     }
 }
Example #12
0
 private function addChart2(\PHPExcel $ea, \PHPExcel_Worksheet $ews)
 {
     $title = new \PHPExcel_Chart_Title($ews->getTitle());
     $dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$D$1', NULL, 1), new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$E$1', NULL, 1));
     $xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$F$2:$F$91', NULL, 90));
     $dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$D$2:$D$91', NULL, 90), new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$E$2:$E$91', NULL, 90));
     $ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_LINECHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv);
     $pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds));
     // Set legend
     $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
     $chart = new \PHPExcel_Chart('Chart1', $title, $legend, $pa, true, 0, NULL, NULL);
     $chart->setTopLeftPosition('I1');
     $chart->setBottomRightPosition('AA21');
     $ews->addChart($chart);
     return $chart;
 }
 /**
  * Extract range values
  *
  * @param	string				&$pRange	String based range representation
  * @param	PHPExcel_Worksheet	$pSheet		Worksheet
  * @return  mixed				Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  * @param	boolean				$resetLog	Flag indicating whether calculation log should be reset or not
  * @throws	PHPExcel_Calculation_Exception
  */
 public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = null, $resetLog = true)
 {
     // Return value
     $returnValue = array();
     if ($pSheet !== null) {
         $pSheetName = $pSheet->getTitle();
         if (strpos($pRange, '!') !== false) {
             list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             $pSheet = $this->_workbook->getSheetByName($pSheetName);
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if ($namedRange !== null) {
             $pSheet = $namedRange->getWorksheet();
             $pRange = $namedRange->getRange();
             $splitRange = PHPExcel_Cell::splitRange($pRange);
             //	Convert row and column references
             if (ctype_alpha($splitRange[0][0])) {
                 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
             } elseif (ctype_digit($splitRange[0][0])) {
                 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
             }
         } else {
             return PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         if (!isset($aReferences[1])) {
             //	Single cell (or single column or row) in range
             list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
             $cellValue = null;
             if ($pSheet->cellExists($aReferences[0])) {
                 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
             } else {
                 $returnValue[$currentRow][$currentCol] = null;
             }
         } else {
             // Extract cell data for all cells in the range
             foreach ($aReferences as $reference) {
                 // Extract range
                 list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
                 $cellValue = null;
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = null;
                 }
             }
         }
     }
     // Return
     return $returnValue;
 }
Example #14
0
 /**
  * Write SheetPr
  *
  * @param	PHPExcel_Shared_XMLWriter		$objWriter		XML Writer
  * @param	PHPExcel_Worksheet				$pSheet			Worksheet
  * @throws	Exception
  */
 private function _writeSheetPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // sheetPr
     $objWriter->startElement('sheetPr');
     $objWriter->writeAttribute('codeName', $pSheet->getTitle());
     // outlinePr
     $objWriter->startElement('outlinePr');
     $objWriter->writeAttribute('summaryBelow', $pSheet->getShowSummaryBelow() ? '1' : '0');
     $objWriter->writeAttribute('summaryRight', $pSheet->getShowSummaryRight() ? '1' : '0');
     $objWriter->endElement();
     // pageSetUpPr
     if (!is_null($pSheet->getPageSetup()->getFitToHeight()) || !is_null($pSheet->getPageSetup()->getFitToWidth())) {
         $objWriter->startElement('pageSetUpPr');
         $objWriter->writeAttribute('fitToPage', '1');
         $objWriter->endElement();
     }
     $objWriter->endElement();
 }
Example #15
0
	/**
	 * Add external sheet
	 *
	 * @param PHPExcel_Worksheet $pSheet External sheet to add
	 * @throws Exception
	 */
	public function addExternalSheet(PHPExcel_Worksheet $pSheet) {
		if (!is_null($this->getSheetByName($pSheet->getTitle()))) {
			throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
		}

		$pSheet->rebindParent($this);
		$this->addSheet($pSheet);
	}
Example #16
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)
 {
     $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 ($cellID = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         // 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->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->getCell($cell->getCoordinate())->setValue('');
         } 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 && 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: comments
     $aComments = $pSheet->getComments();
     $aNewComments = array();
     // the new array of all comments
     foreach ($aComments as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewComments[$newReference] = $value;
     }
     $pSheet->setComments($aNewComments);
     // replace the comments array
     // 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 #17
0
 /**
  * Worksheet title getter/setter
  */
 public function title($title = NULL)
 {
     if ($title === NULL) {
         return $this->_worksheet->getTitle();
     } else {
         $this->_worksheet->setTitle($title);
         return $this;
     }
 }
Example #18
0
 /**
  * Writes Excel BIFF BOUNDSHEET record.
  *
  * @param PHPExcel_Worksheet  $sheet Worksheet name
  * @param integer $offset    Location of worksheet BOF
  */
 private function writeBoundSheet($sheet, $offset)
 {
     $sheetname = $sheet->getTitle();
     $record = 0x85;
     // Record identifier
     // sheet state
     switch ($sheet->getSheetState()) {
         case PHPExcel_Worksheet::SHEETSTATE_VISIBLE:
             $ss = 0x0;
             break;
         case PHPExcel_Worksheet::SHEETSTATE_HIDDEN:
             $ss = 0x1;
             break;
         case PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN:
             $ss = 0x2;
             break;
         default:
             $ss = 0x0;
             break;
     }
     // sheet type
     $st = 0x0;
     $grbit = 0x0;
     // Visibility and sheet type
     $data = pack("VCC", $offset, $ss, $st);
     $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheetname);
     $length = strlen($data);
     $header = pack("vv", $record, $length);
     $this->append($header . $data);
 }
Example #19
0
 /**
  * Write Defined Name for PrintTitles
  *
  * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
  * @param 	PHPExcel_Worksheet			$pSheet
  * @param 	int							$pSheetId
  * @throws 	Exception
  */
 private function _writeDefinedNameForPrintTitles(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
 {
     // definedName for PrintTitles
     if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
         $objWriter->startElement('definedName');
         $objWriter->writeAttribute('name', '_xlnm.Print_Titles');
         $objWriter->writeAttribute('localSheetId', $pSheetId);
         // Setting string
         $settingString = '';
         // Columns to repeat
         if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
             $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
             $settingString = $pSheet->getTitle() . '!$' . $repeat[0] . ':$' . $repeat[1];
         }
         // Rows to repeat
         if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
             if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                 $settingString .= ',';
             }
             $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
             $settingString = $pSheet->getTitle() . '!$' . $repeat[0] . ':$' . $repeat[1];
         }
         $objWriter->writeRaw($settingString);
         $objWriter->endElement();
     }
 }
Example #20
0
 /**
  * Insert a new column or row, updating all possible related data
  *
  * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
  * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
  * @param   PHPExcel_Worksheet  $pSheet     The worksheet that we're editing
  * @throws  PHPExcel_Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = NULL)
 {
     $remove = $pNumCols < 0 || $pNumRows < 0;
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     while ($cellID = $remove ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
         if ($cellIndex - 1 + $pNumCols < 0) {
             continue;
         }
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             } else {
                 // Formula should not be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
             }
             // Clear the original cell
             $pSheet->getCellCacheController()->deleteCacheData($cellID);
         } else {
             /*	We don't need to update styles for rows/columns before our insertion position,
             			but we do still need to adjust any formulae	in those cells					*/
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             }
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: row dimensions
     $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: page breaks
     $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: comments
     $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: hyperlinks
     $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: data validations
     $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: merge cells
     $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: protected cells
     $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: autofilter
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());
             if (count($autoFilterColumns) > 0) {
                 sscanf($pBefore, '%[A-Z]%d', $column, $row);
                 $columnIndex = PHPExcel_Cell::columnIndexFromString($column);
                 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
                 if ($columnIndex <= $rangeEnd[0]) {
                     if ($pNumCols < 0) {
                         //	If we're actually deleting any columns that fall within the autofilter range,
                         //		then we delete any rules for those columns
                         $deleteColumn = $columnIndex + $pNumCols - 1;
                         $deleteCount = abs($pNumCols);
                         for ($i = 1; $i <= $deleteCount; ++$i) {
                             if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) {
                                 $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
                             }
                             ++$deleteColumn;
                         }
                     }
                     $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0];
                     //	Shuffle columns in autofilter range
                     if ($pNumCols > 0) {
                         //	For insert, we shuffle from end to beginning to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         $startColRef = $startCol;
                         $endColRef = $rangeEnd[0];
                         $toColRef = $rangeEnd[0] + $pNumCols;
                         do {
                             $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef - 1), PHPExcel_Cell::stringFromColumnIndex($toColRef - 1));
                             --$endColRef;
                             --$toColRef;
                         } while ($startColRef <= $endColRef);
                     } else {
                         //	For delete, we shuffle from beginning to end to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         do {
                             $autoFilter->shiftColumn($startColID, $toColID);
                             ++$startColID;
                             ++$toColID;
                         } while ($startColID != $endColID);
                     }
                 }
             }
         }
         $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
Example #21
0
 /**
  * @param PHPExcel $objPHPExcel
  * @param PHPExcel_Worksheet $sheet
  * @param string $col
  * @param int|string $row
  * @param string $day
  * @return bool
  * @throws PHPExcel_Exception
  */
 function getDay(PHPExcel $objPHPExcel, PHPExcel_Worksheet $sheet, PHPExcel_Cell $cell, $arr_filter)
 {
     $g = true;
     $b = false;
     $objPHPExcel->setActiveSheetIndexByName($sheet->getTitle());
     $act_sheet = $objPHPExcel->getActiveSheet();
     $index_sheet = $objPHPExcel->getActiveSheetIndex();
     $row = $cell->getRow();
     $column = $cell->getColumn();
     // $cell_v=$cell->getFormattedValue();
     $columnHiestIndex = PHPExcel_Cell::columnIndexFromString($act_sheet->getHighestColumn($row));
     $rowHiestIndex = $act_sheet->getHighestRow();
     $column = PHPExcel_Cell::columnIndexFromString($column);
     //$this->testEcho($rowHiestIndex);
     $cell_value = '';
     for ($i = 0; $i < $column; $i++) {
         $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row);
         //  $this->testEcho($cell_value);
         if (in_array($cell_value, $arr_filter)) {
             //
             //  $this->testEcho($cell_value);
             $b = true;
         }
         if ($b) {
             $g = false;
             break;
         }
     }
     if ($g) {
         for ($i = $column + 1; $i <= $columnHiestIndex; $i++) {
             $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row);
             //  $this->testEcho($cell_value);
             if (in_array($cell_value, $arr_filter)) {
                 //      $this->testEcho($cell_value);
                 $b = true;
             }
             if ($b) {
                 $g = false;
                 break;
             }
         }
     }
     if ($g) {
         for ($i = 0; $i < $row; $i++) {
             $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $column, $i);
             //  $this->testEcho($cell_value);
             if (in_array($cell_value, $arr_filter)) {
                 //     $this->testEcho($cell_value);
                 $b = true;
             }
             if ($b) {
                 $g = false;
                 break;
             }
         }
     }
     if ($g) {
         for ($i = $row + 1; $i <= $rowHiestIndex; $i++) {
             $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $column, $i);
             //  $this->testEcho($cell_value);
             if (in_array($cell_value, $arr_filter)) {
                 //         $this->testEcho($cell_value);
                 $b = true;
             }
             if ($b) {
                 break;
             }
         }
     }
     /*
             for ($i = 0; $i <= $rowHiestIndex; $i++) {
                 $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row);
               //  $this->testEcho($cell_value);
                 if ($cell_value == $day) {
                   $this->testEcho($cell_value);
                     return $b = true;
                 }
             }
             $this->testEcho('Тестим B');
             $this->testEcho($b);
             if (!$b) {
                 $colHiestIndex = $sheet->getHighestRow($col);
                 $this->testEcho($colHiestIndex);
                 for ($i = 0; $i <= $colHiestIndex; $i++) {
                     $cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $col, $i);
                     if ($cell_value == $day) {
                         $this->testEcho($cell_value);
                   }
                 }
           }
     */
     return $cell_value;
 }
Example #22
0
 /**
  * Write Defined Name for PrintTitles
  *
  * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
  * @param 	PHPExcel_Worksheet			$pSheet
  * @param 	int							$pSheetId
  * @throws 	PHPExcel_Writer_Exception
  */
 private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
 {
     // definedName for PrintArea
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $objWriter->startElement('definedName');
         $objWriter->writeAttribute('name', '_xlnm.Print_Area');
         $objWriter->writeAttribute('localSheetId', $pSheetId);
         // Setting string
         $settingString = '';
         // Print area
         $printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
         $chunks = array();
         foreach ($printArea as $printAreaRect) {
             $printAreaRect[0] = PHPExcel_Cell::absoluteReference($printAreaRect[0]);
             $printAreaRect[1] = PHPExcel_Cell::absoluteReference($printAreaRect[1]);
             $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
         }
         $objWriter->writeRawData(implode(',', $chunks));
         $objWriter->endElement();
     }
 }
Example #23
0
 /**
  * Write Defined Name for PrintTitles
  *
  * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
  * @param 	PHPExcel_Worksheet			$pSheet
  * @param 	int							$pSheetId
  * @throws 	Exception
  */
 private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
 {
     // definedName for PrintArea
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $objWriter->startElement('definedName');
         $objWriter->writeAttribute('name', '_xlnm.Print_Area');
         $objWriter->writeAttribute('localSheetId', $pSheetId);
         // Setting string
         $settingString = '';
         // Print area
         $printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
         $printArea[0] = PHPExcel_Cell::absoluteCoordinate($printArea[0]);
         $printArea[1] = PHPExcel_Cell::absoluteCoordinate($printArea[1]);
         $objWriter->writeRaw('\'' . $pSheet->getTitle() . '\'!' . implode(':', $printArea));
         $objWriter->endElement();
     }
 }
Example #24
0
 /**
  * Writes the Excel BIFF EXTERNSHEET record. These references are used by
  * formulas. A formula references a sheet name via an index. Since we store a
  * reference to all of the external worksheets the EXTERNSHEET index is the same
  * as the worksheet index.
  *
  * @param string $sheetname The name of a external worksheet
  */
 private function _writeExternsheet($sheetname)
 {
     $record = 0x17;
     // Record identifier
     // References to the current sheet are encoded differently to references to
     // external sheets.
     //
     if ($this->_phpSheet->getTitle() == $sheetname) {
         $sheetname = '';
         $length = 0x2;
         // The following 2 bytes
         $cch = 1;
         // The following byte
         $rgch = 0x2;
         // Self reference
     } else {
         $length = 0x2 + strlen($sheetname);
         $cch = strlen($sheetname);
         $rgch = 0x3;
         // Reference to a sheet in the current workbook
     }
     $header = pack("vv", $record, $length);
     $data = pack("CC", $cch, $rgch);
     $this->_append($header . $data . $sheetname);
 }
Example #25
0
 /**
  * Write SheetPr
  *
  * @param 	PHPExcel_Shared_XMLWriter		$objWriter 		XML Writer
  * @param 	PHPExcel_Worksheet				$pSheet			Worksheet
  * @throws 	Exception
  */
 private function _writeSheetPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // sheetPr
     $objWriter->startElement('sheetPr');
     $objWriter->writeAttribute('codeName', $pSheet->getTitle());
     // outlinePr
     $objWriter->startElement('outlinePr');
     $objWriter->writeAttribute('summaryBelow', $pSheet->getShowSummaryBelow() ? '1' : '0');
     $objWriter->writeAttribute('summaryRight', $pSheet->getShowSummaryRight() ? '1' : '0');
     $objWriter->endElement();
     $objWriter->endElement();
 }