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