mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-24 18:18:25 +00:00
Mark Baker
4a04499bff
* Allow single-cell checks on conditional styles, even when the style is configured for a range of cells * Work on the CellMatcher logic to evaluate Conditionals for a cell based on its value, and identify which conditional styles should be applied * Refactor style merging and cell matching for conditional formatting into separate classes; this should make it easier to test, and easier to extend for other CF expressions subsequently * Added support for containsErrors and notContainsErrors * Initial work on a wizard to help simplify created Conditional Formatting rules, to ensure that the correct expressions are set * Further work on extending the Conditional Formatting rules to cover more of the options that are available in MS Excel * Prevent phpcs-fixer from removing class @method annotations, used to identify the signature for magic methods used in Wizard classes * Implement `fromConditional()`` method to allow the creation of a CF Wizard from an existing Conditional * Ensure that xlsx Reader picks up the timePeriod attribute for DatesOccurring CF Rules * Allow Duplicates/Uniques CF Rules to be recognised in the Xlsx Reader * Basic Xlsx reading of CF Rules/Styles from <extLst><ext><ConditinalFormattings> element, and not just the <ConditinalFormatting> element of the worksheet * Add some validation for operands passed to the CF Wizards - remove any leading ``=` from formulae, because they'll be embedded into other formulae - unwrap any string literals from quotes, because that's also handled internally Handle cross-worksheet cell references in cellReferences and Formulae/Expressions * re-baseline phpstan * Update Change Log with details of the CF Improvements
118 lines
5.5 KiB
PHP
118 lines
5.5 KiB
PHP
<?php
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Reader\Xlsx;
|
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
use PhpOffice\PhpSpreadsheet\Shared\File;
|
|
use PhpOffice\PhpSpreadsheet\Style\Color;
|
|
use PhpOffice\PhpSpreadsheet\Style\Conditional;
|
|
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class Xlsx2Test extends TestCase
|
|
{
|
|
public function testLoadXlsxConditionalFormatting2(): void
|
|
{
|
|
// Make sure Conditionals are read correctly from existing file
|
|
$filename = 'tests/data/Reader/XLSX/conditionalFormatting2Test.xlsx';
|
|
$reader = IOFactory::createReader('Xlsx');
|
|
$spreadsheet = $reader->load($filename);
|
|
$worksheet = $spreadsheet->getActiveSheet();
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('A2:A8');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_NOTCONTAINSBLANKS, $conditionalRule->getConditionType());
|
|
self::assertEquals('LEN(TRIM(A2))>0', $conditions[0]);
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('B2:B8');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_CONTAINSBLANKS, $conditionalRule->getConditionType());
|
|
self::assertEquals('LEN(TRIM(B2))=0', $conditions[0]);
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('C2:C8');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_CELLIS, $conditionalRule->getConditionType());
|
|
self::assertEquals(Conditional::OPERATOR_GREATERTHAN, $conditionalRule->getOperatorType());
|
|
self::assertEquals('5', $conditions[0]);
|
|
}
|
|
|
|
public function testReloadXlsxConditionalFormatting2(): void
|
|
{
|
|
// Make sure conditionals from existing file are maintained across save
|
|
$filename = 'tests/data/Reader/XLSX/conditionalFormatting2Test.xlsx';
|
|
$outfile = File::temporaryFilename();
|
|
$reader = IOFactory::createReader('Xlsx');
|
|
$spreadshee1 = $reader->load($filename);
|
|
$writer = IOFactory::createWriter($spreadshee1, 'Xlsx');
|
|
$writer->save($outfile);
|
|
$spreadsheet = $reader->load($outfile);
|
|
unlink($outfile);
|
|
$worksheet = $spreadsheet->getActiveSheet();
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('A2:A8');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_NOTCONTAINSBLANKS, $conditionalRule->getConditionType());
|
|
self::assertEquals('LEN(TRIM(A2))>0', $conditions[0]);
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('B2:B8');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_CONTAINSBLANKS, $conditionalRule->getConditionType());
|
|
self::assertEquals('LEN(TRIM(B2))=0', $conditions[0]);
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('C2:C8');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_CELLIS, $conditionalRule->getConditionType());
|
|
self::assertEquals(Conditional::OPERATOR_GREATERTHAN, $conditionalRule->getOperatorType());
|
|
self::assertEquals('5', $conditions[0]);
|
|
}
|
|
|
|
public function testNewXlsxConditionalFormatting2(): void
|
|
{
|
|
// Make sure blanks/non-blanks added by PhpSpreadsheet are handled correctly
|
|
$outfile = File::temporaryFilename();
|
|
$spreadshee1 = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
|
|
$sheet = $spreadshee1->getActiveSheet();
|
|
$sheet->setCellValue('A2', 'a2');
|
|
$sheet->setCellValue('A4', 'a4');
|
|
$sheet->setCellValue('A6', 'a6');
|
|
$cond1 = new Conditional();
|
|
$cond1->setConditionType(Conditional::CONDITION_CONTAINSBLANKS);
|
|
$cond1->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
|
|
$cond1->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_RED);
|
|
$cond = [$cond1];
|
|
$sheet->getStyle('A1:A6')->setConditionalStyles($cond);
|
|
$writer = IOFactory::createWriter($spreadshee1, 'Xlsx');
|
|
$writer->save($outfile);
|
|
$reader = IOFactory::createReader('Xlsx');
|
|
$spreadsheet = $reader->load($outfile);
|
|
unlink($outfile);
|
|
$worksheet = $spreadsheet->getActiveSheet();
|
|
|
|
$conditionalStyle = $worksheet->getConditionalStyles('A1:A6');
|
|
self::assertNotEmpty($conditionalStyle);
|
|
$conditionalRule = $conditionalStyle[0];
|
|
$conditions = $conditionalRule->getConditions();
|
|
self::assertNotEmpty($conditions);
|
|
self::assertEquals(Conditional::CONDITION_CONTAINSBLANKS, $conditionalRule->getConditionType());
|
|
self::assertEquals('LEN(TRIM(A1))=0', $conditions[0]);
|
|
}
|
|
}
|