from   logging             import getLogger, config, DEBUG, INFO, WARN, ERROR, CRITICAL
import json
import os
import sys
import time
import math
import locale
import datetime
import csvExcelConst
import csvExcelImportXml
import pandas as pd 
import openpyxl
import openpyxl
from openpyxl.styles.fonts import Font
from openpyxl.worksheet.table import Table, TableStyleInfo

class csvExcelConvert:
    class csvExcelConvertError(FileNotFoundError):
        pass
    # --------------------------------------------------------
    # 空のＥＸＣＥＬファイルを生成
    # --------------------------------------------------------
    def createEmptyExcelFile(self, excelFileName):
        workbook = openpyxl.Workbook()  #自動的にシート名「sheet」が0番目に作成される。
        workbook.save(excelFileName)
        workbook.close
        return

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルを読込む
    # --------------------------------------------------------
    def loadExcelFile(self, excelFileName):
        wb = openpyxl.load_workbook(excelFileName)
        return wb 

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルのシート名一覧を取得する
    # --------------------------------------------------------
    def getSheetNames(self, workbook):
        
        sheetNames = workbook.getSheetNames
        sheetCount = len(sheetNames)
        return sheetNames, sheetCount     

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルのアクティブシートを取得する
    # --------------------------------------------------------
    def getActiveSheet(self, workbook):
        
        sheet = workbook.active
        return sheet     

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルのアクティブシートの変更
    # --------------------------------------------------------
    def changeActiveSheet(self, workbook, sheetName):
        
        for ws in workbook.worksheets:
            if ws.title.casefold() == sheetName.casefold():
                ws.sheet_view.tabSelected = True
                return
        
        return 

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルのシート名を変更する
    # --------------------------------------------------------
    def renameSheetName(self, sheet, newName):
        
        sheet.title = newName
        return

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルのシート名が存在しているかチェックする
    # --------------------------------------------------------
    def checkSheetNames(self, workbook, sheetName):
        
        sheetNames = workbook.getSheetNames
        
        for ckName in sheetNames:
            if ckName.casefold() == sheetName.casefold():
                return True
        
        return False

    # --------------------------------------------------------
    # ＥＸＣＥＬファイル最後にシートを追加する
    # --------------------------------------------------------
    def addSheetName(self, workbook, sheetName):
        
        workbook.create_sheet(title=sheetName, index=len(workbook.sheetNames))

        sheet = workbook[sheetName]
        return sheet

    # --------------------------------------------------------
    # ＥＸＣＥＬファイルの指定したシートを削除する
    # --------------------------------------------------------
    def deleteSheetName(self, workbook, sheetName):
        
        for ws in workbook.worksheets:

            if ws.title.casefold() == sheetName.casefold():
                workbook.remove(ws)
                return

        return

    # --------------------------------------------------------
    # ＥＸＣＥＬの列文字の取得
    # --------------------------------------------------------
    def getExcelColName(self, cols):

        colSuffix = ""
        colSuffixNum = math.floor(cols / 26)
        if colSuffixNum > 0:
            colSuffix = chr(0x0040 + colSuffixNum)
        
        colStr = chr(0x0040 + (cols % 26))

        return colSuffix + colStr
    #=======================================================#
    # ＣＳＶファイルをＥＸＣＥＬに変換する
    #-------------------------------------------------------#
    def convertExcelSheet(self, log, outputFileName, sheetName, defFileName, csvFileName):

        defHead, defItem = csvExcelImportXml.getDef(defFileName)

        log.log(INFO, f"CSVファイル名＝{csvFileName}")
        log.log(INFO, f"defファイル名＝{defFileName}")

        # ================================================================
        # ＣＳＶファイルを読込む encodingを指定しないとutf-8と見なされる
        # ----------------------------------------------------------------
        csvData = pd.read_csv(csvFileName, encoding="Shift-JIS")

        # =========================================================
        # 読込んだＣＳＶファイルをＥｘｃｅｌファイルへ出力する
        # ---------------------------------------------------------
        num_rows, num_cols = csvData.shape
        log.log(INFO, f"行数＝{num_rows}、列数＝{num_cols}")

        winLockRow = int(defItem[csvExcelConst.WINDOW_LOCK_ROW]) - 1 # Window枠の固定　行位置
        winLockCol = int(defItem[csvExcelConst.WINDOW_LOCK_COL]) - 1 # Window枠の固定　列位置

        log.log(DEBUG, f"winLockRow＝{winLockRow}、winLockCol＝{winLockCol}")
        #
        # pd.ExcelWriteもmodeは、a, w
        # with pd.ExcelWriter(outputFileName, engine='openpyxl', mode='a', if_sheet_exists='new') as writer:
        # with pd.ExcelWriter(outputFileName, engine='openpyxl', mode='a') as writer:

        # csvData.to_excel(outputFileName, sheet_name=sheetName, index=False, freeze_panes=(winLockRow, winLockCol))
        with pd.ExcelWriter(outputFileName) as writer:
            csvData.to_excel(writer, sheet_name=sheetName, index=False, freeze_panes=(winLockRow, winLockCol))
        
        # =========================================================
        # 生成されたＥｘｃｅｌの読込み及びシートの取得
        # ---------------------------------------------------------
        wb = self.loadExcelFile(outputFileName)
        ws = wb[sheetName]
        # ws = wb.active

        # =========================================================
        # Ｅｘｃｅｌのフォント指定とシート名の変更
        # ---------------------------------------------------------
        # deleteSheetName(wb, 'sheet')
        font = Font(name='メイリオ')
        for row in ws:

            for cell in row:
                ws[cell.coordinate].font = font
                #  print(cell.coordinate) # 現在のセルの位置　例：A4
        wb.save
        # =========================================================
        # Ｅｘｃｅｌの列幅を自動設定
        # ---------------------------------------------------------
        for col in ws.columns:
            max_length = 0
            column = col[0].column

            for cell in col:
                
                tmp_len = 0
                tmp_str = str(cell.value).strip()
                tmp_max = len(tmp_str)
                
                if tmp_max == 0:
                    continue

                for i in range(tmp_max) :
                    tmp_char = tmp_str[i:i+1]
                    work_len = len(tmp_char.encode('utf-8'))
                    if work_len == 3: # 漢字の場合は、３バイトになるが、列幅換算は２バイト
                        tmp_len += 2
                    else:
                        tmp_len += 1  # 半角文字は１バイト

                if tmp_len > max_length:
                    max_length = tmp_len

            adjusted_width = (max_length + 2) * 1.2
            column_name    = self.getExcelColName(column)

            ws.column_dimensions[column_name].width = adjusted_width
            log.log(DEBUG, f"列名＝{column_name}, 列幅＝" + str(adjusted_width))
        wb.save
        # ================================================================
        # Ｅｘｃｅｌ列の表示形式設定(表示形式は列での一括設定は出来ない)
        # ----------------------------------------------------------------
        
        dict_members = defItem[csvExcelConst.COLS]

        for mem in dict_members:
            mem2 = dict_members[mem]

            col_number_num  = int(mem2[csvExcelConst.COL_NO])
            col_number_char = self.getExcelColName(col_number_num)
            col_edit_format = mem2[csvExcelConst.EDIT_FORMAT].replace('赤', 'Red')
            col_edit_format = col_edit_format.replace('黒', 'Black')
            col_edit_format = col_edit_format.replace('青', 'Blue')
            col_edit_format = col_edit_format.replace('水', 'Cyan')
            col_edit_format = col_edit_format.replace('緑', 'Green')
            col_edit_format = col_edit_format.replace('紫', 'Magenta')
            col_edit_format = col_edit_format.replace('白', 'White')
            col_edit_format = col_edit_format.replace('黄', 'Yellow')
            
            edit_Number_rows = num_rows + 2  # タイトル行の分＋１とrangeは１つ前で終了するので＋１、合計＋２
        
            for i in range(2, edit_Number_rows) :
                cell_char   = col_number_char + str(i)
                ws[cell_char].number_format = col_edit_format
                # log.log(DEBUG, f"列No＝%s 表示形式＝%s" % (cell_char, col_edit_format))
        wb.save    
        
        # =========================================================
        # Ｅｘｃｅｌにテーブルを設定
        # ---------------------------------------------------------
        cellRange="A1:" + self.getExcelColName(num_cols) + str(num_rows + 1)  # +1はヘッダー行
        tableName  = defHead[csvExcelConst.TABLE_NAME]
        tableStyle = defHead[csvExcelConst.TABLE_STYLE]

        log.log(DEBUG, f"tableName={tableName}")
        log.log(DEBUG, f"tableStyle={tableStyle}")
        log.log(DEBUG, f"Range={cellRange}")

        table_style = TableStyleInfo(name=tableStyle, showRowStripes=True)
        table = Table(displayName=tableName, ref=cellRange)
        table.tableStyleInfo = table_style

        ws.add_table(table)
        wb.save
        
sys.modules[__name__]=csvExcelConvert()