Report showing all the fields in BD, up to NULL fields

Question

Report showing all the fields in BD, up to NULL fields

Navigation

#1 by (0 votes)

0

This is my code where an export is made of all my data stored in my DB (reports), but in my BD I have null fields and when downloading the excel report I download all the data that are stored in the BD and I just need you to show in that report only the records that I need and not show me the lines where my fields are null

This is how my BD is modeled:

id_Control Primaria  
nombre  
ap_paterno  
ap_materno      
NSS 
CURP    
RFC     
fecha_nac   
id_puesto FK  
idpuesto_semanal FK NULL  
id_area FK 
idarea_semanal FK  NULL
idctg_turno FK  
idctg_empresa FK    
id_nomina FK

<?php

 require 'Classes/PHPExcel.php';
 require("connect_db.php");
 $sql = "SELECT id_Control, nombre, ap_paterno, ap_materno, NSS, CURP, RFC, fecha_nac, idpuesto_semanal, idarea_semanal, idctg_turno, idctg_empresa FROM trabajador";
 $resultado = $mysqli->query($sql);
 
 $fila = 2;
 
 $objPHPExcel = new PHPExcel ();
 $objPHPExcel->getProperties()->setCreator("Codigos de programacion")->
 setDescription("Reporte de Trabajador");
 
 $objPHPExcel->setActiveSheetIndex(0);
 $objPHPExcel->getActiveSheet()->setTitle("Reporte_Trabajador");
 
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
 
 $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Numero de Control');
 $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Nombre');
 $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Apellido Paterno');
 $objPHPExcel->getActiveSheet()->setCellValue('D1', 'Apellido Materno');
 $objPHPExcel->getActiveSheet()->setCellValue('E1', 'NSS');
 $objPHPExcel->getActiveSheet()->setCellValue('F1', 'CURP');
 $objPHPExcel->getActiveSheet()->setCellValue('G1', 'RFC');
 $objPHPExcel->getActiveSheet()->setCellValue('H1', 'Fecha de Nacimiento');
 $objPHPExcel->getActiveSheet()->setCellValue('I1', 'Puesto');
 $objPHPExcel->getActiveSheet()->setCellValue('J1', 'Area');
 $objPHPExcel->getActiveSheet()->setCellValue('K1', 'Turno');
 $objPHPExcel->getActiveSheet()->setCellValue('L1', 'Empresa');
 
 
 while($row = $resultado->fetch_assoc())
 {
	 
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $row['id_Control'] ); 
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $row['nombre'] ); 
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $row['ap_paterno'] );
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $row['ap_materno'] );
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $row['NSS'] );
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $row['CURP'] );
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $row['RFC'] );
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, $row['fecha_nac'] );
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, $row['idpuesto_semanal'] );
	$objPHPExcel->getActiveSheet()->setCellValue('J'.$fila, $row['idarea_semanal'] );
	$objPHPExcel->getActiveSheet()->setCellValue('K'.$fila, $row['idctg_turno'] );
	$objPHPExcel->getActiveSheet()->setCellValue('L'.$fila, $row['idctg_empresa'] ); 
	
	$fila++;
 }


 header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 header('Content-Disposition: attechment;filename="Reporte_Trabajador_Semanal.xlsx"');
 header('Cache-Control: max-age=0');
 
 $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
 $objWriter->save('php://output');
 
 
?>

html php excel phpexcel

asked by Carlos 21.05.2018 в 14:27

source

1 answer

meter html in XSL What is the solution to all present, past and future NullPointerException errors?

score 0 · Accepted Answer

If you mean the complete record this null, then you have to change your query.

This is your query starts:

 $sql = "SELECT id_Control, nombre, ap_paterno, ap_materno, NSS, CURP, 
 RFC, fecha_nac, 
 idpuesto_semanal, idarea_semanal, idctg_turno, idctg_empresa FROM 
 trabajador"

Example if you do not want those whose name is null would be like this:

 $sql = "SELECT id_Control, nombre, ap_paterno, ap_materno, NSS, CURP, 
 RFC, fecha_nac, 
 idpuesto_semanal,
 idarea_semanal, idctg_turno, idctg_empresa 
 FROM trabajador **where nombre is not null**"

And so for each field, this will bypass the entire record if it has the null field.