public function save() { $post = $this->_post(NULL, TRUE); try { if (is_array($post)) { $nivelAct = BalanceAlimTable::getInstance()->find($post['balance_id']); if (!$nivelAct) { $msg = "Balance no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } if ($post['id']) { $balance_mmes = $this->_getTable()->find($post['id']); if (!$balance_mmes) { $msg = "BD no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } } else { $balance_mmes = new BalanceMes(); } $balance_mmes->fromArray($post, false); $balance_mmes->set('BalanceAlim', $nivelAct); $balance_mmes->save(); $this->_jsonResponse($balance_mmes->toArray()); } } catch (Exception $exc) { log_message('error', $exc->getMessage()); log_message('error', $exc->getTraceAsString()); $this->_jsonResponse(array("msg" => "Ha ocurrido un error mientras se intentaba guardar..."), 500); } }
public function save() { $post = $this->_post(NULL, TRUE); try { if (is_array($post)) { $producto = NomProductoTable::getInstance()->find($post['producto_id']); if (!$producto) { $msg = "Producto no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } $nivelAct = BalanceAlimTable::getInstance()->find($post['balance_alim_id']); if (!$nivelAct) { $msg = "Balance no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } if ($post['id']) { $databaseModel = $this->_getTable()->find($post['id']); if (!$databaseModel) { $msg = "BD no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } } else { $databaseModel = new BaseDatos(); } $databaseModel->fromArray($post, false); $databaseModel->set('Producto', $producto); $databaseModel->set('BalanceAlim', $nivelAct); $databaseModel->save(); $this->_jsonResponse($databaseModel->toArray()); } } catch (Exception $exc) { log_message('error', $exc->getMessage()); log_message('error', $exc->getTraceAsString()); $this->_jsonResponse(array("msg" => "Ha ocurrido un error mientras se intentaba guardar BD"), 500); } }
private function generar_reporte_portada($year) { $time = $year . '%'; $headers = ProductoFuentesTable::getInstance()->productos($time); $pr1 = ProductoFuentesTable::getInstance()->proveedores_fuentes($time); $pr2 = ProductoFuentesTable::getInstance()->proveedores_fuentes2($time); $proveedores = $this->count_distinct($pr1, $pr2, 'nombre'); // Create new PHPExcel object $this->load->library('phpexcel'); // Set document properties $this->phpexcel->getProperties()->setCreator($this->session->userdata('username'))->setLastModifiedBy("Aliplan")->setTitle("Confeccionado por: Revisado por:")->setSubject("Office 2007 XLSX Report Document")->setDescription("Report document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Report result file"); // Add some data $this->phpexcel->getActiveSheet()->setCellValue('A1', 'ANALISIS DE LA CIFRA DIRECTIVA PLAN ' . $year); $this->phpexcel->getActiveSheet()->getStyle('A1')->getAlignment()->setIndent(1); $this->phpexcel->getActiveSheet()->mergeCells('A1:B1'); $this->phpexcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); // Set fills $this->phpexcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); $this->phpexcel->getActiveSheet()->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getActiveSheet()->getStyle('A2')->getFill()->getStartColor()->setARGB('FF808080'); // Set fonts $this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara'); $this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14); $this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); $this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); $this->phpexcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $this->phpexcel->getActiveSheet()->setCellValue('A3', 'PRODUCTOS'); $this->phpexcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle('A3')->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getActiveSheet()->setCellValue('A4', 'FUENTES'); $this->phpexcel->getActiveSheet()->getStyle('A4')->applyFromArray($this->styles('blackStyle')); $col = 0; $row = 5; $fuentes = NomFuentesTable::getInstance()->fuentes($time); foreach ($fuentes as $value) { $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value['nombre'], true); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $col = 1; foreach ($headers as $v) { $cantidad = 0; $arr = ProductoFuentesTable::getInstance()->cantidad($time, $v['producto_id'], $value['fuente_id']); if ($arr && $arr[0]) { $obj = $arr[0]; $cantidad = $obj['cantidad']; } $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col++, $row, $cantidad, true); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } $row++; $col = 0; } //Sumatoria de fuentes $num_fuentes = count($fuentes); for ($col_number = 1; $col_number <= count($headers); $col_number++) { $a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 5); $a_coor = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 4 + $num_fuentes); $b_coor = $b->getCoordinate(); $c = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, 4, '=SUM(' . $a_coor . ':' . $b_coor . ')', true); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } //Sumatoria de Destinos for ($col_number = 1; $col_number <= count($headers); $col_number++) { $a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 9); $a_coor = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 14); $b_coor = $b->getCoordinate(); $c = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 15); $c_coor = $c->getCoordinate(); $d = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 17); $d_coor = $d->getCoordinate(); $e = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 33); $e_coor = $e->getCoordinate(); $suma = '=' . $a_coor . '+' . $b_coor . '+' . $c_coor . '+' . $d_coor . '+' . $e_coor; $f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, 5 + $num_fuentes, $suma, true); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } $col = 0; $arr_columna_a = $this->columna_a(); $coor_nomin = 0; $coor_consumoSocial = 0; $coor_consumoInter = 0; $reserva_balance = 0; $canasta_familiar = 0; foreach ($arr_columna_a as $value) { $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row++, $value[0], true); $coor = $a->getCoordinate(); if ($value[0] === 'Nominalizados') { $coor_nomin = $row - 1; } else { if ($value[0] === 'Consumo Intermedio') { $coor_consumoInter = $row - 1; } else { if ($value[0] === 'Reserva del Balance') { $reserva_balance = $row - 1; } else { if ($value[0] === 'Canasta Familiar') { $canasta_familiar = $row - 1; } else { if ($value[0] === 'Consumo Social') { $coor_consumoSocial = $row - 1; } } } } } $this->phpexcel->getActiveSheet()->getStyle($coor)->getAlignment()->setIndent($value[1]); $this->phpexcel->getActiveSheet()->getStyle($coor)->applyFromArray($this->styles($value[2])); } //FORMULA DE NOMINALIZADOS $col = 1; foreach ($headers as $value) { $cantidad = 0; $arr = ProductoFuentesTable::getInstance()->nominalizados($time, $value['producto_id']); if ($arr && $arr[0]) { $obj = $arr[0]; $cantidad = $obj['cantidad']; } $f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col++, $coor_nomin, $cantidad, true); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } //FORMULA DE CONSUMO SOCIAL for ($col_number = 1; $col_number <= count($headers); $col_number++) { $a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $coor_consumoSocial + 1); $a_coor = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $coor_consumoSocial + 9); $b_coor = $b->getCoordinate(); $bb = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $coor_consumoSocial + 11); $bb_coor = $bb->getCoordinate(); $c = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, $coor_consumoSocial, '=SUM(' . $a_coor . ':' . $b_coor . ')+' . $bb_coor, true); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } //FORMULA DE CONSUMO INTERMEDIO $col = 1; foreach ($headers as $value) { $cantidad = 0; $arr = ProductoFuentesTable::getInstance()->consumo_intermedio($time, $value['producto_id']); if ($arr && $arr[0]) { $obj = $arr[0]; $cantidad = $obj['cantidad']; } $f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col++, $coor_consumoInter, $cantidad, true); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } //FORMULA DE RESERVA DEL BALANCE for ($col_number = 1; $col_number <= count($headers); $col_number++) { $a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 4); $a_coor = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 5 + $num_fuentes); $b_coor = $b->getCoordinate(); $prod = '=PRODUCT(' . $a_coor . '-' . $b_coor . ')'; $f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, $reserva_balance, $prod, true); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } //SUMATORIA DE Grupo Empresarial de Comercio y la G. for ($col_number = 1; $col_number <= count($headers); $col_number++) { $a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 9); $a_coor = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $canasta_familiar); $b_coor = $b->getCoordinate(); $sum = '=SUM(' . $a_coor . ':' . $b_coor . ')'; $f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, 7 + $num_fuentes, $sum, true); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } $t = 1; foreach ($proveedores as $value) { $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($t, 2, $value[0], true); $coordA = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($t + $value[1] - 1, 2); $coordB = $b->getCoordinate(); $this->phpexcel->getActiveSheet()->mergeCells($coordA . ':' . $coordB); $this->phpexcel->getActiveSheet()->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getActiveSheet()->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $t = $t + $value[1]; } $j = 1; foreach ($headers as $value) { $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($j, 3, $value['nombre_producto'], true); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90); $this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle')); $j = $j + 1; } $lastColumn_row1 = count($headers); $lastCell = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($lastColumn_row1, 1); $this->phpexcel->getActiveSheet()->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getActiveSheet()->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080'); // Set header and footer. When no different headers for odd/even are used, odd header is assumed. $this->phpexcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&RFecha &D'); $this->phpexcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPágina &P de &N'); // Set page orientation and size $this->phpexcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $this->phpexcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER); // Rename first worksheet $this->phpexcel->getActiveSheet()->setTitle('PORTADA'); //MENSUAL $i = 1; $newWorkSheet = new PHPExcel_Worksheet($this->phpexcel, 'MENSUAL'); $this->phpexcel->addSheet($newWorkSheet, $i); $organismos = BalanceAlimTable::getInstance()->organismos($time); $headers = $this->headers_basedatos($time); $balanceAlim = $this->balance_bd($time); $valores = $this->matrix_de_valores_ajuste_productos($time); $matrix = $this->create_matrix($headers, $balanceAlim, $valores); $pr1 = $this->proveedores_bd($time); $pr2 = $this->proveedores_bd2($time); $proveedores = $this->count_distinct($pr1, $pr2, 'nombre'); // Add some data $this->phpexcel->getSheet($i)->setCellValue('A1', 'BALANCE DE ALIMENTO DEL MES'); $this->phpexcel->getSheet($i)->getStyle('A1')->getAlignment()->setIndent(1); $this->phpexcel->getSheet($i)->mergeCells('A1:D1'); $this->phpexcel->getSheet($i)->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); // Set fills $this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->getStartColor()->setARGB('FF808080'); // Set fonts $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setName('Candara'); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setSize(19); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setBold(true); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); $this->phpexcel->getSheet($i)->getColumnDimension('A')->setAutoSize(true); $this->phpexcel->getSheet($i)->setCellValue('A4', 'TOTAL CAP'); $this->phpexcel->getSheet($i)->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getSheet($i)->getStyle('A4')->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle')); //NOMBRE DE LAS COLUMNAS $this->phpexcel->getSheet($i)->setCellValue('A3', 'ENTIDAD/COMEDOR'); $this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle')); $indice_organismos = array(); $x = 5; foreach ($organismos as $org) { $entityNames = BalanceAlimTable::getInstance()->entidadesPorOrganismo($time, $org['organismo_id']); $organism = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $x, $org['organismo_nombre'], true); $this->phpexcel->getSheet($i)->getStyle($organism->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($organism->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $row_org = $x; $arr_temp = array(); for ($index = 1; $index <= count($headers); $index++) { $total_coord = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $x); array_push($arr_temp, $total_coord->getCoordinate()); } array_push($indice_organismos, $arr_temp); $x = $x + 1; $total_1 = array(); foreach ($entityNames as $en) { $arr = BalanceAlimTable::getInstance()->balance_alimentacion_tabla($en['entidad_id']); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $x, $en['entidad_nombre'], true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(1); $a1 = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(1, $x); $this->phpexcel->getSheet($i)->getStyle($a1->getCoordinate())->applyFromArray($this->styles('blackStyle')); $total_cap_por_org = array(); for ($index = 1; $index <= count($headers); $index++) { $aa = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $x); $this->phpexcel->getSheet($i)->getStyle($aa->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($aa->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $next = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $x + 1); $coord1 = $next->getCoordinate(); $last = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, count($arr) + $x); $coord2 = $last->getCoordinate(); $total_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $x, '=IF(SUM(' . $coord1 . ':' . $coord2 . ')>0,0,1)', true); $total_cell = $total_coord->getCoordinate(); $this->phpexcel->getSheet($i)->getStyle($total_cell)->getNumberFormat()->setFormatCode('#,##0.000'); array_push($total_cap_por_org, $total_cell); } array_push($total_1, $total_cap_por_org); $x = $x + 1; foreach ($arr as $value) { $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $x, $value['comedor_nombre']); $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $x); $coord_a = $a->getCoordinate(); $this->phpexcel->getSheet($i)->getStyle($coord_a)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($coord_a)->getAlignment()->setIndent(2); $col = 1; foreach ($matrix[$value['id']] as $producto_id) { $result_query = BaseDatosTable::getInstance()->valor_producto_mes($value['balance_id'], $producto_id); $result = 0; if (count($result_query) > 0) { $result = $result_query[0]['valor_mes']; $ajuste = $result_query[0]['ajuste_mes'] * $result / 100; $result = $result + $ajuste; } $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $x, $result, true); $coordin = $a->getCoordinate(); $this->phpexcel->getSheet($i)->getStyle($coordin)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($coordin)->getNumberFormat()->setFormatCode('#,##0.000'); $col = $col + 1; } $x = $x + 1; } } $total = array(); foreach ($total_1[0] as $value) { array_push($total, $value); } //quitar el primer elemento de la lista array_shift($total_1); foreach ($total_1 as $arr) { for ($index = 0; $index < count($arr); $index++) { $total[$index] = $total[$index] . '+' . $arr[$index]; } } for ($index = 1; $index <= count($headers); $index++) { $sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $row_org, '=' . $total[$index - 1], true); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } } //TOTAL GENERAL $total = array(); foreach ($indice_organismos[0] as $value) { array_push($total, $value); } //quitar el primer elemento de la lista array_shift($indice_organismos); foreach ($indice_organismos as $arr) { for ($index = 0; $index < count($arr); $index++) { $total[$index] = $total[$index] . '+' . $arr[$index]; } } for ($index = 1; $index <= count($headers); $index++) { $sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, 4, '=' . $total[$index - 1], true); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } $coord = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(1, 4); $this->phpexcel->getSheet($i)->getStyle($coord->getCoordinate())->applyFromArray($this->styles('blackStyle')); $t = 1; foreach ($proveedores as $value) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($t, 2, $value[0], true); $coordA = $a->getCoordinate(); $b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($t + $value[1] - 1, 2); $coordB = $b->getCoordinate(); $this->phpexcel->getSheet($i)->mergeCells($coordA . ':' . $coordB); $this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $t = $t + $value[1]; } $j = 1; foreach ($headers as $value) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, 3, $value['nombre_producto'], true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle')); $j = $j + 1; } $lastColumn_row1 = count($headers); $lastCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($lastColumn_row1, 1); $this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080'); // Set header and footer. When no different headers for odd/even are used, odd header is assumed. $this->phpexcel->getSheet($i)->getHeaderFooter()->setOddHeader('&RFecha &D'); $this->phpexcel->getSheet($i)->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPage &P of &N'); // Set page orientation and size $this->phpexcel->getSheet($i)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $this->phpexcel->getSheet($i)->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER); //Sumatoria de TOTAL CAP for ($col_number = 1; $col_number <= count($headers); $col_number++) { $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($col_number, 4); $a_coor = $a->getCoordinate(); $suma = '=MENSUAL!' . $a_coor; $f = $this->phpexcel->getSheet(0)->setCellValueByColumnAndRow($col_number, 6 + $num_fuentes, $suma, true); $this->phpexcel->getSheet(0)->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet(0)->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } $i = $i + 1; //EVENTOS $newWorkSheet = new PHPExcel_Worksheet($this->phpexcel, 'EVENTOS'); $this->phpexcel->addSheet($newWorkSheet, $i); $headers = $this->headers_eventos($time); $items3 = $this->entidades_eventos($time); $org = $this->organismos_eventos($time); $org2 = $this->organismos_eventos2($time); $organismos = $this->count_distinct($org, $org2, 'org_nombre'); $pr1 = $this->proveedores_eventos($time); $pr2 = $this->proveedores_eventos2($time); $proveedores = $this->count_distinct($pr1, $pr2, 'nombre'); $items4 = $this->matrix_de_valores_eventos($time); $arr = $this->create_matrix($headers, $items3, $items4); $this->phpexcel->getSheet($i)->setCellValue('B1', 'EVENTOS'); $this->phpexcel->getSheet($i)->mergeCells('B1:C1'); $this->phpexcel->getSheet($i)->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); // Set fonts $this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setName('Candara'); $this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setSize(20); $this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setBold(true); $this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); // $this->phpexcel->getSheet($i)->setCellValue('E1', PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y')))); // $this->phpexcel->getSheet($i)->getStyle('E1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15); $this->phpexcel->getSheet($i)->setCellValue('B3', 'ORGANISMO/ENTIDAD'); $this->phpexcel->getSheet($i)->setCellValue('A3', 'REEUP'); $this->phpexcel->getSheet($i)->getColumnDimension('B')->setAutoSize(true); $this->phpexcel->getSheet($i)->setCellValue('B4', 'RESERVA DE BALANCE'); $this->phpexcel->getSheet($i)->getStyle('A4')->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle('B4')->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getSheet($i)->getStyle('B3')->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getSheet($i)->getStyle('A2:B2')->applyFromArray($this->styles('headerStyle')); $reserva = array(); $zz = 5; foreach ($organismos as $value) { $firstCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($firstCell->getCoordinate())->applyFromArray($this->styles('borderStyle')); $coor = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, $value[0], true); $this->phpexcel->getSheet($i)->getStyle($coor->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $this->phpexcel->getSheet($i)->getStyle($coor->getCoordinate())->applyFromArray($this->styles('blackStyle')); $total_cap_por_org = array(); for ($index = 2; $index <= count($headers) + 1; $index++) { $sum_coord = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $zz); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); array_push($total_cap_por_org, $sum_coord->getCoordinate()); } array_push($reserva, $total_cap_por_org); $row = $zz; $zz = $zz + 1; $entidades = $this->entidades_por_organismo_eventos($time, $value[0]); $subtotal = array(); foreach ($entidades as $value2) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, $value2['nombre_entidad'], true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(1); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $b = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $zz, $value2['reeup'], true); $this->phpexcel->getSheet($i)->getStyle($b->getCoordinate())->applyFromArray($this->styles('borderStyle')); //sumatoria almuerzo+comida+merienda $subtotal_fila = array(); $x = 2; foreach ($headers as $val) { $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 1); $a_coor = $a->getCoordinate(); $b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 3); $b_coor = $b->getCoordinate(); $sumatoria = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz, '=SUM(' . $a_coor . ':' . $b_coor . ')', true); $this->phpexcel->getSheet($i)->getStyle($sumatoria->getCoordinate())->applyFromArray($this->styles('borderStyle')); array_push($subtotal_fila, $sumatoria->getCoordinate()); $x = $x + 1; } array_push($subtotal, $subtotal_fila); $zz = $zz + 1; //Almuerzo $first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle')); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Almuerzo", true); $almuerzo = $a->getCoordinate(); $this->phpexcel->getSheet($i)->getStyle($almuerzo)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($almuerzo)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $this->phpexcel->getSheet($i)->getStyle($almuerzo)->getAlignment()->setIndent(2); $x = 2; foreach ($headers as $val) { $bd = BaseDatosTable::getInstance()->eventos($year . '%', $value2['entidad_id'], $val['producto_id']); foreach ($bd as $bd_iter) { $alm = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz, $bd_iter['almuerzo'], true); //-----Agregar Comentario------- $alm_coord = $alm->getCoordinate(); $this->phpexcel->getSheet($i)->getComment($alm_coord)->setAuthor('PHPExcel'); $objCommentRichText = $this->phpexcel->getSheet($i)->getComment($alm_coord)->getText()->createTextRun('Fórmula:'); $objCommentRichText->getFont()->setBold(true); $this->phpexcel->getSheet($i)->getComment($alm_coord)->getText()->createTextRun("\r\n"); $this->phpexcel->getSheet($i)->getComment($alm_coord)->getText()->createTextRun('Se multiplica el ' . 'valor del almuerzo(Nivel de actividad) del evento por el valor del producto(Base de datos).'); //----------------------------- $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz + 1, $bd_iter['comida']); $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz + 2, $bd_iter['merienda']); } $celda = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz); $this->phpexcel->getSheet($i)->getStyle($celda->getCoordinate())->applyFromArray($this->styles('borderStyle')); $celda = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 1); $this->phpexcel->getSheet($i)->getStyle($celda->getCoordinate())->applyFromArray($this->styles('borderStyle')); $celda = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 2); $this->phpexcel->getSheet($i)->getStyle($celda->getCoordinate())->applyFromArray($this->styles('borderStyle')); $x = $x + 1; } $zz = $zz + 1; //Comida $first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle')); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Comida", true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); $zz = $zz + 1; //Merienda $first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle')); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Merienda", true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); $zz = $zz + 1; //Conceptos $ev = EventosTable::getInstance()->eventos($year . '%', $value2['entidad_id']); foreach ($ev as $iter) { $first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle')); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, $iter['concepto'], true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); //Valor de los conceptos $x = 2; foreach ($headers as $val) { $event = EventosTable::getInstance()->eventos_por_producto($year . '%', $value2['entidad_id'], $val['producto_id']); foreach ($event as $event_iter) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz, $event_iter['cantidad'], true); } $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $x = $x + 1; } $zz = $zz + 1; } //Ajustes $first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle')); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Ajustes", true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); $x = 2; foreach ($headers as $val) { $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $x = $x + 1; } $zz = $zz + 1; //% $first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz); $this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle')); $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "%", true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); $x = 2; foreach ($headers as $val) { $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $x = $x + 1; } $zz = $zz + 1; } //Subtotal por organismo $total = array(); foreach ($subtotal[0] as $value) { array_push($total, $value); } //quitar el primer elemento de la lista array_shift($subtotal); foreach ($subtotal as $arr) { for ($index = 0; $index < count($arr); $index++) { $total[$index] = $total[$index] . '+' . $arr[$index]; } } for ($index = 2; $index <= count($headers) + 1; $index++) { $sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $row, '=' . $total[$index - 2], true); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle')); } } $t = 2; foreach ($proveedores as $value) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($t, 2, $value[0], true); $coordA = $a->getCoordinate(); $b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($t + $value[1] - 1, 2); $coordB = $b->getCoordinate(); $this->phpexcel->getSheet($i)->mergeCells($coordA . ':' . $coordB); $this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $t = $t + $value[1]; } $j = 2; foreach ($headers as $value) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, 3, $value['nombre_producto'], true); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle')); $j = $j + 1; } //$this->phpexcel->getSheet($i)->fromArray($arr, null, 'B5', false); $this->phpexcel->getSheet($i)->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); //Crear formula para el total general $row_four = 4; $total = array(); foreach ($reserva[0] as $value) { array_push($total, $value); } //quitar el primer elemento de la lista array_shift($reserva); foreach ($reserva as $arr) { for ($index = 0; $index < count($arr); $index++) { $total[$index] = $total[$index] . '+' . $arr[$index]; } } for ($index = 2; $index <= count($headers) + 1; $index++) { $sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $row_four, '=' . $total[$index - 2], true); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); $this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle')); } // Set fills $lastColumn_row1 = count($headers) + 1; $lastCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($lastColumn_row1, 1); $this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080'); // Set header and footer. When no different headers for odd/even are used, odd header is assumed. $this->phpexcel->getSheet($i)->getHeaderFooter()->setOddHeader('&RFecha &D'); $this->phpexcel->getSheet($i)->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPage &P of &N'); // Set page orientation and size $this->phpexcel->getSheet($i)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $this->phpexcel->getSheet($i)->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER); $i = $i + 1; //ORGANISMOS NOMINALIZADOS $newWorkSheet = new PHPExcel_Worksheet($this->phpexcel, 'NOMINALIZADOS Y OTROS'); $this->phpexcel->addSheet($newWorkSheet, $i); $headers = $this->headers_nominalizados($time); $items3 = $this->organismos_nominalizados($time); $pr1 = $this->proveedores_nominalizados($time); $pr2 = $this->proveedores_nominalizados2($time); $proveedores = $this->count_distinct($pr1, $pr2, 'nombre'); $items4 = $this->data_nominalizados($time); $arr = $this->create_matrix($headers, $items3, $items4); // Add some data $this->phpexcel->getSheet($i)->setCellValue('A1', 'Organismos Nominalizados'); $this->phpexcel->getSheet($i)->mergeCells('A1:B1'); $this->phpexcel->getSheet($i)->getStyle('A1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); // Set fonts $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setName('Candara'); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setSize(20); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setBold(true); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); $this->phpexcel->getSheet($i)->setCellValue('A3', 'Nombre'); $this->phpexcel->getSheet($i)->getColumnDimension('A')->setAutoSize(true); $this->phpexcel->getSheet($i)->setCellValue('A4', 'Total'); $this->phpexcel->getSheet($i)->getStyle('A4')->applyFromArray($this->styles('blackStyle')); for ($index = 1; $index <= count($headers); $index++) { $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, 4); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle')); $next = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, 5); $coord1 = $next->getCoordinate(); $last = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, count($items3) + 4); $coord2 = $last->getCoordinate(); $formula = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, 4, '=SUM(' . $coord1 . ':' . $coord2 . ')', true); $this->phpexcel->getSheet($i)->getStyle($formula->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); } $this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle')); $iii = 5; foreach ($items3 as $value) { $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $iii, $value['nombre_entidad']); $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $iii); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $iii = $iii + 1; } $t = 1; foreach ($proveedores as $value) { $a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($t, 2, $value[0], true); $coordA = $a->getCoordinate(); $b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($t + $value[1] - 1, 2); $coordB = $b->getCoordinate(); $this->phpexcel->getSheet($i)->mergeCells($coordA . ':' . $coordB); $this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle')); $this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $t = $t + $value[1]; } $j = 1; foreach ($headers as $value) { $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, 3, $value['nombre_producto']); $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($j, 3); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle')); $j = $j + 1; } $y = 5; foreach ($arr as $v) { $x = 1; foreach ($v as $v2) { $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $y, $v2); $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $y); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); $x = $x + 1; } $y = $y + 1; } //MERCADOS $mercados = NomMercadoTable::getInstance()->mercados($time); foreach ($mercados as $value) { $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $iii, $value['nombre']); $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $iii); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2); $j = 1; foreach ($headers as $v) { $cantidad = 0; $arr = ProductoMercadoTable::getInstance()->cantidad($time, $v['producto_id'], $value['mercado_id']); if ($arr && $arr[0]) { $obj = $arr[0]; $cantidad = $obj['cantidad']; } $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, $iii, $cantidad); $a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($j, $iii); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000'); $j = $j + 1; } $iii = $iii + 1; } //$this->phpexcel->getSheet($i)->fromArray($arr, null, 'B5', false); $this->phpexcel->getSheet($i)->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); // Set fills $lastColumn_row1 = count($headers); $lastCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($lastColumn_row1, 1); $this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080'); $this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->getStartColor()->setARGB('FF808080'); // Set header and footer. When no different headers for odd/even are used, odd header is assumed. $this->phpexcel->getSheet($i)->getHeaderFooter()->setOddHeader('&RFecha &D'); $this->phpexcel->getSheet($i)->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPage &P of &N'); // Set page orientation and size $this->phpexcel->getSheet($i)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $this->phpexcel->getSheet($i)->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $this->phpexcel->setActiveSheetIndex(1); return $this->phpexcel; }