/** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) { // Get a copy of the cell collection /*$aTemp = $pSheet->getCellCollection(); $aCellCollection = array(); foreach ($aTemp as $key => $value) { $aCellCollection[$key] = clone $value; }*/ $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore); // Clear cells if we are removing columns or rows $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); // 1. Clear column strips if we are removing columns if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2; ++$j) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // 2. Clear row strips if we are removing rows if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) { for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // Loop through cells, bottom-up, and change cell coordinates while ($cell = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) { // New coordinates $newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows); // Should the cell be updated? Move value and cellXf index from one cell to another. if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) { // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); $cell->setXfIndex(0); // Insert this cell at its new location if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->setCellValue($newCoordinates, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows)); } else { // Formula should not be adjusted $pSheet->setCellValue($newCoordinates, $cell->getValue()); } // Clear the original cell $pSheet->setCellValue($cell->getCoordinate(), ''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) { $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) { // Style $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1); if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned); } } } } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); if (count($aColumnDimensions) > 0) { foreach ($aColumnDimensions as $objColumnDimension) { $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows); list($newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objColumnDimension->getColumnIndex() != $newReference) { $objColumnDimension->setColumnIndex($newReference); } } $pSheet->refreshColumnDimensions(); } // Update worksheet: row dimensions $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); if (count($aRowDimensions) > 0) { foreach ($aRowDimensions as $objRowDimension) { $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objRowDimension->getRowIndex() != $newReference) { $objRowDimension->setRowIndex($newReference); } } $pSheet->refreshRowDimensions(); $copyDimension = $pSheet->getRowDimension($beforeRow - 1); for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) { $newDimension = $pSheet->getRowDimension($i); $newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } } // Update worksheet: breaks $aBreaks = array_reverse($pSheet->getBreaks(), true); foreach ($aBreaks as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setBreak($newReference, $value); $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE); } } // Update worksheet: hyperlinks $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true); foreach ($aHyperlinkCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setHyperlink($newReference, $value); $pSheet->setHyperlink($key, null); } } // Update worksheet: data validations $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true); foreach ($aDataValidationCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setDataValidation($newReference, $value); $pSheet->setDataValidation($key, null); } } // Update worksheet: merge cells $aMergeCells = $pSheet->getMergeCells(); $aNewMergeCells = array(); // the new array of all merge cells foreach ($aMergeCells as $key => &$value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); $aNewMergeCells[$newReference] = $newReference; } $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array // Update worksheet: protected cells $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true); foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->protectCells($newReference, $value, true); $pSheet->unprotectCells($key); } } // Update worksheet: autofilter if ($pSheet->getAutoFilter() != '') { $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows)); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); } } } // Garbage collect $pSheet->garbageCollect(); }
/** * @param Worksheet $worksheet * @param array $style_h2 * @return Worksheet * @throws \PHPExcel_Exception */ public function exportExcel(Worksheet $worksheet, array $style_h2) { $last_row = $worksheet->getHighestDataRow(); $last_row += 2; $max_col = $worksheet->getHighestDataColumn(); $worksheet->mergeCells("A{$last_row}:{$max_col}{$last_row}"); $worksheet->setCellValue("A{$last_row}", utf8_encode($this->getTitulo())); $worksheet->getStyle("A{$last_row}:{$max_col}{$last_row}")->applyFromArray($style_h2); $worksheet->getRowDimension($last_row)->setRowHeight(20); $last_row += 2; $worksheet->setCellValue("C{$last_row}", utf8_encode('Opción')); $worksheet->setCellValue("D{$last_row}", 'Votos'); $first_row = $last_row; $last_row += 1; foreach ($this->getDatos() as $key => $dato) { $worksheet->setCellValue("B{$last_row}", $key + 1); $worksheet->setCellValue("C{$last_row}", utf8_encode($dato[0])); if (mb_strlen($dato[0]) > 45) { $worksheet->getRowDimension($last_row)->setRowHeight(27); } $worksheet->setCellValue("D{$last_row}", $dato[1]); $last_row++; } $last_row -= 1; $worksheet->getStyle("C{$first_row}:D{$last_row}")->applyFromArray($this->getEstiloTabla('center', true)); $first_row++; $worksheet->getStyle("B{$first_row}:D{$last_row}")->applyFromArray($this->getEstiloTabla()); $first_row -= 1; $top_chart = $first_row - 1; $bottom_chart = $first_row + 12; $chart1 = $this->getChart($first_row, $last_row, $top_chart, $bottom_chart); $worksheet->addChart($chart1); $worksheet->setCellValue("A{$bottom_chart}", ""); return $worksheet; }
/** * 行を完全コピーする * * http://blog.kotemaru.org/old/2012/04/06.html より * @param PHPExcel_Worksheet $sheet * @param int $srcRow * @param int $dstRow * @param int $height * @param int $width * @throws PHPExcel_Exception */ function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width) { for ($row = 0; $row < $height; $row++) { // セルの書式と値の複製 for ($col = 0; $col < $width; $col++) { $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row); $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row); $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row); $sheet->setCellValue($dstCell, $cell->getValue()); $sheet->duplicateStyle($style, $dstCell); } // 行の高さ複製。 $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight(); $sheet->getRowDimension($dstRow + $row)->setRowHeight($h); } // セル結合の複製 // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。 // - $merge="AB16:AC19" foreach ($sheet->getMergeCells() as $mergeCell) { $mc = explode(":", $mergeCell); $col_s = preg_replace("/[0-9]*/", "", $mc[0]); $col_e = preg_replace("/[0-9]*/", "", $mc[1]); $row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow; $row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow; // 複製先の行範囲なら。 if (0 <= $row_s && $row_s < $height) { $merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e); $sheet->mergeCells($merge); } } }
/** * */ protected function _addHeaderReport() { $titles = $this->_content->getElementsByTagName('h4'); // Add the titles $titlesReport = array(); foreach ($titles as $title) { $titlesReport[] = $title->nodeValue; } $cellHeader = $this->_mainSheet->getCell('C2'); $cellHeader->setValue(implode("\r\n", $titlesReport)); $styleHeader = $cellHeader->getStyle(); $styleHeader->getAlignment()->setWrapText(true); $styleHeader->getFont()->setBold(true); $this->_mainSheet->mergeCells('C2:T2'); // Add the logo $logoPath = APPLICATION_PATH . '/../public/images/logo_report.png'; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($logoPath)->setResizeProportional(true)->setCoordinates('I2')->setWorksheet($this->_mainSheet)->setWidthAndHeight(60, 60); $this->_mainSheet->getColumnDimension('J')->setWidth(50); $this->_mainSheet->getRowDimension('2')->setRowHeight(50); // Create the report title $reportTitle = $this->_content->getElementsByTagName('h5'); $titlesValues = array(); foreach ($reportTitle as $title) { $titlesValues[] = trim($title->nodeValue); } $cellTitle = $this->_mainSheet->getCell('C4'); $cellTitle->setValue(implode(' - ', $titlesValues)); $this->_mainSheet->mergeCells('C4:T4'); }
/** * Set dimensions of row or column * * @param \PHPExcel_Worksheet $excelWorksheet * @param $excelElement * @param $documentElement */ protected function setDimensions(\PHPExcel_Worksheet $excelWorksheet, $excelElement, $documentElement) { $dimensions = $this->getDimensionsArray($documentElement); if (isset($dimensions['column']) && $excelElement instanceof \PHPExcel_Cell) { foreach ($dimensions['column'] as $columnKey => $columnValue) { $method = 'set' . ucfirst($columnKey); $excelWorksheet->getColumnDimension($excelElement->getColumn())->{$method}($columnValue); } } if (isset($dimensions['row'])) { foreach ($dimensions['row'] as $rowKey => $rowValue) { $method = 'set' . ucfirst($rowKey); if ($excelElement instanceof \PHPExcel_Cell) { $excelWorksheet->getRowDimension($excelElement->getRow())->{$method}($rowValue); } elseif ($excelElement instanceof \PHPExcel_Worksheet_Row) { $excelWorksheet->getRowDimension($excelElement->getRowIndex())->{$method}($rowValue); } } } }
* Read PRINTGRIDLINES record */ private function _readPrintGridlines() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) { // offset: 0; size: 2; 0 = do not print sheet grid lines; 1 = print sheet gridlines $printGridlines = (bool) self::_GetInt2d($recordData, 0); $this->_phpSheet->setPrintGridlines($printGridlines); } } /** * Read DEFAULTROWHEIGHT record */ private function _readDefaultRowHeight() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2; option flags // offset: 2; size: 2; default height for unused rows, (twips 1/20 point) $height = self::_GetInt2d($recordData, 2); $this->_phpSheet->getDefaultRowDimension()->setRowHeight($height / 20); } /** * Read SHEETPR record */ private function _readSheetPr() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2 // bit: 6; mask: 0x0040; 0 = outline buttons above outline group $isSummaryBelow = (0x40 & self::_GetInt2d($recordData, 0)) >> 6; $this->_phpSheet->setShowSummaryBelow($isSummaryBelow);
/** * 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."); } }
/** * Update row dimensions when inserting/deleting rows/columns * * @param PHPExcel_Worksheet $pSheet The worksheet that we're editing * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') * @param integer $beforeColumnIndex Index number of the column we're inserting/deleting before * @param integer $pNumCols Number of columns to insert/delete (negative values indicate deletion) * @param integer $beforeRow Number of the row we're inserting/deleting before * @param integer $pNumRows Number of rows to insert/delete (negative values indicate deletion) */ protected function _adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) { $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); if (!empty($aRowDimensions)) { 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()); } } }
/** * Apply the AutoFilter rules to the AutoFilter Range * * @throws PHPExcel_Exception * @return PHPExcel_Worksheet_AutoFilter */ public function showHideRows() { list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range); // The heading row should always be visible // echo 'AutoFilter Heading Row ',$rangeStart[1],' is always SHOWN',PHP_EOL; $this->_workSheet->getRowDimension($rangeStart[1])->setVisible(TRUE); $columnFilterTests = array(); foreach ($this->_columns as $columnID => $filterColumn) { $rules = $filterColumn->getRules(); switch ($filterColumn->getFilterType()) { case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER: $ruleValues = array(); // Build a list of the filter value selections foreach ($rules as $rule) { $ruleType = $rule->getRuleType(); $ruleValues[] = $rule->getValue(); } // Test if we want to include blanks in our filter criteria $blanks = FALSE; $ruleDataSet = array_filter($ruleValues); if (count($ruleValues) != count($ruleDataSet)) { $blanks = TRUE; } if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER) { // Filter on absolute values $columnFilterTests[$columnID] = array('method' => '_filterTestInSimpleDataSet', 'arguments' => array('filterValues' => $ruleDataSet, 'blanks' => $blanks)); } else { // Filter on date group values $arguments = array('date' => array(), 'time' => array(), 'dateTime' => array()); foreach ($ruleDataSet as $ruleValue) { $date = $time = ''; if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') { $date .= sprintf('%04d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]); } $dateTime = $date . $time; $arguments['date'][] = $date; $arguments['time'][] = $time; $arguments['dateTime'][] = $dateTime; } // Remove empty elements $arguments['date'] = array_filter($arguments['date']); $arguments['time'] = array_filter($arguments['time']); $arguments['dateTime'] = array_filter($arguments['dateTime']); $columnFilterTests[$columnID] = array('method' => '_filterTestInDateGroupSet', 'arguments' => array('filterValues' => $arguments, 'blanks' => $blanks)); } break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER: $customRuleForBlanks = FALSE; $ruleValues = array(); // Build a list of the filter value selections foreach ($rules as $rule) { $ruleType = $rule->getRuleType(); $ruleValue = $rule->getValue(); if (!is_numeric($ruleValue)) { // Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards $ruleValue = preg_quote($ruleValue); $ruleValue = str_replace(self::$_fromReplace, self::$_toReplace, $ruleValue); if (trim($ruleValue) == '') { $customRuleForBlanks = TRUE; $ruleValue = trim($ruleValue); } } $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue); } $join = $filterColumn->getJoin(); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks)); break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: $ruleValues = array(); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $dynamicRuleType = $rule->getGrouping(); if ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) { // Number (Average) based // Calculate the average $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')'; $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, NULL, $this->_workSheet->getCell('A1')); // Set above/below rule based on greaterThan or LessTan $operator = $dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; $ruleValues[] = array('operator' => $operator, 'value' => $average); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR)); } else { // Date based if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') { // Month or Quarter sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period); if ($periodType == 'M') { $ruleValues = array($period); } else { --$period; $periodEnd = (1 + $period) * 3; $periodStart = 1 + $period * 3; $ruleValues = range($periodStart, periodEnd); } $columnFilterTests[$columnID] = array('method' => '_filterTestInPeriodDateSet', 'arguments' => $ruleValues); $filterColumn->setAttributes(array()); } else { // Date Range $columnFilterTests[$columnID] = $this->_dynamicFilterDateRange($dynamicRuleType, $filterColumn); break; } } } break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: $ruleValues = array(); $dataRowCount = $rangeEnd[1] - $rangeStart[1]; foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $toptenRuleType = $rule->getGrouping(); $ruleValue = $rule->getValue(); $ruleOperator = $rule->getOperator(); } if ($ruleOperator === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) { $ruleValue = floor($ruleValue * ($dataRowCount / 100)); } if ($ruleValue < 1) { $ruleValue = 1; } if ($ruleValue > 500) { $ruleValue = 500; } $maxVal = $this->_calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue); $operator = $toptenRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL; $ruleValues[] = array('operator' => $operator, 'value' => $maxVal); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR)); $filterColumn->setAttributes(array('maxVal' => $maxVal)); break; } } // echo 'Column Filter Test CRITERIA',PHP_EOL; // var_dump($columnFilterTests); // // Execute the column tests for each row in the autoFilter range to determine show/hide, for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) { // echo 'Testing Row = ',$row,PHP_EOL; $result = TRUE; foreach ($columnFilterTests as $columnID => $columnFilterTest) { // echo 'Testing cell ',$columnID.$row,PHP_EOL; $cellValue = $this->_workSheet->getCell($columnID . $row)->getCalculatedValue(); // echo 'Value is ',$cellValue,PHP_EOL; // Execute the filter test $result = $result && call_user_func_array(array('PHPExcel_Worksheet_AutoFilter', $columnFilterTest['method']), array($cellValue, $columnFilterTest['arguments'])); // echo (($result) ? 'VALID' : 'INVALID'),PHP_EOL; // If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests if (!$result) { break; } } // Set show/hide for the row based on the result of the autoFilter result // echo (($result) ? 'SHOW' : 'HIDE'),PHP_EOL; $this->_workSheet->getRowDimension($row)->setVisible($result); } return $this; }
/** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) { // Get a copy of the cell collection /*$aTemp = $pSheet->getCellCollection(); $aCellCollection = array(); foreach ($aTemp as $key => $value) { $aCellCollection[$key] = clone $value; }*/ $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore ); // Remove cell styles? $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols ) . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ); } } if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow + $pNumRows)) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ); } } // Loop trough cells, bottom-up, and change cell coordinates while ( ($cell = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection)) ) { // New coordinates $newCoordinates = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols ) . ($cell->getRow() + $pNumRows); // Should the cell be updated? if ( (PHPExcel_Cell::columnIndexFromString( $cell->getColumn() ) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) && ($cell->getRow() >= $beforeRow) ) { // Update cell styles $pSheet->duplicateStyle( $pSheet->getStyle($cell->getCoordinate()), $newCoordinates . ':' . $newCoordinates ); $pSheet->duplicateStyle( $pSheet->getDefaultStyle(), $cell->getCoordinate() . ':' . $cell->getCoordinate() ); // Insert this cell at its new location if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->setCellValue( $newCoordinates , $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows) ); } else { // Formula should not be adjusted $pSheet->setCellValue($newCoordinates, $cell->getValue()); } // Clear the original cell $pSheet->setCellValue($cell->getCoordinate(), ''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ), ($beforeColumn . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols ) . $i) ); } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ), (PHPExcel_Cell::stringFromColumnIndex($i) . $beforeRow) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1 + $pNumRows)) ); } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); foreach ($aColumnDimensions as $objColumnDimension) { $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows); list($newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objColumnDimension->getColumnIndex() != $newReference) { $objColumnDimension->setColumnIndex($newReference); } } $pSheet->refreshColumnDimensions(); // Update worksheet: row dimensions $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); foreach ($aRowDimensions as $objRowDimension) { $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objRowDimension->getRowIndex() != $newReference) { $objRowDimension->setRowIndex($newReference); } } $pSheet->refreshRowDimensions(); $copyDimension = $pSheet->getRowDimension($beforeRow - 1); for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; $i++) { $newDimension = $pSheet->getRowDimension($i); $newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } // Update worksheet: breaks $aBreaks = array_reverse($pSheet->getBreaks(), true); foreach ($aBreaks as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setBreak( $newReference, $value ); $pSheet->setBreak( $key, PHPExcel_Worksheet::BREAK_NONE ); } } // Update worksheet: merge cells $aMergeCells = array_reverse($pSheet->getMergeCells(), true); foreach ($aMergeCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->mergeCells( $newReference ); $pSheet->unmergeCells( $key ); } } // Update worksheet: protected cells $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true); foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->protectCells( $newReference, $value, true ); $pSheet->unprotectCells( $key ); } } // Update worksheet: autofilter if ($pSheet->getAutoFilter() != '') { $pSheet->setAutoFilter( $this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows) ); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->setFreezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) ); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) ); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange( $this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows) ); } } } // Garbage collect $pSheet->garbageCollect(); }
/** * Sets the height (and other settings) of one row. * * @param integer $row The row to set * @param integer $height Height we are giving to the row (null to set just format without setting the height) * @param mixed $format The optional format we are giving to the row * @param bool $hidden The optional hidden attribute * @param integer $level The optional outline level (0-7) */ public function set_row($row, $height, $format = null, $hidden = false, $level = 0) { if ($level < 0) { $level = 0; } else { if ($level > 7) { $level = 7; } } if (isset($height)) { $this->worksheet->getRowDimension($row + 1)->setRowHeight($height); } $this->worksheet->getRowDimension($row + 1)->setVisible(!$hidden); $this->worksheet->getRowDimension($row + 1)->setOutlineLevel($level); $this->apply_row_format($row, $format); }
/** * Método que gera a planilha de acompnahamento do livro e disciplinas informadas * @param int $livro O código do livro * @param int $materia O código da máteria */ public function acompanhamento() { //Inicializando os filtros usados na página $this->filtro->initGets(array("livro", "disciplina")); //Inicializando os objeto básicos $sqlTopico = new SqlTopico(); //Buscando os tópicos $topicosLista = $sqlTopico->listarTodos($this->filtro); //Verificando se o livro é válido if (!$topicosLista->rowCount()) { echo Javascript::alert("Não foi encontrado conteúdo " . "associado ao livro e disciplina informados"); die(Javascript::close()); } $topicos = $topicosLista->fetchAll(); //Array com os contepudos do livro // $topicoInicia = $topicos[0]; //Primeiro item do array com os dados básicos //Variáveis iniciais básicas $xBase = 1; //Coluna inicial da impressão $yBase = 1; //Linha de início da impressão $nColunasCapitulos = 5; //Número de colunas da culuna inicial $nColunasPaginas = 1; //Número de colunas da culuna de páginas $nColunasAulas = 10; //Número de colunas referentes ao número máximo de aulas $nColunasMax = 0; //Define o número máximo de colunas da planilha $indiceUltColuna = 0; //Define o indice da ultima coluna da planilha $colecao = $topicoInicia->getCapitulo()->getLivro()->getColecao()->getNome(); //Nome da coleção a qual o livro pertence $tituloPlanilha = "PLANILHA DE ACOMPANHAMENTO PROGRAMÁTICO ({$this->data["ano_letivo"]})"; $textoAula = "Aula "; //Define o texto das celulas "aula" $textoData = "Data:"; //Define o texto das celulas "data" $alturaLinha = 40; //Define a autura minima das linhas $larguraLegenda = 5; //Define a largura de cada coluna das legendas em pixels $textoPaguinas = "PÁGINAS"; //Define o texto da coluna de páginas $textoPaguinasVertical = FALSE; //Define se o texto sobre a coluna páginas ficará na vertical ou não //Coluna da esquerda $arrayExplicativo = array("Preenchimento", "O status do andamento dos assuntos do livro da coleção {$colecao} " . "deve ser atualizado a cada aula ministrada pelo professor, " . "independente se na aula foi utilizado ou não o livro didático."); $arrayDadosProfessor = array("PROFESSOR:", "", "", "SÉRIE:", "TURMA:", "UNIDADE:"); //Coluna da direita $arrayDadosLivro = array("", "{$topicoInicia->getCapitulo()->getLivro()->getTitulo()} ({$topicoInicia->getSequencial()} º Bimestre)", $topicoInicia->getCapitulo()->getDisciplina()->getNome(), "Livro Didático: {$topicoInicia->getCapitulo()->getLivro()->getTitulo()}", ""); $arrayColunaAulaData = array($textoAula, $textoData, ""); // $arrayLegenda = array("A" => "EM ANDAMENTO", "C" => "CONCLUÍDO", "R" => "REVISADO"); //Início da execução //Verificação de erros de configuração if (count($arrayExplicativo) + count($arrayDadosProfessor) != count($arrayDadosLivro) + count($arrayColunaAulaData)) { //As somas de itens entre os pares de arrays acima devem ser iguais die("Planilha mal configurada"); } //Variaveis dinâmicas usadas na execução $x = $xBase; $y = $yBase + 1; $nColunasMax += $xBase + $nColunasCapitulos + $nColunasPaginas + $nColunasAulas * count($arrayLegenda); $indiceUltColuna += $xBase + $nColunasMax; //Inserindo os dados na planilha // $this->workSheet->setCellValueByColumnAndRow($x, $y, $tituloPlanilha); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(20); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($x + 1) . $y . ":" . ExcelAux::indiceParaColuna($indiceUltColuna + 1) . $y)->applyFromArray(array("font" => array("bold" => TRUE), "borders" => array("top" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("argb" => "000000")), "left" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("argb" => "000000")), "right" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("argb" => "000000")), "bottom" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("argb" => "000000"))))); $this->workSheet->mergeCellsByColumnAndRow($x, $y, $indiceUltColuna, $y); $this->workSheet->getRowDimension($y++)->setRowHeight($alturaLinha); //Variaveis de indices e contadores $linhaExpl = 0; $linhaProf = 0; $linhaLivro = 0; $linhaAulaData = 0; $colunaLegenda = 0; $colunaLegendaChave = array_keys($arrayLegenda); foreach (range(0, count($arrayExplicativo) + count($arrayDadosProfessor) - 1) as $value) { $x = $xBase; //Preenchendo a coluna da esquerda if (isset($arrayExplicativo[$linhaExpl])) { $this->workSheet->setCellValueByColumnAndRow($x, $y, $arrayExplicativo[$linhaExpl++]); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setWrapText(TRUE); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(12); if ($linhaExpl == count($arrayExplicativo)) { $this->workSheet->getRowDimension($y)->setRowHeight($alturaLinha + 25); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($xBase + 1) . $y . ":" . ExcelAux::indiceParaColuna($xBase + $nColunasCapitulos + 2) . $y)->applyFromArray(array("borders" => array("bottom" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))))); } } else { $this->workSheet->setCellValueByColumnAndRow($x, $y, $arrayDadosProfessor[$linhaProf++]); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(14); } $this->workSheet->getStyleByColumnAndRow($x, $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FFFFFF")))); $this->workSheet->getStyleByColumnAndRow($xBase + $nColunasCapitulos + 1, $y)->applyFromArray(array("borders" => array("right" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))))); $this->workSheet->mergeCellsByColumnAndRow($xBase, $y, $nColunasCapitulos + $xBase + 1, $y); //Preenchendo as colunas das legendas if (isset($colunaLegendaChave[$colunaLegenda])) { $x += $nColunasCapitulos + $nColunasPaginas + $colunaLegenda + $xBase; $this->workSheet->setCellValueByColumnAndRow($x, $y, $textoPaguinas); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(14); if ((bool) $textoPaguinasVertical) { $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setTextRotation(90); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } else { $this->workSheet->getColumnDimensionByColumn($x)->setWidth(13); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } $this->workSheet->getStyleByColumnAndRow($x, $y)->applyFromArray(array("font" => array("italic" => TRUE, "bold" => TRUE))); $this->workSheet->mergeCellsByColumnAndRow($x, $y, $x, $y + count($arrayExplicativo) + count($arrayDadosProfessor) - 1); foreach ($arrayLegenda as $legenda) { $x = $xBase + $nColunasCapitulos + $nColunasPaginas + $colunaLegenda + 2; $this->workSheet->setCellValueByColumnAndRow($x, $y, $arrayLegenda[$colunaLegendaChave[$colunaLegenda]]); $this->workSheet->getStyleByColumnAndRow($x, $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "EFAC86")))); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($x) . $y . ":" . ExcelAux::indiceParaColuna($x) . ($y + count($arrayDadosLivro) - 1))->applyFromArray(array("borders" => array("right" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))), "font" => array("italic" => TRUE))); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setTextRotation(90); $this->workSheet->mergeCellsByColumnAndRow($x, $y, $x, $y + count($arrayDadosLivro) - 1); $colunaLegenda++; } } //Preenchendo a coluna da direita $x = $xBase; if (isset($arrayDadosLivro[$linhaLivro])) { $x += $nColunasCapitulos + $nColunasPaginas + count($arrayLegenda) + 2; $this->workSheet->setCellValueByColumnAndRow($x, $y, $arrayDadosLivro[$linhaLivro++]); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setWrapText(TRUE); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(18); $this->workSheet->getStyleByColumnAndRow($x, $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FFFFFF")), "borders" => array("left" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))), "font" => array("bold" => TRUE))); $this->workSheet->mergeCellsByColumnAndRow($x, $y, $indiceUltColuna, $y); } else { $x = $xBase + $nColunasCapitulos + $nColunasPaginas - (count($arrayLegenda) - 2); $colorir = FALSE; foreach (range(1, $nColunasAulas) as $value) { $this->workSheet->setCellValueByColumnAndRow($x += count($arrayLegenda), $y, $arrayColunaAulaData[$linhaAulaData] . (!$linhaAulaData ? str_pad($value, 2, 0, STR_PAD_LEFT) : "")); $this->workSheet->getStyleByColumnAndRow($x, $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => ($colorir = !$colorir) ? "EFAC86" : "FFFFFF")), "borders" => array("left" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))), "font" => array("underline" => TRUE))); if (!$linhaAulaData) { //Centralizando a primeira palavra do bloco $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(16); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($x) . $y . ":" . ExcelAux::indiceParaColuna($x + count($arrayLegenda)) . $y)->applyFromArray(array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000'))), "font" => array("bold" => TRUE, "underline" => FALSE))); } $this->workSheet->mergeCellsByColumnAndRow($x, $y, $x + count($arrayLegenda) - 1, $y); } $linhaAulaData++; } $y++; } //Preenchendo o resto da planilha $capituloTitulo = ""; foreach ($topicos as $topico) { $x = $xBase; if ($capituloTitulo != $topico->getCapitulo()->getSequencial()) { $capituloTitulo = $topico->getCapitulo()->getSequencial(); $tituloCapitulo = "{$topico->getCapitulo()->getSequencial()}: {$topico->getCapitulo()->getNome()}"; $this->workSheet->setCellValueByColumnAndRow($x, $y, $tituloCapitulo); $this->workSheet->getRowDimension($y)->setRowHeight($alturaLinha); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(16); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setWrapText(TRUE); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($xBase + 1) . $y . ":" . ExcelAux::indiceParaColuna($xBase + $nColunasCapitulos + $nColunasPaginas + 2) . $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "9BC2E6")), 'font' => array('bold' => true), "borders" => array("top" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))))); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($xBase + 1) . $y . ":" . ExcelAux::indiceParaColuna($indiceUltColuna + 1) . $y)->applyFromArray(array("borders" => array("top" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))))); $this->workSheet->mergeCellsByColumnAndRow($xBase, $y, $nColunasCapitulos + $xBase + 1, $y); $this->workSheet->mergeCellsByColumnAndRow($xBase + $nColunasCapitulos + 2, $y, $indiceUltColuna, $y++); } $topicoDesc = "Tópico {$topico->getSequencial()} : {$topico->getNome()}"; $pagina = "{$topico->getPaginaInicial()} a {$topico->getPaginaFinal()}"; $this->workSheet->setCellValueByColumnAndRow($x, $y, $topicoDesc); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(14); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($xBase + 1) . $y . ":" . ExcelAux::indiceParaColuna($xBase + $nColunasCapitulos + $nColunasPaginas + 2) . $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "F2F2F2")), 'font' => array("italic" => TRUE), "borders" => array("top" => array("style" => PHPExcel_Style_Border::BORDER_THIN, "color" => array("rgb" => "000000"))))); $this->workSheet->getRowDimension($y)->setRowHeight($alturaLinha); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setWrapText(TRUE); $this->workSheet->setCellValueByColumnAndRow($x += $nColunasCapitulos + $xBase + 1, $y, $pagina); $this->workSheet->getStyleByColumnAndRow($x, $y)->getFont()->setSize(14); $this->workSheet->getStyleByColumnAndRow($x, $y)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FFFFFF")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000'))))); $this->workSheet->getStyleByColumnAndRow($x, $y)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->workSheet->mergeCellsByColumnAndRow($xBase, $y, $nColunasCapitulos + $xBase + 1, $y); // $x++; $colorir = TRUE; foreach (range(1, $nColunasAulas) as $colAula) { foreach ($colunaLegendaChave as $value) { $this->workSheet->setCellValueByColumnAndRow($x + 1, $y, $value); $this->workSheet->getStyleByColumnAndRow($x + 1, $y)->getFont()->setSize(14); $this->workSheet->getStyleByColumnAndRow($x + 1, $y)->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000'))), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $colorir ? "EFAC86" : "FFFFFF")))); $this->workSheet->getStyleByColumnAndRow(++$x, $y)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); } $colorir = !$colorir; } $y++; } //Definindo a largura das páginas como autosize // $this->workSheet // ->getColumnDimensionByColumn($xBase + $nColunasCapitulos + $nColunasPaginas) // ->setAutoSize(TRUE); //Definindo a largura das colunas das legendas foreach (range($xBase + $nColunasCapitulos + $nColunasPaginas + 2, $indiceUltColuna) as $value) { $this->workSheet->getColumnDimensionByColumn($value)->setWidth($larguraLegenda); } //Definindo as bordas em volta da planilha $this->workSheet->getStyle(ExcelAux::indiceParaColuna($xBase + 1) . --$y . ":" . ExcelAux::indiceParaColuna($indiceUltColuna + 1) . $y)->applyFromArray(array("borders" => array("bottom" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("argb" => "000000"))))); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($xBase + 1) . ($yBase + 1) . ":" . ExcelAux::indiceParaColuna($xBase + 1) . $y)->applyFromArray(array("borders" => array("left" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("argb" => "000000"))))); $this->workSheet->getStyle(ExcelAux::indiceParaColuna($indiceUltColuna + 1) . ($yBase + 1) . ":" . ExcelAux::indiceParaColuna($indiceUltColuna + 1) . $y)->applyFromArray(array("borders" => array("right" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("argb" => "000000"))))); //Imprimindo a planilha $this->printPlanilha($colecao); }
/** * ROW * * This record contains the properties of a single row in a * sheet. Rows and cells in a sheet are divided into blocks * of 32 rows. * * -- "OpenOffice.org's Documentation of the Microsoft * Excel File Format" */ private function _readRow() { $spos = $this->_pos; $length = $this->_GetInt2d($this->_data, $spos + 2); $recordData = substr($this->_data, $spos + 4, $length); $spos += 4; if (!$this->_readDataOnly) { // offset: 0; size: 2; index of this row $r = $this->_GetInt2d($recordData, 0); // offset: 2; size: 2; index to column of the first cell which is described by a cell record // offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1 // offset: 6; size: 2; // bit: 14-0; mask: 0x7FF; height of the row, in twips = 1/20 of a point $height = (0x7ff & $this->_GetInt2d($recordData, 6)) >> 0; // bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height $useDefaultHeight = (0x8000 & $this->_GetInt2d($recordData, 6)) >> 15; if (!$useDefaultHeight) { $this->_phpSheet->getRowDimension($r + 1)->setRowHeight($height / 20); } // offset: 8; size: 2; not used // offset: 10; size: 2; not used in BIFF5-BIFF8 // offset: 12; size: 4; option flags and default row formatting // bit: 2-0: mask: 0x00000007; outline level of the row $level = (0x7 & $this->_GetInt4d($recordData, 12)) >> 0; $this->_phpSheet->getRowDimension($r + 1)->setOutlineLevel($level); // bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed $isCollapsed = (0x10 & $this->_GetInt4d($recordData, 12)) >> 4; $this->_phpSheet->getRowDimension($r + 1)->setCollapsed($isCollapsed); // bit: 5; mask: 0x00000020; 1 = row is hidden $isHidden = (0x20 & $this->_GetInt4d($recordData, 12)) >> 5; $this->_phpSheet->getRowDimension($r + 1)->setVisible(!$isHidden); } $this->_pos += 4 + $length; }
/** * Apply the AutoFilter rules to the AutoFilter Range * * @throws PHPExcel_Exception * @return PHPExcel_Worksheet_AutoFilter */ public function showHideRows() { list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range); // The heading row should always be visible echo 'AutoFilter Heading Row ', $rangeStart[1], ' is always SHOWN', PHP_EOL; $this->_workSheet->getRowDimension($rangeStart[1])->setVisible(TRUE); $columnFilterTests = array(); foreach ($this->_columns as $columnID => $filterColumn) { $rules = $filterColumn->getRules(); switch ($filterColumn->getFilterType()) { case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER: $ruleValues = array(); // Build a list of the filter value selections foreach ($rules as $rule) { $ruleType = $rule->getRuleType(); $ruleValues[] = $rule->getValue(); } // Test if we want to include blanks in our filter criteria $blanks = FALSE; $ruleDataSet = array_filter($ruleValues); if (count($ruleValues) != count($ruleDataSet)) { $blanks = TRUE; } if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER) { // Filter on absolute values $columnFilterTests[$columnID] = array('method' => '_filterTestInSimpleDataSet', 'arguments' => array('filterValues' => $ruleDataSet, 'blanks' => $blanks)); } else { // Filter on date group values $arguments = array(); foreach ($ruleDataSet as $ruleValue) { $date = $time = ''; if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') { $date .= sprintf('%04d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]); } $dateTime = $date . $time; $arguments['date'][] = $date; $arguments['time'][] = $time; $arguments['dateTime'][] = $dateTime; } // Remove empty elements $arguments['date'] = array_filter($arguments['date']); $arguments['time'] = array_filter($arguments['time']); $arguments['dateTime'] = array_filter($arguments['dateTime']); $columnFilterTests[$columnID] = array('method' => '_filterTestInDateGroupSet', 'arguments' => array('filterValues' => $arguments, 'blanks' => $blanks)); } break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER: $ruleValues = array(); // Build a list of the filter value selections foreach ($rules as $rule) { $ruleType = $rule->getRuleType(); $ruleValue = $rule->getValue(); if (!is_numeric($ruleValue)) { // Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards $ruleValue = preg_quote($ruleValue); $ruleValue = str_replace(self::$_fromReplace, self::$_toReplace, $ruleValue); } $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue); } $join = $filterColumn->getAndOr(); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join)); break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: $ruleValues = array(); //var_dump($rules); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $dynamicRuleType = $rule->getGrouping(); echo '$dynamicRuleType is ', $dynamicRuleType, PHP_EOL; if ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) { // Number based $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')'; echo 'Average Formula Result is ', $averageFormula, PHP_EOL; $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, NULL, $this->_workSheet->getCell('A1')); $operator = $dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; $ruleValues[] = array('operator' => $operator, 'value' => $average); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_ANDOR_OR)); } else { // Date based $columnFilterTests[$columnID] = array('method' => '_filterTypeDynamicFilters', 'arguments' => $ruleValues); } } break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: $ruleValues = array(); var_dump($rules); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway } $columnFilterTests[$columnID] = array('method' => '_filterTypeTopTenFilters', 'arguments' => $ruleValues); break; } } echo 'Column Filter Test CRITERIA', PHP_EOL; var_dump($columnFilterTests); for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) { echo 'Testing Row = ', $row, PHP_EOL; $result = TRUE; foreach ($columnFilterTests as $columnID => $columnFilterTest) { echo 'Testing cell ', $columnID . $row, PHP_EOL; $cellValue = $this->_workSheet->getCell($columnID . $row)->getCalculatedValue(); echo 'Value is ', $cellValue, PHP_EOL; // Execute the filter test $result = $result && call_user_func_array(array('PHPExcel_Worksheet_AutoFilter', $columnFilterTest['method']), array($cellValue, $columnFilterTest['arguments'])); echo $result ? 'VALID' : 'INVALID', PHP_EOL; // If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests if (!$result) { break; } } echo $result ? 'SHOW' : 'HIDE', PHP_EOL; $this->_workSheet->getRowDimension($row)->setVisible($result); } return $this; }
/** * 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 (!empty($aColumnDimensions)) { 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 (!empty($aRowDimensions)) { 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 $autoFilter = $pSheet->getAutoFilter(); $autoFilterRange = $autoFilter->getRange(); if (!empty($autoFilterRange)) { if ($pNumCols != 0) { $autoFilterColumns = array_keys($autoFilter->getColumns()); if (count($autoFilterColumns) > 0) { list($column, $row) = sscanf($pBefore, '%[A-Z]%d'); $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(); }
/** * Write the header of a worksheet * * @param PHPExcel_Worksheet $worksheet * @param string $wsKind the kind of worksheet * @param array $wsProps the attribues of the worksheet */ function writeWSHeader($worksheet, $wsKind, $wsProps) { $hrange = $wsProps['HeaderRange']; if (!$hrange) { $hrange = computeHeaderRange($wsProps['Columns']); } $colNames = getHeaderNames($wsProps['Columns']); $worksheet->fromArray($colNames, null, 'A1'); if ($wsProps['HeaderHeight']) { $worksheet->getRowDimension('1')->setRowHeight($wsProps['HeaderHeight']); } $freezeCell = 'A2'; $repeat_header = true; switch ($wsKind) { case 'Front Page': $freezeCell = 'A3'; $repeat_header = false; break; case 'DC Stats': $worksheet->getStyle($hrange)->getAlignment()->setWrapText(true); break; case 'DC Inventory': case 'Rack Inventory': break; } $worksheet->freezePane($freezeCell); $worksheet->getStyle($hrange)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $worksheet->getStyle($hrange)->getFill()->getStartColor()->setRGB($wsProps['FillColor']); $worksheet->getStyle($hrange)->getFont()->getColor()->setRGB($wsProps['HeadingFontColor']); $worksheet->getStyle($hrange)->getFont()->setBold(true); if ($repeat_header) { $worksheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1); } }
/** * Write VML comment to XML format * * @param PHPExcel_Worksheet $pWorksheet * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param string $pCellReference Cell reference * @param PHPExcel_Comment $pComment Comment * @throws PHPExcel_Writer_Exception */ public function _writeVMLComment(PHPExcel_Worksheet $pWorksheet = null, PHPExcel_Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', PHPExcel_Comment $pComment = null) { // Metadata list($column, $row) = PHPExcel_Cell::coordinateFromString($pCellReference); $column = PHPExcel_Cell::columnIndexFromString($column); $id = 1024 + $column + $row; $id = substr($id, 0, 4); $cssWidth = $pComment->getWidth(); $cssHeight = $pComment->getHeight(); // v:shape $objWriter->startElement('v:shape'); $objWriter->writeAttribute('id', '_x0000_s' . $id); $objWriter->writeAttribute('type', '#_x0000_t202'); $objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $cssWidth . ';height:' . $cssHeight . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden')); $objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB()); $objWriter->writeAttribute('o:insetmode', 'auto'); // v:fill $objWriter->startElement('v:fill'); $objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB()); $objWriter->endElement(); // v:shadow $objWriter->startElement('v:shadow'); $objWriter->writeAttribute('on', 't'); $objWriter->writeAttribute('color', 'black'); $objWriter->writeAttribute('obscured', 't'); $objWriter->endElement(); // v:path $objWriter->startElement('v:path'); $objWriter->writeAttribute('o:connecttype', 'none'); $objWriter->endElement(); // v:textbox $objWriter->startElement('v:textbox'); $objWriter->writeAttribute('style', 'mso-direction-alt:auto'); // div $objWriter->startElement('div'); $objWriter->writeAttribute('style', 'text-align:left'); $objWriter->endElement(); $objWriter->endElement(); // x:ClientData $objWriter->startElement('x:ClientData'); $objWriter->writeAttribute('ObjectType', 'Note'); // x:MoveWithCells $objWriter->writeElement('x:MoveWithCells', ''); // x:SizeWithCells $objWriter->writeElement('x:SizeWithCells', ''); // x:Anchor // anchor is a nice way to locate the comment sensibly with respect to the sheet's rows/columns, but // in order to do so, need to be able to convert roughly to point dimensions from the comments // width/height, which are optimized for css if (preg_match('/\\s*pt\\s*$/', $cssWidth) && preg_match('/\\s*pt\\s*$/', $cssHeight)) { // compute CSS height to an integer # pts $width = intval(preg_replace('/\\s*pt\\s*$/', '', $cssWidth)); $height = intval(preg_replace('/\\s*pt\\s*$/', '', $cssHeight)); // starting from the row/column this is being placed in try // to figure out the row column that should anchor the lower-right // corner of the comment $clearedWidth = 0; $clearedHeight = 0; $maxColumns = 2; $maxRows = 10; // loop incremenets, so decrement both to start $curColumn = $column - 1; $curRow = $row - 1; while ($clearedWidth < $width && $curColumn - $column < $maxColumns) { ++$curColumn; $dim = $pWorksheet->getColumnDimensionByColumn($curColumn, false); $clearedWidth += $dim && $dim->getWidth() > 0 ? $dim->getWidth() : 96; } while ($clearedHeight < $height && $curRow - $row < $maxRows) { ++$curRow; $dim = $pWorksheet->getRowDimension($curRow, false); $clearedHeight += $dim && $dim->getRowHeight() > 0 ? $dim->getRowHeight() : 14; } $colBump = 15; $rowBump = 10; $anchor = $column . ', ' . $colBump . ', ' . ($row - 1) . ', ' . $rowBump . ', ' . $curColumn . ', ' . max($clearedWidth - $width, 0) . ', ' . $curRow . ', ' . max($clearedHeight - $height, 0); // lower-right row offset $objWriter->writeElement('x:Anchor', $anchor); } // x:AutoFill $objWriter->writeElement('x:AutoFill', 'False'); // x:Row $objWriter->writeElement('x:Row', $row - 1); // x:Column $objWriter->writeElement('x:Column', $column - 1); $objWriter->endElement(); $objWriter->endElement(); }