# OBSAH SOUBORU:
# - ziskavani podmineneho formatovani
# - pomocne funkce pro urceni platnosti pravidel

import colorsys
import math
import re
from builtins import range

from openpyxl import Workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.cell_range import MultiCellRange
from openpyxl.worksheet.worksheet import Worksheet
from collections import Counter

import colors
import dataGet
import utils
import settings
from classes import CCell, CRow, CCol
from enums import EFontSize, EHAlign, EVAlign, EFontFlags, EColor, EConvertedState, EBorderStyle, EBorder, EMergedCell

calculated_avgs = {}
unique_in_ranges = {}
non_rep_in_ranges = {}
dupl_in_ranges = {}
min_in_ranges = {}
max_in_ranges = {}
med_in_ranges = {}
lin_interps = {}
pos_lists = {}
value_counters_in_ranges = {}


def lin_interp(x: float, x0: float, x1: float):
    return x0 + (x * (x1 - x0))


def calc_colorscale3(ws: Worksheet, calc_range: MultiCellRange, cell_value: float, clrs: 'list[str]', percentile: float):
    """Funkce pocita, jake barve ve spojitem trojbarevnem prechodu (gradientu) odpovida hodnota cell_value. A to tak
    ze si to zredukuje na dvojbarevny prechod (ktery uz umi resit calc_colorscale2) podle toho zda je hodnota nad nebo
    pod medianem (nad/pod vstupnim percentilem). Shodou okolnosti to excel dela uplne stejne"""
    if ws is None:
        raise Exception('Neplatny list!')

    med = get_median(ws, calc_range, percentile)
    cell_clr = EColor.NOT_SET

    if med is not None and cell_value >= med:
        cell_clr = calc_colorscale2(ws, calc_range, cell_value, [clrs[1], clrs[2]], minval=med)
    elif med is not None:
        cell_clr = calc_colorscale2(ws, calc_range, cell_value, [clrs[0], clrs[1]], maxval=med)

    return cell_clr


def calc_colorscale2(ws: Worksheet, calc_range: MultiCellRange, cell_value: float, clrs: 'list[str]',
                     maxval: float = None, minval: float = None):
    """Funkce pocita, jake barve ve spojitem dvoubarevnem prechodu (gradientu) odpovida hodnota cell_value.
    V tuhle chvili je limitovana debilnim ninecolors, uz aby autor pridal moznost udelat barvu pozadi pomoci rgb

    Returns:
        'cell_ninecolor'
        """
    if ws is None:
        raise Exception('Neplatny list!')

    # prevedu si barvy
    color_start = colors.hex_to_rgb(clrs[0])
    color_end = colors.hex_to_rgb(clrs[-1])

    # zjistim min a max hodnoty v danem range (pokud prichazim z trojbarevne skaly, tak zjistuju jen jednu z nich,
    # ta druha bude median)
    if maxval is None:
        range_max = get_max(ws, calc_range)
    else:
        range_max = maxval

    if minval is None:
        range_min = get_min(ws, calc_range)
    else:
        range_min = minval

    # zjistim, kde se hodnota bunky nachazi mezi min (pos == 0) a max hodnotou (pos == 1). delam jakoby pomer usecek
    pos = 1
    denom = range_max - range_min
    if denom != 0:
        pos = (cell_value - range_min) / denom

    # podle pos si spocitam barvu, ktera teto hodnote nalezi pomoci lin interpolace. interpoluju kazdou slozku zvlast
    # ( nezname: y, zname: pos, y0, y1, y1 > y0 a 1 >= pos >= 0. pak se lin interpolace dela pomoci:
    # y = y0 + (pos * (y1 - y0) )
    clr = [0, 0, 0]
    for i in range(3):
        clr[i] = (color_start[i] + (pos * (color_end[i] - color_start[i]))) / 255.0

    # barvu prevedu do ninecolors
    h, s, b = colorsys.rgb_to_hsv(clr[0], clr[1], clr[2])
    return colors.hsb_to_ninecolors(h * 360, s, b, False)

    # Define start and end colours and image height and width
    # colourA = [255, 0, 255]
    # colourB = [0, 255, 255]
    # h, w = 1080, 1920

    # Make output image
    # gradient = np.zeros((h, w, 3), np.uint8)

    # Fill R, G and B channels with linear gradient between two end colours
    # gradient[:, :, 0] = np.linspace(colourA[0], colourB[0], w, dtype=np.uint8)
    # gradient[:, :, 1] = np.linspace(colourA[1], colourB[1], w, dtype=np.uint8)
    # gradient[:, :, 2] = np.linspace(colourA[2], colourB[2], w, dtype=np.uint8)

    # Save result
    # Image.fromarray(gradient).save('result.png')


def get_median(ws: Worksheet, calc_range: MultiCellRange, percentile: float = 0.5):
    """Urceni medianu v dane oblasti bunek."""

    if ws is None:
        raise Exception('Neplatny list!')

    sub_mds = []

    # kdybych uz to nekdy pocital
    for cell_range in calc_range.ranges:
        if cell_range.coord in med_in_ranges:
            sub_mds.append(med_in_ranges[cell_range.coord])

    if len(sub_mds) == len(calc_range.ranges):
        sub_mds.sort()
        if len(sub_mds) % 2 == 1:
            return sub_mds[math.floor(len(sub_mds) * percentile)]

        return (sub_mds[math.floor(len(sub_mds) * percentile) - 1]
                + sub_mds[math.floor((len(sub_mds) * percentile))]) / 2.0

    # a kdyby zase ne
    for cell_range in calc_range.ranges:
        # udelam list hodnot v danem cell_range (abych se na to pak v budoucnu mohl jen odkazat)
        cell_data = []
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                if isinstance(cell.value, int) or isinstance(cell.value, float):
                    cell_data.append(cell.value)

        try:
            # ten setridim (snad python pouzije quick sort lulw)
            cell_data.sort()

            # a spocitam median dle definice a hlavne ulozim
            if len(cell_data) % 2 == 1:
                med = cell_data[math.floor(len(cell_data) * percentile)]
                med_in_ranges[cell_range.coord] = med
                sub_mds.append(med)
            else:
                med = (cell_data[math.floor(len(cell_data) * percentile) - 1]
                       + cell_data[math.floor((len(cell_data) * percentile))]) / 2.0
                med_in_ranges[cell_range.coord] = med
                sub_mds.append(med)
        except IndexError:
            med_in_ranges[cell_range.coord] = None
            return None

    # pak z techto sub-medianu udelam dalsi median, ktery je ten finalni. doufam ze neprovadim matematicky zlocin
    sub_mds.sort()
    if len(sub_mds) % 2 == 1:
        return sub_mds[math.floor(len(sub_mds) * percentile)]

    return (sub_mds[math.floor(len(sub_mds) * percentile) - 1]
            + sub_mds[math.floor((len(sub_mds) * percentile))]) / 2.0


def get_min(ws: Worksheet, calc_range: MultiCellRange):
    """Urceni minimalni hodnoty v dane oblasti bunek"""

    if ws is None:
        raise Exception('Neplatny list!')

    sub_mins = []

    # calc_range muze obsahovat vic podoblasti (excel se obcas bugne a z 1 oblasti si udela X, i kdyz je to stale 1 obl)
    # jelikoz ale nevime, kolik jich tam je a jestli je to ta puvodni oblast, musime si to overit.
    # takze se podivame, jestli uz jsem minimum pro kazdou podoblast pocital a pokud pro vsechny, vratime to jakoby
    # globalni minimum
    for cell_range in calc_range.ranges:
        if cell_range.coord in min_in_ranges:
            sub_mins.append(min_in_ranges[cell_range.coord])

    if len(sub_mins) == len(calc_range.ranges):
        return min(sub_mins)

    sub_mins.clear()

    # tady je samotny vypocet minima. opet, spocitame minimum kazde podoblasti a vracime to globalni
    for cell_range in calc_range.ranges:
        cell_data = []
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                if isinstance(cell.value, int) or isinstance(cell.value, float):
                    cell_data.append(cell.value)

        cur_min = min(cell_data)
        sub_mins.append(cur_min)

        # ulozime si podoblast a jeji minimum, aby to slo overit. kdybych si ulozil to globalni,
        # muze se nekde stat chyba
        min_in_ranges[cell_range.coord] = cur_min

    return min(sub_mins)


def get_val_percentile(ws: Worksheet, calc_range: MultiCellRange, x: float):
    """Urceni percentilu hodnoty 'x' v ramci dane oblasti bunek"""

    if ws is None:
        raise Exception('Neplatny list!')

    num_all = 0
    num_below_x = 0

    # pocital jsem to uz nekdy?
    for cell_range in calc_range.ranges:
        if cell_range.coord in value_counters_in_ranges:
            cnt, all_in_cell_range = value_counters_in_ranges[cell_range.coord]
            num_all += all_in_cell_range
            num_below_x += sum([c for i, c in cnt.items() if i is not None and i < x])

    if num_all > 0:
        return num_below_x / num_all

    # vypocet (pokud ho delam poprve)
    # vytvorim list vsech hodnot
    cell_list = []
    for cell_range in calc_range.ranges:
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                cell_list.append(cell.value)

        # spocitam kolikrat tam ktera hodnota je a ulozim (at to nepocitam 69420x)
        cnt = Counter(cell_list)
        value_counters_in_ranges[cell_range.coord] = (cnt, len(cell_list))

    # urcim, kolik jich je mensich nez hodnota zadana (x)
    for cell_range in calc_range.ranges:
        cnt, all_in_cell_range = value_counters_in_ranges[cell_range.coord]
        num_all += all_in_cell_range
        num_below_x += sum([c for i, c in cnt.items() if i is not None and i < x])

    # kdybych nahodou delil nulou
    if num_all > 0:
        # spocitam percentil dle definice
        return num_below_x / num_all

    return 0


def get_top_y_percent(ws: Worksheet, calc_range: MultiCellRange, percentile: float):
    """Funkce vrati hodnoty s (1-x)-percentilem nebo lepsim v dane oblasti"""

    all_vals = []
    for cell_range in calc_range.ranges:
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                if type(cell.value) in [int, float]:
                    all_vals.append(cell.value)

    all_vals.sort()
    return all_vals[math.floor(percentile * len(all_vals)):]


def get_max(ws: Worksheet, calc_range: MultiCellRange):
    """Urceni ciselneho maxima v oblasti bunek"""

    range_max = float('-inf')

    sub_maxes = []
    for cell_range in calc_range.ranges:
        if cell_range.coord in max_in_ranges:
            sub_maxes.append(max_in_ranges[cell_range.coord])

    if len(sub_maxes) == len(calc_range.ranges):
        return max(sub_maxes)

    for cell_range in calc_range.ranges:
        cur_max = float('-inf')

        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                if isinstance(cell.value, (int, float)):
                    if cell.value > cur_max:
                        cur_max = cell.value

        if cur_max > range_max:
            range_max = cur_max

        max_in_ranges[cell_range.coord] = cur_max

    return range_max


def unique_vals(ws: Worksheet, calc_range: MultiCellRange):
    """[1,2,2,3,3,4,5] -> [1,2,3,4,5]"""
    if ws is None:
        raise Exception('Neplatny list!')

    cell_list = []

    sub_unqs = set()
    i = 0
    for cell_range in calc_range.ranges:
        if cell_range.coord in unique_in_ranges:
            sub_unqs.add(unique_in_ranges[cell_range.coord])
            i += 1

    if i == len(calc_range.ranges):
        return list(sub_unqs)

    # vytvorim list vsech hodnot
    for cell_range in calc_range.ranges:
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                cell_list.append(cell.value)

        # pouziju Counter (ten vcelku efektivne spocita, kolikrat je jaka hodnota v danem listu)
        cnt = Counter(cell_list)

        # vytahnu to, co ma v counteru hodnotu >= 1
        unqs = [i for i, c in cnt.items() if c >= 1]

        # ulozim at to nemusim urcovat 69420x (VYRAZNE URYCHLI PREVOD dlouhych tabulek)
        unique_in_ranges[cell_range.coord] = unqs
        sub_unqs.update(unqs)

    return list(sub_unqs)


def duplicate_vals(ws: Worksheet, calc_range: MultiCellRange):
    """[1,2,2,3,3,4,5] -> [2,3]"""

    if ws is None:
        raise Exception('Neplatny list!')

    sub_dupes = []
    all_dupes = set()
    i = 0
    for cell_range in calc_range.ranges:
        if cell_range.coord in dupl_in_ranges:
            sub_dupes.extend(dupl_in_ranges[cell_range.coord])
            i += 1

    if i == len(calc_range.ranges):
        if i == 1:
            return sub_dupes

        cnt = Counter(sub_dupes)
        return [j for j, c in cnt.items() if c > 1]

    cell_list = []

    for cell_range in calc_range.ranges:
        # vytvorim list vsech hodnot v cell_range
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                cell_list.append(cell.value)

        # pouziju Counter (ten vcelku efektivne spocita, kolikrat je jaka hodnota v danem cell_range)
        cnt = Counter(cell_list)

        # vytahnu to, co ma v counteru hodnotu > 1
        dupes = [j for j, c in cnt.items() if c > 1]

        # ulozim at to nemusim urcovat 69420x
        dupl_in_ranges[cell_range.coord] = dupes

        # pro urceni globalne duplicitnich hodnot vyuziju toho ze v set() se veci nemuzou opakovat
        all_dupes.update(dupes)

    return list(all_dupes)


def non_repeating_vals(ws: Worksheet, calc_range: MultiCellRange):
    """[1,2,2,3,3,4,5] -> [1,4,5]"""

    if ws is None:
        raise Exception('Neplatny list!')

    # kdybych uz to nekdy v minulosti urcoval. pro hodne opakujici se hodnoty se tim vypocet mirne urychli.
    sub_nrs = []
    all_nrs = set()
    i = 0
    for cell_range in calc_range.ranges:
        if cell_range.coord in dupl_in_ranges:
            sub_nrs.extend(non_rep_in_ranges[cell_range.coord])
            i += 1

    if i == len(calc_range.ranges):
        if i == 1:
            return sub_nrs

        cnt = Counter(sub_nrs)
        return [j for j, c in cnt.items() if c == 1]

    cell_list = []

    # vypocet
    for cell_range in calc_range.ranges:
        # vytvorim list vsech hodnot v cell_range
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                cell_list.append(cell.value)

        # pouziju Counter (ten vcelku efektivne spocita, kolikrat je jaka hodnota v danem cell_range)
        cnt = Counter(cell_list)

        # vytahnu to, co ma v counteru hodnotu == 1
        nrs = [j for j, c in cnt.items() if c == 1]

        # ulozim at to nemusim urcovat 69420x
        non_rep_in_ranges[cell_range.coord] = nrs

        # pro urceni globalne neopakujich se hodnot vyuziju toho ze v set() se veci nemuzou opakovat
        all_nrs.update(nrs)

    return list(all_nrs)


def calc_average(ws: Worksheet, calc_range: MultiCellRange):
    """Vypocet aritmetickeho prumeru v dane oblasti"""

    if ws is None:
        raise Exception('Neplatny list!')

    sm = 0
    sm_count = 0
    sub_avgs = []
    i = 0

    for cell_range in calc_range.ranges:
        if cell_range.coord in calculated_avgs:
            sub_avgs.extend(calculated_avgs[cell_range.coord])
            i += 1

    if i == len(calc_range.ranges):
        if i == 1:
            return sub_avgs

        return sum(sub_avgs) / len(sub_avgs)

    for cell_range in calc_range.ranges:
        for row in ws.iter_rows(cell_range.min_row, cell_range.max_row, cell_range.min_col, cell_range.max_col):
            for cell in row:
                if isinstance(cell.value, (int, float)):
                    sm += cell.value
                    sm_count += 1

        calculated_avgs[cell_range.coord] = sm / sm_count

    return sm / sm_count


def get_formatting(wb: Workbook, ws: Worksheet, cell: Cell, is_merged: EMergedCell, do_border: bool = False):
    """Funkce zjistuje, pod ktera podminema formatovani spada dana bunka a pro tato formatovani vyhodnoti zda se maji
    nebo nemaji pro bunku pouzit. Pokud ano, vrati bunku typu CCell ve ktere uz jsou tyto formaty spravne nastaveny."""

    if ws is None:
        raise Exception('Neplatny list!')

    if ws.conditional_formatting is None:
        return None

    if not ws.conditional_formatting._cf_rules:
        return None

    if not settings.conv.condi.enable:
        return None

    # zjistim zda bunka je soucasti nejakeho pravidla pro podminene formatovani
    all_rules = []
    for c in ws.conditional_formatting._cf_rules:
        for cell_range in c.cells.ranges:
            if cell.coordinate in cell_range:
                all_rules.append(c)

    if all_rules is None:
        return None

    # a pokud ano, tak musim zjistit, jak ji to ovlivni. tedy zjistit, zda pro bunku podminka plati. jedna bunka
    # muze byt soucasti mnoha podminek.
    # pravidla jsou v all_rules automaticky razena podle priority, tzn. ze pokud najdu shodu na vice mistech, prednost
    # dostane ta pozdejsi shoda, coz je v poradku
    style = None
    my_cell = CCell(cell.coordinate)

    # sloucene bunky potrebuji extra zachazeni. openpyxl povazuje vsechny krome prvni sloucene za prazdne a bez formatu,
    # takze format techto bunek musim zjistit z te prvni bunky
    merged_cell = utils.is_merged(cell, ws)
    if merged_cell is not None:
        cell = ws.cell(merged_cell.min_row, merged_cell.min_col)

    is_colorscale = False
    colorscale_priority = -1
    styles = {}
    borders = {}

    for rule in all_rules:
        for cf in rule.rules:
            # s textem nemuzu porovnavat cislo (prumer, vetsi, mensi, atd.)
            if not isinstance(cell.value, (int, float)) \
                    and cf.type not in ['containsText', 'duplicateValues', 'uniqueValues', 'equals']:
                continue

            if cf.type == 'cellIs':
                if cf.operator == 'equal':
                    # v cf.formula je vse str, takze si to musim prevest a zkontrolvat 3 hlavni pripady
                    if cell.value == float(cf.formula[0]):
                        styles[cf.priority] = cf.dxf
                    elif cell.value == int(cf.formula[0]):
                        styles[cf.priority] = cf.dxf
                    elif cell.value == cf.formula[0]:
                        styles[cf.priority] = cf.dxf
                elif cf.operator == 'lessThan':
                    if cell.value < float(cf.formula[0]):
                        styles[cf.priority] = cf.dxf
                elif cf.operator == 'greaterThan':
                    if cell.value > float(cf.formula[0]):
                        styles[cf.priority] = cf.dxf
                elif cf.operator == 'between':
                    if float(cf.formula[0]) <= cell.value <= float(cf.formula[1]):
                        styles[cf.priority] = cf.dxf
            elif cf.type == 'top10':
                top10 = get_top_y_percent(ws, rule.cells, cf.rank / 100.0)
                if cell.value in top10:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'aboveAverage':
                avg = calc_average(ws, rule.cells)
                if cell.value > avg:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'equalAverage':
                avg = calc_average(ws, rule.cells)
                if cell.value == avg:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'containsText':
                if re.search(cf.text, str(cell.value)) is not None:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'duplicateValues':
                dup = duplicate_vals(ws, rule.cells)
                if cell.value in dup:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'uniqueValues':
                unq = non_repeating_vals(ws, rule.cells)
                if cell.value in unq:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'colorScale' and settings.conv.condi.colorscale:
                clrs = [colors.get_rgb_from_color_obj(wb, x) for x in cf.colorScale.color]
                if cf.colorScale.cfvo[1].type == 'percentile':
                    percentile = cf.colorScale.cfvo[1].val / 100.0
                    cell_clr = calc_colorscale3(ws, rule.cells, cell.value, clrs, percentile)
                else:
                    cell_clr = calc_colorscale2(ws, rule.cells, cell.value, clrs)

                if cell_clr != EColor.NOT_SET:
                    my_cell.dxf.bgcolor = cell_clr

                is_colorscale = True
                colorscale_priority = cf.priority
            elif cf.type == 'dataBar':
                pass
            elif cf.type == 'notContainsBlanks':
                if cell.value is not None:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'containsBlanks':
                if cell.value is None or re.search(r'^\s+$', str(cell.value)) is not None:
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'containsErrors':
                if cell.data_type == 'e':
                    styles[cf.priority] = cf.dxf
            elif cf.type == 'notContainsErrors':
                if cell.data_type != 'e':
                    styles[cf.priority] = cf.dxf

    # pak musim to formatovani nastavit v latexu, velice podobne jako v get_cell_properties. akorat ze tady tech
    # moznosti neni tolik.

    # okraje
    # uzivatel si muze podminene formatovat treba 6546 okraju v jednom souboru. ktery z nich se zobrazi zalezi
    # na priorite podmineneho formatovani, ktere tento okraj zpusobi (mensi cislo == nizsi priorita).
    # nejlepsi na tom je, ze okraj kazde strany 1 bunky muze mit jinou barvicku, styl, i prioritu, totez pro bunku pod.
    if do_border and settings.conv.condi.borders:
        for priority, style in styles.items():
            if hasattr(style, 'border') and style.border is not None:
                borders[priority] = style.border

        for priority in borders:
            if borders[priority].bottom.style is not None:
                if priority > my_cell.dxf.border_priorities[0]:
                    my_cell.dxf.border_styles[0] = EBorderStyle[borders[priority].bottom.style.upper()]
                    my_cell.dxf.borders |= EBorder.BOTTOM
                    my_cell.dxf.border_colors[0] = colors.get_cell_color_nine(wb, borders[priority].bottom.color)
                    my_cell.dxf.border_priorities[0] = priority
            if borders[priority].top.style is not None:
                if priority > my_cell.dxf.border_priorities[2]:
                    my_cell.dxf.border_styles[2] = EBorderStyle[borders[priority].top.style.upper()]
                    my_cell.dxf.borders |= EBorder.TOP
                    my_cell.dxf.border_colors[2] = colors.get_cell_color_nine(wb, borders[priority].top.color)
                    my_cell.dxf.border_priorities[2] = priority
            if my_cell.dxf.borders & EBorder.NONE:
                my_cell.dxf.borders ^= EBorder.NONE

            if priority > my_cell.dxf.border_priorities[1]:
                if borders[priority].right.style is not None:
                    my_cell.dxf.border_styles[1] = EBorderStyle[borders[priority].right.style.upper()]
                    my_cell.dxf.borders |= EBorder.RIGHT
                    my_cell.dxf.border_colors[1] = colors.get_cell_color_nine(wb, borders[priority].right.color)
                    my_cell.dxf.border_priorities[1] = priority
            if priority > my_cell.dxf.border_priorities[3]:
                if borders[priority].left.style is not None:
                    my_cell.dxf.border_styles[3] = EBorderStyle[borders[priority].left.style.upper()]
                    my_cell.dxf.borders |= EBorder.LEFT
                    my_cell.dxf.border_colors[3] = colors.get_cell_color_nine(wb, borders[priority].left.color)
                    my_cell.dxf.border_priorities[3] = priority

            if my_cell.dxf.borders & EBorder.NONE:
                my_cell.dxf.borders ^= EBorder.NONE

        if not settings.conv.border:
            my_cell.remove_borders()
        else:
            if not settings.conv.border.style:
                my_cell.dxf.border_styles = [EBorderStyle.THIN, EBorderStyle.THIN, EBorderStyle.THIN, EBorderStyle.THIN]
            if not settings.conv.color.border:
                my_cell.dxf.border_colors = [EColor.NOT_SET, EColor.NOT_SET, EColor.NOT_SET, EColor.NOT_SET]

        return my_cell

    # podminena formatovani jdou kombinovat. takze to musim projet pro vsechny, ktere pro danou bunku plati.
    # dale si musim zajistit, ze u tech podm. formatovani, ktere meni stejne atributy, zvitezi to s vyssi (ciselne
    # mensi) prioritou tim, ze si ho prvne seradim podle tech priorit. u colorScale je nutna mala vyjimka.
    styles = dict(sorted(styles.items(), reverse=True))
    for priority, style in styles.items():
        if settings.conv.color.enable:
            # barva textu
            try:
                text_ltx_color = colors.get_cell_color_nine(wb, style.font.color)
            except AttributeError:
                text_ltx_color = 0

            if text_ltx_color not in [0, EColor.BLACK, EColor.NOT_SET] and settings.conv.color.fg:
                my_cell.dxf.fgcolor = text_ltx_color

            # barva pozadi
            if not is_colorscale or priority < colorscale_priority:
                try:
                    bg_ltx_color = colors.get_cell_color_nine(wb, style.fill.bgColor)
                except AttributeError:
                    bg_ltx_color = EColor.NOT_SET

                if my_cell.dxf.bgcolor != EColor.NOT_SET and settings.conv.color.bg and bg_ltx_color == EColor.WHITE:
                    # muze se stat ze puvodne barevne pozadi bude dale prebarveno na bile
                    # v tom pripade ho chci prebarvit
                    my_cell.dxf.bgcolor = EColor.NOT_SET
                elif bg_ltx_color not in [EColor.NOT_SET, EColor.WHITE] and settings.conv.color.bg:
                    my_cell.dxf.bgcolor = bg_ltx_color

        # styl textu
        if settings.conv.text.flags and style.font is not None:
            cell_font = EFontFlags.NONE

            if style.font.b:
                cell_font = EFontFlags.BOLD

            if style.font.i:
                if cell_font & EFontFlags.NONE:
                    cell_font = EFontFlags.ITALIC
                else:
                    cell_font |= EFontFlags.ITALIC

            my_cell.dxf.font_flags = cell_font

            # podminene formatovat jde i ciselny format, nedari se mi to tedka zprovoznit :/
            # if hasattr(style, 'number_format') and is_merged != EMergedCell.MERGED_EMPTY:
            #    my_cell.disp_value = dataGet.get_disp_value_num(cell.value, style.number_format)

    return my_cell
