import os
import xlrd
import xlwt
from xlutils.copy import copy
from xlutils.filter import process,XLRDReader,XLWTWriter
import datetime


def copy2(wb):
 w = XLWTWriter()
 process(
 XLRDReader(wb,'unknown.xls'),
 w
 )
 return w.output[0][1], w.style_list
# 估值表类型
# 中债
V_CNBD = 0x0001
# 中证
V_CSI1 = 0x0002
# 交易场所类型
# 银行间
EX_IB = 0x1001
# 交易所
EX_SHSZ = 0x1002

def generate_excel(PATH):
 today = datetime.datetime.today().strftime("%Y%m%d")
 today2 = datetime.datetime.today().strftime("%Y-%m-%d")
 output = "债券估值导入模板.xls"
 outputdate = ""
 datefreq = {}

 files = [x for x in os.listdir(PATH) if x.endswith('.xls') 
 and (x.find('中债估值2')>= 0 or x.find('中证估值2')>=0)]
 print(files)

 # 保存解析到的债券信息
 global_dict = {}

 for item in files:
 print("开始解析文件:{}".format(item))

 # 估值表类型: 中债估值(cnbd)、中证估值(csi1)
 file_type = None
 # 交易场所类型: 银行间(IB)、交易所(SHSZ)
 ex_type = None

 if item.find('中债') >= 0:
 file_type = V_CNBD
 else:
 file_type = V_CSI1

 if item.find('.IB') > 0:
 ex_type = EX_IB
 else:
 ex_type = EX_SHSZ

 # 债券ID
 code = item[item.find('(')+1:item.find(')')].split('.')
 code.reverse()
 code = ''.join(code)
 if code not in global_dict:
 global_dict[code] = {}
 global_dict[code]['value_date'] = ''
 global_dict[code]['remain_date'] = ''
 global_dict[code]['dirty_price'] = ''
 global_dict[code]['net_value'] = ''
 global_dict[code]['modidura_value'] = ''
 global_dict[code]['cnvxty_value'] = ''
 global_dict[code]['vobp_value'] = ''
 global_dict[code]['yield_value'] = ''


 wb = xlrd.open_workbook(os.path.join(PATH, item))
 sheet = wb.sheets()[0]
 # bugfix: 2020年09月15日 17:00:51
 #data = sheet.row(1)
 data = [ x for x in sheet.get_rows() if x[1].value == '推荐' ][0]


 # 文件日期
 file_date = item[4:14].replace('-', '/')
 global_dict[code]['file_date'] = file_date

 # 估值日期
 value_date = data[0].value.split('-')

 # 统计估值日期出现频率
 key = "".join(value_date)
 if not key in datefreq:
 datefreq[key] = 1
 else:
 datefreq[key] += 1

 value_date.reverse()
 final_value_date = "/".join(value_date)
 #final_value_date = '/'.join([x for x in map(lambda x: str(int(x)), value_date)])
 global_dict[code]['value_date'] = final_value_date

 # 剩余期限 只有中债文件有
 remain_date = ""
 if file_type == V_CNBD:
 remain_date = data[2].value
 global_dict[code]['remain_date'] = remain_date

 # 市价全价
 dirty_price = ""
 # 银行间使用中债的价格
 if file_type == V_CNBD and ex_type == EX_IB:
 dirty_price = data[3].value
 elif file_type == V_CSI1 and ex_type == EX_SHSZ:
 dirty_price = data[5].value
 if global_dict[code]['dirty_price'] == "":
 global_dict[code]['dirty_price'] = dirty_price

 # 市价净价
 net_value = ""
 if file_type == V_CNBD and ex_type == EX_IB:
 net_value = data[5].value
 elif file_type == V_CSI1 and ex_type == EX_SHSZ:
 net_value = data[7].value
 if global_dict[code]['net_value'] == "":
 global_dict[code]['net_value'] = net_value

 # 市价久期
 modidura_value = ""
 if file_type == V_CNBD and ex_type == EX_IB:
 modidura_value = data[8].value
 elif file_type == V_CSI1 and ex_type == EX_SHSZ:
 modidura_value = data[9].value
 if global_dict[code]['modidura_value'] == "":
 global_dict[code]['modidura_value'] = modidura_value

 # 市价凸性
 cnvxty_value = ""
 if file_type == V_CNBD and ex_type == EX_IB:
 cnvxty_value = data[9].value
 elif file_type == V_CSI1 and ex_type == EX_SHSZ:
 cnvxty_value = data[10].value
 if global_dict[code]['cnvxty_value'] == "":
 global_dict[code]['cnvxty_value'] = cnvxty_value

 # 基点价值
 vobp_value = ""
 if file_type == V_CNBD:
 vobp_value = data[10].value
 global_dict[code]['vobp_value'] = vobp_value

 # 市价收益率
 yield_value = ""
 if file_type == V_CNBD and ex_type == EX_IB:
 yield_value = data[6].value
 elif file_type == V_CSI1 and ex_type == EX_SHSZ:
 yield_value = data[8].value
 if global_dict[code]['yield_value'] == "":
 global_dict[code]['yield_value'] = yield_value

 #print(global_dict)
 # 打开模板excel文件 并保留原文件格式
 rdbook = xlrd.open_workbook(os.path.join(PATH, output), formatting_info=True)
 # 获取总行数
 rdsheet = rdbook.sheet_by_index(0)
 # 获取拷贝
 wtbook, style_list = copy2(rdbook)
 # 获取xlwd可以操作的sheeet
 wtsheet = wtbook.get_sheet(0)

 rownum = 1
 for code in global_dict.keys():
 # 债券ID
 xf_index = rdsheet.cell_xf_index(0, 0)
 wtsheet.write(rownum, 0, code, style_list[xf_index])
 # 估值日
 xf_index = rdsheet.cell_xf_index(0, 1)
 wtsheet.write(rownum, 1, global_dict[code]['value_date'], style_list[xf_index])
 # 剩余期限
 xf_index = rdsheet.cell_xf_index(0, 2)
 wtsheet.write(rownum, 2, global_dict[code]['remain_date'], style_list[xf_index])
 # 市价全价
 xf_index = rdsheet.cell_xf_index(0, 3)
 wtsheet.write(rownum, 3, global_dict[code]['dirty_price'], style_list[xf_index])
 # 市价净价
 xf_index = rdsheet.cell_xf_index(0, 4)
 wtsheet.write(rownum, 4, global_dict[code]['net_value'], style_list[xf_index])
 # 市价久期
 xf_index = rdsheet.cell_xf_index(0, 5)
 wtsheet.write(rownum, 5, global_dict[code]['modidura_value'], style_list[xf_index])
 # 市价凸性
 xf_index = rdsheet.cell_xf_index(0, 6)
 wtsheet.write(rownum, 6, global_dict[code]['cnvxty_value'], style_list[xf_index])
 # 基点价值
 xf_index = rdsheet.cell_xf_index(0, 7)
 wtsheet.write(rownum, 7, global_dict[code]['vobp_value'], style_list[xf_index])
 # 市价收益率
 xf_index = rdsheet.cell_xf_index(0, 12)
 wtsheet.write(rownum, 12, global_dict[code]['yield_value'], style_list[xf_index])
 rownum += 1
 outputdate = sorted(datefreq.items(), key=lambda kv:(kv[1], kv[0]), reverse=True)[0][0]
 wtbook.save(os.path.join(PATH, '债券估值导入{}.xls'.format(outputdate)))
 print('处理完成')
 return "处理成功"

if __name__ == '__main__':
 path = input()
 ret = generate_excel(path)
 print(ret)