def chk_proportion_file(path, wt_logfile):
output_info_redirect(u'开始检查%s...\n' % path, wt_logfile)
filename = u'费用占比.xlsx'
file_path = path + '\\' + filename
if not os.path.exists(path):
output_info_redirect(u'未找到%s, 将自动创建改目录...\n' % path, wt_logfile)
os.mkdir(path)
if os.path.isfile(file_path):
now_time = time.strftime('%Y-%m-%d_%H-%M-%S', time.localtime(time.time()))
file_path_tmp = file_path.split('.xlsx')[0]
file_path_back = file_path_tmp + '_backup_at_' + now_time + '.xlsx'
output_info_redirect(u'发现 费用占比.xlsx 文件已存在, 将重命名原文件...\n', wt_logfile)
shutil.move(file_path, file_path_back)
output_info_redirect(u'检查完毕!\n', wt_logfile)
return file_path
def shop_depart_relationship(bank_info_xl_path, logfile):
# bank_info_xl_path = u'E:\诺互银行\银行信息资料\制单银行-门店对应表.xlsx'
sheet_name = u'有银行的'
relation_wb = load_workbook(bank_info_xl_path)
if sheet_name in relation_wb.sheetnames:
relation_sheet = relation_wb[sheet_name]
relation_dict = {}
# key 部门名称, 如: 总经办, 华东一区, 成都XXXX综合店;
# value 制单部门, 如: 上海诺互制单, 成都八分制单
for i in range(2, relation_sheet.max_row + 1):
shop_name = relation_sheet.cell(i, 2).value
depart_name = relation_sheet.cell(i, 3).value
if shop_name and depart_name:
relation_dict[shop_name] = depart_name
else:
continue
return relation_dict
else:
text = u'%s 没有找到 [%s]sheet, 请检查... \n' \
u'系统将在3秒后退出 \n' % (bank_info_xl_path, sheet_name)
output_info_redirect(text, logfile)
time.sleep(3)
sys.exit(1)
def merge_proportion_result(proportion_file, src_path, wt_logfile):
wb = openpyxl.Workbook()
ws = wb.active
ws.title = u'费用占比'
line1 = [u'门店代码', u'门店名称', u'外包修理费', u'人员成本', u'配件成本', u'人员、配件成本合计', u'外包修理费占比成本',
u'外包修理费占比收入', u'汽油费', u'路桥费', u'停车费', u'巡检费合计', u'主营业务成本', u'巡检费占比成本百分比',
u'主营业务收入(不含暂估)', u'巡检费占收入百分比']
for i in range(len(line1)):
col = i + 1
ws.cell(1, col, line1[i])
raw = 2
for shop in os.listdir(src_path):
shop_file_path = src_path + '\\' + shop
name_and_values = calculate_cost_and_income(shop_file_path, wt_logfile)
shop_name = name_and_values[0]
shop_values = name_and_values[1]
output_info_redirect(u'正在将 %s 的数据写入 费用占比.xlsx 中...\n' % shop_name, wt_logfile)
ws.cell(raw, 1, shop_values['shop_code'])
ws.cell(raw, 2, shop_values['shop_name'])
ws.cell(raw, 3, shop_values['outsource_repair_cost'])
ws.cell(raw, 4, shop_values['human_cost'])
ws.cell(raw, 5, shop_values['fitting_cost'])
ws.cell(raw, 6, "=D%s+E%s" % (raw, raw))
ws.cell(raw, 7, "=C%s/F%s" % (raw, raw))
ws.cell(raw, 8, "=C%s/O%s" % (raw, raw))
ws.cell(raw, 9, shop_values['petrol_cost'])
ws.cell(raw, 10, shop_values['road_toll'])
ws.cell(raw, 11, shop_values['parking_fee'])
ws.cell(raw, 12, "=I%s+J%s+K%s" % (raw, raw, raw))
ws.cell(raw, 13, shop_values['main_business_cost'])
ws.cell(raw, 14, "=L%s/M%s" % (raw, raw))
ws.cell(raw, 15, shop_values['main_business_income'])
ws.cell(raw, 16, "=L%s/O%s" % (raw, raw))
output_info_redirect(u'写入完毕!\n', wt_logfile)
raw += 1
wb.save(proportion_file)
output_info_redirect(u'保存 费用占比.xlsx 完成!\n', wt_logfile)
text = u'###' * 20 + \
u'\n请按 [回车键] 退出...'
output_info_redirect(text, wt_logfile)
raw_input()
time.sleep(1)
return
def calculate_cost_and_income(file_path, wt_logfile):
filename = file_path.split('\\')[-1]
output_info_redirect(u'开始统计 %s 的费用占比...\n' % filename, wt_logfile)
items_and_code = {'outsource_repair_cost': ['6401.01.001.0003-', '6401.01.002.0004-', '6401.01.003.0004-'],
'human_cost': ['6401.99-'],
'fitting_cost': ['6401.01.002.0002-', '6401.01.003.0002-', '6401.02.001.0002-'],
'petrol_cost': ['6401.88.001-', '6401.04.002.0002-'],
'road_toll': ['6401.88.002-', '6401.04.002.0003-'],
'parking_fee': ['6401.88.003-', '6401.04.002.0004-'],
'main_business_cost': ['6401-'],
'main_business_income': ['6001.01-', '6001.02-']}
code_and_value = {'6401.01.001.0003-': 0, '6401.01.002.0004-': 0, '6401.01.003.0004-': 0, '6401.99-': 0,
'6401.01.002.0002-': 0, '6401.01.003.0002-': 0, '6401.02.001.0002-': 0,
'6401.88.001-': 0, '6401.04.002.0002-': 0, '6401.88.002-': 0, '6401.04.002.0003-': 0,
'6401.88.003-': 0, '6401.04.002.0004-': 0, '6401-': 0, '6001.01-': 0, '6001.02-': 0}
items_and_cost = {}
f = open(file_path)
content_list = f.read().split('\n')
f.close()
shop_name = file_path.split(u'店')[0].split('\\')[-1] + u'店'
shop_code = re.split('[>-]', content_list[95])[1]
items_and_cost['shop_name'] = shop_name
items_and_cost['shop_code'] = shop_code
output_info_redirect(u'%s 的门店编号是: %s\n' % (shop_name, shop_code), wt_logfile)
for code in code_and_value:
for line in content_list:
if code in line:
line_num = content_list.index(line) + 1
if content_list[line_num] == '</TD>':
line_num += 1
code_and_value[code] = float(re.split('[=>]', content_list[line_num])[2])
break
output_info_redirect(u'%s 获取的各项数据为: %s\n' % (shop_name, code_and_value), wt_logfile)
for item in items_and_code:
item_sum = 0
for code in items_and_code[item]:
item_sum += code_and_value[code]
items_and_cost[item] = item_sum
output_info_redirect(u'%s 的费用占比数据为: %s\n' % (shop_name, items_and_cost), wt_logfile)
return shop_name, items_and_cost
def core_method(dst_dir, err_dir, wt_logfile):
final_echo = {
'calc_err': [],
'map_err': [],
}
shop_name_map_file_dict = {}
dept_list = os.listdir(dst_dir)
text = u'正在检查 利润表合并 文件夹...\n'
output_info_redirect(text, wt_logfile)
if u'利润表合并' not in dept_list:
text = u'发现错误!\n' \
u'%s 中没有找到 利润表合并 文件夹 请检查...\n' % dst_dir + \
u'系统将退出! 请按 [回车键] 退出!\n'
output_info_redirect(text, wt_logfile)
raw_input()
return
merge_dir = os.path.join(dst_dir, u'利润表合并')
if u'上海诺互利润表.xlsx' not in os.listdir(merge_dir):
text = u'发现错误!\n' \
u'%s 中没有找到 上海诺互利润表.xlsx 请检查...\n' % merge_dir + \
u'系统将退出! 请按 [回车键] 退出!\n'
output_info_redirect(text, wt_logfile)
raw_input()
return
else:
merge_file = os.path.join(merge_dir, u'上海诺互利润表.xlsx')
merge_wb = openpyxl.load_workbook(filename=merge_file)
merge_wb_shts = merge_wb.sheetnames
dept_list.remove(u'利润表合并')
merge_data = {} # 存放门店的利润表数据
text = u'检查完毕!\n' \
u'正在检查 城市合并版 文件夹...'
output_info_redirect(text, wt_logfile)
combine_dir = os.path.join(dst_dir, u'城市合并版')
_del_path(combine_dir, wt_logfile)
# prime_dept_name = [u'北京诺互', u'成都诺互', u'广州景沪', u'广州诺互', u'海口诺互', u'昆明百当诺互',
# u'南京诺互', u'青岛楷模', u'上海昌保', u'深圳深南', u'沈阳诺互', u'天津诺互',
# u'武汉云景', u'西安来护']
calc_err_dir = os.path.join(err_dir, u'不平的报表')
merge_err_dir = os.path.join(err_dir, u'合并时出错的报表')
# os.makedirs(calc_err_dir)
text = u'开始处理利润表...\n'
output_info_redirect(text, wt_logfile)
for dept in dept_list:
dept_dir = os.path.join(dst_dir, dept)
shops_list = os.listdir(dept_dir)
try:
city_combine_filename = [
x for x in shops_list if x.startswith(dept)
][0]
except IndexError:
text = u'#########\n发现错误!\n' \
u'%s 文件夹中, 没有以 %s 开头的城市利润汇总表\n' \
u'#########' % (dept, dept)
output_info_redirect(text, wt_logfile)
continue
shops_list.remove(city_combine_filename)
city_combine_file = os.path.join(dept_dir, city_combine_filename)
rb = xlrd.open_workbook(city_combine_file, formatting_info=True)
total_profit = rb.sheet_by_index(0).cell(17, 1).value
text = u'\n' + u'###' * 34 + u'\n#\n' + u'###' * 34 + u'\n' + \
u'%s 在 汇总表中的净利润是: %s\n' % (dept, total_profit) + \
u'**' * 20 + u'\n' + \
u'准备修改汇总表 %s 的sheet名称...' % city_combine_filename
output_info_redirect(text, wt_logfile)
date_part = city_combine_filename.split('-')[-1]
combine_sht_name = dept + u'-利润表-' + date_part.split('.xls')[0]
change_sheet_name(city_combine_file, combine_sht_name, sht_num=0)
cp_to_sht_num = 2
from_sht_num = 1
if len(shops_list) > 0:
city_profit = 0.0
else:
city_profit = total_profit
merge_shops_name = []
month = int(date_part.split('.')[1])
merge_col = month * 3 - 1
for shop in shops_list:
shop_file = os.path.join(dept_dir, shop)
shop_name_tmp = shop.split(u'利润表')[0]
shop_name = shop_name_tmp + u'店' if not shop_name_tmp.endswith(
u'店') else shop_name_tmp
cp_to_sht_name = shop_name + u'-利润表-' + date_part.split('.xls')[0]
# 获取当月的实际净利润
shop_rb = xlrd.open_workbook(shop_file, formatting_info=True)
shop_profit = shop_rb.sheet_by_index(0).cell(17, 1).value
city_profit += shop_profit
text = u'**' * 20 + u'\n' + \
u'*开始编辑 %s\n' \
u'*加上 %s 的 %s 后, %s 的实际净利润是: %s\n' \
% (shop, shop_name, shop_profit, dept, city_profit) + \
u'**' * 20
output_info_redirect(text, wt_logfile)
# 获取当月的其他数据 写入字典merge_data
shop_rs = shop_rb.sheet_by_index(0)
merge_data[shop_name] = shop_rs.col_values(1, 1, 21)
merge_shops_name.append(shop_name)
shop_name_map_file_dict[shop_name] = {
'name': shop,
'path': shop_file
}
# 调用common方法中的 - 复制sheet表函数
copy_sheet(shop_file,
city_combine_file,
from_sht_num,
cp_to_sht_num,
cp_to_sht_name,
wt_logfile=wt_logfile)
total_profit = float(str(total_profit).decode('utf-8'))
city_profit = float(str(city_profit).decode('utf-8'))
text = u'@@' * 20 + u'\n' + \
u'现在 %s 的总实际净利润是: %s\n' % (dept, city_profit) + \
u'开始计算 %s 利润表B18单元格(净利润) 是否准确...\n' \
u'汇总数额 %s VS 实际数额 %s\n' % (dept, total_profit, city_profit)
output_info_redirect(text, wt_logfile)
# 如果total数和实际数不一致, 那么报错,
# 如果total数和实际数一致, 那么归档报表, 并把merge_data写入利润表合并
if total_profit != city_profit:
text = u'%s B18(净利润) 有错误, 将把该文件夹移动到 [不平的报表] 中...' % dept
output_info_redirect(text, wt_logfile)
os.path.exists(calc_err_dir) and 1 or os.mkdir(calc_err_dir)
move_to_dir = os.path.join(calc_err_dir, dept)
shutil.move(dept_dir, move_to_dir)
final_echo['calc_err'].append(dept_dir)
text = u'移动完毕!\n'
output_info_redirect(text, wt_logfile)
else:
# 汇总 [利润表 - 城市合并版]到同一个文件夹
name_part = dept + u'利润表'
combine_file_name = '-'.join((name_part, date_part))
combine_file = os.path.join(combine_dir, combine_file_name)
text = u'准确无误!\n' \
u'准备将 汇总表 %s\n' \
u'复制到 城市合并版 %s\n' % (city_combine_file, combine_file)
output_info_redirect(text, wt_logfile)
shutil.copy(city_combine_file, combine_file)
text = u'复制完成!\n\n' \
u'开始合并利润表...'
output_info_redirect(text, wt_logfile)
# 整理 利润表合并
for merge_shop in merge_shops_name:
try:
merge_sht_num = map((lambda y: merge_shop in y),
merge_wb_shts).index(True)
except ValueError:
text = u'没有在 %s 中找到 %s 的sheet名称, 或该名称不匹配\n' \
u'将把该文件 移动到 %s , 并跳过汇总过程...' \
% (u'上海诺互利润表.xlsx', merge_shop, merge_err_dir)
output_info_redirect(text, wt_logfile)
os.path.exists(merge_err_dir) and 1 or os.mkdir(
merge_err_dir)
from_file = shop_name_map_file_dict[merge_shop]['path']
move_to_file = os.path.join(
merge_err_dir,
shop_name_map_file_dict[merge_shop]['name'])
shutil.move(from_file, move_to_file)
final_echo['map_err'].append(merge_shop)
text = u'移动完毕!\n'
output_info_redirect(text, wt_logfile)
continue
else:
merge_ws = merge_wb[merge_wb_shts[merge_sht_num]]
data = merge_data[merge_shop]
for i in range(len(data)):
fill_num = data[i]
if i == 0 and fill_num != 0:
merge_ws.cell(3, merge_col, fill_num)
elif i not in [10, 14, 16] and fill_num != 0:
merge_row = i + 5
merge_ws.cell(merge_row, merge_col, fill_num)
merge_wb.save(merge_file)
text = u'%s 利润表合并完毕' % merge_shop
output_info_redirect(text, wt_logfile)
merge_wb.close()
text = u'\n' + u'###' * 20 + \
u'\n利润表 Excel文件处理完毕.\n' \
u'请在 %s 中获取处理后的Excel文件\n' % dst_dir
output_info_redirect(text, wt_logfile)
if os.listdir(err_dir):
err_ori_xls_dir = os.path.join(err_dir, u'有错误的原始报表')
if os.path.exists(err_ori_xls_dir):
for i in os.listdir(err_ori_xls_dir):
print i,
i = i.encode('utf-8')
with open(wt_logfile, 'a+') as write_file:
write_file.write(i + '\n')
print('')
text = u'原始报表有误, 请在 %s 获取相关报表\n' % err_ori_xls_dir
output_info_redirect(text, wt_logfile)
if len(final_echo['calc_err']) > 0:
for i in final_echo['calc_err']:
print i,
i = i.encode('utf-8')
with open(wt_logfile, 'a+') as write_file:
write_file.write(i + '\n')
print('')
text = u'报表不平, 请在 %s 获取相关报表\n' % calc_err_dir
output_info_redirect(text, wt_logfile)
if len(final_echo['map_err']) > 0:
for i in final_echo['map_err']:
print i,
i = i.encode('utf-8')
with open(wt_logfile, 'a+') as write_file:
write_file.write(i + '\n')
print('')
text = u'在合并报表时, 没有匹配到相应sheet, 请在 %s 获取相关报表\n' % merge_err_dir
output_info_redirect(text, wt_logfile)
else:
text = u'并且, 没有找到出错的报表! Good Job~\n'
output_info_redirect(text, wt_logfile)
os.removedirs(err_dir)
text = u'###' * 20 + \
u'\n请按 [回车键] 退出...'
output_info_redirect(text, wt_logfile)
raw_input()
time.sleep(1)
return
choice = raw_input()
logfile_path = create_logfile(BASE_DIR)
check_path_list = [] # .append([文件/路径名称, 'dir'/'file'])
if choice == '1':
print u'即将进行 - 银行导出数据核对\n'
from BankExcelCheck import payment_check
filename = u'银行核对公式.xlsx'
filePath = os.path.join(bank_check_dir, filename)
check_path_list.append([filePath, 'file'])
check_work_folder(check_path_list, logfile_path)
# check_folder('BankExcelCheck', logfile_path)
output_info_redirect(u'开始执行银行数据核对...', logfile_path)
payment_check(filePath)
output_info_redirect(u'核对完毕! \n系统将在2秒后退出\n', logfile_path)
time.sleep(2)
break
elif choice == '2' or choice == '3':
while True:
print(u'请输入付款日期(以YYYY-MM-DD格式, 如: 2019-01-01):')
pay_date = raw_input()
print(u'付款日期是 %s, 确认请按1, 重新输入请按2' % pay_date)
confirm = raw_input()
if confirm == '1':
break
def core_method(dst_dir, err_dir, wt_logfile):
final_echo = {'col_err': []}
from_sht_num = 1
cp_to_sht_num = 2
cp_to_sht_name = 'Page1_Copy'
col_err_dir = os.path.join(err_dir, u'列标题不匹配')
for filename in os.listdir(dst_dir):
file_path = os.path.join(dst_dir, filename)
copy_sheet(from_file=file_path,
cp_to_file=file_path,
from_sht_num=from_sht_num,
cp_to_sht_num=cp_to_sht_num,
cp_to_sht_name=cp_to_sht_name,
wt_logfile=wt_logfile)
# 获取需要处理的sheet, 以及需要处理的col
ori_rb = xlrd.open_workbook(file_path, formatting_info=True)
sht_name = 'Page1_Copy'
ori_sht = ori_rb.sheet_by_name(sht_name)
row_title = ori_sht.row_values(0)
xlapp = win32com.client.Dispatch('Excel.Application')
text = u'正在获取 科目代码、贷方 和 凭证摘要 所在的列...'
output_info_redirect(text, wt_logfile)
col_summary_num = col_subject_num = col_credit_num = -1
for i in row_title:
if i == u'凭证摘要':
col_summary_num = row_title.index(i)
elif i == u'科目代码':
col_subject_num = row_title.index(i)
elif i == u'贷方':
col_credit_num = row_title.index(i)
if col_summary_num >= 0 and col_subject_num >= 0 and col_credit_num >= 0:
text = u'获取成功! 科目代码、贷方、凭证摘要 分别在 %s %s %s 列\n\n' \
% (col_subject_num, col_credit_num, col_summary_num) + \
u'开始删除 凭证摘要为: 结转本期损益 以及 之后连续空单元格 所在的行...'
output_info_redirect(text, wt_logfile)
# 每进行一次删除行循环, 都要重新加载一次excel文件
# 删除'凭证摘要'列中, 单元格内容为: '结转本期损益' 和 之后的连续空单元格 的行
while True:
ori_rb = xlrd.open_workbook(file_path, formatting_info=True)
ori_sht = ori_rb.sheet_by_name('Page1_Copy')
col_summary = ori_sht.col_values(col_summary_num)
selected = [
x for x in range(len(col_summary))
if col_summary[x] == u'结转本期损益'
]
if len(selected) == 0:
text = u'这张表格中 没有 凭证摘要为: 结转本期损益\n'
output_info_redirect(text, wt_logfile)
break
else:
selected = selected[0]
text = u'已找到 结转本期损益, 当前所在行号为: %s\n' % (selected + 1) + \
u'开始计算 连续空单元格 的行号...'
output_info_redirect(text, wt_logfile)
void_col = [
x for x in range(len(col_summary))
if col_summary[x] == ''
]
to_del = [
selected,
]
tmp_num = selected + 1
if tmp_num in void_col:
for j in range(void_col.index(tmp_num), len(void_col)):
if void_col[j] == tmp_num:
to_del.append(void_col[j])
tmp_num += 1
else:
break
text = u'计算完毕, 当前需要删除的行号起始位置是: %s\n' \
u'需要删除连续的 %s 行' % (tmp_num, len(to_del))
output_info_redirect(text, wt_logfile)
if len(to_del) > 0:
xlbook = xlapp.Workbooks.Open(file_path)
xlsht = xlbook.Worksheets(sht_name)
del_line = selected + 1
for i in range(len(to_del)):
xlsht.Rows(del_line).Delete()
# xlbook.Save()
xlbook.Close(SaveChanges=True)
text = u'删除完毕！继续检查... \n'
output_info_redirect(text, wt_logfile)
# 删除 '贷方' 不等于0的行
text = u'开始删除 贷方 不等于0的行'
output_info_redirect(text, wt_logfile)
ori_rb = xlrd.open_workbook(file_path, formatting_info=True)
ori_sht = ori_rb.sheet_by_name('Page1_Copy')
col_credit = ori_sht.col_values(col_credit_num)
i = 1
xlbook = xlapp.Workbooks.Open(file_path)
xlsht = xlbook.Worksheets(sht_name)
while i < len(col_credit):
del_line = i + 1
if col_credit[i] != 0:
col_credit.remove(col_credit[i])
xlsht.Rows(del_line).Delete()
i -= 1
i += 1
# xlbook.Save()
xlbook.Close(SaveChanges=True)
text = u'删除完毕！\n'
output_info_redirect(text, wt_logfile)
# 【原】删除 '科目代码' 属于del_list的行
# 【现】删除 '科目代码' 不以 6 开头, 或属于del_list的行
# old_del_list = ['1001', '1002.001', '1002.002', '1002.003', '1002.004',
# '1122.01', '1122.02', '1122.03', '1122.05',
# '1221.04.001', '1221.04.002', '1221.04.003', '1405.01.01', '1405.01.02',
# '2221.01.01.01', '2221.01.01.02', '2221.01.01.03',
# '2241.04.001', '2241.04.002', '2203.01.001', '2203.01.003',
# '1301.02.001.0001', '1301.02.001.0002',
# '6403.01', '6403.02', '6403.03', '6403.04', '6403.05', '6403.06', '6403.07',
# '6403.08', '6403.09', '6403.10', '6403.11', '6403.12', '6403.13']
del_list = ['6401.98', '6602.17.001', '6401.01.003.0002']
# text = u'开始删除 科目代码 在列表中的行\n' \
# u'该列表为:\n' \
# u'%s' % del_list
text = u'开始删除 科目代码 不以 6 开头\n' \
u'或等于 6401.98, 6602.17.001, 6401.01.003.0002的行\n'
output_info_redirect(text, wt_logfile)
ori_rb = xlrd.open_workbook(file_path, formatting_info=True)
ori_sht = ori_rb.sheet_by_name('Page1_Copy')
col_subject = ori_sht.col_values(col_subject_num)
i = 1
xlbook = xlapp.Workbooks.Open(file_path)
xlsht = xlbook.Worksheets(sht_name)
while i < len(col_subject):
del_line = i + 1
if not str(col_subject[i]).startswith('6') or str(
col_subject[i]) in del_list:
col_subject.remove(col_subject[i])
xlsht.Rows(del_line).Delete()
i -= 1
# if str(col_subject[i]) in del_list:
# col_subject.remove(col_subject[i])
# xlsht.Rows(del_line).Delete()
# i -= 1
i += 1
# xlbook.Save()
xlbook.Close(SaveChanges=True)
text = u'删除完毕！\n'
output_info_redirect(text, wt_logfile)
else:
text = u'%s 中, 科目代码、贷方、凭证摘要 有一项或多项未找到\n' \
u'将跳过所有处理步骤, 并把它移动到 %s 中...' % (filename, col_err_dir)
output_info_redirect(text, wt_logfile)
os.path.exists(col_err_dir) and 1 or os.mkdir(col_err_dir)
move_to_path = os.path.join(col_err_dir, filename)
shutil.move(file_path, move_to_path)
final_echo['col_err'].append(filename)
text = u'移动完毕!\n'
output_info_redirect(text, wt_logfile)
text = u'\n' + u'###' * 20 + \
u'\n金蝶文件切割 Excel文件处理完毕.\n' \
u'请在 %s 中获取处理后的Excel文件 \n' % dst_dir
output_info_redirect(text, wt_logfile)
if os.listdir(err_dir):
err_ori_xls_dir = os.path.join(err_dir, u'有错误的原始报表')
if os.path.exists(err_ori_xls_dir):
for i in os.listdir(err_ori_xls_dir):
print i,
i = i.encode('utf-8')
with open(wt_logfile, 'a+') as write_file:
write_file.write(i + '\n')
print('')
text = u'原始报表有误, 请在 %s 获取相关报表\n' % err_ori_xls_dir
output_info_redirect(text, wt_logfile)
if len(final_echo['col_err']) > 0:
for i in final_echo['col_err']:
print i
i = i.encode('utf-8')
with open(wt_logfile, 'a+') as write_file:
write_file.write(i + '\n')
print(u'~~~~~')
text = u'没有在报表内找到 科目代码、贷方、或 凭证摘要, 请在 %s 获取相关报表\n' % err_ori_xls_dir
output_info_redirect(text, wt_logfile)
else:
text = u'并且, 没有找到出错的报表! Good Job~\n'
output_info_redirect(text, wt_logfile)
os.removedirs(err_dir)
text = u'###' * 20 + \
u'\n请按 [回车键] 退出...'
output_info_redirect(text, wt_logfile)
raw_input()
time.sleep(1)
return
def get_accountant_src_xl_data(accountant_src_xl_path, shop_depart_relation_data, date, logfile):
# accountant_src_xl_path = u'E:\诺互银行\会计给到原件'
# shop_depart_relation_data is a dict, got from function shop_depart_relationship
# date looks like YYYY-MM-DD
resp_dict = {}
data_list = []
no_depart_data_list = []
xl_file_list = os.listdir(accountant_src_xl_path)
for _xl_file in xl_file_list:
if not _xl_file.endswith('.xlsx'):
continue
else:
_xl_file_path = os.path.join(accountant_src_xl_path, _xl_file)
_src_wb = load_workbook(_xl_file_path)
_ws_names = _src_wb.sheetnames
if 'Sheet1' in _ws_names:
_src_ws = _src_wb['Sheet1']
else:
_src_ws = _src_wb[_ws_names[0]]
_my_max_row = _src_ws.max_row
_my_max_col = _src_ws.max_column
# 从第二行开始遍历,
# 如果这一行没有数据, 则pass;
# 如果这一行有数据, 则判断 店名(第六列) 是否在shop_depart_relation_data的key_list中
# - 如果没有, 则把一整行数据记入no_depart_data_list
# - 如果有, 则摘取个别数据, 记入data_list
for _row in range(2, _my_max_row + 1):
if _src_ws.cell(_row, 2).value:
_my_shop = _src_ws.cell(_row, 6).value
if _my_shop not in shop_depart_relation_data:
text = u'%s 第%i行, 未找到[%s]的记账部门 \n' % (_xl_file, _row, _src_ws.cell(_row, 6).value)
output_info_redirect(text, logfile)
_no_depart_data = []
# 如果no_depart_data_list没有数据, 则向列表中加入标题行(即: 第一行)
if len(no_depart_data_list) == 0:
_data_row_list = [1, _row]
else:
_data_row_list = [_row]
for i in _data_row_list:
for _col in range(1, _my_max_col + 1):
_no_depart_data.append(_src_ws.cell(i, _col).value)
no_depart_data_list.append(_no_depart_data)
continue
else:
depart_name = shop_depart_relation_data[_my_shop]
summary = _src_ws.cell(_row, 12).value
payee_name = _src_ws.cell(_row, 8).value
payee_card_num = str(_src_ws.cell(_row, 11).value).replace(' ', '')
payee_bank = _src_ws.cell(_row, 10).value
amount = float(_src_ws.cell(_row, 9).value)
bill_num = _src_ws.cell(_row, 3).value
sign_by = _src_ws.cell(_row, 16).value
expense_type = _src_ws.cell(_row, 17).value
pay_at = date
applicant = _src_ws.cell(_row, 7).value
data_list.append([depart_name, summary, payee_name, payee_card_num, payee_bank, amount,
bill_num, sign_by, expense_type, pay_at, applicant])
else:
continue
resp_dict['data_list'] = data_list
if len(no_depart_data_list) > 0:
resp_dict['no_depart_data_list'] = no_depart_data_list
return resp_dict
def merge_payment_xl(total_xl, merge_data, logfile):
# total_xl = u'E:\诺互银行\付款总计\month.day\month.day付款总计.xlsx'
# accountant_src_xl_path = u'E:\诺互银行\会计给到原件'
# merge_data is a list looks like [[depart_name1, summary1, .....], [depart_name2, summary2, ....], ....]
app = xlwings.App(visible=True, add_book=False)
total_wb = app.books.open(total_xl)
payment_detail_ws = total_wb.sheets[u'要付款明细']
# payment_detail_ws = total_wb.sheets['Sheet1']
# 获取used_range, 根据最后一个cell找到最大行号
sht_max_row_num = payment_detail_ws.used_range.last_cell.row
output_info_redirect(u'要付款明细最大行号为: %s\n待合并的数据共: %s 行\n'
% (sht_max_row_num, len(merge_data)), logfile)
depart_name_tag_dict = {} # 记录depart_name及它所对应的最后一行号
depart_name_tag_list = [] # 按顺序append depart_name, 当有插入行操作时, 此depart_name和它之后的所有depart_name 所对应的插入行号 + 1
# 1. 遍历[要付款明细]sheet表的A列, 如果连续两个单元格内容不一样,
# 则把 先一个单元格的内容 作为key, 行号 作为value 记入depart_name_tag_dict
# 2. 并且按顺序将 先一个单元格的内容 记入depart_name_tag_list, 作为先后顺序, 在插入行时, 后面的depart_name对应的value + 1
for i in range(2, sht_max_row_num + 1):
depart_name_1 = payment_detail_ws.range('A' + str(i)).value
depart_name_2 = payment_detail_ws.range('A' + str(i+1)).value
if depart_name_1 != depart_name_2:
depart_name_tag_dict[depart_name_1] = i
depart_name_tag_list.append(depart_name_1)
# 遍历会计原始数据
for data in merge_data:
# 找到所需插入的行号, 插入数据
_depart_name = data[0]
if _depart_name in depart_name_tag_dict:
# 如果 日款表 中, 找到 记账部门, 直接添加
insert_at = depart_name_tag_dict[_depart_name] + 1
payment_detail_ws.api.Rows(insert_at).Insert()
payment_detail_ws.range('A' + str(insert_at)).value = data
# 此depart_name和它之后的所有depart_name 所对应的插入行号 + 1 #####特别备注, 包括它自己
depart_index = depart_name_tag_list.index(_depart_name)
for _name in depart_name_tag_list[depart_index:]:
depart_name_tag_dict[_name] = depart_name_tag_dict[_name] + 1
else:
# # 如果 日款表 中, 没有找到 记账部门, 则往最后一行添加此条目; excel的最后一行空白行号 = sht_max_row_num + 1
# insert_at = sht_max_row_num + 1
# payment_detail_ws.range('A' + str(insert_at)).value = data
# depart_name_tag_dict[_depart_name] = sht_max_row_num
# depart_name_tag_list.append(_depart_name)
# # 加在最后一行有点问题. 个别条目会隔非常多空行后, 添加在excel表最后
#
# 更改为: 在表格头部(第二行)插入 没有找到记账部门的付款信息
#
insert_at = 2
payment_detail_ws.api.Rows(insert_at).Insert()
payment_detail_ws.range('A' + str(insert_at)).value = data
depart_name_tag_dict[_depart_name] = 2
for _name in depart_name_tag_list[:]:
depart_name_tag_dict[_name] = depart_name_tag_dict[_name] + 1
sht_max_row_num += 1
output_info_redirect(u'%s插入在表格当前的第%s行, 现在表格共%s行' % (data[6], insert_at, sht_max_row_num), logfile)
# 保存并退出
total_wb.save()
total_wb.close()
app.quit()
return
print(u'脚本启动! \n'
u'请选择需要操作的项目: \n'
u'1: 处理金蝶导出文件 \n'
u'2: 利润表 \n'
u'3: 费用占比 \n'
u'4: 直接退出 \n')
choice = raw_input()
if choice == '1':
wt_logfile = create_logfile('kingdee')
text = u'选择了1\n' + \
u'##' * 14 + '\n' + \
u'# 开始运行 金蝶报表切割脚本 #\n' + \
u'##' * 14
output_info_redirect(text, wt_logfile)
src_dir = u'E:\报表处理\金蝶报表切割\原始报表'
dst_dir = u'E:\报表处理\金蝶报表切割\处理后报表'
err_dir = u'E:\报表处理\金蝶报表切割\\z有错误的报表'
chk_folder(src_dir, wt_logfile, dst_dir, err_dir)
copy_files(src_dir, dst_dir, tag='file', wt_logfile=wt_logfile)
KingdeeFileCut.core_method(dst_dir, err_dir, wt_logfile=wt_logfile)
break
elif choice == '2':
wt_logfile = create_logfile('profit')
text = u'选择了2\n' + \
u'##' * 14 + '\n' + \
def get_voucher_info(split_from_xl_path, pay_time, payee_data, paying_bank_data, logfile_path):
# return data looks like {'voucher_data': {记账部门1-地区α: [数据A, 数据B....],
# 记账部门2-地区β: [数据C, 数据D....], ......}
# 'error_data': [[u'错误原因', ....], [数据E, 数据F...], [数据G, 数据H...], ......]}
# split_from_xl_path = u'E:\诺互银行\付款总计\mm.dd\mm.dd日款.xlsx'
# pay_time is a string looks like YYYY-mm-dd
# payee_data got from DependedInfo.get_payee_info
# paying_bank_data got from DependedInfo.get_paying_bank_info
pay_time = int(str(pay_time).replace('-', ''))
app = xlwings.App(visible=True, add_book=False)
wb = app.books.open(split_from_xl_path)
# ws = wb.sheets[u'要付款明细']
ws = wb.sheets['Sheet1']
last_row_num = ws.used_range.last_cell.row
output_info_redirect(u'要付款明细最大行号为: %s' % last_row_num, logfile_path)
voucher_data = {}
error_data = [[u'错误原因', u'出款银行', u'摘要信息', u'最终收款人', u'银行卡号', u'开户银行',
u'支付金额', u'单据编号', u'签收人', u'费用类型', u'出款日期', u'申请人']]
for row_num in range(2, last_row_num + 1):
making_depart = ws.range('A' + str(row_num)).value
if not making_depart:
# 这一行是空行
continue
else:
payee_account = ws.range('D' + str(row_num)).value
# 如果"收款人账号"或"制单部门"没有在相应的xxx_data中找到,
# 那么就把对应的这一整行数据, append到xxx_not_found列表中
this_line_data = ws.range('A' + str(row_num)).expand('right').value
# type(this_line_data) == list
if payee_account not in payee_data:
output_info_redirect(u'没有在"收款人信息"中找到[收款账号]:%s, 将把付款明细第%s行记入错误信息...'
% (payee_account, row_num), logfile_path)
this_line_data.insert(0, u'收款人信息不存在')
error_data.append(this_line_data)
elif making_depart not in paying_bank_data:
output_info_redirect(u'没有在"付款映射信息"中找到[记账部门]:%s, 将把付款明细第%s行记入错误信息...'
% (making_depart, row_num), logfile_path)
this_line_data.insert(0, u'制单部门不存在')
error_data.append(this_line_data)
else:
_append_data = [
payee_account, # C 收款人帐号 - 总表D列
this_line_data[2], # D 收款人名称 - 总表C列|this_line_data index 2
this_line_data[4], # E 收方开户支行 - 总表E列|this_line_data index 4
payee_data[payee_account]['province'], # F 收款人所在省 - 根据收款人账号, 向<打款资料库>查到相应 省
payee_data[payee_account]['city'], # G 收款人所在市 - 根据收款人账号, 向<打款资料库>查到相应 市
'', # H 收方邮件地址 - 空白
'', # I 收方移动电话 - 空白
u'人民币', # J 币种 - [人民币]
'', # K 付款分行 - 空白
u'普通', # L 结算方式 - [普通]
'', # M 业务种类 - 空白
paying_bank_data[making_depart][0], # N 付方帐号 - 根据depart_name匹配<银行核对公式>的[银行明细]表
pay_time, # O 期望日 - 输入的日期, 格式为YYYYmmdd
'', # P 期望时间 - 空白
this_line_data[6], # Q 用途 - 总表G列|this_line_data index 6
this_line_data[5], # R 金额 - 总表F列|this_line_data index 5
'', # S 收方行号 - 空白
'', # T 收方开户银行 - 空白
'', # U 业务摘要 - 空白
]
making_depart_region = paying_bank_data[making_depart][1]
voucher_key = '-'.join([making_depart, making_depart_region])
if voucher_key in voucher_data.keys():
voucher_data[voucher_key].append(_append_data)
else:
voucher_data[voucher_key] = [_append_data]
wb.close()
app.quit()
resp_data = {'voucher_data': voucher_data}
if len(error_data) > 1:
resp_data['error_data'] = error_data
return resp_data