/**
* Добавляет в таблицу сводные данные по отчету
*
* @param PHPExcel_Worksheet $activeSheet
* @param array $reportData
* @param $rowc
*/
protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc)
{
$activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('E' . $rowc, $reportData['total']['debit'])->setCellValue('F' . $rowc, $reportData['total']['sum'])->setCellValue('H' . $rowc, $reportData['total']['sum_with_vat']);
$activeSheet->getStyle('A' . $rowc . ':K' . $rowc)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'dbe5f1')), 'font' => array('bold' => true, 'color' => array('rgb' => '000000'))));
$activeSheet->setCellValue('A' . ($rowc + 2), 'Не оплачено (без учета ндс):')->setCellValue('E' . ($rowc + 2), $reportData['total']['not_paid'])->setCellValue('A' . ($rowc + 3), 'Оплачено (без учета ндс):')->setCellValue('E' . ($rowc + 3), $reportData['total']['paid']);
$activeSheet->getStyle('A' . ($rowc + 2) . ':E' . ($rowc + 2))->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'f2dddc')));
$activeSheet->getStyle('A' . ($rowc + 3) . ':E' . ($rowc + 3))->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'eaf1dd')));
}
private function buildFooter()
{
$column_index = 0;
foreach ($this->responseTableView->getFooterData() as $footer) {
$column_name = Utility::getNameFromNumber($column_index++);
$this->sheet->setCellValue($column_name . $this->row_index, $footer);
$this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
}
}
private function buildHead()
{
$column_index = 1;
foreach ($this->analyze->getCriteriaNames() as $criterion_name) {
$column_name = Utility::getNameFromNumber($column_index++);
$this->sheet->setCellValue($column_name . $this->row_index, $criterion_name);
$this->sheet->getColumnDimension($column_name)->setWidth(30, 0);
$this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
}
$this->row_index++;
}
/**
* @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;
}
private function addTransactionPage(PHPExcel_Worksheet $activeSheet)
{
$activeSheet->setTitle('Транзакции')->setCellValue('A12', 'Статус')->setCellValue('B12', 'Тип')->setCellValue('C12', 'ID')->setCellValue('D12', 'Дата')->setCellValue('E12', 'IP')->setCellValue('F12', 'ГЕО')->setCellValue('G12', 'URL цели')->setCellValue('H12', 'Источник')->setCellValue('I12', 'Материал')->setCellValue('J12', 'Выплата')->setCellValue('K12', 'Вознаграждение')->setCellValue('L12', 'Зароботок')->setCellValue('M12', 'Цель');
$row = 13;
$availableStatuses = ActionsLog::getAvailableStatuses();
foreach ($this->transactionData['rows'] as $tr) {
$activeSheet->setCellValue('A' . $row, $availableStatuses[$tr['status']])->setCellValue('B' . $row, $tr['source_type_name'])->setCellValue('C' . $row, $tr['id'])->setCellValue('D' . $row, Yii::app()->dateFormatter->formatDateTime($tr['date']))->setCellValue('E' . $row, $tr['ip'])->setCellValue('F' . $row, $tr['geo'])->setCellValue('G' . $row, $tr['target_url_decoded'])->setCellValue('H' . $row, $tr['source_name'])->setCellValue('I' . $row, $tr['target_name'])->setCellValue('J' . $row, $tr['payment'])->setCellValue('K' . $row, $tr['reward'])->setCellValue('L' . $row, $tr['debit'])->setCellValue('M' . $row, $tr['action_name']);
$row++;
}
$activeSheet->setCellValue('J' . $row, $this->transactionData['total']['payment'])->setCellValue('K' . $row, $this->transactionData['total']['reward'])->setCellValue('L' . $row, $this->transactionData['total']['debit']);
$activeSheet->getColumnDimension('A')->setWidth(16.3 * 1.05);
$activeSheet->getColumnDimension('B')->setWidth(16.43 * 1.05);
$activeSheet->getColumnDimension('C')->setWidth(5 * 1.05);
$activeSheet->getColumnDimension('D')->setWidth(17.86 * 1.05);
$activeSheet->getColumnDimension('E')->setWidth(14.14 * 1.05);
$activeSheet->getColumnDimension('F')->setWidth(34 * 1.05);
$activeSheet->getColumnDimension('G')->setWidth(31 * 1.05);
$activeSheet->getColumnDimension('H')->setWidth(30.86 * 1.05);
$activeSheet->getColumnDimension('I')->setWidth(19.14 * 1.05);
$activeSheet->getColumnDimension('J')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('K')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('L')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('M')->setWidth(30.7 * 1.05);
$activeSheet->getStyle('A12:M' . $row)->getAlignment()->setWrapText(true);
$this->formatTable($activeSheet, 'A', '12', 'M', $row, array('formatTotal' => true, 'innerRowHeight' => -1, 'headerRowHeight' => 27));
$this->addLogo($activeSheet);
$this->setHeader($activeSheet, $this->getHeaders());
$this->setPageFit($activeSheet, self::FIT_TO_WIDTH, PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
}
/**
* format the sums row if there are any sums
* (borders and background color)
*/
protected function formatSums()
{
if (!$this->anySumsWereAdded) {
return;
}
$this->sheet->getStyle(self::excelRange(0, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr), count($this->activeColumns_arr) - 1, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr)))->applyFromArray(array('font' => array('bold' => 'true'), 'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))));
$this->sheet->getStyle(self::excelRange(0, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr), count($this->activeColumns_arr) - 1, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr)))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFEEEEEE');
}
/**
* Write one line of the data center statistics
*
* @param PHPExcel_Worksheet $worksheet
* @param string $style
* @param array $wsProps
* @param integer $rownum
* @param array $DCStatsSum
*/
function writeDCStatsLine($worksheet, $style, $wsProps, $rownum, $DCStatsSum)
{
$worksheet->fromArray($DCStatsSum, null, 'A' . $rownum);
$lastCol = count($DCStatsSum) - 1;
$range = 'A' . $rownum . ':' . PHPExcel_Cell::stringFromColumnIndex($lastCol) . $rownum;
switch ($style) {
case 'Total':
$worksheet->getStyle($range)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$worksheet->getStyle($range)->getFill()->getStartColor()->setRGB($wsProps['FillColor']);
$worksheet->getStyle($range)->getFont()->getColor()->setRGB($wsProps['HeadingFontColor']);
case 'SummaryLine':
$worksheet->getStyle($range)->getFont()->setBold(true);
$worksheet->getStyle($range)->applyFromArray($wsProps['Border Style']);
}
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet hashcode
$sheetHash = $pSheet->getHashCode();
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow] : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = '';
if (!$this->_useInlineCss) {
$cssClass = 'column' . $colNum;
} else {
$cssClass = isset($this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum] : '';
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
// Convert UTF8 data to PCDATA
$cellData = htmlspecialchars($cellData);
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex();
$cssClass .= ' ' . $cell->getDataType();
} else {
$cssClass .= isset($this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()]) ? $this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()] : '';
// General horizontal alignment: Actual horizontal alignment depends on dataType
if ($pSheet->getStyle($cell->getCoordinate())->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()])) {
if (preg_match('/text-align: [^;]*;/', $cssClass)) {
$cssClass = preg_replace('/text-align: [^;]*;/', $this->_cssStyles['.' . $cell->getDataType()], $cssClass);
} else {
$cssClass .= $this->_cssStyles['.' . $cell->getDataType()];
}
}
}
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first[0] == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
//** Necessary redundant code for the sake of PHPExcel_Writer_PDF **
// We must explicitly write the width of the <td> element because TCPDF
// does not recognize e.g. <col style="width:42pt">;
$width = 0;
$columnIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
for ($i = $columnIndex; $i < $columnIndex + $colSpan; ++$i) {
if (isset($this->_columnWidths[$sheetHash][$i])) {
$width += $this->_columnWidths[$sheetHash][$i];
}
}
$cssClass .= 'width: ' . $width . 'pt; ';
//** end of redundant code **
$html .= ' style="' . $cssClass . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
$html .= $cellData;
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
protected function setBorderStyle(\PHPExcel_Worksheet $phpExcelSheet, $fromCode, $toCode, $rowLength, $color)
{
$borderStyle = ['borders' => ['inside' => ['style' => \PHPExcel_Style_Border::BORDER_THIN, 'color' => ['argb' => $color]], 'outline' => ['style' => \PHPExcel_Style_Border::BORDER_THIN, 'color' => ['argb' => $color]]]];
$phpExcelSheet->getStyle($fromCode . '1:' . $toCode . $rowLength)->applyFromArray($borderStyle);
}
/**
* 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);
}
private function addChart1(\PHPExcel $ea, \PHPExcel_Worksheet $ews)
{
//The below line should be moved into addAnalysis but we move this here to show that $ews is actually referring to the sheet
$ews->getStyle('b4')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
$title = new \PHPExcel_Chart_Title($ews->getTitle());
// Set the data serie labels
$dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Summary!A1', NULL, 1));
// Set X-Axis Labels
$xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Summary!A2:A3', NULL, 2));
// Set data serie values
$dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Summary!B2:B3', NULL, 2));
// Build a dataserie
$ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_PIECHART, null, range(0, count($dsv) - 1), $dsl, $xal, $dsv);
// A layout for the Pie Chart
$layout = new \PHPExcel_Chart_Layout();
$layout->setShowVal(true);
$layout->setShowPercent(true);
// Set series in the plot area
$pa = new \PHPExcel_Chart_PlotArea($layout, array($ds));
// Set legend
$legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
//Create Chart
$chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL);
$chart->setTopLeftPosition('K1');
$chart->setBottomRightPosition('U30');
$ews->addChart($chart);
}
/**
* Форматирует таблицу с данными
*
* @param PHPExcel_Worksheet $activeSheet
* @param $x1
* @param $y1
* @param $x2
* @param $y2
*/
protected function formatTable(PHPExcel_Worksheet $activeSheet, $x1, $y1, $x2, $y2)
{
$activeSheet->getStyle($x1 . $y1 . ':' . $x2 . $y1)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '528ed6')), 'font' => array('size' => 10, 'color' => array('rgb' => 'FFFFFF')), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)));
$activeSheet->getStyle($x1 . $y1 . ':' . $x2 . $y2)->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '000000')))));
}
private function getStyleOfCell(\PHPExcel_Worksheet $templateSheet, $templateCor)
{
$style = $templateSheet->getStyle($templateCor);
$style = $style->getIsSupervisor() ? $style->getSharedComponent() : $style;
return $style->getIndex();
}
/**
* Inserts data into worksheet and returns it
*
* @return PHPExcel_Worksheet
*/
public function render()
{
// Set worksheet header
$this->_set_row(1, $this->columns, TRUE);
//set header style
$obj_style = new PHPExcel_Style();
$style = Kohana::$config->load('phpexcel.header');
$obj_style->applyFromArray($style);
$column_dim = PHPExcel_Cell::stringFromColumnIndex(count($this->columns) - 1);
$this->_worksheet->setSharedStyle($obj_style, 'A1:' . $column_dim . '1');
// Set data
$rows = 0;
foreach ($this->data as $row => $data) {
$this->_set_row($row + 2, $data);
$rows++;
}
// Set column styles and width
$column = 0;
foreach (array_keys($this->columns) as $key) {
$column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
$format = Arr::get($this->formats, $key);
if ($format !== NULL) {
$this->_worksheet->getStyle($column_dim . 2 . ':' . $column_dim . (2 + $rows))->getNumberFormat()->setFormatCode($format);
}
if ($this->auto_size === TRUE) {
$this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
}
$column++;
}
return $this->_worksheet;
}
/**
* Add an header row to a {@link PHPExcel_Worksheet}.
* @param PHPExcel_Worksheet $sheet
* @param array $fields List of fields
* @return PHPExcel_Worksheet
*/
protected function headerRow(PHPExcel_Worksheet &$sheet, array $fields)
{
// Counter
$row = 1;
$col = 0;
// Add each field to the first row
foreach ($fields as $field => $type) {
$sheet->setCellValueByColumnAndRow($col, $row, $field);
$col++;
}
// Get the last column
$col--;
$endcol = PHPExcel_Cell::stringFromColumnIndex($col);
// Set Autofilters and Header row style
$sheet->setAutoFilter("A1:{$endcol}1");
$sheet->getStyle("A1:{$endcol}1")->getFont()->setBold(true);
return $sheet;
}
protected function getStyle($char)
{
return $this->sheet->getStyle($char . $this->currentRow->getRowIndex());
}
protected function formatTableHeader(PHPExcel_Worksheet $activeSheet, $x1, $y1, $x2, $y2)
{
$activeSheet->getStyle($x1 . $y1 . ':' . $x2 . $y2)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'dbe5f1')), 'font' => array('bold' => true, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '7f7f7f')), 'vertical' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '7f7f7f')))));
}
/**
* Set format for column
* @param string $columnName
* @param string $formatString
* @return \Meridius\PhpExcel\Formatter
*/
private function formatColumn($columnName, $formatString)
{
$this->sheet->getStyle($columnName . '1:' . $columnName . $this->sheet->getHighestDataRow())->getNumberFormat()->setFormatCode($formatString);
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();
}
protected function apply_row_format($row, $format = null)
{
if (!$format) {
$format = new MoodleExcelFormat();
} else {
if (is_array($format)) {
$format = new MoodleExcelFormat($format);
}
}
$this->worksheet->getStyle($row + 1)->applyFromArray($format->get_format_array());
}
/**
* @param int $curRow
* @param PHPExcel_Worksheet $curSheet
* @param string $color
* @param boolean $partial
*/
private function highlightRow($curRow, $curSheet, $color, $partial = false)
{
if ($partial) {
$range = "A{$curRow}:H{$curRow}";
} else {
$range = "A{$curRow}:O{$curRow}";
}
$curSheet->getStyle($range)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $color))));
}
/**
* Write row to HTML file
*
* @param mixed $pFileHandle PHP filehandle
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @throws Exception
*/
private function _writeRow($pFileHandle = null, PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (!is_null($pFileHandle) && is_array($pValues)) {
// Write row start
fwrite($pFileHandle, ' <tr class="row' . $pRow . '">' . "\r\n");
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = 'column' . $colNum;
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
}
$cellData .= $element->getText();
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode();
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
fwrite($pFileHandle, ' <td');
fwrite($pFileHandle, ' class="' . $cssClass . '"');
if ($colSpan > 1) {
fwrite($pFileHandle, ' colspan="' . $colSpan . '"');
}
if ($rowSpan > 1) {
fwrite($pFileHandle, ' rowspan="' . $rowSpan . '"');
}
fwrite($pFileHandle, '>');
// Image?
$this->_writeImageInCell($pFileHandle, $pSheet, $cell->getCoordinate());
// Cell data
fwrite($pFileHandle, $cellData);
// Column end
fwrite($pFileHandle, '</td>' . "\r\n");
}
// Next column
$colNum++;
}
// Write row end
fwrite($pFileHandle, ' </tr>' . "\r\n");
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* Добавляет в таблицу сводные данные по отчету
*
* @param PHPExcel_Worksheet $activeSheet
* @param array $reportData
* @param $rowc
*/
protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc)
{
$activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('C' . $rowc, $reportData['total']['shows'])->setCellValue('D' . $rowc, $reportData['total']['clicks'])->setCellValue('E' . $rowc, $reportData['total']['ctr'])->setCellValue('F' . $rowc, $reportData['total']['clickfraud'])->setCellValue('G' . $rowc, $reportData['total']['price']);
$activeSheet->getStyle('A' . $rowc . ':D' . $rowc)->applyFromArray(array('font' => array('bold' => true)));
}
/**
* Read BLANK record
*/
private function _readBlank()
{
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
$pos += 4;
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; col index
$col = $this->_GetInt2d($recordData, 2);
$columnString = PHPExcel_Cell::stringFromColumnIndex($col);
// Read cell?
if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($recordData, 4);
// add style information
if (!$this->_readDataOnly) {
$this->_phpSheet->getStyle($columnString . ($row + 1))->applyFromArray($this->_xf[$xfindex]);
}
}
// move stream pointer to next record
$this->_pos += 4 + $length;
}
/**
* Inserts data into worksheet and returns it
*
* @return PHPExcel_Worksheet
*/
public function render()
{
// Set worksheet header
if ($this->include_names) {
$this->_set_row(1, $this->columns, TRUE);
$offset = 2;
} else {
$offset = 1;
}
// Set data
$rows = 0;
foreach ($this->data as $row => $data) {
$this->_set_row($row + $offset, $data);
$rows++;
}
// Set column styles and width
$column = 0;
foreach ($this->columns as $key => $name) {
$column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
$format = Arr::get($this->formats, $key);
if ($format !== NULL) {
$this->_worksheet->getStyle($column_dim . $offset . ':' . $column_dim . ($offset + $rows))->getNumberFormat()->setFormatCode($format);
}
if ($this->auto_size === TRUE) {
$this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
}
$column++;
}
return $this->_worksheet;
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet hashcode
$sheetHash = $pSheet->getHashCode();
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow] : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = '';
if (!$this->_useInlineCss) {
$cssClass = 'column' . $colNum;
} else {
$cssClass = isset($this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum] : '';
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
// Convert UTF8 data to PCDATA
$cellData = htmlspecialchars($cellData);
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex();
} else {
$cssClass .= isset($this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()]) ? $this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()] : '';
}
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
$html .= ' style="' . $cssClass . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
if ($this->_useInlineCss) {
$html .= '<span style="' . $cssClass . '">';
}
$html .= $cellData;
if ($this->_useInlineCss) {
$html .= '</span>';
}
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}