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)