import os
import sys
import re
import io
import csv
import openpyxl
try:
    from chardet.universaldetector import UniversalDetector
except:
    print("")
    print("####################################################################")
    print("####################################################################")
    print("  Error in tklib.tkexcel: module [chardet] is not installed.")
    print("####################################################################")
    print("####################################################################")
    print("")
    exit()
try:
    import msoffcrypto
except:
    '''
    print("")
    print("####################################################################")
    print("####################################################################")
    print("  Warning in tklib.tkexcel: module [msoffcrypto] is not installed.")
    print("    You cannot open encrypted Excel files protected with password")
    print("    Install by pip install msoffcrypto-tool if needed")
    print("####################################################################")
    print("####################################################################")
    print("")
    '''
    msoffcrypto = None


from tklib.tkobject import tkObject
from tklib.tkutils import mprint, IsDir, IsFile, SplitFilePath, modify_path, delete_file
from tklib.tkutils import pint, pfloat, pconv
from tklib.tkutils import split_file_path, replace_path
from tklib.tkfile import get_encoding
from tklib.tksci.tkmatrix import make_matrix1, make_matrix2
import tklib.tkre
from tklib.tkdatafile import tkDataFile


#=======================================
# Handling MS-Excel .xlsx files
#=======================================


def convert_color(color):
    color_dict = {
        "black": "000000",
        "red"  : "FF0000",
        "green": "00FF00",
        "blue" : "0000FF",
        }

    if color in color_dict.keys():
        return color_dict[color]

    return color.lstrip("#")

def convert_linestyle(linestyle):
    if linestyle == "dashed":
        return "lgDash"
    else:
        return "solid"

# point to EMU (English Metric Unit)
# 1 inch = 914400 EMU 
# 1 pt = 1/72 inch、
def convert_size(s):
    return int(12700 * s)

class tkExcel_sheet(openpyxl.worksheet.worksheet.Worksheet):
    def __init__(self, sheet = None):
        super().__init__()
        self.ws      = sheet

    def __del__(self):
        pass

    def __str__(self):
        return self.ClassPath()

class tkExcelChart():
    def __init__(self, style = 'scatter'):
        if style == 'scatter':
            self.chart = ScatterChart()
        else:
            self.chart = False

    def add_chart(self, ws, position = "A1"):
        ws.add_chart(self.chart, position)

    def set_figsize(self, width, height):
        self.chart.width = width
        self.chart.height = height

    def set_title(self, title):
        self.chart.title = title

    def set_xlabel(self, label):
        self.chart.x_axis.title = label

    def set_ylabel(self, label):
        self.chart.y_axis.title = label

    def set_legend(self, style = ''):
        if style is None:
            self.chart.legend = None

    def add_plot(self, ws, xrange, yrange, title_from_data = False, title = None,
                 width = None, color = "000000", linestyle = "-"):
        ls = convert_linestyle(linestyle)
        cl = convert_color(color)
        w  = convert_size(width)

        x_ref = Reference(ws, min_col = xrange[0], min_row = xrange[1], max_row = xrange[2])
        y_ref = Reference(ws, min_col = yrange[0], min_row = yrange[1], max_row = yrange[2])
        series = Series(y_ref, x_ref, title_from_data = title_from_data, title = title)
        series.graphicalProperties.line.width     = w
        series.graphicalProperties.line.solidFill = cl
        series.graphicalProperties.line.prstDash  = ls
        self.chart.series.append(series)

    def set_xlim(self, range):
        self.chart.x_axis.scaling.min = range[0]
        self.chart.x_axis.scaling.max = range[1]

    def set_ylim(self, range):
        self.chart.y_axis.scaling.min = range[0]
        self.chart.y_axis.scaling.max = range[1]

    def set_xgrid(self, style = None):
        self.chart.x_axis.majorGridlines = style

    def set_ygrid(self, style = None):
        self.chart.y_axis.majorGridlines = style

    def set_xticks(self, style = "none"):
        if style is None: style = "none"
        self.chart.x_axis.majorTickMark = style

    def set_xticks(self, style = "none"):
        if style is None: style = "none"
        self.chart.x_axis.minorTickMark = style

class tkExcel(tkDataFile):
    def __init__(self, path = None, mode = 'r', password = None, allow_no_password = False, tmp_file = None,
                    OpenFile = True, CloseFile = False, try_text = False, data_only = True, keep_vbas = False,
                    try_text_file = False, encoding = None, 
                    split_from = 1, IsPrint = False, **args):
        self.fp       = None
        self.path     = None
        self.mode     = None
        self.password = None
        self.tmp_file = None
        self.try_text_file = try_text_file
        self.wb       = None
        self.ws       = None
        self.cur_row  = 0
        self.cur_col  = 0
        self.labelarray    = []
        self.datalistarray = []
        self.split_from = split_from
        self.encoding = encoding
        self.password = password
        self.allow_no_password = allow_no_password
        self.update(**args)

        self.initialize(**args)

        super().__init__(path = path, mode = mode, OpenFile = False, CloseFile = False, **args)

        self.path    = self.IfYes(path is not None, path, self.path)
        self.mode    = self.IfYes(mode is not None, mode, self.mode)

        self.labelarray    = []
        self.datalistarray = []

        if OpenFile and self.path is not None:
#           self.open(self.path, self.mode, data_only)
            self.open_encrypted(path = path, mode = mode, password = password, allow_no_password = allow_no_password, 
                        tmp_file = tmp_file, 
                        try_text_file = try_text_file, data_only = data_only, keep_vbas = keep_vbas,
                        encoding = encoding, split_from = split_from, IsPrint = IsPrint)

        if CloseFile:
            self.close()

    def __del__(self):
#        self.close()
        pass

    def __str__(self):
        return self.ClassPath()

    def initialize(self, **args):
        if self.fp:
            self.close()

        if self.wb:
            self.close()
            self.wb = None

        self.fp      = None
        self.path    = None
        self.mode    = None
        self.wb      = None
        self.ws      = None
        self.cur_row = 0
        self.cur_col = 0
        self.labelarray    = []
        self.datalistarray = []

        self.update(**args)
        
    def open_text(self, path = None, mode = 'r', encoding = None, split_from = None, separator = ' ', IsPrint = True):
        if encoding is None:
            encoding = get_encoding(path, defval = None)

        self.wb = openpyxl.Workbook()
        self.fp = None
        if self.ws is None:
            print("")
            print(f"Error in tkexcel.open(): Cannot open [{path}]")
            print( "  Would be an encrypted file")
            return None

        self.ws = self.wb.active

        if split_from is None:
            split_from = self.split_from

        self.ws = self.wb.active

        fp = open(path, mode = mode, encoding = encoding)
        irow = 1
        for i in range(split_from - 1):
            line = fp.readline().strip()
            self.ws.cell(irow, 1).value = line
            irow += 1

        while 1:
            line = fp.readline()
            if not line:
                break

            row = line.split(separator)
            for icol in range(len(row)):
                self.ws.cell(irow, icol + 1).value = pconv(row[icol], row[icol])
            irow += 1

        fp.close()

        return self.wb

    def open_csv(self, path = None, mode = 'r', encoding = None, split_from = None, IsPrint = True):
        if encoding is None:
            encoding = get_encoding(path, defval = None)
        
        self.wb = openpyxl.Workbook()
        self.fp = None
        if self.wb is None:
            print("")
            print(f"Error in tkexcel.open(): Cannot open [{path}]")
            print( "  Would be an encrypted file")
            return None

        if split_from is None:
            split_from = self.split_from

        self.ws = self.wb.active

        fp = open(path, mode = mode, encoding = encoding)
        reader = csv.reader(fp)
        irow = 1
        for i in range(split_from - 1):
            line = fp.readline()
            if not line:
                break

            self.ws.cell(irow, 1).value = line.strip
            irow += 1

        for row in reader:
            for icol in range(len(row)):
                self.ws.cell(irow, icol + 1).value = pconv(row[icol], row[icol])
            irow += 1

        fp.close()

        return self.wb

    def open(self, path = None, mode = None, data_only = True, keep_vbas = False,
                    password = None, allow_no_password = False, tmp_file = "tmp.xlsx", try_text_file = False,
                    encoding = None, split_from = None, IsPrint = True):
        if password is not None and password != "":
            return self.open_encrypted(path = path, mode = mode, password = password, allow_no_password = allow_no_password, 
                        tmp_file = tmp_file, 
                        try_text_file = try_text_file, data_only = data_only, 
                        keep_vbas = keep_vbas, encoding = encoding, split_from = split_from, IsPrint = IsPrint)

        self.initialize()
        self.SetPath(path, mode)
        if split_from is None:
            split_from = self.split_from

        self.wb = None
        self.ws = None
        self.fp = None
        if path is None:
            path = self.path

        dirname, basename, filebody, ext = split_file_path(path)
        if ext.lower() == '.csv':
            return self.open_csv(path = path, mode = mode, encoding = encoding, split_from = split_from, IsPrint = IsPrint)
        elif try_text_file and ext.lower() == '.txt':
            return self.open_text(path = path, mode = mode, encoding = encoding, split_from = split_from, IsPrint = IsPrint)
        elif ext.lower() != ".xlsx" and ext.lower() != ".xlsm":
            return None

        if self.mode == 'w':
            if IsFile(path) and not delete_file(path):
                self.errormsg(sys._getframe().f_code.co_name, "Can not delete [{}]".format(self.path))
            self.wb = openpyxl.Workbook()
        elif self.mode == 'a':
#  UserWarning: Data Validation extension is not supported and will be removed がでるのでWarning抑制
            openpyxl.reader.excel.warnings.simplefilter('ignore')
            self.wb = openpyxl.load_workbook(self.path, data_only = data_only, keep_vba = keep_vba)
        else:
            if not os.path.isfile(path):
                print("")
                print(f"Error in tkexcel.open() (line233): [{path}] does not exist.")
                return None

#  UserWarning: Data Validation extension is not supported and will be removed がでるのでWarning抑制
            openpyxl.reader.excel.warnings.simplefilter('ignore')
            try:
                self.wb = openpyxl.load_workbook(self.path, data_only = data_only)
            except:
                print("")
                print(f"Error in tkexcel.open(): Cannot load workbook from [{path}].")
                print( "  Would be an encrypted file")
                return None

        self.ws = self.wb.active
        if self.ws is None:
            print("")
            print(f"Error in tkexcel.open(): Cannot open [{path}]")
            print( "  Would be an encrypted file")
            return None

#        print("s=", self.path, self.mode, self.wb, self.ws)

        return self.wb

    def Open(self, path = None, mode = None, data_only = True, 
                    password = None, allow_no_password = False, tmp_file = "tmp.xlsx", try_text_file = False,
                    keep_vbas = False, encoding = None, IsPrint = True):
        if password is not None and password != "":
            return self.open_encrypted(path = path, mode = mode, password = password, allow_no_password = allow_no_password, 
                        tmp_file = tmp_file, 
                        try_text_file = try_text_file, data_only = data_only, 
                        keep_vbas = keep_vbas, encoding = encoding, split_from = split_from, IsPrint = IsPrint)
                                
        return self.open(path = path, mode = mode, try_text_file = try_text_file, data_only = data_only, keep_vbas = keep_vbas,
                        encoding = encoding, IsPrint = IsPrint)

    def open_encrypted(self, path = None, mode = None, password = None, allow_no_password = False, 
                    tmp_file = 'tmp.xlsx', 
                    try_text_file = False, data_only = True, keep_vbas = False, encoding = None, 
                    split_from = None, IsPrint = True):
        if msoffcrypto is None:
            print("")
            print("####################################################################")
            print("  Error in tklib.tkexcel.open_encrypted(): module [msoffcrypto] is not installed.")
            print("    You cannot open encrypted Excel files protected with password")
            print("    Install by pip install msoffcrypto-tool if needed")
            print("####################################################################")

            if allow_no_password and (password is None or password == ''):
                print("Try to open without password")
                ret = self.open(path = path, mode = mode, try_text_file = try_text_file, data_only = data_only, keep_vbas = keep_vbas,
                            encoding = encoding, split_from = split_from, IsPrint = IsPrint)
                return ret
            else:
                return None

        if password is None or password == '':
            ret = self.open(path = path, mode = mode, try_text_file = try_text_file, data_only = data_only, keep_vbas = keep_vbas,
                            encoding = encoding, split_from = split_from, IsPrint = IsPrint)
            return ret


# https://news.mynavi.jp/techplus/article/zeropython-98/
        if path is None:
            path = self.path

        if IsPrint:
            print(f"パスワード付きExcelファイル {path} の読み込み")

        decrypted = io.BytesIO()
        with open(path, 'rb') as fp:
            msfile = msoffcrypto.OfficeFile(fp)
            try:
                msfile.load_key(password = password)
                msfile.decrypt(decrypted)
                print(f" In tkexcel.open_encrypted(): Succeeded in opening encrypted file [{path}]")
            except:
                print()
                if not allow_no_password:
                    print(f"Error in tkexcel.open_encrypted(): password is given but could not open [{path}]")
                    print()
                    exit()
                else:
                    print(f"Warning in tkexcel.open_encrypted(): password is given but could not open [{path}]")
                    print(f"   Try to open [{path}] without passwd.")
                    tmp_file = None
                    password = None

# 暗号解除した後ファイルに保存 --- (*3)
        if password is not None and password != "" and tmp_file is None:
            print(f"\nError in tkexcel.open_encrypted(): File is encrypted so tmp_file must be given (given None)\n")
            exit()

        if IsPrint:
            if tmp_file: 
                print(f"  {tmp_file} に保存")
                with open(tmp_file, 'wb') as fp:
                    fp.write(decrypted.getbuffer())
            else:
                tmp_file = path
        
        if IsPrint:
            print(f"  {tmp_file} を読み込み")
        self.wb = self.open(path = tmp_file, mode = mode, 
                                data_only = data_only, encoding = encoding, IsPrint = IsPrint)

        if IsPrint:
            print(f"  {tmp_file} を削除")
        os.remove(tmp_file)

        return self.wb

    def close(self):
        if self.wb is not None:
            if self.mode == 'w' or self.mode == 'a':
                self.wb.save(self.path)
#            self.wb = None

    def Close(self):
        self.close()

    def set_path(self, path = None, mode = None):
        self.path = self.IfYes(path is not None, path, self.path)
        self.mode = self.IfYes(mode is not None, mode, self.mode)

    def SetPath(self, path = None, mode = None):
        return self.set_path(path = path, mode = mode)

    def max_row(self):
        if self.ws is None:
            return None

        return self.ws.max_row

    def max_column(self):
        if self.ws is None:
            return None

        return self.ws.max_column

    def update_cell(self, cell_name, val):
        wb = self.wb
        lower = cell_name.lower()
        for name in wb.defined_names:
            if name.lower() == lower:
                dn = wb.defined_names[name]
                for sheet_name, cell_addr in dn.destinations:
                    ws = wb[sheet_name]
                    ws[cell_addr].value = val
                return True
        return False


# 1-base
    def set(self, row, column, val, ws = None):
        ws = self.get_sheet(ws)
        ws.cell(row = row, column = column).value = val

# 0-base
    def set_val(self, irow, icol, val, ws = None):
        ws = self.get_sheet(ws)
        ws.cell(row = irow + 1, column = icol + 1).value = val

# 1-base
    def get(self, row, column, def_val = None, ws = None):
        ws = self.get_sheet(ws)

        val = ws.cell(row, column).value
        if def_val is not None and val is None:
            val = def_val
        
        return val

# 0-base
    def get_val(self, irow, icol, def_val = None, ws = None):
        ws = self.get_sheet(ws)

        val = ws.cell(irow+1, icol+1).value
        if def_val is not None and val is None:
            val = def_val
        
        return val

    def Get(self, irow, icol):
        return self.get_val(irow, icol)

    def get_labels(self, ws = None, irow_origin = 1, icol_origin = 1):
        if ws is None:
            ws = self.ws

        labels = []
        for icol in range(icol_origin, self.max_column() + 1):
            val = ws.cell(row = irow_origin, column = icol).value
            labels.append(val)

        return labels

    def append_data(self, data, print_level = 0):
        labels = self.get_labels()
        ndata  = self.max_row() - 1
        for key, val in data.items():
# keyが無かったら列を追加
            if key not in labels:
                icol = self.max_column() + 1
                if print_level:
                    print(f"key [{key}] is not in the labels. Add [{key}] to icol={icol}")
                self.set(irow = 1, icol = icol, val = key)
                labels = self.get_labels()

# 最終行に値を追加
            val = data[key]
            self.set(irow = ndata + 2, icol = labels.index(key) + 1, 
                     val = pfloat(val, val))

    def get_column_letter(self, i):
        return openpyxl.utils.get_column_letter(i)

    def get_icol_from_letter(self, s):
        return openpyxl.utils.column_index_from_string(s)
        
    def get_irow_from_label(self, label, column_org = 1, row_org = 1, sheet = None):
        if sheet is None:
            sheet = self.ws

        for i in range(row_org, sheet.max_row + 1):
            s = sheet.cell(row = i, column = column_org).value
            if type(s) is int or type(label) is int:
                if pint(s) == pint(label):
                    return i
            elif type(s) is float or type(label) is float:
                if pfloat(s) == pfloat(label):
                    return i
            else:
                if s == label:
                    return i

        return None

    def get_icolumn_from_label_regex(self, label, column_org = 1, row_org = 1, sheet = None, flags = 0):
        if sheet is None:
            sheet = self.ws

        if sheet is None:
            return None

        for i in range(column_org, sheet.max_column + 1):
            s = sheet.cell(row = row_org, column = i).value
            if type(s) is int or type(label) is int:
                if pint(s) == pint(label):
                    return i
            elif type(s) is float or type(label) is float:
                if pfloat(s) == pfloat(label):
                    return i
            else:
#                print("l=", label, s)
                if s is None:
                    continue
                if re.search(label, s, flags = flags):
                    return i
                if label == s:
                    return i

        return None

    def get_icolumn_from_label1(self, label, column_org = 1, row_org = 1, sheet = None):
        if sheet is None:
            sheet = self.ws

        if sheet is None:
            return None

        for i in range(column_org, sheet.max_column + 1):
            s = sheet.cell(row = row_org, column = i).value
            if type(s) is int or type(label) is int:
                if pint(s) == pint(label):
                    return i
            elif type(s) is float or type(label) is float:
                if pfloat(s) == pfloat(label):
                    return i
            else:
                if s == label:
                    return i

        return None

    def get_icolumn_from_label(self, label, column_org = 1, row_org = 1, sheet = None):
        if type(label) is list or type(label) is tuple:
            ilist = []
            for s in label:
                val = self.get_icolumn_from_label(s, column_org = column_org, row_org = row_org, sheet = sheet)
                ilist.append(val)
            return ilist
        else:
            return self.get_icolumn_from_label1(label, column_org = column_org, row_org = row_org, sheet = sheet)

    def find_data_array(self, regexp, flags = 0, convert_type = None, def_val = None):
        if type(regexp) is int:
            idx = regexp
        else:
            try:
                idx = int(regexp)
            except:
                idx = self.get_icolumn_from_label_regex(regexp, flags = flags)

        ncolumns = self.max_column()
        if ncolumns is None:
            print(f"\nError in tkexcel.get_icolumn_from_label(): Can not get worksheet)")
            return None, None

        if ncolumns < idx:
            print(f"\nError in tkexcel.get_icolumn_from_label(): idx={idx} is not found (max_columns={ncolumns})")
            return None, None

        nrows    = self.max_row()
        labels = self.get_labels()
        data_list = []
        for irow in range(2, nrows + 1):
            v = self.get(irow, idx, def_val = def_val)
            if convert_type is float and v is not None:
                if def_val == 'input':
                    dv = v
                else:
                    dv = None
                v = pfloat(v, defval = dv)
            elif convert_type is int and v is not None:
                if def_val == 'input':
                    dv = v
                else:
                    dv = None
                v = pint(v, defval = dv)
            data_list.append(v)
        
        return labels[idx-1], data_list

    def read_data(self, idata = None, flags = '', convert_type = None, def_val = None, is_print = True):
        if idata is None:
            idata = [i + 1 for i in range(self.ws.max_column)]
        labels = []
        data_list = []
        for idx in idata:
            label, data = self.find_data_array(regexp = idx, flags = flags, convert_type = convert_type, def_val = def_val)
            if label is None or label == '':
                continue
                
            labels.append(label)
            data_list.append(data)

        return labels, data_list

    def get_row_data(self, irow, def_val = None, ws = None):
        ws = self.get_sheet(ws)
        data = []
        for icol in range(1, self.ws.max_column + 1):
            data.append(self.get(irow = irow, icol = icol, def_val = def_val, ws = ws))
        return data

    def get_col_data(self, icol, def_val = None, ws = None):
        ws = self.get_sheet(ws)
        if type(icol) is str:
#    def get_icolumn_from_label_regex(self, label, column_org = 1, row_org = 1, sheet = None, flags = 0):
            icol = self.get_icolumn_from_label_regex(icol)
#            icol = self.get_icol_from_letter(icol)

        data = []
        for irow in range(2, self.ws.max_row + 1):
            data.append(self.get(irow = irow, icol = icol))
        return data

    def get_specified_data(self, indexes = None, rows = None, convert_label = True):
        if indexes is None:
            indexes = range(self.max_column())
        if rows is None:
            rows = range(self.max_row())

        nindexes = len(indexes)
        nrows    = len(rows)
        ws = self.ws

        labels = []
        for i in indexes:
            val1 = ws.cell(row = 1, column = i).value
# シート名に使えない文字を_に置換
            if convert_label:
                if val1 is None or val1 == '':
                    val1 = f'{i}列'
                else:
                    val1 = re.sub(r'[\r\n\\/:]', '_', val1)

            labels.append(val1)

        data_list = []
        for i in range(nindexes):
            data_list.append([])
        for i in range(nindexes):
            for j in range(nrows):
                data_list[i].append(None)

        for i in range(nindexes):
            icol = indexes[i]
            for j in range(nrows):
                irow = rows[j]
#            print("i=", i, icol, irow, end = '')
                val = ws.cell(row = irow, column = icol).value
                data_list[i][j] = val
#            print(f"  {val}")

        return labels, data_list

    def get_sheet_by_name(self, title):
        return self.wb.get_sheet_by_name(title)

    def find_isheet(self, name, reg_exp = False):
        sheetnames = self.wb.sheetnames

        if type(name) is not str:
            for i in range(len(self.wb.worksheets)):
                if self.wb.worksheets[i] == self.ws:
                    return i, self.wb.sheetnames[i], self.wb.worksheets[i]
            else:
                return None, None, None

        if reg_exp:
            for i in range(len(sheetnames)):
                if re.search(name, sheetnames[i]):
                    return i, name, self.wb.worksheets[i]
        else:
            for i in range(len(sheetnames)):
                if sheetnames[i] == name:
                    return i, name, self.wb.worksheets[i]

        return None, None, None

    def create_sheet(self, index = 0, title = None):
        self.wb.create_sheet(index = index, title = title)

    def remove_sheet(self, title):
        self.wb.remove_sheet(self.get_sheet_by_name(title))

    def set_sheet_name(self, title, sheet = None):
        if sheet is None:
            sheet = self.ws
        sheet.title = title

    def get_sheet(self, index):
        if index is None:
            return self.ws

        if type(index) is int:
#            name = self.wb.get_sheet_names()[index]
            return self.wb.worksheets[index]

        if type(index) is str:
            name = index
            return self.wb.get_sheet_by_name(name)                

        return index

    def get_column_width(self, icolumn, sheet = None):
        if sheet is None:
            sheet = self.ws

        if type(icolumn) == int:
            icolumn = openpyxl.utils.get_column_letter(icolumn)
        return sheet.column_dimensions[icolumn].width

    def set_column_width(self, icolumn, width = None, sheet = None):
        if sheet is None:
            sheet = self.ws

        if type(icolumn) == int:
            icolumn = openpyxl.utils.get_column_letter(icolumn)
        sheet.column_dimensions[icolumn].width = width

    def get_column_hidden(self, icolumn, sheet = None):
        if sheet is None:
            sheet = self.ws

        if type(icolumn) == int:
            icolumn = openpyxl.utils.get_column_letter(icolumn)

        return sheet.column_dimensions[icolumn].hidden

    def set_column_hidden(self, icolumn, hidden = None, sheet = None):
        if sheet is None:
            sheet = self.ws

        if type(icolumn) == int:
            icolumn = openpyxl.utils.get_column_letter(icolumn)
        sheet.column_dimensions[icolumn].hidden = hidden

    def set_cell_font(self, row, column, sheet = None, font_color = '000000', 
                        italic = False, bold = False, strike = False, underline = None,
                        size = None, font_name = None,
                        vertAlign = 'baseline'):
        if sheet is None:
            sheet = self.ws

        font = openpyxl.styles.fonts.Font(
                    color = font_color, bold = bold, italic = italic, 
                    strike = strike, underline = underline,
                    size = size, name = font_name,
                    vertAlign = vertAlign)
        cell = sheet.cell(row = row, column = column)
        cell.font = font

    def set_cell_fill(self, row, column, sheet = None, fgcolor = 'FFFFFF', bgcolor = 'FFFFFF', pattern = 'solid'):
        if sheet is None:
            sheet = self.ws

        fill = openpyxl.styles.PatternFill(patternType = pattern, fgColor = fgcolor, bgColor = bgcolor)
        cell = sheet.cell(row = row, column = column)
        cell.fill = fill

    def find_val_in_row(self, val, irow, column_org = 1, sheet = None):
        return self.get_icolumn_from_label(val, column_org = column_org, row_org = irow, sheet = sheet)
        
    def find_val_in_column(self, val, icolumn, row_org = 1, sheet = None, auto_add = False):
        if sheet is None:
            sheet = self.ws

        ilast_vald_data = None
        for irow in range(row_org, sheet.max_row + 1):
            s = sheet.cell(row = irow, column = icolumn).value
            if s == val:
                return irow
            if s is not None and s != '':
                ilast_vald_data = irow

        if auto_add:
            ilast_vald_data += 1
            sheet.cell(row = ilast_vald_data, column = icolumn).value = val
            return ilast_vald_data

        else:
            return None

# cell = 'A1' to invalidate the freeze
    def freeze_panes(self, cell = 'A1', sheet = None):
        if sheet is None:
            sheet = self.ws
        sheet.freeze_panes = cell

    def store_column_widths(self, sheet = None, column_org = 1, row_org = 1):
        if sheet is None:
            sheet = self.ws

        max_column = sheet.max_column
        self.width_list = []
        self.width_dict = {}
        for icol in range(column_org, sheet.max_column+1):
            w = self.get_column_width(icol, sheet = sheet)
            label = sheet.cell(row = row_org, column = icol).value
            self.width_list.append(w)
            self.width_dict[label] = w

        return self.width_list, self.width_dict

    def restore_column_widths(self, sheet = None, column_org = 1, row_org = 1, by_label = True):
        if sheet is None:
            sheet = self.ws
            return None

        if by_label:
            for label in self.width_dict.keys():
                icol = self.get_icolumn_from_label(label, sheet = sheet, column_org = column_org, row_org = row_org)
                self.set_column_width(icol, width = self.width_dict[label], sheet = sheet)
        else:
            max_column = sheet.max_column
            for icol in range(column_org, sheet.max_column+1):
                self.set_column_width(icol, width = self.width_list[icol], sheet = sheet)

    def store_column_hidden(self, sheet = None, column_org = 1, row_org = 1):
        if sheet is None:
            sheet = self.ws

        max_column = sheet.max_column
        self.hidden_list = []
        self.hidden_dict = {}
        for icol in range(column_org, sheet.max_column+1):
            h = self.get_column_hidden(icol, sheet = sheet)
            label = sheet.cell(row = row_org, column = icol).value
#            print("icol=", icol, label, h)
            self.hidden_list.append(h)
            self.hidden_dict[label] = h

        return self.hidden_list, self.hidden_dict

    def restore_column_hidden(self, sheet = None, column_org = 1, row_org = 1, by_label = True):
        if sheet is None:
            sheet = self.ws
            return None

        if by_label:
            for label in self.hidden_dict.keys():
                icol = self.get_icolumn_from_label(label, sheet = sheet, column_org = column_org, row_org = row_org)
                self.set_column_hidden(icol, hidden = self.hidden_dict[label], sheet = sheet)
        else:
            max_column = sheet.max_column
            for icol in range(column_org, sheet.max_column+1):
                self.set_column_hidden(icol, hidden = self.hidden_list[icol], sheet = sheet)

    def insert_rows(self, irow):
        self.ws.insert_rows(irow)

    def insert_cols(self, icol, sheet = None, label = None, 
                        check_exist = False, return_if_exist = False,
                        column_org = 1, row_org = 1,
                        font = None, alignment = None, fill = None, border = None,
                        width = None, restore_width = True, restore_hidden = True):
        if sheet is None:
            sheet = self.ws

        if restore_width:
            self.store_column_widths(sheet = sheet, column_org = column_org, row_org = row_org)
        if restore_hidden:
            self.store_column_hidden(sheet = sheet, column_org = column_org, row_org = row_org)

        if check_exist and label is not None:
            idx = self.get_icolumn_from_label(label, sheet = sheet, column_org = column_org, row_org = row_org)
            if idx is None:
                sheet.insert_cols(icol)
                sheet.cell(row = row_org, column = icol).value = label
            elif return_if_exist:
                return idx

        idx = self.get_icolumn_from_label(label, sheet = sheet, column_org = column_org, row_org = row_org)

        if width is not None:
            self.set_column_width(icol, width = width, sheet = sheet)

        max_row = sheet.max_row

        if font is not None:
            if type(font) is int:
                if font >= icol:
                    isrc = font + 1
                else:
                    isrc = font
                
                for irow in range(row_org, max_row+1):
                    fo = sheet.cell(row = irow, column = isrc).font.copy()
                    sheet.cell(row = irow, column = idx).font = fo
            else:
                for irow in range(row_org, max_row+1):
                    sheet.cell(row = irow, column = idx).font = font

        if alignment is not None:
            if type(alignment) is int:
                if alignment >= icol:
                    isrc = alignment + 1
                else:
                    isrc = alignment
                for irow in range(row_org, max_row+1):
                    al = sheet.cell(row = irow, column = isrc).alignment.copy()
                    sheet.cell(row = irow, column = idx).alignment = al
            else:
                for irow in range(row_org, max_row+1):
                    sheet.cell(row = irow, column = idx).alignment = alignment

        if fill is not None:
            if type(fill) is int:
                if fill >= icol:
                    isrc = fill + 1
                else:
                    isrc = fill
                for irow in range(row_org, max_row+1):
                    fi = sheet.cell(row = irow, column = isrc).fill.copy()
                    sheet.cell(row = irow, column = idx).fill = fi
            elif type(fill) is str:
                for irow in range(row_org, max_row+1):
                    self.set_cell_fill(irow, idx, sheet = sheet, fgcolor = fill, bgcolor = fill, pattern = 'solid')
            else:
                for irow in range(row_org, max_row+1):
                    sheet.cell(row = irow, column = idx).fill = fill

        if border is not None:
            if type(border) is int:
                if border >= icol:
                    isrc = border + 1
                else:
                    isrc = border
                for irow in range(row_org, max_row+1):
                    bo = sheet.cell(row = irow, column = isrc).border.copy()
                    sheet.cell(row = irow, column = idx).border = bo
            else:
                for irow in range(row_org, max_row+1):
                    sheet.cell(row = irow, column = idx).border = border

        if restore_width:
            self.restore_column_widths(sheet = sheet, 
                    column_org = column_org, row_org = row_org, by_label = True)
        if restore_hidden:
            self.restore_column_hidden(sheet = sheet, 
                    column_org = column_org, row_org = row_org, by_label = True)

        return idx
#        return icol

    def delete_rows(self, irow, irow2 = None):
        self.ws.delete_rows(irow, irow2)

    def delete_cols(self, idx, amount = None):
        if amount is None:
            self.ws.delete_cols(idx)
        else:
            self.ws.delete_cols(idx, amount = amount)

    def get_row(self, irow, ws = None):
        ws = self.get_sheet(ws)
        self.cur_row = irow + 1
        self.cur_col = 0

        return ws[irow+1]

    def copy_cell_format(self, ws_source = None, irow_source = None, icol_source = None, 
                               ws_target = None, irow_target = None, icol_target = None, 
                               format = 'value|fill|font|border'):
        ws_source = self.get_sheet(ws_source)
        ws_target = self.get_sheet(ws_target)

        if 'value' in format:
            val = ws_source.cell(row = irow_source, column = icol_source).value
            ws_target.cell(row = irow_target, column = icol_target).value = val

        if 'fill' in format:
            fill = ws_source.cell(row = irow_source, column = icol_source).fill.copy()
            ws_target.cell(row = irow_target, column = icol_target).fill = fill

        if 'font' in format:
            font = ws_source.cell(row = irow_source, column = icol_source).font.copy()
            ws_target.cell(row = irow_target, column = icol_target).font = font

        if 'border' in format:
            border = ws_source.cell(row = irow_source, column = icol_source).border.copy()
            ws_target.cell(row = irow_target, column = icol_target).border = border

# 違うwoorkbookのシートをコピー
    def copy_worksheet2(self, ws_source = None, title = None):
        for icol in range(ws_source.max_column):
            icol += 1
            for irow in range(ws_source.max_row):
                irow += 1
                self.copy_cell_format(ws_source = ws_source, irow_source = irow, icol_source = icol, 
                               ws_target = self.ws, irow_target = irow, icol_target = icol, 
                               format = 'value|fill|font|border')

        self.ws.title = ws_source.title

        return self.ws

# 同じwoorkbookのシートをコピー
    def copy_worksheet(self, ws_source = None, title = None):
        prev_ws = self.ws
        ws_source = self.get_sheet(ws_source)

        try:
            ws_target = self.wb.copy_worksheet(ws_source)
        except:
            return self.copy_worksheet2(ws_source = ws_source, title = title)

        if title is not None:
            ws_target.title = title
        else:
            ws_target.title = ws_source.title
        self.ws = prev_ws
        
        return ws_target

    def append(self, datas):
        self.ws.append(datas)
        self.cur_row = self.max_row() + 1
        self.cur_col = 0

    def Append(self, datas):
        self.append(datas)

    def save(self, path = None, workbook = None):
        if path is None:
            path = self.path
        if workbook is None:
            workbook = self.wb

        self.SetPath(path, mode = 'w')

        try:
            return workbook.save(path)
        except:
            print(f"\nError in tkexcel.save(): Could not write to [{path}]")
            print(f"   Check if the file is opend, or check write permission")
            return None

    def write(self, irow, icol, val):
        if type(val) is dict:
            self.ws.cell(irow + 1, icol + 1).value = val["val"]
            if val.get("number_format", None) is not None:
                self.ws.cell(irow + 1, icol + 1).number_format = val["number_format"]
            if val.get("horizontal_allignment", None) is not None:
                if val["horizontal_allignment"] == '<':
                    self.ws.cell(irow + 1, icol + 1).alignment = Alignment(horizontal = "leftContinuous")
                elif val["horizontal_allignment"] == '^':
                    self.ws.cell(irow + 1, icol + 1).alignment = Alignment(horizontal = "centerContinuous")
                elif val["horizontal_allignment"] == '>':
                    self.ws.cell(irow + 1, icol + 1).alignment = Alignment(horizontal = "rightContinuous")
        else:
            self.ws.cell(irow + 1, icol + 1).value = val

    def Write(self, irow, icol, val):
        self.write(irow, icol, val)

    def print(self, *vals, end = '\n'):
        if type(vals[0]) is list or type(vals[0]) is tuple:
            for v in vals[0]:
                self.Write(self.cur_row, self.cur_col, v)
                self.cur_col += 1
        else:
            for i in range(len(vals)):
                self.Write(self.cur_row, self.cur_col, vals[i])
                self.cur_col += 1

        if end == '\n':
            self.cur_row += 1
            self.cur_col = 0
#            print("r=", self.cur_row, self.cur_col)
        else:
            pass
#        print("r=", self.cur_row, self.cur_col)

    def Print(self, *vals, end = '\n'):
        self.print(*vals, end)
        
    def load_workbook(self, path = None):
        self.SetPath(path = path, mode = 'w')

#  UserWarning: Data Validation extension is not supported and will be removed がでるのでWarning抑制
        openpyxl.reader.excel.warnings.simplefilter('ignore')
        book = openpyxl.load_workbook(self.path)
        self.wb = book

        return book

    def sheetnames(self, path = None):
#  UserWarning: Data Validation extension is not supported and will be removed がでるのでWarning抑制
        openpyxl.reader.excel.warnings.simplefilter('ignore')
        book = self.load_workbook(path)
        return book.sheetnames

    def read_sheet(self, path = None, sheet_name = None, mode = 'r', data_only = True, 
                    password = None, allow_no_password = False,
                    irow_origin = 1, icol_origin = 1):
        self.SetPath(path = path, mode = mode)

        if self.wb is None:
            wb = self.open(self.path, mode = mode, data_only = data_only, password = password, allow_no_password = allow_no_password)
            was_opened = False
        else:
            was_opened = True
        
        if sheet_name is not None:
            try:
                idx = int(sheet_name)
                ws = self.wb.worksheets[idx]
            except:
                ws = self.wb[sheet_name]
        else:
            if self.wb is None or self.wb.active is None: return None
            ws = self.wb.active

        max_row    = ws.max_row
        max_column = ws.max_column

        cells = []
        irow = 0
        for _irow in range(irow_origin, max_row + 1):
#        for row in ws:
#            row = ws[_irow]
            cells.append([])

            icol = 0
            for _icol in range(icol_origin, max_column + 1):
#            for cell in row:
#                cell = row[_icol]
                cell = ws.cell(row = _irow, column = _icol)
                cells[irow].append(cell)
#                if _irow < 10:
#                    print(f"  {_irow}行{_icol}列: [{cell.value}]")

                icol += 1

            irow += 1

        if was_opened:
            self.Close()

        self.ws = ws

        return cells

    def Read_sheet(self, path = None, sheet_name = None, mode = 'r', data_only = True):
        return self.read_sheet(path = path, sheet_name = sheet_name, mode = mode, data_only = data_only)

    def get_list_from_cells(self, cells = None, debug = False):
        header = []
        for cell in cells[0]:
            header.append(cell.value)

        nrow = len(cells)
        ncol = len(cells[0])
        debug=1
        if debug:
            print("get_list_from_cells(): len(cells)=", len(cells))
            print("get_list_from_cells(): len(cells[0])=", len(cells[0]))

        datalist = make_matrix2(ncol, nrow - 1, defval = None)
        for irow in range(1, nrow):
#            row  = self.get_row(irow+1)
#            vals = []
            for icol in range(ncol):
                val = cells[irow][icol].value
#                val = self.get_val(irow + 1, icol)
                datalist[icol][irow-1] = val
#            datalist.append(vals)

        if debug:
            print("Read_sheet_list: len(datalist)=", len(datalist))
            print("Read_sheet_list: len(datalist[0])=", len(datalist[0]))

#        print("datalist=", datalist)
#        for i in range(nrow):
#            for j in range(ncol):
#                print(datalist[i][j], " ", end = '')
#            print("")

        return header, datalist

    def read_sheet_list(self, path= None, sheet_name = None, mode = 'r', data_only = True, password = None, allow_no_password = False,
                            irow_origin = 1, icol_origin = 1, debug = False):
        cells = self.read_sheet(path = path, sheet_name = sheet_name, mode = mode, data_only = data_only, 
                        password = password, allow_no_password = allow_no_password,
                        irow_origin = irow_origin, icol_origin = icol_origin)
        header, datalist = self.get_list_from_cells(cells = cells, debug = debug)

        return header, datalist

    def Read_sheet_list(self, path = None, sheet_name = None, mode = 'r', data_only = True, debug = False):
        return self.read_sheet_list(path = path, sheet_name = sheet_name, mode = mode, data_only = data_only, debug = debug)

    def get_dict_from_datalist(self, datalist = None, keys = None, irow_origin = 1, icol_origin = 1, allow_overlapped_keys  = True):
        nrow = len(datalist[0])
        ncol = len(keys)

        dict = {}
        for i in range(ncol):
            field_name = keys[i]
            dict[field_name] = []

        red_field = {}
        for icol in range(icol_origin, ncol + 1):
            field_name = keys[icol-1]
            if not allow_overlapped_keys and red_field.get(field_name, None) is not None:
                continue

            red_field[field_name] = 1

            for irow in range(irow_origin, nrow + 1):
                dict[field_name].append(datalist[icol-1][irow-1])

        return dict

    def read_sheet_dict(self, path = None, sheet_name = None, allow_overlapped_keys = True, 
                    irow_origin = 1, icol_origin = 1, debug = False):

        keys, datalist = self.read_sheet_list(path = path, sheet_name = sheet_name, 
                                irow_origin = irow_origin, icol_origin = icol_origin, debug = debug)
#        sheetnames = self.sheetnames()
        sheetnames = self.wb.sheetnames

        if debug:
            print("Read_sheet_dict: len(datalist)=", len(datalist))
            print("Read_sheet_dict: len(datalist[0])=", len(datalist[0]))
            exit()
        
        dict = self.get_dict_from_datalist(datalist = datalist, keys = keys, irow_origin = irow_origin, icol_origin = icol_origin, 
                        allow_overlapped_keys = allow_overlapped_keys)

        return dict, sheetnames, keys, datalist

    def Read_sheet_dict(self, path = None, sheet_name = None, irow_origin = 1, icol_origin = 1, allow_overlapped_keys = True, debug = False):
        return self.read_sheet_dict(path = path, sheet_name = sheet_name, irow_origin = irow_origin, icol_origin = icol_origin,
                        allow_overlapped_keys = allow_overlapped_keys, debug = debug)

    def get_matrix_from_cells(self, cells = None):
        if cells is None:
            cells = self.read_sheet()

        header = []
        for cell in cells[0]:
            header.append(cell.value)
#        print("header=", header)
        matrix = []
        for irow in range(1, len(cells)):
            row = self.get_row(irow)
            vals = []
            for cell in row:
                vals.append(cell.value)
            matrix.append(vals)
#        print("matrix=", matrix)

    def read_sheet_matrix(self, sheet_name = None):
        cells = self.read_sheet()
        header, matrix = self.get_matrix_from_cells(cells = cells)

        return header, matrix

    def Read_sheet_matrix(self, sheet_name = None):
        return self.read_sheet_matrix(sheet_name = sheet_name)

    def read_minimum_matrix(self, close_fp = False, force_numeric = True):
        labels   = []
        row = self.get_row(0)
        for cell in row:
            val = cell.value
            if val is None:
                break

            if type(val) is str:
                val.strip()
            labels.append(val)

        ncol = len(labels)
        datalist = make_matrix1(ncol, type = 'list')
        irow = 1
        while 1:
            row = self.get_row(irow)
            if row is None or row[0].value is None:
                break

            for icol in range(ncol):
                try:
                    if force_numeric:
                        val = pfloat(row[icol].value)
                    else:
                        val = pfloat(row[icol].value, defval = row[icol].value)
                except:
                    val = None
                datalist[icol].append(val)

            irow += 1

        if close_fp:
            self.Close()

        self.labelarray    = labels
        self.datalistarray = datalist

        return labels, datalist

    def Read_minimum_matrix(self, close_fp = False, force_numeric = True):
        return self.read_minimum_matrix(close_fp = close_fp, force_numeric = force_numeric)


def main():
    path = "a.xlsx"
    xls = tkExcel(path, 'w')
    xls.Write(0, 0, 'a00')
    xls.Write(1, 1, 'a11')
    xls.Close()

    xls = tkExcel(path, 'a')
    xls.Write(0, 1, 'a01')
    xls.Write(1, 1, 'a22')
    xls.Close()

    xls = tkExcel(path, 'r')
    print("00=", xls.Get(0, 0))
    print("01=", xls.Get(0, 1))
    print("10=", xls.Get(1, 0))
    print("11=", xls.Get(1, 1))
    xls.Close()

    path = "b.xlsx"
    xls = tkExcel(path, 'w')
    xls.print([0, 1, 2, 'aa'])
    xls.print([3, 0, 1, 2, 'bb'])
    xls.print([3, 'a&'], end = '')
    xls.print([3, 5])
    xls.Close()

    
if __name__ == "__main__":
    main()
