def get_subjects(wb, sht_name='模板', item_analysis='未核销', sht_range=None):
col_items = '条目'
col_subjects = '科目'
col_subjects_table = '科目表名'
sht = wb.sheets[sht_name]
if sht_range != None:
arr = sht.range(sht_range).options(np.array).value
else:
arr = sht.used_range.options(np.array).value
check_max_rc(arr, sht_name)
# 获取列索引
items_col = np.argwhere(arr[0] == col_items)[0, 0]
subjects_col = np.argwhere(arr[0] == col_subjects)[0, 0]
subjects_table_col = np.argwhere(arr[0] == col_subjects_table)[0, 0]
# 获取条目列
arr_items = arr[:, items_col]
# 获取item_analysis对应的行索引
analysis_row = np.argwhere(arr_items == item_analysis)[:, 0]
subjects = arr[analysis_row, subjects_col]
subjects_tables = arr[analysis_row, subjects_table_col]
if len(np.argwhere(subjects == 'nan')) >= 1:
index_nan = np.argwhere(subjects == 'nan')
subjects = np.delete(subjects, index_nan)
subjects_tables = np.delete(subjects_tables, index_nan)
logging.warning("'{sht_name}表'中科目列{row}行存在空单元格，请确认！".format(
sht_name=sht_name, row=index_nan))
elif len(np.argwhere(subjects_tables == 'nan')) >= 1:
index_nan = np.argwhere(subjects_tables == 'nan')
subjects = np.delete(subjects, index_nan)
subjects_tables = np.delete(subjects_tables, index_nan)
logging.warning("'{sht_name}表'中科目表名列{row}行存在空单元格，请确认！".format(
sht_name=sht_name, row=index_nan))
# dict_subjects = dict.fromkeys(subjects, subjects_tables)
return subjects, subjects_tables
wb1 = xw.Book(excel_configs.account_analysis_book)
#wb2 = xw.Book(excel_configs.non_verification_book)
#wb3 = xw.Book(excel_configs.payable_book)
# 检查 ‘未核销’
logging.info('======================开始复制“未核销”======================')
subjects, subjects_tables = xw_funs.get_subjects(wb1, sht_name = excel_configs.template_sheet, item_analysis = '未核销')
if len(subjects) > 0:
wb2 = xw.Book(excel_configs.non_verification_book)
for subject, subjects_table in zip(subjects, subjects_tables):
subject = subject.strip()
subjects_table = subjects_table.strip()
dict_suppliers = xw_funs.check_non_verification_table(wb2, subject, sht_name = excel_configs.non_verification_sheet)
if len(dict_suppliers) == 0:
logging.warning("未在'未核销表'中查找到'{subject}'科目,请手动确认!!!".format(subject = subject))
continue
t_start = time.time()
logging.info("‘{subjects_tb}’正在复制...".format(subjects_tb = subjects_table))
if xw_funs.copy_last_account(wb1, subjects_table):
t_end = time.time()
logging.info("‘{subjects_tb}’复制完毕！ 耗时：{time_used:.2f}秒".format(subjects_tb = subjects_table, time_used = t_end - t_start))
# 检查 ‘应付’
logging.info('======================开始复制“应付”======================')
subjects, subjects_tables = xw_funs.get_subjects(wb1, sht_name = excel_configs.template_sheet, item_analysis = '应付')
if len(subjects) > 0:
wb3 = xw.Book(excel_configs.payable_book)
for subject, subjects_table in zip(subjects, subjects_tables):
subject = subject.strip()
'server_ip':
lambda v: v is not None and type(v) == str,
'visible_port':
lambda v: v is not None and type(v) == int and 0 <= v and v <= 65535,
'nogui':
lambda v: v is not None and type(v) == bool,
'default_template':
lambda v: v is not None and type(v) == str
}
try:
with open('settings.json', 'r') as __file:
loaded = load(__file)
for key in defaults.keys():
l = loaded.get(key)
if validator[key](l):
settings[key] = l
else:
settings[key] = defaults[key]
logging.warning(f'Invalid setting "{key}" with value: {l}')
except OSError:
settings = defaults
finally:
with open('settings.json', 'w') as __file:
dump(settings, __file, indent=2)
# Testing
if __name__ == '__main__':
for setting in settings.items():
print(f'{setting[0]:20} : {setting[1]}')