vendor/phpoffice/phpexcel/Examples/10autofilter-selection-2.php

   1 <?php
   2 /**
   3  * PHPExcel
   4  *
   5  * Copyright (c) 2006 - 2015 PHPExcel
   6  *
   7  * This library is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU Lesser General Public
   9  * License as published by the Free Software Foundation; either
  10  * version 2.1 of the License, or (at your option) any later version.
  11  *
  12  * This library is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15  * Lesser General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Lesser General Public
  18  * License along with this library; if not, write to the Free Software
  19  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20  *
  21  * @category   PHPExcel
  22  * @package    PHPExcel
  23  * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
  24  * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  25  * @version    ##VERSION##, ##DATE##
  26  */
  27
  28 /** Error reporting */
  29 error_reporting(E_ALL);
  30 ini_set('display_errors', TRUE);
  31 ini_set('display_startup_errors', TRUE);
  32 date_default_timezone_set('Europe/London');
  33
  34 define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
  35
  36 /** Include PHPExcel */
  37 require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
  38
  39
  40 // Create new PHPExcel object
  41 echo date('H:i:s').' Create new PHPExcel object'.EOL;
  42 $objPHPExcel = new PHPExcel();
  43
  44 // Set document properties
  45 echo date('H:i:s').' Set document properties'.EOL;
  46 $objPHPExcel->getProperties()->setCreator('Maarten Balliauw')
  47                                                          ->setLastModifiedBy('Maarten Balliauw')
  48                                                          ->setTitle('PHPExcel Test Document')
  49                                                          ->setSubject('PHPExcel Test Document')
  50                                                          ->setDescription('Test document for PHPExcel, generated using PHP classes.')
  51                                                          ->setKeywords('office PHPExcel php')
  52                                                          ->setCategory('Test result file');
  53
  54 // Create the worksheet
  55 echo date('H:i:s').' Add data'.EOL;
  56 $objPHPExcel->setActiveSheetIndex(0);
  57 $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Financial Year')
  58                               ->setCellValue('B1', 'Financial Period')
  59                               ->setCellValue('C1', 'Country')
  60                               ->setCellValue('D1', 'Date')
  61                               ->setCellValue('E1', 'Sales Value')
  62                               ->setCellValue('F1', 'Expenditure')
  63                               ;
  64 $startYear = $endYear = $currentYear = date('Y');
  65 $startYear--;
  66 $endYear++;
  67
  68 $years = range($startYear,$endYear);
  69 $periods = range(1,12);
  70 $countries = array(     'United States',        'UK',           'France',       'Germany',
  71                                         'Italy',                        'Spain',        'Portugal',     'Japan'
  72                                   );
  73
  74 $row = 2;
  75 foreach($years as $year) {
  76         foreach($periods as $period) {
  77                 foreach($countries as $country) {
  78                         $endDays = date('t',mktime(0,0,0,$period,1,$year));
  79                         for($i = 1; $i <= $endDays; ++$i) {
  80                                 $eDate = PHPExcel_Shared_Date::FormattedPHPToExcel(
  81                                         $year,
  82                                         $period,
  83                                         $i
  84                                 );
  85                                 $value = rand(500,1000) * (1 + rand(-0.25,+0.25));
  86                                 $salesValue = $invoiceValue = NULL;
  87                                 $incomeOrExpenditure = rand(-1,1);
  88                                 if ($incomeOrExpenditure == -1) {
  89                                         $expenditure = rand(-500,-1000) * (1 + rand(-0.25,+0.25));
  90                                         $income = NULL;
  91                                 } elseif ($incomeOrExpenditure == 1) {
  92                                         $expenditure = rand(-500,-1000) * (1 + rand(-0.25,+0.25));
  93                                         $income = rand(500,1000) * (1 + rand(-0.25,+0.25));;
  94                                 } else {
  95                                         $expenditure = NULL;
  96                                         $income = rand(500,1000) * (1 + rand(-0.25,+0.25));;
  97                                 }
  98                                 $dataArray = array(     $year,
  99                                                                         $period,
 100                                                                         $country,
 101                                                                         $eDate,
 102                                                                         $income,
 103                                                                         $expenditure,
 104                                                                   );
 105                                 $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A'.$row++);
 106                         }
 107                 }
 108         }
 109 }
 110 $row--;
 111
 112
 113 // Set styling
 114 echo date('H:i:s').' Set styling'.EOL;
 115 $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);
 116 $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setWrapText(TRUE);
 117 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12.5);
 118 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10.5);
 119 $objPHPExcel->getActiveSheet()->getStyle('D2:D'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
 120 $objPHPExcel->getActiveSheet()->getStyle('E2:F'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
 121 $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14);
 122 $objPHPExcel->getActiveSheet()->freezePane('A2');
 123
 124
 125
 126 // Set autofilter range
 127 echo date('H:i:s').' Set autofilter range'.EOL;
 128 // Always include the complete filter range!
 129 // Excel does support setting only the caption
 130 // row, but that's not a best practise...
 131 $objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
 132
 133 // Set active filters
 134 $autoFilter = $objPHPExcel->getActiveSheet()->getAutoFilter();
 135 echo date('H:i:s').' Set active filters'.EOL;
 136 // Filter the Country column on a filter value of Germany
 137 //      As it's just a simple value filter, we can use FILTERTYPE_FILTER
 138 $autoFilter->getColumn('C')
 139     ->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER)
 140     ->createRule()
 141                 ->setRule(
 142                         PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
 143                         'Germany'
 144                 );
 145 // Filter the Date column on a filter value of the year to date
 146 $autoFilter->getColumn('D')
 147         ->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER)
 148         ->createRule()
 149                 ->setRule(
 150                         PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
 151                         NULL,
 152                         PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE
 153                 )
 154                 ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
 155 // Display only sales values that are between 400 and 600
 156 $autoFilter->getColumn('E')
 157     ->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER)
 158     ->createRule()
 159                 ->setRule(
 160                         PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,
 161                         400
 162                 )
 163                 ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
 164 $autoFilter->getColumn('E')
 165     ->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND)
 166     ->createRule()
 167                 ->setRule(
 168                         PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,
 169                         600
 170                 )
 171                 ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
 172
 173
 174 // Set active sheet index to the first sheet, so Excel opens this as the first sheet
 175 $objPHPExcel->setActiveSheetIndex(0);
 176
 177
 178 // Save Excel 2007 file
 179 echo date('H:i:s') , " Write to Excel2007 format" , EOL;
 180 $callStartTime = microtime(true);
 181
 182 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 183 $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
 184 $callEndTime = microtime(true);
 185 $callTime = $callEndTime - $callStartTime;
 186
 187 echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
 188 echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
 189 // Echo memory usage
 190 echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
 191
 192
 193 // Save Excel 95 file
 194 echo date('H:i:s') , " Write to Excel5 format" , EOL;
 195 $callStartTime = microtime(true);
 196
 197 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 198 $objWriter->save(str_replace('.php', '.xls', __FILE__));
 199 $callEndTime = microtime(true);
 200 $callTime = $callEndTime - $callStartTime;
 201
 202 echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
 203 echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
 204 // Echo memory usage
 205 echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
 206
 207
 208 // Echo memory peak usage
 209 echo date('H:i:s').' Peak memory usage: '.(memory_get_peak_usage(true) / 1024 / 1024).' MB'.EOL;
 210
 211 // Echo done
 212 echo date('H:i:s').' Done writing files'.EOL;
 213 echo 'Files have been created in ' , getcwd() , EOL;