/**
* @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) {
if ($XF->attributes()->applyNumberFormat) {
$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 {
$this->Styles[] = false;
}
}
}
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;
}
}
/**
* @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('xlsx', false) . 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 = $workbook = new SimpleXMLElement($Zip->getFromName('xl/workbook.xml'));
$workbook->registerXPathNamespace('w', self::workbook_ns);
$workbook->registerXPathNamespace('r', self::doc_rels_ns);
$elements = $workbook->xpath('/w:workbook/w:sheets/w:sheet[@r:id and @name]');
$relation_prefix = array_search(self::doc_rels_ns, $workbook->getDocNamespaces());
$index = [];
$map = [];
foreach ($elements as $elem) {
$id = (string) $elem->attributes($relation_prefix, true)['id'];
$name = (string) $elem['name'];
$map[$id] = $name;
$index[] = $id;
}
$this->Sheets = $map;
$this->SheetIndexesToIds = $index;
}
// Getting the general workbook information
if ($Zip->locateName('xl/_rels/workbook.xml.rels') !== false) {
$this->WorkbookRels = $relations = new SimpleXMLElement($Zip->getFromName('xl/_rels/workbook.xml.rels'));
$relations->registerXPathNamespace('r', self::pkg_rels_ns);
$elements = $relations->xpath('/r:Relationships/r:Relationship[@Id and @Target and ' . '@Type = \'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\']');
$map = [];
foreach ($elements as $elem) {
$id = (string) $elem['Id'];
$target = (string) $elem['Target'];
$inZipPath = 'xl/' . $target;
if ($Zip->locateName($inZipPath) !== false) {
$Zip->extractTo($this->TempDir, $inZipPath);
$map[$id] = $target;
}
}
$this->SheetIdsToFiles = $map;
}
// 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();
}
}
//$this -> Sheets = $this -> Sheets();
/* bullhockey
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;
}
}
