import os import sys import re import glob import getpass import openpyxl sys.path.append("D:/tkProg/tkProg.main/tklib/python") from tklib.tkutils import terminate, getarg, getintarg, getfloatarg from tklib.tkutils import pint, pfloat from tklib.tkparams import tkParams from tklib.tkapplication import tkApplication from tklib.tkexcel_db import tkExcelDB app = tkApplication() cparams = tkParams() logfile = app.replace_path(None, template = ["{dirname}", "{filebody}-out.txt"]) print() print(f"Open logfile [{logfile}]") app.redirect(targets = ["stdout", logfile], mode = 'w') cparams.mode = "blind" cparams.score_files = "" cparams.outfile = '' tmp_file = 'tmp.xlsx' password = None icol_indexes = [1] icol_targets = [2] irow_targets = [2,3,4] cparams.mode = getarg(1, cparams.mode) cparams.score_files = getarg(2, cparams.score_files) cparams.outfile = getarg(3, cparams.outfile) icol_indexes = getarg(4, icol_indexes) icol_targets = getarg(5, icol_targets) irow_targets = getarg(6, irow_targets) password = getarg(7, password) if password is None or password == '': print("") password = getpass.getpass("Input password >>") def clean_up(*files): print("") print("ファイルクリーンアップ") for f in files: if os.path.isfile(f): print(f" {f} が存在ます。削除します") os.remove(f) def search_input_files(fmask): files = glob.glob(cparams.score_files) input_files = [] for f in sorted(files): if '~' in f: continue input_files.append(f) return input_files def convert_list_str(s, imin, imax): if type(s) is list or type(s) is tuple: return s l = [] for a in s.split(','): m = re.match(r'(\d*)\-(\d*)', a) if m: g = m.groups() r0 = pint(g[0], imin) r1 = pint(g[1], imax) l.extend(range(r0, r1 + 1)) else: l.append(pint(a, imin)) return l def merge(): global icol_indexes, icol_targets, irow_targets # 一時ファイル、出力ファイルを削除 clean_up(cparams.outfile, tmp_file) print("") print("動作モード:", cparams.mode) allow_nopw = 'allow_nopw' in cparams.mode print("　パスワードが与えられていても、パスワードの無いファイルを開く:", allow_nopw) print("入力ファイル:") print(" 評価ファイル　　:", cparams.score_files) print("出力ファイル:") print(" 評価まとめファイル:", cparams.outfile) # 入力ファイル取得 input_files = search_input_files(cparams.score_files) print("Found input_files:", input_files) file_replace = [] print("") if len(input_files) == 0: print("入力ファイルが見つからないので修了します") exit() print("入力ファイル") for idx, f in enumerate(input_files): if 'blind' in cparams.mode: idx_name = f"#{idx+1:03d}" else: idx_name = f file_replace.append(idx_name) print(f" {f} => {idx_name}") # シートサイズの読み込み print("") print(f"{input_files[0]}のシートサイズを読み込む") xls_in = tkExcelDB(f, table_name = None, password = password, allow_no_password = allow_nopw, tmp_file = tmp_file, OpenFile = 1, CloseFile = 1, description = '評点ファイル[0]', IsPrint = True) if xls_in.ws is None: print("") print(f"Error: Cannot read [{f}]") exit() max_column = xls_in.max_column() max_row = xls_in.max_row() print(" 最大列番号:", max_column) print(" 最大行番号:", max_row) # icol_indexesのラベル行を読み込み、indexesとindexes_dataに入れる # col_names = openpyxl.utils.get_column_letter(int(icol_indexes)) print(f"{input_files[0]}の[{icol_indexes}]列から出力ファイルのシート名を読み込む") icol_indexes = convert_list_str(icol_indexes, 0, max_column) icol_targets = convert_list_str(icol_targets, 0, max_column) irow_targets = convert_list_str(irow_targets, 0, max_row) print(f" 対象行番号:", irow_targets) indexes, indexes_data = xls_in.get_specified_data(icol_indexes, irow_targets, convert_label = True) print(f" 行番号 ", end = '') for i in range(len(indexes)): print(f"{indexes[i]}\t", end = '') print("") for j in range(len(indexes_data[0])): irow = irow_targets[j] print(f" {irow:6} ", end = '') for i in range(len(indexes)): print(f"{indexes_data[i][j]}\t", end = '') print("") # icol_targetsのラベル行を読み込み、shhetnamesに入れる print("") # col_name = openpyxl.utils.get_column_letter(int(icol_targets)) print(f"{input_files[0]}の[{icol_targets}]列から出力ファイルのシート名を読み込む") print(f" 対象行番号:", irow_targets) sheetnames, data = xls_in.get_specified_data(icol_targets, irow_targets, convert_label = True) nsheet = len(sheetnames) print(" nsheet=", nsheet) print(" sheetnames=", sheetnames) print("") print(f"評価まとめファイル {cparams.outfile} の作成") print( " 入力ファイル:", input_files) xls_out = tkExcelDB(cparams.outfile, mode = 'w', OpenFile = 1, data_only = True) # 出力ファイルにシートを作成 print(" シート作成=", 0, sheetnames[0]) xls_out.set_sheet_name(sheetnames[0]) ws_out = [xls_out.get_sheet(0)] for i in range(1, len(sheetnames)): print(" シート作成=", i, sheetnames[i]) xls_out.create_sheet(index = i, title = sheetnames[i]) ws_out.append(xls_out.get_sheet(i)) print(" ws_out=", ws_out) print() print("Writing to [{cparams.outfile}]") labels_written = False # 入力ファイルをスキャン for ifile in range(len(input_files)): f = input_files[ifile] if f != cparams.outfile and '~' not in f: print(f" {f}を読み込み") xls_in = tkExcelDB(f, table_name = None, password = password, allow_no_password = allow_nopw, tmp_file = tmp_file, OpenFile = 1, CloseFile = 1, description = f'評点ファイル[{f}]', IsPrint = True) labels, data = xls_in.get_specified_data(icol_targets, irow_targets, convert_label = True) sheet_in = xls_in.ws print() print("Writing index information...") # 出力シートにindex情報を書き込む #icol_indexes = [3, 4] #indexes, indexes_data = read_labels(input_files[0], icol_indexes, irow_targets) if not labels_written: for isheet in range(nsheet): for idata in range(len(data[0])): irow0 = irow_targets[idata] irow1 = idata + 2 for idx in range(len(icol_indexes)): icol0 = icol_indexes[idx] icol1 = idx + 1 # print("idx=", idx, icol_indexes, indexes) val = indexes_data[idx][idata] col_name = openpyxl.utils.get_column_letter(icol1) print(f" {indexes[idx]} {val} を{irow1}行{icol1}列[{col_name}]に書き込み") if idata == 0: ws_out[isheet].cell(row = 1, column = icol1).value = indexes[idx] ws_out[isheet].cell(row = irow1, column = icol1).value = val xls_out.copy_cell_format(ws_source = sheet_in, irow_source = irow0, icol_source = icol0, ws_target = ws_out[isheet], irow_target = irow1, icol_target = icol1, format = 'fill|font|border') labels_written = True print() print("Writing labels and values...") print("icol_indexes=", icol_indexes) print("ifile=", ifile) # 出力シートの１行目にファイル名/file_replaceをラベルとして書き込む #icol_targets = [5, 6, 7] #irow_targets = list(range(25, 35+1)) + list(range(37, 49+1)) #labels, data = read_labels(f, icol_targets, irow_targets) for isheet in range(nsheet): icol1 = ifile + len(icol_indexes) + 2 irow1 = 1 col_name = openpyxl.utils.get_column_letter(icol1) print(f" ファイル名 {file_replace[ifile]} を シート [{sheetnames[isheet]}] の{irow1}行{icol1}列[{col_name}]に書き込み") xls_out.set(row = irow1, column = icol1, val = file_replace[ifile], ws = ws_out[isheet]) for idata in range(len(indexes_data[0])): for isheet in range(nsheet): val = data[isheet][idata] icol0 = icol_targets[isheet] irow0 = irow_targets[idata] icol1 = ifile + len(icol_indexes) + 2 irow1 = idata + 2 col_name = openpyxl.utils.get_column_letter(icol1) print(f" {f}: {irow0}行{icol0}列の値 {val} を シート [{sheetnames[isheet]}] の {irow1}行{icol1}列[{col_name}]に書き込み") xls_out.set(row = irow1, column = icol1, val = val, ws = ws_out[isheet]) xls_out.set_column_width(icol1, width = 5, sheet = ws_out[isheet]) xls_out.close() print("") print(f"{cparams.outfile} に保存しました") print("") def main(): merge() if __name__ == "__main__": main()