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