def add_customer(file,sheet_name):
  wb = open_workbook(file)
  sheet = wb.sheet_by_name(sheet_name)
  fileHandle = open ( 'organization_customer.txt', 'a' )
  for row in range(1,sheet.nrows):
    customer_no = transform_code_to_utf(sheet.cell(row,0).value)
    print customer_no
    id_type = transform_code_to_utf(sheet.cell(row,1).value)
    id_no = transform_code_to_utf(sheet.cell(row,2).value)
    registration_auth = transform_code_to_utf(sheet.cell(row,3).value)
    id_start_time = getdate(sheet.cell(row,4).value)
    id_end_time = getdate(sheet.cell(row,5).value)
    organization_code = transform_code_to_utf(sheet.cell(row,6).value)
    units_awarded = transform_code_to_utf(sheet.cell(row,7).value)
    sign_date = getdate(sheet.cell(row,8).value)
    end_date = getdate(sheet.cell(row,9).value)
    customer_name = transform_code_to_utf(sheet.cell(row,10).value)
    english_name = transform_code_to_utf(sheet.cell(row,11).value)
    register_date = getdate(sheet.cell(row,12).value)
    register_area_code = get_code(sheet.cell(row,13).value)
    credit_code_no = transform_code_to_utf(sheet.cell(row,14).value)
    enterprise_property = transform_code_to_utf(sheet.cell(row,15).value)
    corporate_enterprise = transform_code_to_utf(sheet.cell(row,16).value)
    industry_category = transform_code_to_utf(sheet.cell(row,17).value)
    if industry_category.find('\n'):
      industry_category = industry_category.replace('\n','')
      print "YYYYYY"
    ownership_property = transform_code_to_utf(sheet.cell(row,18).value)
    main_business_range = transform_code_to_utf(sheet.cell(row,19).value)
    if main_business_range.find('\n'):
      main_business_range = main_business_range.strip('\n')
    affiliated_business_range = transform_code_to_utf(sheet.cel(row,20).value)
    register_capital = transform_code_to_utf(sheet.cell(row,21).value)
    recieve_capital = transform_code_to_utf(sheet.cell(row,22).value)
    capital_currency = get_value(sheet.cell(row,23).value)
    china_sign = get_code(sheet.cell(row,24).value)
    country_tax_register_code = transform_code_to_utf(sheet.cell(row,25).value)
    tax_register_strat_date = getdate(sheet.cell(row,26).value)
    tax_register_deadline = transform_code_to_utf(sheet.cell(row,27).value)
    local_tax_register_code  = transform_code_to_utf(sheet.cell(row,28).value)
    local_register_start_date = getdate(sheet.cell(row,29).value)
    local_register_deadline = transform_code_to_utf(sheet.cell(row,30).value)
    customer_type = get_code(sheet.cell(row,31).value)
    public_enterprise_type = get_code(sheet.cell(row,32).value)
    income_rank = sheet.cell(row,33).value
    enterprise_holding = get_code(sheet.cell(row,34).value)
    resident_symbol = get_code(sheet.cell(row,35).value)
    country_city_symbol = get_code(sheet.cell(row,36).value)
    home_broad_symbol = get_code(sheet.cell(row,37).value)
    enterprise_scale = get_code(sheet.cell(row,38).value)
    labor_intensive_symbol = get_code(sheet.cell(row,39).value)
    own_broker = get_code(sheet.cell(row,40).value)
    related_party_type = get_code(sheet.cell(row,41).value)
    positions_proportion = get_code(sheet.cell(row,42).value)
    enterprise_economy_type = transform_code_to_utf(sheet.cell(row,43).value)
    stock_code = get_code(sheet.cell(row,44).value)
    staf_num = transform_code_to_utf(sheet.cell(row,45).value)
    open_account_licence = transform_code_to_utf(sheet.cell(row,46).value)
    basic_account_no = transform_code_to_utf(sheet.cell(row,47).value)
    basic_account_bank_no = transform_code_to_utf(sheet.cell(row,48).value)
    basic_account_bank = transform_code_to_utf(sheet.cell(row,49).value)
    contact_num = transform_code_to_utf(sheet.cell(row,50).value)
    contact_address = transform_code_to_utf(sheet.cell(row,51).value)
    post_code = transform_code_to_utf(sheet.cell(row,52).value)
    company_web_add = transform_code_to_utf(sheet.cell(row,53).value)
    corp_id_type = transform_code_to_utf(sheet.cell(row,54).value)
    corp_id_no = transform_code_to_utf(sheet.cell(row,55).value)
    corp_name = transform_code_to_utf(sheet.cell(row,56).value)
    corp_sex = transform_code_to_utf(sheet.cell(row,57).value)
    corp_nationality = transform_code_to_utf(sheet.cell(row,58).value)
    corp_licence_auth = transform_code_to_utf(sheet.cell(row,59).value)
    corp_birth_date = getdate(sheet.cell(row,60).value)
    corp_licence_issued_date = getdate(sheet.cell(row,61).value)
    corp_licence_deadline = getdate(sheet.cell(row,62).value)
    corp_licence_address = transform_code_to_utf(sheet.cell(row,63).value)

    if customer_no is None or customer_no == '' or customer_no == '\n' or customer_no == '\t' or customer_no == '\r' or customer_no =='999999999' or len(customer_no)>11:
      continue
    else:
      line1 = transform_code_to_utf(sheet_name) + "|" + customer_no + "|" + id_type + "|" + id_no + "|" + registration_auth + "|" + id_start_time + "|" + id_end_time + "|" +  organization_code + "|" + units_awarded + "|" + sign_date

      line2 = end_date + "|" + customer_name + "|" + english_name + "|" + register_date + "|" + register_area_code + "|" + credit_code_no + "|" + enterprise_property  + "|" + corporate_enterprise + "|" + industry_category + "|" + ownership_property

      line3 =  main_business_range + "|" + affiliated_business_range + "|" + register_capital + "|" + recieve_capital + "|" + capital_currency + "|" + china_sign + "|" + country_tax_register_code + "|" + tax_register_strat_date + "|" + tax_register_deadline + "|" + local_tax_register_code

      line4 =  local_register_start_date + "|" + local_register_deadline + "|" + customer_type + "|" + public_enterprise_type + "|" + income_rank + "|" + enterprise_holding + "|" + resident_symbol + "|" + country_city_symbol + "|" + home_broad_symbol + "|" + enterprise_scale

      line5 =  labor_intensive_symbol + "|" + own_broker + "|" + related_party_type + "|" + positions_proportion + "|" + enterprise_economy_type + "|" + stock_code + "|" + staf_num + "|" + open_account_licence + "|" + basic_account_no + "|" + basic_account_bank_no

      line6 =  basic_account_bank + "|" + contact_num + "|" + contact_address + "|" + post_code + "|" + company_web_add + "|" + corp_id_type + "|" + corp_id_no + "|" + corp_name + "|" + corp_sex + "|" + corp_nationality + "|" + corp_licence_auth + "|" + corp_birth_date + "|" + corp_licence_issued_date + "|" + corp_licence_deadline + "|" + corp_licence_address + "\n"

      line = line1 + "|" +line2 + "|" + line3 + "|" + line4 + "|" + line5 + "|" + line6
      fileHandle.write(line)
  fileHandle.close()
  return True


#获取usting中的code字段
def get_code(ustring):
  str = transform_code_to_utf(ustring)
  if str.find("-")!=-1:
    if str!='':
      code,value = str.split('-')
    else:
      code = ''
  else:
    code = str

if  __name__=='__main__':
 if add_customer(u'数据文档/支行对公客户信息.xls',u'25银鹰')==False:
    exit(1)