123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175 |
- <?php
- error_reporting(E_ALL);
- ini_set('display_errors', TRUE);
- ini_set('display_startup_errors', TRUE);
- date_default_timezone_set('Europe/London');
- define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
- require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
- echo date('H:i:s') , " Create new PHPExcel object" , EOL;
- $objPHPExcel = new PHPExcel();
- echo date('H:i:s') , " Set document properties" , EOL;
- $objPHPExcel->getProperties()
- ->setCreator("PHPOffice")
- ->setLastModifiedBy("PHPOffice")
- ->setTitle("PHPExcel Test Document")
- ->setSubject("PHPExcel Test Document")
- ->setDescription("Test document for PHPExcel, generated using PHP classes.")
- ->setKeywords("Office PHPExcel php")
- ->setCategory("Test result file");
- function transpose($value) {
- return array($value);
- }
- $continentColumn = 'D';
- $column = 'F';
- foreach(glob('./data/continents/*') as $key => $filename) {
- $continent = pathinfo($filename, PATHINFO_FILENAME);
- echo "Loading $continent", EOL;
- $continent = str_replace(' ','_',$continent);
- $countries = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
- $countryCount = count($countries);
-
- $countries = array_map('transpose', $countries);
- $objPHPExcel->getActiveSheet()
- ->fromArray($countries, null, $column . '1');
- $objPHPExcel->addNamedRange(
- new PHPExcel_NamedRange(
- $continent,
- $objPHPExcel->getActiveSheet(), $column . '1:' . $column . $countryCount
- )
- );
- $objPHPExcel->getActiveSheet()
- ->getColumnDimension($column)
- ->setVisible(false);
- $objPHPExcel->getActiveSheet()
- ->setCellValue($continentColumn . ($key+1), $continent);
- ++$column;
- }
- $objPHPExcel->getActiveSheet()
- ->getColumnDimension($continentColumn)
- ->setVisible(false);
- $objPHPExcel->addNamedRange(
- new PHPExcel_NamedRange(
- 'Continents',
- $objPHPExcel->getActiveSheet(), $continentColumn . '1:' . $continentColumn . ($key+1)
- )
- );
- $objPHPExcel->getActiveSheet()
- ->setCellValue('A1', 'Continent:');
- $objPHPExcel->getActiveSheet()
- ->setCellValue('B1', 'Select continent');
- $objPHPExcel->getActiveSheet()
- ->setCellValue('B3', '=' . $column . 1);
- $objPHPExcel->getActiveSheet()
- ->setCellValue('B3', 'Select country');
- $objPHPExcel->getActiveSheet()
- ->getStyle('A1:A3')
- ->getFont()->setBold(true);
- $objValidation = $objPHPExcel->getActiveSheet()
- ->getCell('B1')
- ->getDataValidation();
- $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST )
- ->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION )
- ->setAllowBlank(false)
- ->setShowInputMessage(true)
- ->setShowErrorMessage(true)
- ->setShowDropDown(true)
- ->setErrorTitle('Input error')
- ->setError('Continent is not in the list.')
- ->setPromptTitle('Pick from the list')
- ->setPrompt('Please pick a continent from the drop-down list.')
- ->setFormula1('=Continents');
- $objPHPExcel->getActiveSheet()
- ->setCellValue('A3', 'Country:');
- $objPHPExcel->getActiveSheet()
- ->getStyle('A3')
- ->getFont()->setBold(true);
- $objValidation = $objPHPExcel->getActiveSheet()
- ->getCell('B3')
- ->getDataValidation();
- $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST )
- ->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION )
- ->setAllowBlank(false)
- ->setShowInputMessage(true)
- ->setShowErrorMessage(true)
- ->setShowDropDown(true)
- ->setErrorTitle('Input error')
- ->setError('Country is not in the list.')
- ->setPromptTitle('Pick from the list')
- ->setPrompt('Please pick a country from the drop-down list.')
- ->setFormula1('=INDIRECT($B$1)');
- $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
- $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
- $objPHPExcel->setActiveSheetIndex(0);
- echo date('H:i:s') , " Write to Excel2007 format" , EOL;
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
- $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
- echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
- echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
- echo date('H:i:s') , " Done writing files" , EOL;
- echo 'Files have been created in ' , getcwd() , EOL;
|