Newer
Older
DirtyScripts / RS / ExcelCal / SimpleXLSXEx.php
root on 21 Apr 2022 20 KB xls2ical
  1. <?php
  2.  
  3. /** @noinspection MultiAssignmentUsageInspection */
  4.  
  5. namespace Shuchkin;
  6.  
  7. use SimpleXMLElement;
  8.  
  9. class SimpleXLSXEx
  10. {
  11. public static $IC = [
  12. 0 => '000000',
  13. 1 => 'FFFFFF',
  14. 2 => 'FF0000',
  15. 3 => '00FF00',
  16. 4 => '0000FF',
  17. 5 => 'FFFF00',
  18. 6 => 'FF00FF',
  19. 7 => '00FFFF',
  20. 8 => '000000',
  21. 9 => 'FFFFFF',
  22. 10 => 'FF0000',
  23. 11 => '00FF00',
  24. 12 => '0000FF',
  25. 13 => 'FFFF00',
  26. 14 => 'FF00FF',
  27. 15 => '00FFFF',
  28. 16 => '800000',
  29. 17 => '008000',
  30. 18 => '000080',
  31. 19 => '808000',
  32. 20 => '800080',
  33. 21 => '008080',
  34. 22 => 'C0C0C0',
  35. 23 => '808080',
  36. 24 => '9999FF',
  37. 25 => '993366',
  38. 26 => 'FFFFCC',
  39. 27 => 'CCFFFF',
  40. 28 => '660066',
  41. 29 => 'FF8080',
  42. 30 => '0066CC',
  43. 31 => 'CCCCFF',
  44. 32 => '000080',
  45. 33 => 'FF00FF',
  46. 34 => 'FFFF00',
  47. 35 => '00FFFF',
  48. 36 => '800080',
  49. 37 => '800000',
  50. 38 => '008080',
  51. 39 => '0000FF',
  52. 40 => '00CCFF',
  53. 41 => 'CCFFFF',
  54. 42 => 'CCFFCC',
  55. 43 => 'FFFF99',
  56. 44 => '99CCFF',
  57. 45 => 'FF99CC',
  58. 46 => 'CC99FF',
  59. 47 => 'FFCC99',
  60. 48 => '3366FF',
  61. 49 => '33CCCC',
  62. 50 => '99CC00',
  63. 51 => 'FFCC00',
  64. 52 => 'FF9900',
  65. 53 => 'FF6600',
  66. 54 => '666699',
  67. 55 => '969696',
  68. 56 => '003366',
  69. 57 => '339966',
  70. 58 => '003300',
  71. 59 => '333300',
  72. 60 => '993300',
  73. 61 => '993366',
  74. 62 => '333399',
  75. 63 => '333333',
  76. 64 => '000000', // System Foreground
  77. 65 => 'FFFFFF', // System Background'
  78. ];
  79. public static $CH = [
  80. 0 => 'ANSI_CHARSET',
  81. 1 => 'DEFAULT_CHARSET',
  82. 2 => 'SYMBOL_CHARSET',
  83. 77 => 'MAC_CHARSET',
  84. 128 => 'SHIFTJIS_CHARSET',
  85. //129 => 'HANGEUL_CHARSET',
  86. 129 => 'HANGUL_CHARSET',
  87. 130 => 'JOHAB_CHARSET',
  88. 134 => 'GB2312_CHARSET',
  89. 136 => 'CHINESEBIG5_CHARSET',
  90. 161 => 'GREEK_CHARSET',
  91. 162 => 'TURKISH_CHARSET',
  92. 163 => 'VIETNAMESE_CHARSET',
  93. 177 => 'HEBREW_CHARSET',
  94. 178 => 'ARABIC_CHARSET',
  95. 186 => 'BALTIC_CHARSET',
  96. 204 => 'RUSSIAN_CHARSET',
  97. 222 => 'THAI_CHARSET',
  98. 238 => 'EASTEUROPE_CHARSET',
  99. 255 => 'OEM_CHARSET'
  100. ];
  101. public $xlsx;
  102. public $themeColors;
  103. public $fonts;
  104. public $fills;
  105. public $borders;
  106. public $cellStyles;
  107. public $css;
  108.  
  109. public function __construct(SimpleXLSX $xlsx)
  110. {
  111. $this->xlsx = $xlsx;
  112. $this->readThemeColors();
  113. $this->readFonts();
  114. $this->readFills();
  115. $this->readBorders();
  116. $this->readXfs();
  117. }
  118. public function readThemeColors()
  119. {
  120. $this->themeColors = [];
  121. if (isset($this->xlsx->theme->themeElements->clrScheme)) {
  122. $colors12 = ['lt1', 'dk1', 'lt2', 'dk2','accent1','accent2','accent3','accent4','accent5',
  123. 'accent6','hlink','folHlink'];
  124. foreach ($colors12 as $c) {
  125. $v = $this->xlsx->theme->themeElements->clrScheme->{$c};
  126. if (isset($v->sysClr)) {
  127. $this->themeColors[] = (string) $v->sysClr['lastClr'];
  128. } elseif (isset($v->srgbClr)) {
  129. $this->themeColors[] = (string) $v->srgbClr['val'];
  130. } else {
  131. $this->themeColors[] = null;
  132. }
  133. }
  134. }
  135. }
  136.  
  137.  
  138. public function readFonts()
  139. {
  140. // fonts
  141. $this->fonts = [];
  142. if (isset($this->xlsx->styles->fonts->font)) {
  143. foreach ($this->xlsx->styles->fonts->font as $v) {
  144. $u = '';
  145. if (isset($v->u)) {
  146. $u = isset($v->u['val']) ? (string) $v->u['val'] : 'single';
  147. }
  148. $this->fonts[] = [
  149. 'b' => isset($v->b) && ($v->b['val'] === null || $v->b['val']),
  150. 'i' => isset($v->i) && ($v->i['val'] === null || $v->i['val']),
  151. 'u' => $u,
  152. 'strike' => isset($v->strike) && ($v->strike['val'] === null || $v->strike['val']),
  153. 'sz' => isset($v->sz['val']) ? (int) $v->sz['val'] : 11,
  154. 'color' => $this->getColorValue($v->color),
  155. 'name' => isset($v->name['val']) ? (string) $v->name['val'] : 'Calibri',
  156. 'family' => isset($v->family['val']) ? (int) $v->family['val'] : 2,
  157. 'charset' => isset($v->charset['val']) ? (int) $v->charset['val'] : 1,
  158. 'scheme' => isset($v->scheme['val']) ? (string) $v->scheme['val'] : 'minor'
  159. ];
  160. }
  161. }
  162. }
  163. public function readFills()
  164. {
  165. // fills
  166. $this->fills = [];
  167. if (isset($this->xlsx->styles->fills->fill)) {
  168. foreach ($this->xlsx->styles->fills->fill as $v) {
  169. if (isset($v->patternFill)) {
  170. $this->fills[] = [
  171. 'pattern' => isset($v->patternFill['patternType']) ? (string) $v->patternFill['patternType'] : 'none',
  172. 'fgcolor' => $this->getColorValue($v->patternFill->fgColor),
  173. 'bgcolor' => $this->getColorValue($v->patternFill->bgColor)
  174. ];
  175. }
  176. }
  177. }
  178. }
  179. public function readBorders()
  180. {
  181. $this->borders = [];
  182. if (isset($this->xlsx->styles->borders->border)) {
  183. foreach ($this->xlsx->styles->borders->border as $v) {
  184. $this->borders[] = [
  185. 'left' => [
  186. 'style' => (string) $v->left['style'],
  187. 'color' => $this->getColorValue($v->left->color)
  188. ],
  189. 'right' => [
  190. 'style' => (string) $v->right['style'],
  191. 'color' => $this->getColorValue($v->right->color)
  192. ],
  193. 'top' => [
  194. 'style' => (string) $v->top['style'],
  195. 'color' => $this->getColorValue($v->top->color)
  196. ],
  197. 'bottom' => [
  198. 'style' => (string) $v->bottom['style'],
  199. 'color' => $this->getColorValue($v->bottom->color)
  200. ],
  201. 'diagonal' => [
  202. 'style' => (string) $v->diagonal['style'],
  203. 'color' => $this->getColorValue($v->diagonal->color)
  204. ],
  205. 'horizontal' => [
  206. 'style' => (string) $v->horizontal['style'],
  207. 'color' => $this->getColorValue($v->horizontal->color)
  208. ],
  209. 'vertical' => [
  210. 'style' => (string) $v->vertical['style'],
  211. 'color' => $this->getColorValue($v->vertical->color)
  212. ],
  213. 'diagonalUp' => (bool) $v['diagonalUp'],
  214. 'diagonalDown' => (bool) $v['diagonalDown'],
  215. 'outline' => !(isset($v['outline'])) || $v['outline']
  216. ];
  217. }
  218. }
  219. }
  220.  
  221. public function readXfs()
  222. {
  223. // cellStyles
  224. $this->cellStyles = [];
  225. if (isset($this->xlsx->styles->cellStyleXfs->xf)) {
  226. foreach ($this->xlsx->styles->cellStyleXfs->xf as $v) {
  227. $x = [];
  228. foreach ($v->attributes() as $k1 => $v1) {
  229. $x[ $k1 ] = (int) $v1;
  230. }
  231. if (isset($v->alignment)) {
  232. foreach ($v->alignment->attributes() as $k1 => $v1) {
  233. $x['alignment'][$k1] = (string) $v1;
  234. }
  235. }
  236. $this->cellStyles[] = $x;
  237. }
  238. }
  239. // css
  240. $this->css = [];
  241. // xf
  242. if (isset($this->xlsx->styles->cellXfs->xf)) {
  243. $k = 0;
  244. foreach ($this->xlsx->styles->cellXfs->xf as $v) {
  245. $cf = &$this->xlsx->cellFormats[$k];
  246.  
  247. // alignment
  248. $alignment = [];
  249. if (isset($v->alignment)) {
  250. foreach ($v->alignment->attributes() as $k1 => $v1) {
  251. $alignment[$k1] = (string)$v1;
  252. }
  253. }
  254.  
  255. if (isset($cf['xfId'], $this->cellStyles[ $cf['xfId'] ])) {
  256. $s = $this->cellStyles[$cf['xfId']];
  257. if (!empty($s['applyNumberFormat'])) {
  258. $cf['numFmtId'] = $s['numFmtId'];
  259. }
  260. if (!empty($s['applyFont'])) {
  261. $cf['fontId'] = $s['fontId'];
  262. }
  263. if (!empty($s['applyBorder'])) {
  264. $cf['borderId'] = $s['borderId'];
  265. }
  266. if (!empty($s['applyAlignment'])) {
  267. $alignment = $s['alignment'];
  268. }
  269. }
  270. $cf['alignment'] = $alignment;
  271.  
  272. $align = null;
  273. if (isset($alignment['horizontal'])) {
  274. $align = $alignment['horizontal'];
  275. if ($align === 'centerContinuous') {
  276. $align = 'center';
  277. }
  278. if ($align === 'distributed') {
  279. $align = 'justify';
  280. }
  281. if ($align === 'general') {
  282. $align = null;
  283. }
  284. }
  285. $cf['align'] = $align;
  286.  
  287. $valign = null;
  288. if (isset($alignment['vertical'])) {
  289. $valign = $alignment['vertical'];
  290. if ($valign === 'center' || $valign === 'distributed' || $valign === 'justify') {
  291. $valign = 'middle';
  292. }
  293. }
  294. $cf['valign'] = $valign;
  295.  
  296. // font
  297. $cf['font'] = $this->fonts[ $cf['fontId'] ]['name'];
  298. $cf['color'] = $this->fonts[ $cf['fontId'] ]['color'];
  299. $cf['f-size'] = $this->fonts[ $cf['fontId'] ]['sz'];
  300. $cf['f-b'] = $this->fonts[ $cf['fontId'] ]['b'];
  301. $cf['f-i'] = $this->fonts[ $cf['fontId'] ]['i'];
  302. $cf['f-u'] = $this->fonts[ $cf['fontId'] ]['u'];
  303. $cf['f-strike'] = $this->fonts[ $cf['fontId'] ]['strike'];
  304.  
  305. // fill
  306. $cf['bgcolor'] = $this->fills[ $cf['fillId'] ]['fgcolor'];
  307.  
  308. // borders
  309. if (isset($this->borders[ $cf['borderId'] ])) {
  310. $border = $this->borders[ $cf['borderId'] ];
  311.  
  312. $borders = ['left', 'right', 'top', 'bottom'];
  313. foreach ($borders as $b) {
  314. $cf['b-' . $b.'-color'] = $border[$b]['color'];
  315. if ($border[$b]['style'] === '' || $border[$b]['style'] === 'none') {
  316. $cf['b-' . $b.'-style'] = '';
  317. $cf['b-' . $b.'-color'] = '';
  318. } elseif ($border[$b]['style'] === 'dashDot'
  319. || $border[$b]['style'] === 'dashDotDot'
  320. || $border[$b]['style'] === 'dashed'
  321. ) {
  322. $cf['b-' . $b.'-style'] = 'dashed';
  323. } else {
  324. $cf['b-' . $b.'-style'] = 'solid';
  325. }
  326. }
  327. }
  328.  
  329. $css = '';
  330.  
  331. if ($cf['color']) {
  332. $css .= 'color: #'.$cf['color'].';';
  333. }
  334. if ($cf['font']) {
  335. $css .= 'font-family: '.$cf['font'].';';
  336. }
  337. if ($cf['f-size']) {
  338. // $css .= 'font-size: '.($cf['f-size'] * 0.352806).'mm;';
  339. $css .= 'font-size: '.(round($cf['f-size'] * 1.3333) + 2).'px;';
  340. }
  341. if ($cf['f-b']) {
  342. $css .= 'font-weight: bold;';
  343. }
  344. if ($cf['f-i']) {
  345. $css .= 'font-style: italic;';
  346. }
  347. if ($cf['f-u']) {
  348. $css .= 'text-decoration: underline;';
  349. }
  350. if ($cf['f-strike']) {
  351. $css .= 'text-decoration: line-through;';
  352. }
  353. if ($cf['bgcolor']) {
  354. $css .= 'background-color: #' . $cf['bgcolor'] . ';';
  355. }
  356. if ($cf['align']) {
  357. $css .= 'text-align: '.$cf['align'].';';
  358. }
  359. if ($cf['valign']) {
  360. $css .= 'vertical-align: '.$cf['valign'].';';
  361. }
  362. if ($cf['b-top-style']) {
  363. $css .= 'border-top-style: '.$cf['b-top-style'].';';
  364. $css .= 'border-top-color: #'.$cf['b-top-color'].';';
  365. $css .= 'border-top-width: thin;';
  366. }
  367. if ($cf['b-right-style']) {
  368. $css .= 'border-right-style: '.$cf['b-right-style'].';';
  369. $css .= 'border-right-color: #'.$cf['b-right-color'].';';
  370. $css .= 'border-right-width: thin;';
  371. }
  372. if ($cf['b-bottom-style']) {
  373. $css .= 'border-bottom-style: '.$cf['b-bottom-style'].';';
  374. $css .= 'border-bottom-color: #'.$cf['b-bottom-color'].';';
  375. $css .= 'border-bottom-width: thin;';
  376. }
  377. if ($cf['b-left-style']) {
  378. $css .= 'border-left-style: '.$cf['b-left-style'].';';
  379. $css .= 'border-left-color: #'.$cf['b-left-color'].';';
  380. $css .= 'border-left-width: thin;';
  381. }
  382. $this->css[$k] = $css;
  383. $k++;
  384. }
  385. }
  386. }
  387.  
  388. public function readRowsEx($worksheetIndex = 0, $limit = 0)
  389. {
  390. if (($ws = $this->xlsx->worksheet($worksheetIndex)) === false) {
  391. return;
  392. }
  393.  
  394. $dim = $this->xlsx->dimension($worksheetIndex);
  395. $numCols = $dim[0];
  396. $numRows = $dim[1];
  397.  
  398. /*$emptyRow = array();
  399. for ($i = 0; $i < $numCols; $i++) {
  400. $emptyRow[] = null;
  401. }
  402. */
  403. $cols = [];
  404. for ($i = 0; $i < $numCols; $i++) {
  405. $cols[] = ['s' => 0, 'hidden' => false, 'width' => 0];
  406. }
  407. // $hiddenCols = [];
  408. /* @var SimpleXMLElement $ws */
  409. if (isset($ws->cols)) {
  410. foreach ($ws->cols->col as $col) {
  411. $min = (int)$col['min'];
  412. $max = (int)$col['max'];
  413. if (($max-$min) > 100) {
  414. $max = $min;
  415. }
  416. for ($i = $min; $i <= $max; $i++) {
  417. $cols[$i-1] = [
  418. 's' => (int)$col['style'],
  419. 'hidden' => (bool)$col['hidden'],
  420. 'width' => $col['customWidth'] ? (float) $col['width'] : 0
  421. ];
  422. }
  423. }
  424. }
  425.  
  426. $curR = 0;
  427. $_limit = $limit;
  428.  
  429. foreach ($ws->sheetData->row as $row) {
  430. $curC = 0;
  431.  
  432. $r_idx = (int)$row['r'];
  433. $r_style = ['s' => 0, 'hidden' => (bool)$row['hidden'], 'height' => 0];
  434. if ($row['customFormat']) {
  435. $r_style['s'] = (int)$row['s'];
  436. }
  437. if ($row['customHeight']) {
  438. $r_style['height'] = (int)$row['ht'];
  439. }
  440.  
  441. $cells = [];
  442. for ($i = 0; $i < $numCols; $i++) {
  443. $cells[] = null;
  444. }
  445.  
  446. foreach ($row->c as $c) {
  447. $r = (string)$c['r'];
  448. $t = (string)$c['t'];
  449. $s = (int)$c['s'];
  450.  
  451. $idx = $this->xlsx->getIndex($r);
  452. $x = $idx[0];
  453. $y = $idx[1];
  454.  
  455. if ($x > -1) {
  456. $curC = $x;
  457. if ($curC >= $numCols) {
  458. $numCols = $curC + 1;
  459. }
  460. while ($curR < $y) {
  461. $emptyRow = [];
  462. for ($i = 0; $i < $numCols; $i++) {
  463. $emptyRow[] = $this->valueEx($cols[$i], $i, $curR);
  464. }
  465. yield $emptyRow;
  466. $curR++;
  467.  
  468. $_limit--;
  469. if ($_limit === 0) {
  470. return;
  471. }
  472. }
  473. }
  474. $data = [
  475. 'type' => $t,
  476. 'name' => $r,
  477. 'value' => $this->xlsx->value($c),
  478. 'href' => $this->xlsx->href($worksheetIndex, $c),
  479. 'f' => (string)$c->f,
  480. 'r' => $r_idx,
  481. 's' => ($s > 0) ? $s : $cols[$curC]['s'],
  482. 'hidden' => $r_style['hidden'] || $cols[$curC]['hidden'],
  483. 'width' => $cols[$curC]['width'],
  484. 'height' => $r_style['height']
  485. ];
  486. $cells[$curC] = $this->valueEx($data, $curC, $curR);
  487.  
  488. $curC++;
  489. }
  490. // check empty cells
  491. for ($i = 0; $i < $numCols; $i++) {
  492. if ($cells[$i] === null) {
  493. if ($r_style['s'] > 0) {
  494. $data = $r_style;
  495. } else {
  496. $data = $cols[$i];
  497. }
  498. $data['width'] = $cols[$i]['width'];
  499. $data['height'] = $r_style['height'];
  500. $cells[$i] = $this->valueEx($data, $i, $curR);
  501. }
  502. }
  503.  
  504. yield $cells;
  505.  
  506. $curR++;
  507. $_limit--;
  508. if ($_limit === 0) {
  509. break;
  510. }
  511. }
  512.  
  513. while ($curR < $numRows) {
  514. $emptyRow = [];
  515. for ($i = 0; $i < $numCols; $i++) {
  516. $data = $cols[$i];
  517. $emptyRow[] = $this->valueEx($data, $i, $curR);
  518. }
  519. yield $emptyRow;
  520. $curR++;
  521. $_limit--;
  522. if ($_limit === 0) {
  523. return;
  524. }
  525. }
  526. }
  527.  
  528. protected function valueEx($data, $x = null, $y = null)
  529. {
  530.  
  531. $r = [
  532. 'type' => '',
  533. 'name' => '',
  534. 'value' => '',
  535. 'href' => '',
  536. 'f' => '',
  537. 'format' => '',
  538. 's' => 0,
  539. 'css' => '',
  540. 'r' => '',
  541. 'hidden' => false,
  542. 'width' => 0,
  543. 'height' => 0
  544. ];
  545. foreach ($data as $k => $v) {
  546. if (isset($r[$k])) {
  547. $r[$k] = $v;
  548. }
  549. }
  550. $st = &$this->xlsx->cellFormats[$r['s']];
  551. $r['format'] = $st['format'];
  552. $r['css'] = &$this->css[ $r['s'] ];
  553. if ($r['value'] !== '' && !$st['align'] && !in_array($r['type'], ['s','str','inlineStr','e'], true)) {
  554. $r['css'] .= 'text-align: right;';
  555. }
  556.  
  557. if (!$r['name']) {
  558. $c = '';
  559. for ($k = $x; $k >= 0; $k = (int)($k / 26) - 1) {
  560. $c = chr($k % 26 + 65) . $c;
  561. }
  562. $r['name'] = $c . ($y + 1);
  563. $r['r'] = $y+1;
  564. }
  565. return $r;
  566. }
  567. public function getColorValue(SimpleXMLElement $a = null, $default = '')
  568. {
  569. if ($a === null) {
  570. return $default;
  571. }
  572. $c = $default; // auto
  573. if ($a['rgb'] !== null) {
  574. $c = substr((string) $a['rgb'], 2); // FFCCBBAA -> CCBBAA
  575. } elseif ($a['indexed'] !== null && isset(static::$IC[ (int) $a['indexed'] ])) {
  576. $c = static::$IC[ (int) $a['indexed'] ];
  577. } elseif ($a['theme'] !== null && isset($this->themeColors[ (int) $a['theme'] ])) {
  578. $c = $this->themeColors[ (int) $a['theme'] ];
  579. }
  580. if ($a['tint'] !== null) {
  581. list($r,$g,$b) = array_map('hexdec', str_split($c, 2));
  582. $tint = (float) $a['tint'];
  583. if ($tint > 0) {
  584. $r += (255 - $r) * $tint;
  585. $g += (255 - $g) * $tint;
  586. $b += (255 - $b) * $tint;
  587. } else {
  588. $r += $r * $tint;
  589. $g += $g * $tint;
  590. $b += $b * $tint;
  591. }
  592. $c = strtoupper(
  593. str_pad(dechex((int) $r), 2, '0', 0) .
  594. str_pad(dechex((int) $g), 2, '0', 0) .
  595. str_pad(dechex((int) $b), 2, '0', 0)
  596. );
  597. }
  598. return $c;
  599. }
  600. }
Buy Me A Coffee