# OBSAH SOUBORU:
# - ziskavani dat o bunkach
#   - zde jen rucni formatovani, podminene a tabulky jsou zvlast
# - takove spolecne misto kde se to da dohromady a vrati do main

from datetime import datetime
from typing import Union

from openpyxl import Workbook
from openpyxl.cell import Cell, MergedCell
from openpyxl.worksheet.cell_range import CellRange
from openpyxl.worksheet.worksheet import Worksheet

import table
import utils
import settings
from classes import CCell, CRow, CCol, CTableStyle, CDxf, CCellDO
from enums import EFontFlags, EColor, EBorder, EBorderStyle, EMergedCell, ETableOptions

import math
import colors
import condi
import fontsize
import re


def get_cell_properties_dataonly(cell: Cell, cur_row: int, cur_col: int,
                                 is_merged: EMergedCell = EMergedCell.NOT_MERGED, merged_size=(1, 1)):
    """Funkce o bunkach zjistuje pouze jejich hodnoty a pozici v zavislosti na tom, jak jsou sloucene.
    Parameters:
        cell: zkoumana bunka
        cur_row: radek zkoumane bunky ve vystupni tabulce
        cur_col: sloupec, .....totez.....
        is_merged: typ sloucene bunky"""

    my_cell = CCellDO(cell.coordinate)
    my_cell.x = cur_col
    my_cell.y = cur_row

    if is_merged != EMergedCell.MERGED_EMPTY:
        disp_value = get_disp_value_num(cell, cell.value)
        my_cell.merged_size = merged_size
    else:
        disp_value = ''

    if disp_value not in [None, ''] or is_merged == EMergedCell.MERGED_EMPTY:
        my_cell.disp_value = disp_value
    else:
        if cell.value:
            my_cell.disp_value = cell.value
        else:
            my_cell.disp_value = ''

    return my_cell


def get_cell_properties(wb: Workbook, ws: Worksheet, cell: Cell, cur_row: int, cur_col: int,
                        table_styles: 'list[CTableStyle]', table_ranges, is_merged: EMergedCell = EMergedCell.NOT_MERGED,
                        border_only: bool = False):
    """Funkce slouzi k ziskani hledanych udaju o bunce openpyxl a jejich prevodu na bunku meho typu CCell. Openpyxl to
    ma vse ulozeno na 46161631 mistech a jeste nektere veci musim pocitat atd. Takze si to co me zajima ulozim do sveho
    datoveho typu se kterym dale pracuji

    Parameters:
        wb: odkaz na Excel sesit
        ws: odkaz na list sesitu
        cell: zkoumana bunka
        cur_row: radek zkoumane bunky ve vystupni tabulce
        cur_col: sloupec, .....totez.....
        table_styles: seznam vsech stylu tabulky uvnitr listu
        table_ranges: seznam vsech oblasti tabulky uvnitr listu
        is_merged: typ sloucene bunky
        border_only: skoncit zjistenim okraju?"""

    if ws is None:
        return None

    merged_cell = utils.is_merged(cell, ws)
    original_cell = cell
    is_condi = False

    # prazdna cast sloucene bunky nema v openpyxl ulozene zadne formaty. tak je prevezmu od te "pocatecni".
    # na formaty slouzi 'cell', na okraje slouzi 'original_cell'
    if merged_cell is not None and is_merged == EMergedCell.MERGED_EMPTY:
        cell = ws.cell(merged_cell.min_row, merged_cell.min_col)

    my_cell = table.get_cell_style_from_table(cell, table_styles, table_ranges)

    if my_cell is not None and my_cell.dxf.font_flags != EFontFlags.NONE:
        my_cell.dxf.font_flags ^= EFontFlags.NONE

    if my_cell is None and settings.conv.condi.enable:
        my_cell = condi.get_formatting(wb, ws, cell, cell.coordinate)
    elif settings.conv.condi.enable:
        condi_cell = condi.get_formatting(wb, ws, cell, cell.coordinate)
        if condi_cell is not None:
            my_cell += condi_cell

    if my_cell is None:
        my_cell = CCell(cell.coordinate)

    if merged_cell is None:
        is_last_h = False
        is_last_v = False
    else:
        is_last_h = original_cell.row == merged_cell.max_row
        is_last_v = original_cell.column == merged_cell.max_col

    # dolni horizontalni okraje (okraje prvniho radku vyresim zvlast, je to jedine misto kde nepujde o "dolni" okraj)
    cell_bottom_border, cell_bottom_border_style, cell_bottom_border_color, condi_h \
        = get_cell_borders_hor(wb, ws, original_cell, my_cell, table_styles, table_ranges,
                               is_last_h, is_merged != EMergedCell.NOT_MERGED)

    if settings.conv.border.enable:
        my_cell.dxf.borders = cell_bottom_border

    if settings.conv.border.style:
        my_cell.dxf.border_styles[0] = cell_bottom_border_style
    else:
        my_cell.dxf.border_styles[0] = EBorderStyle.THIN

    if settings.conv.color.border:
        my_cell.dxf.border_colors[0] = cell_bottom_border_color

    # prave vertikalni okraje (opet prvni sloupec resen zvlast)
    cell_right_border, cell_right_border_style, cell_right_border_color, condi_v \
        = get_cell_borders_vert(wb, ws, original_cell, my_cell, table_styles, table_ranges,
                                is_last_v, is_merged != EMergedCell.NOT_MERGED)

    if settings.conv.border.enable:
        my_cell.dxf.borders |= cell_right_border
        my_cell.dxf.borders ^= EBorder.NONE

    if settings.conv.border.style:
        my_cell.dxf.border_styles[1] = cell_right_border_style
    else:
        my_cell.dxf.border_styles[1] = EBorderStyle.THIN

    if settings.conv.color.border:
        my_cell.dxf.border_colors[1] = cell_right_border_color

    if border_only:
        return my_cell

    blank_cell = CCell(my_cell.coord)

    # barva z textoveho formatu (napr. Obecny, Cislo, Mena...)
    format_color = get_disp_value_clr(cell, cell.value)
    if my_cell == blank_cell:
        if format_color != EColor.NOT_SET:
            my_cell.dxf.fgcolor = format_color

    if is_merged != EMergedCell.MERGED_EMPTY:
        disp_value = get_disp_value_num(cell, cell.value)
    else:
        disp_value = ''

    if disp_value not in [None, ''] or is_merged == EMergedCell.MERGED_EMPTY:
        my_cell.disp_value = disp_value
    else:
        if cell.value:
            my_cell.disp_value = cell.value
        else:
            my_cell.disp_value = ''

    my_cell.x = cur_col
    my_cell.y = cur_row

    # zarovnani textu
    if settings.conv.halign:
        my_cell.get_halign(cell)

    if settings.conv.valign:
        my_cell.get_valign(cell)

    # barvy
    if not (condi_h or condi_v) and settings.conv.color.enable:
        if settings.conv.color.fg:
            cell_fgcolor = colors.get_cell_color_nine(wb, cell.font.color)
            if cell_fgcolor not in [EColor.NOT_SET, EColor.BLACK]:
                my_cell.dxf.fgcolor = cell_fgcolor

        if settings.conv.color.bg:
            cell_bgcolor = colors.get_cell_color_nine(wb, cell.fill.fgColor)
            if cell_bgcolor not in [EColor.NOT_SET, EColor.WHITE]:
                # and not (cell_bgcolor == EColor.BLACK and my_cell.fgcolor == EColor.BLACK):
                my_cell.dxf.bgcolor = cell_bgcolor

    # velikost a styl textu
    if settings.conv.text.textsize:
        my_cell.dxf.font_size = fontsize.get_font_size(cell, int(settings.ltx.default_text_size), 'article')

    if settings.conv.text.flags:
        if cell.font.b:
            my_cell.dxf.font_flags |= EFontFlags.BOLD

        if cell.font.i:
            my_cell.dxf.font_flags |= EFontFlags.ITALIC

        if cell.font.u:
            if cell.font.u[0] == 's':
                my_cell.dxf.font_flags |= EFontFlags.UNDER
            else:
                my_cell.dxf.font_flags |= EFontFlags.DOUBLE_UNDER

        if my_cell.dxf.font_size != EFontFlags.NONE:
            my_cell.dxf.font_flags ^= EFontFlags.NONE

    return my_cell


def get_cell_borders_hor(wb: Workbook, ws: Worksheet, cell: Cell, table_cell: CCell, table_styles: 'list[CTableStyle]',
                         table_ranges: 'list[tuple[tuple[int, int], tuple[int, int]]]',
                         is_last: bool = False, is_merged: bool = False):
    """Funkce zjistuje zda ma bunka horizontalni okraje. Je zamerena pouze na dolni okraj 'cell', s tim ze tento okraj
    v ni muze byt bud primo ulozen, nebo je ulozen jako horni okraj bunky o radek nize.
    Horni okraj je reseny zvlast, pujde jen o 1 radek"""

    if cell is None:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    if ws is None:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    if not settings.conv.border.enable:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    # nechci aby okraj "protinal vertikalne sloucene bunky"
    if not is_last and is_merged:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    cell_1down = ws.cell(cell.row + 1, cell.column)
    merged_1_down = utils.is_merged(cell_1down, ws)

    if not is_merged and merged_1_down is not None:
        cell_1down = ws.cell(merged_1_down.min_row, merged_1_down.min_col)

    did_i_condi = False
    if settings.conv.condi.enable:
        condi_cell = condi.get_formatting(wb, ws, cell, cell.coordinate, True)
        condi_cell_1_down = condi.get_formatting(wb, ws, cell_1down, cell_1down.coordinate, True)
    else:
        condi_cell = None
        condi_cell_1_down = None

    if settings.conv.tables.enable:
        table_cell_1_down = table.get_cell_style_from_table(cell_1down, table_styles, table_ranges)
    else:
        table_cell_1_down = None

    borders = EBorder.NONE
    border_style = EBorderStyle.NONE
    border_color = EColor.NOT_SET

    # cf okraje u jsou obou bunek
    if condi_cell is not None and condi_cell_1_down is not None:
        # vyssi prioritu ma spodni
        if condi_cell_1_down.dxf.border_priorities[2] > condi_cell.dxf.border_priorities[0]:
            if condi_cell_1_down.dxf.border_styles[2] != EBorderStyle.NONE:
                border_style = condi_cell_1_down.dxf.border_styles[2]
                borders |= EBorder.BOTTOM
                did_i_condi = True
            if condi_cell_1_down.dxf.border_colors[2] != EColor.NOT_SET:
                border_color = condi_cell_1_down.dxf.border_colors[2]

        # vyssi prioritu ma zkoumana, nebo maji obe stejnou
        else:
            if condi_cell.dxf.border_styles[0] != EBorderStyle.NONE:
                border_style = condi_cell.dxf.border_styles[0]
                borders |= EBorder.BOTTOM
                did_i_condi = True
            if condi_cell.dxf.border_colors[0] != EColor.NOT_SET:
                border_color = condi_cell.dxf.border_colors[0]

    # cf okraj je jen u zkoumane
    elif condi_cell is not None and condi_cell_1_down is None:
        if condi_cell.dxf.border_styles[0] != EBorderStyle.NONE:
            border_style = condi_cell.dxf.border_styles[0]
            borders |= EBorder.BOTTOM
            did_i_condi = True
        if condi_cell.border_colors[0] != EColor.NOT_SET:
            border_color = condi_cell.border_colors[0]

    # cf okraj je jen u bunky pod zkoumanou
    elif condi_cell is None and condi_cell_1_down is not None:
        if condi_cell_1_down.dxf.border_styles[2] != EBorderStyle.NONE:
            border_style = condi_cell_1_down.dxf.border_styles[2]
            borders |= EBorder.BOTTOM
            did_i_condi = True
        if condi_cell_1_down.border_colors[2] != EColor.NOT_SET:
            border_color = condi_cell_1_down.dxf.border_colors[2]

    # zadne cf okraje
    if not did_i_condi:
        # "rucni" okraje
        if cell_1down.border and cell_1down.border.top.style is not None:
            border_style = EBorderStyle[cell_1down.border.top.style.upper()]
            borders = EBorder.BOTTOM
            border_color = colors.get_cell_color_nine(wb, cell_1down.border.top.color)

        elif cell.border and cell.border.bottom.style is not None:
            border_style = EBorderStyle[cell.border.bottom.style.upper()]
            borders = EBorder.BOTTOM
            border_color = colors.get_cell_color_nine(wb, cell.border.bottom.color)

        # okraje z tabulky
        else:
            # okraje koliduji
            if table_cell_1_down is not None and table_cell is not None:
                if table_cell_1_down.dxf.border_priorities[2] > table_cell.dxf.border_priorities[0]:
                    border_style = table_cell_1_down.dxf.border_styles[2]
                    borders = EBorder.BOTTOM
                    border_color = table_cell_1_down.dxf.border_colors[2]
                else:
                    border_style = table_cell.dxf.border_styles[0]
                    borders = EBorder.BOTTOM
                    border_color = table_cell.dxf.border_colors[0]

            # okraj ma jen aktualni bunka
            elif table_cell is not None:
                border_style = table_cell.dxf.border_styles[0]
                borders = EBorder.BOTTOM
                border_color = table_cell.dxf.border_colors[0]

            # okraj ma jen bunka pod
            elif table_cell_1_down is not None:
                border_style = table_cell_1_down.dxf.border_styles[2]
                borders = EBorder.BOTTOM
                border_color = table_cell_1_down.dxf.border_colors[2]

    return borders, border_style, border_color, did_i_condi


def get_cell_borders_vert(wb: Workbook, ws: Worksheet, cell: Union[Cell, MergedCell], table_cell: CCell,
                          table_styles: 'list[CTableStyle]',
                          table_ranges: 'list[tuple[tuple[int, int], tuple[int, int]]]',
                          is_last: bool = False, is_merged: bool = False):
    """Funkce zjistuje zda ma bunka svisle okraje. Je zamerena pouze na pravy okraj 'cell', s tim ze tento okraj
    v ni muze byt bud primo ulozen, nebo je ulozen jako levy okraj bunky o sloupec vedle (napravo).
    Levy okraj je reseny zvlast, pujde jen o 1 sloupec"""

    if cell is None:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    if ws is None:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    if not settings.conv.border.enable:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    # nechci aby okraj "protinal vertikalne sloucene bunky"
    if not is_last and is_merged:
        return EBorder.NONE, EBorderStyle.NONE, EColor.NOT_SET, False

    cell_1right = ws.cell(cell.row, cell.column + 1)
    merged_1right = utils.is_merged(cell_1right, ws)

    if not is_merged and merged_1right is not None:
        cell_1right = ws.cell(merged_1right.min_row, merged_1right.min_col)

    did_i_condi = False
    if settings.conv.condi.enable:
        condi_cell = condi.get_formatting(wb, ws, cell, cell.coordinate, True)
        condi_cell_1_right = condi.get_formatting(wb, ws, cell_1right, cell_1right.coordinate, True)
    else:
        condi_cell = None
        condi_cell_1_right = None

    if settings.conv.tables.enable:
        table_cell_1_right = table.get_cell_style_from_table(cell_1right, table_styles, table_ranges)
    else:
        table_cell_1_right = None

    borders = EBorder.NONE
    border_style = EBorderStyle.NONE
    border_color = EColor.NOT_SET

    # cf okraje u jsou obou bunek
    if condi_cell is not None and condi_cell_1_right is not None:
        # vyssi prioritu ma ta vpravo
        if condi_cell_1_right.dxf.border_priorities[3] > condi_cell.dxf.border_priorities[1]:
            if condi_cell_1_right.dxf.border_styles[3] != EBorderStyle.NONE:
                border_style = condi_cell_1_right.dxf.border_styles[3]
                borders |= EBorder.RIGHT
                did_i_condi = True
            if condi_cell_1_right.dxf.border_colors[3] != EColor.NOT_SET:
                border_color = condi_cell_1_right.dxf.border_colors[3]

        # vyssi prioritu ma zkoumana, nebo maji obe stejnou
        else:
            if condi_cell.dxf.border_styles[1] != EBorderStyle.NONE:
                border_style = condi_cell.dxf.border_styles[1]
                borders |= EBorder.RIGHT
                did_i_condi = True
            if condi_cell.dxf.border_colors[1] != EColor.NOT_SET:
                border_color = condi_cell.dxf.border_colors[1]

    # cf okraj je jen u zkoumane
    elif condi_cell is not None and condi_cell_1_right is None:
        if condi_cell.dxf.border_styles[1] != EBorderStyle.NONE:
            border_style = condi_cell.dxf.border_styles[1]
            borders |= EBorder.RIGHT
            did_i_condi = True
        if condi_cell.dxf.border_colors[1] != EColor.NOT_SET:
            border_color = condi_cell.dxf.border_colors[1]

    # cf okraj je jen u bunky pod zkoumanou
    elif condi_cell is None and condi_cell_1_right is not None:
        if condi_cell_1_right.border_styles[3] != EBorderStyle.NONE:
            border_style = condi_cell_1_right.border_styles[3]
            borders |= EBorder.RIGHT
            did_i_condi = True
        if condi_cell_1_right.dxf.border_colors[3] != EColor.NOT_SET:
            border_color = condi_cell_1_right.dxf.border_colors[3]

    # zadne cf okraje
    if not did_i_condi:
        if cell_1right.border and cell_1right.border.left.style is not None:
            border_style = EBorderStyle[cell_1right.border.left.style.upper()]
            borders = EBorder.RIGHT
            border_color = colors.get_cell_color_nine(wb, cell_1right.border.left.color)

        elif cell.border and cell.border.right.style is not None:
            border_style = EBorderStyle[cell.border.right.style.upper()]
            borders = EBorder.RIGHT
            border_color = colors.get_cell_color_nine(wb, cell.border.right.color)

        # okraje z tabulky
        else:
            # okraje koliduji
            if table_cell_1_right is not None and table_cell is not None:
                if table_cell_1_right.dxf.border_priorities[3] > table_cell.dxf.border_priorities[1]:
                    border_style = table_cell_1_right.dxf.border_styles[3]
                    borders = EBorder.RIGHT
                    border_color = table_cell_1_right.dxf.border_colors[3]
                else:
                    border_style = table_cell.dxf.border_styles[1]
                    borders = EBorder.RIGHT
                    border_color = table_cell.dxf.border_colors[1]

            # okraj ma jen aktualni bunka
            elif table_cell is not None:
                border_style = table_cell.dxf.border_styles[1]
                borders = EBorder.RIGHT
                border_color = table_cell.dxf.border_colors[1]

            # okraj ma jen bunka pod
            elif table_cell_1_right is not None:
                border_style = table_cell_1_right.dxf.border_styles[3]
                borders = EBorder.RIGHT
                border_color = table_cell_1_right.dxf.border_colors[3]

    return borders, border_style, border_color, did_i_condi


def handle_datetime(cell_value: datetime, fmt_to_search: str):
    # protoze poradi jednotlivych datovych formatu muze byt v podstate libovolne, tak na to musim jit obecne. tedy
    # cely fmt retezec si rozdelim v mistech kde je nejaky netextovy znak a u kazde z tecto casti zkoumam, o kterou
    # cast datetime formatu se jedna. podle toho tvorim seznam jednotlivych casti formatu v presnem poradi, ze kterych
    # se snadno vytvori vysledny format data, ktery se pak pouze aplikuje na cell_value.

    # poznamka: protoze data jsou v excelu lokalizovana, pomoci nejakeho retezce mezi [] na zacatku, je nejlepsi si ten
    # konkretni format zadat na vstupu rucne. jinak se vsude oddeli jen mezerami, bo v tuhle chvili nemam zrovna cas
    # na uplnou implementaci.

    # nekdy ten fmt str ma na zacatku takovy [$-F800] nebo [$-cs-CZ], ktere ma neco spolecneho s lokazlizaci.
    # to neresim a automaticky davam ceskou lokalizaci, coz samozrejme neni spravny pristup.
    fmt_to_search = re.sub(r'\[\S+\]\W?', repl='', string=fmt_to_search, count=1)

    # ve fmt retezci je kazdy oddelovaci znak escapovan pomoci \, musi to tak byt protoze Microsoft to chce,
    # ja je tam nepotrebuju, teda doufam, a ve svoji verzi je proste smazu
    fmt_to_search = fmt_to_search.replace('\\', '')

    # jednotlive casti datetime retezce, musi byt necim oddeleny
    parts = re.split(r'([-_/\\+–.,* \s!?\'\"|<>]+)', fmt_to_search)

    # odpovidajici python datetime formaty v poradi vyskytu
    # https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior
    fmt_string_parts = []

    i = 0
    for part in parts:
        # 'm' muze jak mesic tak minuta. v pripade ze je predtim hh nebo za tim ss, tak je to minuta, jinak se to
        # vzdy povazuje za mesic
        mtc_month = re.search(r'm{1,4}', part)
        maybe_minute = re.search(r'((hh?\W{0,2}mm?\W{0,2}ss?)|(hh?\W{0,2}mm?)|(mm?\W{0,2}ss?))', fmt_to_search)

        mtc_day = re.search(r'd{1,4}', part)
        mtc_year = re.search('yy(yy)?', part)
        mtc_hour = re.search(r'hh?', part)
        mtc_second = re.search(r'ss?', part)
        mtc_ampm = re.search(r'AM?/PM?', part)

        # oddelovac
        sep = ''
        if i < len(parts) - 2 and re.search(r'\w+', parts[i + 1]) is None:
            sep = parts[i + 1]
        else:
            sep = ' '

        if mtc_day is not None:
            d_len = len(mtc_day.group())
            if d_len == 1:
                # 'X' slouzi jako znak ktery se ve vyslednem str urcite neobjevi. pomoci nej jsem schopen najit mista
                # kde je pocatecni nula tam, kde dle str z excelu byt nema.
                # cela tato sarada proto, ze datetime neumi zakladni vec jako mesic bez pocatecni nuly apod.
                fmt_string_parts.append('X%d' + sep)

            elif d_len == 2:
                fmt_string_parts.append('%d' + sep)

            elif d_len == 3:
                fmt_string_parts.append('%a' + sep)

            elif d_len == 4:
                fmt_string_parts.append('%A' + sep)

        elif mtc_month is not None and maybe_minute is None:
            m_len = len(mtc_month.group())

            if m_len == 1:
                fmt_string_parts.append('X%m' + sep)

            elif m_len == 2:
                fmt_string_parts.append('%m' + sep)

            elif m_len == 3:
                fmt_string_parts.append('%b' + sep)

            elif m_len == 4:
                fmt_string_parts.append('%B' + sep)

        elif maybe_minute is not None:
            if len(maybe_minute.group()) > 1:
                fmt_string_parts.append('%M' + sep)
            else:
                fmt_string_parts.append('X%M' + sep)

        elif mtc_year is not None:
            is_long = len(mtc_year.group()) > 2
            fmt_string_parts.append('%Y' + sep) if is_long else fmt_string_parts.append('%y' + sep)

        elif mtc_hour is not None:
            is_long = len(mtc_hour.group()) > 1
            fmt_string_parts.append('%h' + sep) if is_long else fmt_string_parts.append('X%h' + sep)

        elif mtc_second is not None:
            is_long = len(mtc_second.group()) > 1
            fmt_string_parts.append('%S' + sep) if is_long else fmt_string_parts.append('X%S' + sep)

        elif mtc_ampm is not None:
            fmt_string_parts.append('%p' + sep)

        i += 1

    # vysledny datetime formatovaci retezec
    import locale
    locale.setlocale(locale.LC_TIME, 'cs_CZ')

    dt_fmt_str = ''
    for i in range(len(fmt_string_parts)):
        dt_fmt_str += fmt_string_parts[i]

    return cell_value.strftime(dt_fmt_str).replace('X0', 'X').replace('X', '')


def get_disp_value_num(cell: Cell, cell_value):
    """Funknce vrati text bunky formatovany tak jak v excelu pro vse co neni 'Obecny', 'Datum', nebo 'Text'. Tato
      operace je nutna, protoze ulozene hodnoty mohou byt rozdilne od tech zobrazenych
      Parameters:
          cell (Cell): odkaz na Excel bunku
          cell_value (Any): hodnota ulozena v cell.value
      """

    # kody formatu v excelu jsou krasne popsany zde
    # https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
    # tldr:
    #   # ............. cislice tam muze nebo nemusi byt, zalezi na hodnote bunky (analogie formatu %g)
    #   # ............. cislice tam muze nebo nemusi byt, zalezi na hodnote bunky (analogie formatu %g)
    #   0 ............. cislice tam bude, pokud hodnota nema na teto pozici nic, vlozi se nula   (analogie %f)
    #   1 az 9 ........ tato cislice se na danou pozici vlozi (nechapu k cemu je to dobre, ale jde to)
    #   / ............. pokusi se desetinna mista vyjadrit jako zlomek (max 3 cislice / 3 cislice)
    #   # ### ......... oddeli tisice mezerou (pripadne carkou/teckou/whatever pokud to tam dame misto te mezery)
    #                   a pro cisla mensi nez 1 odebere nulu pred des. carkou
    #   # ##0 ......... oddeli tisice mezerou (nebo necim jinym), ale nulu pred des. carkou necha
    #   [Red] ......... nastavi cervenou barvu textu (moznych je i par dalsich barev)
    #   [Red][<100] ... nastavi cerveny text jen kdyz je hodnota mensi nez 100 (misto < a 100 jde pouzit i neco jine)
    #   "Kč" .......... hodnota se bude povazovat za menu
    #   %  ............ hodnota se bude povazovat za procenta (tj. vynasobi ji 100 a prida znak %)
    #   @ ............. blok textu (tj. kdybych nekde mel text i cisla, tim @ reknu aby s textem zachazel jako s textem
    #                   a cisla naformatoval dle zbytku formatovaciho retezce)
    #   \\ ............ textovy znak
    #   ? ............. princip jako u #, akorat tim ? rikame, ze maji byt zarovnane podle desetinne carky (tecky).
    #                   nejsem si uplne jisty jestli to pujde implementovat do latexu, protoze urcite nejde pouzit
    #                   \begin{align} pres vice bunek a pro \UseTblrLibrary{siunitx} je zase nutne prekopat cely sloupec

    if not hasattr(cell, 'number_format'):
        return cell_value

    # cely str formatu je rozdelen na (nejvyse) 4 casti pomoci ';'. Kazda cast odpovida (v tomto poradi) kladnym cislum,
    # zapornym cislum, nulam, textu. Pokud nektera "chybi", je nutno ji oddelit ';'. Pro zajisteni formatu bunky tedy
    # staci vytahnout potrebnou cast celeho retezce, zjistit co nastavuje a aplikovat na hodnotu bunky.
    # pri celem formatovani predpokladame, ze formatovaci retezec je platny (tj. excel ho dovolil nastavit)

    fmts = cell.number_format.split(';')
    fmts_len = len(fmts)
    fmt_to_search = ''
    disp_value = ''
    is_num = isinstance(cell_value, (int, float))

    if len(fmts) > 1:
        # datum je vzdy v prvnim str
        if is_num and not isinstance(cell_value, datetime) and cell_value > 0:
            fmt_to_search = fmts[0]
        elif isinstance(cell_value, datetime):
            return handle_datetime(cell_value, fmts[0])
        elif is_num and cell_value < 0 and fmts_len > 1:
            fmt_to_search = fmts[1]
        elif is_num and cell_value == 0 and fmts_len > 2:
            fmt_to_search = fmts[2]
        elif fmts_len > 3:
            # v pripade ze tam ma @, pak se obsah bunky zobrazi jak je a jdou k nemu pridat retezce pred anebo za.
            # pokud tam @ nema (musi to policko mit prazdne), pak jsou zobrazeny jen ty hodnoty, ktere jsou cele ciselne
            mtc_text = re.search(r'(\"[\w\d  ]+\"\s?)?@\s?((\"[\w\d  ]+\")|(_\S))?', fmt_to_search, re.U)
            if mtc_text is not None:
                mtc_preprended_text = re.search(r'(\"[\w\d  ]+\"\s?)?', fmt_to_search, re.U)
                if mtc_preprended_text is not None:
                    disp_value = mtc_preprended_text.group()

                disp_value += cell_value

                mtc_appended_text = re.search(r'((\"[\w\d  ]+\")|(_\S))?', fmt_to_search, re.U)
                if mtc_appended_text is not None:
                    disp_value += mtc_appended_text.group()

            elif is_num:
                disp_value = str(cell_value)

            return disp_value

        # zadnym formatovanim excelu rekneme aby hodnotu skryl (ale jen pokud je tam tech sub-sormatu vice)
        if len(fmt_to_search) == 0:
            return ''

    # datumy maji automaticky datovy typ datetime, nice
    elif isinstance(cell_value, datetime):
        return handle_datetime(cell_value, fmts[0])

    # neni nastaveny format
    else:
        # zde se provede escape specialnich latex znaku
        fmt_to_search = cell.number_format
        if not isinstance(cell_value, (int, float)):
            if settings.ltx.escape and isinstance(cell_value, str):
                has_ltx_math = re.search(r'\$[\w\d^_\[\](){}/÷+\-*×%.,\\]+\$', cell_value)

                # matika nezabira cely obsah bunky
                if has_ltx_math and str(cell_value) != has_ltx_math.group():
                    # ulozim si puvodni
                    nonescaped_math = has_ltx_math.group()
                    # neco pomoci ceho ji budu schopny najit i vcetne \
                    first_char_after_math = nonescaped_math[1]
                    # provedu escape
                    cell_value = cell_value.translate(str.maketrans({'{': r'\{', '}': r'\}',
                                                                     '&': r'\&', '%': r'\%', '_': r'\_', '^': r'\^',
                                                                     '#': r'\#', '\\': '\\textbackslash '}))

                    # najdu znovu matematickou cast a prepisu ji tou puvodni
                    escaped_math = re.search(r'\$\\?' + first_char_after_math + r'.*\$', cell_value)
                    cell_value = cell_value.replace(escaped_math.group(), nonescaped_math, 1)

                # je tam matika a neni zapnuty anti-escape
                if has_ltx_math is not None and not settings.ltx.dont_escape_math:
                    cell_value = cell_value.translate(str.maketrans({'{': r'\{', '}': r'\}',
                                                                     '&': r'\&', '%': r'\%', '_': r'\_', '^': r'\^',
                                                                     '#': r'\#', '\\': '\\textbackslash '}))

                # neni tam matika
                elif has_ltx_math is None:
                    cell_value = cell_value.translate(str.maketrans({'{': r'\{', '}': r'\}',
                                                                     '&': r'\&', '%': r'\%', '_': r'\_', '^': r'\^',
                                                                     '#': r'\#'}))

            # v tabularray jde vnutit novy radek, klasicky pomoci \\, akorat se dany text musi dat mezi {}
            if '\n' in str(cell_value):
                cell_value = cell_value.replace('\n', r' \\ ')
                cell_value = f'{{{cell_value}}}'

            return cell_value

    # zde se zjistí, které "Znaky" aplikovat
    mtc_percent = re.search(r'[#0](\.[0#]+)?\s?%', fmt_to_search)
    mtc_fraction = re.search(r'(#\" \")?(((\?/\?)|(\?\?/\?\?)'
                             r'|(\?{3}/\?{3}))|(\?{3}/[1-9]\d{3})|'
                             r'(\?\?/[1-9]\d)|(\?/[1-9]))', fmt_to_search)
    mtc_sci = re.search(r'E[+-]0+', fmt_to_search)
    mtc_accuracy = re.search(r'[0#]\.((0*#+})|(0+#*))', fmt_to_search)
    mtc_sep_thousands = re.search(r'^#,##[0#]', fmt_to_search)
    mtc_leading_digits = re.search(r'^\d+\.', fmt_to_search)
    mtc_trailing_digits = re.search(r'^[0#]+[1-9]+\.', fmt_to_search)
    mtc_currency = re.search(r'\"(\w{1,3})?([$€£₽¥₩ƒ₼₡₱¢₭₮₦฿₴₫])?\"', fmt_to_search, re.U)

    # a zde se aplikují (v pořadí definice)
    # procenta
    if mtc_percent is not None:
        mtc_decimals = re.search(r'\.[0#]+', mtc_percent.group())
        if mtc_decimals is not None:
            prec_f = len(mtc_decimals.group()) - 1
            prec_g = mtc_decimals.group().count('#')
            if prec_g != 0:
                disp_value = f'{100 * cell_value:.{prec_f}f}'
                for i in range(prec_g):
                    if disp_value[-1] == '0':
                        disp_value = disp_value[:-1]
                    else:
                        break

                disp_value = disp_value + ' \\%'
            else:
                disp_value = f'{100 * cell_value:.{prec_f}f} \\%'

            if mtc_percent.group()[0] == '#' and cell_value < 1:
                disp_value = disp_value[1:]

    # zlomky (https://docs.python.org/3/library/fractions.html#fractions.Fraction.limit_denominator)
    # excel si obcas dela co chce s temi zlomky. prestoze je recen jen maximalni pocet cifer, tak nepouzije co
    # nejpresnejsi vyjadreni. asi se snazi byt konzistentni v ramci vice bunek, to ale ja nemam jak efektivne zajistit.
    elif mtc_fraction is not None:
        mtc_known_denom = re.search(r'/[1-9]\d?\d?', mtc_fraction.group())
        is_complex_frac = re.search(r'#" "', mtc_fraction.group()) \
                          is not None and abs(cell_value) > 1

        # jmenovatel je zadany
        if mtc_known_denom is not None:
            max_denom = int(mtc_known_denom.group()[1:])
            dec_part = cell_value - math.floor(cell_value)

            numer = round(dec_part * max_denom)

            if numer != 0 and numer != max_denom:
                if settings.ltx.excel_frac_as_ltx_frac:
                    if is_complex_frac:
                        disp_value = f'{math.floor(cell_value)} $\\frac{{{numer}}}{{{max_denom}}}$'
                    else:
                        disp_value = f'$\\frac{{{numer + max_denom * math.floor(cell_value)}}}{{{max_denom}}}$'
                else:
                    if is_complex_frac:
                        disp_value = f'{math.floor(cell_value)} {numer}/{max_denom}'
                    else:
                        disp_value = f'{math.floor(cell_value)} ' \
                                     f'{numer + max_denom * math.floor(cell_value)}/{max_denom}'
            elif numer == max_denom:
                disp_value = f'{math.floor(cell_value) + 1}'
            else:
                disp_value = '0'

        # jakykoli x-mistny zlomek
        else:
            denom_len = len(re.search(r'/\?+', mtc_fraction.group()).group())
            if denom_len == 2:
                max_denom = 9
            elif denom_len == 3:
                max_denom = 99
            else:
                max_denom = 999

            if is_complex_frac:
                if abs(cell_value) > 1:
                    dec_part = cell_value - math.floor(cell_value)
                else:
                    dec_part = cell_value
            else:
                dec_part = cell_value

            from fractions import Fraction as Fr
            fr_part = Fr(dec_part).limit_denominator(max_denom)

            if settings.ltx.excel_frac_as_ltx_frac:
                fr_part_ltx = f'$\\frac{{{fr_part.numerator}}}{{{fr_part.denominator}}}$'
            else:
                fr_part_ltx = f'{fr_part.numerator}/{fr_part.denominator}'

            if is_complex_frac and abs(fr_part) < 1:
                if abs(cell_value) > 1:
                    disp_value = f'{math.floor(cell_value)} {fr_part_ltx}'
                else:
                    disp_value = f'{fr_part_ltx}'
            elif is_complex_frac:
                disp_value = f'{cell_value}'
            else:
                disp_value = fr_part_ltx

            if disp_value in ['$\\frac{0}{1}$', '0/1']:
                disp_value = '0'

    # vedecka notace
    elif mtc_sci is not None:
        mtc_decimals = re.search(r'\.[0#]+', fmt_to_search)
        if mtc_decimals is not None:
            prec_f = len(mtc_decimals.group()) - 1
            prec_g = mtc_decimals.group().count('#')
            if prec_g == 0:
                disp_value = f'{cell_value:.{prec_f}E}'

            # smazu nuly v desetinne casti
            else:
                disp_value = f'{cell_value:.{prec_f}E}'
                try:
                    for i in range(prec_g):
                        if disp_value[-1] == '0':
                            disp_value = disp_value[:-1]
                        else:
                            break
                except ValueError:
                    disp_value = f'{cell_value:.0E}'

            if not settings.ltx.use_sci_nums:
                e_idx = disp_value.index('E')
                num_part = disp_value[:e_idx]
                exp_part = disp_value[e_idx + 1:]
                if exp_part[0] == '-':
                    exp_part = f'-{exp_part[1:].lstrip("0")}'
                else:
                    exp_part = exp_part.lstrip("0")
                disp_value = f'${num_part} \\cdot 10^{{{exp_part}}}$'

    # desetinna presnost
    if mtc_accuracy is not None \
            and mtc_fraction is None \
            and mtc_sci is None and mtc_percent is None:

        show_lead_zero = True
        if mtc_accuracy.group()[0] == '#' and abs(cell_value < 1):
            show_lead_zero = False

        # zjistí se počty nul a počet '#'
        prec_f = len(mtc_accuracy.group()) - 2
        prec_g = mtc_accuracy.group().count('#')

        if not show_lead_zero:
            prec_g -= 1

        if prec_g != 0:
            # řetězec se formátuje na tolik desetinných míst,
            # kolik je v součtu v řetězci nul a '#' pomocí f-string
            disp_value = f'{cell_value:.{prec_f}f}'

            # případné nuly se postupně odeberou tak
            # že se zkoumá poslední znak výstupu tolikrát,
            # kolik je v řetězci '#'. Pokud je '0', pak
            # je smazán.
            for i in range(prec_g):
                if disp_value[-1] == '0':
                    disp_value = disp_value[:-1]
        else:
            # v tomto případě v řetězci nejsou žádné '#'
            disp_value = f'{cell_value:.{prec_f}f}'

        if not show_lead_zero:
            disp_value = disp_value[1:]

    # oddeleni tisicu
    if mtc_sep_thousands is not None:
        if disp_value == '':
            disp_value = str(cell_value)

        separator = settings.ltx.thousand_separator
        if len(separator) > 1:
            separator = '~'

        mtc_dot = re.search(r'[.\s]', disp_value)
        if mtc_dot is not None:
            non_dec_part = disp_value[:mtc_dot.start()]
        else:
            non_dec_part = disp_value

        non_dec_part_num = int(non_dec_part)
        non_dec_part = f'{non_dec_part_num:,}'.replace(',', separator)

        if mtc_dot is not None:
            disp_value = f'{non_dec_part}.{disp_value[mtc_dot.end():]}'
        else:
            disp_value = non_dec_part

        # pripade odebrani nuly pred des carkou
        if cell_value < 1 and re.search('0', mtc_sep_thousands.group()) is None:
            try:
                # pokud .index() nenajde shodu, vyvola ValueError, coz je pecka
                dot_idx = disp_value.index('.')
                disp_value = disp_value[dot_idx:]
            except ValueError:
                # nemame co odebrat
                pass

    # cislice pred hodnotou (napr. '8.123' -> '12048.123')
    if mtc_leading_digits is not None:
        try:
            dot_idx = str(cell_value).index('.')
            cell_value_non_dec = str(cell_value)[:dot_idx]
        except ValueError:
            cell_value_non_dec = str(cell_value)

        if len(cell_value_non_dec) < len(mtc_leading_digits.group()) - 1:
            # pokud jsou ve formatovacim retezci pred hodnotou jen nuly, pak se jich pred cislo prida jen tolik, kolik
            # je jich v tom formatovacim retezci navic oproti celociselne casti hodnoty
            zeros_only = re.search(r'0+\.', mtc_leading_digits.group())
            if zeros_only is not None:
                num_zeros_to_keep = (len(zeros_only.group()) - 1) - len(cell_value_non_dec)
                disp_value = ('0' * num_zeros_to_keep) + disp_value
            else:
                # -1 protoze to hledam vcetne des. tecky
                disp_value = mtc_leading_digits.group()[:-1] + disp_value

    # cislice pred desetinnou carkou (nechapu k cemu, ale mozne to je. napr. '8.123' -> '869.123')
    if mtc_trailing_digits is not None:
        try:
            dot_idx = disp_value.index('.')
            parts = [disp_value[:dot_idx], disp_value[dot_idx:]]
            disp_value = parts[0] + mtc_trailing_digits.group() + parts[1]
        except ValueError:
            # nema desetinnou tecku, tak to pridam na konec
            disp_value = disp_value + mtc_trailing_digits.group()

    # mena
    if mtc_currency is not None and mtc_percent is None:
        # mena je na konci
        if mtc_accuracy is not None and mtc_currency.start() > mtc_accuracy.start():
            # menu hledam vcetne "", takze se jich musim zbavit.
            disp_value = disp_value + ' ' + mtc_currency.group().strip('"')
        elif mtc_sep_thousands is not None and mtc_currency.start() > mtc_sep_thousands.start():
            disp_value = disp_value + ' ' + mtc_currency.group().strip('"')
        elif mtc_fraction is not None and mtc_currency.start() > mtc_fraction.start():
            disp_value = disp_value + ' ' + mtc_currency.group().strip('"')
        elif mtc_sci is not None and mtc_currency.start() > mtc_sci.start():
            disp_value = disp_value + ' ' + mtc_currency.group().strip('"')
        elif mtc_percent is not None and mtc_currency.start() > mtc_percent.start():
            disp_value = disp_value + ' ' + mtc_currency.group().strip('"')

        # mena je na zacatku
        else:
            disp_value = mtc_currency.group().strip('"') + ' ' + disp_value

    if settings.ltx.dec_separator != '.' and len(settings.ltx.dec_separator) == 1:
        disp_value = disp_value.replace('.', settings.ltx.dec_separator)

    return disp_value


def get_disp_value_clr(cell: Cell, cell_value):
    """Funkce z vlastniho formatovani bunky (viz get_disp_value_num) ziska pouze barvu textu. V pripade ze je zmena
    barvy podminena (napr. jen pro zaporne hodnoty, cisla mensi nez X), barvu vraci jne pri splneni podminky
    Parameters:
          cell (Cell): odkaz na Excel bunku
          cell_value (Any): hodnota ulozena v cell.value"""

    if not hasattr(cell, 'number_format'):
        return EColor.NOT_SET

    fmts = cell.number_format.split(';')
    fmts_len = len(fmts)
    fmt_to_search = ''
    cell_color_fmt = ''
    is_num = isinstance(cell_value, (int, float))

    if len(fmts) > 1:
        if is_num and cell_value > 0:
            fmt_to_search = fmts[0]
        elif is_num and cell_value < 0 and fmts_len > 1:
            fmt_to_search = fmts[1]
        elif is_num and cell_value == 0 and fmts_len > 2:
            fmt_to_search = fmts[2]
        elif fmts_len > 3:
            fmt_to_search = fmts[3]

    if not isinstance(cell_value, (int, float)):
        return EColor.NOT_SET

    mtc_cond_color = re.search(r'\[((red)|(blue)|(cyan)|(green)|(magenta)|(yellow)|(black)|(white))]'
                               r'\[[<>=]=?\d+(\.\d+)]', fmt_to_search.lower())
    mtc_color = re.search(r'\[((red)|(blue)|(cyan)|(green)|(magenta)|(yellow)|(black)|(white))]',
                          fmt_to_search.lower())

    # podminena barva
    if mtc_cond_color is not None:
        # nevim dopredu jakou operaci bude provadet, takze pouziju knihovnu operator, ktera umozni tu porovnavaci
        # operaci (funkci) zavest jako promennou
        import operator as oper
        ops = {'<': oper.lt, '>': oper.gt, '=': oper.eq, '==': oper.eq, '>=': oper.ge, '<=': oper.le}
        cond_operator = re.search(r'[<>=]=?', mtc_cond_color.group())
        operator_function = ops[cond_operator]
        cond_value = re.search(r'\d+(\.\d+)?', mtc_cond_color.group())

        if operator_function(cell_value, cond_value):
            cond_color = re.search(r'\[((red)|(blue)|(cyan)|(green)|(magenta)|(yellow)|(black)|(white))]',
                                   mtc_cond_color.group().lower())
            cell_color_fmt = f'{cond_color.group()[1:-1]}'

    # nepodminena barva
    if mtc_cond_color is None and mtc_color is not None:
        # hledam to vcetne [], ktere musi byt na zacatku a konci shody
        cell_color_fmt = f'{mtc_color.group()[1:-1]}'

    return EColor(cell_color_fmt)
