| 1 |
<?php |
| 2 |
|
| 3 |
use PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
| 4 |
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
| 5 |
|
| 6 |
mt_srand(1234567890); |
| 7 |
|
| 8 |
require __DIR__ . '/../Header.php'; |
| 9 |
|
| 10 |
// List functions |
| 11 |
$helper->log('List implemented functions'); |
| 12 |
$calc = Calculation::getInstance(); |
| 13 |
print_r($calc->getImplementedFunctionNames()); |
| 14 |
|
| 15 |
// Create new Spreadsheet object |
| 16 |
$helper->log('Create new Spreadsheet object'); |
| 17 |
$spreadsheet = new Spreadsheet(); |
| 18 |
|
| 19 |
// Add some data, we will use some formulas here |
| 20 |
$helper->log('Add some data and formulas'); |
| 21 |
$spreadsheet->getActiveSheet()->setCellValue('A14', 'Count:') |
| 22 |
->setCellValue('A15', 'Sum:') |
| 23 |
->setCellValue('A16', 'Max:') |
| 24 |
->setCellValue('A17', 'Min:') |
| 25 |
->setCellValue('A18', 'Average:') |
| 26 |
->setCellValue('A19', 'Median:') |
| 27 |
->setCellValue('A20', 'Mode:'); |
| 28 |
|
| 29 |
$spreadsheet->getActiveSheet()->setCellValue('A22', 'CountA:') |
| 30 |
->setCellValue('A23', 'MaxA:') |
| 31 |
->setCellValue('A24', 'MinA:'); |
| 32 |
|
| 33 |
$spreadsheet->getActiveSheet()->setCellValue('A26', 'StDev:') |
| 34 |
->setCellValue('A27', 'StDevA:') |
| 35 |
->setCellValue('A28', 'StDevP:') |
| 36 |
->setCellValue('A29', 'StDevPA:'); |
| 37 |
|
| 38 |
$spreadsheet->getActiveSheet()->setCellValue('A31', 'DevSq:') |
| 39 |
->setCellValue('A32', 'Var:') |
| 40 |
->setCellValue('A33', 'VarA:') |
| 41 |
->setCellValue('A34', 'VarP:') |
| 42 |
->setCellValue('A35', 'VarPA:'); |
| 43 |
|
| 44 |
$spreadsheet->getActiveSheet()->setCellValue('A37', 'Date:'); |
| 45 |
|
| 46 |
$spreadsheet->getActiveSheet()->setCellValue('B1', 'Range 1') |
| 47 |
->setCellValue('B2', 2) |
| 48 |
->setCellValue('B3', 8) |
| 49 |
->setCellValue('B4', 10) |
| 50 |
->setCellValue('B5', true) |
| 51 |
->setCellValue('B6', false) |
| 52 |
->setCellValue('B7', 'Text String') |
| 53 |
->setCellValue('B9', '22') |
| 54 |
->setCellValue('B10', 4) |
| 55 |
->setCellValue('B11', 6) |
| 56 |
->setCellValue('B12', 12); |
| 57 |
|
| 58 |
$spreadsheet->getActiveSheet()->setCellValue('B14', '=COUNT(B2:B12)') |
| 59 |
->setCellValue('B15', '=SUM(B2:B12)') |
| 60 |
->setCellValue('B16', '=MAX(B2:B12)') |
| 61 |
->setCellValue('B17', '=MIN(B2:B12)') |
| 62 |
->setCellValue('B18', '=AVERAGE(B2:B12)') |
| 63 |
->setCellValue('B19', '=MEDIAN(B2:B12)') |
| 64 |
->setCellValue('B20', '=MODE(B2:B12)'); |
| 65 |
|
| 66 |
$spreadsheet->getActiveSheet()->setCellValue('B22', '=COUNTA(B2:B12)') |
| 67 |
->setCellValue('B23', '=MAXA(B2:B12)') |
| 68 |
->setCellValue('B24', '=MINA(B2:B12)'); |
| 69 |
|
| 70 |
$spreadsheet->getActiveSheet()->setCellValue('B26', '=STDEV(B2:B12)') |
| 71 |
->setCellValue('B27', '=STDEVA(B2:B12)') |
| 72 |
->setCellValue('B28', '=STDEVP(B2:B12)') |
| 73 |
->setCellValue('B29', '=STDEVPA(B2:B12)'); |
| 74 |
|
| 75 |
$spreadsheet->getActiveSheet()->setCellValue('B31', '=DEVSQ(B2:B12)') |
| 76 |
->setCellValue('B32', '=VAR(B2:B12)') |
| 77 |
->setCellValue('B33', '=VARA(B2:B12)') |
| 78 |
->setCellValue('B34', '=VARP(B2:B12)') |
| 79 |
->setCellValue('B35', '=VARPA(B2:B12)'); |
| 80 |
|
| 81 |
$spreadsheet->getActiveSheet()->setCellValue('B37', '=DATE(2007, 12, 21)') |
| 82 |
->setCellValue('B38', '=DATEDIF( DATE(2007, 12, 21), DATE(2007, 12, 22), "D" )') |
| 83 |
->setCellValue('B39', '=DATEVALUE("01-Feb-2006 10:06 AM")') |
| 84 |
->setCellValue('B40', '=DAY( DATE(2006, 1, 2) )') |
| 85 |
->setCellValue('B41', '=DAYS360( DATE(2002, 2, 3), DATE(2005, 5, 31) )'); |
| 86 |
|
| 87 |
$spreadsheet->getActiveSheet()->setCellValue('C1', 'Range 2') |
| 88 |
->setCellValue('C2', 1) |
| 89 |
->setCellValue('C3', 2) |
| 90 |
->setCellValue('C4', 2) |
| 91 |
->setCellValue('C5', 3) |
| 92 |
->setCellValue('C6', 3) |
| 93 |
->setCellValue('C7', 3) |
| 94 |
->setCellValue('C8', '0') |
| 95 |
->setCellValue('C9', 4) |
| 96 |
->setCellValue('C10', 4) |
| 97 |
->setCellValue('C11', 4) |
| 98 |
->setCellValue('C12', 4); |
| 99 |
|
| 100 |
$spreadsheet->getActiveSheet()->setCellValue('C14', '=COUNT(C2:C12)') |
| 101 |
->setCellValue('C15', '=SUM(C2:C12)') |
| 102 |
->setCellValue('C16', '=MAX(C2:C12)') |
| 103 |
->setCellValue('C17', '=MIN(C2:C12)') |
| 104 |
->setCellValue('C18', '=AVERAGE(C2:C12)') |
| 105 |
->setCellValue('C19', '=MEDIAN(C2:C12)') |
| 106 |
->setCellValue('C20', '=MODE(C2:C12)'); |
| 107 |
|
| 108 |
$spreadsheet->getActiveSheet()->setCellValue('C22', '=COUNTA(C2:C12)') |
| 109 |
->setCellValue('C23', '=MAXA(C2:C12)') |
| 110 |
->setCellValue('C24', '=MINA(C2:C12)'); |
| 111 |
|
| 112 |
$spreadsheet->getActiveSheet()->setCellValue('C26', '=STDEV(C2:C12)') |
| 113 |
->setCellValue('C27', '=STDEVA(C2:C12)') |
| 114 |
->setCellValue('C28', '=STDEVP(C2:C12)') |
| 115 |
->setCellValue('C29', '=STDEVPA(C2:C12)'); |
| 116 |
|
| 117 |
$spreadsheet->getActiveSheet()->setCellValue('C31', '=DEVSQ(C2:C12)') |
| 118 |
->setCellValue('C32', '=VAR(C2:C12)') |
| 119 |
->setCellValue('C33', '=VARA(C2:C12)') |
| 120 |
->setCellValue('C34', '=VARP(C2:C12)') |
| 121 |
->setCellValue('C35', '=VARPA(C2:C12)'); |
| 122 |
|
| 123 |
$spreadsheet->getActiveSheet()->setCellValue('D1', 'Range 3') |
| 124 |
->setCellValue('D2', 2) |
| 125 |
->setCellValue('D3', 3) |
| 126 |
->setCellValue('D4', 4); |
| 127 |
|
| 128 |
$spreadsheet->getActiveSheet()->setCellValue('D14', '=((D2 * D3) + D4) & " should be 10"'); |
| 129 |
|
| 130 |
$spreadsheet->getActiveSheet()->setCellValue('E12', 'Other functions') |
| 131 |
->setCellValue('E14', '=PI()') |
| 132 |
->setCellValue('E15', '=RAND()') |
| 133 |
->setCellValue('E16', '=RANDBETWEEN(5, 10)'); |
| 134 |
|
| 135 |
$spreadsheet->getActiveSheet()->setCellValue('E17', 'Count of both ranges:') |
| 136 |
->setCellValue('F17', '=COUNT(B2:C12)'); |
| 137 |
|
| 138 |
$spreadsheet->getActiveSheet()->setCellValue('E18', 'Total of both ranges:') |
| 139 |
->setCellValue('F18', '=SUM(B2:C12)'); |
| 140 |
|
| 141 |
$spreadsheet->getActiveSheet()->setCellValue('E19', 'Maximum of both ranges:') |
| 142 |
->setCellValue('F19', '=MAX(B2:C12)'); |
| 143 |
|
| 144 |
$spreadsheet->getActiveSheet()->setCellValue('E20', 'Minimum of both ranges:') |
| 145 |
->setCellValue('F20', '=MIN(B2:C12)'); |
| 146 |
|
| 147 |
$spreadsheet->getActiveSheet()->setCellValue('E21', 'Average of both ranges:') |
| 148 |
->setCellValue('F21', '=AVERAGE(B2:C12)'); |
| 149 |
|
| 150 |
$spreadsheet->getActiveSheet()->setCellValue('E22', 'Median of both ranges:') |
| 151 |
->setCellValue('F22', '=MEDIAN(B2:C12)'); |
| 152 |
|
| 153 |
$spreadsheet->getActiveSheet()->setCellValue('E23', 'Mode of both ranges:') |
| 154 |
->setCellValue('F23', '=MODE(B2:C12)'); |
| 155 |
|
| 156 |
// Calculated data |
| 157 |
$helper->log('Calculated data'); |
| 158 |
for ($col = 'B'; $col != 'G'; ++$col) { |
| 159 |
for ($row = 14; $row <= 41; ++$row) { |
| 160 |
if ((($formula = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue()) !== null) && |
| 161 |
($formula[0] == '=')) { |
| 162 |
$helper->log('Value of ' . $col . $row . ' [' . $formula . ']: ' . $spreadsheet->getActiveSheet()->getCell($col . $row)->getCalculatedValue()); |
| 163 |
} |
| 164 |
} |
| 165 |
} |
| 166 |
|
| 167 |
// |
| 168 |
// If we set Pre Calculated Formulas to true then PhpSpreadsheet will calculate all formulae in the |
| 169 |
// workbook before saving. This adds time and memory overhead, and can cause some problems with formulae |
| 170 |
// using functions or features (such as array formulae) that aren't yet supported by the calculation engine |
| 171 |
// If the value is false (the default) for the Xlsx Writer, then MS Excel (or the application used to |
| 172 |
// open the file) will need to recalculate values itself to guarantee that the correct results are available. |
| 173 |
// |
| 174 |
//$writer->setPreCalculateFormulas(true); |
| 175 |
// Save |
| 176 |
$helper->write($spreadsheet, __FILE__); |
| 177 |
|
