Any chance PhpSpreadsheet can support data bar in Conditional format? #938

fdreamsu · 2019-03-19T05:01:22Z

This is a feature request. Current conditional format functions can not build excel like this.

Anyone knows how to do it with PHP?

MarkBaker · 2019-03-19T06:53:29Z

Looks like a sparkline to me, and no it isn't something that PHPSPreadsheet can do

fdreamsu · 2019-03-19T10:09:39Z

Well, I unzip an excel file and figure out dataBar code myself. I finnaly did it with some ugly coding, maybe I will optimize them some other day.
PhpOffice\PhpSpreadsheet\Style\Conditional:15

class Conditional implements IComparable
{
    // Condition types
...
    const CONDITION_DATABAR = 'dataBar'; // add by fdream, 2019/03/19
...

PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet:462

    private function writeConditionalFormatting(XMLWriter $objWriter, PhpspreadsheetWorksheet $pSheet)
    {
        $dataBars = [];
        // Conditional id
        $id = 1;

        // Loop through styles in the current worksheet
        foreach ($pSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
            foreach ($conditionalStyles as $conditional) {
                // WHY was this again?
                // if ($this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()) == '') {
                //    continue;
                // }
                if ($conditional->getConditionType() == Conditional::CONDITION_DATABAR) {
                    $dataBars[] = $cellCoordinate;
                } elseif ($conditional->getConditionType() != Conditional::CONDITION_NONE) {
                    // conditionalFormatting
                    $objWriter->startElement('conditionalFormatting');
                    $objWriter->writeAttribute('sqref', $cellCoordinate);

                    // cfRule
                    $objWriter->startElement('cfRule');
                    $objWriter->writeAttribute('type', $conditional->getConditionType());
                    $objWriter->writeAttribute('dxfId', $this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()));
                    $objWriter->writeAttribute('priority', $id++);

                    if (($conditional->getConditionType() == Conditional::CONDITION_CELLIS || $conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT)
                        && $conditional->getOperatorType() != Conditional::OPERATOR_NONE) {
                        $objWriter->writeAttribute('operator', $conditional->getOperatorType());
                    }

                    if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getText() !== null) {
                        $objWriter->writeAttribute('text', $conditional->getText());
                    }

                    if ($conditional->getStopIfTrue()) {
                        $objWriter->writeAttribute('stopIfTrue', '1');
                    }

                    if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_CONTAINSTEXT
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'NOT(ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . ')))');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_BEGINSWITH
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'LEFT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_ENDSWITH
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'RIGHT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_NOTCONTAINS
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . '))');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CELLIS
                        || $conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        || $conditional->getConditionType() == Conditional::CONDITION_EXPRESSION) {
                        foreach ($conditional->getConditions() as $formula) {
                            // Formula
                            $objWriter->writeElement('formula', $formula);
                        }
                    }

                    $objWriter->endElement();

                    $objWriter->endElement();
                }
            }
        }
        
        if (!empty($dataBars)) {
            // it won't work when there is more than 255 cells
            for ($l=count($dataBars), $i=0; $i < $l; $i += 255) {
                // conditionalFormatting
                $objWriter->startElement('conditionalFormatting');
                $objWriter->writeAttribute('sqref', implode(' ', array_slice($dataBars, $i, 255)));
                
                // cfRule
                $objWriter->startElement('cfRule');
                    $objWriter->writeAttribute('type', Conditional::CONDITION_DATABAR);
                    $objWriter->writeAttribute('priority', '1');
                
                    $objWriter->startElement('dataBar');
                    
                        $objWriter->startElement('cfvo');
                        $objWriter->writeAttribute('type', 'num');
                        $objWriter->writeAttribute('val', '0'); // min number here
                        $objWriter->endElement();
                        $objWriter->startElement('cfvo');
                        $objWriter->writeAttribute('type', 'num');
                        $objWriter->writeAttribute('val', '0.7'); // max number here
                        $objWriter->endElement();
                        $objWriter->startElement('color');
                        $objWriter->writeAttribute('rgb', 'FF3F3F3F'); // gradient color here. Still trying to figure out how solid color works
                        $objWriter->endElement();
                    
                    $objWriter->endElement();
                
                $objWriter->endElement();
                
                $objWriter->endElement();
            }
        }
    }

lfernandes · 2019-12-05T18:56:43Z

Well, I unzip an excel file and figure out dataBar code myself. I finnaly did it with some ugly coding, maybe I will optimize them some other day.
PhpOffice\PhpSpreadsheet\Style\Conditional:15

class Conditional implements IComparable
{
    // Condition types
...
    const CONDITION_DATABAR = 'dataBar'; // add by fdream, 2019/03/19
...

PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet:462

    private function writeConditionalFormatting(XMLWriter $objWriter, PhpspreadsheetWorksheet $pSheet)
    {
        $dataBars = [];
        // Conditional id
        $id = 1;

        // Loop through styles in the current worksheet
        foreach ($pSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
            foreach ($conditionalStyles as $conditional) {
                // WHY was this again?
                // if ($this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()) == '') {
                //    continue;
                // }
                if ($conditional->getConditionType() == Conditional::CONDITION_DATABAR) {
                    $dataBars[] = $cellCoordinate;
                } elseif ($conditional->getConditionType() != Conditional::CONDITION_NONE) {
                    // conditionalFormatting
                    $objWriter->startElement('conditionalFormatting');
                    $objWriter->writeAttribute('sqref', $cellCoordinate);

                    // cfRule
                    $objWriter->startElement('cfRule');
                    $objWriter->writeAttribute('type', $conditional->getConditionType());
                    $objWriter->writeAttribute('dxfId', $this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()));
                    $objWriter->writeAttribute('priority', $id++);

                    if (($conditional->getConditionType() == Conditional::CONDITION_CELLIS || $conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT)
                        && $conditional->getOperatorType() != Conditional::OPERATOR_NONE) {
                        $objWriter->writeAttribute('operator', $conditional->getOperatorType());
                    }

                    if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getText() !== null) {
                        $objWriter->writeAttribute('text', $conditional->getText());
                    }

                    if ($conditional->getStopIfTrue()) {
                        $objWriter->writeAttribute('stopIfTrue', '1');
                    }

                    if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_CONTAINSTEXT
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'NOT(ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . ')))');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_BEGINSWITH
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'LEFT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_ENDSWITH
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'RIGHT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        && $conditional->getOperatorType() == Conditional::OPERATOR_NOTCONTAINS
                        && $conditional->getText() !== null) {
                        $objWriter->writeElement('formula', 'ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . '))');
                    } elseif ($conditional->getConditionType() == Conditional::CONDITION_CELLIS
                        || $conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT
                        || $conditional->getConditionType() == Conditional::CONDITION_EXPRESSION) {
                        foreach ($conditional->getConditions() as $formula) {
                            // Formula
                            $objWriter->writeElement('formula', $formula);
                        }
                    }

                    $objWriter->endElement();

                    $objWriter->endElement();
                }
            }
        }
        
        if (!empty($dataBars)) {
            // it won't work when there is more than 255 cells
            for ($l=count($dataBars), $i=0; $i < $l; $i += 255) {
                // conditionalFormatting
                $objWriter->startElement('conditionalFormatting');
                $objWriter->writeAttribute('sqref', implode(' ', array_slice($dataBars, $i, 255)));
                
                // cfRule
                $objWriter->startElement('cfRule');
                    $objWriter->writeAttribute('type', Conditional::CONDITION_DATABAR);
                    $objWriter->writeAttribute('priority', '1');
                
                    $objWriter->startElement('dataBar');
                    
                        $objWriter->startElement('cfvo');
                        $objWriter->writeAttribute('type', 'num');
                        $objWriter->writeAttribute('val', '0'); // min number here
                        $objWriter->endElement();
                        $objWriter->startElement('cfvo');
                        $objWriter->writeAttribute('type', 'num');
                        $objWriter->writeAttribute('val', '0.7'); // max number here
                        $objWriter->endElement();
                        $objWriter->startElement('color');
                        $objWriter->writeAttribute('rgb', 'FF3F3F3F'); // gradient color here. Still trying to figure out how solid color works
                        $objWriter->endElement();
                    
                    $objWriter->endElement();
                
                $objWriter->endElement();
                
                $objWriter->endElement();
            }
        }
    }

Hello @fdreamsu , I'm trying to do the same, but it seems to don't be working anymore.

Can you please give me some help?

To apply i'm doing:

$conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_DATABAR);
$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('G4')->getConditionalStyles();
$conditionalStyles[] = $conditional1;
$spreadsheet->getActiveSheet()->getStyle('G4')->setConditionalStyles($conditionalStyles);

Thanks in advance!

Current result:

Expected:

cpnadal · 2019-12-06T12:38:10Z

Many thanks De : Lucas L Fernandes <[email protected]> Envoyé : jeudi 5 décembre 2019 19:57 À : PHPOffice/PhpSpreadsheet <[email protected]> Cc : Cyrille PIERRE-NADAL <[email protected]>; Manual <[email protected]> Objet : Re: [PHPOffice/PhpSpreadsheet] Any chance PhpSpreadsheet can support data bar in Conditional format? (#938) Well, I unzip an excel file and figure out dataBar code myself. I finnaly did it with some ugly coding, maybe I will optimize them some other day. PhpOffice\PhpSpreadsheet\Style\Conditional:15 class Conditional implements IComparable { // Condition types ... const CONDITION_DATABAR = 'dataBar'; // add by fdream, 2019/03/19 ... PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet:462 private function writeConditionalFormatting(XMLWriter $objWriter, PhpspreadsheetWorksheet $pSheet) { $dataBars = []; // Conditional id $id = 1; // Loop through styles in the current worksheet foreach ($pSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) { foreach ($conditionalStyles as $conditional) { // WHY was this again? // if ($this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()) == '') { // continue; // } if ($conditional->getConditionType() == Conditional::CONDITION_DATABAR) { $dataBars[] = $cellCoordinate; } elseif ($conditional->getConditionType() != Conditional::CONDITION_NONE) { // conditionalFormatting $objWriter->startElement('conditionalFormatting'); $objWriter->writeAttribute('sqref', $cellCoordinate); // cfRule $objWriter->startElement('cfRule'); $objWriter->writeAttribute('type', $conditional->getConditionType()); $objWriter->writeAttribute('dxfId', $this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode())); $objWriter->writeAttribute('priority', $id++); if (($conditional->getConditionType() == Conditional::CONDITION_CELLIS || $conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT) && $conditional->getOperatorType() != Conditional::OPERATOR_NONE) { $objWriter->writeAttribute('operator', $conditional->getOperatorType()); } if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT && $conditional->getText() !== null) { $objWriter->writeAttribute('text', $conditional->getText()); } if ($conditional->getStopIfTrue()) { $objWriter->writeAttribute('stopIfTrue', '1'); } if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT && $conditional->getOperatorType() == Conditional::OPERATOR_CONTAINSTEXT && $conditional->getText() !== null) { $objWriter->writeElement('formula', 'NOT(ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . ')))'); } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT && $conditional->getOperatorType() == Conditional::OPERATOR_BEGINSWITH && $conditional->getText() !== null) { $objWriter->writeElement('formula', 'LEFT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"'); } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT && $conditional->getOperatorType() == Conditional::OPERATOR_ENDSWITH && $conditional->getText() !== null) { $objWriter->writeElement('formula', 'RIGHT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"'); } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT && $conditional->getOperatorType() == Conditional::OPERATOR_NOTCONTAINS && $conditional->getText() !== null) { $objWriter->writeElement('formula', 'ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . '))'); } elseif ($conditional->getConditionType() == Conditional::CONDITION_CELLIS || $conditional->getConditionType() == Conditional::CONDITION_CONTAINSTEXT || $conditional->getConditionType() == Conditional::CONDITION_EXPRESSION) { foreach ($conditional->getConditions() as $formula) { // Formula $objWriter->writeElement('formula', $formula); } } $objWriter->endElement(); $objWriter->endElement(); } } } if (!empty($dataBars)) { // it won't work when there is more than 255 cells for ($l=count($dataBars), $i=0; $i < $l; $i += 255) { // conditionalFormatting $objWriter->startElement('conditionalFormatting'); $objWriter->writeAttribute('sqref', implode(' ', array_slice($dataBars, $i, 255))); // cfRule $objWriter->startElement('cfRule'); $objWriter->writeAttribute('type', Conditional::CONDITION_DATABAR); $objWriter->writeAttribute('priority', '1'); $objWriter->startElement('dataBar'); $objWriter->startElement('cfvo'); $objWriter->writeAttribute('type', 'num'); $objWriter->writeAttribute('val', '0'); // min number here $objWriter->endElement(); $objWriter->startElement('cfvo'); $objWriter->writeAttribute('type', 'num'); $objWriter->writeAttribute('val', '0.7'); // max number here $objWriter->endElement(); $objWriter->startElement('color'); $objWriter->writeAttribute('rgb', 'FF3F3F3F'); // gradient color here. Still trying to figure out how solid color works $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); } } } Hello @fdreamsu<https://github.com/fdreamsu> , I'm trying to do the same, but it seems to don't be working anymore. Can you please give me some help? To apply i'm doing: $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_DATABAR); $conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('G4')->getConditionalStyles(); $conditionalStyles[] = $conditional1; $spreadsheet->getActiveSheet()->getStyle('G4')->setConditionalStyles($conditionalStyles); Thanks in advance! — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<#938?email_source=notifications&email_token=AA5M3QQR44NUPW6TO36KGXLQXFFGZA5CNFSM4G7NFA32YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEGBX4HY#issuecomment-562265631>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AA5M3QRURUNQ7JWPUPNEKSDQXFFGZANCNFSM4G7NFA3Q>.

fdreamsu · 2019-12-07T08:26:46Z

@lfernandes Sorry i don't have a clue. Haven't touch any of this for a long time.
I remember it was not too hard to understand how databar works in the excel file source code. You can simply unzip an excel file see for yourself. Just search "dataBar" in file xl/worksheets/sheet1.xml

MarkBaker added the enhancement label Mar 19, 2019

fdreamsu closed this as completed Mar 20, 2019

tera911 mentioned this issue Dec 14, 2020

Support DataBar of conditional formatting rule #1754

Merged

5 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Any chance PhpSpreadsheet can support data bar in Conditional format? #938

Any chance PhpSpreadsheet can support data bar in Conditional format? #938

fdreamsu commented Mar 19, 2019

MarkBaker commented Mar 19, 2019

fdreamsu commented Mar 19, 2019

lfernandes commented Dec 5, 2019 •

edited

Loading

cpnadal commented Dec 6, 2019 via email

fdreamsu commented Dec 7, 2019

Any chance PhpSpreadsheet can support data bar in Conditional format? #938

Any chance PhpSpreadsheet can support data bar in Conditional format? #938

Comments

fdreamsu commented Mar 19, 2019

MarkBaker commented Mar 19, 2019

fdreamsu commented Mar 19, 2019

lfernandes commented Dec 5, 2019 • edited Loading

cpnadal commented Dec 6, 2019 via email

fdreamsu commented Dec 7, 2019

lfernandes commented Dec 5, 2019 •

edited

Loading