/**
* 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;
