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']); } }
/** * Render the header row */ protected function renderHeader() { $columnNumber = 0; // Headers if ($this->templateVariableContainer->exists('listCaptions')) { foreach ($this->templateVariableContainer['listCaptions'] as $columnIdentifier => $caption) { /* @var $caption Tx_PtExtlist_Domain_Model_List_Cell */ $this->activeSheet->setCellValueByColumnAndRow($columnNumber, $this->rowNumber, strip_tags($caption->getValue())); $excelSettings = $this->getExcelSettingsByColumnIdentifier($columnIdentifier); /** * Width */ if (is_array($excelSettings) && array_key_exists('width', $excelSettings)) { $this->activeSheet->getColumnDimensionByColumn($columnNumber)->setWidth($excelSettings['width']); } else { $this->activeSheet->getColumnDimensionByColumn($columnNumber)->setAutoSize(true); } $this->doCellStyling($columnNumber, $columnIdentifier, 'header'); $columnNumber++; } } $this->rowNumber++; }
/** * Populates the PHPExcel sheet with the headers from the result query * * @param SMWQueryResult $res The query result */ protected function populateDocumentWithHeaders(SMWQueryResult $res) { $this->colNum = 0; foreach ($res->getPrintRequests() as $pr) { $header = $pr->getLabel(); if ($this->showLabel($header)) { $this->sheet->setCellValueByColumnAndRow($this->colNum, self::HEADER_ROW_OFFSET, $header)->getStyleByColumnAndRow($this->colNum, self::HEADER_ROW_OFFSET)->getFont()->setBold(true); $this->colNum++; } } }
/** * @author caochunhui@dachuwang.com * @description 用数组和地址直接生成excel文件 * 每一个数组占一个sheet */ private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array()) { //下面的代码是抄的。 //set cache $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); //open excel file $write_objPHPExcel = new PHPExcel(); $write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10); //下面要循环了 $sheet_cnt = 0; foreach ($arr as $item) { //用订单id.csv来命名每一个sheet $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]); //$out_sheet->setTitle($item); //row index start from 1 $row_index = 0; foreach ($item as $row) { $row_index++; //$cellIterator = $row->getCellIterator(); //$cellIterator->setIterateOnlyExistingCells(false); //column index start from 0 $column_index = -1; foreach ($row as $cell) { $column_index++; //var_dump($cell); $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING); } } //如果条码数组不为空,那么说明需要在sheet里插入条码 if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) { $barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]); if ($barcode_download_res['code'] == 200) { //no pic you say a jb $pic_path = $barcode_download_res['file']; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('barcode'); $objDrawing->setDescription(''); $objDrawing->setPath($pic_path); $objDrawing->setHeight(50); $objDrawing->setCoordinates('D26'); //$objDrawing->setOffsetX(10); //$objDrawing->getShadow()->setVisible(true); //$objDrawing->getShadow()->setDirection(36); $objDrawing->setWorksheet($out_sheet); //no pic you say a jb } } $write_objPHPExcel->addSheet($out_sheet); $sheet_cnt++; } $write_objPHPExcel->removeSheetByIndex(0); //删除第一个空sheet //上面要循环了 //上面的代码是抄的 //write excel file $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel); $dir_name = dirname($out_name); if (!is_dir($dir_name)) { $res = mkdir($dir_name, 0777, TRUE); } $objWriter->save($out_name); }
/** * Write one blank somewhere in the worksheet. * * @param integer $row Zero indexed row * @param integer $col Zero indexed column * @param mixed $format The XF format for the cell */ public function write_blank($row, $col, $format = null) { $this->worksheet->setCellValueByColumnAndRow($col, $row + 1, ''); $this->apply_format($row, $col, $format); }
/** * Set value in specific cell * @param \PHPExcel_Worksheet $sheet The worksheet * @param integer $col The selected column * @param integer $row The selected row * @param array $value The values to insert * @param array $format Associative arrays with formats * @param array $styleArray An array representing the style * @param $row */ protected function applyValue(\PHPExcel_Worksheet $sheet, $col, $row, $value, $format, $styleArray = array()) { if (0 == count($styleArray)) { $styleArray = $this->getDefaultStyleArrayForRow($row); } $style = new \PHPExcel_Style(); $style->applyFromArray($styleArray); switch ($format) { case Format::DATE: if (!$value instanceof \DateTime) { $date = new \DateTime($value); } else { $date = $value; } $value = \PHPExcel_Shared_Date::PHPToExcel($date); $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); break; case Format::DATETIME: if (!$value instanceof \DateTime) { $date = new \DateTime($value); } else { $date = $value; } $value = \PHPExcel_Shared_Date::PHPToExcel($date); $style->getNumberFormat()->setFormatCode(self::FORMAT_DATETIME); break; case Format::FLOAT2: $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); break; case Format::INTEGER: $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER); break; case Format::MONEY: case Format::MONEY2: $style->getNumberFormat()->setFormatCode(self::FORMAT_EUR); break; case Format::PCT: $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE); break; case Format::PCT2: $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00); break; case Format::STRING: $style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT); break; } $sheet->setCellValueByColumnAndRow($col, $row, $value); $sheet->duplicateStyle($style, \PHPExcel_Cell::stringFromColumnIndex($col) . $row); }
/** * Writes a row of values * * @param \PHPExcel_Worksheet $worksheet * @param array $data An array of values with column indexes as keys */ protected function writeValues(\PHPExcel_Worksheet $worksheet, array $data) { $worksheetName = $worksheet->getTitle(); $row = $this->rowIndexes[$worksheetName]; foreach ($this->labels[$worksheet->getTitle()] as $column => $label) { if (isset($data[$label])) { $worksheet->setCellValueByColumnAndRow($column, $row, $data[$label]); } } $this->rowIndexes[$worksheetName]++; }
/** * @author caochunhui@dachuwang.com * @description 用数组和地址直接生成excel文件 * 每一个数组占一个sheet */ private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '') { //下面的代码是抄的。 //set cache $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); //open excel file $write_objPHPExcel = new PHPExcel(); //下面要循环了 $sheet_cnt = 0; foreach ($arr as $item) { //用订单id.csv来命名每一个sheet $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]); //$out_sheet->setTitle($item); //row index start from 1 $row_index = 0; foreach ($item as $row) { $row_index++; //$cellIterator = $row->getCellIterator(); //$cellIterator->setIterateOnlyExistingCells(false); //column index start from 0 $column_index = -1; foreach ($row as $cell) { $column_index++; //var_dump($cell); $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell); } } $write_objPHPExcel->addSheet($out_sheet); $sheet_cnt++; } $write_objPHPExcel->removeSheetByIndex(0); //删除第一个空sheet //上面要循环了 //上面的代码是抄的 //write excel file $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel); $dir_name = dirname($out_name); if (!is_dir($dir_name)) { $res = mkdir($dir_name, 0777, TRUE); } $objWriter->save($out_name); }
protected function set_sheet_data(array $data, PHPExcel_Worksheet $sheet) { foreach ($data as $row => $columns) { foreach ($columns as $column => $value) { $sheet->setCellValueByColumnAndRow($column, $row, $value); } } }
/** * generating the worksheet * * @param UDate $lastUpdatedInDB * @param PHPExcel_Worksheet $sheet * @param array $data * @param string $preFix * @param bool $debug * * @return Array The array of images */ private static function _genSheet(UDate $lastUpdatedInDB, PHPExcel_Worksheet &$sheet, array $data, $preFix = '', $debug = false) { self::_log('-- Generating the sheets: ', __CLASS__ . '::' . __FUNCTION__, $preFix); $rowNo = 1; $titles = array_keys(self::_getRowWithDefaultValues($lastUpdatedInDB, null, $preFix, $debug)); foreach ($titles as $colNo => $colValue) { $sheet->setCellValueByColumnAndRow($colNo, $rowNo, $colValue); } $rowNo += 1; self::_log('Generated title row', '', $preFix . self::TAB); $imageFiles = array(); foreach ($data as $index => $product) { self::_log('ROW: ' . $index . ', SKU: ' . $product->getSku(), '', $preFix . self::TAB); if (!$product instanceof Product) { self::_log('SKIPPED, invalid product.', '', $preFix . self::TAB . self::TAB); continue; } $rowValue = self::_getRowWithDefaultValues($lastUpdatedInDB, $product, $preFix, $debug); $rowValues = array($rowValue); $images = ProductImage::getAllByCriteria('productId = ? and updated > ?', array($product->getId(), trim($lastUpdatedInDB))); //images self::_log('Got ' . count($images) . ' ProductImage(s) after "' . trim($lastUpdatedInDB) . '" for productID: ' . $product->getId(), '', $preFix . self::TAB); if (count($images) > 0) { foreach ($images as $index => $image) { if (!($asset = $image->getAsset()) instanceof Asset) { self::_log('No Asset found for Image Index: ' . $index, '', $preFix . self::TAB . self::TAB); continue; } if (!is_file($asset->getPath())) { self::_log('No file found: ' . $asset->getPath(), '', $preFix . self::TAB . self::TAB); continue; } $imageFiles[] = array('fileName' => $asset->getFilename(), 'filePath' => $asset->getPath()); self::_log('Added array(fileName=>' . $asset->getFilename() . ', filePath => ' . $asset->getPath() . ') to imageFiles', '', $preFix . self::TAB . self::TAB); $imageFilePath = '{{IMAGE_IMPORT_DIR}}/' . self::$_imageDirName . '/' . $asset->getFilename(); self::_log('New Image Path into the CSV("image" column):' . $imageFilePath, '', $preFix . self::TAB . self::TAB); if (intval($index) === 0) { $rowValues[0]['image'] = $imageFilePath; $rowValues[0]['small_image'] = $imageFilePath; $rowValues[0]['thumbnail'] = $imageFilePath; } else { $rowValues[0]['media_gallery'] = $rowValues[0]['media_gallery'] . ';' . $imageFilePath; self::_log('added onto media_gallery: ' . $rowValues[0]['media_gallery'], '', $preFix . self::TAB . self::TAB); } } } //start looping in the outer loop self::_log('There are ' . count($rowValues) . ' row(s) in total.', '', $preFix . self::TAB); foreach ($rowValues as $row) { foreach (array_values($row) as $colNo => $colValue) { $sheet->setCellValueByColumnAndRow($colNo, $rowNo, $colValue); } $rowNo += 1; } self::_log('ADDED.', '', $preFix . self::TAB . self::TAB); } self::_log('-- DONE', __CLASS__ . '::' . __FUNCTION__, $preFix); return $imageFiles; }
/** * 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); } } //Adicionando os valores da coluna foreach (array_keys($bimestres) as $bimestre) { foreach (array_keys($conceitos) as $conceito) { $comando = '$this->workSheet->setCellValueByColumnAndRow($coluna, ' . '$linha, $per' . $conceito . $bimestre . 'B) ->getStyleByColumnAndRow($coluna++, $linha) ->applyFromArray(//Aplicando formatação a celula 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);'; eval($comando); } } // $coluna = $colunaInicial; //Resetando o contador de colunas $linha++; //Incrementando a linha } //Definindo o texto da linha de médias $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, "MÉDIA")->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); //Adicionando as médias foreach (array_keys($bimestres) as $bimestre) { foreach (array_keys($conceitos) as $conceito) { $comando = '$this->workSheet->setCellValueByColumnAndRow($coluna, ' . '$linha, Matematica::mediaAritimeticaArray($md' . $conceito . $bimestre . 'B)) ->getStyleByColumnAndRow($coluna++, $linha) ->applyFromArray(//Aplicando formatação a celula 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);'; eval($comando); } } //Imcrementando a linha $linha++; $coluna = $colunaInicial; //Resetando o contador de colunas //----------------------------------------------------------------------------------------------------- //----------------------------------------------------------------------------------------------------- //----------------------------------------------------------------------------------------------------- //----------------------------------------------------------------------------------------------------- ////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) . " AO LONGO DOS BIMESTRES"; $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 ($conceitos as $conceito) { $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $conceito)->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 + 3, $linha); //Mesclando as colunas $coluna += 4; } $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 ($conceitos as $conceito) { foreach (array_keys($bimestres) as $bimestre) { $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $bimRomanos[$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); } } //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(); $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); } } //Adicionando os valores da coluna foreach (array_keys($conceitos) as $conceito) { foreach (array_keys($bimestres) as $bimestre) { $comando = '$this->workSheet->setCellValueByColumnAndRow($coluna, ' . '$linha, $per' . $conceito . $bimestre . 'B) ->getStyleByColumnAndRow($coluna++, $linha) ->applyFromArray(//Aplicando formatação a celula 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);'; eval($comando); } } // $coluna = $colunaInicial; //Resetando o contador de colunas $linha++; //Incrementando a linha } //Definindo o texto da linha de médias $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, "MÉDIA")->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); //Adicionando as médias foreach (array_keys($conceitos) as $conceito) { foreach (array_keys($bimestres) as $bimestre) { $comando = '$this->workSheet->setCellValueByColumnAndRow($coluna, ' . '$linha, Matematica::mediaAritimeticaArray($md' . $conceito . $bimestre . 'B)) ->getStyleByColumnAndRow($coluna++, $linha) ->applyFromArray(//Aplicando formatação a celula 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);'; eval($comando); } } //Imcrementando a linha $linha++; $coluna = $colunaInicial; //Resetando o contador de colunas // } } //Imprimindo a planuilha $this->printPlanilha("Avaliações"); }
/** * Add a new row to a {@link PHPExcel_Worksheet} based of a * {@link DataObjectInterface} * @param PHPExcel_Worksheet $sheet * @param DataObjectInterface $item * @param array $fields List of fields to include * @return PHPExcel_Worksheet */ protected function addRow(PHPExcel_Worksheet &$sheet, DataObjectInterface $item, array $fields) { $row = $sheet->getHighestRow() + 1; $col = 0; foreach ($fields as $field => $type) { $sheet->setCellValueByColumnAndRow($col, $row, $item->{$field}); $col++; } return $sheet; }