import string
import time

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, NamedStyle, PatternFill
wb = Workbook()
ws = wb.active
ws.title = 'Access Points'

header = {'Network': 16, 'Address': 20.43, 'AP-Name': 29, 'Tags': 29,
          'IP Address': 19, 'Gateway': 19, 'DNS': 20.57, 'AP Model': 18.29,
          'Status': 18.29, 'Serial': 21.29, 'MAC': 18.57}


def stamp():
    now = time.localtime()
    now_val = ('%s%s%s%s%s%s') % (now[1], now[2], now[0], now[3], now[4],
                                  now[5])
    return now_val


def create_header(ws):
    ws.append(list(header.copy().keys()))


def cell_format(ws):
    headstyle = NamedStyle(name='headstyle')
    headstyle.font = Font(bold=True, name='Times New Roman')
    headstyle.alignment = Alignment(horizontal='center')
    headfill = PatternFill(start_color='ddd9c4', end_color='ddd9c4',
                           fill_type='solid')
    # blah = [cell.value for cell in ws[1]]
    for cell in ws[1]:
        cell.style = headstyle
        cell.fill = headfill


def set_widths(ws, header):
    for col in string.ascii_uppercase[:len(header.keys())]:
        # ws.column_dimensions['A'].width = 200
        # print(col)
        headval = ws['{}{}'.format(col, '1')].value
        widthval = (header[headval])
        ws.column_dimensions[col].width = widthval


def find_header(search_string):
    """Returns col number for searched value in first row."""
    from openpyxl.utils import column_index_from_string
    myheader = ws[1]
    for item in myheader:
        if search_string in item.value:
            print(item.value)
            alpha_col = item.column
            num_col = column_index_from_string(item.column)
    return(num_col, alpha_col)


def write_serials(serials, num_col):
    # set our first row to be after the header
    rrow = 2
    for item in serials:
        ws.cell(column=num_col, row=rrow, value=item)
        rrow = rrow + 1
        print(rrow)


myserials = ['Q2QW-5Y9P-3C3Y', 'Q2QW-5VEB-MXQW']
create_header(ws)
cell_format(ws)
set_widths(ws, header)
num_col, alpha_col = find_header('Serial')
write_serials(myserials, num_col)

# sys.exit()
fileBase = 'Meraki-API'
fileName = fileBase + '_' + stamp() + '.xlsx'
# print(fileName)
wb.save('C:\\temp\\clean-daily\\{}'.format(fileName))