| 1 |
<?php |
| 2 |
/** |
| 3 |
* Excel 2007-2019/Office 365 Reader Class |
| 4 |
* |
| 5 |
* Based on SimpleXLSX v1.0.16 by Sergey Shuchkin. |
| 6 |
* @link https://github.com/shuchkin/simplexlsx/ |
| 7 |
* |
| 8 |
* @package TablePress |
| 9 |
* @subpackage Import |
| 10 |
* @author Sergey Shuchkin, Tobias Bäthge |
| 11 |
* @since 1.1.0 |
| 12 |
*/ |
| 13 |
|
| 14 |
namespace Shuchkin; |
| 15 |
|
| 16 |
use SimpleXMLElement; |
| 17 |
|
| 18 |
/** |
| 19 |
* PHP Excel 2007-2013/Office 365 Reader Class |
| 20 |
* @package TablePress |
| 21 |
* @subpackage Import |
| 22 |
* @author Sergey Shuchkin, Tobias Bäthge |
| 23 |
* @since 1.1.0 |
| 24 |
*/ |
| 25 |
class SimpleXLSX { |
| 26 |
public static $CF = [ // Cell formats |
| 27 |
0 => 'General', |
| 28 |
1 => '0', |
| 29 |
2 => '0.00', |
| 30 |
3 => '#,##0', |
| 31 |
4 => '#,##0.00', |
| 32 |
9 => '0%', |
| 33 |
10 => '0.00%', |
| 34 |
11 => '0.00E+00', |
| 35 |
12 => '# ?/?', |
| 36 |
13 => '# ??/??', |
| 37 |
14 => 'mm-dd-yy', |
| 38 |
15 => 'd-mmm-yy', |
| 39 |
16 => 'd-mmm', |
| 40 |
17 => 'mmm-yy', |
| 41 |
18 => 'h:mm AM/PM', |
| 42 |
19 => 'h:mm:ss AM/PM', |
| 43 |
20 => 'h:mm', |
| 44 |
21 => 'h:mm:ss', |
| 45 |
22 => 'm/d/yy h:mm', |
| 46 |
|
| 47 |
37 => '#,##0 ;(#,##0)', |
| 48 |
38 => '#,##0 ;[Red](#,##0)', |
| 49 |
39 => '#,##0.00;(#,##0.00)', |
| 50 |
40 => '#,##0.00;[Red](#,##0.00)', |
| 51 |
|
| 52 |
44 => '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)', |
| 53 |
45 => 'mm:ss', |
| 54 |
46 => '[h]:mm:ss', |
| 55 |
47 => 'mmss.0', |
| 56 |
48 => '##0.0E+0', |
| 57 |
49 => '@', |
| 58 |
|
| 59 |
27 => '[$-404]e/m/d', |
| 60 |
30 => 'm/d/yy', |
| 61 |
36 => '[$-404]e/m/d', |
| 62 |
50 => '[$-404]e/m/d', |
| 63 |
57 => '[$-404]e/m/d', |
| 64 |
|
| 65 |
59 => 't0', |
| 66 |
60 => 't0.00', |
| 67 |
61 => 't#,##0', |
| 68 |
62 => 't#,##0.00', |
| 69 |
67 => 't0%', |
| 70 |
68 => 't0.00%', |
| 71 |
69 => 't# ?/?', |
| 72 |
70 => 't# ??/??', |
| 73 |
]; |
| 74 |
public $nf = []; // number formats |
| 75 |
public $cellFormats = []; // cellXfs |
| 76 |
public $datetimeFormat = 'Y-m-d H:i:s'; |
| 77 |
public $debug; |
| 78 |
public $activeSheet = 0; |
| 79 |
public $rowsExReader; |
| 80 |
|
| 81 |
/* @var SimpleXMLElement[] $sheets */ |
| 82 |
protected $sheets; |
| 83 |
protected $sheetNames = []; |
| 84 |
protected $sheetFiles = []; |
| 85 |
// scheme |
| 86 |
public $styles; |
| 87 |
protected $hyperlinks; |
| 88 |
/* @var array[] $package */ |
| 89 |
protected $package; |
| 90 |
protected $sharedstrings; |
| 91 |
protected $date1904 = 0; |
| 92 |
|
| 93 |
|
| 94 |
/* |
| 95 |
private $date_formats = array( |
| 96 |
0xe => "d/m/Y", |
| 97 |
0xf => "d-M-Y", |
| 98 |
0x10 => "d-M", |
| 99 |
0x11 => "M-Y", |
| 100 |
0x12 => "h:i a", |
| 101 |
0x13 => "h:i:s a", |
| 102 |
0x14 => "H:i", |
| 103 |
0x15 => "H:i:s", |
| 104 |
0x16 => "d/m/Y H:i", |
| 105 |
0x2d => "i:s", |
| 106 |
0x2e => "H:i:s", |
| 107 |
0x2f => "i:s.S" |
| 108 |
); |
| 109 |
private $number_formats = array( |
| 110 |
0x1 => "%1.0f", // "0" |
| 111 |
0x2 => "%1.2f", // "0.00", |
| 112 |
0x3 => "%1.0f", //"#,##0", |
| 113 |
0x4 => "%1.2f", //"#,##0.00", |
| 114 |
0x5 => "%1.0f", //"$#,##0;($#,##0)", |
| 115 |
0x6 => '$%1.0f', //"$#,##0;($#,##0)", |
| 116 |
0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)", |
| 117 |
0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)", |
| 118 |
0x9 => '%1.0f%%', //"0%" |
| 119 |
0xa => '%1.2f%%', //"0.00%" |
| 120 |
0xb => '%1.2f', //"0.00E00", |
| 121 |
0x25 => '%1.0f', //"#,##0;(#,##0)", |
| 122 |
0x26 => '%1.0f', //"#,##0;(#,##0)", |
| 123 |
0x27 => '%1.2f', //"#,##0.00;(#,##0.00)", |
| 124 |
0x28 => '%1.2f', //"#,##0.00;(#,##0.00)", |
| 125 |
0x29 => '%1.0f', //"#,##0;(#,##0)", |
| 126 |
0x2a => '$%1.0f', //"$#,##0;($#,##0)", |
| 127 |
0x2b => '%1.2f', //"#,##0.00;(#,##0.00)", |
| 128 |
0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)", |
| 129 |
0x30 => '%1.0f'); //"##0.0E0"; |
| 130 |
// }}} |
| 131 |
*/ |
| 132 |
protected $errno = 0; |
| 133 |
protected $error = false; |
| 134 |
/** |
| 135 |
* @var false|SimpleXMLElement |
| 136 |
*/ |
| 137 |
public $theme; |
| 138 |
|
| 139 |
|
| 140 |
public function __construct($filename = null, $is_data = null, $debug = null) |
| 141 |
{ |
| 142 |
if ($debug !== null) { |
| 143 |
$this->debug = $debug; |
| 144 |
} |
| 145 |
$this->package = [ |
| 146 |
'filename' => '', |
| 147 |
'mtime' => 0, |
| 148 |
'size' => 0, |
| 149 |
'comment' => '', |
| 150 |
'entries' => [] |
| 151 |
]; |
| 152 |
if ($filename && $this->_unzip($filename, $is_data)) { |
| 153 |
$this->_parse(); |
| 154 |
} |
| 155 |
} |
| 156 |
|
| 157 |
protected function _unzip($filename, $is_data = false) |
| 158 |
{ |
| 159 |
|
| 160 |
if ($is_data) { |
| 161 |
$this->package['filename'] = 'default.xlsx'; |
| 162 |
$this->package['mtime'] = time(); |
| 163 |
$this->package['size'] = $this->_strlen($filename); |
| 164 |
|
| 165 |
$vZ = $filename; |
| 166 |
} else { |
| 167 |
if (!is_readable($filename)) { |
| 168 |
$this->error(1, 'File not found ' . $filename); |
| 169 |
|
| 170 |
return false; |
| 171 |
} |
| 172 |
|
| 173 |
// Package information |
| 174 |
$this->package['filename'] = $filename; |
| 175 |
$this->package['mtime'] = filemtime($filename); |
| 176 |
$this->package['size'] = filesize($filename); |
| 177 |
|
| 178 |
// Read file |
| 179 |
$vZ = file_get_contents($filename); |
| 180 |
} |
| 181 |
// Cut end of central directory |
| 182 |
/* $aE = explode("\x50\x4b\x05\x06", $vZ); |
| 183 |
|
| 184 |
if (count($aE) == 1) { |
| 185 |
$this->error('Unknown format'); |
| 186 |
return false; |
| 187 |
} |
| 188 |
*/ |
| 189 |
// Explode to each part |
| 190 |
$aE = explode("\x50\x4b\x03\x04", $vZ); |
| 191 |
array_shift($aE); |
| 192 |
|
| 193 |
$aEL = count($aE); |
| 194 |
if ($aEL === 0) { |
| 195 |
$this->error(2, 'Unknown archive format'); |
| 196 |
|
| 197 |
return false; |
| 198 |
} |
| 199 |
// Search central directory end record |
| 200 |
$last = $aE[$aEL - 1]; |
| 201 |
$last = explode("\x50\x4b\x05\x06", $last); |
| 202 |
if (count($last) !== 2) { |
| 203 |
$this->error(2, 'Unknown archive format'); |
| 204 |
|
| 205 |
return false; |
| 206 |
} |
| 207 |
// Search central directory |
| 208 |
$last = explode("\x50\x4b\x01\x02", $last[0]); |
| 209 |
if (count($last) < 2) { |
| 210 |
$this->error(2, 'Unknown archive format'); |
| 211 |
|
| 212 |
return false; |
| 213 |
} |
| 214 |
$aE[$aEL - 1] = $last[0]; |
| 215 |
|
| 216 |
// Loop through the entries |
| 217 |
foreach ($aE as $vZ) { |
| 218 |
$aI = []; |
| 219 |
$aI['E'] = 0; |
| 220 |
$aI['EM'] = ''; |
| 221 |
// Retrieving local file header information |
| 222 |
// $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL', $vZ); |
| 223 |
$aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL/v1EFL', $vZ); |
| 224 |
|
| 225 |
// Check if data is encrypted |
| 226 |
// $bE = ($aP['GPF'] && 0x0001) ? TRUE : FALSE; |
| 227 |
// $bE = false; |
| 228 |
$nF = $aP['FNL']; |
| 229 |
$mF = $aP['EFL']; |
| 230 |
|
| 231 |
// Special case : value block after the compressed data |
| 232 |
if ($aP['GPF'] & 0x0008) { |
| 233 |
$aP1 = unpack('V1CRC/V1CS/V1UCS', $this->_substr($vZ, -12)); |
| 234 |
|
| 235 |
$aP['CRC'] = $aP1['CRC']; |
| 236 |
$aP['CS'] = $aP1['CS']; |
| 237 |
$aP['UCS'] = $aP1['UCS']; |
| 238 |
// 2013-08-10 |
| 239 |
$vZ = $this->_substr($vZ, 0, -12); |
| 240 |
if ($this->_substr($vZ, -4) === "\x50\x4b\x07\x08") { |
| 241 |
$vZ = $this->_substr($vZ, 0, -4); |
| 242 |
} |
| 243 |
} |
| 244 |
|
| 245 |
// Getting stored filename |
| 246 |
$aI['N'] = $this->_substr($vZ, 26, $nF); |
| 247 |
$aI['N'] = str_replace('\\', '/', $aI['N']); |
| 248 |
|
| 249 |
if ($this->_substr($aI['N'], -1) === '/') { |
| 250 |
// is a directory entry - will be skipped |
| 251 |
continue; |
| 252 |
} |
| 253 |
|
| 254 |
// Truncate full filename in path and filename |
| 255 |
$aI['P'] = dirname($aI['N']); |
| 256 |
$aI['P'] = ($aI['P'] === '.') ? '' : $aI['P']; |
| 257 |
$aI['N'] = basename($aI['N']); |
| 258 |
|
| 259 |
$vZ = $this->_substr($vZ, 26 + $nF + $mF); |
| 260 |
|
| 261 |
if ($this->_strlen($vZ) !== (int)$aP['CS']) { // check only if availabled |
| 262 |
$aI['E'] = 1; |
| 263 |
$aI['EM'] = 'Compressed size is not equal with the value in header information.'; |
| 264 |
} |
| 265 |
// } elseif ( $bE ) { |
| 266 |
// $aI['E'] = 5; |
| 267 |
// $aI['EM'] = 'File is encrypted, which is not supported from this class.'; |
| 268 |
/* } else { |
| 269 |
switch ($aP['CM']) { |
| 270 |
case 0: // Stored |
| 271 |
// Here is nothing to do, the file ist flat. |
| 272 |
break; |
| 273 |
case 8: // Deflated |
| 274 |
$vZ = gzinflate($vZ); |
| 275 |
break; |
| 276 |
case 12: // BZIP2 |
| 277 |
if (extension_loaded('bz2')) { |
| 278 |
$vZ = bzdecompress($vZ); |
| 279 |
} else { |
| 280 |
$aI['E'] = 7; |
| 281 |
$aI['EM'] = 'PHP BZIP2 extension not available.'; |
| 282 |
} |
| 283 |
break; |
| 284 |
default: |
| 285 |
$aI['E'] = 6; |
| 286 |
$aI['EM'] = "De-/Compression method {$aP['CM']} is not supported."; |
| 287 |
} |
| 288 |
if (!$aI['E']) { |
| 289 |
if ($vZ === false) { |
| 290 |
$aI['E'] = 2; |
| 291 |
$aI['EM'] = 'Decompression of data failed.'; |
| 292 |
} elseif ($this->_strlen($vZ) !== (int)$aP['UCS']) { |
| 293 |
$aI['E'] = 3; |
| 294 |
$aI['EM'] = 'Uncompressed size is not equal with the value in header information.'; |
| 295 |
} elseif (crc32($vZ) !== $aP['CRC']) { |
| 296 |
$aI['E'] = 4; |
| 297 |
$aI['EM'] = 'CRC32 checksum is not equal with the value in header information.'; |
| 298 |
} |
| 299 |
} |
| 300 |
} |
| 301 |
*/ |
| 302 |
|
| 303 |
// DOS to UNIX timestamp |
| 304 |
$aI['T'] = mktime( |
| 305 |
($aP['FT'] & 0xf800) >> 11, |
| 306 |
($aP['FT'] & 0x07e0) >> 5, |
| 307 |
($aP['FT'] & 0x001f) << 1, |
| 308 |
($aP['FD'] & 0x01e0) >> 5, |
| 309 |
$aP['FD'] & 0x001f, |
| 310 |
(($aP['FD'] & 0xfe00) >> 9) + 1980 |
| 311 |
); |
| 312 |
|
| 313 |
$this->package['entries'][] = [ |
| 314 |
'data' => $vZ, |
| 315 |
'ucs' => (int)$aP['UCS'], // ucompresses size |
| 316 |
'cm' => $aP['CM'], // compressed method |
| 317 |
'cs' => isset($aP['CS']) ? (int) $aP['CS'] : 0, // compresses size |
| 318 |
'crc' => $aP['CRC'], |
| 319 |
'error' => $aI['E'], |
| 320 |
'error_msg' => $aI['EM'], |
| 321 |
'name' => $aI['N'], |
| 322 |
'path' => $aI['P'], |
| 323 |
'time' => $aI['T'] |
| 324 |
]; |
| 325 |
} // end for each entries |
| 326 |
|
| 327 |
return true; |
| 328 |
} |
| 329 |
|
| 330 |
protected function _strlen($str) |
| 331 |
{ |
| 332 |
return (ini_get('mbstring.func_overload') & 2) ? mb_strlen($str, '8bit') : strlen($str); |
| 333 |
} |
| 334 |
|
| 335 |
public function error($num = null, $str = null) |
| 336 |
{ |
| 337 |
if ($num) { |
| 338 |
$this->errno = $num; |
| 339 |
$this->error = $str; |
| 340 |
if ($this->debug) { |
| 341 |
trigger_error(__CLASS__ . ': ' . $this->error, E_USER_WARNING); |
| 342 |
} |
| 343 |
} |
| 344 |
|
| 345 |
return $this->error; |
| 346 |
} |
| 347 |
|
| 348 |
protected function _substr($str, $start, $length = null) |
| 349 |
{ |
| 350 |
return (ini_get('mbstring.func_overload') & 2) ? mb_substr($str, $start, ($length === null) ? mb_strlen($str, '8bit') : $length, '8bit') : substr($str, $start, ($length === null) ? strlen($str) : $length); |
| 351 |
} |
| 352 |
|
| 353 |
protected function _parse() |
| 354 |
{ |
| 355 |
// Document data holders |
| 356 |
$this->sharedstrings = []; |
| 357 |
$this->sheets = []; |
| 358 |
// $this->styles = array(); |
| 359 |
// $m1 = 0; // memory_get_peak_usage( true ); |
| 360 |
// Read relations and search for officeDocument |
| 361 |
if ($relations = $this->getEntryXML('_rels/.rels')) { |
| 362 |
foreach ($relations->Relationship as $rel) { |
| 363 |
$rel_type = basename(trim((string)$rel['Type'])); // officeDocument |
| 364 |
$rel_target = $this->_getTarget('', (string)$rel['Target']); // /xl/workbook.xml or xl/workbook.xml |
| 365 |
|
| 366 |
if ($rel_type === 'officeDocument' && $workbook = $this->getEntryXML($rel_target)) { |
| 367 |
$index_rId = []; // [0 => rId1] |
| 368 |
|
| 369 |
$index = 0; |
| 370 |
foreach ($workbook->sheets->sheet as $s) { |
| 371 |
$this->sheetNames[$index] = (string)$s['name']; |
| 372 |
$index_rId[$index] = (string)$s['id']; |
| 373 |
$index++; |
| 374 |
} |
| 375 |
if ((int)$workbook->workbookPr['date1904'] === 1) { |
| 376 |
$this->date1904 = 1; |
| 377 |
} |
| 378 |
|
| 379 |
|
| 380 |
if ($workbookRelations = $this->getEntryXML(dirname($rel_target) . '/_rels/workbook.xml.rels')) { |
| 381 |
// Loop relations for workbook and extract sheets... |
| 382 |
foreach ($workbookRelations->Relationship as $workbookRelation) { |
| 383 |
$wrel_type = basename(trim((string)$workbookRelation['Type'])); // worksheet |
| 384 |
$wrel_path = $this->_getTarget(dirname($rel_target), (string)$workbookRelation['Target']); |
| 385 |
if (!$this->entryExists($wrel_path)) { |
| 386 |
continue; |
| 387 |
} |
| 388 |
|
| 389 |
|
| 390 |
if ($wrel_type === 'worksheet') { // Sheets |
| 391 |
if ($sheet = $this->getEntryXML($wrel_path)) { |
| 392 |
$index = array_search((string)$workbookRelation['Id'], $index_rId, true); |
| 393 |
$this->sheets[$index] = $sheet; |
| 394 |
$this->sheetFiles[$index] = $wrel_path; |
| 395 |
} |
| 396 |
} elseif ($wrel_type === 'sharedStrings') { |
| 397 |
if ($sharedStrings = $this->getEntryXML($wrel_path)) { |
| 398 |
foreach ($sharedStrings->si as $val) { |
| 399 |
if (isset($val->t)) { |
| 400 |
$this->sharedstrings[] = (string)$val->t; |
| 401 |
} elseif (isset($val->r)) { |
| 402 |
$this->sharedstrings[] = $this->_parseRichText($val); |
| 403 |
} |
| 404 |
} |
| 405 |
} |
| 406 |
} elseif ($wrel_type === 'styles') { |
| 407 |
$this->styles = $this->getEntryXML($wrel_path); |
| 408 |
|
| 409 |
// number formats |
| 410 |
$this->nf = []; |
| 411 |
if (isset($this->styles->numFmts->numFmt)) { |
| 412 |
foreach ($this->styles->numFmts->numFmt as $v) { |
| 413 |
$this->nf[(int)$v['numFmtId']] = (string)$v['formatCode']; |
| 414 |
} |
| 415 |
} |
| 416 |
|
| 417 |
$this->cellFormats = []; |
| 418 |
if (isset($this->styles->cellXfs->xf)) { |
| 419 |
foreach ($this->styles->cellXfs->xf as $v) { |
| 420 |
$x = [ |
| 421 |
'format' => null |
| 422 |
]; |
| 423 |
foreach ($v->attributes() as $k1 => $v1) { |
| 424 |
$x[ $k1 ] = (int) $v1; |
| 425 |
} |
| 426 |
if (isset($x['numFmtId'])) { |
| 427 |
if (isset($this->nf[$x['numFmtId']])) { |
| 428 |
$x['format'] = $this->nf[$x['numFmtId']]; |
| 429 |
} elseif (isset(self::$CF[$x['numFmtId']])) { |
| 430 |
$x['format'] = self::$CF[$x['numFmtId']]; |
| 431 |
} |
| 432 |
} |
| 433 |
|
| 434 |
$this->cellFormats[] = $x; |
| 435 |
} |
| 436 |
} |
| 437 |
} elseif ($wrel_type === 'theme') { |
| 438 |
$this->theme = $this->getEntryXML($wrel_path); |
| 439 |
} |
| 440 |
} |
| 441 |
|
| 442 |
break; |
| 443 |
} |
| 444 |
// reptile hack :: find active sheet from workbook.xml |
| 445 |
foreach ($workbook->bookViews->workbookView as $s) { |
| 446 |
if (!empty($s['activeTab'])) { |
| 447 |
$this->activeSheet = (int)$s['activeTab']; |
| 448 |
} |
| 449 |
} |
| 450 |
} |
| 451 |
} |
| 452 |
} |
| 453 |
|
| 454 |
// $m2 = memory_get_peak_usage(true); |
| 455 |
// echo __FUNCTION__.' M='.round( ($m2-$m1) / 1048576, 2).'MB'.PHP_EOL; |
| 456 |
|
| 457 |
if (count($this->sheets)) { |
| 458 |
// Sort sheets |
| 459 |
ksort($this->sheets); |
| 460 |
|
| 461 |
return true; |
| 462 |
} |
| 463 |
|
| 464 |
return false; |
| 465 |
} |
| 466 |
|
| 467 |
public function getEntryXML($name) |
| 468 |
{ |
| 469 |
if ($entry_xml = $this->getEntryData($name)) { |
| 470 |
$this->deleteEntry($name); // economy memory |
| 471 |
// dirty remove namespace prefixes and empty rows |
| 472 |
$entry_xml = preg_replace('/xmlns[^=]*="[^"]*"/i', '', $entry_xml); // remove namespaces |
| 473 |
$entry_xml .= ' '; // force run garbage collector |
| 474 |
$entry_xml = preg_replace('/[a-zA-Z0-9]+:([a-zA-Z0-9]+="[^"]+")/', '$1', $entry_xml); // remove namespaced attrs |
| 475 |
$entry_xml .= ' '; |
| 476 |
$entry_xml = preg_replace('/<[a-zA-Z0-9]+:([^>]+)>/', '<$1>', $entry_xml); // fix namespaced openned tags |
| 477 |
$entry_xml .= ' '; |
| 478 |
$entry_xml = preg_replace('/<\/[a-zA-Z0-9]+:([^>]+)>/', '</$1>', $entry_xml); // fix namespaced closed tags |
| 479 |
$entry_xml .= ' '; |
| 480 |
|
| 481 |
if (strpos($name, '/sheet')) { // dirty skip empty rows |
| 482 |
// remove <row...> <c /><c /></row> |
| 483 |
$entry_xml = preg_replace('/<row[^>]+>\s*(<c[^\/]+\/>\s*)+<\/row>/', '', $entry_xml, -1, $cnt); |
| 484 |
$entry_xml .= ' '; |
| 485 |
// remove <row /> |
| 486 |
$entry_xml = preg_replace('/<row[^\/>]*\/>/', '', $entry_xml, -1, $cnt2); |
| 487 |
$entry_xml .= ' '; |
| 488 |
// remove <row...></row> |
| 489 |
$entry_xml = preg_replace('/<row[^>]*><\/row>/', '', $entry_xml, -1, $cnt3); |
| 490 |
$entry_xml .= ' '; |
| 491 |
if ($cnt || $cnt2 || $cnt3) { |
| 492 |
$entry_xml = preg_replace('/<dimension[^\/]+\/>/', '', $entry_xml); |
| 493 |
$entry_xml .= ' '; |
| 494 |
} |
| 495 |
// file_put_contents( basename( $name ), $entry_xml ); // @to do comment!!! |
| 496 |
} |
| 497 |
$entry_xml = trim($entry_xml); |
| 498 |
|
| 499 |
// $m1 = memory_get_usage(); |
| 500 |
// XML External Entity (XXE) Prevention, libxml_disable_entity_loader deprecated in PHP 8 |
| 501 |
if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { |
| 502 |
$_old = libxml_disable_entity_loader(); |
| 503 |
} |
| 504 |
|
| 505 |
$_old_uie = libxml_use_internal_errors(true); |
| 506 |
|
| 507 |
$entry_xmlobj = simplexml_load_string($entry_xml, 'SimpleXMLElement', LIBXML_COMPACT | LIBXML_PARSEHUGE); |
| 508 |
|
| 509 |
libxml_use_internal_errors($_old_uie); |
| 510 |
|
| 511 |
if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { |
| 512 |
/** @noinspection PhpUndefinedVariableInspection */ |
| 513 |
libxml_disable_entity_loader($_old); |
| 514 |
} |
| 515 |
|
| 516 |
// $m2 = memory_get_usage(); |
| 517 |
// echo round( ($m2-$m1) / (1024 * 1024), 2).' MB'.PHP_EOL; |
| 518 |
|
| 519 |
if ($entry_xmlobj) { |
| 520 |
return $entry_xmlobj; |
| 521 |
} |
| 522 |
$e = libxml_get_last_error(); |
| 523 |
if ($e) { |
| 524 |
$this->error(3, 'XML-entry ' . $name . ' parser error ' . $e->message . ' line ' . $e->line); |
| 525 |
} |
| 526 |
} else { |
| 527 |
$this->error(4, 'XML-entry not found ' . $name); |
| 528 |
} |
| 529 |
|
| 530 |
return false; |
| 531 |
} |
| 532 |
|
| 533 |
// sheets numeration: 1,2,3.... |
| 534 |
|
| 535 |
public function getEntryData($name) |
| 536 |
{ |
| 537 |
$name = ltrim(str_replace('\\', '/', $name), '/'); |
| 538 |
$dir = $this->_strtoupper(dirname($name)); |
| 539 |
$name = $this->_strtoupper(basename($name)); |
| 540 |
foreach ($this->package['entries'] as &$entry) { |
| 541 |
if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { |
| 542 |
if ($entry['error']) { |
| 543 |
return false; |
| 544 |
} |
| 545 |
switch ($entry['cm']) { |
| 546 |
case -1: |
| 547 |
case 0: // Stored |
| 548 |
// Here is nothing to do, the file ist flat. |
| 549 |
break; |
| 550 |
case 8: // Deflated |
| 551 |
$entry['data'] = gzinflate($entry['data']); |
| 552 |
break; |
| 553 |
case 12: // BZIP2 |
| 554 |
if (extension_loaded('bz2')) { |
| 555 |
$entry['data'] = bzdecompress($entry['data']); |
| 556 |
} else { |
| 557 |
$entry['error'] = 7; |
| 558 |
$entry['error_message'] = 'PHP BZIP2 extension not available.'; |
| 559 |
} |
| 560 |
break; |
| 561 |
default: |
| 562 |
$entry['error'] = 6; |
| 563 |
$entry['error_msg'] = 'De-/Compression method '.$entry['cm'].' is not supported.'; |
| 564 |
} |
| 565 |
if (!$entry['error'] && $entry['cm'] > -1) { |
| 566 |
$entry['cm'] = -1; |
| 567 |
if ($entry['data'] === false) { |
| 568 |
$entry['error'] = 2; |
| 569 |
$entry['error_msg'] = 'Decompression of data failed.'; |
| 570 |
} elseif ($this->_strlen($entry['data']) !== (int)$entry['ucs']) { |
| 571 |
$entry['error'] = 3; |
| 572 |
$entry['error_msg'] = 'Uncompressed size is not equal with the value in header information.'; |
| 573 |
} elseif (crc32($entry['data']) !== $entry['crc']) { |
| 574 |
$entry['error'] = 4; |
| 575 |
$entry['error_msg'] = 'CRC32 checksum is not equal with the value in header information.'; |
| 576 |
} |
| 577 |
} |
| 578 |
|
| 579 |
return $entry['data']; |
| 580 |
} |
| 581 |
} |
| 582 |
unset($entry); |
| 583 |
$this->error(5, 'Entry not found ' . ($dir ? $dir . '/' : '') . $name); |
| 584 |
|
| 585 |
return false; |
| 586 |
} |
| 587 |
public function deleteEntry($name) |
| 588 |
{ |
| 589 |
$name = ltrim(str_replace('\\', '/', $name), '/'); |
| 590 |
$dir = $this->_strtoupper(dirname($name)); |
| 591 |
$name = $this->_strtoupper(basename($name)); |
| 592 |
foreach ($this->package['entries'] as $k => $entry) { |
| 593 |
if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { |
| 594 |
unset($this->package['entries'][$k]); |
| 595 |
return true; |
| 596 |
} |
| 597 |
} |
| 598 |
return false; |
| 599 |
} |
| 600 |
|
| 601 |
protected function _strtoupper($str) |
| 602 |
{ |
| 603 |
return (ini_get('mbstring.func_overload') & 2) ? mb_strtoupper($str, '8bit') : strtoupper($str); |
| 604 |
} |
| 605 |
|
| 606 |
protected function _getTarget($base, $target) |
| 607 |
{ |
| 608 |
$target = trim($target); |
| 609 |
if (strpos($target, '/') === 0) { |
| 610 |
return $this->_substr($target, 1); |
| 611 |
} |
| 612 |
$target = ($base ? $base . '/' : '') . $target; |
| 613 |
// a/b/../c -> a/c |
| 614 |
$parts = explode('/', $target); |
| 615 |
$abs = []; |
| 616 |
foreach ($parts as $p) { |
| 617 |
if ('.' === $p) { |
| 618 |
continue; |
| 619 |
} |
| 620 |
if ('..' === $p) { |
| 621 |
array_pop($abs); |
| 622 |
} else { |
| 623 |
$abs[] = $p; |
| 624 |
} |
| 625 |
} |
| 626 |
return implode('/', $abs); |
| 627 |
} |
| 628 |
|
| 629 |
/* |
| 630 |
* @param string $name Filename in archive |
| 631 |
* @return SimpleXMLElement|bool |
| 632 |
*/ |
| 633 |
|
| 634 |
public function entryExists($name) |
| 635 |
{ |
| 636 |
// 0.6.6 |
| 637 |
$dir = $this->_strtoupper(dirname($name)); |
| 638 |
$name = $this->_strtoupper(basename($name)); |
| 639 |
foreach ($this->package['entries'] as $entry) { |
| 640 |
if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { |
| 641 |
return true; |
| 642 |
} |
| 643 |
} |
| 644 |
|
| 645 |
return false; |
| 646 |
} |
| 647 |
|
| 648 |
protected function _parseRichText($is = null) |
| 649 |
{ |
| 650 |
$value = []; |
| 651 |
|
| 652 |
if (isset($is->t)) { |
| 653 |
$value[] = (string)$is->t; |
| 654 |
} elseif (isset($is->r)) { |
| 655 |
foreach ($is->r as $run) { |
| 656 |
$value[] = (string)$run->t; |
| 657 |
} |
| 658 |
} |
| 659 |
|
| 660 |
return implode('', $value); |
| 661 |
} |
| 662 |
|
| 663 |
public static function parseFile($filename, $debug = false) |
| 664 |
{ |
| 665 |
return self::parse($filename, false, $debug); |
| 666 |
} |
| 667 |
|
| 668 |
public static function parse($filename, $is_data = false, $debug = false) |
| 669 |
{ |
| 670 |
$xlsx = new self(); |
| 671 |
$xlsx->debug = $debug; |
| 672 |
if ($xlsx->_unzip($filename, $is_data)) { |
| 673 |
$xlsx->_parse(); |
| 674 |
} |
| 675 |
if ($xlsx->success()) { |
| 676 |
return $xlsx; |
| 677 |
} |
| 678 |
self::parseError($xlsx->error()); |
| 679 |
self::parseErrno($xlsx->errno()); |
| 680 |
|
| 681 |
return false; |
| 682 |
} |
| 683 |
|
| 684 |
public function success() |
| 685 |
{ |
| 686 |
return !$this->error; |
| 687 |
} |
| 688 |
|
| 689 |
// https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex |
| 690 |
|
| 691 |
public static function parseError($set = false) |
| 692 |
{ |
| 693 |
static $error = false; |
| 694 |
|
| 695 |
return $set ? $error = $set : $error; |
| 696 |
} |
| 697 |
|
| 698 |
public static function parseErrno($set = false) |
| 699 |
{ |
| 700 |
static $errno = false; |
| 701 |
|
| 702 |
return $set ? $errno = $set : $errno; |
| 703 |
} |
| 704 |
|
| 705 |
public function errno() |
| 706 |
{ |
| 707 |
return $this->errno; |
| 708 |
} |
| 709 |
|
| 710 |
public static function parseData($data, $debug = false) |
| 711 |
{ |
| 712 |
return self::parse($data, true, $debug); |
| 713 |
} |
| 714 |
|
| 715 |
|
| 716 |
|
| 717 |
public function worksheet($worksheetIndex = 0) |
| 718 |
{ |
| 719 |
|
| 720 |
|
| 721 |
if (isset($this->sheets[$worksheetIndex])) { |
| 722 |
$ws = $this->sheets[$worksheetIndex]; |
| 723 |
|
| 724 |
if (!isset($this->hyperlinks[$worksheetIndex]) && isset($ws->hyperlinks)) { |
| 725 |
$this->hyperlinks[$worksheetIndex] = []; |
| 726 |
$sheet_rels = str_replace('worksheets', 'worksheets/_rels', $this->sheetFiles[$worksheetIndex]) . '.rels'; |
| 727 |
$link_ids = []; |
| 728 |
|
| 729 |
if ($rels = $this->getEntryXML($sheet_rels)) { |
| 730 |
// hyperlink |
| 731 |
// $rel_base = dirname( $sheet_rels ); |
| 732 |
foreach ($rels->Relationship as $rel) { |
| 733 |
$rel_type = basename(trim((string)$rel['Type'])); |
| 734 |
if ($rel_type === 'hyperlink') { |
| 735 |
$rel_id = (string)$rel['Id']; |
| 736 |
$rel_target = (string)$rel['Target']; |
| 737 |
$link_ids[$rel_id] = $rel_target; |
| 738 |
} |
| 739 |
} |
| 740 |
} |
| 741 |
foreach ($ws->hyperlinks->hyperlink as $hyperlink) { |
| 742 |
$ref = (string)$hyperlink['ref']; |
| 743 |
if ($this->_strpos($ref, ':') > 0) { // A1:A8 -> A1 |
| 744 |
$ref = explode(':', $ref); |
| 745 |
$ref = $ref[0]; |
| 746 |
} |
| 747 |
// $this->hyperlinks[ $worksheetIndex ][ $ref ] = (string) $hyperlink['display']; |
| 748 |
$loc = (string)$hyperlink['location']; |
| 749 |
$id = (string)$hyperlink['id']; |
| 750 |
if ($id) { |
| 751 |
$href = $link_ids[$id] . ($loc ? '#' . $loc : ''); |
| 752 |
} else { |
| 753 |
$href = $loc; |
| 754 |
} |
| 755 |
$this->hyperlinks[$worksheetIndex][$ref] = $href; |
| 756 |
} |
| 757 |
} |
| 758 |
|
| 759 |
return $ws; |
| 760 |
} |
| 761 |
$this->error(6, 'Worksheet not found ' . $worksheetIndex); |
| 762 |
|
| 763 |
return false; |
| 764 |
} |
| 765 |
|
| 766 |
protected function _strpos($haystack, $needle, $offset = 0) |
| 767 |
{ |
| 768 |
return (ini_get('mbstring.func_overload') & 2) ? mb_strpos($haystack, $needle, $offset, '8bit') : strpos($haystack, $needle, $offset); |
| 769 |
} |
| 770 |
|
| 771 |
/** |
| 772 |
* returns [numCols,numRows] of worksheet |
| 773 |
* |
| 774 |
* @param int $worksheetIndex |
| 775 |
* |
| 776 |
* @return array |
| 777 |
*/ |
| 778 |
public function dimension($worksheetIndex = 0) |
| 779 |
{ |
| 780 |
|
| 781 |
if (($ws = $this->worksheet($worksheetIndex)) === false) { |
| 782 |
return [0, 0]; |
| 783 |
} |
| 784 |
/* @var SimpleXMLElement $ws */ |
| 785 |
|
| 786 |
$ref = (string)$ws->dimension['ref']; |
| 787 |
|
| 788 |
if ($this->_strpos($ref, ':') !== false) { |
| 789 |
$d = explode(':', $ref); |
| 790 |
$idx = $this->getIndex($d[1]); |
| 791 |
|
| 792 |
return [$idx[0] + 1, $idx[1] + 1]; |
| 793 |
} |
| 794 |
/* |
| 795 |
if ( $ref !== '' ) { // 0.6.8 |
| 796 |
$index = $this->getIndex( $ref ); |
| 797 |
|
| 798 |
return [ $index[0] + 1, $index[1] + 1 ]; |
| 799 |
} |
| 800 |
*/ |
| 801 |
|
| 802 |
// slow method |
| 803 |
$maxC = $maxR = 0; |
| 804 |
$iR = -1; |
| 805 |
foreach ($ws->sheetData->row as $row) { |
| 806 |
$iR++; |
| 807 |
$iC = -1; |
| 808 |
foreach ($row->c as $c) { |
| 809 |
$iC++; |
| 810 |
$idx = $this->getIndex((string)$c['r']); |
| 811 |
$x = $idx[0]; |
| 812 |
$y = $idx[1]; |
| 813 |
if ($x > -1) { |
| 814 |
if ($x > $maxC) { |
| 815 |
$maxC = $x; |
| 816 |
} |
| 817 |
if ($y > $maxR) { |
| 818 |
$maxR = $y; |
| 819 |
} |
| 820 |
} else { |
| 821 |
if ($iC > $maxC) { |
| 822 |
$maxC = $iC; |
| 823 |
} |
| 824 |
if ($iR > $maxR) { |
| 825 |
$maxR = $iR; |
| 826 |
} |
| 827 |
} |
| 828 |
} |
| 829 |
} |
| 830 |
|
| 831 |
return [$maxC + 1, $maxR + 1]; |
| 832 |
} |
| 833 |
|
| 834 |
public function getIndex($cell = 'A1') |
| 835 |
{ |
| 836 |
|
| 837 |
if (preg_match('/([A-Z]+)(\d+)/', $cell, $m)) { |
| 838 |
$col = $m[1]; |
| 839 |
$row = $m[2]; |
| 840 |
|
| 841 |
$colLen = $this->_strlen($col); |
| 842 |
$index = 0; |
| 843 |
|
| 844 |
for ($i = $colLen - 1; $i >= 0; $i--) { |
| 845 |
$index += (ord($col[$i]) - 64) * pow(26, $colLen - $i - 1); |
| 846 |
} |
| 847 |
|
| 848 |
return [$index - 1, $row - 1]; |
| 849 |
} |
| 850 |
|
| 851 |
// $this->error( 'Invalid cell index ' . $cell ); |
| 852 |
|
| 853 |
return [-1, -1]; |
| 854 |
} |
| 855 |
|
| 856 |
public function value($cell) |
| 857 |
{ |
| 858 |
// Determine data type |
| 859 |
$dataType = (string)$cell['t']; |
| 860 |
|
| 861 |
if ($dataType === '' || $dataType === 'n') { // number |
| 862 |
$s = (int)$cell['s']; |
| 863 |
if ($s > 0 && isset($this->cellFormats[$s])) { |
| 864 |
if (array_key_exists('format', $this->cellFormats[$s])) { |
| 865 |
$format = $this->cellFormats[$s]['format']; |
| 866 |
if (preg_match('/(m|AM|PM)/', preg_replace('/\"[^"]+\"/', '', $format))) { // [mm]onth,AM|PM |
| 867 |
$dataType = 'D'; |
| 868 |
} |
| 869 |
} else { |
| 870 |
$dataType = 'n'; |
| 871 |
} |
| 872 |
} |
| 873 |
} |
| 874 |
|
| 875 |
$value = ''; |
| 876 |
|
| 877 |
switch ($dataType) { |
| 878 |
case 's': |
| 879 |
// Value is a shared string |
| 880 |
if ((string)$cell->v !== '') { |
| 881 |
$value = $this->sharedstrings[(int)$cell->v]; |
| 882 |
} |
| 883 |
break; |
| 884 |
|
| 885 |
case 'str': // formula? |
| 886 |
if ((string)$cell->v !== '') { |
| 887 |
$value = (string)$cell->v; |
| 888 |
} |
| 889 |
break; |
| 890 |
|
| 891 |
case 'b': |
| 892 |
// Value is boolean |
| 893 |
$value = (string)$cell->v; |
| 894 |
if ($value === '0') { |
| 895 |
$value = false; |
| 896 |
} elseif ($value === '1') { |
| 897 |
$value = true; |
| 898 |
} else { |
| 899 |
$value = (bool)$cell->v; |
| 900 |
} |
| 901 |
|
| 902 |
break; |
| 903 |
|
| 904 |
case 'inlineStr': |
| 905 |
// Value is rich text inline |
| 906 |
$value = $this->_parseRichText($cell->is); |
| 907 |
|
| 908 |
break; |
| 909 |
|
| 910 |
case 'e': |
| 911 |
// Value is an error message |
| 912 |
if ((string)$cell->v !== '') { |
| 913 |
$value = (string)$cell->v; |
| 914 |
} |
| 915 |
break; |
| 916 |
|
| 917 |
case 'D': |
| 918 |
// Date as float |
| 919 |
if (!empty($cell->v)) { |
| 920 |
$value = $this->datetimeFormat ? gmdate($this->datetimeFormat, $this->unixstamp((float)$cell->v)) : (float)$cell->v; |
| 921 |
} |
| 922 |
break; |
| 923 |
|
| 924 |
case 'd': |
| 925 |
// Date as ISO YYYY-MM-DD |
| 926 |
if ((string)$cell->v !== '') { |
| 927 |
$value = (string)$cell->v; |
| 928 |
} |
| 929 |
break; |
| 930 |
|
| 931 |
default: |
| 932 |
// Value is a string |
| 933 |
$value = (string)$cell->v; |
| 934 |
|
| 935 |
// Check for numeric values |
| 936 |
if (is_numeric($value)) { |
| 937 |
/** @noinspection TypeUnsafeComparisonInspection */ |
| 938 |
if ($value == (int)$value) { |
| 939 |
$value = (int)$value; |
| 940 |
} /** @noinspection TypeUnsafeComparisonInspection */ elseif ($value == (float)$value) { |
| 941 |
$value = (float)$value; |
| 942 |
} |
| 943 |
} |
| 944 |
} |
| 945 |
|
| 946 |
return $value; |
| 947 |
} |
| 948 |
|
| 949 |
public function unixstamp($excelDateTime) |
| 950 |
{ |
| 951 |
|
| 952 |
$d = floor($excelDateTime); // days since 1900 or 1904 |
| 953 |
$t = $excelDateTime - $d; |
| 954 |
|
| 955 |
if ($this->date1904) { |
| 956 |
$d += 1462; |
| 957 |
} |
| 958 |
|
| 959 |
$t = (abs($d) > 0) ? ($d - 25569) * 86400 + round($t * 86400) : round($t * 86400); |
| 960 |
|
| 961 |
return (int)$t; |
| 962 |
} |
| 963 |
|
| 964 |
public function href($worksheetIndex, $cell) |
| 965 |
{ |
| 966 |
$ref = (string)$cell['r']; |
| 967 |
return isset($this->hyperlinks[$worksheetIndex][$ref]) ? $this->hyperlinks[$worksheetIndex][$ref] : ''; |
| 968 |
} |
| 969 |
|
| 970 |
public function toHTML($worksheetIndex = 0) |
| 971 |
{ |
| 972 |
$s = '<table class=excel>'; |
| 973 |
foreach ($this->readRows($worksheetIndex) as $r) { |
| 974 |
$s .= '<tr>'; |
| 975 |
foreach ($r as $c) { |
| 976 |
$s .= '<td nowrap>' . ($c === '' ? ' ' : htmlspecialchars($c, ENT_QUOTES)) . '</td>'; |
| 977 |
} |
| 978 |
$s .= "</tr>\r\n"; |
| 979 |
} |
| 980 |
$s .= '</table>'; |
| 981 |
|
| 982 |
return $s; |
| 983 |
} |
| 984 |
public function toHTMLEx($worksheetIndex = 0) |
| 985 |
{ |
| 986 |
$s = '<table class=excel>'; |
| 987 |
$y = 0; |
| 988 |
foreach ($this->readRowsEx($worksheetIndex) as $r) { |
| 989 |
$s .= '<tr>'; |
| 990 |
$x = 0; |
| 991 |
foreach ($r as $c) { |
| 992 |
$tag = 'td'; |
| 993 |
$css = $c['css']; |
| 994 |
if ($y === 0) { |
| 995 |
$tag = 'th'; |
| 996 |
$css .= $c['width'] ? 'width: '.round($c['width'] * 0.47, 2).'em;' : ''; |
| 997 |
} |
| 998 |
|
| 999 |
if ($x === 0 && $c['height']) { |
| 1000 |
$css .= 'height: '.round($c['height'] * 1.3333).'px;'; |
| 1001 |
} |
| 1002 |
$s .= '<'.$tag.' style="'.$css.'" nowrap>' . ($c['value'] === '' ? ' ' : htmlspecialchars($c['value'], ENT_QUOTES)) . '</'.$tag.'>'; |
| 1003 |
$x++; |
| 1004 |
} |
| 1005 |
$s .= "</tr>\r\n"; |
| 1006 |
$y++; |
| 1007 |
} |
| 1008 |
$s .= '</table>'; |
| 1009 |
|
| 1010 |
return $s; |
| 1011 |
} |
| 1012 |
public function rows($worksheetIndex = 0, $limit = 0) |
| 1013 |
{ |
| 1014 |
return iterator_to_array($this->readRows($worksheetIndex, $limit), false); |
| 1015 |
} |
| 1016 |
// thx Gonzo |
| 1017 |
/** |
| 1018 |
* @param $worksheetIndex |
| 1019 |
* @param $limit |
| 1020 |
* @return \Generator |
| 1021 |
*/ |
| 1022 |
public function readRows($worksheetIndex = 0, $limit = 0) |
| 1023 |
{ |
| 1024 |
|
| 1025 |
if (($ws = $this->worksheet($worksheetIndex)) === false) { |
| 1026 |
return; |
| 1027 |
} |
| 1028 |
$dim = $this->dimension($worksheetIndex); |
| 1029 |
$numCols = $dim[0]; |
| 1030 |
$numRows = $dim[1]; |
| 1031 |
|
| 1032 |
$emptyRow = []; |
| 1033 |
for ($i = 0; $i < $numCols; $i++) { |
| 1034 |
$emptyRow[] = ''; |
| 1035 |
} |
| 1036 |
|
| 1037 |
$curR = 0; |
| 1038 |
$_limit = $limit; |
| 1039 |
/* @var SimpleXMLElement $ws */ |
| 1040 |
foreach ($ws->sheetData->row as $row) { |
| 1041 |
$r = $emptyRow; |
| 1042 |
$curC = 0; |
| 1043 |
foreach ($row->c as $c) { |
| 1044 |
// detect skipped cols |
| 1045 |
$idx = $this->getIndex((string)$c['r']); |
| 1046 |
$x = $idx[0]; |
| 1047 |
$y = $idx[1]; |
| 1048 |
|
| 1049 |
if ($x > -1) { |
| 1050 |
$curC = $x; |
| 1051 |
while ($curR < $y) { |
| 1052 |
yield $emptyRow; |
| 1053 |
$curR++; |
| 1054 |
$_limit--; |
| 1055 |
if ($_limit === 0) { |
| 1056 |
return; |
| 1057 |
} |
| 1058 |
} |
| 1059 |
} |
| 1060 |
$r[$curC] = $this->value($c); |
| 1061 |
$curC++; |
| 1062 |
} |
| 1063 |
yield $r; |
| 1064 |
|
| 1065 |
$curR++; |
| 1066 |
$_limit--; |
| 1067 |
if ($_limit === 0) { |
| 1068 |
return; |
| 1069 |
} |
| 1070 |
} |
| 1071 |
while ($curR < $numRows) { |
| 1072 |
yield $emptyRow; |
| 1073 |
$curR++; |
| 1074 |
$_limit--; |
| 1075 |
if ($_limit === 0) { |
| 1076 |
return; |
| 1077 |
} |
| 1078 |
} |
| 1079 |
} |
| 1080 |
|
| 1081 |
public function rowsEx($worksheetIndex = 0, $limit = 0) |
| 1082 |
{ |
| 1083 |
return iterator_to_array($this->readRowsEx($worksheetIndex, $limit), false); |
| 1084 |
} |
| 1085 |
// https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex |
| 1086 |
/** |
| 1087 |
* @param $worksheetIndex |
| 1088 |
* @param $limit |
| 1089 |
* @return \Generator|null |
| 1090 |
*/ |
| 1091 |
public function readRowsEx($worksheetIndex = 0, $limit = 0) |
| 1092 |
{ |
| 1093 |
if (!$this->rowsExReader) { |
| 1094 |
require_once __DIR__ . '/SimpleXLSXEx.php'; |
| 1095 |
$this->rowsExReader = new SimpleXLSXEx($this); |
| 1096 |
} |
| 1097 |
return $this->rowsExReader->readRowsEx($worksheetIndex, $limit); |
| 1098 |
} |
| 1099 |
|
| 1100 |
/** |
| 1101 |
* Returns cell value |
| 1102 |
* VERY SLOW! Use ->rows() or ->rowsEx() |
| 1103 |
* |
| 1104 |
* @param int $worksheetIndex |
| 1105 |
* @param string|array $cell ref or coords, D12 or [3,12] |
| 1106 |
* |
| 1107 |
* @return mixed Returns NULL if not found |
| 1108 |
*/ |
| 1109 |
public function getCell($worksheetIndex = 0, $cell = 'A1') |
| 1110 |
{ |
| 1111 |
|
| 1112 |
if (($ws = $this->worksheet($worksheetIndex)) === false) { |
| 1113 |
return false; |
| 1114 |
} |
| 1115 |
if (is_array($cell)) { |
| 1116 |
$cell = $this->_num2name($cell[0]) . $cell[1];// [3,21] -> D21 |
| 1117 |
} |
| 1118 |
if (is_string($cell)) { |
| 1119 |
$result = $ws->sheetData->xpath("row/c[@r='" . $cell . "']"); |
| 1120 |
if (count($result)) { |
| 1121 |
return $this->value($result[0]); |
| 1122 |
} |
| 1123 |
} |
| 1124 |
|
| 1125 |
return null; |
| 1126 |
} |
| 1127 |
|
| 1128 |
protected function _num2name($num) |
| 1129 |
{ |
| 1130 |
$numeric = ($num - 1) % 26; |
| 1131 |
$letter = chr(65 + $numeric); |
| 1132 |
$num2 = (int)(($num - 1) / 26); |
| 1133 |
if ($num2 > 0) { |
| 1134 |
return $this->_num2name($num2) . $letter; |
| 1135 |
} |
| 1136 |
return $letter; |
| 1137 |
} |
| 1138 |
|
| 1139 |
public function getSheets() |
| 1140 |
{ |
| 1141 |
return $this->sheets; |
| 1142 |
} |
| 1143 |
|
| 1144 |
public function sheetsCount() |
| 1145 |
{ |
| 1146 |
return count($this->sheets); |
| 1147 |
} |
| 1148 |
|
| 1149 |
public function sheetName($worksheetIndex) |
| 1150 |
{ |
| 1151 |
if (isset($this->sheetNames[$worksheetIndex])) { |
| 1152 |
return $this->sheetNames[$worksheetIndex]; |
| 1153 |
} |
| 1154 |
|
| 1155 |
return false; |
| 1156 |
} |
| 1157 |
|
| 1158 |
public function sheetNames() |
| 1159 |
{ |
| 1160 |
|
| 1161 |
return $this->sheetNames; |
| 1162 |
} |
| 1163 |
|
| 1164 |
public function getStyles() |
| 1165 |
{ |
| 1166 |
return $this->styles; |
| 1167 |
} |
| 1168 |
|
| 1169 |
public function getPackage() |
| 1170 |
{ |
| 1171 |
return $this->package; |
| 1172 |
} |
| 1173 |
|
| 1174 |
public function setDateTimeFormat($value) |
| 1175 |
{ |
| 1176 |
$this->datetimeFormat = is_string($value) ? $value : false; |
| 1177 |
} |
| 1178 |
} |
| 1179 |
|
