build : SpreadsheetReader.php
<?php
/**
* Main class for spreadsheet reading
*
* @version 0.5.10
* @author Martins Pilsetnieks
*/
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = "XLSX";
const TYPE_XLS = "XLS";
const TYPE_CSV = "CSV";
const TYPE_ODS = "ODS";
private $Options = array(
"Delimiter" => "",
"Enclosure" => """
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception("SpreadsheetReader: File (".$Filepath.") not readable");
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we"re lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception("SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.");
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception("SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.");
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case "text/csv":
case "text/comma-separated-values":
case "text/plain":
$this -> Type = self::TYPE_CSV;
break;
case "application/vnd.ms-excel":
case "application/msexcel":
case "application/x-msexcel":
case "application/x-ms-excel":
case "application/vnd.ms-excel":
case "application/x-excel":
case "application/x-dos_ms_excel":
case "application/xls":
case "application/xlt":
case "application/x-xls":
// Excel does weird stuff
if (in_array($Extension, array("csv", "tsv", "txt")))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case "application/vnd.oasis.opendocument.spreadsheet":
case "application/vnd.oasis.opendocument.spreadsheet-template":
$this -> Type = self::TYPE_ODS;
break;
case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
case "application/vnd.openxmlformats-officedocument.spreadsheetml.template":
case "application/xlsx":
case "application/xltx":
$this -> Type = self::TYPE_XLSX;
break;
case "application/xml":
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case "xlsx":
case "xltx": // XLSX template
case "xlsm": // Macro-enabled XLSX
case "xltm": // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case "xls":
case "xlt":
$this -> Type = self::TYPE_XLS;
break;
case "ods":
case "odt":
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception("SpreadsheetReader: Invalid type (".$Type.")");
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn"t, it ends badly.
if (!class_exists("SpreadsheetReader_".$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR."SpreadsheetReader_".$Type.".php");
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind()
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current()
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next()
{
if ($this -> Handle)
{
$this -> Index++;
return $this -> Handle -> next();
}
return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key()
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we"ve iterated to the end of the collection
*
* @return boolean FALSE if there"s nothing more to iterate over
*/
public function valid()
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count()
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position)
{
if (!$this -> Handle)
{
throw new OutOfBoundsException("SpreadsheetReader: No file opened");
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException("SpreadsheetError: Position ".$Position." not found");
}
}
return null;
}
}
?>
THEN BUILD LIBRARY : SpreadsheetReader_XLS.php
<?php
/**
- Class for parsing XLS files *
-
@author Martins Pilsetnieks
/
class SpreadsheetReader_XLS implements Iterator, Countable
{
/*
* @var array Options array, pre-populated with the default values.
*/
private $Options = array(
);/** * @var resource File handle */ private $Handle = false; private $Index = 0; private $Error = false; /** * @var array Sheet information */ private $Sheets = false; private $SheetIndexes = array(); /** * @var int Current sheet index */ private $CurrentSheet = 0; /** * @var array Content of the current row */ private $CurrentRow = array(); /** * @var int Column count in the sheet */ private $ColumnCount = 0; /** * @var int Row count in the sheet */ private $RowCount = 0; /** * @var array Template to use for empty rows. Retrieved rows are merged * with this so that empty cells are added, too */ private $EmptyRow = array(); /** * @param string Path to file * @param array Options */ public function __construct($Filepath, array $Options = null) { if (!is_readable($Filepath)) { throw new Exception("SpreadsheetReader_XLS: File not readable (".$Filepath.")"); } if (!class_exists("Spreadsheet_Excel_Reader")) { throw new Exception("SpreadsheetReader_XLS: Spreadsheet_Excel_Reader class not available"); } $this -> Handle = new Spreadsheet_Excel_Reader($Filepath, false, "UTF-8"); if (function_exists("mb_convert_encoding")) { $this -> Handle -> setUTFEncoder("mb"); } if (empty($this -> Handle -> sheets)) { $this -> Error = true; return null; } $this -> ChangeSheet(0); } public function __destruct() { unset($this -> Handle); } /** * Retrieves an array with information about sheets in the current file * * @return array List of sheets (key is sheet index, value is name) */ public function Sheets() { if ($this -> Sheets === false) { $this -> Sheets = array(); $this -> SheetIndexes = array_keys($this -> Handle -> sheets); foreach ($this -> SheetIndexes as $SheetIndex) { $this -> Sheets[] = $this -> Handle -> boundsheets[$SheetIndex]["name"]; } } return $this -> Sheets; } /** * Changes the current sheet in the file to another * * @param int Sheet index * * @return bool True if sheet was successfully changed, false otherwise. */ public function ChangeSheet($Index) { $Index = (int)$Index; $Sheets = $this -> Sheets(); if (isset($this -> Sheets[$Index])) { $this -> rewind(); $this -> CurrentSheet = $this -> SheetIndexes[$Index]; $this -> ColumnCount = $this -> Handle -> sheets[$this -> CurrentSheet]["numCols"]; $this -> RowCount = $this -> Handle -> sheets[$this -> CurrentSheet]["numRows"]; // For the case when Spreadsheet_Excel_Reader doesn"t have the row count set correctly. if (!$this -> RowCount && count($this -> Handle -> sheets[$this -> CurrentSheet]["cells"])) { end($this -> Handle -> sheets[$this -> CurrentSheet]["cells"]); $this -> RowCount = (int)key($this -> Handle -> sheets[$this -> CurrentSheet]["cells"]); } if ($this -> ColumnCount) { $this -> EmptyRow = array_fill(1, $this -> ColumnCount, ""); } else { $this -> EmptyRow = array(); } } return false; } public function __get($Name) { switch ($Name) { case "Error": return $this -> Error; break; } return null; } // !Iterator interface methods /** * Rewind the Iterator to the first element. * Similar to the reset() function for arrays in PHP */ public function rewind() { $this -> Index = 0; } /** * Return the current element. * Similar to the current() function for arrays in PHP * * @return mixed current element from the collection */ public function current() { if ($this -> Index == 0) { $this -> next(); } return $this -> CurrentRow; } /** * Move forward to next element. * Similar to the next() function for arrays in PHP */ public function next() { // Internal counter is advanced here instead of the if statement // because apparently it"s fully possible that an empty row will not be // present at all $this -> Index++; if ($this -> Error) { return array(); } elseif (isset($this -> Handle -> sheets[$this -> CurrentSheet]["cells"][$this -> Index])) { $this -> CurrentRow = $this -> Handle -> sheets[$this -> CurrentSheet]["cells"][$this -> Index]; if (!$this -> CurrentRow) { return array(); } $this -> CurrentRow = $this -> CurrentRow + $this -> EmptyRow; ksort($this -> CurrentRow); $this -> CurrentRow = array_values($this -> CurrentRow); return $this -> CurrentRow; } else { $this -> CurrentRow = $this -> EmptyRow; return $this -> CurrentRow; } } /** * Return the identifying key of the current element. * Similar to the key() function for arrays in PHP * * @return mixed either an integer or a string */ public function key() { return $this -> Index; } /** * Check if there is a current element after calls to rewind() or next(). * Used to check if we"ve iterated to the end of the collection * * @return boolean FALSE if there"s nothing more to iterate over */ public function valid() { if ($this -> Error) { return false; } return ($this -> Index <= $this -> RowCount); } // !Countable interface method /** * Ostensibly should return the count of the contained items but this just returns the number * of rows read so far. It"s not really correct but at least coherent. */ public function count() { if ($this -> Error) { return 0; } return $this -> RowCount; }}
?>
THEN BUILD XLSX LIBRARY : SpreadsheetReader_XLSX.php
<?php
/**
- Class for parsing XLSX files specifically *
-
@author Martins Pilsetnieks
*/
class SpreadsheetReader_XLSX implements Iterator, Countable
{
const CELL_TYPE_BOOL = "b";
const CELL_TYPE_NUMBER = "n";
const CELL_TYPE_ERROR = "e";
const CELL_TYPE_SHARED_STR = "s";
const CELL_TYPE_STR = "str";
const CELL_TYPE_INLINE_STR = "inlineStr";/** * Number of shared strings that can be reasonably cached, i.e., that aren"t read from file but stored in memory. * If the total number of shared strings is higher than this, caching is not used. * If this value is null, shared strings are cached regardless of amount. * With large shared string caches there are huge performance gains, however a lot of memory could be used which * can be a problem, especially on shared hosting. */ const SHARED_STRING_CACHE_LIMIT = 50000; private $Options = array( "TempDir" => "", "ReturnDateTimeObjects" => false ); private static $RuntimeInfo = array( "GMPSupported" => false ); private $Valid = false; /** * @var SpreadsheetReader_* Handle for the reader object */ private $Handle = false; // Worksheet file /** * @var string Path to the worksheet XML file */ private $WorksheetPath = false; /** * @var XMLReader XML reader object for the worksheet XML file */ private $Worksheet = false; // Shared strings file /** * @var string Path to shared strings XML file */ private $SharedStringsPath = false; /** * @var XMLReader XML reader object for the shared strings XML file */ private $SharedStrings = false; /** * @var array Shared strings cache, if the number of shared strings is low enough */ private $SharedStringCache = array(); // Workbook data /** * @var SimpleXMLElement XML object for the workbook XML file */ private $WorkbookXML = false; // Style data /** * @var SimpleXMLElement XML object for the styles XML file */ private $StylesXML = false; /** * @var array Container for cell value style data */ private $Styles = array(); private $TempDir = ""; private $TempFiles = array(); private $CurrentRow = false; // Runtime parsing data /** * @var int Current row in the file */ private $Index = 0; /** * @var array Data about separate sheets in the file */ private $Sheets = false; private $SharedStringCount = 0; private $SharedStringIndex = 0; private $LastSharedStringValue = null; private $RowOpen = false; private $SSOpen = false; private $SSForwarded = false; private static $BuiltinFormats = array( 0 => "", 1 => "0", 2 => "0.00", 3 => "#,##0", 4 => "#,##0.00", 9 => "0%", 10 => "0.00%", 11 => "0.00E+00", 12 => "# ?/?", 13 => "# ??/??", 14 => "mm-dd-yy", 15 => "d-mmm-yy", 16 => "d-mmm", 17 => "mmm-yy", 18 => "h:mm AM/PM", 19 => "h:mm:ss AM/PM", 20 => "h:mm", 21 => "h:mm:ss", 22 => "m/d/yy h:mm", 37 => "#,##0 ;(#,##0)", 38 => "#,##0 ;[Red](#,##0)", 39 => "#,##0.00;(#,##0.00)", 40 => "#,##0.00;[Red](#,##0.00)", 45 => "mm:ss", 46 => "[h]:mm:ss", 47 => "mmss.0", 48 => "##0.0E+0", 49 => "@", // CHT & CHS 27 => "[$-404]e/m/d", 30 => "m/d/yy", 36 => "[$-404]e/m/d", 50 => "[$-404]e/m/d", 57 => "[$-404]e/m/d", // THA 59 => "t0", 60 => "t0.00", 61 =>"t#,##0", 62 => "t#,##0.00", 67 => "t0%", 68 => "t0.00%", 69 => "t# ?/?", 70 => "t# ??/??" ); private $Formats = array(); private static $DateReplacements = array( "All" => array( "\\" => "", "am/pm" => "A", "yyyy" => "Y", "yy" => "y", "mmmmm" => "M", "mmmm" => "F", "mmm" => "M", ":mm" => ":i", "mm" => "m", "m" => "n", "dddd" => "l", "ddd" => "D", "dd" => "d", "d" => "j", "ss" => "s", ".s" => "" ), "24H" => array( "hh" => "H", "h" => "G" ), "12H" => array( "hh" => "h", "h" => "G" ) ); private static $BaseDate = false; private static $DecimalSeparator = "."; private static $ThousandSeparator = ""; private static $CurrencyCode = ""; /** * @var array Cache for already processed format strings */ private $ParsedFormatCache = array(); /** * @param string Path to file * @param array Options: * TempDir => string Temporary directory path * ReturnDateTimeObjects => bool True => dates and times will be returned as PHP DateTime objects, false => as strings */ public function __construct($Filepath, array $Options = null) { if (!is_readable($Filepath)) { throw new Exception("SpreadsheetReader_XLSX: File not readable (".$Filepath.")"); } $this -> TempDir = isset($Options["TempDir"]) && is_writable($Options["TempDir"]) ? $Options["TempDir"] : sys_get_temp_dir(); $this -> TempDir = rtrim($this -> TempDir, DIRECTORY_SEPARATOR); $this -> TempDir = $this -> TempDir.DIRECTORY_SEPARATOR.uniqid().DIRECTORY_SEPARATOR; $Zip = new ZipArchive; $Status = $Zip -> open($Filepath); if ($Status !== true) { throw new Exception("SpreadsheetReader_XLSX: File not readable (".$Filepath.") (Error ".$Status.")"); } // Getting the general workbook information if ($Zip -> locateName("xl/workbook.xml") !== false) { $this -> WorkbookXML = new SimpleXMLElement($Zip -> getFromName("xl/workbook.xml")); } // Extracting the XMLs from the XLSX zip file if ($Zip -> locateName("xl/sharedStrings.xml") !== false) { $this -> SharedStringsPath = $this -> TempDir."xl".DIRECTORY_SEPARATOR."sharedStrings.xml"; $Zip -> extractTo($this -> TempDir, "xl/sharedStrings.xml"); $this -> TempFiles[] = $this -> TempDir."xl".DIRECTORY_SEPARATOR."sharedStrings.xml"; if (is_readable($this -> SharedStringsPath)) { $this -> SharedStrings = new XMLReader; $this -> SharedStrings -> open($this -> SharedStringsPath); $this -> PrepareSharedStringCache(); } } $Sheets = $this -> Sheets(); foreach ($this -> Sheets as $Index => $Name) { if ($Zip -> locateName("xl/worksheets/sheet".$Index.".xml") !== false) { $Zip -> extractTo($this -> TempDir, "xl/worksheets/sheet".$Index.".xml"); $this -> TempFiles[] = $this -> TempDir."xl".DIRECTORY_SEPARATOR."worksheets".DIRECTORY_SEPARATOR."sheet".$Index.".xml"; } } $this -> ChangeSheet(0); // If worksheet is present and is OK, parse the styles already if ($Zip -> locateName("xl/styles.xml") !== false) { $this -> StylesXML = new SimpleXMLElement($Zip -> getFromName("xl/styles.xml")); if ($this -> StylesXML && $this -> StylesXML -> cellXfs && $this -> StylesXML -> cellXfs -> xf) { foreach ($this -> StylesXML -> cellXfs -> xf as $Index => $XF) { // Format #0 is a special case - it is the "General" format that is applied regardless of applyNumberFormat if ($XF -> attributes() -> applyNumberFormat || (0 == (int)$XF -> attributes() -> numFmtId)) { $FormatId = (int)$XF -> attributes() -> numFmtId; // If format ID >= 164, it is a custom format and should be read from styleSheet\numFmts $this -> Styles[] = $FormatId; } else { // 0 for "General" format $this -> Styles[] = 0; } } } if ($this -> StylesXML -> numFmts && $this -> StylesXML -> numFmts -> numFmt) { foreach ($this -> StylesXML -> numFmts -> numFmt as $Index => $NumFmt) { $this -> Formats[(int)$NumFmt -> attributes() -> numFmtId] = (string)$NumFmt -> attributes() -> formatCode; } } unset($this -> StylesXML); } $Zip -> close(); // Setting base date if (!self::$BaseDate) { self::$BaseDate = new DateTime; self::$BaseDate -> setTimezone(new DateTimeZone("UTC")); self::$BaseDate -> setDate(1900, 1, 0); self::$BaseDate -> setTime(0, 0, 0); } // Decimal and thousand separators if (!self::$DecimalSeparator && !self::$ThousandSeparator && !self::$CurrencyCode) { $Locale = localeconv(); self::$DecimalSeparator = $Locale["decimal_point"]; self::$ThousandSeparator = $Locale["thousands_sep"]; self::$CurrencyCode = $Locale["int_curr_symbol"]; } if (function_exists("gmp_gcd")) { self::$RuntimeInfo["GMPSupported"] = true; } } /** * Destructor, destroys all that remains (closes and deletes temp files) */ public function __destruct() { foreach ($this -> TempFiles as $TempFile) { @unlink($TempFile); } // Better safe than sorry - shouldn"t try deleting "." or "/", or "..". if (strlen($this -> TempDir) > 2) { @rmdir($this -> TempDir."xl".DIRECTORY_SEPARATOR."worksheets"); @rmdir($this -> TempDir."xl"); @rmdir($this -> TempDir); } if ($this -> Worksheet && $this -> Worksheet instanceof XMLReader) { $this -> Worksheet -> close(); unset($this -> Worksheet); } unset($this -> WorksheetPath); if ($this -> SharedStrings && $this -> SharedStrings instanceof XMLReader) { $this -> SharedStrings -> close(); unset($this -> SharedStrings); } unset($this -> SharedStringsPath); if (isset($this -> StylesXML)) { unset($this -> StylesXML); } if ($this -> WorkbookXML) { unset($this -> WorkbookXML); } } /** * Retrieves an array with information about sheets in the current file * * @return array List of sheets (key is sheet index, value is name) */ public function Sheets() { if ($this -> Sheets === false) { $this -> Sheets = array(); foreach ($this -> WorkbookXML -> sheets -> sheet as $Index => $Sheet) { $Attributes = $Sheet -> attributes("r", true); foreach ($Attributes as $Name => $Value) { if ($Name == "id") { $SheetID = (int)str_replace("rId", "", (string)$Value); break; } } $this -> Sheets[$SheetID] = (string)$Sheet["name"]; } ksort($this -> Sheets); } return array_values($this -> Sheets); } /** * Changes the current sheet in the file to another * * @param int Sheet index * * @return bool True if sheet was successfully changed, false otherwise. */ public function ChangeSheet($Index) { $RealSheetIndex = false; $Sheets = $this -> Sheets(); if (isset($Sheets[$Index])) { $SheetIndexes = array_keys($this -> Sheets); $RealSheetIndex = $SheetIndexes[$Index]; } $TempWorksheetPath = $this -> TempDir."xl/worksheets/sheet".$RealSheetIndex.".xml"; if ($RealSheetIndex !== false && is_readable($TempWorksheetPath)) { $this -> WorksheetPath = $TempWorksheetPath; $this -> rewind(); return true; } return false; } /** * Creating shared string cache if the number of shared strings is acceptably low (or there is no limit on the amount */ private function PrepareSharedStringCache() { while ($this -> SharedStrings -> read()) { if ($this -> SharedStrings -> name == "sst") { $this -> SharedStringCount = $this -> SharedStrings -> getAttribute("count"); break; } } if (!$this -> SharedStringCount || (self::SHARED_STRING_CACHE_LIMIT < $this -> SharedStringCount && self::SHARED_STRING_CACHE_LIMIT !== null)) { return false; } $CacheIndex = 0; $CacheValue = ""; while ($this -> SharedStrings -> read()) { switch ($this -> SharedStrings -> name) { case "si": if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT) { $this -> SharedStringCache[$CacheIndex] = $CacheValue; $CacheIndex++; $CacheValue = ""; } break; case "t": if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT) { continue; } $CacheValue .= $this -> SharedStrings -> readString(); break; } } $this -> SharedStrings -> close(); return true; } /** * Retrieves a shared string value by its index * * @param int Shared string index * * @return string Value */ private function GetSharedString($Index) { if ((self::SHARED_STRING_CACHE_LIMIT === null || self::SHARED_STRING_CACHE_LIMIT > 0) && !empty($this -> SharedStringCache)) { if (isset($this -> SharedStringCache[$Index])) { return $this -> SharedStringCache[$Index]; } else { return ""; } } // If the desired index is before the current, rewind the XML if ($this -> SharedStringIndex > $Index) { $this -> SSOpen = false; $this -> SharedStrings -> close(); $this -> SharedStrings -> open($this -> SharedStringsPath); $this -> SharedStringIndex = 0; $this -> LastSharedStringValue = null; $this -> SSForwarded = false; } // Finding the unique string count (if not already read) if ($this -> SharedStringIndex == 0 && !$this -> SharedStringCount) { while ($this -> SharedStrings -> read()) { if ($this -> SharedStrings -> name == "sst") { $this -> SharedStringCount = $this -> SharedStrings -> getAttribute("uniqueCount"); break; } } } // If index of the desired string is larger than possible, don"t even bother. if ($this -> SharedStringCount && ($Index >= $this -> SharedStringCount)) { return ""; } // If an index with the same value as the last already fetched is requested // (any further traversing the tree would get us further away from the node) if (($Index == $this -> SharedStringIndex) && ($this -> LastSharedStringValue !== null)) { return $this -> LastSharedStringValue; } // Find the correct <si> node with the desired index while ($this -> SharedStringIndex <= $Index) { // SSForwarded is set further to avoid double reading in case nodes are skipped. if ($this -> SSForwarded) { $this -> SSForwarded = false; } else { $ReadStatus = $this -> SharedStrings -> read(); if (!$ReadStatus) { break; } } if ($this -> SharedStrings -> name == "si") { if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT) { $this -> SSOpen = false; $this -> SharedStringIndex++; } else { $this -> SSOpen = true; if ($this -> SharedStringIndex < $Index) { $this -> SSOpen = false; $this -> SharedStrings -> next("si"); $this -> SSForwarded = true; $this -> SharedStringIndex++; continue; } else { break; } } } } $Value = ""; // Extract the value from the shared string if ($this -> SSOpen && ($this -> SharedStringIndex == $Index)) { while ($this -> SharedStrings -> read()) { switch ($this -> SharedStrings -> name) { case "t": if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT) { continue; } $Value .= $this -> SharedStrings -> readString(); break; case "si": if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT) { $this -> SSOpen = false; $this -> SSForwarded = true; break 2; } break; } } } if ($Value) { $this -> LastSharedStringValue = $Value; } return $Value; } /** * Formats the value according to the index * * @param string Cell value * @param int Format index * * @return string Formatted cell value */ private function FormatValue($Value, $Index) { if (!is_numeric($Value)) { return $Value; } if (isset($this -> Styles[$Index]) && ($this -> Styles[$Index] !== false)) { $Index = $this -> Styles[$Index]; } else { return $Value; } // A special case for the "General" format if ($Index == 0) { return $this -> GeneralFormat($Value); } $Format = array(); if (isset($this -> ParsedFormatCache[$Index])) { $Format = $this -> ParsedFormatCache[$Index]; } if (!$Format) { $Format = array( "Code" => false, "Type" => false, "Scale" => 1, "Thousands" => false, "Currency" => false ); if (isset(self::$BuiltinFormats[$Index])) { $Format["Code"] = self::$BuiltinFormats[$Index]; } elseif (isset($this -> Formats[$Index])) { $Format["Code"] = $this -> Formats[$Index]; } // Format code found, now parsing the format if ($Format["Code"]) { $Sections = explode(";", $Format["Code"]); $Format["Code"] = $Sections[0]; switch (count($Sections)) { case 2: if ($Value < 0) { $Format["Code"] = $Sections[1]; } break; case 3: case 4: if ($Value < 0) { $Format["Code"] = $Sections[1]; } elseif ($Value == 0) { $Format["Code"] = $Sections[2]; } break; } } // Stripping colors $Format["Code"] = trim(preg_replace("{^\[[[:alpha:]]+\]}i", "", $Format["Code"])); // Percentages if (substr($Format["Code"], -1) == "%") { $Format["Type"] = "Percentage"; } elseif (preg_match("{^(\[\$[[:alpha:]]*-[0-9A-F]*\])*[hmsdy]}i", $Format["Code"])) { $Format["Type"] = "DateTime"; $Format["Code"] = trim(preg_replace("{^(\[\$[[:alpha:]]*-[0-9A-F]*\])}i", "", $Format["Code"])); $Format["Code"] = strtolower($Format["Code"]); $Format["Code"] = strtr($Format["Code"], self::$DateReplacements["All"]); if (strpos($Format["Code"], "A") === false) { $Format["Code"] = strtr($Format["Code"], self::$DateReplacements["24H"]); } else { $Format["Code"] = strtr($Format["Code"], self::$DateReplacements["12H"]); } } elseif ($Format["Code"] == "[$EUR ]#,##0.00_-") { $Format["Type"] = "Euro"; } else { // Removing skipped characters $Format["Code"] = preg_replace("{_.}", "", $Format["Code"]); // Removing unnecessary escaping $Format["Code"] = preg_replace("{\\\\}", "", $Format["Code"]); // Removing string quotes $Format["Code"] = str_replace(array(""", "*"), "", $Format["Code"]); // Removing thousands separator if (strpos($Format["Code"], "0,0") !== false || strpos($Format["Code"], "#,#") !== false) { $Format["Thousands"] = true; } $Format["Code"] = str_replace(array("0,0", "#,#"), array("00", "##"), $Format["Code"]); // Scaling (Commas indicate the power) $Scale = 1; $Matches = array(); if (preg_match("{(0|#)(,+)}", $Format["Code"], $Matches)) { $Scale = pow(1000, strlen($Matches[2])); // Removing the commas $Format["Code"] = preg_replace(array("{0,+}", "{#,+}"), array("0", "#"), $Format["Code"]); } $Format["Scale"] = $Scale; if (preg_match("{#?.*\?\/\?}", $Format["Code"])) { $Format["Type"] = "Fraction"; } else { $Format["Code"] = str_replace("#", "", $Format["Code"]); $Matches = array(); if (preg_match("{(0+)(\.?)(0*)}", preg_replace("{\[[^\]]+\]}", "", $Format["Code"]), $Matches)) { $Integer = $Matches[1]; $DecimalPoint = $Matches[2]; $Decimals = $Matches[3]; $Format["MinWidth"] = strlen($Integer) + strlen($DecimalPoint) + strlen($Decimals); $Format["Decimals"] = $Decimals; $Format["Precision"] = strlen($Format["Decimals"]); $Format["Pattern"] = "%0".$Format["MinWidth"].".".$Format["Precision"]."f"; } } $Matches = array(); if (preg_match("{\[\$(.*)\]}u", $Format["Code"], $Matches)) { $CurrFormat = $Matches[0]; $CurrCode = $Matches[1]; $CurrCode = explode("-", $CurrCode); if ($CurrCode) { $CurrCode = $CurrCode[0]; } if (!$CurrCode) { $CurrCode = self::$CurrencyCode; } $Format["Currency"] = $CurrCode; } $Format["Code"] = trim($Format["Code"]); } $this -> ParsedFormatCache[$Index] = $Format; } // Applying format to value if ($Format) { if ($Format["Code"] == "@") { return (string)$Value; } // Percentages elseif ($Format["Type"] == "Percentage") { if ($Format["Code"] === "0%") { $Value = round(100 * $Value, 0)."%"; } else { $Value = sprintf("%.2f%%", round(100 * $Value, 2)); } } // Dates and times elseif ($Format["Type"] == "DateTime") { $Days = (int)$Value; // Correcting for Feb 29, 1900 if ($Days > 60) { $Days--; } // At this point time is a fraction of a day $Time = ($Value - (int)$Value); $Seconds = 0; if ($Time) { // Here time is converted to seconds // Some loss of precision will occur $Seconds = (int)($Time * 86400); } $Value = clone self::$BaseDate; $Value -> add(new DateInterval("P".$Days."D".($Seconds ? "T".$Seconds."S" : ""))); if (!$this -> Options["ReturnDateTimeObjects"]) { $Value = $Value -> format($Format["Code"]); } else { // A DateTime object is returned } } elseif ($Format["Type"] == "Euro") { $Value = "EUR ".sprintf("%1.2f", $Value); } else { // Fractional numbers if ($Format["Type"] == "Fraction" && ($Value != (int)$Value)) { $Integer = floor(abs($Value)); $Decimal = fmod(abs($Value), 1); // Removing the integer part and decimal point $Decimal *= pow(10, strlen($Decimal) - 2); $DecimalDivisor = pow(10, strlen($Decimal)); if (self::$RuntimeInfo["GMPSupported"]) { $GCD = gmp_strval(gmp_gcd($Decimal, $DecimalDivisor)); } else { $GCD = self::GCD($Decimal, $DecimalDivisor); } $AdjDecimal = $DecimalPart/$GCD; $AdjDecimalDivisor = $DecimalDivisor/$GCD; if ( strpos($Format["Code"], "0") !== false || strpos($Format["Code"], "#") !== false || substr($Format["Code"], 0, 3) == "? ?" ) { // The integer part is shown separately apart from the fraction $Value = ($Value < 0 ? "-" : ""). $Integer ? $Integer." " : "". $AdjDecimal."/". $AdjDecimalDivisor; } else { // The fraction includes the integer part $AdjDecimal += $Integer * $AdjDecimalDivisor; $Value = ($Value < 0 ? "-" : ""). $AdjDecimal."/". $AdjDecimalDivisor; } } else { // Scaling $Value = $Value / $Format["Scale"]; if (!empty($Format["MinWidth"]) && $Format["Decimals"]) { if ($Format["Thousands"]) { $Value = number_format($Value, $Format["Precision"], self::$DecimalSeparator, self::$ThousandSeparator); } else { $Value = sprintf($Format["Pattern"], $Value); } $Value = preg_replace("{(0+)(\.?)(0*)}", $Value, $Format["Code"]); } } // Currency/Accounting if ($Format["Currency"]) { $Value = preg_replace("", $Format["Currency"], $Value); } } } return $Value; } /** * Attempts to approximate Excel"s "general" format. * * @param mixed Value * * @return mixed Result */ public function GeneralFormat($Value) { // Numeric format if (is_numeric($Value)) { $Value = (float)$Value; } return $Value; } // !Iterator interface methods /** * Rewind the Iterator to the first element. * Similar to the reset() function for arrays in PHP */ public function rewind() { // Removed the check whether $this -> Index == 0 otherwise ChangeSheet doesn"t work properly // If the worksheet was already iterated, XML file is reopened. // Otherwise it should be at the beginning anyway if ($this -> Worksheet instanceof XMLReader) { $this -> Worksheet -> close(); } else { $this -> Worksheet = new XMLReader; } $this -> Worksheet -> open($this -> WorksheetPath); $this -> Valid = true; $this -> RowOpen = false; $this -> CurrentRow = false; $this -> Index = 0; } /** * Return the current element. * Similar to the current() function for arrays in PHP * * @return mixed current element from the collection */ public function current() { if ($this -> Index == 0 && $this -> CurrentRow === false) { $this -> next(); $this -> Index--; } return $this -> CurrentRow; } /** * Move forward to next element. * Similar to the next() function for arrays in PHP */ public function next() { $this -> Index++; $this -> CurrentRow = array(); if (!$this -> RowOpen) { while ($this -> Valid = $this -> Worksheet -> read()) { if ($this -> Worksheet -> name == "row") { // Getting the row spanning area (stored as e.g., 1:12) // so that the last cells will be present, even if empty $RowSpans = $this -> Worksheet -> getAttribute("spans"); if ($RowSpans) { $RowSpans = explode(":", $RowSpans); $CurrentRowColumnCount = $RowSpans[1]; } else { $CurrentRowColumnCount = 0; } if ($CurrentRowColumnCount > 0) { $this -> CurrentRow = array_fill(0, $CurrentRowColumnCount, ""); } $this -> RowOpen = true; break; } } } // Reading the necessary row, if found if ($this -> RowOpen) { // These two are needed to control for empty cells $MaxIndex = 0; $CellCount = 0; $CellHasSharedString = false; while ($this -> Valid = $this -> Worksheet -> read()) { switch ($this -> Worksheet -> name) { // End of row case "row": if ($this -> Worksheet -> nodeType == XMLReader::END_ELEMENT) { $this -> RowOpen = false; break 2; } break; // Cell case "c": // If it is a closing tag, skip it if ($this -> Worksheet -> nodeType == XMLReader::END_ELEMENT) { continue; } $StyleId = (int)$this -> Worksheet -> getAttribute("s"); // Get the index of the cell $Index = $this -> Worksheet -> getAttribute("r"); $Letter = preg_replace("{[^[:alpha:]]}S", "", $Index); $Index = self::IndexFromColumnLetter($Letter); // Determine cell type if ($this -> Worksheet -> getAttribute("t") == self::CELL_TYPE_SHARED_STR) { $CellHasSharedString = true; } else { $CellHasSharedString = false; } $this -> CurrentRow[$Index] = ""; $CellCount++; if ($Index > $MaxIndex) { $MaxIndex = $Index; } break; // Cell value case "v": case "is": if ($this -> Worksheet -> nodeType == XMLReader::END_ELEMENT) { continue; } $Value = $this -> Worksheet -> readString(); if ($CellHasSharedString) { $Value = $this -> GetSharedString($Value); } // Format value if necessary if ($Value !== "" && $StyleId && isset($this -> Styles[$StyleId])) { $Value = $this -> FormatValue($Value, $StyleId); } elseif ($Value) { $Value = $this -> GeneralFormat($Value); } $this -> CurrentRow[$Index] = $Value; break; } } // Adding empty cells, if necessary // Only empty cells inbetween and on the left side are added if ($MaxIndex + 1 > $CellCount) { $this -> CurrentRow = $this -> CurrentRow + array_fill(0, $MaxIndex + 1, ""); ksort($this -> CurrentRow); } } return $this -> CurrentRow; } /** * Return the identifying key of the current element. * Similar to the key() function for arrays in PHP * * @return mixed either an integer or a string */ public function key() { return $this -> Index; } /** * Check if there is a current element after calls to rewind() or next(). * Used to check if we"ve iterated to the end of the collection * * @return boolean FALSE if there"s nothing more to iterate over */ public function valid() { return $this -> Valid; } // !Countable interface method /** * Ostensibly should return the count of the contained items but this just returns the number * of rows read so far. It"s not really correct but at least coherent. */ public function count() { return $this -> Index + 1; } /** * Takes the column letter and converts it to a numerical index (0-based) * * @param string Letter(s) to convert * * @return mixed Numeric index (0-based) or boolean false if it cannot be calculated */ public static function IndexFromColumnLetter($Letter) { $Powers = array(); $Letter = strtoupper($Letter); $Result = 0; for ($i = strlen($Letter) - 1, $j = 0; $i >= 0; $i--, $j++) { $Ord = ord($Letter[$i]) - 64; if ($Ord > 26) { // Something is very, very wrong return false; } $Result += $Ord * pow(26, $j); } return $Result - 1; } /** * Helper function for greatest common divisor calculation in case GMP extension is * not enabled * * @param int Number #1 * @param int Number #2 * * @param int Greatest common divisor */ public static function GCD($A, $B) { $A = abs($A); $B = abs($B); if ($A + $B == 0) { return 0; } else { $C = 1; while ($A > 0) { $C = $A; $A = $B % $A; $B = $C; } return $C; } }}
?>
example Call :
require("SpreadsheetReader.php");
$Reader = new SpreadsheetReader($inputfile);
$totalCount = 0;
$test=0;
foreach ($Reader as $Row)
{
$count_rows = $Row;
foreach($count_rows as $countRow){
if($countRow != ""){
$taa = explode(" ",$countRow);
$tCount = count($taa);
$result[]=$countRow." ";
}
}
}
return implode("",$result);
FULL REFERENCE
Top comments (0)