<?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; } }