/** * Generate export fields for EXCEL. * * @param GridField $gridField * @return array */ public function generateExportFileData($gridField) { $excelColumns = $this->exportColumns ? $this->exportColumns : singleton($gridField->getModelClass())->summaryFields(); $objPHPExcel = new \PHPExcel\Spreadsheet(); $worksheet = $objPHPExcel->getActiveSheet()->setTitle($gridField->getModelClass()); $col = 'A'; foreach ($excelColumns as $columnSource => $columnHeader) { $heading = !is_string($columnHeader) && is_callable($columnHeader) ? $columnSource : $columnHeader; $worksheet->setCellValue($col . '1', $heading); $col++; } $worksheet->freezePane('A2'); $items = $gridField->getManipulatedList(); // @todo should GridFieldComponents change behaviour based on whether others are available in the config? foreach ($gridField->getConfig()->getComponents() as $component) { if ($component instanceof GridFieldFilterHeader || $component instanceof GridFieldSortableHeader) { $items = $component->getManipulatedData($gridField, $items); } } $row = 2; foreach ($items->limit(null) as $item) { $columnData = array(); $col = 'A'; foreach ($excelColumns as $columnSource => $columnHeader) { if (!is_string($columnHeader) && is_callable($columnHeader)) { if ($item->hasMethod($columnSource)) { $relObj = $item->{$columnSource}(); } else { $relObj = $item->relObject($columnSource); } $value = $columnHeader($relObj); } else { $value = $gridField->getDataFieldValue($item, $columnSource); } $worksheet->getCell($col . $row)->setValueExplicit($value, PHPExcel\Cell\DataType::TYPE_STRING); $col++; } $row++; $item->destroy(); } $writer = PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); ob_start(); $writer->save('php://output'); $data = ob_get_clean(); return $data; }
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet, representing sales data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', '-0.5')->setCellValue('A2', '-0.25')->setCellValue('A3', '0.0')->setCellValue('A4', '0.25')->setCellValue('A5', '0.5')->setCellValue('A6', '0.75')->setCellValue('A7', '1.0')->setCellValue('A8', '1.25'); $objPHPExcel->getActiveSheet()->getStyle('A1:A8')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_PERCENTAGE_00); // Add conditional formatting echo date('H:i:s'), " Add conditional formatting", EOL; $objConditional1 = new \PHPExcel\Style\Conditional(); $objConditional1->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_LESSTHAN)->addCondition('0'); $objConditional1->getStyle()->getFont()->getColor()->setARGB(\PHPExcel\Style\Color::COLOR_RED); $objConditional3 = new \PHPExcel\Style\Conditional(); $objConditional3->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_GREATERTHANOREQUAL)->addCondition('1'); $objConditional3->getStyle()->getFont()->getColor()->setARGB(\PHPExcel\Style\Color::COLOR_GREEN); $conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('A1')->getConditionalStyles(); array_push($conditionalStyles, $objConditional1); array_push($conditionalStyles, $objConditional3); $objPHPExcel->getActiveSheet()->getStyle('A1')->setConditionalStyles($conditionalStyles); // duplicate the conditional styles across a range of cells echo date('H:i:s'), " Duplicate the conditional formatting across a range of cells", EOL;
require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Set timezone echo date('H:i:s'), " Set timezone", EOL; date_default_timezone_set('UTC'); // Set value binder echo date('H:i:s'), " Set value binder", EOL; \PHPExcel\Cell::setValueBinder(new \PHPExcel\Cell\AdvancedValueBinder()); // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Set default font echo date('H:i:s'), " Set default font", EOL; $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); // Set column widths echo date('H:i:s'), " Set column widths", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14); // Add some data, resembling some different data types echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String value:')->setCellValue('B1', 'Mark Baker'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Numeric value #1:')->setCellValue('B2', 12345); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Numeric value #2:')->setCellValue('B3', -12.345); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Numeric value #3:')->setCellValue('B4', 0.12345); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Numeric value #4:')->setCellValue('B5', '12345'); $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Numeric value #5:')->setCellValue('B6', '1.2345'); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Numeric value #6:')->setCellValue('B7', '.12345'); $objPHPExcel->getActiveSheet()->setCellValue('A8', 'Numeric value #7:')->setCellValue('B8', '1.234e-5');
1000 6 2000 12 4000 36 8000 64 15000 465 */ // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet echo date('H:i:s'), " Add data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname"); $objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname"); $objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone"); $objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax"); $objPHPExcel->getActiveSheet()->setCellValue('E1', "Is Client ?"); // Hide "Phone" and "fax" column echo date('H:i:s'), " Hide 'Phone' and 'fax' columns", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false); // Set outline levels echo date('H:i:s'), " Set outline levels", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1)->setVisible(false)->setCollapsed(true); // Freeze panes echo date('H:i:s'), " Freeze panes", EOL; $objPHPExcel->getActiveSheet()->freezePane('A2'); // Rows to repeat at top
ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet echo date('H:i:s'), " Add data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Cell B3 and B5 contain data validation...")->setCellValue('A3', "Number:")->setCellValue('B3', "10")->setCellValue('A5', "List:")->setCellValue('B5', "Item A")->setCellValue('A7', "List #2:")->setCellValue('B7', "Item #2")->setCellValue('D2', "Item #1")->setCellValue('D3', "Item #2")->setCellValue('D4', "Item #3")->setCellValue('D5', "Item #4")->setCellValue('D6', "Item #5"); // Set data validation echo date('H:i:s'), " Set data validation", EOL; $objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation(); $objValidation->setType(\PHPExcel\Cell\DataValidation::TYPE_WHOLE); $objValidation->setErrorStyle(\PHPExcel\Cell\DataValidation::STYLE_STOP); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Only numbers between 10 and 20 are allowed!'); $objValidation->setPromptTitle('Allowed input'); $objValidation->setPrompt('Only numbers between 10 and 20 are allowed.'); $objValidation->setFormula1(10); $objValidation->setFormula2(20); $objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();
date_default_timezone_set('Europe/London'); if (PHP_SAPI == 'cli') { die('This example should only be run from a Web Browser'); } /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!'); // Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (OpenDocument) header('Content-Type: application/vnd.oasis.opendocument.spreadsheet'); header('Content-Disposition: attachment;filename="01simple.ods"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet, representing sales data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Description')->setCellValue('B1', 'Amount'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Paycheck received')->setCellValue('B2', 100); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Cup of coffee bought')->setCellValue('B3', -1.5); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Cup of coffee bought')->setCellValue('B4', -1.5); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Cup of tea bought')->setCellValue('B5', -1.2); $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Found some money')->setCellValue('B6', 8); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Total:')->setCellValue('B7', '=SUM(B2:B6)'); // Set column widths echo date('H:i:s'), " Set column widths", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12); // Add conditional formatting echo date('H:i:s'), " Add conditional formatting", EOL; $objConditional1 = new \PHPExcel\Style\Conditional(); $objConditional1->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_BETWEEN)->addCondition('200')->addCondition('400'); $objConditional1->getStyle()->getFont()->getColor()->setARGB(\PHPExcel\Style\Color::COLOR_YELLOW);
* @version ##VERSION##, ##DATE## */ /** Error reporting */ error_reporting(E_ALL); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet, representing sales data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice'); $objPHPExcel->getActiveSheet()->setCellValue('D1', \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y')))); $objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_XLSX15); $objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566'); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id'); $objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description'); $objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price'); $objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount'); $objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total'); $objPHPExcel->getActiveSheet()->setCellValue('A4', '1001'); $objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies'); $objPHPExcel->getActiveSheet()->setCellValue('C4', '20'); $objPHPExcel->getActiveSheet()->setCellValue('D4', '1'); $objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")'); $objPHPExcel->getActiveSheet()->setCellValue('A5', '1012'); $objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); mt_srand(1234567890); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // List functions echo date('H:i:s'), " List implemented functions", EOL; $objCalc = \PHPExcel\Calculation::getInstance(); print_r($objCalc->listFunctionNames()); // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data and formulas", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A14', 'Count:')->setCellValue('A15', 'Sum:')->setCellValue('A16', 'Max:')->setCellValue('A17', 'Min:')->setCellValue('A18', 'Average:')->setCellValue('A19', 'Median:')->setCellValue('A20', 'Mode:'); $objPHPExcel->getActiveSheet()->setCellValue('A22', 'CountA:')->setCellValue('A23', 'MaxA:')->setCellValue('A24', 'MinA:'); $objPHPExcel->getActiveSheet()->setCellValue('A26', 'StDev:')->setCellValue('A27', 'StDevA:')->setCellValue('A28', 'StDevP:')->setCellValue('A29', 'StDevPA:'); $objPHPExcel->getActiveSheet()->setCellValue('A31', 'DevSq:')->setCellValue('A32', 'Var:')->setCellValue('A33', 'VarA:')->setCellValue('A34', 'VarP:')->setCellValue('A35', 'VarPA:'); $objPHPExcel->getActiveSheet()->setCellValue('A37', 'Date:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range 1')->setCellValue('B2', 2)->setCellValue('B3', 8)->setCellValue('B4', 10)->setCellValue('B5', True)->setCellValue('B6', False)->setCellValue('B7', 'Text String')->setCellValue('B9', '22')->setCellValue('B10', 4)->setCellValue('B11', 6)->setCellValue('B12', 12); $objPHPExcel->getActiveSheet()->setCellValue('B14', '=COUNT(B2:B12)')->setCellValue('B15', '=SUM(B2:B12)')->setCellValue('B16', '=MAX(B2:B12)')->setCellValue('B17', '=MIN(B2:B12)')->setCellValue('B18', '=AVERAGE(B2:B12)')->setCellValue('B19', '=MEDIAN(B2:B12)')->setCellValue('B20', '=MODE(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B22', '=COUNTA(B2:B12)')->setCellValue('B23', '=MAXA(B2:B12)')->setCellValue('B24', '=MINA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B26', '=STDEV(B2:B12)')->setCellValue('B27', '=STDEVA(B2:B12)')->setCellValue('B28', '=STDEVP(B2:B12)')->setCellValue('B29', '=STDEVPA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B31', '=DEVSQ(B2:B12)')->setCellValue('B32', '=VAR(B2:B12)')->setCellValue('B33', '=VARA(B2:B12)')->setCellValue('B34', '=VARP(B2:B12)')->setCellValue('B35', '=VARPA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B37', '=DATE(2007, 12, 21)')->setCellValue('B38', '=DATEDIF( DATE(2007, 12, 21), DATE(2007, 12, 22), "D" )')->setCellValue('B39', '=DATEVALUE("01-Feb-2006 10:06 AM")')->setCellValue('B40', '=DAY( DATE(2006, 1, 2) )')->setCellValue('B41', '=DAYS360( DATE(2002, 2, 3), DATE(2005, 5, 31) )'); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Range 2')->setCellValue('C2', 1)->setCellValue('C3', 2)->setCellValue('C4', 2)->setCellValue('C5', 3)->setCellValue('C6', 3)->setCellValue('C7', 3)->setCellValue('C8', '0')->setCellValue('C9', 4)->setCellValue('C10', 4)->setCellValue('C11', 4)->setCellValue('C12', 4); $objPHPExcel->getActiveSheet()->setCellValue('C14', '=COUNT(C2:C12)')->setCellValue('C15', '=SUM(C2:C12)')->setCellValue('C16', '=MAX(C2:C12)')->setCellValue('C17', '=MIN(C2:C12)')->setCellValue('C18', '=AVERAGE(C2:C12)')->setCellValue('C19', '=MEDIAN(C2:C12)')->setCellValue('C20', '=MODE(C2:C12)'); $objPHPExcel->getActiveSheet()->setCellValue('C22', '=COUNTA(C2:C12)')->setCellValue('C23', '=MAXA(C2:C12)')->setCellValue('C24', '=MINA(C2:C12)'); $objPHPExcel->getActiveSheet()->setCellValue('C26', '=STDEV(C2:C12)')->setCellValue('C27', '=STDEVA(C2:C12)')->setCellValue('C28', '=STDEVP(C2:C12)')->setCellValue('C29', '=STDEVPA(C2:C12)'); $objPHPExcel->getActiveSheet()->setCellValue('C31', '=DEVSQ(C2:C12)')->setCellValue('C32', '=VAR(C2:C12)')->setCellValue('C33', '=VARA(C2:C12)')->setCellValue('C34', '=VARP(C2:C12)')->setCellValue('C35', '=VARPA(C2:C12)');
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 />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Sum:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range #1')->setCellValue('B2', 3)->setCellValue('B3', 7)->setCellValue('B4', 13)->setCellValue('B5', '=SUM(B2:B4)'); echo date('H:i:s'), " Sum of Range #1 is ", $objPHPExcel->getActiveSheet()->getCell('B5')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Range #2')->setCellValue('C2', 5)->setCellValue('C3', 11)->setCellValue('C4', 17)->setCellValue('C5', '=SUM(C2:C4)'); echo date('H:i:s'), " Sum of Range #2 is ", $objPHPExcel->getActiveSheet()->getCell('C5')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Total of both ranges:'); $objPHPExcel->getActiveSheet()->setCellValue('B7', '=SUM(B5:C5)'); echo date('H:i:s'), " Sum of both Ranges is ", $objPHPExcel->getActiveSheet()->getCell('B7')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('A8', 'Minimum of both ranges:'); $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C4)'); echo date('H:i:s'), " Minimum value in either Range is ", $objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('A9', 'Maximum of both ranges:'); $objPHPExcel->getActiveSheet()->setCellValue('B9', '=MAX(B2:C4)'); echo date('H:i:s'), " Maximum value in either Range is ", $objPHPExcel->getActiveSheet()->getCell('B9')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('A10', 'Average of both ranges:'); $objPHPExcel->getActiveSheet()->setCellValue('B10', '=AVERAGE(B2:C4)');
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet echo date('H:i:s'), " Add data and page breaks", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname")->setCellValue('B1', "Lastname")->setCellValue('C1', "Phone")->setCellValue('D1', "Fax")->setCellValue('E1', "Is Client ?"); // Add data for ($i = 2; $i <= 50; $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "FName {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, "LName {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, "PhoneNo {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, "FaxNo {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, true); // Add page breaks every 10 rows if ($i % 10 == 0) { // Add a page break $objPHPExcel->getActiveSheet()->setBreak('A' . $i, \PHPExcel\Worksheet::BREAK_ROW); } } // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0);
// and its directory location on your server //$rendererName = PHPExcel\Settings::PDF_RENDERER_TCPDF; $rendererName = PHPExcel\Settings::PDF_RENDERER_MPDF; //$rendererName = PHPExcel\Settings::PDF_RENDERER_DOMPDF; //$rendererLibrary = 'tcPDF5.9'; $rendererLibrary = 'mPDF5.4'; //$rendererLibrary = 'domPDF0.6.0beta3'; $rendererLibraryPath = dirname(__FILE__) . '/../../../libraries/PDF/' . $rendererLibrary; // Create new PHPExcel object $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("PDF Test Document")->setSubject("PDF Test Document")->setDescription("Test document for PDF, generated using PHP classes.")->setKeywords("pdf php")->setCategory("Test result file"); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!'); // Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); $objPHPExcel->getActiveSheet()->setShowGridLines(false); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); if (!PHPExcel\Settings::setPdfRenderer($rendererName, $rendererLibraryPath)) { die('NOTICE: Please set the $rendererName and $rendererLibraryPath values' . '<br />' . 'at the top of this script as appropriate for your directory structure'); } // Redirect output to a client’s web browser (PDF) header('Content-Type: application/pdf'); header('Content-Disposition: attachment;filename="01simple.pdf"'); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'PDF'); $objWriter->save('php://output'); exit;
/** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $sharedStyle1 = new \PHPExcel\Style(); $sharedStyle2 = new \PHPExcel\Style(); $sharedStyle1->applyFromArray(array('fill' => array('type' => \PHPExcel\Style\Fill::FILL_SOLID, 'color' => array('argb' => 'FFCCFFCC')), 'borders' => array('bottom' => array('style' => \PHPExcel\Style\Border::BORDER_THIN), 'right' => array('style' => \PHPExcel\Style\Border::BORDER_MEDIUM)))); $sharedStyle2->applyFromArray(array('fill' => array('type' => \PHPExcel\Style\Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')), 'borders' => array('bottom' => array('style' => \PHPExcel\Style\Border::BORDER_THIN), 'right' => array('style' => \PHPExcel\Style\Border::BORDER_MEDIUM)))); $objPHPExcel->getActiveSheet()->duplicateStyle($sharedStyle1, "A1:T100"); $objPHPExcel->getActiveSheet()->duplicateStyle($sharedStyle2, "C5:R95"); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true); $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; echo 'Call time to write Workbook was ', sprintf('%.4f', $callTime), " seconds", EOL; // Echo memory usage echo date('H:i:s'), ' Current memory usage: ', memory_get_usage(true) / 1024 / 1024, " MB", EOL; // Save Excel 95 file echo date('H:i:s'), " Write to Excel5 format", EOL; $callStartTime = microtime(true);
'; $html2 = '<p> <font color="#ff0000"> 100°C is a hot temperature </font> <br> <font color="#0080ff"> 10°F is cold </font> </p>'; $html3 = '2<sup>3</sup> equals 8'; $html4 = 'H<sub>2</sub>SO<sub>4</sub> is the chemical formula for Sulphuric acid'; $html5 = '<strong>bold</strong>, <em>italic</em>, <strong><em>bold+italic</em></strong>'; $wizard = new \PHPExcel\Helper\HTML(); $richText = $wizard->toRichTextObject($html1); $objPHPExcel->getActiveSheet()->setCellValue('A1', $richText); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(48); $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true); $richText = $wizard->toRichTextObject($html2); $objPHPExcel->getActiveSheet()->setCellValue('A2', $richText); $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1); $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setWrapText(true); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', $wizard->toRichTextObject($html3)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', $wizard->toRichTextObject($html4)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A5', $wizard->toRichTextObject($html5)); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Rich Text Examples'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0);
ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')->setCellValue('A2', 'Lastname:')->setCellValue('A3', 'Fullname:')->setCellValue('B1', 'Maarten')->setCellValue('B2', 'Balliauw')->setCellValue('B3', '=B1 & " " & B2'); // Define named ranges echo date('H:i:s'), " Define named ranges", EOL; $objPHPExcel->addNamedRange(new \PHPExcel\NamedRange('PersonName', $objPHPExcel->getActiveSheet(), 'B1')); $objPHPExcel->addNamedRange(new \PHPExcel\NamedRange('PersonLN', $objPHPExcel->getActiveSheet(), 'B2')); // Rename named ranges echo date('H:i:s'), " Rename named ranges", EOL; $objPHPExcel->getNamedRange('PersonName')->setName('PersonFN'); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Person'); // Create a new worksheet, after the default sheet echo date('H:i:s'), " Create new Worksheet object", EOL; $objPHPExcel->createSheet(); // Add some data to the second sheet, resembling some different data types echo date('H:i:s'), " Add some data", EOL;
date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Set default font echo date('H:i:s'), " Set default font", EOL; $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10); // Add some data, resembling some different data types echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String')->setCellValue('B1', 'Simple')->setCellValue('C1', 'PHPExcel'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'String')->setCellValue('B2', 'Symbols')->setCellValue('C2', '!+&=()~§±æþ'); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'String')->setCellValue('B3', 'UTF-8')->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов'); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number')->setCellValue('B4', 'Integer')->setCellValue('C4', 12); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Number')->setCellValue('B5', 'Float')->setCellValue('C5', 34.56); $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Number')->setCellValue('B6', 'Negative')->setCellValue('C6', -7.89); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Boolean')->setCellValue('B7', 'True')->setCellValue('C7', true); $objPHPExcel->getActiveSheet()->setCellValue('A8', 'Boolean')->setCellValue('B8', 'False')->setCellValue('C8', false); $dateTimeNow = time(); $objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')->setCellValue('B9', 'Date')->setCellValue('C9', \PHPExcel\Shared\Date::PHPToExcel($dateTimeNow)); $objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); $objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', \PHPExcel\Shared\Date::PHPToExcel($dateTimeNow)); $objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_TIME4); $objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', \PHPExcel\Shared\Date::PHPToExcel($dateTimeNow)); $objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_DATETIME); $objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL);
{ return array($value); } // Add some data $continentColumn = 'D'; $column = 'F'; // Set data for dropdowns 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); // Transpose $countries from a row to a column array $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; } // Hide the dropdown data $objPHPExcel->getActiveSheet()->getColumnDimension($continentColumn)->setVisible(false); $objPHPExcel->addNamedRange(new PHPExcel\NamedRange('Continents', $objPHPExcel->getActiveSheet(), $continentColumn . '1:' . $continentColumn . ($key + 1))); // Set selection cells $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); // Set linked validators
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties 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"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Set the page layout view as page layout $objPHPExcel->getActiveSheet()->getSheetView()->setView(PHPExcel\Worksheet\SheetView::SHEETVIEW_PAGE_LAYOUT); // Save Excel 2007 file 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; // Save Excel5 file echo date('H:i:s'), " Write to Excel5 format", EOL; $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save(str_replace('.php', '.xls', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL; // Echo done echo date('H:i:s'), " Done writing files", EOL; echo 'Files have been created in ', getcwd(), EOL;
ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // List functions echo date('H:i:s') . " List implemented functions\n"; $objCalc = \PHPExcel\Calculation::getInstance(); print_r($objCalc->listFunctionNames()); // Create new PHPExcel object echo date('H:i:s') . " Create new PHPExcel object\n"; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Add some data, we will use some formulas here echo date('H:i:s') . " Add some data\n"; $objPHPExcel->getActiveSheet()->setCellValue('A14', 'Count:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range 1'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 2); $objPHPExcel->getActiveSheet()->setCellValue('B3', 8); $objPHPExcel->getActiveSheet()->setCellValue('B4', 10); $objPHPExcel->getActiveSheet()->setCellValue('B5', True); $objPHPExcel->getActiveSheet()->setCellValue('B6', False); $objPHPExcel->getActiveSheet()->setCellValue('B7', 'Text String'); $objPHPExcel->getActiveSheet()->setCellValue('B9', '22'); $objPHPExcel->getActiveSheet()->setCellValue('B10', 4); $objPHPExcel->getActiveSheet()->setCellValue('B11', 6); $objPHPExcel->getActiveSheet()->setCellValue('B12', 12); $objPHPExcel->getActiveSheet()->setCellValue('B14', '=COUNT(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Range 2'); $objPHPExcel->getActiveSheet()->setCellValue('C2', 1); $objPHPExcel->getActiveSheet()->setCellValue('C3', 2);
date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->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"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!'); // Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); $objPHPExcel->getActiveSheet()->setCellValue('A8', "Hello\nWorld"); $objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1); $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true); // Use PCLZip rather than ZipArchive to create the Excel2007 OfficeOpenXML file PHPExcel\Settings::setZipClass(PHPExcel\Settings::PCLZIP); $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); $callEndTime = microtime(true);
*/ /** Error reporting */ 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 />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data and formulas", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A1', '=B1')->setCellValue('A2', '=B2+1')->setCellValue('B1', '=A1+1')->setCellValue('B2', '=A2'); \PHPExcel\Calculation::getInstance($objPHPExcel)->cyclicFormulaCount = 100; // Calculated data echo date('H:i:s'), " Calculated data", EOL; for ($row = 1; $row <= 2; ++$row) { for ($col = 'A'; $col != 'C'; ++$col) { if (!is_null($formula = $objPHPExcel->getActiveSheet()->getCell($col . $row)->getValue()) && $formula[0] == '=') { echo 'Value of ', $col, $row, ' [', $formula, ']: ', $objPHPExcel->getActiveSheet()->getCell($col . $row)->getCalculatedValue() . EOL; } } } // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true); $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); //
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!'); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello'); $objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!'); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set document security echo date('H:i:s'), " Set document security", EOL; $objPHPExcel->getSecurity()->setLockWindows(true); $objPHPExcel->getSecurity()->setLockStructure(true); $objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel"); // Set sheet security echo date('H:i:s'), " Set sheet security", EOL; $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel'); $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
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 />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s') . ' Create new PHPExcel object' . EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s') . ' Set document properties' . EOL; $objPHPExcel->getProperties()->setCreator('Maarten Balliauw')->setLastModifiedBy('Maarten Balliauw')->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'); // Create the worksheet echo date('H:i:s') . ' Add data' . EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Financial Year')->setCellValue('B1', 'Financial Period')->setCellValue('C1', 'Country')->setCellValue('D1', 'Date')->setCellValue('E1', 'Sales Value')->setCellValue('F1', 'Expenditure'); $startYear = $endYear = $currentYear = date('Y'); $startYear--; $endYear++; $years = range($startYear, $endYear); $periods = range(1, 12); $countries = array('United States', 'UK', 'France', 'Germany', 'Italy', 'Spain', 'Portugal', 'Japan'); $row = 2; foreach ($years as $year) { foreach ($periods as $period) { foreach ($countries as $country) { $endDays = date('t', mktime(0, 0, 0, $period, 1, $year)); for ($i = 1; $i <= $endDays; ++$i) { $eDate = \PHPExcel\Shared\Date::formattedPHPToExcel($year, $period, $i); $value = rand(500, 1000) * (1 + rand(-0.25, +0.25)); $salesValue = $invoiceValue = NULL;
// Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Generate an image echo date('H:i:s'), " Generate an image", EOL; $gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream'); $textColor = imagecolorallocate($gdImage, 255, 255, 255); imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel', $textColor); // Add a drawing to the worksheet echo date('H:i:s'), " Add a drawing to the worksheet", EOL; $objDrawing = new \PHPExcel\Worksheet\MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(\PHPExcel\Worksheet\MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(\PHPExcel\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(36); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); 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'), " Write to HTML format", EOL; $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'HTML'); $objWriter->save(str_replace('.php', '.html', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.html', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL; // Echo done echo date('H:i:s'), " Done writing file", EOL; echo 'Files have been created in ', getcwd(), EOL;
* Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * * @category PHPExcel * @package PHPExcel * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version ##VERSION##, ##DATE## */ /** PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; $objPHPExcel = new \PHPExcel\Spreadsheet(); $objWorksheet = $objPHPExcel->getActiveSheet(); $objWorksheet->fromArray(array(array('', 2010, 2011, 2012), array('Q1', 12, 15, 21), array('Q2', 56, 73, 86), array('Q3', 52, 61, 69), array('Q4', 30, 32, 0))); // Set the Labels for each data series we want to plot // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $dataSeriesLabels1 = array(new \PHPExcel\Chart\DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1)); // Set the X-Axis Labels // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Mark Baker")->setLastModifiedBy("Mark Baker")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Crouching'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Tiger'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Hidden'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Dragon'); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set document security echo date('H:i:s'), " Set cell protection", EOL; // Set sheet security echo date('H:i:s'), " Set sheet security", EOL; $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); $objPHPExcel->getActiveSheet()->getStyle('A2:B2')->getProtection()->setLocked(\PHPExcel\Style\Protection::PROTECTION_UNPROTECTED); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s') . ' Create new PHPExcel object' . EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s') . ' Set document properties' . EOL; $objPHPExcel->getProperties()->setCreator('Maarten Balliauw')->setLastModifiedBy('Maarten Balliauw')->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'); // Create the worksheet echo date('H:i:s') . ' Add data' . EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Year')->setCellValue('B1', 'Quarter')->setCellValue('C1', 'Country')->setCellValue('D1', 'Sales'); $dataArray = array(array('2010', 'Q1', 'United States', 790), array('2010', 'Q2', 'United States', 730), array('2010', 'Q3', 'United States', 860), array('2010', 'Q4', 'United States', 850), array('2011', 'Q1', 'United States', 800), array('2011', 'Q2', 'United States', 700), array('2011', 'Q3', 'United States', 900), array('2011', 'Q4', 'United States', 950), array('2010', 'Q1', 'Belgium', 380), array('2010', 'Q2', 'Belgium', 390), array('2010', 'Q3', 'Belgium', 420), array('2010', 'Q4', 'Belgium', 460), array('2011', 'Q1', 'Belgium', 400), array('2011', 'Q2', 'Belgium', 350), array('2011', 'Q3', 'Belgium', 450), array('2011', 'Q4', 'Belgium', 500), array('2010', 'Q1', 'UK', 690), array('2010', 'Q2', 'UK', 610), array('2010', 'Q3', 'UK', 620), array('2010', 'Q4', 'UK', 600), array('2011', 'Q1', 'UK', 720), array('2011', 'Q2', 'UK', 650), array('2011', 'Q3', 'UK', 580), array('2011', 'Q4', 'UK', 510), array('2010', 'Q1', 'France', 510), array('2010', 'Q2', 'France', 490), array('2010', 'Q3', 'France', 460), array('2010', 'Q4', 'France', 590), array('2011', 'Q1', 'France', 620), array('2011', 'Q2', 'France', 650), array('2011', 'Q3', 'France', 415), array('2011', 'Q4', 'France', 570), array('2010', 'Q1', 'Germany', 720), array('2010', 'Q2', 'Germany', 680), array('2010', 'Q3', 'Germany', 640), array('2010', 'Q4', 'Germany', 660), array('2011', 'Q1', 'Germany', 680), array('2011', 'Q2', 'Germany', 620), array('2011', 'Q3', 'Germany', 710), array('2011', 'Q4', 'Germany', 690), array('2010', 'Q1', 'Spain', 510), array('2010', 'Q2', 'Spain', 490), array('2010', 'Q3', 'Spain', 470), array('2010', 'Q4', 'Spain', 420), array('2011', 'Q1', 'Spain', 460), array('2011', 'Q2', 'Spain', 390), array('2011', 'Q3', 'Spain', 430), array('2011', 'Q4', 'Spain', 415), array('2010', 'Q1', 'Italy', 440), array('2010', 'Q2', 'Italy', 410), array('2010', 'Q3', 'Italy', 420), array('2010', 'Q4', 'Italy', 450), array('2011', 'Q1', 'Italy', 430), array('2011', 'Q2', 'Italy', 370), array('2011', 'Q3', 'Italy', 350), array('2011', 'Q4', 'Italy', 335)); $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A2'); // Set title row bold echo date('H:i:s') . ' Set title row bold' . EOL; $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true); // Set autofilter echo date('H:i:s') . ' Set autofilter' . EOL; // Always include the complete filter range! // Excel does support setting only the caption // row, but that's not a best practise... $objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension()); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL;
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Add some data, we will use printing features echo date('H:i:s'), " Add some data", EOL; for ($i = 1; $i < 200; $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value'); } // Set header and footer. When no different headers for odd/even are used, odd header is assumed. echo date('H:i:s'), " Set header/footer", EOL; $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HPlease treat this document as confidential!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); // Add a drawing to the header echo date('H:i:s'), " Add a drawing to the header", EOL; $objDrawing = new \PHPExcel\Worksheet\HeaderFooterDrawing(); $objDrawing->setName('PHPExcel logo'); $objDrawing->setPath('./images/phpexcel_logo.gif'); $objDrawing->setHeight(36); $objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, \PHPExcel\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT); // Set page orientation and size echo date('H:i:s'), " Set page orientation and size", EOL;