load($file); $writer = new HtmlWriter($spreadsheet); $writer->setConditionalFormatting(true); $this->data = $writer->generateHtmlAll(); $spreadsheet->disconnectWorksheets(); } private function extractCell(string $coordinate): string { [$column, $row] = Coordinate::indexesFromString($coordinate); --$column; --$row; // extract row into $matches $match = preg_match('~~s', $this->data, $matches); if ($match !== 1) { return 'unable to match row'; } $rowData = $matches[0]; // extract cell into $matches $match = preg_match('~Jan<', 'no conditional styling for B1'], ['F2', 'background-color:#C6EFCE;">120<', 'conditional style for F2'], ['H2', 'background-color:#FFEB9C;">90<', 'conditional style for H2'], ['F3', 'background-color:#C6EFCE;">70<', 'conditional style for cell F3'], ['H3', 'background-color:#FFEB9C;">60<', 'conditional style for cell H3'], ['F4', 'background-color:#C6EFCE;">1<', 'conditional style for cell F4'], ['L4', 'background-color:#FFC7CE;">5<', 'conditional style for cell L4'], ['F5', 'class="column5 style1 n">0<', 'no conditional styling for F5'], ]; foreach ($expectedMatches as $expected) { [$coordinate, $expectedString, $message] = $expected; $string = $this->extractCell($coordinate); self::assertStringContainsString($expectedString, $string, $message); } } public function testPercentages(): void { $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->fromArray([ [0.0014676309754806], [0.0058290134726582], [0.00031478256190473], [0.0075923486102745], [0.0041865730298472], ]); $sheet->getStyle('A1:A5') ->getNumberFormat() ->setFormatCode('0.00%'); $conditionals = []; $conditional1 = new Conditional(); $colorscale = new ConditionalColorScale(); $min = new ConditionalFormatValueObject('min'); $max = new ConditionalFormatValueObject('max'); $mid = new ConditionalFormatValueObject('percentile', 50); $colorscale ->setSqref('A1:A5', $sheet) ->setMinimumColor(new Color('83CCEB')) ->setMidpointColor(new Color('AEAEAE')) ->setMaximumColor(new Color('FF6CAD')) ->setMinimumConditionalFormatValueObject($min) ->setMidpointConditionalFormatValueObject($mid) ->setMaximumConditionalFormatValueObject($max) ->setScaleArray(); $conditional1->setColorScale($colorscale) ->setConditionType(Conditional::CONDITION_COLORSCALE); $conditionals = [$conditional1]; $sheet->getStyle('$A$1:$A$5') ->setConditionalStyles($conditionals); $writer = new HtmlWriter($spreadsheet); $writer->setConditionalFormatting(true); $html = $writer->generateHtmlAll(); self::assertStringContainsString('0.15%', $html); self::assertStringContainsString('0.58%', $html); self::assertStringContainsString('0.03%', $html); self::assertStringContainsString('0.76%', $html); self::assertStringContainsString('0.42%', $html); $spreadsheet->disconnectWorksheets(); } public function testPercentages2(): void { // fill color was being returned incorrectly // because some of the colors required leading 0. $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->fromArray([ [0.0014676309754806], [0.0058290134726582], [0.00031478256190473], [0.0075923486102745], [0.0041865730298472], ]); $sheet->getStyle('A1:A5') ->getNumberFormat() ->setFormatCode('0.00%'); $conditionals = []; $conditional1 = new Conditional(); $colorscale = new ConditionalColorScale(); $min = new ConditionalFormatValueObject('min'); $max = new ConditionalFormatValueObject('max'); $mid = new ConditionalFormatValueObject('percentile', 50); $colorscale ->setSqref('A1:A5', $sheet) ->setMinimumColor(new Color('FF0000')) ->setMidpointColor(new Color('00FF00')) ->setMaximumColor(new Color('0000FF')) ->setMinimumConditionalFormatValueObject($min) ->setMidpointConditionalFormatValueObject($mid) ->setMaximumConditionalFormatValueObject($max) ->setScaleArray(); $conditional1->setColorScale($colorscale) ->setConditionType(Conditional::CONDITION_COLORSCALE); $conditionals = [$conditional1]; $sheet->getStyle('$A$1:$A$5') ->setConditionalStyles($conditionals); $writer = new HtmlWriter($spreadsheet); $writer->setConditionalFormatting(true); $html = $writer->generateHtmlAll(); self::assertStringContainsString('0.15%', $html); self::assertStringContainsString('0.58%', $html); self::assertStringContainsString('0.03%', $html); self::assertStringContainsString('0.76%', $html); self::assertStringContainsString('0.42%', $html); $spreadsheet->disconnectWorksheets(); } public function testPercentagesNoMid(): void { $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->fromArray([ [0.0014676309754806], [0.0058290134726582], [0.00031478256190473], [0.0075923486102745], [0.0041865730298472], ]); $sheet->getStyle('A1:A5') ->getNumberFormat() ->setFormatCode('0.00%'); $conditionals = []; $conditional1 = new Conditional(); $colorscale = new ConditionalColorScale(); $min = new ConditionalFormatValueObject('min'); $max = new ConditionalFormatValueObject('max'); //$mid = new ConditionalFormatValueObject('percentile', 50); self::assertFalse($colorscale->colorScaleReadyForUse()); self::assertSame('FF000000', $colorscale->getColorForValue(0.0)); $colorscale ->setSqref('A1:A5', $sheet) ->setMinimumColor(new Color('FF0000')) //->setMidpointColor(new Color('00FF00')) ->setMaximumColor(new Color('0000FF')) ->setMinimumConditionalFormatValueObject($min) //->setMidpointConditionalFormatValueObject($mid) ->setMaximumConditionalFormatValueObject($max) ->setScaleArray(); self::assertSame('A1:A5', $colorscale->getSqRef()); $conditional1->setColorScale($colorscale) ->setConditionType(Conditional::CONDITION_COLORSCALE); $conditionals = [$conditional1]; $sheet->getStyle('$A$1:$A$5') ->setConditionalStyles($conditionals); $writer = new HtmlWriter($spreadsheet); $writer->setConditionalFormatting(true); $html = $writer->generateHtmlAll(); self::assertStringContainsString('0.15%', $html); self::assertStringContainsString('0.58%', $html); self::assertStringContainsString('0.03%', $html); self::assertStringContainsString('0.76%', $html); self::assertStringContainsString('0.42%', $html); $spreadsheet->disconnectWorksheets(); } }