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>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>
Raw
Excel2003XMLParser.php
<?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/862741
*
* 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];
}
}
?>
Raw
index.php
<?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');
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment