from openpyxl import Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment, numbers
from openpyxl.utils.cell import coordinate_to_tuple
from openpyxl.utils import get_column_letter
import numpy as np

def tuple_to_coordinate(row, column):
    col_letter = get_column_letter(column)
    return '{}{}'.format(col_letter, row)

def set_range_style(ws, cell_range, border=Border(), fill=None, font=None, alignment=None, number_format=None, merged=True):
    """
    Apply styles to a range of cells as if they were a single cell.

    :param ws:  Excel worksheet instance
    :param range: An excel range to style (e.g. A1:F20)
    :param border: An openpyxl Border
    :param fill: An openpyxl PatternFill or GradientFill
    :param font: An openpyxl Font object
    :param alignment: An openpyxl Alignment object
    :param number_format: An openpyxl Number_format object
    :param merged: Whether merge cells

    """

    top = Border(top=border.top)
    left = Border(left=border.left)
    right = Border(right=border.right)
    bottom = Border(bottom=border.bottom)
    # default border
    inline = Side(border_style="thin", color="BFBFBF")
    in_border = Border(right=inline , bottom=inline, left=inline, top=inline)

    if merged:
        ws.merge_cells(cell_range)

    rows = ws[cell_range]

    for row in rows:
        for c in row:
            c.border = in_border
            if fill:
                c.fill = fill
            if font:
                c.font = font
            if alignment:
                c.alignment = alignment
            if number_format:
                c.number_format = number_format

    for c in rows[0]:
        new_border = Border(left=c.border.left, top=top.top, right=c.border.right, bottom=c.border.bottom)
        c.border = new_border
    for c in rows[-1]:
        new_border = Border(left=c.border.left, top=c.border.top, right=c.border.right, bottom=bottom.bottom)
        c.border = new_border
    for row in rows:
        l = row[0]
        r = row[-1]
        new_border = Border(left=left.left, top=l.border.top, right=l.border.right, bottom=l.border.bottom)
        l.border = new_border
        new_border = Border(left=r.border.left, top=r.border.top, right=right.right, bottom=r.border.bottom)
        r.border = new_border


def set_header(ws, cell_range, name=None, merged=False):
    if name:
        first_cell = ws[cell_range.split(":")[0]]
        first_cell.value = name
    thin = Side(border_style="thin", color="000000")
    border = Border(top=thin, left=thin, right=thin, bottom=thin)
    fill = PatternFill("solid", fgColor="BCD6EE")
    al = Alignment(horizontal="center", vertical="center")
    font = Font(name=u'微軟雅黑', size=11)
    set_range_style(ws, cell_range, border=border, fill=fill, font=font, alignment=al, merged=merged)

def set_title(ws, cell, title):
    ws[cell].font = Font(name=u'微軟雅黑', bold=True, size=11)
    ws[cell].value = title