Problem with Worksheet.php PHPEXCEL

Question

Problem with Worksheet.php PHPEXCEL

Navigation

0

I have been using this tool to generate excel files, and it has worked excellently, but when creating a new excel it marks me an error and I can not find a solution, I leave here my code and the code of the Worksheet.

------------ Code that I use for the creation of the excel -----------------------

<?php
if (PHP_SAPI == 'cli')
	die('Exclusivo para navegador WEB');

/** Incluye PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
// Crear nuevo objeto PHPExcel
$objPHPExcel = new PHPExcel();

// Propiedades del documento
$objPHPExcel->getProperties()->setCreator("CFE")
							 ->setLastModifiedBy("CFE")
							 ->setTitle("AUTORIZACIONES EMITIDAS")
							 ->setSubject("EMITIDAS")
							 ->setDescription("AUTORIZACIONES EMITIDAS")
							 ->setKeywords("")
							 ->setCategory("");

$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A12:A14');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B12:B14');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C12:D13');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E12:D14');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('F12:F14');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G12:G14');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('H12:H14');


$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A12', 'NO. DE PREDIO')
            ->setCellValue('B12', "PROPIETARIO")
            ->setCellValue('C12', 'KILOMETRAJE')
            ->setCellValue('C14', 'DE')
            ->setCellValue('D14', 'AL')
            ->setCellValue('E12', 'LONGITUD')
            ->setCellValue('F12', 'SUPERFICIE ADI')
            ->setCellValue('G12', 'NO. AUTORIZACION')
            ->setCellValue('H12', 'FECHA AUTORIZACION')
            ->setCellValue('E2', 'AUTORIZACIONES EMITIDAS')
            ->setCellValue('D4', 'DEL')
            ->setCellValue('G4', 'AL');



			
// Fuente de la primera fila en negrita
$boldArray = array('font' => array('bold' => true,),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),);



$objPHPExcel->getActiveSheet()->getStyle('A12:H14')->applyFromArray
(
	array('fill' =>
		array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' =>
		array('rgb' => 'B2FEF1')
		)
	)
 
);

$objPHPExcel->getActiveSheet()->getStyle('A15:H15')->applyFromArray
(
	array('fill' =>
		array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' =>
		array('rgb' => '7E8281')
		)
	)
 
);
$boldArray3 = array(
	'font' => array('bold' => true,
	'size'  => 12,
	'name'  => 'Swis721 BlkEx BT',
	'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT),),

	);



$objPHPExcel->getActiveSheet()->getStyle('A12:H14')->applyFromArray($boldArray);
$objPHPExcel->getActiveSheet()->getStyle('A5:A7')->applyFromArray($boldArray3);


$objPHPExcel->getActiveSheet()->getStyle("A12:H14")->applyFromArray
(
    array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000')
            )
        )
    )
);

$objDrawing = new PHPExcel_Worksheet_Drawing();
                    $objDrawing->setName('CFE');
                    $objDrawing->setDescription('CFE');
                    $img = '../img/cfe.jpg'; 
                    $objDrawing->setPath($img);
                    $objDrawing->setOffsetX(0);
                    $objDrawing->setOffsetY(300);
                    $objDrawing->setCoordinates('A1');
                    $objDrawing->setHeight(65); 
                    $objDrawing->setWorksheet($objPHPExcel->setActiveSheetIndex());        
	
//Ancho de las columnas

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);		
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);

					
require("../conexion.php");

$id=$_POST['obra'];
$del=$_POST['del'];
$al=$_POST['al'];

  $checa="SELECT *
		  FROM ttramite as ttra
		  INNER JOIN tpredio as tpre ON ttra.TTRA_IDPRE=tpre.TPRE_IDPRE
		  INNER JOIN tgestoria as tges ON tges.TGES_IDPRE=tpre.TPRE_IDPRE
		  INNER JOIN tavaluo as tava ON tava.TAVA_IDPRE=tpre.TPRE_IDPRE
          INNER JOIN tobra as tob ON tpre.TPRE_IDOBR=tob.TOBR_IDOBR 
		  WHERE tob.TOBR_IDOBR='$id'
          GROUP by ttra.TTRA_IDTRA";
$resultadoc = $mysqli->query($checa);
if($resultadoc->num_rows>0){

	$sql="SELECT *
		  FROM ttramite as ttra
		  INNER JOIN tpredio as tpre ON ttra.TTRA_IDPRE=tpre.TPRE_IDPRE
		  INNER JOIN tgestoria as tges ON tges.TGES_IDPRE=tpre.TPRE_IDPRE
		  INNER JOIN tavaluo as tava ON tava.TAVA_IDPRE=tpre.TPRE_IDPRE
          INNER JOIN tobra as tob ON tpre.TPRE_IDOBR=tob.TOBR_IDOBR 
		  WHERE tob.TOBR_IDOBR='$id' AND ttra.TTRA_FAUTP BETWEEN '$del' AND '$al'
          GROUP by ttra.TTRA_IDTRA
          ORDER by tpre.TPRE_IDPRE";

	$resultado = $mysqli->query($sql);
	$cel=16;
   while($row2=$resultado->fetch_array()){
		$cel2=4;
			$dd="E".$cel2;
			$ga="H".$cel2;
		$cel2+=1;
			$p="A".$cel2;
		$cel2+=1;			
			$q="A".$cel2;
		$cel2+=1;			
			$r="A".$cel2;

		$noobra=$row2['TOBR_NOMBR'];
		$clave=$row2['TOBR_IDOBR'];
		$voltaje=$row2['TOBR_VOLTA'];
		$circuito=$row2['TOBR_NCIRC'];
		$kilometraje=$row2['TOBR_KILOM'];
		$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue($dd, $del)
            ->setCellValue($ga, $al)
            ->setCellValue($p, "  OBRA:  ".$noobra)
            ->setCellValue($q, "  CLAVE:  " .$clave)
            ->setCellValue($r, "  Caracteristicas:  voltaje ".$voltaje."/ km ".$kilometraje."/ Circuito: ".$circuito);


	

	//Numero de fila donde empezara a crear  el reporte


		$predio=$row2['TPRE_NPRED'];
		$propietario=$row2['TPRO_NOMBR'];
		$inicial=$row2['TPRE_KMINI'];
		$final=$row2['TPRE_KMFIN'];
		$longitud=$row2['TPRE_LONGI'];
		$adi=$row2['TPRE_SUADI'];
		$noauto=$row2['TTRA_NOAUT'];
		$fauto=$row2['TTRA_FAUTP'];


	
			$a="A".$cel;
			$b="B".$cel;
			$c="C".$cel;
			$d="D".$cel;
			$e="E".$cel;
			$f="F".$cel;
			$g="G".$cel;
			$h="H".$cel;


			// Agregar datos
			$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue($a, $predio)
            ->setCellValue($b, $propietario)
            ->setCellValue($c, $inicial)
            ->setCellValue($d, $final)
            ->setCellValue($e, $longitud)
            ->setCellValue($f, $adi)
            ->setCellValue($g, $noauto)
            ->setCellValue($h, $fauto);

$cel+=1;

	}}
else{
	$sqlelse="SELECT * FROM  tobra WHERE TOBR_IDOBR='$id'";

	$resultadoelse = $mysqli->query($sqlelse);

	if($rowelse=$resultadoelse->fetch_array()){
		$cel2=4;
			$dd="E".$cel2;
			$ga="H".$cel2;
		$cel2+=1;
			$p="A".$cel2;
		$cel2+=1;			
			$q="A".$cel2;
		$cel2+=1;			
			$r="A".$cel2;

		$noobra=$row2['TOBR_NOMBR'];
		$clave=$row2['TOBR_IDOBR'];
		$voltaje=$row2['TOBR_VOLTA'];
		$circuito=$row2['TOBR_NCIRC'];
		$kilometraje=$row2['TOBR_KILOM'];
		$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue($dd, $del)
            ->setCellValue($ga, $al)
            ->setCellValue($p, "  OBRA:  ".$noobra)
            ->setCellValue($q, "  CLAVE:  " .$clave)
            ->setCellValue($r, "  Caracteristicas:  voltaje ".$voltaje."/ km ".$kilometraje."/ Circuito: ".$circuito);}
	$cel=16;
	$h="H".$cel;
}
/*Fin extracion de datos MYSQL*/
$rango="A16:$h";

$styleArray = array('font' => array( 'name' => 'Arial','size' => 10), array('bold' => true,),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
'borders'=>array('allborders'=>array('style'=> PHPExcel_Style_Border::BORDER_THIN,'color'=>array('argb' => 'FFF'))));


$objPHPExcel->getActiveSheet()->getStyle($rango)->applyFromArray($styleArray);

// Cambiar el nombre de hoja de cálculo
$objPHPExcel->getActiveSheet()->setTitle('Autorizaciones emitidas');


// Establecer índice de hoja activa a la primera hoja , por lo que Excel abre esto como la primera hoja
$objPHPExcel->setActiveSheetIndex(0);


// Redirigir la salida al navegador web de un cliente ( Excel5 )
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Autorizaciones Emitidas  obra  '.$id.'.xls"');
header('Cache-Control: max-age=0');
// Si usted está sirviendo a IE 9 , a continuación, puede ser necesaria la siguiente
header('Cache-Control: max-age=1');

// Si usted está sirviendo a IE a través de SSL , a continuación, puede ser necesaria la siguiente
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

This is the error Notice: Undefined offset: 0 in C: \ xampp \ htdocs \ SICOPRI \ reports \ Classes \ PHPExcel \ Worksheet.php on line 1429

	public function mergeCells($pRange = 'A1:A1')
	{
		// Uppercase coordinate
		$pRange = strtoupper($pRange);

		if (strpos($pRange,':') !== false) {
			$this->_mergeCells[$pRange] = $pRange;

			// make sure cells are created

			// get the cells in the range
			$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);

			// create upper left cell if it does not already exist
			$upperLeft = $aReferences[0]; <-------Linea que marca error
			if (!$this->cellExists($upperLeft)) {
				$this->getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
			}

			// create or blank out the rest of the cells in the range
			$count = count($aReferences);
			for ($i = 1; $i < $count; $i++) {
				$this->getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
			}

		} else {
			throw new Exception('Merge must be set on a range of cells.');
		}

		return $this;
	}

mysql php phpexcel

asked by Erik Raúl González Páez 15.10.2018 в 19:23

source

0 answers

Create a view from the values of a field Print Graphics JasperReport