public function getExcel($lista_id) { $nomina = VistaNomina::select('empleado as Empleado', 'salario_diario as SD', 'salario_semanal as Sueldo', 'dias_pago as DT', 'nomina_ss as SS', 'h_extra as H.E.', 'p_dominical as PD', 'otras_percepciones as OP', 'bono_mtto as BM', DB::raw('if(infonavit > 0, infonavit * -1, 0 ) as INF'), DB::raw('if(abono_prestamo > 0, abono_prestamo * -1, 0 ) as PRE'), 'nomina as Total', DB::raw('if(ss > 0, " ", 0 ) as firma'))->where('lista_id', '=', $lista_id)->get(); $lista = Lista::find($lista_id); $fecha = 'Nomina ' . date("d/M/Y", strtotime($lista->fecha_inicio)) . ' al ' . date("d/M/Y", strtotime($lista->fecha_fin)); $empleados_no = VistaNomina::where('lista_id', '=', $lista_id)->count(); $incidencias = VistaNomina::select('empleado as Empleado', 'departamento as Depto.', DB::raw('sa+do+lu+ma+mi+ju+vi as Días'), DB::raw('if(sa > 0, "Ok", "" ) as Sab'), DB::raw('if(do > 0, "Ok", "" ) as Dom'), DB::raw('if(lu > 0, "Ok", "" ) as Lun'), DB::raw('if(ma > 0, "Ok", "" ) as Mar'), DB::raw('if(mi > 0, "Ok", "" ) as Mie'), DB::raw('if(ju > 0, "Ok", "" ) as Jue'), DB::raw('if(vi > 0, "Ok", "" ) as Vie'), 'prima_dominical as PD', 'hora_extra as HE', 'observaciones as Observaciones')->where('lista_id', '=', $lista_id)->get(); ob_end_clean(); ob_start(); //At the very top of your program (first line) Excel::create($fecha, function ($excel) use($nomina, $incidencias, $empleados_no, $fecha) { // Set the title $excel->setTitle('Relacion de incidencias semanal'); // Chain the setters $excel->setCreator('By MDL')->setCompany('Parque Funeral Guadalule'); // Call them separately $excel->setDescription('A demonstration to change the file properties'); $excel->sheet('Nomina', function ($sheet) use($nomina, $empleados_no, $fecha) { // Manipulate first row //DATOS GENERALES DE LA NOMINA $sheet->row(1, array('INVERSIONES PFG, S.A. DE C.V. ' . $fecha)); //ESPACIO PARA ENCABEZADO $sheet->mergeCells('A1:M1'); //FORMATOS DE LAS COL $sheet->setColumnFormat(array('B' => '0.00', 'C' => '0.00', 'E' => '0.00', 'F' => '0.00', 'G' => '0.00', 'H' => '0.00', 'I' => '0.00', 'J' => '0.00', 'K' => '0.00', 'L' => '0.00')); //formato de celdas TITULOS en negritas $sheet->cells('A1:O1', function ($cells) { //primer renglon en negritas $cells->setFontWeight('bold'); // Set font size $cells->setFontSize(14); // Set alignment to center $cells->setAlignment('center'); // Set vertical alignment to middle $cells->setValignment('middle'); }); //formato de celdas encabezado en negritas, CONGELA LA PRIMER COLUMNA Y LOS RENGLONES HASTA EL 3 $sheet->freezePaneByColumnAndRow(1, 3)->cells('A2:M2', function ($cells) { //primer renglon en negritas $cells->setFontWeight('bold'); // Set font size $cells->setFontSize(12); // Set alignment to center $cells->setAlignment('center'); // Set vertical alignment to middle $cells->setValignment('middle'); // Set black background $cells->setBackground('#000000'); // Set with font color $cells->setFontColor('#ffffff'); }); //formato y alineacion de el contenido $sheet->cells('C1:L' . ($empleados_no + 2), function ($cells) { // Set alignment to center $cells->setAlignment('center'); // Set vertical alignment to middle $cells->setValignment('middle'); }); //primer renglon con borde $sheet->setBorder('A2:M' . ($empleados_no + 2), 'thin'); for ($i = 2; $i < $empleados_no + 3; $i++) { $sheet->setHeight($i, 25); } // Set width for multiple cells $sheet->setWidth(array('A' => 35, 'C' => 10, 'D' => 5, 'E' => 10, 'L' => 10, 'M' => 25)); $sheet->setOrientation('landscape'); $sheet->fromArray($nomina, null, 'A2', false); }); //segunda hoja de movimientos $excel->sheet('Movimientos', function ($sheet) use($incidencias, $fecha, $empleados_no) { // Manipulate first row //DATOS GENERALES DE LA NOMINA $sheet->row(1, array('INVERSIONES PFG, S.A. DE C.V. ' . " OBSERVACIONES DE " . $fecha)); //ESPACIO PARA ENCABEZADO $sheet->mergeCells('A1:M1'); //formato de celdas TITULOS en negritas $sheet->cells('A1:O1', function ($cells) { //primer renglon en negritas $cells->setFontWeight('bold'); // Set font size $cells->setFontSize(14); // Set alignment to center $cells->setAlignment('center'); // Set vertical alignment to middle $cells->setValignment('middle'); }); //formato de celdas encabezado en negritas, CONGELA LA PRIMER COLUMNA Y LOS RENGLONES HASTA EL 3 $sheet->freezePaneByColumnAndRow(1, 3)->cells('A2:M2', function ($cells) { //primer renglon en negritas $cells->setFontWeight('bold'); // Set font size $cells->setFontSize(12); // Set alignment to center $cells->setAlignment('center'); // Set vertical alignment to middle $cells->setValignment('middle'); // Set black background $cells->setBackground('#000000'); // Set with font color $cells->setFontColor('#ffffff'); }); $sheet->setOrientation('landscape'); $sheet->fromArray($incidencias, null, 'A2', true); //COLOR DE LOS DOMINGOS $sheet->cells('E3:E' . ($empleados_no + 2), function ($cells) { $cells->setBackground('#F7FE2E'); $cells->setFontWeight('bold'); }); }); })->export('xlsx'); ob_flush(); }