$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); $objConditional1->getStyle()->getFont()->setBold(true); $objConditional1->getStyle()->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objConditional2 = new \PHPExcel\Style\Conditional(); $objConditional2->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_LESSTHAN)->addCondition('0'); $objConditional2->getStyle()->getFont()->getColor()->setARGB(\PHPExcel\Style\Color::COLOR_RED); $objConditional2->getStyle()->getFont()->setItalic(true); $objConditional2->getStyle()->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objConditional3 = new \PHPExcel\Style\Conditional(); $objConditional3->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_GREATERTHANOREQUAL)->addCondition('0'); $objConditional3->getStyle()->getFont()->getColor()->setARGB(\PHPExcel\Style\Color::COLOR_GREEN); $objConditional3->getStyle()->getFont()->setItalic(true); $objConditional3->getStyle()->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles(); array_push($conditionalStyles, $objConditional1); array_push($conditionalStyles, $objConditional2); array_push($conditionalStyles, $objConditional3); $objPHPExcel->getActiveSheet()->getStyle('B2')->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; $objPHPExcel->getActiveSheet()->duplicateConditionalStyle($objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles(), 'B3:B7'); // Set fonts echo date('H:i:s'), " Set fonts", EOL; $objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFont()->setBold(true); //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true);
// 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; $objPHPExcel->getActiveSheet()->duplicateConditionalStyle($objPHPExcel->getActiveSheet()->getStyle('A1')->getConditionalStyles(), 'A2:A8'); // 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;