Read XLSX with namespaces in their XML

[JulienChavee]

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?

Xlsx that contains namespaces in their XML are correctly loaded

What is the current behavior?

The array of data after reading the xlsx is empty

What are the steps to reproduce?

I'm using the following code to read this file: example.xlsx

<?php
		$reader = IOFactory::createReaderForFile($filePath);
		$spreadsheet=$reader->load($filePath);
		$sheet=$spreadsheet->getSheet(0);
                print_r($sheet->toArray());
?>

Which versions of PhpSpreadsheet and PHP are affected?

• PhpSpreadsheet 1.6
• PHP >= 5.6

Solution

As requested in #206, I tried to find a better solution instead of just str_replace.
As I'm not an expert in XLSX and XML, I don't know if it works correctly for all cases.

All tests I've done are OK with this solution, so can you take a look this and give me your review? If it's seems good, I can submit a pull request with this.

private function removeXMLNamespaces($xml)
{
	$nameSpaceToRemove = array('x','d');

		if(!empty($xml))
		{
			$simpleXMLElement=new \SimpleXMLElement($xml);
			$domSimpleXMLElement=dom_import_simplexml($simpleXMLElement);

			if(!is_null($domSimpleXMLElement))
			{
				$dom=new \DOMDocument('1.0');
				$domSimpleXMLElement=$dom->importNode($domSimpleXMLElement,true);
				$domSimpleXMLElement=$dom->appendChild($domSimpleXMLElement);

				$element=$dom->childNodes->item(0);

				foreach($simpleXMLElement->getDocNamespaces() as $name=>$uri)
				{
					if(in_array($name, $nameSpaceToRemove))
						$element->removeAttributeNS($uri,$name);
				}

				return $dom->saveXML();
			}
			else
				return $xml;
		}
		else
			return $xml;
}

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[JulienChavee]

Just up this issue else it'll be closed.
If you need any help or something, let me know!

[LouiseGs]

Hello, I have exactly the same problem that is related in this subject: "Can´t read data from Excel file generated by openpyxl #842" PHPOffice/PHPExcel#842.
I am using openpyxl to generate a file and then I try to read it with phpExcel. Despite all the solution propose I still can't fix my problem … I have just tried your solution but I still have the same issue. If you have any idea or suggestion about how I could at least find if this is really a problem of namespace. It will help me a lot.

Thank you in advance.

[isleshocky77]

@LouiseGs I think I documented this somewhere else; however, I can't find it so I'll post it here. I was having an issue reading an .xlsx file that appears to be generated from SpreadsheetLight. When digging into the code it's due to this issue which is having namespaces in the file.

I used the following solution:

<?php
# src/Company/Bridge/PhpOffice/PhpSpreadsheet/Reader/Excel2007_NonStandardNamespacesWorkaround.php

namespace Company\Bridge\PhpOffice\PhpSpreadsheet\Reader;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

/**
 * @see https://stackoverflow.com/questions/29514200/phpexcel-parsing-xlsx-files-with-x-namespace
 * @see https://github.com/PHPOffice/PHPExcel/issues/1187#issuecomment-295032648
 * @see https://github.com/PHPOffice/PhpSpreadsheet/issues/500
 */
class Excel2007_NonStandardNamespacesWorkaround extends Xlsx
{
    public function __construct()
    {
        parent::__construct();
        $this->securityScanner->setAdditionalCallback([self::class, 'securityScan']);
    }

    public static function securityScan($xml)
    {
        return str_replace(
            [
                '<x:',
                '</x:',
                /*':x=',*/
                '<d:',
                '</d:',
                /*, ':d='*/
            ],
            [
                '<',
                '</',
                /*'=',*/
                '<',
                '</',
                /*, '='*/
            ],
            $xml
        );
    }
}

<?php
# src/Company/Bundle/Device/WidgetBundle/Tests/Component/Import/Importer/WidgetImporterTest.php

namespace Company\Bundle\Device\WidgetBundle\Tests\Component\Import\Importer;

use Ramsey\Uuid\Uuid;
use Company\Bridge\PhpOffice\PhpSpreadsheet\Reader\Excel2007_NonStandardNamespacesWorkaround;

class WidgetImporterTest extends \vi_Test_Case_Unit
{
    /**
     * @dataProvider fileProvider
     */
    public function testPhpSpreadSheet($inputFileName)
    {
        $inputFileName = __DIR__ . '/./../../../../resources/data/' . $inputFileName;
        $reader = new Excel2007_NonStandardNamespacesWorkaround();
        $spreadsheet = $reader->load($inputFileName);

        $sheets = $spreadsheet->getSheetNames();
        $this->assertNotEmpty($sheets);
        $this->assertEquals('Widget Test Results', $sheets[0]);
    }

[LouiseGs]

Thank you for your help @isleshocky77. I think I have already tried it but I will try again.

[JulienChavee]

Hello,

I just read the PHPOffice/PHPExcel#842 and it seems that your library is using the namespace "nso". In my solution (and the one from @isleshocky77), we are only removing namespace "x" and "d".

In my solution, you can try to add "nso" (or "ns0" I don't really know which one is used) in the array nameSpaceToRemove.
Don't forget to call the function via the securityScanner callback.

[LouiseGs]

I had already added “nso” but not “ns0” and I’m not sure that I have called the function via the securityScanner callback. I will try this as soon as possible and tell you the result.
Thank you again for helping me.

[LouiseGs]

Thank you isleshocky77 and JulienChavee for your help !
Your solution has finally work for me (with “ns0”).

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[JulienChavee]

This is still an issue.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[JulienChavee]

This is still an issue.

[Oukaishen]

This is still an issue, please help

[esteban-coder]

Simpler solution

Inside the XmlScanner.php, add the following line:

    public function scan($xml)
    {
        //fails if we replace <v: 
        //$xml = preg_replace('/(<\/?)(\w+:)/', "$1", $xml);             
        //$xml = preg_replace('/(<\/?)((ap|vt|x|v)+:)/', "$1", $xml);

        //$xml = preg_replace('/(<\/?)((ap|vt|x)+:)/', "$1", $xml);    
        //doesn't fail with <ap: and <vt:
        $xml = preg_replace('/(<\/?)(x+:)/', "$1", $xml);
        ...
        ...
    }

Or place that code inside Xlsx.php:

    private function getFromZipArchive(ZipArchive $archive, $fileName = '')
    {
        ...
        ...
        if ($contents === false) {
            $contents = $archive->getFromName(substr($fileName, 1), 0, ZipArchive::FL_NOCASE);
        }

        $contents = preg_replace('/(<\/?)(x+:)/', "$1", $contents);

        return $contents;
    }

I unzipped xlsx file and open all xml file, to investigate the differences between xlsx files (one ok and the other returning an empty sheet).

I get into this because of the following link: PhpWord - Reader - Word2007.php
Look in the fuction getFromZipArchive of that link:

        // Remove namespaces from elements and attributes name
        if ($removeNamespace) {
            $contents = preg_replace('~(</?|\s)w:~is', '$1', $contents);
        }

But It doesn't work, don't know why, so I did my own version.

Hope it helps, and comment about that link I found, it could help.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[JulienChavee]

Hello, this is still an issue

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[JulienChavee]

Still an issue.

[JulienChavee]

Still an issue

[Dina-Nashaat]

I am experiencing the same issue - any plans to fix this in future releases?

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[JulienChavee]

This is still an issue

[OliverMensahDev]

Is this still an issue?

[JulienChavee]

Yes it is

[mylemans]

We also ran into this bug and the 'hack' mentioned by esteban-code does result in data (sheets) becoming available (while there were no sheets loaded before)

A permanent solution for this bug would be nice.

[gdespaux]

I believe we're facing a similar issue. An XLSX file from an external source gives the error:

PHP Fatal error:  Uncaught PhpOffice\PhpSpreadsheet\Exception: Your requested sheet index: -1 is out of bounds. The actual number of sheets is 0. in .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php:688
Stack trace:
#0 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php(580): PhpOffice\PhpSpreadsheet\Spreadsheet->getSheet(-1)
#1 ...PhpOffice\PhpSpreadsheet\Spreadsheet->getActiveSheet()

Converting the file to CSV beforehand fixes it, or opening in Excel and re-saving it. Not ideal for large numbers of files though.

[IMSoP]

Since XML namespaces are frequently misunderstood, I'd like to point out a few things:

Firstly, the local prefix of a namespace does not affect its meaning, it is just for use in that particular document (or fragment). The identifier of a namespace is a URI (not a URI that points anywhere, just a unique URI for ownership purposes).

The default namespace is basically the same, just think of it as having an empty prefix. The following three XML documents should all be treated as completely equivalent:

<foo xmlns="http://example.com/my-namespace><bar /></foo>

<x:foo xmlns:x="http://example.com/my-namespace><x:bar /></x:foo>

<supercalifragilisticexpialidocious:foo xmlns:supercalifragilisticexpialidocious="http://example.com/my-namespace><supercalifragilisticexpialidocious:bar /></supercalifragilisticexpialidocious:foo>

Secondly, PHP's SimpleXML library has full support for namespaces, but the programmer needs to account for them by "switching" namespaces using the ->children() method. See Reference - how do I handle namespaces (tags and attributes with colon in) in SimpleXML?

So, while mangling the XML to forcefully change the namespaces will work around this bug, it is not the correct fix. There is nothing wrong, or particularly "exotic" about the XML.

The actual bug is that somewhere in the PhpSpreadsheet library, it is not selecting the correct namespace. It just happens that most XLSX files (those created by Excel itself, I'm guessing) have the required namespace as the default (no-prefix) namespace, and that gets selected by SimpleXML by default.

[IMSoP]

Actually, looking at the source code, I think the trouble goes deeper - I see lots of use of this anti-pattern:

$namespaces = $xml->getNamespaces(true);
$xml_ss = $xml->children($namespaces['ss']);

This looks at the local aliases in the XML document, and picks whichever happens to have the prefix 'ss'. Every instance of that pattern needs to be replaced with a reference to the actual namespace URI, e.g.

$xml_ss = $xml->children('urn:schemas-microsoft-com:office:spreadsheet');

Probably the list of namespaces in this comment could be put into constants somewhere in the library. Not listed there is what namespace is expected to be the default, which should be treated exactly the same as the others.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[esteban-coder]

Is this still an issue?
If it´s not, what is the solid solution.
I am using PHP 7.1 in production, so I need PhpSpreadsheet version 1.9.0, I can't upadate it as it now requires PHP 7.4 or greater.

[IMSoP]

@esteban-code The fix for this is a complex and far-reaching change to the library, so is always going to be part of a new release, not something that can trivially be applied as a patch to an old one. The version of the library you are on is two and a half years' old, and PHP 7.1 itself stopped receiving official security updates more than 2 years ago, so you're unlikely to find many people volunteering to test their libraries on it. I know updating projects to work on newer versions can be a lot of effort, but that's just the price you have to pay for progress in this business, I'm afraid.

[esteban-coder]

@IMSoP Ok, I will update my environment. So the issue is finally fixed? And since which version of PhpSpreadSheet is finally working as expected?

[oleibman]

1.20 was the first phase, will mostly handle the data, but not styles.

[oleibman]

PR #2471, which handles merging of styles, has just been merged. Closing this issue.