import os
import re
import xlrd as xl

# if os.name == 'nt':
#     import win32com.client
#     import win32gui
#     import win32con


class SumTableTool:
    def __init__(self, wb_path):
        self.path = wb_path

        if not os.path.exists(self.path):
            return None

        self.excel = None
        self.wb = None
        self.ws = None

        self.prev = []
        self.values = []

        self.asc_pattern = re.compile(r'^\d{8}@\d+\.asc$')

        self.asc_filter = {'from': None, 'to': None}
        self.comment_filter = ''

        self.load_workbook()

    def load_workbook(self):
        try:
            self.excel = xl.open_workbook(filename=self.path)
        except Exception:
            print('error on open_workbook // path: {}'.format(self.path))
            return None

        try:
            self.wb = self.excel
        except Exception:
            return None

        try:
            if 'Sum_table' in self.wb.sheet_names():
                self.ws = self.wb.sheet_by_name('Sum_table')
            elif 'Data' in self.wb.sheet_names():
                self.ws = self.wb.sheet_by_name('Data')
            else:
                raise ValueError('No appropriate worksheet.')
        except ValueError as err:
            return None

    def reload_workbook(self):
        self.excel = None
        self.load_workbook()

    def get_workbook_name(self):
        return os.path.basename(self.path)

    def get_workbook_path(self):
        return self.path

    def get_selected_address(self):
        # return an address of Cells(last row + 1, 1) // column A
        return '$A${}'.format(self.find_last_row() + 1)
        # return self.excel.ActiveCell.Address

    def get_selected_value(self):
        last_row = self.find_last_row()
        try:
            return self.ws.cell_value(last_row + 1, 0)
        except IndexError:
            return ''
        # return self.excel.ActiveCell.Value

    def find_columns(self, labels, spreadsheet=True):
        headers = self.get_headers()
        offset = 0
        try:
            return [headers.index(l) + offset for l in labels]
        except ValueError:
            print('find_columns: Label Not Found')
            return None

    def find_last_row(self):
        return len(self.ws.col_values(0)) - 1

    def find_last_column(self):
        return len(self.get_headers()) - 1

    def get_headers(self):
        return self.ws.row_values(0, 0)

    def get_values(self):
        last_row = self.find_last_row()
        # last_col = self.find_last_column() + 1  # needs '+1' because source code uses _cell_values[rowx][start_colx:end_colx]
        self.values = [list(r) for r in self.ws._cell_values if r[0]
                       is not None and self.asc_pattern.match(r[0])]
        if self.asc_filter['from'] is not None or self.asc_filter['to'] is not None:
            self.values = self.filter_by_asc(
                self.asc_filter['from'], self.asc_filter['to'])
        elif self.comment_filter != '':
            print('comfil: ', self.comment_filter)
            self.values = self.filter_by_comment(self.comment_filter)
            print('value: ', self.values)
        return self.values

    def get_asc_list(self):
        if self.values == []:
            self.get_values()
        return [r[0] for r in self.values]

    def filter_by_comment(self, pattern):
        comm_col = self.find_columns(['Comment'])[0]
        pat = re.compile(pattern, re.I)
        if self.values == []:
            self.values = self.get_values()

        return [r for r in self.values if pat.search(r[comm_col])]

    def filter_by_asc(self, start=None, end=None):
        # start=None, end=None: All (from smallest to largest)
        # start=None, end=xxx: from smallest to xxx
        # start=xxx, end=None: from xxx to largest
        # start=xxx, end=yyy: from xxx to yyy
        if self.values == []:
            self.values = self.get_values()
        asc = [r[0] for r in self.values]
        rNum = len(self.values)
        if start is None:
            if end is None:
                return self.values
            else:
                smallest = asc[0]
                largest = end
        elif end is None:
            smallest = start
            largest = asc[rNum - 1]
        else:
            smallest = start
            largest = end

        try:
            s_idx = asc.index(smallest)
            e_idx = asc.index(largest)
            if s_idx < 0 or e_idx < 0:
                return self.values
        except ValueError:
            return self.values

        return self.values[s_idx:e_idx + 1]

    def is_modified(self, val=None):
        if val is None:
            if self.values == []:
                self.get_values()
            else:
                val = self.values
        return not self.prev == val

    def modified_rows(self):
        out = []
        self.get_values()
        if self.is_modified():
            out = [r for r in range(len(self.values)) if r >= len(
                self.prev) or not self.prev[r] == self.values[r]]

        return out


if __name__ == '__main__':
    # test_excel_file = '/Users/saburo/Desktop/20180924_test.xlsx'
    test_excel_file = '/Users/saburo/Documents/WindowsSharedFolder/20180129_d18O_Byerly_KK.xlsx'
    if not os.path.exists(test_excel_file):
        test_excel_file = 'E:/20180129_d18O_Byerly_KK.xlsx'
    exl = SumTableTool(test_excel_file)
    if exl is None:
        print('error')
        exit()
    print(exl.get_workbook_path())
    print(exl.get_workbook_name())
    print(exl.wb.sheet_names())
    print(len(exl.get_headers()))
    print(exl.find_columns(['X', 'Y']))
    # print exl.filter_by_asc(start='20180201@550.asc', end='201802925@553.asc')
    print('last row: {}, col: {}, header_len: {}'.format(
        exl.find_last_row(), exl.find_last_column(), len(exl.get_headers())))
    print(len(exl.filter_by_comment('KIM-5')))
    print('selected address:', exl.get_selected_address())
    print('selected value:', exl.get_selected_value())
    print(exl.ws.cell_value(740, 22))
    val = exl.get_values()
    print(val[490])

    # print exl.ws._cell_values
