/**
  * Добавляет в таблицу сводные данные по отчету
  *
  * @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')));
 }
Пример #2
0
 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++;
 }
Пример #4
0
 /**
  * @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;
 }
Пример #5
0
 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);
 }
Пример #6
0
 /**
  * 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');
 }
Пример #7
0
/**
 * 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']);
    }
}
Пример #8
0
 /**
  * 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 = '&nbsp;';
             $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 = '&nbsp;';
                 }
                 // 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.");
     }
 }
Пример #9
0
 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);
 }
Пример #10
0
 /**
  * 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);
 }
Пример #11
0
 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();
 }
Пример #14
0
 /**
  * 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;
 }
Пример #16
0
 protected function getStyle($char)
 {
     return $this->sheet->getStyle($char . $this->currentRow->getRowIndex());
 }
Пример #17
0
 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')))));
 }
Пример #18
0
 /**
  * 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;
 }
Пример #19
0
    /**
     * Insert a new column, updating all possible related data
     *
     * @param 	int	$pBefore	Insert before this one
     * @param 	int	$pNumCols	Number of columns to insert
     * @param 	int	$pNumRows	Number of rows to insert
     * @throws 	Exception
     */
    public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) {
		// 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();
    }
Пример #20
0
 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());
 }
Пример #21
0
 /**
  * @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))));
 }
Пример #22
0
 /**
  * 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 = '&nbsp;';
             $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 = '&nbsp;';
                 }
                 // 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.");
     }
 }
Пример #23
0
 /**
  * Добавляет в таблицу сводные данные по отчету
  *
  * @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)));
 }
Пример #24
0
 /**
  * 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;
 }
Пример #25
-1
 /**
  * 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;
 }
Пример #26
-1
 /**
  * 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 = '&nbsp;';
             $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 = '&nbsp;';
                 }
                 // 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.");
     }
 }