# 导入包
import pandas as pd
import xlrd
# 用pandas读入dta文件
df = pd.read_stata('financial_advisor.dta')
df['deal_num']=df['deal_num'].str.replace('\'','')
df['deal_num']=df['deal_num'].str.strip()
df['excel_name']=df['excel_name'].str.replace('\'','')
#df=df.drop(columns=['excel_name'])
#print(df['deal_num'])
en=df['excel_name'].values.tolist()
dn=df['deal_num'].values.tolist()
ri=df['row_index'].values.tolist()
ci=df['col_index'].values.tolist()
#ty=df['type'].values.tolist()
#程序的主体部分
for k,j,l,h in zip(en,dn,ri,ci):
    excel=r'd:/Thomson20190623/%s'%k
    data=xlrd.open_workbook(excel)
    sheet=data.sheet_by_name('%s'%j)
    la=sheet.cell_value(l+1,h)
    la_role=sheet.cell_value(l+1,h+1)
    #financial与legal rank value 位置不同 +2 与+4
    la_rank_value=sheet.cell_value(l+1,h+2)
    print(k,j,l,h,la,la_role,la_rank_value,sep=';')
    # if m=='Both':
    #   value=sheet.cell_value(l,h+1)
    # if m=='T':
    #   value=sheet.cell_value(l,h+2)
    # if m=='A':
    #   value=sheet.cell_value(l,h+2)