Skip to content

Instantly share code, notes, and snippets.

@lahirwisada

lahirwisada/XML2003Parser.php

Forked from faisalman/XML2003Parser.php
Created February 6, 2017 12:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lahirwisada/d2902e34db941985bf8f09beb8a38121 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lahirwisada/d2902e34db941985bf8f09beb8a38121 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @faisalman faisalman revised this gist Apr 11, 2011. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion readme.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1 +1 @@
    This repository soon will be moved to https://github.com/faisalman/SimpleExcel
    This repository has been moved to http://github.com/faisalman/simple-excel-php
  2. @faisalman faisalman revised this gist Mar 13, 2011. 6 changed files with 332 additions and 312 deletions.
    208 changes: 0 additions & 208 deletions Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,208 +0,0 @@
    <?php
    /**
    * Excel 2003 XML-Parser
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    * http://gist.github.com/862741
    *
    * Copyright (c) 2011 Faisalman <movedpixel@gmail.com>
    *
    * The MIT License
    * http://www.opensource.org/licenses/mit-license.php
    *
    * Permission is hereby granted, free of charge, to any person obtaining a copy
    * of this software and associated documentation files (the "Software"), to deal
    * in the Software without restriction, including without limitation the rights
    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    * copies of the Software, and to permit persons to whom the Software is
    * furnished to do so, subject to the following conditions:
    *
    * The above copyright notice and this permission notice shall be included in
    * all copies or substantial portions of the Software.
    * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
    * THE SOFTWARE.
    *
    * @author Faisalman <movedpixel@gmail.com>
    * @copyright 2011 Faisalman
    * @example see index.php
    * @license http://www.opensource.org/licenses/mit-license.php The MIT License
    * @link http://gist.github.com/862741
    * @todo Check row index
    * @version 11 Mar 2011
    */

    class Excel2003XMLParser
    {
    /**
    *
    * this variable hold the array result
    *
    * @access private
    * @var array
    */
    private $array_table;

    /**
    *
    * this variable hold SimpleXML object
    *
    * @access private
    * @var simplexml object
    */
    private $simplexml_table;

    /**
    *
    * @param string $url Path to XML file
    */

    public function __construct($url){
    $this->loadXMLFile($url);
    }

    /**
    *
    * @access private
    * @param object $attrs_obj
    * @return array
    */
    private function getAttributes($attrs_obj){
    $attrs_arr = array();
    foreach($attrs_obj as $attrs){
    $attrs = (array)$attrs;
    foreach($attrs as $attr){
    $attr_keys = array_keys($attr);
    $attrs_arr[$attr_keys[0]] = $attr[$attr_keys[0]];
    }
    }
    return $attrs_arr;
    }

    /**
    *
    * @param int $row_num Row number
    * @param int $col_num Column number
    * @return string
    */
    public function getCellData($row_num,$col_num){
    return $this->array_table[$row_num-1]["row_contents"][$col_num-1]["col_contents"];
    }

    /**
    *
    * @param int $col_num Column number
    * @return array
    */
    public function getColumnData($col_num){
    $col_arr = array();

    // get the specified column within every row
    foreach($this->array_table as $row){
    array_push($col_arr,$row["row_contents"][$col_num-1]["col_contents"]);
    }
    return $col_arr;
    }

    /**
    *
    * @param int $row_num Row number
    * @return array
    */
    public function getRowData($row_num){
    $row_arr = array();

    // get the specified column within every row
    foreach($this->array_table[$row_num-1]["row_contents"] as $col){
    array_push($row_arr,$col["col_contents"]);
    }
    return $row_arr;
    }

    /**
    *
    * use this method to get the complete array
    *
    * @return array
    */
    public function getTableData(){
    return $this->array_table;
    }

    /**
    *
    * @param string $url Path to XML file
    * @return bool Returns TRUE if file exist, FALSE if does'nt
    */
    public function loadXMLFile($url){

    // assign simpleXML object
    if(!$this->simplexml_table = simplexml_load_file($url)) {

    // return immediately if there is an error
    return FALSE;
    }

    $rows = $this->simplexml_table->Worksheet->Table->Row;
    $this->array_table = array();
    $row_num = 1;

    // loop through all rows
    foreach($rows as $row){

    $cells = $row->Cell;
    $row_attrs = $row->xpath('@ss:*');
    $row_attrs_arr = $this->getAttributes($row_attrs);
    $row_arr = array();
    $col_num = 1;

    // loop through row cells
    foreach($cells as $cell){

    // check whether ss:Index attribute exist
    $cell_index = $cell->xpath('@ss:Index');

    // if exist, push empty value until the specified index
    if(count($cell_index) > 0){
    $gap = $cell_index[0]-count($row_arr);
    for($i = 1; $i < $gap; $i++){
    array_push($row_arr,"");
    $col_num += 1;
    }
    }

    // get all cell and data attributes
    $cell_attrs = $cell->xpath('@ss:*');
    $cell_attrs_arr = $this->getAttributes($cell_attrs);
    $data_attrs = $cell->Data->xpath('@ss:*');
    $data_attrs_arr = $this->getAttributes($data_attrs);

    // push column array
    array_push($row_arr,array(
    "col_num" => $col_num,
    "col_contents" => htmlspecialchars(strval($cell->Data)), // convert data to string and escape HTML tags
    "cell_attrs" => $cell_attrs_arr,
    "data_attrs" => $data_attrs_arr
    ));
    $col_num += 1;
    }

    // push row array
    array_push($this->array_table,array(
    "row_num" => $row_num,
    "row_contents" => $row_arr,
    "row_attrs" => $row_attrs_arr
    ));
    $row_num += 1;
    }

    // succeed :)
    return TRUE;
    }
    }

    ?>
    241 changes: 241 additions & 0 deletions XML2003Parser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,241 @@
    <?php

    /**
    * Excel 2003 XML-Parser
    *
    * PHP library for parsing Microsoft Excel 2003 XML Spreadsheet
    * http://gist.github.com/862741
    *
    * Copyright (c) 2011 Faisalman <movedpixel@gmail.com>
    *
    * Permission is hereby granted, free of charge, to any person obtaining a copy
    * of this software and associated documentation files (the "Software"), to deal
    * in the Software without restriction, including without limitation the rights
    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    * copies of the Software, and to permit persons to whom the Software is
    * furnished to do so, subject to the following conditions:
    *
    * The above copyright notice and this permission notice shall be included in
    * all copies or substantial portions of the Software.
    *
    * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
    * THE SOFTWARE.
    *
    * @author Faisalman
    * @copyright 2011 (c) Faisalman
    * @example see example.php
    * @license http://www.opensource.org/licenses/mit-license
    * @link http://gist.github.com/862741
    * @package SimpleExcel
    * @version 0.0.1
    */
    class XML2003Parser
    {
    /**
    * Holds the parsed result
    * @access private
    * @var array
    */
    private $table_arr;

    /**
    * @param string $url Path to XML file (optional)
    * @param bool $escape Set whether input had to be escaped from HTML tags, default to TRUE
    * @return void
    */
    public function __construct($url = NULL, $escape = TRUE){
    if(isset($url)) $this->loadXMLFile($url,$escape);
    }

    /**
    * Extract attributes from SimpleXMLElement object
    * @access private
    * @param object $attrs_obj
    * @return array
    */
    private function getAttributes($attrs_obj){
    $attrs_arr = array();
    foreach($attrs_obj as $attrs){
    $attrs = (array)$attrs;
    foreach($attrs as $attr){
    $attr_keys = array_keys($attr);
    $attrs_arr[$attr_keys[0]] = $attr[$attr_keys[0]];
    }
    }
    return $attrs_arr;
    }

    /**
    * Get data of the specified cell as an array
    * @param int $row_num Row number
    * @param int $col_num Column number
    * @return mixed Returns an array or FALSE if cell doesn't exist
    */
    public function getCellData($row_num, $col_num){

    // check whether the cell exists
    if(!isset($this->table_arr['table_contents'][$row_num-1]['row_contents'][$col_num-1])){
    return FALSE;
    }
    return $this->table_arr['table_contents'][$row_num-1]['row_contents'][$col_num-1];
    }

    /**
    * Get data of the specified column as an array
    * @param int $col_num Column number
    * @return mixed Returns an array or FALSE if table doesn't exist
    */
    public function getColumnData($col_num){
    $col_arr = array();

    if(!isset($this->table_arr['table_contents'])){
    return FALSE;
    }

    // get the specified column within every row
    foreach($this->table_arr['table_contents'] as $row){
    array_push($col_arr,$row['row_contents'][$col_num-1]);
    }

    // return the array, if empty then return FALSE
    return $col_arr;
    }

    /**
    * Get data of the specified row as an array
    * @param int $row_num Row number
    * @return mixed Returns an array FALSE if row doesn't exist
    */
    public function getRowData($row_num){
    if(!isset($this->table_arr['table_contents'][$row_num-1]['row_contents'])){
    return FALSE;
    }
    $row = $this->table_arr['table_contents'][$row_num-1]['row_contents'];
    $row_arr = array();

    // get the specified column within every row
    foreach($row as $cell){
    array_push($row_arr,$cell);
    }

    // return the array, if empty then return FALSE
    return $row_arr;
    }

    /**
    * Get data of all cells as an array
    * @return mixed Returns an array or FALSE if table doesn't exist
    */
    public function getTableData(){
    return isset($this->table_arr) ? $this->table_arr : FALSE;
    }

    /**
    * Load the XML file to be parsed
    * @param string $url Path to XML file
    * @param bool $escape Set whether input had to be escaped from HTML tags, default to TRUE
    * @return bool Returns TRUE if file exist and valid, FALSE if does'nt
    * @todo Check for valid XML 2003 namespace
    */
    public function loadXMLFile($url, $escape = TRUE){
    $this->table_arr = array(
    'doc_props' => array(),
    'table_contents' => array()
    );

    // assign simpleXML object
    if($simplexml_table = simplexml_load_file($url)){

    // check XML namespace and return if the loaded file isn't a valid XML 2003 spreadsheet
    $xmlns = $simplexml_table->getDocNamespaces();
    if($xmlns['ss'] != 'urn:schemas-microsoft-com:office:spreadsheet'){
    return FALSE;
    }
    } else {

    // when error loading file
    return FALSE;
    }

    // extract document properties
    $doc_props = (array)$simplexml_table->DocumentProperties;
    $this->table_arr['doc_props'] = $doc_props;

    $rows = $simplexml_table->Worksheet->Table->Row;
    $row_num = 1;

    // loop through all rows
    foreach($rows as $row){

    $cells = $row->Cell;
    $row_attrs = $row->xpath('@ss:*');
    $row_attrs_arr = $this->getAttributes($row_attrs);
    $row_arr = array();
    $col_num = 1;

    // loop through all row's cells
    foreach($cells as $cell){

    // check whether ss:Index attribute exist
    $cell_index = $cell->xpath('@ss:Index');

    // if exist, push empty value until the specified index
    if(count($cell_index) > 0){
    $gap = $cell_index[0]-count($row_arr);
    for($i = 1; $i < $gap; $i++){
    array_push($row_arr,array(
    'row_num' => $row_num,
    'col_num' => $col_num,
    'datatype' => '',
    'value' => '',
    //'cell_attrs' => '',
    //'data_attrs' => ''
    ));
    $col_num += 1;
    }
    }

    // get all cell and data attributes
    $cell_attrs = $cell->xpath('@ss:*');
    $cell_attrs_arr = $this->getAttributes($cell_attrs);
    $data_attrs = $cell->Data->xpath('@ss:*');
    $data_attrs_arr = $this->getAttributes($data_attrs);
    $cell_datatype = $data_attrs_arr['Type'];

    // extract data from cell
    $cell_value = (string)$cell->Data;

    // filter from any HTML tags
    if($escape) $cell_value = htmlspecialchars($cell_value);

    // push column array
    array_push($row_arr,array(
    'row_num' => $row_num,
    'col_num' => $col_num,
    'datatype' => $cell_datatype,
    'value' => $cell_value,
    //'cell_attrs' => $cell_attrs_arr,
    //'data_attrs' => $data_attrs_arr
    ));
    $col_num += 1;
    }

    // push row array
    array_push($this->table_arr['table_contents'],array(
    'row_num' => $row_num,
    'row_contents' => $row_arr,
    //'row_attrs' => $row_attrs_arr
    ));
    $row_num += 1;
    }

    // load succeed :)
    return TRUE;
    }
    }
    ?>
    60 changes: 60 additions & 0 deletions example.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,60 @@
    <?php

    /**
    * Usage example, run this on webserver and see the result on webbrowser
    */

    //ini_set('display_errors','On');

    echo "\nSample of XML 2003 Spreadsheet file (example.xml):\n";
    echo "<pre>";
    echo htmlspecialchars(file_get_contents('example.xml'));
    echo "</pre><hr/>";

    // load the class file
    require_once 'XML2003Parser.php';

    // instantiate new object
    $excel = new XML2003Parser('example.xml');
    //$excel->loadXMLFile('example.xml'); -> unnecessary since file is already loaded on construct (see line above)

    // get array of the table
    $table = $excel->getTableData();

    // display instruction
    echo "\$excel = new XML2003Parser();
    <br/>\$excel->loadXMLFile('example.xml');
    <br/>\$table = \$excel->getTableData();
    <br/>then print the given array in \$table to an HTML table:";

    // print as HTML table
    echo "<table border=1>";
    foreach($table["table_contents"] as $row){
    echo "<tr>";
    foreach($row["row_contents"] as $cell){
    echo "<td>";
    echo $cell["value"];
    echo "</td>";
    }
    echo "</tr>";
    }
    echo "</table>(see how from PHP source of this page)<hr/>";

    echo "<pre>Output of getColumnData(4):\n";

    print_r($excel->getColumnData(4)); // print an array of all data within column 4

    echo "\nOutput of getRowData(3):\n";
    print_r($excel->getRowData(3)); // print an array of all data within row 3

    echo "\nOutput of getCellData(2,1):\n";
    print_r($excel->getCellData(2,1)); // print the data within row 2 column 1

    echo "\n\nOutput of getTableData():\n";
    print_r($table); // print an array of all data
    echo "</pre>";

    // load another different XML file
    //$excel->loadXMLFile('example-2.xml');

    ?>
    104 changes: 30 additions & 74 deletions example.xml
    View file
    Original file line number Diff line number Diff line change
    @@ -34,48 +34,36 @@
    <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="3" x:FullColumns="1"
    x:FullRows="1" ss:DefaultRowHeight="15">
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">T</Data></Cell>
    <Cell><Data ss:Type="String">H</Data></Cell>
    <Cell><Data ss:Type="String">I</Data></Cell>
    <Cell><Data ss:Type="String">S</Data></Cell>
    <Cell ss:Index="6"><Data ss:Type="String">I</Data></Cell>
    <Cell><Data ss:Type="String">S</Data></Cell>
    <Cell ss:Index="9"><Data ss:Type="String">A</Data></Cell>
    <Cell><Data ss:Type="String">N</Data></Cell>
    <Cell ss:Index="12"><Data ss:Type="String">E</Data></Cell>
    <Cell><Data ss:Type="String">X</Data></Cell>
    <Cell><Data ss:Type="String">A</Data></Cell>
    <Cell><Data ss:Type="String">M</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">P</Data></Cell>
    <Cell><Data ss:Type="String">L</Data></Cell>
    <Cell><Data ss:Type="String">E</Data></Cell>
    <Cell ss:Index="5"><Data ss:Type="String">O</Data></Cell>
    <Cell><Data ss:Type="String">F</Data></Cell>
    <Cell ss:Index="8"><Data ss:Type="String">A</Data></Cell>
    <Cell><Data ss:Type="String">N</Data></Cell>
    <Cell ss:Index="11"><Data ss:Type="String">E</Data></Cell>
    <Cell><Data ss:Type="String">X</Data></Cell>
    <Cell><Data ss:Type="String">C</Data></Cell>
    <Cell><Data ss:Type="String">E</Data></Cell>
    <Cell><Data ss:Type="String">L</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell ss:Index="6"><Data ss:Type="String">D</Data></Cell>
    <Cell><Data ss:Type="String">O</Data></Cell>
    <Cell><Data ss:Type="String">C</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
    <Cell><Data ss:Type="String">M</Data></Cell>
    <Cell><Data ss:Type="String">E</Data></Cell>
    <Cell><Data ss:Type="String">N</Data></Cell>
    <Cell><Data ss:Type="String">T</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">id</Data></Cell>
    <Cell><Data ss:Type="String">nama_kota</Data></Cell>
    <Cell><Data ss:Type="String">id_tipe</Data></Cell>
    <Cell><Data ss:Type="String">id_wilayah</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">Kab. Bogor</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">Kab. Sukabumi</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="String">Kab. Cianjur</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="String">Kab. Bandung</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <PageSetup>
    @@ -96,36 +84,4 @@
    <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">
    <Row ss:AutoFitHeight="0"/>
    </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>
    <Unsynced/>
    <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">
    <Row ss:AutoFitHeight="0"/>
    </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>
    <Unsynced/>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>
    30 changes: 0 additions & 30 deletions index.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,30 +0,0 @@
    <?php
    /**
    * Usage example
    */

    //ini_set('display_errors','On');

    // load the class
    require_once 'Excel2003XMLParser.php';

    // instantiate new object
    $excel = new Excel2003XMLParser('example.xml');

    echo "<pre>Output of column 3:\n";
    print_r($excel->getColumnData(3)); // print an array of all data within column 3

    echo "\nOutput of row 1:\n";
    print_r($excel->getRowData(1)); // print an array of all data within row 1

    echo "\nOutput of row 3 column 4:\n";
    echo $excel->getCellData(3,4); // print the data within row 3 column 4

    echo "\n\nOutput of all data:\n";
    print_r($excel->getTableData()); // print an array of all data
    echo "</pre>";

    // load another different XML file
    //$excel->loadXMLFile('example-2.xml');

    ?>
    1 change: 1 addition & 0 deletions readme.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1 @@
    This repository soon will be moved to https://github.com/faisalman/SimpleExcel
  3. @faisalman faisalman revised this gist Mar 10, 2011. 2 changed files with 4 additions and 132 deletions.
    6 changes: 4 additions & 2 deletions Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -32,7 +32,8 @@
    * @example see index.php
    * @license http://www.opensource.org/licenses/mit-license.php The MIT License
    * @link http://gist.github.com/862741
    * @version 10 Mar 2011
    * @todo Check row index
    * @version 11 Mar 2011
    */

    class Excel2003XMLParser
    @@ -168,8 +169,9 @@ public function loadXMLFile($url){
    // if exist, push empty value until the specified index
    if(count($cell_index) > 0){
    $gap = $cell_index[0]-count($row_arr);
    for($i = 0; $i < $gap; $i++){
    for($i = 1; $i < $gap; $i++){
    array_push($row_arr,"");
    $col_num += 1;
    }
    }

    130 changes: 0 additions & 130 deletions example.xml
    View file
    Original file line number Diff line number Diff line change
    @@ -1,261 +1,131 @@
    <?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>Faisalman</Author>

    <Keywords>Example file</Keywords>

    <LastAuthor>Faisalman</LastAuthor>

    <Created>2011-03-10T19:20:21Z</Created>

    <Version>12.00</Version>

    </DocumentProperties>

    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

    <WindowHeight>7935</WindowHeight>

    <WindowWidth>20055</WindowWidth>

    <WindowTopX>240</WindowTopX>

    <WindowTopY>75</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:CharSet="1" x:Family="Swiss" ss:Size="11"

    ss:Color="#000000"/>

    <Interior/>

    <NumberFormat/>

    <Protection/>

    </Style>

    </Styles>

    <Worksheet ss:Name="Sheet1">

    <Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="3" x:FullColumns="1"

    x:FullRows="1" ss:DefaultRowHeight="15">

    <Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="String">T</Data></Cell>

    <Cell><Data ss:Type="String">H</Data></Cell>

    <Cell><Data ss:Type="String">I</Data></Cell>

    <Cell><Data ss:Type="String">S</Data></Cell>

    <Cell ss:Index="6"><Data ss:Type="String">I</Data></Cell>

    <Cell><Data ss:Type="String">S</Data></Cell>

    <Cell ss:Index="9"><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">N</Data></Cell>

    <Cell ss:Index="12"><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">X</Data></Cell>

    <Cell><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">M</Data></Cell>

    </Row>

    <Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="String">P</Data></Cell>

    <Cell><Data ss:Type="String">L</Data></Cell>

    <Cell><Data ss:Type="String">E</Data></Cell>

    <Cell ss:Index="5"><Data ss:Type="String">O</Data></Cell>

    <Cell><Data ss:Type="String">F</Data></Cell>

    <Cell ss:Index="8"><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">N</Data></Cell>

    <Cell ss:Index="11"><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">X</Data></Cell>

    <Cell><Data ss:Type="String">C</Data></Cell>

    <Cell><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">L</Data></Cell>

    </Row>

    <Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="Number">2</Data></Cell>

    <Cell><Data ss:Type="Number">0</Data></Cell>

    <Cell><Data ss:Type="Number">0</Data></Cell>

    <Cell><Data ss:Type="Number">3</Data></Cell>

    <Cell ss:Index="6"><Data ss:Type="String">D</Data></Cell>

    <Cell><Data ss:Type="String">O</Data></Cell>

    <Cell><Data ss:Type="String">C</Data></Cell>

    <Cell><Data ss:Type="String">U</Data></Cell>

    <Cell><Data ss:Type="String">M</Data></Cell>

    <Cell><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">N</Data></Cell>

    <Cell><Data ss:Type="String">T</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>

    <Unsynced/>

    <Selected/>

    <Panes>

    <Pane>

    <Number>3</Number>

    <ActiveRow>2</ActiveRow>

    <ActiveCol>12</ActiveCol>

    </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">

    <Row ss:AutoFitHeight="0"/>

    </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>

    <Unsynced/>

    <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">

    <Row ss:AutoFitHeight="0"/>

    </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>

    <Unsynced/>

    <ProtectObjects>False</ProtectObjects>

    <ProtectScenarios>False</ProtectScenarios>

    </WorksheetOptions>

    </Worksheet>

    </Workbook>
  4. @faisalman faisalman revised this gist Mar 10, 2011. 3 changed files with 231 additions and 130 deletions.
    8 changes: 6 additions & 2 deletions Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -2,8 +2,12 @@
    /**
    * Excel 2003 XML-Parser
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    *
    * http://gist.github.com/862741
    *
    * Copyright (c) 2011 Faisalman <movedpixel@gmail.com>
    *
    * The MIT License
    * http://www.opensource.org/licenses/mit-license.php
    *
    * Permission is hereby granted, free of charge, to any person obtaining a copy
    * of this software and associated documentation files (the "Software"), to deal
    @@ -178,7 +182,7 @@ public function loadXMLFile($url){
    // push column array
    array_push($row_arr,array(
    "col_num" => $col_num,
    "col_contents" => strval($cell->Data),
    "col_contents" => htmlspecialchars(strval($cell->Data)), // convert data to string and escape HTML tags
    "cell_attrs" => $cell_attrs_arr,
    "data_attrs" => $data_attrs_arr
    ));
    318 changes: 212 additions & 106 deletions example.xml
    View file
    Original file line number Diff line number Diff line change
    @@ -1,155 +1,261 @@
    <?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>Faisalman</Author>

    <Keywords>Example file</Keywords>

    <LastAuthor>Faisalman</LastAuthor>
    <Created>2011-02-19T11:39:25Z</Created>
    <LastSaved>2011-02-19T12:28:14Z</LastSaved>
    <Company>-</Company>

    <Created>2011-03-10T19:20:21Z</Created>

    <Version>12.00</Version>

    </DocumentProperties>

    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>8895</WindowHeight>
    <WindowWidth>15135</WindowWidth>

    <WindowHeight>7935</WindowHeight>

    <WindowWidth>20055</WindowWidth>

    <WindowTopX>240</WindowTopX>
    <WindowTopY>120</WindowTopY>

    <WindowTopY>75</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"/>

    <Font ss:FontName="Calibri" x:CharSet="1" x:Family="Swiss" ss:Size="11"

    ss:Color="#000000"/>

    <Interior/>

    <NumberFormat/>

    <Protection/>

    </Style>
    <Style ss:ID="s62">
    <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s64">
    <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s65">
    <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s66">
    <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"
    ss:Bold="1"/>
    <Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s67">
    <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"
    ss:Bold="1"/>
    <Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s68">
    <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s69">
    <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    <NumberFormat ss:Format="#,##0"/>
    </Style>
    <Style ss:ID="s70">
    <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#FFFFFF"/>
    </Style>

    </Styles>

    <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="498" x:FullColumns="1"
    x:FullRows="1" ss:StyleID="s62" ss:DefaultRowHeight="15">
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="22.5"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="225"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="120"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="75"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="112.5" ss:Span="5"/>
    <Column ss:Index="11" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="150"/>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">T</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">H</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">I</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">I</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">X</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">M</Data></Cell>

    <Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="3" x:FullColumns="1"

    x:FullRows="1" ss:DefaultRowHeight="15">

    <Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="String">T</Data></Cell>

    <Cell><Data ss:Type="String">H</Data></Cell>

    <Cell><Data ss:Type="String">I</Data></Cell>

    <Cell><Data ss:Type="String">S</Data></Cell>

    <Cell ss:Index="6"><Data ss:Type="String">I</Data></Cell>

    <Cell><Data ss:Type="String">S</Data></Cell>

    <Cell ss:Index="9"><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">N</Data></Cell>

    <Cell ss:Index="12"><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">X</Data></Cell>

    <Cell><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">M</Data></Cell>

    </Row>

    <Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="String">P</Data></Cell>

    <Cell><Data ss:Type="String">L</Data></Cell>

    <Cell><Data ss:Type="String">E</Data></Cell>

    <Cell ss:Index="5"><Data ss:Type="String">O</Data></Cell>

    <Cell><Data ss:Type="String">F</Data></Cell>

    <Cell ss:Index="8"><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">N</Data></Cell>

    <Cell ss:Index="11"><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">X</Data></Cell>

    <Cell><Data ss:Type="String">C</Data></Cell>

    <Cell><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">L</Data></Cell>

    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">P</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">O</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">X</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">C</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>

    <Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="Number">2</Data></Cell>

    <Cell><Data ss:Type="Number">0</Data></Cell>

    <Cell><Data ss:Type="Number">0</Data></Cell>

    <Cell><Data ss:Type="Number">3</Data></Cell>

    <Cell ss:Index="6"><Data ss:Type="String">D</Data></Cell>

    <Cell><Data ss:Type="String">O</Data></Cell>

    <Cell><Data ss:Type="String">C</Data></Cell>

    <Cell><Data ss:Type="String">U</Data></Cell>

    <Cell><Data ss:Type="String">M</Data></Cell>

    <Cell><Data ss:Type="String">E</Data></Cell>

    <Cell><Data ss:Type="String">N</Data></Cell>

    <Cell><Data ss:Type="String">T</Data></Cell>

    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">2</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">0</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">0</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">3</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">D</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">O</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">U</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">M</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">T</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>

    <Unsynced/>
    <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
    </Print>

    <Selected/>
    <LeftColumnVisible>5</LeftColumnVisible>

    <Panes>

    <Pane>

    <Number>3</Number>

    <ActiveRow>2</ActiveRow>
    <ActiveCol>9</ActiveCol>

    <ActiveCol>12</ActiveCol>

    </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">

    <Row ss:AutoFitHeight="0"/>

    </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>

    <Unsynced/>

    <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">

    <Row ss:AutoFitHeight="0"/>

    </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>

    <Unsynced/>

    <ProtectObjects>False</ProtectObjects>

    <ProtectScenarios>False</ProtectScenarios>

    </WorksheetOptions>

    </Worksheet>

    </Workbook>
    35 changes: 13 additions & 22 deletions index.php
    View file
    Original file line number Diff line number Diff line change
    @@ -7,32 +7,23 @@

    // load the class
    require_once 'Excel2003XMLParser.php';

    // instantiate new object
    $excel = new Excel2003XMLParser('example.xml');
    $excel = new Excel2003XMLParser('example.xml');

    echo "<pre>Output of column 3:\n===========\n";

    // print an array of all data within column 3
    print_r($excel->getColumnData(3));
    echo "<pre>Output of column 3:\n";
    print_r($excel->getColumnData(3)); // print an array of all data within column 3

    echo "\nOutput of row 1:\n===========\n";

    // print an array of all data within row 1
    print_r($excel->getRowData(1));
    echo "\nOutput of row 1:\n";
    print_r($excel->getRowData(1)); // print an array of all data within row 1

    echo "\nOutput of row 3 column 4:\n";
    echo $excel->getCellData(3,4); // print the data within row 3 column 4

    echo "\nOutput of row 2 column 8:\n===========\n";

    // print the data within row 2 column 8
    echo $excel->getCellData(2,8);

    echo "\n\nOutput of all data:\n===========\n";

    // print an array of all data
    print_r($excel->getTableData());

    echo "</pre>";

    echo "\n\nOutput of all data:\n";
    print_r($excel->getTableData()); // print an array of all data
    echo "</pre>";

    // load another different XML file
    //$excel->loadXMLFile('example-2.xml');

  5. @faisalman faisalman revised this gist Mar 10, 2011. 1 changed file with 56 additions and 23 deletions.
    79 changes: 56 additions & 23 deletions Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,28 @@
    <?php

    /**
    * Excel 2003 XML-Parser
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    *
    * Copyright (c) 2011 Faisalman <movedpixel@gmail.com>
    *
    * Permission is hereby granted, free of charge, to any person obtaining a copy
    * of this software and associated documentation files (the "Software"), to deal
    * in the Software without restriction, including without limitation the rights
    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    * copies of the Software, and to permit persons to whom the Software is
    * furnished to do so, subject to the following conditions:
    *
    * The above copyright notice and this permission notice shall be included in
    * all copies or substantial portions of the Software.
    * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
    * THE SOFTWARE.
    *
    * @author Faisalman <movedpixel@gmail.com>
    * @copyright 2011 Faisalman
    * @example see index.php
    @@ -17,7 +36,8 @@ class Excel2003XMLParser
    /**
    *
    * this variable hold the array result
    *
    *
    * @access private
    * @var array
    */
    private $array_table;
    @@ -26,13 +46,14 @@ class Excel2003XMLParser
    *
    * this variable hold SimpleXML object
    *
    * @var object
    * @access private
    * @var simplexml object
    */
    private $simplexml_table;

    /**
    *
    * @param string $url
    * @param string $url Path to XML file
    */

    public function __construct($url){
    @@ -59,17 +80,17 @@ private function getAttributes($attrs_obj){

    /**
    *
    * @param int $row_num
    * @param int $col_num
    * @return array
    * @param int $row_num Row number
    * @param int $col_num Column number
    * @return string
    */
    public function getCellData($row_num,$col_num){
    return $this->array_table[$row_num-1]["row_contents"][$col_num-1]["col_contents"];
    }

    /**
    *
    * @param int $col_num
    * @param int $col_num Column number
    * @return array
    */
    public function getColumnData($col_num){
    @@ -84,7 +105,7 @@ public function getColumnData($col_num){

    /**
    *
    * @param int $row_num
    * @param int $row_num Row number
    * @return array
    */
    public function getRowData($row_num){
    @@ -98,6 +119,8 @@ public function getRowData($row_num){
    }

    /**
    *
    * use this method to get the complete array
    *
    * @return array
    */
    @@ -107,17 +130,23 @@ public function getTableData(){

    /**
    *
    * @param string $url
    * @param string $url Path to XML file
    * @return bool Returns TRUE if file exist, FALSE if does'nt
    */
    public function loadXMLFile($url){

    // create temporary simpleXML object
    $this->simplexml_table = simplexml_load_file($url);

    // assign simpleXML object
    if(!$this->simplexml_table = simplexml_load_file($url)) {

    // return immediately if there is an error
    return FALSE;
    }

    $rows = $this->simplexml_table->Worksheet->Table->Row;
    $this->array_table = array();
    $row_num = 1;

    // looping through all rows
    // loop through all rows
    foreach($rows as $row){

    $cells = $row->Cell;
    @@ -126,44 +155,48 @@ public function loadXMLFile($url){
    $row_arr = array();
    $col_num = 1;

    // looping through all row's cells
    // loop through row cells
    foreach($cells as $cell){

    // check whether ss:Index attribute exist
    $cell_index = $cell->xpath('@ss:Index');

    // if exist, push empty value until specified index
    // if exist, push empty value until the specified index
    if(count($cell_index) > 0){
    $gap = $cell_index[0]-count($row_arr);
    for($i = 0; $i < $gap; $i++){
    array_push($row_arr,"");
    }
    }

    $col_attrs = $cell->xpath('@ss:*');
    $col_attrs_arr = $this->getAttributes($col_attrs);
    // get all cell and data attributes
    $cell_attrs = $cell->xpath('@ss:*');
    $cell_attrs_arr = $this->getAttributes($cell_attrs);
    $data_attrs = $cell->Data->xpath('@ss:*');
    $data_attrs_arr = $this->getAttributes($data_attrs);

    // push array of columns
    // push column array
    array_push($row_arr,array(
    "col_num" => $col_num,
    "col_contents" => strval($cell->Data),
    "col_attrs" => $col_attrs_arr,
    "cell_attrs" => $cell_attrs_arr,
    "data_attrs" => $data_attrs_arr
    ));
    $col_num += 1;
    }

    // push array of rows
    // push row array
    array_push($this->array_table,array(
    "row_num" => $row_num,
    "row_contents" => $row_arr,
    "row_attrs" => $row_attrs_arr
    ));
    $row_num += 1;
    }

    // succeed :)
    return TRUE;
    }
    }

    ?>
    ?>
  6. @faisalman faisalman revised this gist Mar 10, 2011. 3 changed files with 193 additions and 56 deletions.
    172 changes: 132 additions & 40 deletions Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -3,34 +3,130 @@
    /**
    * Excel 2003 XML-Parser
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    * into multidimensional Array which represents row and column number
    * https://gist.github.com/862741
    *
    * Copyright 2011, Faisalman
    * Licensed under The MIT License
    * http://www.opensource.org/licenses/mit-license
    */
    * @author Faisalman <movedpixel@gmail.com>
    * @copyright 2011 Faisalman
    * @example see index.php
    * @license http://www.opensource.org/licenses/mit-license.php The MIT License
    * @link http://gist.github.com/862741
    * @version 10 Mar 2011
    */

    class Excel2003XMLParser
    {
    private $table;

    /**
    *
    * this variable hold the array result
    *
    * @var array
    */
    private $array_table;

    /**
    *
    * this variable hold SimpleXML object
    *
    * @var object
    */
    private $simplexml_table;

    /**
    *
    * @param string $url
    */

    public function __construct($url){
    $this->loadXMLFile($url);
    }


    /**
    *
    * @access private
    * @param object $attrs_obj
    * @return array
    */
    private function getAttributes($attrs_obj){
    $attrs_arr = array();
    foreach($attrs_obj as $attrs){
    $attrs = (array)$attrs;
    foreach($attrs as $attr){
    $attr_keys = array_keys($attr);
    $attrs_arr[$attr_keys[0]] = $attr[$attr_keys[0]];
    }
    }
    return $attrs_arr;
    }

    /**
    *
    * @param int $row_num
    * @param int $col_num
    * @return array
    */
    public function getCellData($row_num,$col_num){
    return $this->array_table[$row_num-1]["row_contents"][$col_num-1]["col_contents"];
    }

    /**
    *
    * @param int $col_num
    * @return array
    */
    public function getColumnData($col_num){
    $col_arr = array();

    // get the specified column within every row
    foreach($this->array_table as $row){
    array_push($col_arr,$row["row_contents"][$col_num-1]["col_contents"]);
    }
    return $col_arr;
    }

    /**
    *
    * @param int $row_num
    * @return array
    */
    public function getRowData($row_num){
    $row_arr = array();

    // get the specified column within every row
    foreach($this->array_table[$row_num-1]["row_contents"] as $col){
    array_push($row_arr,$col["col_contents"]);
    }
    return $row_arr;
    }

    /**
    *
    * @return array
    */
    public function getTableData(){
    return $this->array_table;
    }

    /**
    *
    * @param string $url
    */
    public function loadXMLFile($url){

    // create temporary simpleXML object
    $xml = simplexml_load_file($url);
    $rows = $xml->Worksheet->Table->Row;
    $table_arr = array(NULL);
    $this->simplexml_table = simplexml_load_file($url);
    $rows = $this->simplexml_table->Worksheet->Table->Row;
    $this->array_table = array();
    $row_num = 1;

    // looping through all rows
    foreach($rows as $row){

    $cells = $row->Cell;
    $row_arr = array(NULL);
    $row_attrs = $row->xpath('@ss:*');
    $row_attrs_arr = $this->getAttributes($row_attrs);
    $row_arr = array();
    $col_num = 1;

    // looping through all row's cells
    foreach($cells as $cell){

    // check whether ss:Index attribute exist
    @@ -40,38 +136,34 @@ public function loadXMLFile($url){
    if(count($cell_index) > 0){
    $gap = $cell_index[0]-count($row_arr);
    for($i = 0; $i < $gap; $i++){
    array_push($row_arr,NULL);
    array_push($row_arr,"");
    }
    }

    // push array of columns
    array_push($row_arr,strval($cell->Data));
    }

    // push array of rows
    array_push($table_arr,$row_arr);
    }

    $this->table = $table_arr;
    }

    public function getCellData($row_num,$col_num){
    return $this->table[$row_num][$col_num];
    }

    public function getColumnData($col_num){
    $col = array();
    $col_attrs = $cell->xpath('@ss:*');
    $col_attrs_arr = $this->getAttributes($col_attrs);
    $data_attrs = $cell->Data->xpath('@ss:*');
    $data_attrs_arr = $this->getAttributes($data_attrs);

    // push array of columns
    array_push($row_arr,array(
    "col_num" => $col_num,
    "col_contents" => strval($cell->Data),
    "col_attrs" => $col_attrs_arr,
    "data_attrs" => $data_attrs_arr
    ));
    $col_num += 1;
    }

    // get the specified column within every row
    foreach($this->table as $row){
    array_push($col,$row[$col_num]);
    // push array of rows
    array_push($this->array_table,array(
    "row_num" => $row_num,
    "row_contents" => $row_arr,
    "row_attrs" => $row_attrs_arr
    ));
    $row_num += 1;
    }
    return $col;
    }

    public function getRowData($row_num){
    return $this->table[$row_num];
    }
    }

    ?>
    ?>
    41 changes: 36 additions & 5 deletions example.xml
    View file
    Original file line number Diff line number Diff line change
    @@ -78,23 +78,54 @@
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="112.5" ss:Span="5"/>
    <Column ss:Index="11" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="150"/>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">T</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">H</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">I</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">I</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">X</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">M</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">P</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">O</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">-</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">X</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">C</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="45" ss:StyleID="s68" ss:Span="482"/>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">2</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">0</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">0</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">3</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">D</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">O</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">U</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">M</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">T</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <PageSetup>
    36 changes: 25 additions & 11 deletions index.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,25 +1,39 @@
    <?php

    /**
    * Usage example
    */


    //ini_set('display_errors','On');

    // load the class
    require_once 'Excel2003XMLParser.php';

    // instantiate new object
    $excel = new Excel2003XMLParser('example.xml');

    // print an array of all data within column 1
    print_r($excel->getColumnData(1));
    echo "<pre>Output of column 3:\n===========\n";

    // print an array of all data within column 3
    print_r($excel->getColumnData(3));

    // print an array of all data within row 14
    print_r($excel->getRowData(14));

    // print the data within row 15 column 2
    echo $excel->getCellData(15,2);
    echo "\nOutput of row 1:\n===========\n";

    // print an array of all data within row 1
    print_r($excel->getRowData(1));

    // load another different XML file:
    $excel->loadXMLFile('example-2.xml');
    echo "\nOutput of row 2 column 8:\n===========\n";

    // print the data within row 2 column 8
    echo $excel->getCellData(2,8);

    echo "\n\nOutput of all data:\n===========\n";

    // print an array of all data
    print_r($excel->getTableData());

    echo "</pre>";

    // load another different XML file
    //$excel->loadXMLFile('example-2.xml');

    ?>
  7. @faisalman faisalman revised this gist Mar 9, 2011. 1 changed file with 16 additions and 83 deletions.
    99 changes: 16 additions & 83 deletions example.xml
    View file
    Original file line number Diff line number Diff line change
    @@ -10,7 +10,7 @@
    <LastAuthor>Faisalman</LastAuthor>
    <Created>2011-02-19T11:39:25Z</Created>
    <LastSaved>2011-02-19T12:28:14Z</LastSaved>
    <Company>Faisalman</Company>
    <Company>-</Company>
    <Version>12.00</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    @@ -77,89 +77,22 @@
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="75"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="112.5" ss:Span="5"/>
    <Column ss:Index="11" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="150"/>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">FORMULIR C3</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">File ini dikhususkan untuk diupload ke online, disarankan untuk tidak mengubah susunan cell yang ada</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24"/>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:MergeAcross="10" ss:StyleID="s64"><Data ss:Type="String">DAFTAR USULAN KEGIATAN TAHUN ANGGARAN 2011</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24" ss:StyleID="s65">
    <Cell ss:MergeAcross="10" ss:StyleID="s64"><Data ss:Type="String">PROVINSI JAWA BARAT</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24"/>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">KOTA/KAB :</Data></Cell>
    <Cell><Data ss:Type="String">Kabupaten Bogor</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">BIDANG :</Data></Cell>
    <Cell><Data ss:Type="String">Fisik</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">PROGRAM :</Data></Cell>
    <Cell><Data ss:Type="String">Program peningkatan wajar diknas</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">TOLOK UKUR HASIL :</Data></Cell>
    <Cell><Data ss:Type="String">Terwujudnya wajar diknas 9 tahun</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">TARGET HASIL :</Data></Cell>
    <Cell><Data ss:Type="String">1000 siswa</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24"/>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">NO</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">KEGIATAN </Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">PRIORITAS DAERAH</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">SASARAN DAERAH</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">LOKASI</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TOLOK UKUR KELUARAN</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TARGET KELUARAN</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TOLOK UKUR HASIL</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TARGET HASIL</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">APBD KOTA (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">APBD PROV (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">APBN/PLHN (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">SUMBER DANA LAINNYA (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">PRAKIRAAN MAJU</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">JENIS KEGIATAN</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="84" ss:StyleID="s68">
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">Kegiatan pembangunan ruang kelas baru</Data></Cell>
    <Cell><Data ss:Type="String">Kecamatan Soreang Desa Sukamaju</Data></Cell>
    <Cell><Data ss:Type="String">Terbangunnya ruang kelas baru</Data></Cell>
    <Cell><Data ss:Type="String">30 unit</Data></Cell>
    <Cell><Data ss:Type="String">Terbangunnya ruang kelas baru untuk SD</Data></Cell>
    <Cell><Data ss:Type="String">30 unit</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">100</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">200</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">800</Data></Cell>
    <Cell><Data ss:Type="String">Lanjutan</Data></Cell>
    <Cell><Data ss:Type="String">-</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="84" ss:StyleID="s68">
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">Kegiatan pengadaan buku</Data></Cell>
    <Cell><Data ss:Type="String">Kecamatan Banjaran Desa Sukajati</Data></Cell>
    <Cell><Data ss:Type="String">Tersedianya buku SD</Data></Cell>
    <Cell><Data ss:Type="String">1000 paket</Data></Cell>
    <Cell><Data ss:Type="String">Termanfaatkannya buku baru oleh siswa</Data></Cell>
    <Cell><Data ss:Type="String">1000 paket</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">200</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">400</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">1000</Data></Cell>
    <Cell><Data ss:Type="String">Baru</Data></Cell>
    <Cell><Data ss:Type="String">-</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">I</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">M</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">N</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">-</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">X</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">C</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">L</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="45" ss:StyleID="s68" ss:Span="482"/>
    </Table>
  8. @faisalman faisalman revised this gist Mar 9, 2011. 3 changed files with 217 additions and 1 deletion.
    2 changes: 1 addition & 1 deletion Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@
    * Excel 2003 XML-Parser
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    * into multidimensional Array which represents row and column number
    * https://gist.github.com/faisalman
    * https://gist.github.com/862741
    *
    * Copyright 2011, Faisalman
    * Licensed under The MIT License
    191 changes: 191 additions & 0 deletions example.xml
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,191 @@
    <?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>Faisalman</Author>
    <LastAuthor>Faisalman</LastAuthor>
    <Created>2011-02-19T11:39:25Z</Created>
    <LastSaved>2011-02-19T12:28:14Z</LastSaved>
    <Company>Faisalman</Company>
    <Version>12.00</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>8895</WindowHeight>
    <WindowWidth>15135</WindowWidth>
    <WindowTopX>240</WindowTopX>
    <WindowTopY>120</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">
    <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s64">
    <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s65">
    <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s66">
    <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"
    ss:Bold="1"/>
    <Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s67">
    <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"
    ss:Bold="1"/>
    <Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s68">
    <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s69">
    <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
    <NumberFormat ss:Format="#,##0"/>
    </Style>
    <Style ss:ID="s70">
    <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
    <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#FFFFFF"/>
    </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="498" x:FullColumns="1"
    x:FullRows="1" ss:StyleID="s62" ss:DefaultRowHeight="15">
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="22.5"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="225"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="120"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="75"/>
    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="112.5" ss:Span="5"/>
    <Column ss:Index="11" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="150"/>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">FORMULIR C3</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">File ini dikhususkan untuk diupload ke online, disarankan untuk tidak mengubah susunan cell yang ada</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24"/>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:MergeAcross="10" ss:StyleID="s64"><Data ss:Type="String">DAFTAR USULAN KEGIATAN TAHUN ANGGARAN 2011</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24" ss:StyleID="s65">
    <Cell ss:MergeAcross="10" ss:StyleID="s64"><Data ss:Type="String">PROVINSI JAWA BARAT</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24"/>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">KOTA/KAB :</Data></Cell>
    <Cell><Data ss:Type="String">Kabupaten Bogor</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">BIDANG :</Data></Cell>
    <Cell><Data ss:Type="String">Fisik</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">PROGRAM :</Data></Cell>
    <Cell><Data ss:Type="String">Program peningkatan wajar diknas</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">TOLOK UKUR HASIL :</Data></Cell>
    <Cell><Data ss:Type="String">Terwujudnya wajar diknas 9 tahun</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24">
    <Cell ss:Index="2"><Data ss:Type="String">TARGET HASIL :</Data></Cell>
    <Cell><Data ss:Type="String">1000 siswa</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="24"/>
    <Row ss:AutoFitHeight="0" ss:Height="47.25">
    <Cell ss:StyleID="s66"><Data ss:Type="String">NO</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">KEGIATAN </Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">PRIORITAS DAERAH</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">SASARAN DAERAH</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">LOKASI</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TOLOK UKUR KELUARAN</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TARGET KELUARAN</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TOLOK UKUR HASIL</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">TARGET HASIL</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">APBD KOTA (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">APBD PROV (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">APBN/PLHN (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">SUMBER DANA LAINNYA (Rp.000)</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">PRAKIRAAN MAJU</Data></Cell>
    <Cell ss:StyleID="s66"><Data ss:Type="String">JENIS KEGIATAN</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="84" ss:StyleID="s68">
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">Kegiatan pembangunan ruang kelas baru</Data></Cell>
    <Cell><Data ss:Type="String">Kecamatan Soreang Desa Sukamaju</Data></Cell>
    <Cell><Data ss:Type="String">Terbangunnya ruang kelas baru</Data></Cell>
    <Cell><Data ss:Type="String">30 unit</Data></Cell>
    <Cell><Data ss:Type="String">Terbangunnya ruang kelas baru untuk SD</Data></Cell>
    <Cell><Data ss:Type="String">30 unit</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">100</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">200</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">800</Data></Cell>
    <Cell><Data ss:Type="String">Lanjutan</Data></Cell>
    <Cell><Data ss:Type="String">-</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="84" ss:StyleID="s68">
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">Kegiatan pengadaan buku</Data></Cell>
    <Cell><Data ss:Type="String">Kecamatan Banjaran Desa Sukajati</Data></Cell>
    <Cell><Data ss:Type="String">Tersedianya buku SD</Data></Cell>
    <Cell><Data ss:Type="String">1000 paket</Data></Cell>
    <Cell><Data ss:Type="String">Termanfaatkannya buku baru oleh siswa</Data></Cell>
    <Cell><Data ss:Type="String">1000 paket</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">200</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">400</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s69"><Data ss:Type="Number">1000</Data></Cell>
    <Cell><Data ss:Type="String">Baru</Data></Cell>
    <Cell><Data ss:Type="String">-</Data></Cell>
    </Row>
    <Row ss:AutoFitHeight="0" ss:Height="45" ss:StyleID="s68" ss:Span="482"/>
    </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>
    <Unsynced/>
    <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
    </Print>
    <Selected/>
    <LeftColumnVisible>5</LeftColumnVisible>
    <Panes>
    <Pane>
    <Number>3</Number>
    <ActiveRow>2</ActiveRow>
    <ActiveCol>9</ActiveCol>
    </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>
    25 changes: 25 additions & 0 deletions index.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    <?php

    /**
    * Usage example
    */

    // load the class
    require_once 'Excel2003XMLParser.php';

    // instantiate new object
    $excel = new Excel2003XMLParser('example.xml');

    // print an array of all data within column 1
    print_r($excel->getColumnData(1));

    // print an array of all data within row 14
    print_r($excel->getRowData(14));

    // print the data within row 15 column 2
    echo $excel->getCellData(15,2);

    // load another different XML file:
    $excel->loadXMLFile('example-2.xml');

    ?>
  9. @faisalman faisalman created this gist Mar 9, 2011.
    77 changes: 77 additions & 0 deletions Excel2003XMLParser.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,77 @@
    <?php

    /**
    * Excel 2003 XML-Parser
    * PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
    * into multidimensional Array which represents row and column number
    * https://gist.github.com/faisalman
    *
    * Copyright 2011, Faisalman
    * Licensed under The MIT License
    * http://www.opensource.org/licenses/mit-license
    */

    class Excel2003XMLParser
    {
    private $table;

    public function __construct($url){
    $this->loadXMLFile($url);
    }

    public function loadXMLFile($url){

    // create temporary simpleXML object
    $xml = simplexml_load_file($url);
    $rows = $xml->Worksheet->Table->Row;
    $table_arr = array(NULL);

    // looping through all rows
    foreach($rows as $row){
    $cells = $row->Cell;
    $row_arr = array(NULL);

    foreach($cells as $cell){

    // check whether ss:Index attribute exist
    $cell_index = $cell->xpath('@ss:Index');

    // if exist, push empty value until specified index
    if(count($cell_index) > 0){
    $gap = $cell_index[0]-count($row_arr);
    for($i = 0; $i < $gap; $i++){
    array_push($row_arr,NULL);
    }
    }

    // push array of columns
    array_push($row_arr,strval($cell->Data));
    }

    // push array of rows
    array_push($table_arr,$row_arr);
    }

    $this->table = $table_arr;
    }

    public function getCellData($row_num,$col_num){
    return $this->table[$row_num][$col_num];
    }

    public function getColumnData($col_num){
    $col = array();

    // get the specified column within every row
    foreach($this->table as $row){
    array_push($col,$row[$col_num]);
    }
    return $col;
    }

    public function getRowData($row_num){
    return $this->table[$row_num];
    }
    }

    ?>