#Dev-co Snippets

ID: 5 - Generar archivos para MS Excel en CakePHP usando PHPExcel
Posted by andphe on Sat Feb 20 01:05:10 UTC 2010. Language php

<?php
/**
* Esta vista genera al vuelo un archivo en formato MS Excel 2007
* restringiendo los valores que las columnas de los atributos
* pueden tomar.
*
* Requiere http://phpexcel.codeplex.com/ instalado la carpeta 
* app/vendors
*
*/
App::import('vendor','PHPExcel', array('file' => 'PHPExcel.php'));
App::import('vendor','PHPExcel_IOFactory', array('file' => 'PHPExcel/IOFactory.php'));

$objPHPExcel = new PHPExcel();

// Create a first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "C�digo");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "Nombre");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "M�nimo");
$objPHPExcel->getActiveSheet()->setCellValue('D1', "M�ximo");
$objPHPExcel->getActiveSheet()->setCellValue('E1', "Precio");
$letter = 70;

foreach($products[0]['Attribute'] as $attribute){ //works exporting a product 
    $objPHPExcel->getActiveSheet()->setCellValue(chr($letter++).'1', $attribute['name']);

    unset($objValidation);
    $objValidation = $objPHPExcel->getActiveSheet()->getCell('F2')->getDataValidation();
    $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
    $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
    $objValidation->setAllowBlank(false);
    $objValidation->setShowInputMessage(true);
    $objValidation->setShowErrorMessage(true);
    $objValidation->setShowDropDown(true);
    $objValidation->setErrorTitle('Input error');
    $objValidation->setError('Value is not in list.');
    $objValidation->setPromptTitle('Pick from list');
    $objValidation->setPrompt('Please pick a value from the drop-down list.');

    $productOptions = Set::extract($options[$attribute['id']],'{n}.name');
    $objValidation->setFormula1('"'.implode(',', $productOptions).'"');	// Make sure to put the list items between " and "  !!!
    $validations[$attribute['id']] = clone $objValidation;

}

$row = 2;
for($i = 0;$i<100;$i++){
    $price = @$products[0]['Price'][$i];
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, @$price['product_id'] );
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, (@$price['product_id']?$products[0]['Product']['name']:''));
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, @$price['min'] );
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, @$price['max'] );
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, @$price['price'] );
    $letter = 70;
    foreach($products[0]['Attribute'] as $attribute){ //works exporting a product
        $objPHPExcel->getActiveSheet()->getCell(chr($letter++).$row)->setDataValidation($validations[$attribute['id']]);
    }
    $row++;
}

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