php parse xml problems

Question

I'm trying to parse an xml generated by excel, but i'm hitting a stag. I cant get anywhere below the Workbook node:

$benregxml = simplexml_load_file('00_ben_reg_main.xml');
$wrkbook = $benregxml->Workbook->Worksheet;
print_r($wrkbook);

If i log Workbook, that works (output is SimpleXMLElement Object ( )).

I'm actually trying to get the row node, but nothing i try appears to work.

Excel XML code looks like this:

<?xml version="1.0" ?>
<?mso-application progid="Excel.Sheet" ?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Author>Ognen Plavevski</Author>
        <LastAuthor>Ognen Plavevski</LastAuthor>
        <Created>2013-05-24T20:28:09Z</Created>
        <Version>14.00</Version>
    </DocumentProperties>
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <AllowPNG/>
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>10035</WindowHeight>
        <WindowWidth>22995</WindowWidth>
        <WindowTopX>480</WindowTopX>
        <WindowTopY>45</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat/> <Protection/>
        </Style>
        <Style ss:ID="s62">
            <Protection ss:Protected="0"/>
        </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
        <Table ss:ExpandedColumnCount="19" ss:ExpandedRowCount="101" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
            <Row>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">ID</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">PARENT_RECORD_ID</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">PARENT_PAGE_ID</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">PARENT_ELEMENT_ID</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">CREATED_DATE</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">CREATED_BY</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">CREATED_LOCATION</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">CREATED_DEVICE_ID</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">MODIFIED_DATE</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">MODIFIED_BY</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">MODIFIED_LOCATION</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">MODIFIED_DEVICE_ID</Data>
                </Cell>
            </Row>
        </Table>
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            <PageSetup>
                <Header x:Margin="0.3" />
                <Footer x:Margin="0.3" />
                <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75" />
            </PageSetup>
            <Selected/>
            <Panes>
                <Pane>
                    <Number>3</Number>
                    <RangeSelection>R1C1:R101C19</RangeSelection>
                </Pane>
            </Panes>
            <ProtectObjects>False</ProtectObjects>
            <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name="Sheet2">
        <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15"></Table>
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            <PageSetup>
                <Header x:Margin="0.3" />
                <Footer x:Margin="0.3" />
                <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75" />
            </PageSetup>
            <ProtectObjects>False</ProtectObjects>
            <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name="Sheet3">
        <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15"></Table>
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            <PageSetup>
                <Header x:Margin="0.3" />
                <Footer x:Margin="0.3" />
                <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75" />
            </PageSetup>
            <ProtectObjects>False</ProtectObjects>
            <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
    </Worksheet>
</Workbook>

Alkis Kalogeris · Accepted Answer · 2013-05-24 23:30:35Z

2

Try this

$benregxml = simplexml_load_file('00_ben_reg_main.xml');
$wrkbook = $benregxml->Worksheet[0]->Table->Row;
print_r($wrkbook);

It's your level that is the problem and of course that you have 3 Worksheet tags (so it's an array). The workbook tag is the head, so $benregxml = Workbook from the start

UPDATE

$benregxml = simplexml_load_file('00_ben_reg_main.xml');
$cells = $benregxml->Worksheet[0]->Table->Row[0];
//You need Row[0] because Row will return object, but you want just the content, 
    //which is the array of cells.
echo "<pre>"; print_r($cells); echo "</pre>";

echo "<br>----------------------------------------------------------------<br>";
foreach( $cells as $cell ) {

    echo "<pre>"; print_r($cell); echo "</pre>";
}

Just to clarify, Row returns SimpleXMLElement Object which as you see in the output consists of an array of SimpleXMLElement Object of cells. If you want the object you want Row, but if you want the actual array you need Row[0]. If you find this difficult to understand I advise you to check the json format. It will help a lot. Hope this helps.

edited May 24, 2013 at 23:30

answered May 24, 2013 at 22:00

Alkis Kalogeris

17.9k15 gold badges62 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

R0b0tn1k Over a year ago

Is there a better way to debug the output other than print_r?

Alkis Kalogeris Over a year ago

You got two problems. Worksheet[0] was one. The second was that $benregxml = Workbook from loading, so $benregxml->Workbook->Worksheet[0] wouldn't work either. No, I'm afraid there isn't. At least I don't know another way.

R0b0tn1k Over a year ago

One more question. I'm trying to iterate the rows and cells, but the cells bit doesnt work: foreach ($rows as $row): $cells = $row->Cell; foreach ($cells as $cell): print_r($cell); endforeach; endforeach;

Alkis Kalogeris Over a year ago

Hey I remembered a better way of formating the output for debug. Also in this example you have only one row. I'll update my answer in a little bit and you will get the idea.

Alkis Kalogeris Over a year ago

Nope, I've made a mistake. Check again my code. This is the right one. With the new format, you will be able to see the output pretty clear and if you play with it a little you will understand the flow.

Collectives™ on Stack Overflow

php parse xml problems

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related