Programmiererforum

Code: Select all

/**
* Cabeceras para descargar el excel
*/
ini_set('memory_limit', '512M');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=informe_de_precios" . date("Y-M-D-h-m-s") .  ".xls");
header("Content-Transfer-Encoding:  binary ");

require_once '/PHPExcel_1.8.0_doc/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->
getProperties()
->setCreator("Idiso")
->setLastModifiedBy("Idiso")
->setTitle("Informe errores precios")
->setSubject("Informe errores precios")
->setDescription("Informe errores precios")
->setKeywords("Informe errores precios")
->setCategory("Informe errores precios");

$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'CANAL')
->setCellValue('B1', 'COD_HOTEL')
->setCellValue('C1', 'NOMBRE')
->setCellValue('D1', 'TARIFA')
->setCellValue('E1', 'HABITACION')
->setCellValue('F1', 'REGIMEN')
->setCellValue('G1', 'FECHA_INI')
->setCellValue('H1', 'FECHA_FIN')
->setCellValue('I1', 'RECUENTO')
->setCellValue('J1', 'PUBLICADO')
->setCellValue('K1', 'ERROR_DESCRIPTION')
->setCellValue('L1', 'ERROR_DETAIL')
->setCellValue('M1', 'TRAN_XMLIN')
->setCellValue('N1', 'TRAN_XMLOUT');

$objPHPExcel->getActiveSheet(0)->setTitle('Precios');
$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFont()
->setBold(true)
->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFill()->getStartColor()->setARGB('FFFF00');
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:N1');
$objPHPExcel->getActiveSheet()->freezePane('A2');

$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(1)
->setCellValue('A1', 'CANAL')
->setCellValue('B1', 'COD_HOTEL')
->setCellValue('C1', 'NOMBRE')
->setCellValue('D1', 'TARIFA')
->setCellValue('E1', 'HABITACION')
->setCellValue('F1', 'FECHA_INI')
->setCellValue('G1', 'FECHA_FIN')
->setCellValue('H1', 'RECUENTO')
->setCellValue('I1', 'PUBLICADO')
->setCellValue('J1', 'ERROR_DESCRIPTION')
->setCellValue('K1', 'ERROR_DETAIL')
->setCellValue('L1', 'TRAN_XMLIN')
->setCellValue('M1', 'TRAN_XMLOUT');

$objPHPExcel->getActiveSheet(1)->setTitle('Cupos');
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFont()
->setBold(true)
->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFill()->getStartColor()->setARGB('FFFF00');
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:M1');
$objPHPExcel->getActiveSheet()->freezePane('A1');

$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(2)
->setCellValue('A1', 'CANAL')
->setCellValue('B1', 'COD_HOTEL')
->setCellValue('C1', 'NOMBRE')
->setCellValue('D1', 'TARIFA')
->setCellValue('E1', 'HABITACION')
->setCellValue('F1', 'FECHA_INI')
->setCellValue('G1', 'FECHA_FIN')
->setCellValue('H1', 'RECUENTO')
->setCellValue('I1', 'PUBLICADO')
->setCellValue('J1', 'ERROR_DESCRIPTION')
->setCellValue('K1', 'ERROR_DETAIL')
->setCellValue('L1', 'TRAN_XMLIN')
->setCellValue('M1',  'TRAN_XMLOUT');

$objPHPExcel->getActiveSheet(2)->setTitle('Normas');
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFill()->getStartColor()->setARGB('FFFF00');
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:M1');
$objPHPExcel->getActiveSheet()->freezePane('A1');
/**
* Usamos un iterador para darle estilos a cada columna del excel
*/
$fileSystem = new DirectoryIterator("C:\Windows\Temp");
chdir("C:\Windows\Temp");
foreach ($fileSystem as $tempFile) {
if (preg_match("/price\d*.csv/", $tempFile->getFilename())) {
$objPHPExcel->setActiveSheetIndex(0);
$objReader = new PHPExcel_Reader_CSV();
$objReader->setInputEncoding('CP1252');
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\n");
$objReader->setSheetIndex(0);
$objReader->loadIntoExisting($tempFile->getFilename(), $objPHPExcel);
} else if (preg_match("/allotment\d*.csv/", $fileSystem->getFilename())) {
$objPHPExcel->setActiveSheetIndex(1);
$objReader = new PHPExcel_Reader_CSV();
$objReader->setInputEncoding('CP1252');
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\n");
$objReader->setSheetIndex(0);
$objReader->loadIntoExisting($tempFile->getFilename(), $objPHPExcel);
} else if (preg_match("/price\d*.csv/", $fileSystem->getFilename())) {
$objPHPExcel->setActiveSheetIndex(2);
$objReader = new PHPExcel_Reader_CSV();
$objReader->setInputEncoding('CP1252');
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\n");
$objReader->setSheetIndex(0);
$objReader->loadIntoExisting($tempFile->getFilename(), $objPHPExcel);
} else {
continue;
}
$fileSystem->next();
}

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
/**
* Cogemos el index de la hoja activa en el momento para poder iterar
* entre las hojas
*/
$objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));

$sheet = $objPHPExcel->getActiveSheet();
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
/**
* Mediante este foreach le damos el formato que queramos a las columnas,
* si la columna es "mayor"(alfabéticamente) a la J se le da un width
* de 40, si no tiene autoSize
*/
foreach ($cellIterator as $cell) {
//print_r($sheet->getColumnDimension($cell->getColumn()));
if ($sheet->getColumnDimension($cell->getColumn())->getColumnIndex() > "J") {
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(false);
$sheet->getColumnDimension($cell->getColumn())->setWidth(40);
$sheet->getDefaultStyle()->getFont()->setName('Calibri')->setSize(9);
} else {
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
$sheet->getDefaultStyle()->getFont()->setName('Calibri')->setSize(9);
}
}
}

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('php://output');