PHP PHPExcel_Worksheet::getStyleByColumnAndRow方法代码示例

 protected function writeCell($value, $column, $row, $config)
 {
     // auto type
     if (!isset($config['type']) || $config['type'] === null) {
         $this->sheet->setCellValueByColumnAndRow($column, $row, $value);
     } elseif ($config['type'] === 'date') {
         if (!is_int($value)) {
             $timestamp = strtotime($value);
         }
         $this->sheet->SetCellValueByColumnAndRow($column, $row, \PHPExcel_Shared_Date::PHPToExcel($timestamp));
         if (!isset($config['styles']['numberformat']['code'])) {
             $config['styles']['numberformat']['code'] = $this->defaultDateFormat;
         }
     } elseif ($config['type'] === 'url') {
         if (isset($config['label'])) {
             if ($config['label'] instanceof \Closure) {
                 // NOTE: calculate label on top level
                 $label = call_user_func($config['label']);
             } else {
                 $label = $config['label'];
             }
         } else {
             $label = $value;
         }
         $urlValid = filter_var($value, FILTER_VALIDATE_URL) !== false;
         if (!$urlValid) {
             $label = '';
         }
         $this->sheet->setCellValueByColumnAndRow($column, $row, $label);
         if ($urlValid) {
             $this->sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->setUrl($value);
         }
     } else {
         $this->sheet->setCellValueExplicitByColumnAndRow($column, $row, $value, $config['type']);
     }
     if (isset($config['styles'])) {
         $this->sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($config['styles']);
     }
 }

/**
 * 行を完全コピーする
 *
 * http://blog.kotemaru.org/old/2012/04/06.html より
 * @param PHPExcel_Worksheet $sheet
 * @param int $srcRow
 * @param int $dstRow
 * @param int $height
 * @param int $width
 * @throws PHPExcel_Exception
 */
function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width)
{
    for ($row = 0; $row < $height; $row++) {
        // セルの書式と値の複製
        for ($col = 0; $col < $width; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
            $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);
            $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
            $sheet->setCellValue($dstCell, $cell->getValue());
            $sheet->duplicateStyle($style, $dstCell);
        }
        // 行の高さ複製。
        $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
        $sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
    }
    // セル結合の複製
    // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
    // - $merge="AB16:AC19"
    foreach ($sheet->getMergeCells() as $mergeCell) {
        $mc = explode(":", $mergeCell);
        $col_s = preg_replace("/[0-9]*/", "", $mc[0]);
        $col_e = preg_replace("/[0-9]*/", "", $mc[1]);
        $row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow;
        $row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow;
        // 複製先の行範囲なら。
        if (0 <= $row_s && $row_s < $height) {
            $merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e);
            $sheet->mergeCells($merge);
        }
    }
}

 /**
  * @param $columnNumber
  * @param $columnIdentifier
  * @param $type
  */
 protected function doCellStyling($columnNumber, $columnIdentifier, $type)
 {
     $excelSettings = $this->getExcelSettingsByColumnIdentifier($columnIdentifier);
     if (!is_array($excelSettings[$type])) {
         return;
     }
     $settings = $excelSettings[$type];
     if ($settings['dataType']) {
         $this->activeSheet->getCellByColumnAndRow($columnNumber, $this->rowNumber)->setDataType($settings['dataType']);
     }
     if ($settings['wrapText']) {
         $this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->getAlignment()->setWrapText($settings['wrapText']);
     }
     if ($settings['vertical']) {
         $this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->getAlignment()->setVertical($settings['vertical']);
     }
     if ($settings['shrinkToFit']) {
         $this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->getAlignment()->setShrinkToFit($settings['shrinkToFit']);
     }
     if ($type == 'body') {
         if (!array_key_exists($columnIdentifier, $this->bodyCellStyleCache)) {
             $this->bodyCellStyleCache[$columnIdentifier] = $this->buildStyleArray($settings);
         }
         $this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->applyFromArray($this->bodyCellStyleCache[$columnIdentifier]);
     } else {
         $this->activeSheet->getStyleByColumnAndRow($columnNumber, $this->rowNumber)->applyFromArray($this->buildStyleArray($settings));
     }
 }

 /**
  * Sets a date/time value at the given col,row location
  *
  * @param $object    the raw data value object
  */
 protected function setTimeDataValue(\SMWTimeValue $object)
 {
     $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
     $value = \PHPExcel_Shared_Date::stringToExcel(str_replace('T', ' ', $object->getISO8601Date()));
     $this->sheet->getCellByColumnAndRow($this->colNum, $this->rowNum)->setValueExplicit($value, $type);
     if (!$this->styled) {
         $this->sheet->getStyleByColumnAndRow($this->colNum, $this->rowNum)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
     }
 }

 protected function apply_format($row, $col, $format = null)
 {
     if (!$format) {
         $format = new MoodleExcelFormat();
     } else {
         if (is_array($format)) {
             $format = new MoodleExcelFormat($format);
         }
     }
     $this->worksheet->getStyleByColumnAndRow($col, $row + 1)->applyFromArray($format->get_format_array());
 }

 /**
  * Método que gera a planilha de evolução anual por unidade e ano
  */
 public function evolucaoAnual()
 {
     //Inicializando os filtros usados na página
     $this->filtro->initGets(array("unidade", "ano"));
     //Inicializando os objetos básicos
     $sqlUnidade = new SqlUnidade();
     $sqlSerie = new SqlSerie();
     $sqlEnsino = new SqlEnsino();
     $sqlAfa = new SqlAfa();
     //Contadores em geral
     $linha = 0;
     //Define a linha inicial da impressão da planilha
     $coluna = 0;
     //Define a coluna inicial da planilha
     $colunaInicial = $coluna;
     $r = 1;
     //Contador "R" das planilhas
     //Objetos e variaveis básicas da planilha
     $conceitos = array("D" => "Deficiente", "I" => "Insuficiente", "R" => "Regular", "B" => "Bom", "L" => "Louvor");
     $bimestres = array("1" => "1º Bimestre", "2" => "2º Bimestre", "3" => "3º Bimestre", "4" => "4º Bimestre");
     $bimRomanos = array("1" => "I", "2" => "II", "3" => "III", "4" => "IV");
     //Fazendo as consultas iniciais
     $ensinos = $sqlEnsino->listarTodos($this->filtro);
     $avaliacoea = $sqlAfa;
     $avaliacoes = $afaObj->listarAvaliacoes($unidade, $ano);
     die("ok");
     //Iniciando a geração da planilha
     while ($ensino = $ensinos->fetchObject()) {
         $avaliacoes = $afaObj->buscarAvaliacoes($ensino->pk_ensino, $unidade, $ano);
         //Teste para somente exibir as plainlhas de ensinos que contenham registros
         if (count($avaliacoes)) {
             //Ajustando a primeira coluna
             $this->workSheet->getColumnDimensionByColumn($coluna)->setWidth(33);
             //Trecho referente a primeira planilha
             //PREENCHENDO A PRIMEIRA PLANILHA
             //Definindo o título da planiplha
             $tituloPlanilhas = "TABELA R" . $r++ . " – DISTRIBUIÇÃO PERCENTUAL DOS CONCEITOS " . "FORMATIVOS DOS ALUNOS DO " . strtoupper($ensino->nome_ensino) . " EM CADA BIMESTRE";
             $this->workSheet->setCellValueByColumnAndRow($coluna, ++$linha, $tituloPlanilhas)->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FFF2CC")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $this->workSheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(TRUE);
             //Tornando a font bolder
             //
             $linhaReferencia = $linha;
             $linha++;
             $coluna++;
             //Criando o cabeçalho da primeira planilha
             foreach ($bimestres as $bimestre) {
                 $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $bimestre)->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 $this->workSheet->mergeCellsByColumnAndRow($coluna, $linha, $coluna + 4, $linha);
                 //Mesclando as colunas
                 $coluna += 5;
             }
             $this->workSheet->getStyleByColumnAndRow(--$coluna, $linha)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             $coluna = $colunaInicial;
             //Resetando o contador de colunas
             // Adicionando as colunas indicadoras de níveis
             $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, "Série")->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
             $this->workSheet->mergeCellsByColumnAndRow($coluna, $linha, $coluna++, ++$linha);
             //Mesclando as colunas
             //
             foreach (range(0, 3) as $indice) {
                 foreach (array_keys($conceitos) as $letra) {
                     $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $letra)->getStyleByColumnAndRow($coluna++, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 }
             }
             //Mesclando o título da planilha
             $this->workSheet->mergeCellsByColumnAndRow($colunaInicial, $linhaReferencia, --$coluna, $linhaReferencia);
             $this->workSheet->getStyleByColumnAndRow($coluna, $linhaReferencia)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             //
             $linha++;
             //Definindo as variaveis para calculo de médias
             $mdD1B = $mdI1B = $mdR1B = $mdB1B = $mdL1B = array();
             $mdD2B = $mdI2B = $mdR2B = $mdB2B = $mdL2B = array();
             $mdD3B = $mdI3B = $mdR3B = $mdB3B = $mdL3B = array();
             $mdD4B = $mdI4B = $mdR4B = $mdB4B = $mdL4B = array();
             $this->workSheet->getStyleByColumnAndRow(--$coluna, $linha)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             $coluna = $colunaInicial;
             //Resetando o contador de colunas
             //adicionando as séries da primeira coluna
             foreach ($avaliacoes as $avaliacao) {
                 $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $avaliacao["serie_nome"])->getStyleByColumnAndRow($coluna++, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 //Separando os dados
                 foreach (array_keys($bimestres) as $bimestre) {
                     foreach (array_keys($conceitos) as $conceito) {
                         $comando = '$qt' . $conceito . $bimestre . 'B = (float) ' . '(isset($avaliacao["qt-' . strtolower($conceito) . '-' . $bimestre . 'bi"]) ' . '? $avaliacao["qt-' . strtolower($conceito) . '-' . $bimestre . 'bi"] : 0.0);';
                         eval($comando);
                     }
                     $comando = '$qt' . $bimestre . 'B = (float) (isset($avaliacao["qt-' . $bimestre . 'bi"]) ' . '? $avaliacao["qt-' . $bimestre . 'bi"] : 0.0);';
                     eval($comando);
                 }
                 //Definindo os percentudais
                 foreach (array_keys($bimestres) as $bimestre) {
                     foreach (array_keys($conceitos) as $conceito) {
                         $comando = '$per' . $conceito . $bimestre . 'B = ' . 'Matematica::percentualDe($qt' . $conceito . $bimestre . 'B, $qt' . $bimestre . 'B);';
                         eval($comando);
                         $comando = 'array_push($md' . $conceito . $bimestre . 'B, $per' . $conceito . $bimestre . 'B);';
                         eval($comando);
                     }
//.........这里部分代码省略.........