File "clientes_excel.php"

Full path: /home/wwwitravel/public_html/00sistema.cymchiletours.cl/vistas/clientes_excel.php
File size: 0.01 KB (5.54 KB bytes)
MIME-type: text/x-php
Charset: utf-8

Download   Open   Edit   Advanced Editor   Back

<?php
require_once "../clases/config.php";
require_once '../clases/PHPExcel.php';
 
$conexion= mysqli_connect(HOST,USER,PASS, DBNAME) or die ("could not connect to mysql"); 
$conexion->set_charset("utf8");
$sql="SELECT a.id,a.nombre,a.apepat,a.apemat,a.rut,a.email,a.tipo_cliente,a.f_nac,a.banco,a.pasaporte,a.fono1,a.fono2,a.fecha_creacion,CONCAT(b.nombre,' ',b.apepat) as uscreado,c.Pais,d.ciudad,a.comuna,a.direccion,a.observaciones,a.codpostal,a.razon_social,a.cuenta FROM clientes as a LEFT JOIN usuarios as b ON a.us_creador=b.id LEFT JOIN paises as c ON c.Codigo=a.pais LEFT JOIN ciudades as d ON d.idCiudades=a.ciudad ORDER BY a.nombre ASC;";
$resultado=mysqli_query($conexion, $sql) or die(mysql_error());   
$registros = mysqli_num_rows ($resultado);
$fecha=date('d-m-Y'); 
 
 
 
 
 
 if ($registros > 0) {
   $objPHPExcel = new PHPExcel();
   
   //Informacion del excel
   $objPHPExcel->
    getProperties()
        ->setCreator("I-travel Administrador")
        ->setLastModifiedBy("I-travel Administrador")
        ->setTitle("PROVEEDORES")
        ->setSubject("Hoja 1")
        ->setDescription("Documento generado con PHPExcel")
        ->setKeywords("phpexcel")
        ->setCategory("proveedores");    

   $i = 1;    
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, 'ID');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, 'NOMBRE');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, 'TIPO CUENTA');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, 'RAZÓN SOCIAL');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, 'A. PATERNO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, 'A. MATERNO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, 'RUT');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, 'EMAIL');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$i, 'TIPO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$i, 'F. NACIMIENTO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, 'BANCO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$i, 'PASAPORTE');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$i, 'FONO CONTACTO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$i, 'FONO MOVIL');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$i, 'F. CREADO');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P'.$i, 'CREADO POR');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q'.$i, 'PAIS');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R'.$i, 'CIUDAD');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S'.$i, 'COMUNA');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T'.$i, 'DIRECCIÓN');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$i, 'OBSERVACIONES');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$i, 'COD. POSTAL');
   
   $i++;
   
   
   
   while ($registro = mysqli_fetch_object ($resultado)) {
      //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, $registro->nombre);
	  
	  
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, strtoupper($registro->id));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, strtoupper($registro->nombre));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, strtoupper($registro->cuenta));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, strtoupper($registro->razon_social));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, strtoupper($registro->apepat));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, strtoupper($registro->apemat));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, strtoupper($registro->rut));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, strtoupper($registro->email));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$i, strtoupper($registro->tipo_cliente));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$i, strtoupper($registro->f_nac));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, strtoupper($registro->banco));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$i, strtoupper($registro->pasaporte));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$i, strtoupper($registro->fono1));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$i, strtoupper($registro->fono2));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$i, strtoupper($registro->fecha_creacion));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P'.$i, strtoupper($registro->uscreado));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q'.$i, strtoupper($registro->Pais));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R'.$i, strtoupper($registro->ciudad));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S'.$i, strtoupper($registro->comuna));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T'.$i, strtoupper($registro->direccion));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$i, strtoupper($registro->observaciones));
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$i, strtoupper($registro->codpostal));
      $i++;
      
   }
}


foreach(range('A','V') as $columnID) { 
	$objPHPExcel->getActiveSheet()->getColumnDimension($columnID) ->setAutoSize(true);
};
 
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="clientes'.$fecha.'.xlsx"');
header('Cache-Control: max-age=0');

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