PHPOffice /
PhpSpreadsheet
| Conditions | 4 |
| Paths | 6 |
| Total Lines | 85 |
| Code Lines | 73 |
| Lines | 0 |
| Ratio | 0 % |
| Changes | 1 | ||
| Bugs | 0 | Features | 0 |
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
| 1 | <?php |
||
| 13 | public function testXlfn(): void |
||
| 14 | { |
||
| 15 | $formulas = [ |
||
| 16 | // null indicates function not implemented in Calculation engine |
||
| 17 | ['2010', 'A1', '=MODE.SNGL({5.6,4,4,3,2,4})', '=_xlfn.MODE.SNGL({5.6,4,4,3,2,4})', 4], |
||
| 18 | ['2010', 'A2', '=MODE.SNGL({"x","y"})', '=_xlfn.MODE.SNGL({"x","y"})', '#N/A'], |
||
| 19 | ['2013', 'A1', '=ISOWEEKNUM("2019-12-19")', '=_xlfn.ISOWEEKNUM("2019-12-19")', 51], |
||
| 20 | ['2013', 'A2', '=SHEET("2019")', '=_xlfn.SHEET("2019")', null], |
||
| 21 | ['2013', 'A3', '2019-01-04', '2019-01-04', null], |
||
| 22 | ['2013', 'A4', '2019-07-04', '2019-07-04', null], |
||
| 23 | ['2013', 'A5', '2019-12-04', '2019-12-04', null], |
||
| 24 | ['2013', 'B3', 1, 1, null], |
||
| 25 | ['2013', 'B4', 2, 2, null], |
||
| 26 | ['2013', 'B5', -3, -3, null], |
||
| 27 | // multiple xlfn functions interleaved with non-xlfn |
||
| 28 | ['2013', 'C3', '=ISOWEEKNUM(A3)+WEEKNUM(A4)+ISOWEEKNUM(A5)', '=_xlfn.ISOWEEKNUM(A3)+WEEKNUM(A4)+_xlfn.ISOWEEKNUM(A5)', 77], |
||
| 29 | ['2016', 'A1', '=SWITCH(WEEKDAY("2019-12-22",1),1,"Sunday",2,"Monday","No Match")', '=_xlfn.SWITCH(WEEKDAY("2019-12-22",1),1,"Sunday",2,"Monday","No Match")', 'Sunday'], |
||
| 30 | ['2016', 'B1', '=SWITCH(WEEKDAY("2019-12-20",1),1,"Sunday",2,"Monday","No Match")', '=_xlfn.SWITCH(WEEKDAY("2019-12-20",1),1,"Sunday",2,"Monday","No Match")', 'No Match'], |
||
| 31 | ['2019', 'A1', '=CONCAT("The"," ","sun"," ","will"," ","come"," ","up"," ","tomorrow.")', '=_xlfn.CONCAT("The"," ","sun"," ","will"," ","come"," ","up"," ","tomorrow.")', 'The sun will come up tomorrow.'], |
||
| 32 | ]; |
||
| 33 | $workbook = new Spreadsheet(); |
||
| 34 | $sheet = $workbook->getActiveSheet(); |
||
| 35 | $sheet->setTitle('2010'); |
||
| 36 | $sheet = $workbook->createSheet(); |
||
| 37 | $sheet->setTitle('2013'); |
||
| 38 | $sheet = $workbook->createSheet(); |
||
| 39 | $sheet->setTitle('2016'); |
||
| 40 | $sheet = $workbook->createSheet(); |
||
| 41 | $sheet->setTitle('2019'); |
||
| 42 | |||
| 43 | foreach ($formulas as $values) { |
||
| 44 | $sheet = $workbook->setActiveSheetIndexByName($values[0]); |
||
| 45 | $sheet->setCellValue($values[1], $values[2]); |
||
| 46 | } |
||
| 47 | |||
| 48 | $sheet = $workbook->setActiveSheetIndexByName('2013'); |
||
| 49 | $sheet->getStyle('A3:A5')->getNumberFormat()->setFormatCode('yyyy-mm-dd'); |
||
| 50 | $sheet->getColumnDimension('A')->setAutoSize(true); |
||
| 51 | $condition0 = new Conditional(); |
||
| 52 | $condition0->setConditionType(Conditional::CONDITION_EXPRESSION); |
||
| 53 | $condition0->addCondition('ABS(B3)<2'); |
||
| 54 | $condition0->getStyle()->getFill()->setFillType(Fill::FILL_SOLID); |
||
| 55 | $condition0->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_RED); |
||
| 56 | $condition1 = new Conditional(); |
||
| 57 | $condition1->setConditionType(Conditional::CONDITION_EXPRESSION); |
||
| 58 | $condition1->addCondition('ABS(B3)>2'); |
||
| 59 | $condition1->getStyle()->getFill()->setFillType(Fill::FILL_SOLID); |
||
| 60 | $condition1->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_GREEN); |
||
| 61 | $cond = [$condition0, $condition1]; |
||
| 62 | $sheet->getStyle('B3:B5')->setConditionalStyles($cond); |
||
| 63 | $condition0 = new Conditional(); |
||
| 64 | $condition0->setConditionType(Conditional::CONDITION_EXPRESSION); |
||
| 65 | $condition0->addCondition('ISOWEEKNUM(A3)<10'); |
||
| 66 | $condition0->getStyle()->getFill()->setFillType(Fill::FILL_SOLID); |
||
| 67 | $condition0->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_RED); |
||
| 68 | $condition1 = new Conditional(); |
||
| 69 | $condition1->setConditionType(Conditional::CONDITION_EXPRESSION); |
||
| 70 | $condition1->addCondition('ISOWEEKNUM(A3)>40'); |
||
| 71 | $condition1->getStyle()->getFill()->setFillType(Fill::FILL_SOLID); |
||
| 72 | $condition1->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_GREEN); |
||
| 73 | $cond = [$condition0, $condition1]; |
||
| 74 | $sheet->getStyle('A3:A5')->setConditionalStyles($cond); |
||
| 75 | $sheet->setSelectedCell('B1'); |
||
| 76 | |||
| 77 | $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($workbook, 'Xlsx'); |
||
| 78 | $oufil = tempnam(File::sysGetTempDir(), 'phpspreadsheet-test'); |
||
| 79 | $writer->save($oufil); |
||
| 80 | |||
| 81 | $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); |
||
| 82 | $rdobj = $reader->load($oufil); |
||
| 83 | unlink($oufil); |
||
| 84 | foreach ($formulas as $values) { |
||
| 85 | $sheet = $rdobj->setActiveSheetIndexByName($values[0]); |
||
| 86 | self::assertEquals($values[3], $sheet->getCell($values[1])->getValue()); |
||
| 87 | if ($values[4] !== null) { |
||
| 88 | self::assertEquals($values[4], $sheet->getCell($values[1])->getCalculatedValue()); |
||
| 89 | } |
||
| 90 | } |
||
| 91 | $sheet = $rdobj->setActiveSheetIndexByName('2013'); |
||
| 92 | $cond = $sheet->getConditionalStyles('A3:A5'); |
||
| 93 | self::assertEquals('_xlfn.ISOWEEKNUM(A3)<10', $cond[0]->getConditions()[0]); |
||
| 94 | self::assertEquals('_xlfn.ISOWEEKNUM(A3)>40', $cond[1]->getConditions()[0]); |
||
| 95 | $cond = $sheet->getConditionalStyles('B3:B5'); |
||
| 96 | self::assertEquals('ABS(B3)<2', $cond[0]->getConditions()[0]); |
||
| 97 | self::assertEquals('ABS(B3)>2', $cond[1]->getConditions()[0]); |
||
| 98 | } |
||
| 100 |
