<?php
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date;
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
|
use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column;
|
use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule;
|
|
require __DIR__ . '/../Header.php';
|
|
// Create new Spreadsheet object
|
$helper->log('Create new Spreadsheet object');
|
$spreadsheet = new Spreadsheet();
|
|
// Set document properties
|
$helper->log('Set document properties');
|
$spreadsheet->getProperties()->setCreator('Maarten Balliauw')
|
->setLastModifiedBy('Maarten Balliauw')
|
->setTitle('PhpSpreadsheet Test Document')
|
->setSubject('PhpSpreadsheet Test Document')
|
->setDescription('Test document for PhpSpreadsheet, generated using PHP classes.')
|
->setKeywords('office PhpSpreadsheet php')
|
->setCategory('Test result file');
|
|
// Create the worksheet
|
$helper->log('Add data');
|
$spreadsheet->setActiveSheetIndex(0);
|
$spreadsheet->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 = [
|
'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, (int) $year));
|
for ($i = 1; $i <= $endDays; ++$i) {
|
$eDate = Date::formattedPHPToExcel(
|
$year,
|
$period,
|
$i
|
);
|
$value = rand(500, 1000) * (1 + (rand(-1, 1) / 4));
|
$salesValue = $invoiceValue = null;
|
$incomeOrExpenditure = rand(-1, 1);
|
if ($incomeOrExpenditure == -1) {
|
$expenditure = rand(-500, -1000) * (1 + (rand(-1, 1) / 4));
|
$income = null;
|
} elseif ($incomeOrExpenditure == 1) {
|
$expenditure = rand(-500, -1000) * (1 + (rand(-1, 1) / 4));
|
$income = rand(500, 1000) * (1 + (rand(-1, 1) / 4));
|
} else {
|
$expenditure = null;
|
$income = rand(500, 1000) * (1 + (rand(-1, 1) / 4));
|
}
|
$dataArray = [$year,
|
$period,
|
$country,
|
$eDate,
|
$income,
|
$expenditure,
|
];
|
$spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A' . $row++);
|
}
|
}
|
}
|
}
|
--$row;
|
|
// Set styling
|
$helper->log('Set styling');
|
$spreadsheet->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);
|
$spreadsheet->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setWrapText(true);
|
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12.5);
|
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10.5);
|
$spreadsheet->getActiveSheet()->getStyle('D2:D' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD2);
|
$spreadsheet->getActiveSheet()->getStyle('E2:F' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
|
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(14);
|
$spreadsheet->getActiveSheet()->freezePane('A2');
|
|
// Set autofilter range
|
$helper->log('Set autofilter range');
|
// Always include the complete filter range!
|
// Excel does support setting only the caption
|
// row, but that's not a best practise...
|
$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());
|
|
// Set active filters
|
$autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter();
|
$helper->log('Set active filters');
|
// 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(Column::AUTOFILTER_FILTERTYPE_FILTER)
|
->createRule()
|
->setRule(
|
Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
|
'Germany'
|
);
|
// Filter the Date column on a filter value of the year to date
|
$autoFilter->getColumn('D')
|
->setFilterType(Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER)
|
->createRule()
|
->setRule(
|
Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
|
null,
|
Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE
|
)
|
->setRuleType(Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
|
// Display only sales values that are between 400 and 600
|
$autoFilter->getColumn('E')
|
->setFilterType(Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER)
|
->createRule()
|
->setRule(
|
Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,
|
400
|
)
|
->setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
|
$autoFilter->getColumn('E')
|
->setJoin(Column::AUTOFILTER_COLUMN_JOIN_AND)
|
->createRule()
|
->setRule(
|
Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,
|
600
|
)
|
->setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
|
|
// Save
|
$helper->write($spreadsheet, __FILE__);
|
