###############################################################################
#
# Worksheet - A class for writing Excel Worksheets.
#
# SPDX-License-Identifier: BSD-2-Clause
#
# Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org
#
import datetime
import hashlib
import os
import re
from struct import unpack
from warnings import warn

from .exceptions import UndefinedImageSize, UnsupportedImageFormat

COL_NAMES = {}

CHAR_WIDTHS = {
    " ": 3,
    "!": 5,
    '"': 6,
    "#": 7,
    "$": 7,
    "%": 11,
    "&": 10,
    "'": 3,
    "(": 5,
    ")": 5,
    "*": 7,
    "+": 7,
    ",": 4,
    "-": 5,
    ".": 4,
    "/": 6,
    "0": 7,
    "1": 7,
    "2": 7,
    "3": 7,
    "4": 7,
    "5": 7,
    "6": 7,
    "7": 7,
    "8": 7,
    "9": 7,
    ":": 4,
    ";": 4,
    "<": 7,
    "=": 7,
    ">": 7,
    "?": 7,
    "@": 13,
    "A": 9,
    "B": 8,
    "C": 8,
    "D": 9,
    "E": 7,
    "F": 7,
    "G": 9,
    "H": 9,
    "I": 4,
    "J": 5,
    "K": 8,
    "L": 6,
    "M": 12,
    "N": 10,
    "O": 10,
    "P": 8,
    "Q": 10,
    "R": 8,
    "S": 7,
    "T": 7,
    "U": 9,
    "V": 9,
    "W": 13,
    "X": 8,
    "Y": 7,
    "Z": 7,
    "[": 5,
    "\\": 6,
    "]": 5,
    "^": 7,
    "_": 7,
    "`": 4,
    "a": 7,
    "b": 8,
    "c": 6,
    "d": 8,
    "e": 8,
    "f": 5,
    "g": 7,
    "h": 8,
    "i": 4,
    "j": 4,
    "k": 7,
    "l": 4,
    "m": 12,
    "n": 8,
    "o": 8,
    "p": 8,
    "q": 8,
    "r": 5,
    "s": 6,
    "t": 5,
    "u": 8,
    "v": 7,
    "w": 11,
    "x": 7,
    "y": 7,
    "z": 6,
    "{": 5,
    "|": 7,
    "}": 5,
    "~": 7,
}

# The following is a list of Emojis used to decide if worksheet names require
# quoting since there is (currently) no native support for matching them in
# Python regular expressions. It is probably unnecessary to exclude them since
# the default quoting is safe in Excel even when unnecessary (the reverse isn't
# true). The Emoji list was generated from:
#
# https://util.unicode.org/UnicodeJsps/list-unicodeset.jsp?a=%5B%3AEmoji%3DYes%3A%5D&abb=on&esc=on&g=&i=
#
# pylint: disable-next=line-too-long
EMOJIS = "\u00A9\u00AE\u203C\u2049\u2122\u2139\u2194-\u2199\u21A9\u21AA\u231A\u231B\u2328\u23CF\u23E9-\u23F3\u23F8-\u23FA\u24C2\u25AA\u25AB\u25B6\u25C0\u25FB-\u25FE\u2600-\u2604\u260E\u2611\u2614\u2615\u2618\u261D\u2620\u2622\u2623\u2626\u262A\u262E\u262F\u2638-\u263A\u2640\u2642\u2648-\u2653\u265F\u2660\u2663\u2665\u2666\u2668\u267B\u267E\u267F\u2692-\u2697\u2699\u269B\u269C\u26A0\u26A1\u26A7\u26AA\u26AB\u26B0\u26B1\u26BD\u26BE\u26C4\u26C5\u26C8\u26CE\u26CF\u26D1\u26D3\u26D4\u26E9\u26EA\u26F0-\u26F5\u26F7-\u26FA\u26FD\u2702\u2705\u2708-\u270D\u270F\u2712\u2714\u2716\u271D\u2721\u2728\u2733\u2734\u2744\u2747\u274C\u274E\u2753-\u2755\u2757\u2763\u2764\u2795-\u2797\u27A1\u27B0\u27BF\u2934\u2935\u2B05-\u2B07\u2B1B\u2B1C\u2B50\u2B55\u3030\u303D\u3297\u3299\U0001F004\U0001F0CF\U0001F170\U0001F171\U0001F17E\U0001F17F\U0001F18E\U0001F191-\U0001F19A\U0001F1E6-\U0001F1FF\U0001F201\U0001F202\U0001F21A\U0001F22F\U0001F232-\U0001F23A\U0001F250\U0001F251\U0001F300-\U0001F321\U0001F324-\U0001F393\U0001F396\U0001F397\U0001F399-\U0001F39B\U0001F39E-\U0001F3F0\U0001F3F3-\U0001F3F5\U0001F3F7-\U0001F4FD\U0001F4FF-\U0001F53D\U0001F549-\U0001F54E\U0001F550-\U0001F567\U0001F56F\U0001F570\U0001F573-\U0001F57A\U0001F587\U0001F58A-\U0001F58D\U0001F590\U0001F595\U0001F596\U0001F5A4\U0001F5A5\U0001F5A8\U0001F5B1\U0001F5B2\U0001F5BC\U0001F5C2-\U0001F5C4\U0001F5D1-\U0001F5D3\U0001F5DC-\U0001F5DE\U0001F5E1\U0001F5E3\U0001F5E8\U0001F5EF\U0001F5F3\U0001F5FA-\U0001F64F\U0001F680-\U0001F6C5\U0001F6CB-\U0001F6D2\U0001F6D5-\U0001F6D7\U0001F6DC-\U0001F6E5\U0001F6E9\U0001F6EB\U0001F6EC\U0001F6F0\U0001F6F3-\U0001F6FC\U0001F7E0-\U0001F7EB\U0001F7F0\U0001F90C-\U0001F93A\U0001F93C-\U0001F945\U0001F947-\U0001F9FF\U0001FA70-\U0001FA7C\U0001FA80-\U0001FA88\U0001FA90-\U0001FABD\U0001FABF-\U0001FAC5\U0001FACE-\U0001FADB\U0001FAE0-\U0001FAE8\U0001FAF0-\U0001FAF8"  # noqa

# Compile performance critical regular expressions.
RE_LEADING_WHITESPACE = re.compile(r"^\s")
RE_TRAILING_WHITESPACE = re.compile(r"\s$")
RE_RANGE_PARTS = re.compile(r"(\$?)([A-Z]{1,3})(\$?)(\d+)")
RE_QUOTE_RULE1 = re.compile(rf"[^\w\.{EMOJIS}]")
RE_QUOTE_RULE2 = re.compile(rf"^[\d\.{EMOJIS}]")
RE_QUOTE_RULE3 = re.compile(r"^([A-Z]{1,3}\d+)$")
RE_QUOTE_RULE4_ROW = re.compile(r"^R(\d+)")
RE_QUOTE_RULE4_COLUMN = re.compile(r"^R?C(\d+)")


def xl_rowcol_to_cell(row, col, row_abs=False, col_abs=False):
    """
    Convert a zero indexed row and column cell reference to a A1 style string.

    Args:
       row:     The cell row.    Int.
       col:     The cell column. Int.
       row_abs: Optional flag to make the row absolute.    Bool.
       col_abs: Optional flag to make the column absolute. Bool.

    Returns:
        A1 style string.

    """
    if row < 0:
        warn(f"Row number '{row}' must be >= 0")
        return None

    if col < 0:
        warn(f"Col number '{col}' must be >= 0")
        return None

    row += 1  # Change to 1-index.
    row_abs = "$" if row_abs else ""

    col_str = xl_col_to_name(col, col_abs)

    return col_str + row_abs + str(row)


def xl_rowcol_to_cell_fast(row, col):
    """
    Optimized version of the xl_rowcol_to_cell function. Only used internally.

    Args:
       row: The cell row.    Int.
       col: The cell column. Int.

    Returns:
        A1 style string.

    """
    if col in COL_NAMES:
        col_str = COL_NAMES[col]
    else:
        col_str = xl_col_to_name(col)
        COL_NAMES[col] = col_str

    return col_str + str(row + 1)


def xl_col_to_name(col, col_abs=False):
    """
    Convert a zero indexed column cell reference to a string.

    Args:
       col:     The cell column. Int.
       col_abs: Optional flag to make the column absolute. Bool.

    Returns:
        Column style string.

    """
    col_num = col
    if col_num < 0:
        warn(f"Col number '{col_num}' must be >= 0")
        return None

    col_num += 1  # Change to 1-index.
    col_str = ""
    col_abs = "$" if col_abs else ""

    while col_num:
        # Set remainder from 1 .. 26
        remainder = col_num % 26

        if remainder == 0:
            remainder = 26

        # Convert the remainder to a character.
        col_letter = chr(ord("A") + remainder - 1)

        # Accumulate the column letters, right to left.
        col_str = col_letter + col_str

        # Get the next order of magnitude.
        col_num = int((col_num - 1) / 26)

    return col_abs + col_str


def xl_cell_to_rowcol(cell_str):
    """
    Convert a cell reference in A1 notation to a zero indexed row and column.

    Args:
       cell_str:  A1 style string.

    Returns:
        row, col: Zero indexed cell row and column indices.

    """
    if not cell_str:
        return 0, 0

    match = RE_RANGE_PARTS.match(cell_str)
    col_str = match.group(2)
    row_str = match.group(4)

    # Convert base26 column string to number.
    expn = 0
    col = 0
    for char in reversed(col_str):
        col += (ord(char) - ord("A") + 1) * (26**expn)
        expn += 1

    # Convert 1-index to zero-index
    row = int(row_str) - 1
    col -= 1

    return row, col


def xl_cell_to_rowcol_abs(cell_str):
    """
    Convert an absolute cell reference in A1 notation to a zero indexed
    row and column, with True/False values for absolute rows or columns.

    Args:
       cell_str: A1 style string.

    Returns:
        row, col, row_abs, col_abs:  Zero indexed cell row and column indices.

    """
    if not cell_str:
        return 0, 0, False, False

    match = RE_RANGE_PARTS.match(cell_str)

    col_abs = bool(match.group(1))
    col_str = match.group(2)
    row_abs = bool(match.group(3))
    row_str = match.group(4)

    # Convert base26 column string to number.
    expn = 0
    col = 0
    for char in reversed(col_str):
        col += (ord(char) - ord("A") + 1) * (26**expn)
        expn += 1

    # Convert 1-index to zero-index
    row = int(row_str) - 1
    col -= 1

    return row, col, row_abs, col_abs


def xl_range(first_row, first_col, last_row, last_col):
    """
    Convert zero indexed row and col cell references to a A1:B1 range string.

    Args:
       first_row: The first cell row.    Int.
       first_col: The first cell column. Int.
       last_row:  The last cell row.     Int.
       last_col:  The last cell column.  Int.

    Returns:
        A1:B1 style range string.

    """
    range1 = xl_rowcol_to_cell(first_row, first_col)
    range2 = xl_rowcol_to_cell(last_row, last_col)

    if range1 is None or range2 is None:
        warn("Row and column numbers must be >= 0")
        return None

    if range1 == range2:
        return range1

    return range1 + ":" + range2


def xl_range_abs(first_row, first_col, last_row, last_col):
    """
    Convert zero indexed row and col cell references to a $A$1:$B$1 absolute
    range string.

    Args:
       first_row: The first cell row.    Int.
       first_col: The first cell column. Int.
       last_row:  The last cell row.     Int.
       last_col:  The last cell column.  Int.

    Returns:
        $A$1:$B$1 style range string.

    """
    range1 = xl_rowcol_to_cell(first_row, first_col, True, True)
    range2 = xl_rowcol_to_cell(last_row, last_col, True, True)

    if range1 is None or range2 is None:
        warn("Row and column numbers must be >= 0")
        return None

    if range1 == range2:
        return range1

    return range1 + ":" + range2


def xl_range_formula(sheetname, first_row, first_col, last_row, last_col):
    """
    Convert worksheet name and zero indexed row and col cell references to
    a Sheet1!A1:B1 range formula string.

    Args:
       sheetname: The worksheet name.    String.
       first_row: The first cell row.    Int.
       first_col: The first cell column. Int.
       last_row:  The last cell row.     Int.
       last_col:  The last cell column.  Int.

    Returns:
        A1:B1 style range string.

    """
    cell_range = xl_range_abs(first_row, first_col, last_row, last_col)
    sheetname = quote_sheetname(sheetname)

    return sheetname + "!" + cell_range


def quote_sheetname(sheetname):
    """
    Sheetnames used in references should be quoted if they contain any spaces,
    special characters or if they look like a A1 or RC cell reference. The rules
    are shown inline below.

    Args:
       sheetname: The worksheet name. String.

    Returns:
        A quoted worksheet string.

    """
    uppercase_sheetname = sheetname.upper()
    requires_quoting = False
    col_max = 163_84
    row_max = 1048576

    # Don't quote sheetname if it is already quoted by the user.
    if not sheetname.startswith("'"):

        # --------------------------------------------------------------------
        # Rule 1. Sheet names that contain anything other than \w and "."
        # characters must be quoted.
        # --------------------------------------------------------------------
        if RE_QUOTE_RULE1.search(sheetname):
            requires_quoting = True

        # --------------------------------------------------------------------
        # Rule 2. Sheet names that start with a digit or "." must be quoted.
        # --------------------------------------------------------------------
        elif RE_QUOTE_RULE2.search(sheetname):
            requires_quoting = True

        # --------------------------------------------------------------------
        # Rule 3. Sheet names must not be a valid A1 style cell reference.
        # Valid means that the row and column range values must also be within
        # Excel row and column limits.
        # --------------------------------------------------------------------
        elif RE_QUOTE_RULE3.match(uppercase_sheetname):
            match = RE_QUOTE_RULE3.match(uppercase_sheetname)
            cell = match.group(1)
            (row, col) = xl_cell_to_rowcol(cell)

            if 0 <= row < row_max and 0 <= col < col_max:
                requires_quoting = True

        # --------------------------------------------------------------------
        # Rule 4. Sheet names must not *start* with a valid RC style cell
        # reference. Other characters after the valid RC reference are ignored
        # by Excel. Valid means that the row and column range values must also
        # be within Excel row and column limits.
        #
        # Note: references without trailing characters like R12345 or C12345
        # are caught by Rule 3. Negative references like R-12345 are caught by
        # Rule 1 due to the dash.
        # --------------------------------------------------------------------

        # Rule 4a. Check for sheet names that start with R1 style references.
        elif RE_QUOTE_RULE4_ROW.match(uppercase_sheetname):
            match = RE_QUOTE_RULE4_ROW.match(uppercase_sheetname)
            row = int(match.group(1))

            if 0 < row <= row_max:
                requires_quoting = True

        # Rule 4b. Check for sheet names that start with C1 or RC1 style
        elif RE_QUOTE_RULE4_COLUMN.match(uppercase_sheetname):
            match = RE_QUOTE_RULE4_COLUMN.match(uppercase_sheetname)
            col = int(match.group(1))

            if 0 < col <= col_max:
                requires_quoting = True

        # Rule 4c. Check for some single R/C references.
        elif uppercase_sheetname in ("R", "C", "RC"):
            requires_quoting = True

    if requires_quoting:
        # Double quote any single quotes.
        sheetname = sheetname.replace("'", "''")

        # Single quote the sheet name.
        sheetname = f"'{sheetname}'"

    return sheetname


def cell_autofit_width(string):
    """
    Calculate the width required to auto-fit a string in a cell.

    Args:
       string: The string to calculate the cell width for. String.

    Returns:
        The string autofit width in pixels. Returns 0 if the string is empty.

    """
    if not string or len(string) == 0:
        return 0

    # Excel adds an additional 7 pixels of padding to the cell boundary.
    return xl_pixel_width(string) + 7


def xl_pixel_width(string):
    """
    Get the pixel width of a string based on individual character widths taken
    from Excel. UTF8 characters, and other unhandled characters, are given a
    default width of 8.

    Args:
       string: The string to calculate the width for. String.

    Returns:
        The string width in pixels. Note, Excel adds an additional 7 pixels of
        padding in the cell.

    """
    length = 0
    for char in string:
        length += CHAR_WIDTHS.get(char, 8)

    return length


def _xl_color(color):
    # Used in conjunction with the XlsxWriter *color() methods to convert
    # a color name into an RGB formatted string. These colors are for
    # backward compatibility with older versions of Excel.
    named_colors = {
        "black": "#000000",
        "blue": "#0000FF",
        "brown": "#800000",
        "cyan": "#00FFFF",
        "gray": "#808080",
        "green": "#008000",
        "lime": "#00FF00",
        "magenta": "#FF00FF",
        "navy": "#000080",
        "orange": "#FF6600",
        "pink": "#FF00FF",
        "purple": "#800080",
        "red": "#FF0000",
        "silver": "#C0C0C0",
        "white": "#FFFFFF",
        "yellow": "#FFFF00",
    }

    color = named_colors.get(color, color)

    if not re.match("#[0-9a-fA-F]{6}", color):
        warn(f"Color '{color}' isn't a valid Excel color")

    # Convert the RGB color to the Excel ARGB format.
    return "FF" + color.lstrip("#").upper()


def _get_rgb_color(color):
    # Convert the user specified color to an RGB color.
    rgb_color = _xl_color(color)

    # Remove leading FF from RGB color for charts.
    rgb_color = re.sub(r"^FF", "", rgb_color)

    return rgb_color


def _get_sparkline_style(style_id):
    styles = [
        {
            "series": {"theme": "4", "tint": "-0.499984740745262"},
            "negative": {"theme": "5"},
            "markers": {"theme": "4", "tint": "-0.499984740745262"},
            "first": {"theme": "4", "tint": "0.39997558519241921"},
            "last": {"theme": "4", "tint": "0.39997558519241921"},
            "high": {"theme": "4"},
            "low": {"theme": "4"},
        },  # 0
        {
            "series": {"theme": "4", "tint": "-0.499984740745262"},
            "negative": {"theme": "5"},
            "markers": {"theme": "4", "tint": "-0.499984740745262"},
            "first": {"theme": "4", "tint": "0.39997558519241921"},
            "last": {"theme": "4", "tint": "0.39997558519241921"},
            "high": {"theme": "4"},
            "low": {"theme": "4"},
        },  # 1
        {
            "series": {"theme": "5", "tint": "-0.499984740745262"},
            "negative": {"theme": "6"},
            "markers": {"theme": "5", "tint": "-0.499984740745262"},
            "first": {"theme": "5", "tint": "0.39997558519241921"},
            "last": {"theme": "5", "tint": "0.39997558519241921"},
            "high": {"theme": "5"},
            "low": {"theme": "5"},
        },  # 2
        {
            "series": {"theme": "6", "tint": "-0.499984740745262"},
            "negative": {"theme": "7"},
            "markers": {"theme": "6", "tint": "-0.499984740745262"},
            "first": {"theme": "6", "tint": "0.39997558519241921"},
            "last": {"theme": "6", "tint": "0.39997558519241921"},
            "high": {"theme": "6"},
            "low": {"theme": "6"},
        },  # 3
        {
            "series": {"theme": "7", "tint": "-0.499984740745262"},
            "negative": {"theme": "8"},
            "markers": {"theme": "7", "tint": "-0.499984740745262"},
            "first": {"theme": "7", "tint": "0.39997558519241921"},
            "last": {"theme": "7", "tint": "0.39997558519241921"},
            "high": {"theme": "7"},
            "low": {"theme": "7"},
        },  # 4
        {
            "series": {"theme": "8", "tint": "-0.499984740745262"},
            "negative": {"theme": "9"},
            "markers": {"theme": "8", "tint": "-0.499984740745262"},
            "first": {"theme": "8", "tint": "0.39997558519241921"},
            "last": {"theme": "8", "tint": "0.39997558519241921"},
            "high": {"theme": "8"},
            "low": {"theme": "8"},
        },  # 5
        {
            "series": {"theme": "9", "tint": "-0.499984740745262"},
            "negative": {"theme": "4"},
            "markers": {"theme": "9", "tint": "-0.499984740745262"},
            "first": {"theme": "9", "tint": "0.39997558519241921"},
            "last": {"theme": "9", "tint": "0.39997558519241921"},
            "high": {"theme": "9"},
            "low": {"theme": "9"},
        },  # 6
        {
            "series": {"theme": "4", "tint": "-0.249977111117893"},
            "negative": {"theme": "5"},
            "markers": {"theme": "5", "tint": "-0.249977111117893"},
            "first": {"theme": "5", "tint": "-0.249977111117893"},
            "last": {"theme": "5", "tint": "-0.249977111117893"},
            "high": {"theme": "5", "tint": "-0.249977111117893"},
            "low": {"theme": "5", "tint": "-0.249977111117893"},
        },  # 7
        {
            "series": {"theme": "5", "tint": "-0.249977111117893"},
            "negative": {"theme": "6"},
            "markers": {"theme": "6", "tint": "-0.249977111117893"},
            "first": {"theme": "6", "tint": "-0.249977111117893"},
            "last": {"theme": "6", "tint": "-0.249977111117893"},
            "high": {"theme": "6", "tint": "-0.249977111117893"},
            "low": {"theme": "6", "tint": "-0.249977111117893"},
        },  # 8
        {
            "series": {"theme": "6", "tint": "-0.249977111117893"},
            "negative": {"theme": "7"},
            "markers": {"theme": "7", "tint": "-0.249977111117893"},
            "first": {"theme": "7", "tint": "-0.249977111117893"},
            "last": {"theme": "7", "tint": "-0.249977111117893"},
            "high": {"theme": "7", "tint": "-0.249977111117893"},
            "low": {"theme": "7", "tint": "-0.249977111117893"},
        },  # 9
        {
            "series": {"theme": "7", "tint": "-0.249977111117893"},
            "negative": {"theme": "8"},
            "markers": {"theme": "8", "tint": "-0.249977111117893"},
            "first": {"theme": "8", "tint": "-0.249977111117893"},
            "last": {"theme": "8", "tint": "-0.249977111117893"},
            "high": {"theme": "8", "tint": "-0.249977111117893"},
            "low": {"theme": "8", "tint": "-0.249977111117893"},
        },  # 10
        {
            "series": {"theme": "8", "tint": "-0.249977111117893"},
            "negative": {"theme": "9"},
            "markers": {"theme": "9", "tint": "-0.249977111117893"},
            "first": {"theme": "9", "tint": "-0.249977111117893"},
            "last": {"theme": "9", "tint": "-0.249977111117893"},
            "high": {"theme": "9", "tint": "-0.249977111117893"},
            "low": {"theme": "9", "tint": "-0.249977111117893"},
        },  # 11
        {
            "series": {"theme": "9", "tint": "-0.249977111117893"},
            "negative": {"theme": "4"},
            "markers": {"theme": "4", "tint": "-0.249977111117893"},
            "first": {"theme": "4", "tint": "-0.249977111117893"},
            "last": {"theme": "4", "tint": "-0.249977111117893"},
            "high": {"theme": "4", "tint": "-0.249977111117893"},
            "low": {"theme": "4", "tint": "-0.249977111117893"},
        },  # 12
        {
            "series": {"theme": "4"},
            "negative": {"theme": "5"},
            "markers": {"theme": "4", "tint": "-0.249977111117893"},
            "first": {"theme": "4", "tint": "-0.249977111117893"},
            "last": {"theme": "4", "tint": "-0.249977111117893"},
            "high": {"theme": "4", "tint": "-0.249977111117893"},
            "low": {"theme": "4", "tint": "-0.249977111117893"},
        },  # 13
        {
            "series": {"theme": "5"},
            "negative": {"theme": "6"},
            "markers": {"theme": "5", "tint": "-0.249977111117893"},
            "first": {"theme": "5", "tint": "-0.249977111117893"},
            "last": {"theme": "5", "tint": "-0.249977111117893"},
            "high": {"theme": "5", "tint": "-0.249977111117893"},
            "low": {"theme": "5", "tint": "-0.249977111117893"},
        },  # 14
        {
            "series": {"theme": "6"},
            "negative": {"theme": "7"},
            "markers": {"theme": "6", "tint": "-0.249977111117893"},
            "first": {"theme": "6", "tint": "-0.249977111117893"},
            "last": {"theme": "6", "tint": "-0.249977111117893"},
            "high": {"theme": "6", "tint": "-0.249977111117893"},
            "low": {"theme": "6", "tint": "-0.249977111117893"},
        },  # 15
        {
            "series": {"theme": "7"},
            "negative": {"theme": "8"},
            "markers": {"theme": "7", "tint": "-0.249977111117893"},
            "first": {"theme": "7", "tint": "-0.249977111117893"},
            "last": {"theme": "7", "tint": "-0.249977111117893"},
            "high": {"theme": "7", "tint": "-0.249977111117893"},
            "low": {"theme": "7", "tint": "-0.249977111117893"},
        },  # 16
        {
            "series": {"theme": "8"},
            "negative": {"theme": "9"},
            "markers": {"theme": "8", "tint": "-0.249977111117893"},
            "first": {"theme": "8", "tint": "-0.249977111117893"},
            "last": {"theme": "8", "tint": "-0.249977111117893"},
            "high": {"theme": "8", "tint": "-0.249977111117893"},
            "low": {"theme": "8", "tint": "-0.249977111117893"},
        },  # 17
        {
            "series": {"theme": "9"},
            "negative": {"theme": "4"},
            "markers": {"theme": "9", "tint": "-0.249977111117893"},
            "first": {"theme": "9", "tint": "-0.249977111117893"},
            "last": {"theme": "9", "tint": "-0.249977111117893"},
            "high": {"theme": "9", "tint": "-0.249977111117893"},
            "low": {"theme": "9", "tint": "-0.249977111117893"},
        },  # 18
        {
            "series": {"theme": "4", "tint": "0.39997558519241921"},
            "negative": {"theme": "0", "tint": "-0.499984740745262"},
            "markers": {"theme": "4", "tint": "0.79998168889431442"},
            "first": {"theme": "4", "tint": "-0.249977111117893"},
            "last": {"theme": "4", "tint": "-0.249977111117893"},
            "high": {"theme": "4", "tint": "-0.499984740745262"},
            "low": {"theme": "4", "tint": "-0.499984740745262"},
        },  # 19
        {
            "series": {"theme": "5", "tint": "0.39997558519241921"},
            "negative": {"theme": "0", "tint": "-0.499984740745262"},
            "markers": {"theme": "5", "tint": "0.79998168889431442"},
            "first": {"theme": "5", "tint": "-0.249977111117893"},
            "last": {"theme": "5", "tint": "-0.249977111117893"},
            "high": {"theme": "5", "tint": "-0.499984740745262"},
            "low": {"theme": "5", "tint": "-0.499984740745262"},
        },  # 20
        {
            "series": {"theme": "6", "tint": "0.39997558519241921"},
            "negative": {"theme": "0", "tint": "-0.499984740745262"},
            "markers": {"theme": "6", "tint": "0.79998168889431442"},
            "first": {"theme": "6", "tint": "-0.249977111117893"},
            "last": {"theme": "6", "tint": "-0.249977111117893"},
            "high": {"theme": "6", "tint": "-0.499984740745262"},
            "low": {"theme": "6", "tint": "-0.499984740745262"},
        },  # 21
        {
            "series": {"theme": "7", "tint": "0.39997558519241921"},
            "negative": {"theme": "0", "tint": "-0.499984740745262"},
            "markers": {"theme": "7", "tint": "0.79998168889431442"},
            "first": {"theme": "7", "tint": "-0.249977111117893"},
            "last": {"theme": "7", "tint": "-0.249977111117893"},
            "high": {"theme": "7", "tint": "-0.499984740745262"},
            "low": {"theme": "7", "tint": "-0.499984740745262"},
        },  # 22
        {
            "series": {"theme": "8", "tint": "0.39997558519241921"},
            "negative": {"theme": "0", "tint": "-0.499984740745262"},
            "markers": {"theme": "8", "tint": "0.79998168889431442"},
            "first": {"theme": "8", "tint": "-0.249977111117893"},
            "last": {"theme": "8", "tint": "-0.249977111117893"},
            "high": {"theme": "8", "tint": "-0.499984740745262"},
            "low": {"theme": "8", "tint": "-0.499984740745262"},
        },  # 23
        {
            "series": {"theme": "9", "tint": "0.39997558519241921"},
            "negative": {"theme": "0", "tint": "-0.499984740745262"},
            "markers": {"theme": "9", "tint": "0.79998168889431442"},
            "first": {"theme": "9", "tint": "-0.249977111117893"},
            "last": {"theme": "9", "tint": "-0.249977111117893"},
            "high": {"theme": "9", "tint": "-0.499984740745262"},
            "low": {"theme": "9", "tint": "-0.499984740745262"},
        },  # 24
        {
            "series": {"theme": "1", "tint": "0.499984740745262"},
            "negative": {"theme": "1", "tint": "0.249977111117893"},
            "markers": {"theme": "1", "tint": "0.249977111117893"},
            "first": {"theme": "1", "tint": "0.249977111117893"},
            "last": {"theme": "1", "tint": "0.249977111117893"},
            "high": {"theme": "1", "tint": "0.249977111117893"},
            "low": {"theme": "1", "tint": "0.249977111117893"},
        },  # 25
        {
            "series": {"theme": "1", "tint": "0.34998626667073579"},
            "negative": {"theme": "0", "tint": "-0.249977111117893"},
            "markers": {"theme": "0", "tint": "-0.249977111117893"},
            "first": {"theme": "0", "tint": "-0.249977111117893"},
            "last": {"theme": "0", "tint": "-0.249977111117893"},
            "high": {"theme": "0", "tint": "-0.249977111117893"},
            "low": {"theme": "0", "tint": "-0.249977111117893"},
        },  # 26
        {
            "series": {"rgb": "FF323232"},
            "negative": {"rgb": "FFD00000"},
            "markers": {"rgb": "FFD00000"},
            "first": {"rgb": "FFD00000"},
            "last": {"rgb": "FFD00000"},
            "high": {"rgb": "FFD00000"},
            "low": {"rgb": "FFD00000"},
        },  # 27
        {
            "series": {"rgb": "FF000000"},
            "negative": {"rgb": "FF0070C0"},
            "markers": {"rgb": "FF0070C0"},
            "first": {"rgb": "FF0070C0"},
            "last": {"rgb": "FF0070C0"},
            "high": {"rgb": "FF0070C0"},
            "low": {"rgb": "FF0070C0"},
        },  # 28
        {
            "series": {"rgb": "FF376092"},
            "negative": {"rgb": "FFD00000"},
            "markers": {"rgb": "FFD00000"},
            "first": {"rgb": "FFD00000"},
            "last": {"rgb": "FFD00000"},
            "high": {"rgb": "FFD00000"},
            "low": {"rgb": "FFD00000"},
        },  # 29
        {
            "series": {"rgb": "FF0070C0"},
            "negative": {"rgb": "FF000000"},
            "markers": {"rgb": "FF000000"},
            "first": {"rgb": "FF000000"},
            "last": {"rgb": "FF000000"},
            "high": {"rgb": "FF000000"},
            "low": {"rgb": "FF000000"},
        },  # 30
        {
            "series": {"rgb": "FF5F5F5F"},
            "negative": {"rgb": "FFFFB620"},
            "markers": {"rgb": "FFD70077"},
            "first": {"rgb": "FF5687C2"},
            "last": {"rgb": "FF359CEB"},
            "high": {"rgb": "FF56BE79"},
            "low": {"rgb": "FFFF5055"},
        },  # 31
        {
            "series": {"rgb": "FF5687C2"},
            "negative": {"rgb": "FFFFB620"},
            "markers": {"rgb": "FFD70077"},
            "first": {"rgb": "FF777777"},
            "last": {"rgb": "FF359CEB"},
            "high": {"rgb": "FF56BE79"},
            "low": {"rgb": "FFFF5055"},
        },  # 32
        {
            "series": {"rgb": "FFC6EFCE"},
            "negative": {"rgb": "FFFFC7CE"},
            "markers": {"rgb": "FF8CADD6"},
            "first": {"rgb": "FFFFDC47"},
            "last": {"rgb": "FFFFEB9C"},
            "high": {"rgb": "FF60D276"},
            "low": {"rgb": "FFFF5367"},
        },  # 33
        {
            "series": {"rgb": "FF00B050"},
            "negative": {"rgb": "FFFF0000"},
            "markers": {"rgb": "FF0070C0"},
            "first": {"rgb": "FFFFC000"},
            "last": {"rgb": "FFFFC000"},
            "high": {"rgb": "FF00B050"},
            "low": {"rgb": "FFFF0000"},
        },  # 34
        {
            "series": {"theme": "3"},
            "negative": {"theme": "9"},
            "markers": {"theme": "8"},
            "first": {"theme": "4"},
            "last": {"theme": "5"},
            "high": {"theme": "6"},
            "low": {"theme": "7"},
        },  # 35
        {
            "series": {"theme": "1"},
            "negative": {"theme": "9"},
            "markers": {"theme": "8"},
            "first": {"theme": "4"},
            "last": {"theme": "5"},
            "high": {"theme": "6"},
            "low": {"theme": "7"},
        },  # 36
    ]

    return styles[style_id]


def _supported_datetime(dt):
    # Determine is an argument is a supported datetime object.
    return isinstance(
        dt, (datetime.datetime, datetime.date, datetime.time, datetime.timedelta)
    )


def _remove_datetime_timezone(dt_obj, remove_timezone):
    # Excel doesn't support timezones in datetimes/times so we remove the
    # tzinfo from the object if the user has specified that option in the
    # constructor.
    if remove_timezone:
        dt_obj = dt_obj.replace(tzinfo=None)
    else:
        if dt_obj.tzinfo:
            raise TypeError(
                "Excel doesn't support timezones in datetimes. "
                "Set the tzinfo in the datetime/time object to None or "
                "use the 'remove_timezone' Workbook() option"
            )

    return dt_obj


def _datetime_to_excel_datetime(dt_obj, date_1904, remove_timezone):
    # Convert a datetime object to an Excel serial date and time. The integer
    # part of the number stores the number of days since the epoch and the
    # fractional part stores the percentage of the day.
    date_type = dt_obj
    is_timedelta = False

    if date_1904:
        # Excel for Mac date epoch.
        epoch = datetime.datetime(1904, 1, 1)
    else:
        # Default Excel epoch.
        epoch = datetime.datetime(1899, 12, 31)

    # We handle datetime .datetime, .date and .time objects but convert
    # them to datetime.datetime objects and process them in the same way.
    if isinstance(dt_obj, datetime.datetime):
        dt_obj = _remove_datetime_timezone(dt_obj, remove_timezone)
        delta = dt_obj - epoch
    elif isinstance(dt_obj, datetime.date):
        dt_obj = datetime.datetime.fromordinal(dt_obj.toordinal())
        delta = dt_obj - epoch
    elif isinstance(dt_obj, datetime.time):
        dt_obj = datetime.datetime.combine(epoch, dt_obj)
        dt_obj = _remove_datetime_timezone(dt_obj, remove_timezone)
        delta = dt_obj - epoch
    elif isinstance(dt_obj, datetime.timedelta):
        is_timedelta = True
        delta = dt_obj
    else:
        raise TypeError("Unknown or unsupported datetime type")

    # Convert a Python datetime.datetime value to an Excel date number.
    excel_time = delta.days + (
        float(delta.seconds) + float(delta.microseconds) / 1e6
    ) / (60 * 60 * 24)

    # The following is a workaround for the fact that in Excel a time only
    # value is represented as 1899-12-31+time whereas in datetime.datetime()
    # it is 1900-1-1+time so we need to subtract the 1 day difference.
    if isinstance(date_type, datetime.datetime) and dt_obj.isocalendar() == (
        1900,
        1,
        1,
    ):
        excel_time -= 1

    # Account for Excel erroneously treating 1900 as a leap year.
    if not date_1904 and not is_timedelta and excel_time > 59:
        excel_time += 1

    return excel_time


def _preserve_whitespace(string):
    # Check if a string has leading or trailing whitespace that requires a
    # "preserve" attribute.
    return RE_LEADING_WHITESPACE.search(string) or RE_TRAILING_WHITESPACE.search(string)


def _get_image_properties(filename, image_data):
    # Extract dimension information from the image file.
    height = 0
    width = 0
    x_dpi = 96
    y_dpi = 96

    if not image_data:
        # Open the image file and read in the data.
        with open(filename, "rb") as fh:
            data = fh.read()
    else:
        # Read the image data from the user supplied byte stream.
        data = image_data.getvalue()

    digest = hashlib.sha256(data).hexdigest()

    # Get the image filename without the path.
    image_name = os.path.basename(filename)

    # Look for some common image file markers.
    marker1 = unpack("3s", data[1:4])[0]
    marker2 = unpack(">H", data[:2])[0]
    marker3 = unpack("2s", data[:2])[0]
    marker4 = unpack("<L", data[:4])[0]
    marker5 = (unpack("4s", data[40:44]))[0]
    marker6 = unpack("4s", data[:4])[0]

    png_marker = b"PNG"
    bmp_marker = b"BM"
    emf_marker = b" EMF"
    gif_marker = b"GIF8"

    if marker1 == png_marker:
        (image_type, width, height, x_dpi, y_dpi) = _process_png(data)

    elif marker2 == 0xFFD8:
        (image_type, width, height, x_dpi, y_dpi) = _process_jpg(data)

    elif marker3 == bmp_marker:
        (image_type, width, height) = _process_bmp(data)

    elif marker4 == 0x9AC6CDD7:
        (image_type, width, height, x_dpi, y_dpi) = _process_wmf(data)

    elif marker4 == 1 and marker5 == emf_marker:
        (image_type, width, height, x_dpi, y_dpi) = _process_emf(data)

    elif marker6 == gif_marker:
        (image_type, width, height, x_dpi, y_dpi) = _process_gif(data)

    else:
        raise UnsupportedImageFormat(
            f"{filename}: Unknown or unsupported image file format."
        )

    # Check that we found the required data.
    if not height or not width:
        raise UndefinedImageSize(f"{filename}: no size data found in image file.")

    if not image_data:
        fh.close()

    # Set a default dpi for images with 0 dpi.
    if x_dpi == 0:
        x_dpi = 96
    if y_dpi == 0:
        y_dpi = 96

    return image_type, width, height, image_name, x_dpi, y_dpi, digest


def _process_png(data):
    # Extract width and height information from a PNG file.
    offset = 8
    data_length = len(data)
    end_marker = False
    width = 0
    height = 0
    x_dpi = 96
    y_dpi = 96

    # Search through the image data to read the height and width in the
    # IHDR element. Also read the DPI in the pHYs element.
    while not end_marker and offset < data_length:
        length = unpack(">I", data[offset + 0 : offset + 4])[0]
        marker = unpack("4s", data[offset + 4 : offset + 8])[0]

        # Read the image dimensions.
        if marker == b"IHDR":
            width = unpack(">I", data[offset + 8 : offset + 12])[0]
            height = unpack(">I", data[offset + 12 : offset + 16])[0]

        # Read the image DPI.
        if marker == b"pHYs":
            x_density = unpack(">I", data[offset + 8 : offset + 12])[0]
            y_density = unpack(">I", data[offset + 12 : offset + 16])[0]
            units = unpack("b", data[offset + 16 : offset + 17])[0]

            if units == 1 and x_density > 0 and y_density > 0:
                x_dpi = x_density * 0.0254
                y_dpi = y_density * 0.0254

        if marker == b"IEND":
            end_marker = True
            continue

        offset = offset + length + 12

    return "png", width, height, x_dpi, y_dpi


def _process_jpg(data):
    # Extract width and height information from a JPEG file.
    offset = 2
    data_length = len(data)
    end_marker = False
    width = 0
    height = 0
    x_dpi = 96
    y_dpi = 96

    # Search through the image data to read the JPEG markers.
    while not end_marker and offset < data_length:
        marker = unpack(">H", data[offset + 0 : offset + 2])[0]
        length = unpack(">H", data[offset + 2 : offset + 4])[0]

        # Read the height and width in the 0xFFCn elements (except C4, C8
        # and CC which aren't SOF markers).
        if (
            (marker & 0xFFF0) == 0xFFC0
            and marker != 0xFFC4
            and marker != 0xFFC8
            and marker != 0xFFCC
        ):
            height = unpack(">H", data[offset + 5 : offset + 7])[0]
            width = unpack(">H", data[offset + 7 : offset + 9])[0]

        # Read the DPI in the 0xFFE0 element.
        if marker == 0xFFE0:
            units = unpack("b", data[offset + 11 : offset + 12])[0]
            x_density = unpack(">H", data[offset + 12 : offset + 14])[0]
            y_density = unpack(">H", data[offset + 14 : offset + 16])[0]

            if units == 1:
                x_dpi = x_density
                y_dpi = y_density

            if units == 2:
                x_dpi = x_density * 2.54
                y_dpi = y_density * 2.54

            # Workaround for incorrect dpi.
            if x_dpi == 1:
                x_dpi = 96
            if y_dpi == 1:
                y_dpi = 96

        if marker == 0xFFDA:
            end_marker = True
            continue

        offset = offset + length + 2

    return "jpeg", width, height, x_dpi, y_dpi


def _process_gif(data):
    # Extract width and height information from a GIF file.
    x_dpi = 96
    y_dpi = 96

    width = unpack("<h", data[6:8])[0]
    height = unpack("<h", data[8:10])[0]

    return "gif", width, height, x_dpi, y_dpi


def _process_bmp(data):
    # Extract width and height information from a BMP file.
    width = unpack("<L", data[18:22])[0]
    height = unpack("<L", data[22:26])[0]
    return "bmp", width, height


def _process_wmf(data):
    # Extract width and height information from a WMF file.
    x_dpi = 96
    y_dpi = 96

    # Read the bounding box, measured in logical units.
    x1 = unpack("<h", data[6:8])[0]
    y1 = unpack("<h", data[8:10])[0]
    x2 = unpack("<h", data[10:12])[0]
    y2 = unpack("<h", data[12:14])[0]

    # Read the number of logical units per inch. Used to scale the image.
    inch = unpack("<H", data[14:16])[0]

    # Convert to rendered height and width.
    width = float((x2 - x1) * x_dpi) / inch
    height = float((y2 - y1) * y_dpi) / inch

    return "wmf", width, height, x_dpi, y_dpi


def _process_emf(data):
    # Extract width and height information from a EMF file.

    # Read the bounding box, measured in logical units.
    bound_x1 = unpack("<l", data[8:12])[0]
    bound_y1 = unpack("<l", data[12:16])[0]
    bound_x2 = unpack("<l", data[16:20])[0]
    bound_y2 = unpack("<l", data[20:24])[0]

    # Convert the bounds to width and height.
    width = bound_x2 - bound_x1
    height = bound_y2 - bound_y1

    # Read the rectangular frame in units of 0.01mm.
    frame_x1 = unpack("<l", data[24:28])[0]
    frame_y1 = unpack("<l", data[28:32])[0]
    frame_x2 = unpack("<l", data[32:36])[0]
    frame_y2 = unpack("<l", data[36:40])[0]

    # Convert the frame bounds to mm width and height.
    width_mm = 0.01 * (frame_x2 - frame_x1)
    height_mm = 0.01 * (frame_y2 - frame_y1)

    # Get the dpi based on the logical size.
    x_dpi = width * 25.4 / width_mm
    y_dpi = height * 25.4 / height_mm

    # This is to match Excel's calculation. It is probably to account for
    # the fact that the bounding box is inclusive-inclusive. Or a bug.
    width += 1
    height += 1

    return "emf", width, height, x_dpi, y_dpi