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;
}
