'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../Classes/PHPExcel.php'; // Create new PHPExcel object echo date('H:i:s').' Create new PHPExcel object'.EOL; $objPHPExcel = new PHPExcel(); // 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; $incomeOrExpenditure = rand(-1,1); if ($incomeOrExpenditure == -1) { $expenditure = rand(-500,-1000) * (1 + rand(-0.25,+0.25)); $income = NULL; } elseif ($incomeOrExpenditure == 1) { $expenditure = rand(-500,-1000) * (1 + rand(-0.25,+0.25)); $income = rand(500,1000) * (1 + rand(-0.25,+0.25));; } else { $expenditure = NULL; $income = rand(500,1000) * (1 + rand(-0.25,+0.25));; } $dataArray = array( $year, $period, $country, $eDate, $income, $expenditure, ); $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A'.$row++); } } } } $row--; // Set styling echo date('H:i:s').' Set styling'.EOL; $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setWrapText(TRUE); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12.5); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10.5); $objPHPExcel->getActiveSheet()->getStyle('D2:D'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); $objPHPExcel->getActiveSheet()->getStyle('E2:F'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14); $objPHPExcel->getActiveSheet()->freezePane('A2'); // Set autofilter range echo date('H:i:s').' Set autofilter range'.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 filters $autoFilter = $objPHPExcel->getActiveSheet()->getAutoFilter(); echo date('H:i:s').' Set active filters'.EOL; // Filter the Country column on a filter value of Germany // As it's just a simple value filter, we can use FILTERTYPE_FILTER $autoFilter->getColumn('C') ->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER) ->createRule() ->setRule( PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL, 'Germany' ); // Filter the Date column on a filter value of the year to date $autoFilter->getColumn('D') ->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER) ->createRule() ->setRule( PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL, NULL, PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE ) ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER); // Display only sales values that are between 400 and 600 $autoFilter->getColumn('E') ->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER) ->createRule() ->setRule( PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 400 ) ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); $autoFilter->getColumn('E') ->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND) ->createRule() ->setRule( PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL, 600 ) ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); // 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); $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); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save(str_replace('.php', '.xls', __FILE__)); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', 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; // 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;