<?php
session_start();
require("conexion.php");
    $conexion = new mysqli('localhost', 'reasap_admin_cuad', '!Cuadropolis2022!','reasap_PruebasCuadropolis');
    
    //$conexion = new mysqli('localhost','root','root','SistemaMP');
	if (mysqli_connect_errno()) {
    	printf("La conexión con el servidor de base de datos falló: %s\n", mysqli_connect_error());
    	exit();
	}
	$desde = $_GET['desde'];
	$hasta = $_GET['hasta'];
    $nom = $_SESSION['nom_admin'];
    $iduser = $_SESSION['id_user'];
    $suc = $_SESSION['IdSuc'];
    $sucbus = $_GET['sucbus'];

    if($sucbus == 0){
        $consulta = "SELECT V.Serie,V.IdVenta AS 'Folio',IF(V.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', V.Largo,V.Ancho,IF(V.Umed =1, 'CM','IN') AS 'Unidad', V.CodProd AS 'Producto',V.PrecioMold AS 'Moldura', V.cmold AS 'Costo Moldura', V.PrecioCrisN As 'Cristal', V.ccn AS 'Costo Cristal', V.PrecioCrisA AS 'Cristal Anti', V.cca As 'Costo Cristal Anti', V.PrecioMl AS 'Maria Luisa', V.cml As 'Costo ML', V.PrecioRes AS 'Respaldo', V.cr As 'Costo Respaldo', V.PrecioPapel AS 'Papel Fotografico', V.cpap AS 'Costo Papel F.', V.PrecioText AS 'Textura', V.ctext AS 'Costo Textura', V.PrecioMaq AS 'Maquila', V.PrecioCanva As 'Canva', V.ccan AS 'Costo Canva', V.PrecioAcrilico AS 'Acrilico', V.cacr AS 'Costo Acrilico', V.PrecioDobleCara AS 'Doble cara', V.cdc AS 'Costo Doble cara',V.PrecioTotal AS 'Precio total', V.ctotal AS 'Costo Total',V.CrisN,V.CrisA,V.Ml,V.Res,V.PapF,V.Text,V.Canva,V.Acrilico,V.DobleCara,V.TiraC,V.PTiraC,V.CTiraC,V.Fecha,V.Cantidad FROM VentasDetalle V
            Where V.Activo = 1 AND V.Serie != 'COT-' AND V.Fecha BETWEEN '$desde' AND '$hasta' ORDER BY V.Fecha,V.Serie,V.IdVenta ASC";
    }else if($sucbus != 0){
        $consulta = "SELECT V.Serie,V.IdVenta AS 'Folio',IF(V.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', V.Largo,V.Ancho,IF(V.Umed =1, 'CM','IN') AS 'Unidad', V.CodProd AS 'Producto',V.PrecioMold AS 'Moldura', V.cmold AS 'Costo Moldura', V.PrecioCrisN As 'Cristal', V.ccn AS 'Costo Cristal', V.PrecioCrisA AS 'Cristal Anti', V.cca As 'Costo Cristal Anti', V.PrecioMl AS 'Maria Luisa', V.cml As 'Costo ML', V.PrecioRes AS 'Respaldo', V.cr As 'Costo Respaldo', V.PrecioPapel AS 'Papel Fotografico', V.cpap AS 'Costo Papel F.', V.PrecioText AS 'Textura', V.ctext AS 'Costo Textura', V.PrecioMaq AS 'Maquila', V.PrecioCanva As 'Canva', V.ccan AS 'Costo Canva', V.PrecioAcrilico AS 'Acrilico', V.cacr AS 'Costo Acrilico', V.PrecioDobleCara AS 'Doble cara', V.cdc AS 'Costo Doble cara',V.PrecioTotal AS 'Precio total', V.ctotal AS 'Costo Total',V.CrisN,V.CrisA,V.Ml,V.Res,V.PapF,V.Text,V.Canva,V.Acrilico,V.DobleCara,V.TiraC,V.PTiraC,V.CTiraC,V.Fecha,V.Cantidad FROM VentasDetalle V
            Where V.Activo = 1 AND V.Serie != 'COT-' AND V.Fecha BETWEEN '$desde' AND '$hasta' AND V.IdSucursal = '$sucbus' ORDER BY V.Fecha,V.Serie,V.IdVenta ASC";
    }


        $resultado = $conexion->query($consulta);
    


	
	if($resultado->num_rows > 0 )
	{
						
		date_default_timezone_set('America/Mazatlan');

        if (PHP_SAPI == 'cli')
            die('Este archivo solo se puede ver desde un navegador web');

        /** Se agrega la libreria PHPExcel */
        require_once 'lib/PHPExcel/PHPExcel.php';
        // Se crea el objeto PHPExcel
        $objPHPExcel = new PHPExcel();

        // Se asignan las propiedades del libro
        $objPHPExcel->getProperties()->setCreator("SINTECH") //Autor
                             ->setLastModifiedBy("SINTECH") //Ultimo usuario que lo modificó
                             ->setTitle("Reporte de ordenes")
                             ->setSubject("Reporte de ordenes")
                             ->setDescription("Reporte de ordenes")
                             ->setKeywords("reporte ordenes reasa")
                             ->setCategory("Reporte excel");

        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

        if($sucbus == 0){
            $sn = 'GLOBAL';
          }else if($sucbus == 1){
            $sn = 'CULIACAN';
          }else if($sucbus == 2){
            $sn = 'MAZATLAN';
          }else if($sucbus == 3){
            $sn = 'HERMOSILLO';
          }

        $tituloReporte = "REPORTE DE CATEGORIAS ".$sn.' '.$desde.' AL '.$hasta;
        $titulosColumnas = array('FECHA','Fotolibros','Costo Fotolibros','Molduras','Costo Molduras','Fotos','Costo Fotos','Caballetes','Costo Caballetes','Fotomosaicos','Costo Fotomosaicos','Maquila');
        
        $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A2:K2');

                  /* ADD LOGO */
        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setName('Logo');
        $objDrawing->setDescription('Logo');
        $objDrawing->setPath('cuad.png');
        $objDrawing->setCoordinates('A1');
        // set resize to false first
        $objDrawing->setResizeProportional(true);
        // set width later
        $objDrawing->setWidth(250);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(125);
        /* END LOGO */

        // set resize to false first
        $objDrawing->setResizeProportional(true); 
                        
        // Se agregan los titulos del reporte
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A2',$tituloReporte)
                    ->setCellValue('A3',  $titulosColumnas[0])
                    ->setCellValue('B3',  $titulosColumnas[1])
                    ->setCellValue('C3',  $titulosColumnas[2])
                    ->setCellValue('D3',  $titulosColumnas[3])
                    ->setCellValue('E3',  $titulosColumnas[4])
                    ->setCellValue('F3',  $titulosColumnas[5])
                    ->setCellValue('G3',  $titulosColumnas[6])
                    ->setCellValue('H3',  $titulosColumnas[7])
                    ->setCellValue('I3',  $titulosColumnas[8])
                    ->setCellValue('J3',  $titulosColumnas[9])
                    ->setCellValue('K3',  $titulosColumnas[10])
                    ->setCellValue('L3',  $titulosColumnas[11]);

        //Se agregan los datos de los alumnos
        $i = 7;
        $fila = $resultado->fetch_array();
        $fechai = $fila['Fecha'];
        $pcatfl = 0;
        $ccatfl = 0;
        $pcatmold = 0;
        $ccatmold = 0;
        $catfotos = 0;
        $ccatfotos = 0;
        $catcab = 0;
        $ccatcab = 0;
        $catfm = 0;
        $ccatfm = 0;
        $catmaq = 0;

do{
                $fechaf = $fila['Fecha'];


            if(strcmp($fechai, $fechaf) == 0){
                
                $producto = $fila['Producto'];
                $p = mysqli_query($conexion,"SELECT * FROM Productos WHERE Codigo = '$producto'");
                $pf = mysqli_fetch_array($p);
                $cat = $pf['IdCategoria'];
                $cant = $fila['Cantidad'];
                if($fila['TiraC'] == 1){
                    $pprod = $fila['PTiraC'];
                    $cprod = $fila['CTiraC'];
                }else{
                    
                $pprod = $fila['Moldura'];
                $cprod = $fila['Costo Moldura'];
                }
                    $pcrisn = $fila['Cristal'];
                    $ccrisn = $fila['Costo Cristal'];
                    $pcrisa = $fila['Cristal Anti'];
                    $ccrisa = $fila['Costo Crisal Anti'];
                    $pml = $fila['Maria Luisa'];
                    $cml = $fila['Costo ML'];
                    $pres = $fila['Respaldo'];
                    $cres = $fila['Costo Respaldo'];
                    $ppap = $fila['Papel Fotografico'];
                    $cpap = $fila['Costo Papel F.'];
                    $ptext = $fila['Textura'];
                    $ctext = $fila['Costo Textura'];
                    $maq = $fila['Maquila'];
                    $pcanva = $fila['Canva'];
                    $ccanva = $fila['Costo Canva'];
                    $pacr = $fila['Acrilico'];
                    $cacr = $fila['Costo Acrilico'];
                    $pdobca = $fila['Doble cara'];
                    $cdc = $fila['Costo Doble cara'];
                    $ptot = $fila['Precio total'];
                    $ctot = $fila['Costo Total'];

                    if($cat == 2){
                        $pcatfl += $pprod * $cant;
                        $ccatfl += $cprod * $cant;
                    }else if($cat == 3){
                        $pcatmold += $pprod * $cant;
                        $ccatmold += $cprod * $cant;
                    }else if($cat == 4){
                        $catfotos += $pprod * $cant;
                        $ccatfotos += $cprod * $cant;
                    }else if($cat == 5){
                        $catcab += $pprod * $cant;
                        $ccatcab += $cprod * $cant;
                    }else if($cat == 6){
                        $catfm += $pprod * $cant;
                        $ccatfm += $cprod * $cant;
                    }

                    
                    if($pcrisn > 0 && $ccrisn > 0){
                        $catfotos += $pcrisn * $cant;
                        $ccatfotos += $ccrisn * $cant;
                    }
                    if($pcrisa > 0 && $ccrisa > 0){
                        $catfotos += $pcrisa * $cant;
                        $ccatfotos += $ccrisa * $cant;
                    }
                    if($pml > 0 && $cml > 0){
                        $catfotos += $pml * $cant;
                        $ccatfotos += $cml * $cant;
                    }
                    if($pres > 0 && $cres > 0){
                        $catfotos += $pres * $cant;
                        $ccatfotos += $cres * $cant;
                    }
                    if($ppap > 0 && $cpap > 0){
                        $catfotos += $ppap * $cant;
                        $ccatfotos += $cpap * $cant;
                    }
                    if($ptext > 0 && $ctext > 0){
                        $catfotos += $ptext * $cant;
                        $ccatfotos += $ctext * $cant;
                    }
                    if($maq > 0){
                        $catmaq += $maq  * $cant;
                    }
                    if($pcanva >0 && $ccanva >0 ){
                        $catfotos += $pcanva * $cant;
                        $ccatfotos += $ccanva * $cant;
                    }
                    if($pacr >0 && $cacr >0 ){
                        $catfotos += $pacr * $cant;
                        $ccatfotos += $cacr * $cant;
                    }
                    if($pdobca >0 && $cdc >0 ){
                        $catfotos += $pdobca * $cant;
                        $ccatfotos += $cdc * $cant;
                    }


                    $ptprod += $pprod;
                    $ctprod += $cprod;
                    $ptcrisn += $pcrisn;
                    $ctcrisn += $ccrisn;
                    $ptcrisa += $pcrisa;
                    $ctcrisa += $ccrisa;
                    $ptml += $pml;
                    $ctml += $cml;
                    $ptres += $pres;
                    $ctres += $cres;
                    $ptpap += $ppap;
                    $ctpap += $cpap;
                    $pttext += $ptext;
                    $cttext += $ctext;
                    $ptmaq += $maq;
                    $ptcanva += $pcanva;
                    $ctcanva += $ccanva;
                    $ptacr += $pacr;
                    $ctacr += $cacr;
                    $ptdobca += $pdobca;
                    $ctdc += $cdc;
                    $pttot += $ptot;
                    $cttot += $ctot;

            
                }else{
                    $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fechai)
                    ->setCellValue('B'.$i,  $pcatfl)
                    ->setCellValue('C'.$i,  $ccatfl)
                    ->setCellValue('D'.$i,  $pcatmold)
                    ->setCellValue('E'.$i,  $ccatmold)
                    ->setCellValue('F'.$i,  $catfotos)
                    ->setCellValue('G'.$i,  $ccatfotos)
                    ->setCellValue('H'.$i,  $catcab)
                    ->setCellValue('I'.$i,  $ccatcab)
                    ->setCellValue('J'.$i,  $catfm)
                    ->setCellValue('K'.$i,  $ccatfm)
                    ->setCellValue('L'.$i,  $catmaq);
                    $i++;
                    $tpfl += $pcatfl;
                    $tcfl += $ccatfl;
                    $tpmold += $pcatmold;
                    $tcmold += $ccatmold;
                    $tpfotos += $catfotos;
                    $tcfotos += $ccatfotos;
                    $tcatcab += $catcab;
                    $tccatcab += $ccatcab;
                    $tcatfm += $catfm;
                    $tccatfm += $ccatfm;
                    $tmaq += $catmaq;
                    $fechai = $fechaf;
                    $pcatfl = 0;
                    $ccatfl = 0;
                    $pcatmold = 0;
                    $ccatmold = 0;
                    $catfotos = 0;
                    $ccatfotos = 0;
                    $catcab = 0;
                    $ccatcab = 0;
                    $catfm = 0;
                    $ccatfm = 0;
                    $catmaq = 0;
                    $p = mysqli_query($conexion,"SELECT * FROM Productos WHERE CodProd = '$producto'");
                    $pf = mysqli_fetch_array($p);
                    $nomp = $pf['Nombre'];
                    $cat = $pf['IdCategoria'];
                    $c = mysqli_query($conexion,"SELECT * FROM Categoria WHERE IdCategoria = '$cat'");
                    $cf = mysqli_fetch_array($c);
                    $nc = $cf['Descripcion'];


                    $pprod = $fila['Moldura'];
                    $cprod = $fila['Costo Moldura'];
                    $pcrisn = $fila['Cristal'];
                    $ccrisn = $fila['Costo Cristal'];
                    $pcrisa = $fila['Cristal Anti'];
                    $ccrisa = $fila['Costo Crisal Anti'];
                    $pml = $fila['Maria Luisa'];
                    $cml = $fila['Costo ML'];
                    $pres = $fila['Respaldo'];
                    $cres = $fila['Costo Respaldo'];
                    $ppap = $fila['Papel Fotografico'];
                    $cpap = $fila['Costo Papel F.'];
                    $ptext = $fila['Textura'];
                    $ctext = $fila['Costo Textura'];
                    $maq = $fila['Maquila'];
                    $pcanva = $fila['Canva'];
                    $ccanva = $fila['Costo Canva'];
                    $pacr = $fila['Acrilico'];
                    $cacr = $fila['Costo Acrilico'];
                    $pdobca = $fila['Doble cara'];
                    $cdc = $fila['Costo Doble cara'];
                    $ptot = $fila['Precio total'];
                    $ctot = $fila['Costo Total'];

                    if($cat == 2){
                        $pcatfl += $pprod * $cant;
                        $ccatfl += $cprod * $cant;
                    }else if($cat == 3){
                        $pcatmold += $pprod * $cant;
                        $ccatmold += $cprod * $cant;
                    }else if($cat == 4){
                        $catfotos += $pprod * $cant;
                        $ccatfotos += $cprod * $cant;
                    }else if($cat == 5){
                        $catcab += $pprod * $cant;
                        $ccatcab += $cprod * $cant;
                    }else if($cat == 6){
                        $catfm += $pprod * $cant;
                        $ccatfm += $cprod * $cant;
                    }

                    
                    if($pcrisn > 0 && $ccrisn > 0){
                        $catfotos += $pcrisn * $cant;
                        $ccatfotos += $ccrisn * $cant;
                    }
                    if($pcrisa > 0 && $ccrisa > 0){
                        $catfotos += $pcrisa * $cant;
                        $ccatfotos += $ccrisa * $cant;
                    }
                    if($pml > 0 && $cml > 0){
                        $catfotos += $pml * $cant;
                        $ccatfotos += $cml * $cant;
                    }
                    if($pres > 0 && $cres > 0){
                        $catfotos += $pres * $cant;
                        $ccatfotos += $cres * $cant;
                    }
                    if($ppap > 0 && $cpap > 0){
                        $catfotos += $ppap * $cant;
                        $ccatfotos += $cpap * $cant;
                    }
                    if($ptext > 0 && $ctext > 0){
                        $catfotos += $ptext * $cant;
                        $ccatfotos += $ctext * $cant;
                    }
                    if($maq > 0){
                        $catmaq += $maq  * $cant;
                    }
                    if($pcanva >0 && $ccanva >0 ){
                        $catfotos += $pcanva * $cant;
                        $ccatfotos += $ccanva * $cant;
                    }
                    if($pacr >0 && $cacr >0 ){
                        $catfotos += $pacr * $cant;
                        $ccatfotos += $cacr * $cant;
                    }
                    if($pdobca >0 && $cdc >0 ){
                        $catfotos += $pdobca * $cant;
                        $ccatfotos += $cdc * $cant;
                    }
                
                }

                    

 }while($fila = $resultado->fetch_array());

        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fechai)
                    ->setCellValue('B'.$i,  $pcatfl)
                    ->setCellValue('C'.$i,  $ccatfl)
                    ->setCellValue('D'.$i,  $pcatmold)
                    ->setCellValue('E'.$i,  $ccatmold)
                    ->setCellValue('F'.$i,  $catfotos)
                    ->setCellValue('G'.$i,  $ccatfotos)
                    ->setCellValue('H'.$i,  $catcab)
                    ->setCellValue('I'.$i,  $ccatcab)
                    ->setCellValue('J'.$i,  $catfm)
                    ->setCellValue('K'.$i,  $ccatfm)
                    ->setCellValue('L'.$i,  $catmaq);
                    
        $tpfl += $pcatfl;
        $tcfl += $ccatfl;
        $tpmold += $pcatmold;
        $tcmold += $ccatmold;
        $tpfotos += $catfotos;
        $tcfotos += $ccatfotos;
        $tcatcab += $catcab;
        $tccatcab += $ccatcab;
        $tcatfm += $catfm;
        $tccatfm += $ccatfm;
        $tmaq += $catmaq;
        $i += 2;
        $objPHPExcel->setActiveSheetIndex(0)
                    
                    ->setCellValue('B'.$i,  $tpfl)
                    ->setCellValue('C'.$i,  $tcfl)
                    ->setCellValue('D'.$i,  $tpmold)
                    ->setCellValue('E'.$i,  $tcmold)
                    ->setCellValue('F'.$i,  $tpfotos)
                    ->setCellValue('G'.$i,  $tcfotos)
                    ->setCellValue('H'.$i,  $tcatcab)
                    ->setCellValue('I'.$i,  $tccatcab)
                    ->setCellValue('J'.$i,  $tcatfm)
                    ->setCellValue('K'.$i,  $tccatfm)
                    ->setCellValue('L'.$i,  $tmaq);

                    $objPHPExcel->setActiveSheetIndex(0)
                    
                    ->setCellValue('B5',  $tpfl)
                    ->setCellValue('C5',  $tcfl)
                    ->setCellValue('D5',  $tpmold)
                    ->setCellValue('E5',  $tcmold)
                    ->setCellValue('F5',  $tpfotos)
                    ->setCellValue('G5',  $tcfotos)
                    ->setCellValue('H5',  $tcatcab)
                    ->setCellValue('I5',  $tccatcab)
                    ->setCellValue('J5',  $tcatfm)
                    ->setCellValue('K5',  $tccatfm)
                    ->setCellValue('L5',  $tmaq);
        $i++;

        $estiloTituloReporte = array(
            'font' => array(
                'name'      => 'Verdana',
                'bold'      => true,
                'italic'    => false,
                'strike'    => false,
                'size' =>16,
                    'color'     => array(
                        'rgb' => 'FFFFFF'
                    )
            ),
            'fill' => array(
                'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('argb' => 'FF2b5e28')
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_NONE                    
                )
            ), 
            'alignment' =>  array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'rotation'   => 0,
                    'wrap'          => TRUE
            )
        );

        $estiloTituloColumnas = array(
            'font' => array(
                'name'      => 'Arial',
                'bold'      => true,                          
                'color'     => array(
                    'rgb' => 'FFFFFF'
                )
            ),
            'fill'  => array(
                'type'      => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 90,
                'startcolor' => array(
                    'rgb' => '57BB52'
                ),
                'endcolor'   => array(
                    'argb' => 'FF57BB52'
                )
            ),
            'borders' => array(
                'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '143860'
                    )
                ),
                'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '143860'
                    )
                )
            ),
            'alignment' =>  array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'wrap'          => TRUE
            ));
            
        $estiloInformacion = new PHPExcel_Style();
        $estiloInformacion->applyFromArray(
            array(
                'font' => array(
                'name'      => 'Arial',               
                'color'     => array(
                    'rgb' => '000000'
                )
            ),
            'fill'  => array(
                'type'      => PHPExcel_Style_Fill::FILL_SOLID,
                'color'     => array('argb' => 'FFFFFFFF')
            ),
            'borders' => array(
                
                'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '000000'
                    )
                ),
                'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '000000'
                    )
                )

            ),
            'alignment' =>  array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER
                    
            )
        ));
         
        $objPHPExcel->getActiveSheet()->getStyle('A2:L2')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:L3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:L".($i-1));
        $objPHPExcel->getActiveSheet()->getStyle('B4:B'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('C4:C'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('D4:D'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('E4:E'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('F4:F'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('G4:G'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('H4:H'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('I4:I'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('J4:J'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('K4:K'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('L4:L'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        



       // In my case this line didn't make much of a difference
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
// Iterating all the sheets
/** @var PHPExcel_Worksheet $sheet */
foreach ($objPHPExcel->getAllSheets() as $sheet) {
    // Iterating through all the columns
    // The after Z column problem is solved by using numeric columns; thanks to the columnIndexFromString method
    for ($col = 0; $col <= PHPExcel_Cell::columnIndexFromString($sheet->getHighestDataColumn()); $col++) {
        $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
    }
}

        
        // Se asigna el nombre a la hoja
        $objPHPExcel->getActiveSheet()->setTitle('Reporte de productos');

        // Se activa la hoja para que sea la que se muestre cuando el archivo se abre
        $objPHPExcel->setActiveSheetIndex(0);
        // Inmovilizar paneles 
        //$objPHPExcel->getActiveSheet(0)->freezePane('A4');
        $objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0,4);

        // Se manda el archivo al navegador web, con el nombre que se indica (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="ReportedeCategorias.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
        
    }
    else{
        print_r('No hay resultados para mostrar');
    }
?>