Phpspreadsheet: ConditionalFormattingRuleExtension::parseExtDataBarAttributesFromXml(): Node no longer exists

[S-DICKSON]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

The excel sheet to be opened.

What is the current behavior?

When the user submits the excel sheet an exception is thrown

test_bom.zip

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?PHP

    public function store(Request $request)
    {
        try {
            DB::beginTransaction();

            // Open the file
            $spreadsheet = IOFactory::load($request->file('file')->getRealPath()); // fails here

           ...
           ...

            DB::commit();

            } catch (\Exception $e) {

            flash()->error($err->getMessage());
            return redirect()->route('home.create');
        }

        return redirect()->route('home.index');
    }

Which versions of PhpSpreadsheet and PHP are affected?

• It fails on "phpoffice/phpspreadsheet": "1.17.1", PHP 7.3.27-9
• It works on version "phpoffice/phpspreadsheet": "1.16" PHP 7.3.27-9

[dsuurlant]

We're encountering this error message as well for 1.17.1. We can't downgrade as this version fixes another bug for us.

When we dive into our debugging this error happens LOTS of times but the file is still being read correctly otherwise. I'm not sure what's happening, because searching for Node no longer exists returns mostly results about SimpleXML, and this might just be an unsuppressed error.

[dsuurlant]

Update: when we try to ignore this PHP Error, the library crashes on line 132 in the ConditionalFormattingRuleExtension.

[MarkBaker]

I'm looking at the issue; though the test_bom.xlsm file provided uses formula features from Office365 that aren't yet supported by PHPSpreadsheet such as the @ Implicit intersection operator, and structured references.... do you have an example that doesn't use these new features, because they're a distraction while debugging

[S-DICKSON]

@MarkBaker, thanks for looking at this. I've emailed the original creator about the 'intersection operator' and 'structured references'. I'm going to recreate the spreadsheet myself in Office as I opened this one in LibreOffice, which may have caused some issues. I should have more information for you on Monday. Cheers, have a good weekend.

[S-DICKSON]

test_bom_v1.zip

@MarkBaker Sorry for the delay. So I spoke to the creator of the spreadsheet, and he does use Implicit intersection operator, and structured references but they are stripped manually before we use the library. Also, I created another sample spreadsheet in Windows Office, and the problem persists in the latest version. I've attached the spreadsheet; I hope this will helped you.

[MarkBaker]

I'm sure that it will help.... The Office365 features were just triggering too many problems while I was trying to debug that it was making it difficult to see the messages that I needed to see among all the other messages

[MarkBaker]

Can somebody who's been suffering from this problem please test against the Issue-1967-Conditional-Formatting-DataBar-Problems branch... I believe that this should fix anything, and it does with @S-DICKSON's sample file, but I want to know that it works with other problem files before I merge it

Thanks

[S-DICKSON]

@dsuurlant /\ /\ /\ ☝️☝️

[MarkBaker]

I've merged the fix to master anyway; because I'm pretty sure that it's correct, and it doesn't break any of the existing tests

[dsuurlant]

Can confirm that the error no longer shows up in dev-master. Thank you Mark! When do you estimate the next package release will be?

[MarkBaker]

We're currently working on a major refactoring of all the Excel Function implementations, which is almost complete now; but as it deprecates all the original methods, I'd like to get it all complete before doing the next release so that all the remaining deprecations are in the same release (well, spread across the previous and next, but spread across just 2 releases rather than across 3). I'd expect the 1.18 release sometime in the first two weeks of May.