- <?php
-
- /** @noinspection MultiAssignmentUsageInspection */
-
- namespace Shuchkin;
-
- use SimpleXMLElement;
-
- class SimpleXLSXEx
- {
- public static $IC = [
- 0 => '000000',
- 1 => 'FFFFFF',
- 2 => 'FF0000',
- 3 => '00FF00',
- 4 => '0000FF',
- 5 => 'FFFF00',
- 6 => 'FF00FF',
- 7 => '00FFFF',
- 8 => '000000',
- 9 => 'FFFFFF',
- 10 => 'FF0000',
- 11 => '00FF00',
- 12 => '0000FF',
- 13 => 'FFFF00',
- 14 => 'FF00FF',
- 15 => '00FFFF',
- 16 => '800000',
- 17 => '008000',
- 18 => '000080',
- 19 => '808000',
- 20 => '800080',
- 21 => '008080',
- 22 => 'C0C0C0',
- 23 => '808080',
- 24 => '9999FF',
- 25 => '993366',
- 26 => 'FFFFCC',
- 27 => 'CCFFFF',
- 28 => '660066',
- 29 => 'FF8080',
- 30 => '0066CC',
- 31 => 'CCCCFF',
- 32 => '000080',
- 33 => 'FF00FF',
- 34 => 'FFFF00',
- 35 => '00FFFF',
- 36 => '800080',
- 37 => '800000',
- 38 => '008080',
- 39 => '0000FF',
- 40 => '00CCFF',
- 41 => 'CCFFFF',
- 42 => 'CCFFCC',
- 43 => 'FFFF99',
- 44 => '99CCFF',
- 45 => 'FF99CC',
- 46 => 'CC99FF',
- 47 => 'FFCC99',
- 48 => '3366FF',
- 49 => '33CCCC',
- 50 => '99CC00',
- 51 => 'FFCC00',
- 52 => 'FF9900',
- 53 => 'FF6600',
- 54 => '666699',
- 55 => '969696',
- 56 => '003366',
- 57 => '339966',
- 58 => '003300',
- 59 => '333300',
- 60 => '993300',
- 61 => '993366',
- 62 => '333399',
- 63 => '333333',
- 64 => '000000', // System Foreground
- 65 => 'FFFFFF', // System Background'
- ];
- public static $CH = [
- 0 => 'ANSI_CHARSET',
- 1 => 'DEFAULT_CHARSET',
- 2 => 'SYMBOL_CHARSET',
- 77 => 'MAC_CHARSET',
- 128 => 'SHIFTJIS_CHARSET',
- //129 => 'HANGEUL_CHARSET',
- 129 => 'HANGUL_CHARSET',
- 130 => 'JOHAB_CHARSET',
- 134 => 'GB2312_CHARSET',
- 136 => 'CHINESEBIG5_CHARSET',
- 161 => 'GREEK_CHARSET',
- 162 => 'TURKISH_CHARSET',
- 163 => 'VIETNAMESE_CHARSET',
- 177 => 'HEBREW_CHARSET',
- 178 => 'ARABIC_CHARSET',
- 186 => 'BALTIC_CHARSET',
- 204 => 'RUSSIAN_CHARSET',
- 222 => 'THAI_CHARSET',
- 238 => 'EASTEUROPE_CHARSET',
- 255 => 'OEM_CHARSET'
- ];
- public $xlsx;
- public $themeColors;
- public $fonts;
- public $fills;
- public $borders;
- public $cellStyles;
- public $css;
-
- public function __construct(SimpleXLSX $xlsx)
- {
- $this->xlsx = $xlsx;
- $this->readThemeColors();
- $this->readFonts();
- $this->readFills();
- $this->readBorders();
- $this->readXfs();
- }
- public function readThemeColors()
- {
- $this->themeColors = [];
- if (isset($this->xlsx->theme->themeElements->clrScheme)) {
- $colors12 = ['lt1', 'dk1', 'lt2', 'dk2','accent1','accent2','accent3','accent4','accent5',
- 'accent6','hlink','folHlink'];
- foreach ($colors12 as $c) {
- $v = $this->xlsx->theme->themeElements->clrScheme->{$c};
- if (isset($v->sysClr)) {
- $this->themeColors[] = (string) $v->sysClr['lastClr'];
- } elseif (isset($v->srgbClr)) {
- $this->themeColors[] = (string) $v->srgbClr['val'];
- } else {
- $this->themeColors[] = null;
- }
- }
- }
- }
-
-
- public function readFonts()
- {
- // fonts
- $this->fonts = [];
- if (isset($this->xlsx->styles->fonts->font)) {
- foreach ($this->xlsx->styles->fonts->font as $v) {
- $u = '';
- if (isset($v->u)) {
- $u = isset($v->u['val']) ? (string) $v->u['val'] : 'single';
- }
- $this->fonts[] = [
- 'b' => isset($v->b) && ($v->b['val'] === null || $v->b['val']),
- 'i' => isset($v->i) && ($v->i['val'] === null || $v->i['val']),
- 'u' => $u,
- 'strike' => isset($v->strike) && ($v->strike['val'] === null || $v->strike['val']),
- 'sz' => isset($v->sz['val']) ? (int) $v->sz['val'] : 11,
- 'color' => $this->getColorValue($v->color),
- 'name' => isset($v->name['val']) ? (string) $v->name['val'] : 'Calibri',
- 'family' => isset($v->family['val']) ? (int) $v->family['val'] : 2,
- 'charset' => isset($v->charset['val']) ? (int) $v->charset['val'] : 1,
- 'scheme' => isset($v->scheme['val']) ? (string) $v->scheme['val'] : 'minor'
- ];
- }
- }
- }
- public function readFills()
- {
- // fills
- $this->fills = [];
- if (isset($this->xlsx->styles->fills->fill)) {
- foreach ($this->xlsx->styles->fills->fill as $v) {
- if (isset($v->patternFill)) {
- $this->fills[] = [
- 'pattern' => isset($v->patternFill['patternType']) ? (string) $v->patternFill['patternType'] : 'none',
- 'fgcolor' => $this->getColorValue($v->patternFill->fgColor),
- 'bgcolor' => $this->getColorValue($v->patternFill->bgColor)
- ];
- }
- }
- }
- }
- public function readBorders()
- {
- $this->borders = [];
- if (isset($this->xlsx->styles->borders->border)) {
- foreach ($this->xlsx->styles->borders->border as $v) {
- $this->borders[] = [
- 'left' => [
- 'style' => (string) $v->left['style'],
- 'color' => $this->getColorValue($v->left->color)
- ],
- 'right' => [
- 'style' => (string) $v->right['style'],
- 'color' => $this->getColorValue($v->right->color)
- ],
- 'top' => [
- 'style' => (string) $v->top['style'],
- 'color' => $this->getColorValue($v->top->color)
- ],
- 'bottom' => [
- 'style' => (string) $v->bottom['style'],
- 'color' => $this->getColorValue($v->bottom->color)
- ],
- 'diagonal' => [
- 'style' => (string) $v->diagonal['style'],
- 'color' => $this->getColorValue($v->diagonal->color)
- ],
- 'horizontal' => [
- 'style' => (string) $v->horizontal['style'],
- 'color' => $this->getColorValue($v->horizontal->color)
- ],
- 'vertical' => [
- 'style' => (string) $v->vertical['style'],
- 'color' => $this->getColorValue($v->vertical->color)
- ],
- 'diagonalUp' => (bool) $v['diagonalUp'],
- 'diagonalDown' => (bool) $v['diagonalDown'],
- 'outline' => !(isset($v['outline'])) || $v['outline']
- ];
- }
- }
- }
-
- public function readXfs()
- {
- // cellStyles
- $this->cellStyles = [];
- if (isset($this->xlsx->styles->cellStyleXfs->xf)) {
- foreach ($this->xlsx->styles->cellStyleXfs->xf as $v) {
- $x = [];
- foreach ($v->attributes() as $k1 => $v1) {
- $x[ $k1 ] = (int) $v1;
- }
- if (isset($v->alignment)) {
- foreach ($v->alignment->attributes() as $k1 => $v1) {
- $x['alignment'][$k1] = (string) $v1;
- }
- }
- $this->cellStyles[] = $x;
- }
- }
- // css
- $this->css = [];
- // xf
- if (isset($this->xlsx->styles->cellXfs->xf)) {
- $k = 0;
- foreach ($this->xlsx->styles->cellXfs->xf as $v) {
- $cf = &$this->xlsx->cellFormats[$k];
-
- // alignment
- $alignment = [];
- if (isset($v->alignment)) {
- foreach ($v->alignment->attributes() as $k1 => $v1) {
- $alignment[$k1] = (string)$v1;
- }
- }
-
- if (isset($cf['xfId'], $this->cellStyles[ $cf['xfId'] ])) {
- $s = $this->cellStyles[$cf['xfId']];
- if (!empty($s['applyNumberFormat'])) {
- $cf['numFmtId'] = $s['numFmtId'];
- }
- if (!empty($s['applyFont'])) {
- $cf['fontId'] = $s['fontId'];
- }
- if (!empty($s['applyBorder'])) {
- $cf['borderId'] = $s['borderId'];
- }
- if (!empty($s['applyAlignment'])) {
- $alignment = $s['alignment'];
- }
- }
- $cf['alignment'] = $alignment;
-
- $align = null;
- if (isset($alignment['horizontal'])) {
- $align = $alignment['horizontal'];
- if ($align === 'centerContinuous') {
- $align = 'center';
- }
- if ($align === 'distributed') {
- $align = 'justify';
- }
- if ($align === 'general') {
- $align = null;
- }
- }
- $cf['align'] = $align;
-
- $valign = null;
- if (isset($alignment['vertical'])) {
- $valign = $alignment['vertical'];
- if ($valign === 'center' || $valign === 'distributed' || $valign === 'justify') {
- $valign = 'middle';
- }
- }
- $cf['valign'] = $valign;
-
- // font
- $cf['font'] = $this->fonts[ $cf['fontId'] ]['name'];
- $cf['color'] = $this->fonts[ $cf['fontId'] ]['color'];
- $cf['f-size'] = $this->fonts[ $cf['fontId'] ]['sz'];
- $cf['f-b'] = $this->fonts[ $cf['fontId'] ]['b'];
- $cf['f-i'] = $this->fonts[ $cf['fontId'] ]['i'];
- $cf['f-u'] = $this->fonts[ $cf['fontId'] ]['u'];
- $cf['f-strike'] = $this->fonts[ $cf['fontId'] ]['strike'];
-
- // fill
- $cf['bgcolor'] = $this->fills[ $cf['fillId'] ]['fgcolor'];
-
- // borders
- if (isset($this->borders[ $cf['borderId'] ])) {
- $border = $this->borders[ $cf['borderId'] ];
-
- $borders = ['left', 'right', 'top', 'bottom'];
- foreach ($borders as $b) {
- $cf['b-' . $b.'-color'] = $border[$b]['color'];
- if ($border[$b]['style'] === '' || $border[$b]['style'] === 'none') {
- $cf['b-' . $b.'-style'] = '';
- $cf['b-' . $b.'-color'] = '';
- } elseif ($border[$b]['style'] === 'dashDot'
- || $border[$b]['style'] === 'dashDotDot'
- || $border[$b]['style'] === 'dashed'
- ) {
- $cf['b-' . $b.'-style'] = 'dashed';
- } else {
- $cf['b-' . $b.'-style'] = 'solid';
- }
- }
- }
-
- $css = '';
-
- if ($cf['color']) {
- $css .= 'color: #'.$cf['color'].';';
- }
- if ($cf['font']) {
- $css .= 'font-family: '.$cf['font'].';';
- }
- if ($cf['f-size']) {
- // $css .= 'font-size: '.($cf['f-size'] * 0.352806).'mm;';
- $css .= 'font-size: '.(round($cf['f-size'] * 1.3333) + 2).'px;';
- }
- if ($cf['f-b']) {
- $css .= 'font-weight: bold;';
- }
- if ($cf['f-i']) {
- $css .= 'font-style: italic;';
- }
- if ($cf['f-u']) {
- $css .= 'text-decoration: underline;';
- }
- if ($cf['f-strike']) {
- $css .= 'text-decoration: line-through;';
- }
- if ($cf['bgcolor']) {
- $css .= 'background-color: #' . $cf['bgcolor'] . ';';
- }
- if ($cf['align']) {
- $css .= 'text-align: '.$cf['align'].';';
- }
- if ($cf['valign']) {
- $css .= 'vertical-align: '.$cf['valign'].';';
- }
- if ($cf['b-top-style']) {
- $css .= 'border-top-style: '.$cf['b-top-style'].';';
- $css .= 'border-top-color: #'.$cf['b-top-color'].';';
- $css .= 'border-top-width: thin;';
- }
- if ($cf['b-right-style']) {
- $css .= 'border-right-style: '.$cf['b-right-style'].';';
- $css .= 'border-right-color: #'.$cf['b-right-color'].';';
- $css .= 'border-right-width: thin;';
- }
- if ($cf['b-bottom-style']) {
- $css .= 'border-bottom-style: '.$cf['b-bottom-style'].';';
- $css .= 'border-bottom-color: #'.$cf['b-bottom-color'].';';
- $css .= 'border-bottom-width: thin;';
- }
- if ($cf['b-left-style']) {
- $css .= 'border-left-style: '.$cf['b-left-style'].';';
- $css .= 'border-left-color: #'.$cf['b-left-color'].';';
- $css .= 'border-left-width: thin;';
- }
- $this->css[$k] = $css;
-
- $k++;
- }
- }
- }
-
- public function readRowsEx($worksheetIndex = 0, $limit = 0)
- {
- if (($ws = $this->xlsx->worksheet($worksheetIndex)) === false) {
- return;
- }
-
- $dim = $this->xlsx->dimension($worksheetIndex);
- $numCols = $dim[0];
- $numRows = $dim[1];
-
- /*$emptyRow = array();
- for ($i = 0; $i < $numCols; $i++) {
- $emptyRow[] = null;
- }
- */
- $cols = [];
- for ($i = 0; $i < $numCols; $i++) {
- $cols[] = ['s' => 0, 'hidden' => false, 'width' => 0];
- }
- // $hiddenCols = [];
- /* @var SimpleXMLElement $ws */
- if (isset($ws->cols)) {
- foreach ($ws->cols->col as $col) {
- $min = (int)$col['min'];
- $max = (int)$col['max'];
- if (($max-$min) > 100) {
- $max = $min;
- }
- for ($i = $min; $i <= $max; $i++) {
- $cols[$i-1] = [
- 's' => (int)$col['style'],
- 'hidden' => (bool)$col['hidden'],
- 'width' => $col['customWidth'] ? (float) $col['width'] : 0
- ];
- }
- }
- }
-
- $curR = 0;
- $_limit = $limit;
-
- foreach ($ws->sheetData->row as $row) {
- $curC = 0;
-
- $r_idx = (int)$row['r'];
- $r_style = ['s' => 0, 'hidden' => (bool)$row['hidden'], 'height' => 0];
- if ($row['customFormat']) {
- $r_style['s'] = (int)$row['s'];
- }
- if ($row['customHeight']) {
- $r_style['height'] = (int)$row['ht'];
- }
-
- $cells = [];
- for ($i = 0; $i < $numCols; $i++) {
- $cells[] = null;
- }
-
- foreach ($row->c as $c) {
- $r = (string)$c['r'];
- $t = (string)$c['t'];
- $s = (int)$c['s'];
-
- $idx = $this->xlsx->getIndex($r);
- $x = $idx[0];
- $y = $idx[1];
-
- if ($x > -1) {
- $curC = $x;
- if ($curC >= $numCols) {
- $numCols = $curC + 1;
- }
- while ($curR < $y) {
- $emptyRow = [];
- for ($i = 0; $i < $numCols; $i++) {
- $emptyRow[] = $this->valueEx($cols[$i], $i, $curR);
- }
- yield $emptyRow;
- $curR++;
-
- $_limit--;
- if ($_limit === 0) {
- return;
- }
- }
- }
- $data = [
- 'type' => $t,
- 'name' => $r,
- 'value' => $this->xlsx->value($c),
- 'href' => $this->xlsx->href($worksheetIndex, $c),
- 'f' => (string)$c->f,
- 'r' => $r_idx,
- 's' => ($s > 0) ? $s : $cols[$curC]['s'],
- 'hidden' => $r_style['hidden'] || $cols[$curC]['hidden'],
- 'width' => $cols[$curC]['width'],
- 'height' => $r_style['height']
- ];
- $cells[$curC] = $this->valueEx($data, $curC, $curR);
-
- $curC++;
- }
- // check empty cells
- for ($i = 0; $i < $numCols; $i++) {
- if ($cells[$i] === null) {
- if ($r_style['s'] > 0) {
- $data = $r_style;
- } else {
- $data = $cols[$i];
- }
- $data['width'] = $cols[$i]['width'];
- $data['height'] = $r_style['height'];
- $cells[$i] = $this->valueEx($data, $i, $curR);
- }
- }
-
- yield $cells;
-
- $curR++;
- $_limit--;
- if ($_limit === 0) {
- break;
- }
- }
-
- while ($curR < $numRows) {
- $emptyRow = [];
- for ($i = 0; $i < $numCols; $i++) {
- $data = $cols[$i];
- $emptyRow[] = $this->valueEx($data, $i, $curR);
- }
- yield $emptyRow;
- $curR++;
- $_limit--;
- if ($_limit === 0) {
- return;
- }
- }
- }
-
- protected function valueEx($data, $x = null, $y = null)
- {
-
- $r = [
- 'type' => '',
- 'name' => '',
- 'value' => '',
- 'href' => '',
- 'f' => '',
- 'format' => '',
- 's' => 0,
- 'css' => '',
- 'r' => '',
- 'hidden' => false,
- 'width' => 0,
- 'height' => 0
- ];
- foreach ($data as $k => $v) {
- if (isset($r[$k])) {
- $r[$k] = $v;
- }
- }
- $st = &$this->xlsx->cellFormats[$r['s']];
- $r['format'] = $st['format'];
- $r['css'] = &$this->css[ $r['s'] ];
- if ($r['value'] !== '' && !$st['align'] && !in_array($r['type'], ['s','str','inlineStr','e'], true)) {
- $r['css'] .= 'text-align: right;';
- }
-
- if (!$r['name']) {
- $c = '';
- for ($k = $x; $k >= 0; $k = (int)($k / 26) - 1) {
- $c = chr($k % 26 + 65) . $c;
- }
- $r['name'] = $c . ($y + 1);
- $r['r'] = $y+1;
- }
- return $r;
- }
- public function getColorValue(SimpleXMLElement $a = null, $default = '')
- {
- if ($a === null) {
- return $default;
- }
- $c = $default; // auto
- if ($a['rgb'] !== null) {
- $c = substr((string) $a['rgb'], 2); // FFCCBBAA -> CCBBAA
- } elseif ($a['indexed'] !== null && isset(static::$IC[ (int) $a['indexed'] ])) {
- $c = static::$IC[ (int) $a['indexed'] ];
- } elseif ($a['theme'] !== null && isset($this->themeColors[ (int) $a['theme'] ])) {
- $c = $this->themeColors[ (int) $a['theme'] ];
- }
- if ($a['tint'] !== null) {
- list($r,$g,$b) = array_map('hexdec', str_split($c, 2));
- $tint = (float) $a['tint'];
- if ($tint > 0) {
- $r += (255 - $r) * $tint;
- $g += (255 - $g) * $tint;
- $b += (255 - $b) * $tint;
- } else {
- $r += $r * $tint;
- $g += $g * $tint;
- $b += $b * $tint;
- }
- $c = strtoupper(
- str_pad(dechex((int) $r), 2, '0', 0) .
- str_pad(dechex((int) $g), 2, '0', 0) .
- str_pad(dechex((int) $b), 2, '0', 0)
- );
- }
- return $c;
- }
- }