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
PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
Raw
example.xml
<?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>-</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" 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>
</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>
<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>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
Raw
Excel2003XMLParser.php
<?php
/**
* Excel 2003 XML-Parser
* PHP class for parsing Microsoft Excel 2003 XML Spreadsheet
*
* @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
{
/**
*
* 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
$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_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
$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,"");
}
}
$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;
}
// 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;
}
}
}
?>
Raw
index.php
<?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===========\n";
// print an array of all data within column 3
print_r($excel->getColumnData(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 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');
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment