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